xiedeyantu commented on code in PR #4748:
URL: https://github.com/apache/calcite/pull/4748#discussion_r2702312875
##########
core/src/test/resources/sql/sub-query.iq:
##########
@@ -7649,4 +7649,90 @@ EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0])
!ok
+# [CALCITE-7378] Potential incorrect column attribution in RelToSqlConverter
due to implicit table alias handling
+!use blank
+CREATE TABLE a (
+ deptno INTEGER NOT NULL,
+ ename VARCHAR(10)
+);
+(0 rows modified)
+
+!update
+
+CREATE TABLE b (
+ deptno INTEGER NOT NULL,
+ ename VARCHAR(10)
+);
+(0 rows modified)
+
+!update
+
+INSERT INTO a VALUES
+ (10, 'ALLEN'),
+ (20, 'WARD'),
+ (30, 'WARD'),
+ (40, 'SMITH');
+(4 rows modified)
+
+!update
+
+INSERT INTO b VALUES
+ (20, 'WARD'),
+ (30, 'WARD'),
+ (30, 'ALLEN'),
+ (10, 'KING');
+(4 rows modified)
+
+!update
+
+SELECT deptno
+FROM b as b1
+WHERE deptno NOT IN (
+ SELECT deptno
+ FROM a
+ WHERE deptno = b1.deptno AND ename = 'WARD' AND b1.ename = 'WARD'
+);
++--------+
+| DEPTNO |
++--------+
+| 10 |
+| 30 |
++--------+
+(2 rows)
+
+!ok
+
+SELECT deptno
+FROM b as a
+WHERE deptno NOT IN (
+ SELECT deptno
+ FROM a
+ WHERE deptno = a.deptno AND ename = 'WARD' AND a.ename = 'WARD'
+);
++--------+
+| DEPTNO |
++--------+
+| 10 |
++--------+
+(1 row)
+
+!ok
+
+SELECT deptno
+FROM b as a
+WHERE deptno NOT IN (
+ SELECT deptno
+ FROM a as a2
+ WHERE deptno = a.deptno AND ename = 'WARD' AND a.ename = 'WARD'
+);
++--------+
+| DEPTNO |
++--------+
+| 10 |
+| 30 |
++--------+g
Review Comment:
There is an extra 'g' at the end of the line.
##########
core/src/test/resources/sql/sub-query.iq:
##########
@@ -7649,4 +7649,90 @@ EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0])
!ok
+# [CALCITE-7378] Potential incorrect column attribution in RelToSqlConverter
due to implicit table alias handling
+!use blank
Review Comment:
I think it is better to be moved to blank.iq
##########
core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java:
##########
@@ -6426,6 +6426,55 @@ private void checkLiteral2(String expression, String
expected) {
sql(query).withConfig(c -> c.withExpand(false)).ok(expected);
}
+ /** Test cases of
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-7378";>[CALCITE-7378]
+ * Potential incorrect column attribution in RelToSqlConverter due to
implicit
+ * table alias handling</a>. */
+ @Test void testColumnAttributionWithImplicitAlias1() {
+ String query = "select \"product_name\" from \"product\" t1 "
+ + "where \"product_id\" not in (select \"product_id\" "
+ + "from \"product\" t2 "
+ + "where t2.\"product_id\" = t1.\"product_id\" "
+ + "and t1.\"product_id\" = 2 and t2.\"product_id\" = 1)";
+ String expected = "SELECT \"product_name\"\n"
+ + "FROM \"foodmart\".\"product\"\n"
+ + "WHERE \"product_id\" NOT IN (SELECT \"product_id\"\n"
+ + "FROM \"foodmart\".\"product\" AS \"product0\"\n"
+ + "WHERE \"product_id\" = \"product\".\"product_id\" "
+ + "AND \"product\".\"product_id\" = 2 AND \"product_id\" = 1)";
+ sql(query).withConfig(c -> c.withExpand(false)).ok(expected);
+ }
+
+ @Test void testColumnAttributionWithImplicitAlias2() {
Review Comment:
I don't think we need to add tests numbered 2 and 3 here. These are actually
duplicates of 1.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
