You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
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);
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
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
The text was updated successfully, but these errors were encountered: