Issue Details (XML | Word | Printable)

Key: CORE-2073
Type: Bug Bug
Status: Closed Closed
Resolution: Fixed
Priority: Major Major
Assignee: Dmitry Yemanov
Reporter: freemanzav
Votes: 0
Watchers: 1
Operations

If you were logged in you would be able to see more operations.
Firebird Core

Expression indexes bug: incorrect result for the inverted boolean

Created: 09/Sep/08 05:00 AM   Updated: 26/Jan/09 08:28 AM
Component/s: Engine
Affects Version/s: 2.0.0, 2.0.1, 2.0.2, 2.0.3, 2.1.0, 2.0.4, 2.5 Alpha 1, 2.1.1
Fix Version/s: 2.0.5, 2.1.2, 2.5 Beta 1

Time Tracking:
Not Specified

Issue Links:
Relate
 

Planning Status: Unspecified


 Description  « Hide
CREATE TABLE TMP_DATE1
(
  DATE1 DATE,
  DATE2 DATE
);

EXECUTE BLOCK
AS
  DECLARE VARIABLE D DATE;
BEGIN
  D = '01.01.2008';
  WHILE (D < '01.08.2008') DO BEGIN
    INSERT INTO TMP_DATE1(DATE1, DATE2)
      VALUES(:D, :D + 100);
      D = D + 1;
  END
END;

CREATE INDEX TMP_DATE1_IDX1 ON TMP_DATE1 COMPUTED BY (DATE1+0)


---------------------------------------

without index

SELECT count(*) FROM TMP_DATE1 T WHERE '01.03.2008' BETWEEN T.DATE1 AND T.DATE2;

count = 61

---------------------------------------

with index

SELECT count(*) FROM TMP_DATE1 T WHERE '01.03.2008' BETWEEN T.DATE1+0 AND T.DATE2

count = 1


 All   Comments   Work Log   Change History   Version Control   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Sean Leyne added a comment - 09/Sep/08 01:32 PM
Please provide PLANs generated for the statements.

Dmitry Yemanov added a comment - 09/Sep/08 02:46 PM
Sean, this is a complete test case :-) You can see exactly the same plans as the reporter:

PLAN (TMP_DATE1 NATURAL) -- for the first query
and
PLAN (TMP_DATE1 INDEX(TMP_DATE1_IDX1)) -- for the second one

Moreover:

CREATE INDEX TMP_DATE1_IDX2 ON TMP_DATE1 (DATE1) ;

SELECT count(*) FROM TMP_DATE1 T WHERE '01.03.2008' BETWEEN T.DATE1 AND T.DATE2;
-- PLAN (TMP_DATE1 INDEX(TMP_DATE1_IDX2))
-- returns correct result (count == 61)

The simplified predicate which exposes the bug is:
  WHERE '01.03.2008' >= T.DATE1

The problem is that the inverted boolean (literal compared to an indexed object) works correctly for an indexed field but fails for an indexed expression.