missing table name with invalid column in join using (and problems after resolving it)

[monetdb-team]

Date: 2014-08-10 21:31:59 +0200
From: @skinkie
To: SQL devs <>
Version: 11.17.21 (Jan2014-SP3)
CC: @njnes

Last updated: 2014-10-31 14:14:38 +0100

Comment 20018

Date: 2014-08-10 21:31:59 +0200
From: @skinkie

User-Agent: Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/37.0.2062.58 Safari/537.36
Build Identifier:

I noticed when making a typo;

CREATE TABLE "sys"."test_a" (
"a" INTEGER,
"b" INTEGER
);

CREATE TABLE "sys"."test_b" (
"a" INTEGER,
"b" INTEGER
);

SELECT * FROM test_a JOIN test_b USING (c);
JOIN: tables '' and '' do not have a matching column 'c'

Thus I started fixing that based on the rel_dump.c code (patch attached).

sql>SELECT * FROM test_a JOIN test_b USING (c);
JOIN: tables 'test_a' and 'test_b' do not have a matching column 'c'

But something strange started hapening:

sql>\d test_a
CREATE TABLE "sys"."test_a" (
"a" INTEGER,
"b" INTEGER
);
MAPI = (monetdb) /tmp/.s.monetdb.50000
QUERY = SELECT i.name, k.name, kc.nr, c.name FROM sys.idxs AS i LEFT JOIN sys.keys AS k ON i.name = k.name, sys.objects AS kc, sys._columns AS c, sys.schemas s, sys._tables AS t WHERE i.table_id = t.id AND i.id = kc.id AND t.id = c.table_id AND kc.name = c.name AND (k.type IS NULL OR k.type = 1) AND t.schema_id = s.id AND s.name = 'sys' AND t.name = 'test_a' ORDER BY i.name, kc.nr
ERROR = !SELECT: no such column 'i.table_id'

Since the only change I made, outputing a string that would in the "original" case be casted to a NULL (r = NULL) I am quite confused.

Removing the "AS" in the JOIN gives me:

sql>SELECT idxs.name, keys.name, kc.nr, c.name FROM sys.idxs LEFT JOIN sys.keys ON idxs.name = keys.name, sys.objects AS kc, sys._columns AS c, sys.schemas s, sys._tables AS t WHERE idxs.table_id = t.id AND idxs.id = kc.id AND t.id = c.table_id AND kc.name = c.name AND (keys.type IS NULL OR keys.type = 1) AND t.schema_id = s.id AND s.name = 'sys' AND t.name = 'test_a' ORDER BY idxs.name, kc.nr;
+------+------+----+------+
| name | name | nr | name |
+======+======+====+======+
+------+------+----+------+
0 tuples (7.481ms)

places where rel_get_name is used:

exps = rel_projections(sql, t1, rel_get_name(t1), 1, 1)

In the "patched" case the rel_projections query looks:

Breakpoint 6, rel_projections (sql=0x7fd1901bd450, rel=0x7fd190129050, tname=0x7fd19000d3e0 "idxs", settname=1, intern=1) at rel_select.c:342
Breakpoint 6, rel_projections (sql=0x7fd1901bd450, rel=0x7fd1901293c0, tname=0x7fd19000cde0 "keys", settname=1, intern=1) at rel_select.c:342
Breakpoint 6, rel_projections (sql=0x7fd1901bd450, rel=0x7fd190129850, tname=0x0, settname=1, intern=1) at rel_select.c:342
Breakpoint 6, rel_projections (sql=0x7fd1901bd450, rel=0x7fd190129050, tname=0x0, settname=1, intern=1) at rel_select.c:342
Breakpoint 6, rel_projections (sql=0x7fd1901bd450, rel=0x7fd1901293c0, tname=0x0, settname=1, intern=1) at rel_select.c:342
Breakpoint 6, rel_projections (sql=0x7fd1901bd450, rel=0x7fd19012ee40, tname=0x0, settname=1, intern=1) at rel_select.c:342

The original case looks like:

Breakpoint 1, rel_projections (sql=0x7fa230005450, rel=0x7fa2300c4250, tname=0x0, settname=1, intern=1) at rel_select.c:342
Breakpoint 1, rel_projections (sql=0x7fa230005450, rel=0x7fa2300c45c0, tname=0x0, settname=1, intern=1) at rel_select.c:342
Breakpoint 1, rel_projections (sql=0x7fa230005450, rel=0x7fa2300c4a50, tname=0x0, settname=1, intern=1) at rel_select.c:342
Breakpoint 1, rel_projections (sql=0x7fa230005450, rel=0x7fa2300c4250, tname=0x0, settname=1, intern=1) at rel_select.c:342
Breakpoint 1, rel_projections (sql=0x7fa230005450, rel=0x7fa2300c45c0, tname=0x0, settname=1, intern=1) at rel_select.c:342

Is the patch correct, which uncovers a bug? Or is the patch incorrect and NULL is correct?

Reproducible: Always

MonetDB 5 server v11.17.22 (64-bit, 64-bit oids)
This is an unreleased version
Copyright (c) 1993-July 2008 CWI
Copyright (c) August 2008-2014 MonetDB B.V., all rights reserved
Visit http://www.monetdb.org/ for further information
Found 31.4GiB available memory, 8 available cpu cores
Libraries:
libpcre: 8.33 2013-05-28 (compiled with 8.33)
openssl: OpenSSL 1.0.1h 5 Jun 2014 (compiled with OpenSSL 1.0.1h 5 Jun 2014)
libxml2: 2.9.1 (compiled with 2.9.1)
Compiled by: skinkie@bigdata.openebs.nl (x86_64-unknown-linux-gnu)
Compilation: gcc -g -Werror -Wall -Wextra -W -Werror-implicit-function-declaration -Wpointer-arith -Wdeclaration-after-statement -Wundef -Wformat=2 -Wno-format-nonliteral -Winit-self -Winvalid-pch -Wmissing-declarations -Wmissing-format-attribute -Wmissing-prototypes -Wold-style-definition -Wpacked -Wunknown-pragmas -Wvariadic-macros -fstack-protector-all -Wstack-protector -Wpacked-bitfield-compat -Wsync-nand -Wjump-misses-init -Wmissing-include-dirs -Wlogical-op -Wunreachable-code
Linking : /usr/x86_64-pc-linux-gnu/bin/ld -m elf_x86_64

Comment 20019

Date: 2014-08-10 21:34:32 +0200
From: @skinkie

Created attachment 294
Patch which fixes the error message.

Errors can be triggered using:

\d test_a

Attached file: invalid-using-3534.diff (text/plain, 451 bytes)
Description: Patch which fixes the error message.

Comment 20022

Date: 2014-08-11 15:41:14 +0200
From: @njnes

fixed by using rel_name for the error message, the semantics for rel_get_name are needed for the projections

Comment 20023

Date: 2014-08-11 15:42:35 +0200
From: MonetDB Mercurial Repository <>

Changeset fecd6435c289 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=fecd6435c289

Changeset description:

fixed bug #3534, ie give a proper error message

Comment 20024

Date: 2014-08-11 15:46:47 +0200
From: @skinkie

(In reply to comment 2)

fixed by using rel_name for the error message, the semantics for
rel_get_name are needed for the projections

what does rel->r mean in the context of op_basetable?

Comment 20026

Date: 2014-08-11 17:21:39 +0200
From: @skinkie

http//devmonetdborg/hg/MonetDB?cmd=changeset;node=099546ee941f

Suggests nothing :) Question answered.

Comment 20384

Date: 2014-10-31 14:14:38 +0100
From: @sjoerdmullender

Oct2014 has been released.