Date: 2011-11-01 15:19:33 +0100
From: @sjoerdmullender
To: SQL devs <>
Version: 11.5.7 (Aug2011-SP2) [obsolete]
CC: @njnes

Last updated: 2011-11-22 13:55:53 +0100

Comment 16503

Date: 2011-11-01 15:19:33 +0100
From: @sjoerdmullender

The following query is generated by the ODBC driver for the call to SQLTables(stmt, NULL, 0, NULL, 0, NULL, 0, "LOCAL TEMPORARY,GLOBAL TEMPORARY", SQL_NTS);

select e."value" as table_cat, s."name" as table_schem, t."name" as table_name, case when t."type" = 0 and t."system" = false and t."temporary" = 0 and s."name" <> 'tmp' then cast('TABLE' as varchar(20)) when t."type" = 0 and t."system" = false and t."temporary" = 0 and s."name" = 'tmp' then cast('GLOBAL TEMPORARY' as varchar(20)) when t."type" = 0 and t."system" = true and t."temporary" = 0 then cast('SYSTEM TABLE' as varchar(20)) when t."type" = 1 then cast('VIEW' as varchar(20)) when t."type" = 0 and t."system" = false and t."temporary" = 1 then cast('LOCAL TEMPORARY' as varchar(20)) else cast('INTERNAL TABLE TYPE' as varchar(20)) end as table_type, cast(null as varchar(1)) as remarks from sys."schemas" s, sys."tables" t, sys."env"() e where s."id" = t."schema_id" and e.name = 'gdk_dbname' and ((t."type" = 0 and t."system" = false and t."temporary" = 0 and s."name" = 'tmp') or (t."type" = 0 and t."system" = false and t."temporary" = 1)) order by table_type, table_schem, table_name;

The problem is, this query only returns rows for LOCAL TEMPORARY tables. When the final (complex) AND clause is halved by using either one or the other part on either side of the OR, the query works as expected, returning either the local or global temporaries.

Initialize the database with:
create local temporary table loctemp (i int);
create global temporary table globtemp (i int);

Then try the above query. It should result in two rows but only produces one.

The following two queries both return a single row, the first for the global temporary, the second for the local temporary:
select e."value" as table_cat, s."name" as table_schem, t."name" as table_name, case when t."type" = 0 and t."system" = false and t."temporary" = 0 and s."name" <> 'tmp' then cast('TABLE' as varchar(20)) when t."type" = 0 and t."system" = false and t."temporary" = 0 and s."name" = 'tmp' then cast('GLOBAL TEMPORARY' as varchar(20)) when t."type" = 0 and t."system" = true and t."temporary" = 0 then cast('SYSTEM TABLE' as varchar(20)) when t."type" = 1 then cast('VIEW' as varchar(20)) when t."type" = 0 and t."system" = false and t."temporary" = 1 then cast('LOCAL TEMPORARY' as varchar(20)) else cast('INTERNAL TABLE TYPE' as varchar(20)) end as table_type, cast(null as varchar(1)) as remarks from sys."schemas" s, sys."tables" t, sys."env"() e where s."id" = t."schema_id" and e.name = 'gdk_dbname' and ((t."type" = 0 and t."system" = false and t."temporary" = 0 and s."name" = 'tmp')) order by table_type, table_schem, table_name;
select e."value" as table_cat, s."name" as table_schem, t."name" as table_name, case when t."type" = 0 and t."system" = false and t."temporary" = 0 and s."name" <> 'tmp' then cast('TABLE' as varchar(20)) when t."type" = 0 and t."system" = false and t."temporary" = 0 and s."name" = 'tmp' then cast('GLOBAL TEMPORARY' as varchar(20)) when t."type" = 0 and t."system" = true and t."temporary" = 0 then cast('SYSTEM TABLE' as varchar(20)) when t."type" = 1 then cast('VIEW' as varchar(20)) when t."type" = 0 and t."system" = false and t."temporary" = 1 then cast('LOCAL TEMPORARY' as varchar(20)) else cast('INTERNAL TABLE TYPE' as varchar(20)) end as table_type, cast(null as varchar(1)) as remarks from sys."schemas" s, sys."tables" t, sys."env"() e where s."id" = t."schema_id" and e.name = 'gdk_dbname' and ((t."type" = 0 and t."system" = false and t."temporary" = 1)) order by table_type, table_schem, table_name;

Comment 16504

Date: 2011-11-01 15:26:53 +0100
From: @sjoerdmullender

Changeset 8e893c3336de made by Sjoerd Mullender sjoerd@acm.org in the MonetDB repo, refers to this bug.

For complete details, see http//devmonetdborg/hg/MonetDB?cmd=changeset;node=8e893c3336de

Changeset description:

Test for bug #2921.

Comment 16514

Date: 2011-11-03 14:48:09 +0100
From: @njnes

fixed bug in handling 'in' and 'not in' properly for 'constant' left hands.

Comment 16515

Date: 2011-11-03 14:49:19 +0100
From: @njnes

Changeset f4eaa8850588 made by Niels Nes niels@cwi.nl in the MonetDB repo, refers to this bug.

For complete details, see http//devmonetdborg/hg/MonetDB?cmd=changeset;node=f4eaa8850588

Changeset description:

fixed problem with in lists with constants on the left hand side.
Fixes Bug #2921

Comment 16516

Date: 2011-11-03 20:25:33 +0100
From: @sjoerdmullender

I'm not sure it's the same problem, but these two queries should, I believe, give the same result, but they don't. The first one uses a very similar pattern as the original query in the bug report. I rewrote the UNION of SELECTs to a SELECT with an OR.

with sk as (select t.id as table_id, k.type as "type", c.name from sys."schemas" s, sys."tables" t, sys."columns" c, sys."keys" k, sys."objects" kc, sys."env"() e where s."id" = t."schema_id" and t."id" = c."table_id" and t."id" = k."table_id" and c."name" = kc."name" and kc."id" = k."id" and k."type" in (0, 1) and e."name" = 'gdk_dbname'), tid as (select t.id as tid from sys._tables t, sys.keys k where t.id = k.table_id and k.type = 0) select sk.* from sk where (sk.type = 0 and sk.table_id in (select tid from tid)) or (sk.type = 1 and sk.table_id not in (select tid from tid));

with sk as (select t.id as table_id, k.type as "type", c.name from sys."schemas" s, sys."tables" t, sys."columns" c, sys."keys" k, sys."objects" kc, sys."env"() e where s."id" = t."schema_id" and t."id" = c."table_id" and t."id" = k."table_id" and c."name" = kc."name" and kc."id" = k."id" and k."type" in (0, 1) and e."name" = 'gdk_dbname'), tid as (select t.id as tid from sys._tables t, sys.keys k where t.id = k.table_id and k.type = 0) select sk.* from sk where sk.type = 0 and sk.table_id in (select tid from tid) union select sk.* from sk where sk.type = 1 and sk.table_id not in (select tid from tid);

Comment 16519

Date: 2011-11-04 12:35:09 +0100
From: @njnes

this is a different problem. The problem is related to incorrect renaming of the internal column '%TID'.

Comment 16520

Date: 2011-11-04 13:03:05 +0100
From: @njnes

Changeset b2bfd42795eb made by Niels Nes niels@cwi.nl in the MonetDB repo, refers to this bug.

For complete details, see http//devmonetdborg/hg/MonetDB?cmd=changeset;node=b2bfd42795eb

Changeset description:

fix bug #2921 (part 2). Internal expressions shouldn't be renamed.

Comment 16563

Date: 2011-11-22 13:55:53 +0100
From: @grobian

Fixed in Aug2011-SP3