Date: 2012-09-14 13:51:07 +0200
From: @grobian
To: SQL devs <>
Version: -- development
CC: akkaran046

Last updated: 2018-12-31 07:11:19 +0100

Comment 17737

Date: 2012-09-14 13:51:07 +0200
From: @grobian

This is on current Jul2012 (thus post -SP2)

After 70aff6bf7838 Test_JdbcClient fails to report some constraints for a table produced. This appears to be due to a rewrite of simple LIKE expressions to equality expressions. The JDBC call (DatabaseMetaData.getImportedKeys()) indeed uses LIKE expressions to match schema and table name.

Ever since this commit, those expressions are rewritten to simple equality tests, which I believe triggers a bug further on, resulting in incorrectly no result rows.

The problem is nicely illustrated by the following queries. The changes are at the end of the query, there schema and table name are matched using like, equality, and with/without pattern (to avoid triggering the rewrite):

SELECT 'squirrel' AS "PKTABLE_CAT", 'squirrel' AS "FKTABLE_CAT", "pkschema"."name" AS "PKTABLE_SCHEM", "pktable"."name" AS "PKTABLE_NAME", "pkkeycol"."name" AS "PKCOLUMN_NAME", "fkschema"."name" AS "FKTABLE_SCHEM", "fktable"."name" AS "FKTABLE_NAME", "fkkeycol"."name" AS "FKCOLUMN_NAME", "pkkeycol"."nr" AS "KEY_SEQ", 3 AS "UPDATE_RULE", 3 AS "DELETE_RULE", "fkkey"."name" AS "FK_NAME", "pkkey"."name" AS "PK_NAME", 7 AS "DEFERRABILITY" FROM "sys"."keys" AS "fkkey", "sys"."keys" AS "pkkey", "sys". "objects" AS "fkkeycol", "sys"."objects" AS "pkkeycol", "sys"."tables" AS "fktable", "sys"."tables" AS "pktable", "sys"."schemas" AS "fkschema", "sys". "schemas" AS "pkschema" WHERE "fktable"."id" = "fkkey"."table_id" AND "pktable"."id" = "pkkey"."table_id" AND "fkkey"."id" = "fkkeycol"."id" AND "pkkey"."id" = "pkkeycol"."id" AND "fkschema"."id" = "fktable"."schema_id" AND "pkschema"."id" = "pktable"."schema_id" AND "fkkey"."rkey" > -1 AND "fkkey"."rkey" = "pkkey". "id" AND "fkkeycol"."nr" = "pkkeycol"."nr" AND LOWER("fkschema"."name") LIKE 'sys' AND LOWER("fktable"."name") LIKE 'foreign' ORDER BY "PKTABLE_CAT", "PKTABLE_SCHEM", "PKTABLE_NAME", "PK_NAME", "KEY_SEQ";
+-------------+-------------+---------------+--------------+---------------+
| PKTABLE_CAT | FKTABLE_CAT | PKTABLE_SCHEM | PKTABLE_NAME | PKCOLUMN_NAME |>
+=============+=============+===============+==============+===============+
+-------------+-------------+---------------+--------------+---------------+
0 tuples (5.495ms) !9 columns dropped!

