SQL generated for Contains may not work as expected [DNET466]

[firebird-issue-importer]

Submitted by: Dave (davek)

Relate to DNET513

I have a table :

create table SearchTest ( val varchar(20) );

which has two rows:

insert into SearchTest ( val ) values ('one');
insert into SearchTest ( val ) values ('three');

When I try to select all rows where column 'val' contains either 'one' or 'hree':

var a = from b in TestEntities.SEARCHTESTs
from c in new []{ "one", "hree" }
where b.VAL.Contains(c)
select b;

I get a query like this:

SELECT
"C"."VAL" AS "VAL"
FROM "SEARCHTEST" AS "C"
CROSS JOIN (SELECT
_UTF8 X'4F4E45' AS "C1"
FROM ( SELECT 1 AS X FROM RDB$DATABASE) AS "D"
UNION ALL
SELECT
_UTF8 X'48524545' AS "C1"
FROM ( SELECT 1 AS X FROM RDB$DATABASE) AS "E") AS "F"
WHERE ((POSITION("F"."C1", "C"."VAL")) > 0)

The result of this query does not include the row containing 'one' because "F"."C1" is of type CHAR(4), and cannot match a three character value.

CASTing the literals to VARCHAR results in the expected behavior.

Commits: 30d4902

[firebird-issue-importer]

Modified by: @cincuranet

status: Open [ 1 ] => In Progress [ 3 ]

[firebird-issue-importer]

Commented by: @cincuranet

Fixed. String constants are now converted to varchars explicitly.

[firebird-issue-importer]

Modified by: @cincuranet

status: In Progress [ 3 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

Fix Version: vNext [ 10491 ]

[firebird-issue-importer]

Modified by: @cincuranet

Link: This issue relate to DNET513 [ DNET513 ]