Date: 2009-04-24 01:52:47 +0200
From: @swingbit
To: SQL devs <>
Version: 11.3.3 (Apr2011-SP1) [obsolete]

Last updated: 2011-07-29 10:52:46 +0200

Comment 11615

Date: 2009-04-24 13:52:47 +0200
From: @swingbit

In my database I have a table named "tf" (very common in IR..)
When I try to dump the database, one of the queries uses "tf" as a name for a temporary relation, and this clashes with mine.
Probably a safer name should be used there..

sql>\D
MAPI = lhm@localhost:50151
QUERY = WITH "tf" AS (SELECT "s"."name" AS "sname", "f"."name" AS "name", "f"."id" AS "id", "f"."func" AS "func" FROM "sys"."schemas" "s", "sys"."functions" "f" WHERE "f"."sql" = TRUE AND "s"."id" = "f"."schema_id" UNION SELECT "s"."name" AS "sname", "t"."name" AS "name", "t"."id" AS "id", CAST(NULL AS VARCHAR(8196)) AS "func" FROM "sys"."schemas" "s", "sys"."_tables" "t" WHERE "t"."type" BETWEEN 0 AND 1 AND "t"."system" = FALSE AND "s"."id" = "t"."schema_id" UNION SELECT "s"."name" AS "sname", "tr"."name" AS "name", "tr"."id" AS "id", "tr"."statement" AS "func" FROM "sys"."triggers" "tr", "sys"."schemas" "s", "sys"."_tables" "t" WHERE "s"."id" = "t"."schema_id" AND "t"."id" = "tr"."table_id") SELECT * FROM "tf" ORDER BY "tf"."id"

Comment 11616

Date: 2009-04-24 16:03:18 +0200
From: @sjoerdmullender

Looks like a scoping problem.
This is very similar to having a function with a parameter name which happens to be the same as the name of a column of one of the tables that is used in the function. There too you get a complaint about ambiguous names.

I have solved the problem in dump for now by using a less likely name, but I think the scoping problem should be looked at before we close this bug.

Comment 11617

Date: 2009-04-25 19:19:54 +0200
From: @drstmane

Fixed by Sjoerd in MonetDB-clients as of Feb2009-SP2 release (candidate).

Test added in sql/src/test/BugTracker-2009/Tests/name_clash_with_dump.SF-2780395.sql

Comment 11618

Date: 2009-04-25 20:43:33 +0200
From: @sjoerdmullender

Reopened. I didn't "fix" the bug, I worked around it. First I want a pronouncement on the scoping issue, both in the with statement and in functions.

Comment 11619

Date: 2009-08-06 17:14:06 +0200
From: @sjoerdmullender

Niels, can you pronounce?

Comment 11620

Date: 2009-08-06 23:23:01 +0200
From: @njnes

indeed there is a bug in the scoping of functions and with.

Comment 11621

Date: 2010-05-04 09:32:09 +0200
From: Pseudo user for Sourceforge import <>

This bug was previously known as tracker item 2780395 at http://sourceforge.net/support/tracker.php?aid=2780395

Comment 14167

Date: 2010-07-01 16:16:48 +0200
From: @njnes

*** Bug #2059 has been marked as a duplicate of this bug. ***

Comment 15690

Date: 2011-03-28 17:36:34 +0200
From: @sjoerdmullender

The Mar2011 version has been released.

Comment 15947

Date: 2011-07-15 16:55:26 +0200
From: @sjoerdmullender

A lot has changed since this bug was originally opened, but it's still not right. Now we have:

sql>create table foo (i int);
operation successful
sql>with foo as (select * from tables) select * from foo;
+---+
| i |
+===+
+---+
0 tuples (1.529ms)

I would expect the select to return the contents of tables, not foo.

Comment 15948

Date: 2011-07-16 11:59:35 +0200
From: @njnes

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

Changeset description:

look in the 'with' scope before table scope (solves bug #2058).

Comment 15949

Date: 2011-07-17 10:16:58 +0200
From: @njnes

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

Changeset description:

look in the 'with' scope before table scope (solves bug #2058).
(transplanted from b86b9b374542300acf62fadc46465cdcb266b8be)

Comment 15950

Date: 2011-07-17 10:43:54 +0200
From: @sjoerdmullender

Changeset 36e9bbbf02ba 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=36e9bbbf02ba

Changeset description:

Added test for bug #2058.

Comment 15951

Date: 2011-07-17 10:45:21 +0200
From: @sjoerdmullender

Seems to be fixed.

Comment 16011

Date: 2011-07-29 10:52:46 +0200
From: @sjoerdmullender

The Apr2011-SP2 bugfix release is out.