SELECT 'squirrel' AS "PKTABLE_CAT", 'squirrel' AS "FKTABLE_CAT", "pkschema"."name" AS "PKTABLE_SCHEM", "pktable"."name" AS "PKTABLE_NAME", "pkkeycol"."name" AS "PKCOLUMN_NAME", "fkschema"."name" AS "FKTABLE_SCHEM", "fktable"."name" AS "FKTABLE_NAME", "fkkeycol"."name" AS "FKCOLUMN_NAME", "pkkeycol"."nr" AS "KEY_SEQ", 3 AS "UPDATE_RULE", 3 AS "DELETE_RULE", "fkkey"."name" AS "FK_NAME", "pkkey"."name" AS "PK_NAME", 7 AS "DEFERRABILITY" FROM "sys"."keys" AS "fkkey", "sys"."keys" AS "pkkey", "sys". "objects" AS "fkkeycol", "sys"."objects" AS "pkkeycol", "sys"."tables" AS "fktable", "sys"."tables" AS "pktable", "sys"."schemas" AS "fkschema", "sys". "schemas" AS "pkschema" WHERE "fktable"."id" = "fkkey"."table_id" AND "pktable"."id" = "pkkey"."table_id" AND "fkkey"."id" = "fkkeycol"."id" AND "pkkey"."id" = "pkkeycol"."id" AND "fkschema"."id" = "fktable"."schema_id" AND "pkschema"."id" = "pktable"."schema_id" AND "fkkey"."rkey" > -1 AND "fkkey"."rkey" = "pkkey". "id" AND "fkkeycol"."nr" = "pkkeycol"."nr" AND LOWER("fkschema"."name") = 'sys' AND LOWER("fktable"."name") = 'foreign' ORDER BY "PKTABLE_CAT", "PKTABLE_SCHEM", "PKTABLE_NAME", "PK_NAME", "KEY_SEQ";
+-------------+-------------+---------------+--------------+---------------+
| PKTABLE_CAT | FKTABLE_CAT | PKTABLE_SCHEM | PKTABLE_NAME | PKCOLUMN_NAME |>
+=============+=============+===============+==============+===============+
+-------------+-------------+---------------+--------------+---------------+
0 tuples (10.375ms) !9 columns dropped!

SELECT 'squirrel' AS "PKTABLE_CAT", 'squirrel' AS "FKTABLE_CAT", "pkschema"."name" AS "PKTABLE_SCHEM", "pktable"."name" AS "PKTABLE_NAME", "pkkeycol"."name" AS "PKCOLUMN_NAME", "fkschema"."name" AS "FKTABLE_SCHEM", "fktable"."name" AS "FKTABLE_NAME", "fkkeycol"."name" AS "FKCOLUMN_NAME", "pkkeycol"."nr" AS "KEY_SEQ", 3 AS "UPDATE_RULE", 3 AS "DELETE_RULE", "fkkey"."name" AS "FK_NAME", "pkkey"."name" AS "PK_NAME", 7 AS "DEFERRABILITY" FROM "sys"."keys" AS "fkkey", "sys"."keys" AS "pkkey", "sys". "objects" AS "fkkeycol", "sys"."objects" AS "pkkeycol", "sys"."tables" AS "fktable", "sys"."tables" AS "pktable", "sys"."schemas" AS "fkschema", "sys". "schemas" AS "pkschema" WHERE "fktable"."id" = "fkkey"."table_id" AND "pktable"."id" = "pkkey"."table_id" AND "fkkey"."id" = "fkkeycol"."id" AND "pkkey"."id" = "pkkeycol"."id" AND "fkschema"."id" = "fktable"."schema_id" AND "pkschema"."id" = "pktable"."schema_id" AND "fkkey"."rkey" > -1 AND "fkkey"."rkey" = "pkkey". "id" AND "fkkeycol"."nr" = "pkkeycol"."nr" AND LOWER("fkschema"."name") like 'sys%' AND LOWER("fktable"."name") like 'foreign%' ORDER BY "PKTABLE_CAT", "PKTABLE_SCHEM", "PKTABLE_NAME", "PK_NAME", "KEY_SEQ";
+----------+----------+-------+---------------+------------+-------+----------+
| PKTABLE_ | FKTABLE_ | PKTAB | PKTABLE_NAME | PKCOLUMN_N | FKTAB | FKTABLE_ |>
: CAT : CAT : LE_SC : : AME : LE_SC : NAME :>
: : : HEM : : : HEM : :>
+==========+==========+=======+===============+============+=======+==========+
| squirrel | squirrel | sys | allnewtriples | id | sys | foreign |
| squirrel | squirrel | sys | allnewtriples | subject | sys | foreign |
| squirrel | squirrel | sys | allnewtriples | predicate | sys | foreign |
| squirrel | squirrel | sys | allnewtriples | object | sys | foreign |
+----------+----------+-------+---------------+------------+-------+----------+
4 tuples (16.465ms) !7 columns dropped!

