Different results in SQL when using ''|| in join clause (to avoid using unselective index) [CORE3274] #3642

firebird-automations · 2010-12-08T11:20:15Z

Submitted by: Michiel Spoor (mdspoor)

Votes: 2

We regularly use a ||''-construction in selects to avoid the use of
certain (underperforming) indexes, as read in
http://www.firebirdfaq.org/faq158/.

Now we came across a specific curious and disturbing case where this
trick not only avoids using an index but also returns a different
result...

We've been able to reconstruct the issue using an example script for
illustration, and tested it on FB 2.0.6, FB 2.1.3 and the current snapshot of FB 2.1.4 (8 dec 2010).

Now the linkfields between the tables in this example are of an integer
type, and if we use +0 in stead of ||'', the same plan as the ||'' case is used, and the results are correct again.
But I see no reason why ||'' should return an other result, and I can't oversee whether this behaviour won't exhibit in some other situations where the key-field is indeed a varchar field.

----------------------------------------------------------
---
-- Create tables in empty database with following script:
CREATE TABLE ACTIVITEIT
(
ACTIVITEIT_ID INTEGER NOT NULL,
GRPDLN_ID INTEGER NOT NULL,
ATYPE_ID INTEGER NOT NULL,
CONSTRAINT PK_ACTIVITEIT PRIMARY KEY (ACTIVITEIT_ID)
);
CREATE ASC INDEX "I_ACTIVITEIT_atype" ON ACTIVITEIT (ATYPE_ID);
CREATE ASC INDEX "I_ACTIVITEIT_dln" ON ACTIVITEIT (GRPDLN_ID);
INSERT INTO ACTIVITEIT (ACTIVITEIT_ID, GRPDLN_ID, ATYPE_ID) VALUES
(862140, 79742, 893);
INSERT INTO ACTIVITEIT (ACTIVITEIT_ID, GRPDLN_ID, ATYPE_ID) VALUES
(2401857, 117854, 893);
INSERT INTO ACTIVITEIT (ACTIVITEIT_ID, GRPDLN_ID, ATYPE_ID) VALUES
(2487854, 121888, 893);
CREATE TABLE GRP_DEELNAME
(
GRPDLN_ID INTEGER NOT NULL,
ZORG_ID INTEGER NOT NULL,
GROEP_ID CHAR( 3) NOT NULL COLLATE NONE,
DLN_AFMELD_DT DATE,
CONSTRAINT PK_GRP_DEELNAME PRIMARY KEY (GRPDLN_ID)
);
CREATE ASC INDEX "I_GRP_DEELNAME_groep" ON GRP_DEELNAME (GROEP_ID);
CREATE ASC INDEX "I_GRP_DEELNAME_zorg" ON GRP_DEELNAME (ZORG_ID);
INSERT INTO GRP_DEELNAME (GRPDLN_ID, ZORG_ID, GROEP_ID, DLN_AFMELD_DT)
VALUES (79742, 1010282, '137', NULL);
INSERT INTO GRP_DEELNAME (GRPDLN_ID, ZORG_ID, GROEP_ID, DLN_AFMELD_DT)
VALUES (115430, 1022631, '137', NULL);
INSERT INTO GRP_DEELNAME (GRPDLN_ID, ZORG_ID, GROEP_ID, DLN_AFMELD_DT)
VALUES (117854, 1021754, '137', NULL);
INSERT INTO GRP_DEELNAME (GRPDLN_ID, ZORG_ID, GROEP_ID, DLN_AFMELD_DT)
VALUES (121888, 1024286, '137', NULL);
Commit;

----------------------------------------------------------
---
-- First query (normal) --> result okay
SELECT dln.grpdln_id , subq.*
FROM grp_deelname dln
LEFT JOIN ( SELECT dln2.zorg_id AS zorg_id, act2.atype_id
FROM grp_deelname dln2
--> Using plain query : result = okay
INNER JOIN activiteit act2 ON act2.grpdln_id = dln2.grpdln_id
WHERE act2.atype_id = 893
) subq ON subq.zorg_id = dln.zorg_id
WHERE dln.dln_afmeld_dt IS NULL
AND dln.groep_id = '137'

