Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Nested OR conditions may lead to incorrest results [CORE5268] #2038

Closed
firebird-automations opened this issue Jun 10, 2016 · 6 comments
Closed

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: Benjamin Bacik (benjamincomed)

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

Commits: ef9cc3f 1cab1dc

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Version: 4.0 Initial [ 10621 ]

assignee: Dmitry Yemanov [ dimitr ]

security: Developers [ 10012 ] =>

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

summary: Where statement are bugged. => Nested OR conditions may lead to incorrest results

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

Fix Version: 3.0.1 [ 10730 ]

Fix Version: 4.0 Alpha 1 [ 10731 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

It should be fixed in tomorrow's snapshot builds and beyond. Thanks for bugreport.

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

status: Resolved [ 5 ] => Resolved [ 5 ]

QA Status: No test => Done successfully

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

status: Resolved [ 5 ] => Closed [ 6 ]

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment