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

LEFT/RIGHT OUTER JOIN failed to detect ambiguous column reference (SQLancer-NoREC) #11408

Closed
2010YOUY01 opened this issue Jul 11, 2024 · 3 comments · Fixed by #12608
Closed
Labels
bug Something isn't working

Comments

@2010YOUY01
Copy link
Contributor

Describe the bug

Table reference in this query is ambiguous, and should be rejected by the planner.
select t1.v1 from t1 left outer join t1 on true;
Now it can be run successfully, see the reproducer:
(It can also be reproduced if 'left outer join' is changed to 'left join' 'right join' 'right outer join')

> create table t1(v1 int);
0 row(s) fetched.
Elapsed 0.068 seconds.

> insert into t1 values (1);
+-------+
| count |
+-------+
| 1     |
+-------+
1 row(s) fetched.
Elapsed 0.054 seconds.

> select t1.v1 from t1 left outer join t1 on true;
+----+
| v1 |
+----+
| 1  |
+----+
1 row(s) fetched.
Elapsed 0.016 seconds.

> select v1 from t1 left outer join t1 on true;
Schema error: Ambiguous reference to unqualified field v1

To Reproduce

No response

Expected behavior

No response

Additional context

found by SQLancer #11030

@2010YOUY01 2010YOUY01 added the bug Something isn't working label Jul 11, 2024
@2010YOUY01
Copy link
Contributor Author

Note to myself: maybe duplicate

NoREC oracle violated:
    Q1(result size 0):SELECT COUNT(*) FROM t2 table1 FULL JOIN t2 table1 ON ((table1.v1)<(((((table1.v2)|(-1680394990)))==(RADIANS(NULL))))) WHERE table1.v3;
    Q2(result size 3):SELECT COUNT(CASE WHEN TABLE1.V3 THEN 1 ELSE NULL END) FROM t2 table1 FULL JOIN t2 AS table1 ON ((table1.v1)<(((((table1.v2)|(-1680394990)))==(RADIANS(NULL)))));
=======================================
Reproducer:
/*DML*/CREATE TABLE t0(v0 BIGINT, v1 BOOLEAN, v2 BOOLEAN, v3 DOUBLE);
/*DML*/CREATE TABLE t1(v0 DOUBLE);
/*DML*/CREATE TABLE t2(v0 DOUBLE, v1 BOOLEAN, v2 BIGINT, v3 BOOLEAN);
/*DML*/INSERT INTO t0(v3, v0, v2) VALUES ('NaN'::Double, 462373142, false);
/*DML*/INSERT INTO t0(v0, v2) VALUES (462373142, NULL);
/*DML*/INSERT INTO t1(v0) VALUES (0.23090038573154326);
/*DML*/INSERT INTO t1(v0) VALUES ('+Inf'::Double), ('+Inf'::Double);
/*DML*/INSERT INTO t1(v0) VALUES ('+Inf'::Double);
/*DML*/INSERT INTO t2(v2) VALUES (673041184);
/*DML*/INSERT INTO t2(v2, v3) VALUES (212832748, false);
/*DML*/INSERT INTO t2(v3, v0, v2) VALUES (true, 0.9397291016687526, 462373142);
/*DML*/INSERT INTO t2(v1, v2, v0, v3) VALUES (false, 462373142, 0.6388418793650567, true);
/*DML*/INSERT INTO t2(v3, v0) VALUES (true, 0.6388418793650567);
/*DML*/INSERT INTO t2(v0, v2, v3) VALUES (0.31756635573567127, 104424209, false);

@jonahgao
Copy link
Member

Might be related to #11464

@aalexandrov
Copy link
Contributor

aalexandrov commented Jul 16, 2024

Not sure if this is should be classified as a bug or just as an error message that could be improved.

I tested on Postgres, MySQL, and SQL Server and all fail because you cannot have duplicate table names in the FROM list.

I believe the proper fix here is forcing the

select t1.v1 from t1 left outer join t1 on true;

variant to return the same error as

select v1 from t1 left outer join t1 on true;

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
bug Something isn't working
Projects
None yet
Development

Successfully merging a pull request may close this issue.

3 participants