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

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.

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • Best Answer
    May 12, 2017 at 10:05 AM

    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.

    Add comment
    10|10000 characters needed characters exceeded

  • May 11, 2017 at 07:44 PM

    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Ü'
    Add comment
    10|10000 characters needed characters exceeded

  • May 12, 2017 at 07:27 AM

    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 "=".

    Add comment
    10|10000 characters needed characters exceeded

Skip to Content