Incorrect index usage for some cases of index expressions involving literals
----------------------------------------------------------------------------
Key: CORE-5744
URL: http://tracker.firebirdsql.org/browse/CORE-5744
Project: Firebird Core
Issue Type: Bug
Components: Engine
Affects Versions: 3.0.3, 2.5.8, 4.0 Alpha 1, 3.0.2, 2.5.7, 3.0.1, 2.5.6,
3.0.0, 4.0 Initial, 2.5.5, 2.5.4, 2.5.3 Update 1, 2.1.7, 2.5.3, 2.5.2 Update 1,
2.5.2, 2.5.1, 2.5.0
Reporter: Dmitry Yemanov
recreate table tidx (col varchar(10));
commit;
insert into tidx values ('asd');
commit;
create index itidx1 on tidx computed by (col || '0');
commit;
set plan;
select 1 from tidx where col || '0' = 'asd0';
PLAN (TIDX INDEX (ITIDX1))
CONSTANT
============
1
(*) Matching expressions, correct plan and result
select 1 from tidx where col || 0 = 'asd0';
PLAN (TIDX INDEX (ITIDX1))
CONSTANT
============
1
(*) Expressions are in fact different but CAST(0 as VARCHAR(10)) = '0', thus it
surprisingly works.
select 1 from tidx where col || 0 = 'asd0' plan (tidx natural);
PLAN (TIDX NATURAL)
CONSTANT
============
1
(*) Checked without indices - the same result
create index itidx2 on tidx computed by (col || '00');
select 1 from tidx where col || '00' = 'asd00';
PLAN (TIDX INDEX (ITIDX2))
CONSTANT
============
1
(*) Matching expressions, correct plan and result
select 1 from tidx where col || 0 = 'asd0';
PLAN (TIDX INDEX (ITIDX2))
-- NO RESULT!
(*) Expressions are different, plan using index ITIDX2 causes incorrect result
select 1 from tidx where col || 0 = 'asd0' plan (tidx natural);
PLAN (TIDX NATURAL)
CONSTANT
============
1
(*) Checked without indices - one row is returned
I.e. expressions (COL || '00') and (COL || 0) are considered equal and index is
matched, but it definitely shouldn't due to different index keys in the second
index.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://tracker.firebirdsql.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
