[#CORE-2073] Expression indexes bug: incorrect result for the inverted boolean

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

This issue is related to:

~~QA-224~~ Test for CORE-2073

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:

[ Permalink | « Hide ]

Sean Leyne added a comment - 09/Sep/08 01:32 PM

Please provide PLANs generated for the statements.

[ Permalink | « Hide ]

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.