SELECT 'squirrel' AS "PKTABLE_CAT", 'squirrel' AS "FKTABLE_CAT", "pkschema"."name" AS "PKTABLE_SCHEM", "pktable"."name" AS "PKTABLE_NAME", "pkkeycol"."name" AS "PKCOLUMN_NAME", "fkschema"."name" AS "FKTABLE_SCHEM", "fktable"."name" AS "FKTABLE_NAME", "fkkeycol"."name" AS "FKCOLUMN_NAME", "pkkeycol"."nr" AS "KEY_SEQ", 3 AS "UPDATE_RULE", 3 AS "DELETE_RULE", "fkkey"."name" AS "FK_NAME", "pkkey"."name" AS "PK_NAME", 7 AS "DEFERRABILITY" FROM "sys"."keys" AS "fkkey", "sys"."keys" AS "pkkey", "sys". "objects" AS "fkkeycol", "sys"."objects" AS "pkkeycol", "sys"."tables" AS "fktable", "sys"."tables" AS "pktable", "sys"."schemas" AS "fkschema", "sys". "schemas" AS "pkschema" WHERE "fktable"."id" = "fkkey"."table_id" AND "pktable"."id" = "pkkey"."table_id" AND "fkkey"."id" = "fkkeycol"."id" AND "pkkey"."id" = "pkkeycol"."id" AND "fkschema"."id" = "fktable"."schema_id" AND "pkschema"."id" = "pktable"."schema_id" AND "fkkey"."rkey" > -1 AND "fkkey"."rkey" = "pkkey". "id" AND "fkkeycol"."nr" = "pkkeycol"."nr" AND LOWER("fkschema"."name") like 'sys%' AND LOWER("fktable"."name") like 'foreign' ORDER BY "PKTABLE_CAT", "PKTABLE_SCHEM", "PKTABLE_NAME", "PK_NAME", "KEY_SEQ";
+----------+----------+-------+---------------+------------+-------+----------+
| PKTABLE_ | FKTABLE_ | PKTAB | PKTABLE_NAME | PKCOLUMN_N | FKTAB | FKTABLE_ |>
: CAT : CAT : LE_SC : : AME : LE_SC : NAME :>
: : : HEM : : : HEM : :>
+==========+==========+=======+===============+============+=======+==========+
| squirrel | squirrel | sys | allnewtriples | id | sys | foreign |
| squirrel | squirrel | sys | allnewtriples | subject | sys | foreign |
| squirrel | squirrel | sys | allnewtriples | predicate | sys | foreign |
| squirrel | squirrel | sys | allnewtriples | object | sys | foreign |
+----------+----------+-------+---------------+------------+-------+----------+
4 tuples (11.194ms) !7 columns dropped!

SELECT 'squirrel' AS "PKTABLE_CAT", 'squirrel' AS "FKTABLE_CAT", "pkschema"."name" AS "PKTABLE_SCHEM", "pktable"."name" AS "PKTABLE_NAME", "pkkeycol"."name" AS "PKCOLUMN_NAME", "fkschema"."name" AS "FKTABLE_SCHEM", "fktable"."name" AS "FKTABLE_NAME", "fkkeycol"."name" AS "FKCOLUMN_NAME", "pkkeycol"."nr" AS "KEY_SEQ", 3 AS "UPDATE_RULE", 3 AS "DELETE_RULE", "fkkey"."name" AS "FK_NAME", "pkkey"."name" AS "PK_NAME", 7 AS "DEFERRABILITY" FROM "sys"."keys" AS "fkkey", "sys"."keys" AS "pkkey", "sys". "objects" AS "fkkeycol", "sys"."objects" AS "pkkeycol", "sys"."tables" AS "fktable", "sys"."tables" AS "pktable", "sys"."schemas" AS "fkschema", "sys". "schemas" AS "pkschema" WHERE "fktable"."id" = "fkkey"."table_id" AND "pktable"."id" = "pkkey"."table_id" AND "fkkey"."id" = "fkkeycol"."id" AND "pkkey"."id" = "pkkeycol"."id" AND "fkschema"."id" = "fktable"."schema_id" AND "pkschema"."id" = "pktable"."schema_id" AND "fkkey"."rkey" > -1 AND "fkkey"."rkey" = "pkkey". "id" AND "fkkeycol"."nr" = "pkkeycol"."nr" AND LOWER("fkschema"."name") like 'sys' AND LOWER("fktable"."name") like 'foreign%' ORDER BY "PKTABLE_CAT", "PKTABLE_SCHEM", "PKTABLE_NAME", "PK_NAME", "KEY_SEQ";
+-------------+-------------+---------------+--------------+---------------+
| PKTABLE_CAT | FKTABLE_CAT | PKTABLE_SCHEM | PKTABLE_NAME | PKCOLUMN_NAME |>
+=============+=============+===============+==============+===============+
+-------------+-------------+---------------+--------------+---------------+
0 tuples (7.637ms) !9 columns dropped!

