This is an automated email from the ASF dual-hosted git repository. jhyde pushed a commit to branch main in repository https://gitbox.apache.org/repos/asf/calcite.git
commit 372346f3981cd90d28181376c3da4a1dafeb7e78 Author: xiejiajun <[email protected]> AuthorDate: Sun Apr 30 14:09:49 2023 +0800 [CALCITE-5547] JOIN USING returns incorrect column names Close apache/calcite#3183 --- .../calcite/sql/validate/SqlValidatorImpl.java | 21 ++++++- .../org/apache/calcite/test/SqlValidatorTest.java | 36 +++++++++++ core/src/test/resources/sql/join.iq | 71 ++++++++++++++++++++++ 3 files changed, 127 insertions(+), 1 deletion(-) diff --git a/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java b/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java index b53b8c8023..81ce2d7178 100644 --- a/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java +++ b/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java @@ -696,7 +696,10 @@ public class SqlValidatorImpl implements SqlValidatorWithHints { SqlNode from = requireNonNull(scope.getNode().getFrom(), () -> "getFrom for " + scope.getNode()); - new Permute(from, 0).permute(selectItems, fields); + // If some fields before star identifier, + // we should move offset. + int offset = calculatePermuteOffset(selectItems); + new Permute(from, offset).permute(selectItems, fields); } return true; @@ -744,6 +747,18 @@ public class SqlValidatorImpl implements SqlValidatorWithHints { } } + private int calculatePermuteOffset(List<SqlNode> selectItems) { + for (int i = 0; i < selectItems.size(); i++) { + SqlNode selectItem = selectItems.get(i); + SqlNode col = SqlUtil.stripAs(selectItem); + if (col.getKind() == SqlKind.IDENTIFIER + && selectItem.getKind() != SqlKind.AS) { + return i; + } + } + return 0; + } + private SqlNode maybeCast(SqlNode node, RelDataType currentType, RelDataType desiredType) { return SqlTypeUtil.equalSansNullability(typeFactory, currentType, desiredType) @@ -7307,8 +7322,10 @@ public class SqlValidatorImpl implements SqlValidatorWithHints { final List<ImmutableIntList> sources; final RelDataType rowType; final boolean trivial; + final int offset; Permute(SqlNode from, int offset) { + this.offset = offset; switch (from.getKind()) { case JOIN: final SqlJoin join = (SqlJoin) from; @@ -7383,9 +7400,11 @@ public class SqlValidatorImpl implements SqlValidatorWithHints { final List<SqlNode> oldSelectItems = ImmutableList.copyOf(selectItems); selectItems.clear(); + selectItems.addAll(oldSelectItems.subList(0, offset)); final List<Map.Entry<String, RelDataType>> oldFields = ImmutableList.copyOf(fields); fields.clear(); + fields.addAll(oldFields.subList(0, offset)); for (ImmutableIntList source : sources) { final int p0 = source.get(0); Map.Entry<String, RelDataType> field = oldFields.get(p0); diff --git a/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java b/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java index f0f7395e7b..2ec6d059f8 100644 --- a/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java +++ b/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java @@ -4566,6 +4566,42 @@ public class SqlValidatorTest extends SqlValidatorTestCase { + "clause\\) following CROSS JOIN"); } + /** Test case for + * <a href="https://issues.apache.org/jira/browse/CALCITE-5547";>[CALCITE-5547] + * Join using returns incorrect column names</a>. */ + @Test void testExtraColJoinUsing() { + final String expectedType = "RecordType(INTEGER NOT NULL TWO, " + + "INTEGER NOT NULL DEPTNO, " + + "INTEGER NOT NULL EMPNO, " + + "VARCHAR(20) NOT NULL ENAME, " + + "VARCHAR(10) NOT NULL JOB, " + + "INTEGER MGR, " + + "TIMESTAMP(0) NOT NULL HIREDATE, " + + "INTEGER NOT NULL SAL, " + + "INTEGER NOT NULL COMM, " + + "BOOLEAN NOT NULL SLACKER, " + + "VARCHAR(10) NOT NULL NAME) NOT NULL"; + + sql("select 2 as two, * from emp inner join dept using(deptno)") + .type(expectedType); + + sql("select 2 as two, * from emp natural join dept") + .type(expectedType); + + sql("select *, 2 as two from emp natural join dept") + .type("RecordType(INTEGER NOT NULL DEPTNO, " + + "INTEGER NOT NULL EMPNO, " + + "VARCHAR(20) NOT NULL ENAME, " + + "VARCHAR(10) NOT NULL JOB, " + + "INTEGER MGR, " + + "TIMESTAMP(0) NOT NULL HIREDATE, " + + "INTEGER NOT NULL SAL, " + + "INTEGER NOT NULL COMM, " + + "BOOLEAN NOT NULL SLACKER, " + + "VARCHAR(10) NOT NULL NAME, " + + "INTEGER NOT NULL TWO) NOT NULL"); + } + @Test void testJoinUsing() { final String empDeptType = "RecordType(INTEGER NOT NULL DEPTNO," + " INTEGER NOT NULL EMPNO," diff --git a/core/src/test/resources/sql/join.iq b/core/src/test/resources/sql/join.iq index e68b26c30c..8ab534b477 100644 --- a/core/src/test/resources/sql/join.iq +++ b/core/src/test/resources/sql/join.iq @@ -566,4 +566,75 @@ EnumerableCalc(expr#0..10=[{inputs}], expr#11=[COALESCE($t7, $t8)], DEPTNO=[$t11 EnumerableTableScan(table=[[scott, DEPT]]) !plan + +### [CALCITE-5547] Join using returns incorrect column names +select 2 as two, * from emp natural join dept; ++-----+--------+-------+--------+-----------+------+------------+---------+---------+------------+----------+ +| TWO | DEPTNO | EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DNAME | LOC | ++-----+--------+-------+--------+-----------+------+------------+---------+---------+------------+----------+ +| 2 | 10 | 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | | ACCOUNTING | NEW YORK | +| 2 | 10 | 7839 | KING | PRESIDENT | | 1981-11-17 | 5000.00 | | ACCOUNTING | NEW YORK | +| 2 | 10 | 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | | ACCOUNTING | NEW YORK | +| 2 | 20 | 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | | RESEARCH | DALLAS | +| 2 | 20 | 7566 | JONES | MANAGER | 7839 | 1981-02-04 | 2975.00 | | RESEARCH | DALLAS | +| 2 | 20 | 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | | RESEARCH | DALLAS | +| 2 | 20 | 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | | RESEARCH | DALLAS | +| 2 | 20 | 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | | RESEARCH | DALLAS | +| 2 | 30 | 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | SALES | CHICAGO | +| 2 | 30 | 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | SALES | CHICAGO | +| 2 | 30 | 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | SALES | CHICAGO | +| 2 | 30 | 7698 | BLAKE | MANAGER | 7839 | 1981-01-05 | 2850.00 | | SALES | CHICAGO | +| 2 | 30 | 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | SALES | CHICAGO | +| 2 | 30 | 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | | SALES | CHICAGO | ++-----+--------+-------+--------+-----------+------+------------+---------+---------+------------+----------+ +(14 rows) + +!ok + +select SAL * 12 AS YEAR_SAL, * from emp inner join dept using(deptno); ++----------+--------+-------+--------+-----------+------+------------+---------+---------+------------+----------+ +| YEAR_SAL | DEPTNO | EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DNAME | LOC | ++----------+--------+-------+--------+-----------+------+------------+---------+---------+------------+----------+ +| 11400.00 | 30 | 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | | SALES | CHICAGO | +| 13200.00 | 20 | 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | | RESEARCH | DALLAS | +| 15000.00 | 30 | 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | SALES | CHICAGO | +| 15000.00 | 30 | 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | SALES | CHICAGO | +| 15600.00 | 10 | 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | | ACCOUNTING | NEW YORK | +| 18000.00 | 30 | 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | SALES | CHICAGO | +| 19200.00 | 30 | 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | SALES | CHICAGO | +| 29400.00 | 10 | 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | | ACCOUNTING | NEW YORK | +| 34200.00 | 30 | 7698 | BLAKE | MANAGER | 7839 | 1981-01-05 | 2850.00 | | SALES | CHICAGO | +| 35700.00 | 20 | 7566 | JONES | MANAGER | 7839 | 1981-02-04 | 2975.00 | | RESEARCH | DALLAS | +| 36000.00 | 20 | 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | | RESEARCH | DALLAS | +| 36000.00 | 20 | 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | | RESEARCH | DALLAS | +| 60000.00 | 10 | 7839 | KING | PRESIDENT | | 1981-11-17 | 5000.00 | | ACCOUNTING | NEW YORK | +| 9600.00 | 20 | 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | | RESEARCH | DALLAS | ++----------+--------+-------+--------+-----------+------+------------+---------+---------+------------+----------+ +(14 rows) + +!ok + +select empno as two, * from emp natural join dept; ++------+--------+-------+--------+-----------+------+------------+---------+---------+------------+----------+ +| TWO | DEPTNO | EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DNAME | LOC | ++------+--------+-------+--------+-----------+------+------------+---------+---------+------------+----------+ +| 7369 | 20 | 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | | RESEARCH | DALLAS | +| 7499 | 30 | 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | SALES | CHICAGO | +| 7521 | 30 | 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | SALES | CHICAGO | +| 7566 | 20 | 7566 | JONES | MANAGER | 7839 | 1981-02-04 | 2975.00 | | RESEARCH | DALLAS | +| 7654 | 30 | 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | SALES | CHICAGO | +| 7698 | 30 | 7698 | BLAKE | MANAGER | 7839 | 1981-01-05 | 2850.00 | | SALES | CHICAGO | +| 7782 | 10 | 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | | ACCOUNTING | NEW YORK | +| 7788 | 20 | 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | | RESEARCH | DALLAS | +| 7839 | 10 | 7839 | KING | PRESIDENT | | 1981-11-17 | 5000.00 | | ACCOUNTING | NEW YORK | +| 7844 | 30 | 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | SALES | CHICAGO | +| 7876 | 20 | 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | | RESEARCH | DALLAS | +| 7900 | 30 | 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | | SALES | CHICAGO | +| 7902 | 20 | 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | | RESEARCH | DALLAS | +| 7934 | 10 | 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | | ACCOUNTING | NEW YORK | ++------+--------+-------+--------+-----------+------+------------+---------+---------+------------+----------+ +(14 rows) + +!ok + # End join.iq
