Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

scoping issue of functions and with #2058

Closed
monetdb-team opened this issue Nov 30, 2020 · 0 comments
Closed

scoping issue of functions and with #2058

monetdb-team opened this issue Nov 30, 2020 · 0 comments

Comments

@monetdb-team
Copy link

@monetdb-team monetdb-team commented Nov 30, 2020

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.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Linked pull requests

Successfully merging a pull request may close this issue.

None yet
1 participant