Plan:
PLAN JOIN (DLN INDEX (I_GRP_DEELNAME_groep), JOIN (SUBQ DLN2 INDEX
(I_GRP_DEELNAME_zorg), SUBQ ACT2 INDEX (I_ACTIVITEIT_dln, I_ACTIVITEIT_atype)))

Adapted plan:
PLAN JOIN (DLN INDEX (I_GRP_DEELNAME_groep), JOIN (SUBQ DLN2 INDEX
(I_GRP_DEELNAME_zorg), SUBQ ACT2 INDEX (I_ACTIVITEIT_dln, I_ACTIVITEIT_atype)))

4 rows fetched (0 ms)
Result:
GRPDLN_ID ZORG_ID ATYPE_ID
79742 1010282 893
115430 Null Null
117854 1021754 893
121888 1024286 893

(as expected)

----------------------------------------------------------
---
-- Second query (using ||'' as index-avoider) --> result contains errors

SELECT
dln.grpdln_id , subq.*
FROM
grp_deelname dln
LEFT JOIN ( SELECT dln2.zorg_id AS zorg_id, act2.atype_id
FROM grp_deelname dln2
--> Using ||'' as index-avoider
INNER JOIN activiteit act2 ON act2.grpdln_id||'' = dln2.grpdln_id
WHERE act2.atype_id = 893
) subq ON subq.zorg_id = dln.zorg_id
WHERE dln.dln_afmeld_dt IS NULL
AND dln.groep_id = '137'

Plan:
PLAN JOIN (DLN INDEX (I_GRP_DEELNAME_groep), MERGE (SORT (SUBQ ACT2
INDEX (I_ACTIVITEIT_atype)), SORT (SUBQ DLN2 INDEX (I_GRP_DEELNAME_zorg))))

Adapted plan:
PLAN JOIN (DLN INDEX (I_GRP_DEELNAME_groep), MERGE (SORT (SUBQ ACT2
INDEX (I_ACTIVITEIT_atype)), SORT (SUBQ DLN2 INDEX (I_GRP_DEELNAME_zorg))))

4 rows fetched (16 ms)

Result:
GRPDLN_ID ZORG_ID ATYPE_ID
79742 Null Null
115430 Null Null
117854 1021754 893
121888 1024286 893

(first row has unexplained Null values)

firebird-automations · 2010-12-08T11:23:34Z

Modified by: Michiel Spoor (mdspoor)

description: We regularly use a ||''-construction in selects to avoid the use of
certain (underperforming) indexes, as read in
http://www.firebirdfaq.org/faq158/.

Now we came across a specific curious and disturbing case where this
trick not only avoids using an index but also returns a different
result...

We've been able to reconstruct the issue using an example script for
illustration, and tested it on FB 2.0.6 and FB 2.1.3.

Now the linkfields between the tables in this example are of an integer
type, and if we use +0 in stead of ||'', the same plan as the ||'' case is used, and the results are correct again.
But I see no reason why ||'' should return an other result, and I can't oversee whether this behaviour won't exhibit in some other situations where the key-field is indeed a varchar field.

