Hi,



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 result is correct again.
But I'm not confident that this bug won't exhibit in another situations
where the key-field is indeed a varchar field.



Anyone seen this before, or knows whether a bug-issue like this exists?



Kind regards,

Michiel



------------------------------------------------------------------------
---

-- 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 rows have unexplained Null values)



[Non-text portions of this message have been removed]