SELECT 'squirrel' AS "PKTABLE_CAT", 'squirrel' AS "FKTABLE_CAT", "pkschema"."name" AS "PKTABLE_SCHEM", "pktable"."name" AS "PKTABLE_NAME", "pkkeycol"."name" AS "PKCOLUMN_NAME", '|'||"fkschema"."name"||'|' AS "FKTABLE_SCHEM", "fktable"."name" AS "FKTABLE_NAME", "fkkeycol"."name" AS "FKCOLUMN_NAME", "pkkeycol"."nr" AS "KEY_SEQ", 3 AS "UPDATE_RULE", 3 AS "DELETE_RULE", "fkkey"."name" AS "FK_NAME", "pkkey"."name" AS "PK_NAME", 7 AS "DEFERRABILITY" FROM "sys"."keys" AS "fkkey", "sys"."keys" AS "pkkey", "sys". "objects" AS "fkkeycol", "sys"."objects" AS "pkkeycol", "sys"."tables" AS "fktable", "sys"."tables" AS "pktable", "sys"."schemas" AS "fkschema", "sys". "schemas" AS "pkschema" WHERE "fktable"."id" = "fkkey"."table_id" AND "pktable"."id" = "pkkey"."table_id" AND "fkkey"."id" = "fkkeycol"."id" AND "pkkey"."id" = "pkkeycol"."id" AND "fkschema"."id" = "fktable"."schema_id" AND "pkschema"."id" = "pktable"."schema_id" AND "fkkey"."rkey" > -1 AND "fkkey"."rkey" = "pkkey". "id" AND "fkkeycol"."nr" = "pkkeycol"."nr" AND LOWER("fkschema"."name") like 'sys%' AND LOWER("fktable"."name") like 'foreign' ORDER BY "PKTABLE_CAT", "PKTABLE_SCHEM", "PKTABLE_NAME", "PK_NAME", "KEY_SEQ";
+----------+----------+------+---------------+------------+--------+----------+
| PKTABLE_ | FKTABLE_ | PKTA | PKTABLE_NAME | PKCOLUMN_N | FKTABL | FKTABLE_ |>
: CAT : CAT : BLE_ : : AME : E_SCHE : NAME :>
: : : SCHE : : : M : :>
: : : M : : : : :>
+==========+==========+======+===============+============+========+==========+
| squirrel | squirrel | sys | allnewtriples | id | |sys| | foreign |
| squirrel | squirrel | sys | allnewtriples | subject | |sys| | foreign |
| squirrel | squirrel | sys | allnewtriples | predicate | |sys| | foreign |
| squirrel | squirrel | sys | allnewtriples | object | |sys| | foreign |
+----------+----------+------+---------------+------------+--------+----------+
4 tuples (9.797ms) !7 columns dropped!

So it seems the first LIKE expression must stay like, or some further rewriting/optimisation will kill the plan.

It seems 70aff6bf7838 triggers a bug that was in the code for long to be visible now. No test needed, since Test_JdbcClient fails for this reason.

Comment 17738

Date: 2012-09-14 14:07:47 +0200
From: @grobian

Niels, we would like to see this fixed ASAP, if possible, since it blocks Jul2012-SP2.

Comment 17739

Date: 2012-09-17 09:54:04 +0200
From: @sjoerdmullender

Fixed by Niels in changeset f1c7f64f461e

Comment 26766

Date: 2018-12-31 07:11:19 +0100
From: martin garix <>

Sometimes facing this problem,and users asked how do i find my computer in windows 10 http://mycomputerwindows10.com here the one of the best simple perfected working ways,now using this.