----------------------------------------------------------
---
-- Create tables in empty database with following script:
CREATE TABLE ACTIVITEIT
(
ACTIVITEIT_ID INTEGER NOT NULL,
GRPDLN_ID INTEGER NOT NULL,
ATYPE_ID INTEGER NOT NULL,
CONSTRAINT PK_ACTIVITEIT PRIMARY KEY (ACTIVITEIT_ID)
);
CREATE ASC INDEX "I_ACTIVITEIT_atype" ON ACTIVITEIT (ATYPE_ID);
CREATE ASC INDEX "I_ACTIVITEIT_dln" ON ACTIVITEIT (GRPDLN_ID);
INSERT INTO ACTIVITEIT (ACTIVITEIT_ID, GRPDLN_ID, ATYPE_ID) VALUES
(862140, 79742, 893);
INSERT INTO ACTIVITEIT (ACTIVITEIT_ID, GRPDLN_ID, ATYPE_ID) VALUES
(2401857, 117854, 893);
INSERT INTO ACTIVITEIT (ACTIVITEIT_ID, GRPDLN_ID, ATYPE_ID) VALUES
(2487854, 121888, 893);
CREATE TABLE GRP_DEELNAME
(
GRPDLN_ID INTEGER NOT NULL,
ZORG_ID INTEGER NOT NULL,
GROEP_ID CHAR( 3) NOT NULL COLLATE NONE,
DLN_AFMELD_DT DATE,
CONSTRAINT PK_GRP_DEELNAME PRIMARY KEY (GRPDLN_ID)
);
CREATE ASC INDEX "I_GRP_DEELNAME_groep" ON GRP_DEELNAME (GROEP_ID);
CREATE ASC INDEX "I_GRP_DEELNAME_zorg" ON GRP_DEELNAME (ZORG_ID);
INSERT INTO GRP_DEELNAME (GRPDLN_ID, ZORG_ID, GROEP_ID, DLN_AFMELD_DT)
VALUES (79742, 1010282, '137', NULL);
INSERT INTO GRP_DEELNAME (GRPDLN_ID, ZORG_ID, GROEP_ID, DLN_AFMELD_DT)
VALUES (115430, 1022631, '137', NULL);
INSERT INTO GRP_DEELNAME (GRPDLN_ID, ZORG_ID, GROEP_ID, DLN_AFMELD_DT)
VALUES (117854, 1021754, '137', NULL);
INSERT INTO GRP_DEELNAME (GRPDLN_ID, ZORG_ID, GROEP_ID, DLN_AFMELD_DT)
VALUES (121888, 1024286, '137', NULL);
Commit;

----------------------------------------------------------
---
-- First query (normal) --> result okay
SELECT dln.grpdln_id , subq.*
FROM grp_deelname dln
LEFT JOIN ( SELECT dln2.zorg_id AS zorg_id, act2.atype_id
FROM grp_deelname dln2
--> Using plain query : result = okay
INNER JOIN activiteit act2 ON act2.grpdln_id = dln2.grpdln_id
WHERE act2.atype_id = 893
) subq ON subq.zorg_id = dln.zorg_id
WHERE dln.dln_afmeld_dt IS NULL
AND dln.groep_id = '137'

Plan:
PLAN JOIN (DLN INDEX (I_GRP_DEELNAME_groep), JOIN (SUBQ DLN2 INDEX
(I_GRP_DEELNAME_zorg), SUBQ ACT2 INDEX (I_ACTIVITEIT_dln, I_ACTIVITEIT_atype)))

Adapted plan:
PLAN JOIN (DLN INDEX (I_GRP_DEELNAME_groep), JOIN (SUBQ DLN2 INDEX
(I_GRP_DEELNAME_zorg), SUBQ ACT2 INDEX (I_ACTIVITEIT_dln, I_ACTIVITEIT_atype)))

4 rows fetched (0 ms)
Result:
GRPDLN_ID ZORG_ID ATYPE_ID
79742 1010282 893
115430 Null Null
117854 1021754 893
121888 1024286 893

(as expected)

----------------------------------------------------------
---
-- Second query (using ||'' as index-avoider) --> result contains errors

SELECT
dln.grpdln_id , subq.*
FROM
grp_deelname dln
LEFT JOIN ( SELECT dln2.zorg_id AS zorg_id, act2.atype_id
FROM grp_deelname dln2
--> Using ||'' as index-avoider
INNER JOIN activiteit act2 ON act2.grpdln_id||'' = dln2.grpdln_id
WHERE act2.atype_id = 893
) subq ON subq.zorg_id = dln.zorg_id
WHERE dln.dln_afmeld_dt IS NULL
AND dln.groep_id = '137'

Plan:
PLAN JOIN (DLN INDEX (I_GRP_DEELNAME_groep), MERGE (SORT (SUBQ ACT2
INDEX (I_ACTIVITEIT_atype)), SORT (SUBQ DLN2 INDEX (I_GRP_DEELNAME_zorg))))

Adapted plan:
PLAN JOIN (DLN INDEX (I_GRP_DEELNAME_groep), MERGE (SORT (SUBQ ACT2
INDEX (I_ACTIVITEIT_atype)), SORT (SUBQ DLN2 INDEX (I_GRP_DEELNAME_zorg))))

4 rows fetched (16 ms)

Result:
GRPDLN_ID ZORG_ID ATYPE_ID
79742 Null Null
115430 Null Null
117854 1021754 893
121888 1024286 893

(first row has unexplained Null values)

=>