Issue Details (XML | Word | Printable)

Key: CORE-5268
Type: Bug Bug
Status: Closed Closed
Resolution: Fixed
Priority: Critical Critical
Assignee: Dmitry Yemanov
Reporter: Benjamin Bacik
Votes: 0
Watchers: 0
Operations

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

Nested OR conditions may lead to incorrest results

Created: 10/Jun/16 08:59 AM   Updated: 10/Jun/16 06:59 PM
Component/s: Engine
Affects Version/s: 4.0 Initial, 3.0.0
Fix Version/s: 3.0.1, 4.0 Alpha 1

Environment: Win10 Firebird 3.0.0.32483 x64

QA Status: Done successfully


 Description  « Hide
under some Conditions the index of where-clause are not working properly.
The engine is getting a Problem with getting the correct count of records if using variables in where statement. You can reproduce this with this example:

SET SQL DIALECT 3;
CREATE TABLE TESTTAB (
    FELD_A VARCHAR(50),
    FELD_B VARCHAR(10),
    FELD_C CHAR(10)
);
CREATE INDEX TESTTAB_IDX1 ON TESTTAB (FELD_B);
CREATE INDEX TESTTAB_IDX2 ON TESTTAB (FELD_A);

INSERT INTO TESTTAB (FELD_A, FELD_B, FELD_C)
             VALUES ('AAAA', 'BB', 'CC ');
INSERT INTO TESTTAB (FELD_A, FELD_B, FELD_C)
             VALUES ('aaa', 'aa', 'aaa ');
INSERT INTO TESTTAB (FELD_A, FELD_B, FELD_C)
             VALUES ('UUU', 'UUU', 'UUU ');
INSERT INTO TESTTAB (FELD_A, FELD_B, FELD_C)
             VALUES ('uuu', 'uu', 'uu ');

COMMIT WORK;


Test SQLs
--Wrong: should get at least 3 records, but gives only 1
select * from Testtab t
where
   :InputData='123456' -- inputdatastring 'blub' for testcase
  or
     (
        upper(t.feld_b) starting with 'U'
        or
       t.feld_a starting with 'a' /* with Index */
     )
   
  
-- ok: all 3 records found
select * from Testtab t
where
   /* :InputData='123456' or */ -- comment
  ( upper(t.feld_b) starting with 'U'
   or
    t.feld_a starting with 'a' /* with Index */
   )

-- ok:
select * from Testtab t
where
   :InputData='123456' OR -- inputdatastring 'blub' for testcase
  ( upper(t.feld_b) starting with 'U'
   or lower(t.feld_a) starting with 'a' /* Index disabled via LOWER */
   )

also this is ok:
select * from Testtab t
where
   :InputData='123456' OR -- inputdatastring 'blub' for testcase
    upper(t.feld_b) starting with 'U' OR
    t.feld_a starting with 'a'

with no ( ) for sub-or --> BUT: This is mathematically equivalent. to the first statement. --> Getting the right index for records

   






 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Dmitry Yemanov added a comment - 10/Jun/16 12:33 PM
It should be fixed in tomorrow's snapshot builds and beyond. Thanks for bugreport.