[FRG-230] validator can't deal with duplicate column aliases in nested selects

[dynamobi-build]

[reporter="jvs", created="Wed, 1 Nov 2006 00:17:47 -0500 (GMT-05:00)"] This case is mostly of academic interest (only managed to hit it because of a sampling SQL generator which sticks unnecessary select * on top of things).

select * from (select empno as x, deptno as x from sales.emps);

Both columns come back with the same results, which is incorrect. But

select empno as x, deptno as x from sales.emps;

does fine. The problem is that when SqlValidator expands the select *, it needs to make up SqlIdentifiers to reference the inner columns, but there's no way to do that unambiguously. However, there is no ambiguity at the SQL level (and SQL:2003 allows for duplicate column aliases as long as there is no ambiguity from the point of view of outer queries).

Rewrite the inner column aliases to make them unique, and fix up references? Yuck.

[dynamobi-build]

[author="jhyde", created="Wed, 1 Nov 2006 13:51:55 -0500 (GMT-05:00)"] I think this query is invalid. "select *" is expanded to "select x, x", and those "x" references are invalid. Do you agree?

[dynamobi-build]

[author="jvs", created="Wed, 1 Nov 2006 14:11:03 -0500 (GMT-05:00)"] I'm afraid it's probably valid.

SQL:2003 Part 2 Section 7.12 Syntax Rule 3.b:

 "Otherwise, the "*" is equivalent to a sequence in which each <value
 expression> is a column reference that references a column of T and each column of T is referenced
 exactly once. The columns are referenced in the ascending sequence of their ordinal position within T."

Since the expansion is by position rather than name, there's no ambiguity.

dynamobi-build commented 12 years ago

[author="jhyde", created="Wed, 1 Nov 2006 14:18:41 -0500 (GMT-05:00)"] So "" is equivalent to a which is itself invalid. I'd say that makes the whole expression invalid.

The fix should be to reject this statement as invalid.

FWIW, Oracle agrees with me:

Connected to:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

SQL> select * from (select empno as x, deptno as x from emp);
select * from (select empno as x, deptno as x from emp)
       
ERROR at line 1:
ORA-00918: column ambiguously defined

dynamobi-build commented 12 years ago

[author="jvs", created="Wed, 1 Nov 2006 14:49:38 -0500 (GMT-05:00)"] OK, I'll buy that.

dynamobi-build commented 12 years ago

[author="jvs", created="Fri, 8 Dec 2006 14:28:27 -0500 (GMT-05:00)"] Hmm, what do you make of this in Part 2 Section 6.6 Syntax Rule 8.a.ii.1.A:

"NOTE 96 -- Two or more columns with equivalent column names are distinguished by their ordinal positions
within T."

dynamobi-build commented 12 years ago

[author="jhyde", created="Sat, 9 Dec 2006 20:02:08 -0500 (GMT-05:00)"] OK, I concede that it's a genuine bug. (Assuming you didn't make that section up. :) No immediate plans to fix it.

• © Githubissues.
• Githubissues is a development platform for aggregating issues.

[dynamobi-build]

[author="jhyde", created="Wed, 1 Nov 2006 14:18:41 -0500 (GMT-05:00)"] So "" is equivalent to a which is itself invalid. I'd say that makes the whole expression invalid.

The fix should be to reject this statement as invalid.

FWIW, Oracle agrees with me:

Connected to:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

SQL> select * from (select empno as x, deptno as x from emp);
select * from (select empno as x, deptno as x from emp)
       
ERROR at line 1:
ORA-00918: column ambiguously defined

[dynamobi-build]

[author="jvs", created="Wed, 1 Nov 2006 14:49:38 -0500 (GMT-05:00)"] OK, I'll buy that.

[dynamobi-build]

[author="jvs", created="Fri, 8 Dec 2006 14:28:27 -0500 (GMT-05:00)"] Hmm, what do you make of this in Part 2 Section 6.6 Syntax Rule 8.a.ii.1.A:

"NOTE 96 -- Two or more columns with equivalent column names are distinguished by their ordinal positions
within T."

[dynamobi-build]

[author="jhyde", created="Sat, 9 Dec 2006 20:02:08 -0500 (GMT-05:00)"] OK, I concede that it's a genuine bug. (Assuming you didn't make that section up. :) No immediate plans to fix it.