Another "Umlaut" bug in ADS (verified on 10.1, 11.1 and 12)

[jhoehne]

Giving this two tables:

Create Table TableA( TAFIELD1 Char( 5 ), TAFIELD2 CIChar( 50 ), TAFIELD3 Money );
INSERT INTO "TableA" VALUES( 'AAAAA', 'First Line', 12.33 );
INSERT INTO "TableA" VALUES( 'CCCCC', 'Second Line', 432.33 );
INSERT INTO "TableA" VALUES( 'XXXXÜ', 'Third Line', 1 );

Create Table TableB( TBFIELD1 Char( 5 ), TBFIELD2 CIChar( 20 ), TBFIELD3 CIChar( 10 ), TBFIELD4 CIChar( 20 ) );
INSERT INTO "TableB" VALUES( 'AAAAA', 'An der Brücke 1', '12345', 'Teststadt' );
INSERT INTO "TableB" VALUES( 'BBBBB', 'Hauptgasse 64', '54321', 'Wicheln' );
INSERT INTO "TableB" VALUES( 'XXXXÜ', 'Lange Straße 210', '12345', 'Teststadt' );

the query

SELECT ta.*, tb.*
FROM TableA ta INNER JOIN TableB tb ON ta.TaField1 = tb.TbField1
WHERE ta.TaField1 = 'AAAAA'

retrieves one row, as expected. But if we query

SELECT ta.*, tb.*
FROM TableA ta INNER JOIN TableB tb ON ta.TaField1 = tb.TbField1
WHERE ta.TaField1 = 'XXXXÜ'

we get zero rows!

We can avoid this bug using a nested query

SELECT x.* FROM (
SELECT ta.*, tb.*
FROM TableA ta INNER JOIN TableB tb ON ta.TaField1 = tb.TbField1
) x WHERE x.TaField1 = 'XXXXÜ'

which results in one row.

[joachim_drr · Accepted solution]

LIKE respects the collation where = uses a byte-by-byte comparison - which might fail depending on local settings;) NCHAR is Unicode and so it knwos about the special characters.

[jhoehne]

Nice 🙂

This looks somewhat official ("never use special characters in JOIN fields"), but I didn't find it in the docs.

P.S.: if I change the type of the JOIN field to "NChar" it works with "=".

[joachim_drr]

It works if your use LIKE instead of =

SELECT ta.*, tb.*
FROM TableA ta INNER JOIN TableB tb ON ta.TaField1 = tb.TbField1
WHERE ta.TaField1 LIKE 'XXXXÜ'