[CALCITE-2476] SqlAdvisor: produce hints when sub-query with * is present in query
simplifySql produced 0 AS "*", and it caused the SQL to fail validation. The fix is to disable transformation of expressions to "0 as ...", so it keeps select * as is. Project: http://git-wip-us.apache.org/repos/asf/calcite/repo Commit: http://git-wip-us.apache.org/repos/asf/calcite/commit/77b8c4db Tree: http://git-wip-us.apache.org/repos/asf/calcite/tree/77b8c4db Diff: http://git-wip-us.apache.org/repos/asf/calcite/diff/77b8c4db Branch: refs/heads/master Commit: 77b8c4dbf4f42c8cbbbb0e41ef9d49f0c2d9ba39 Parents: 4afea52 Author: Vladimir Sitnikov <[email protected]> Authored: Sun Aug 19 18:20:00 2018 +0300 Committer: Vladimir Sitnikov <[email protected]> Committed: Wed Sep 5 15:24:24 2018 +0300 ---------------------------------------------------------------------- .../calcite/sql/advise/SqlSimpleParser.java | 5 ++++- .../apache/calcite/sql/test/SqlAdvisorTest.java | 22 +++++++++++++------- 2 files changed, 18 insertions(+), 9 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/calcite/blob/77b8c4db/core/src/main/java/org/apache/calcite/sql/advise/SqlSimpleParser.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/sql/advise/SqlSimpleParser.java b/core/src/main/java/org/apache/calcite/sql/advise/SqlSimpleParser.java index 7324a8f..edc7c5f 100644 --- a/core/src/main/java/org/apache/calcite/sql/advise/SqlSimpleParser.java +++ b/core/src/main/java/org/apache/calcite/sql/advise/SqlSimpleParser.java @@ -606,7 +606,9 @@ public class SqlSimpleParser { // Indicates that the expression to be simplified is // outside this sub-query. Preserve a simplified SELECT // clause. - purgeSelectExprsKeepAliases(); + // It might be a good idea to purge select expressions, however + // purgeSelectExprsKeepAliases might end up with <<0 as "*">> which is not valid. + // purgeSelectExprsKeepAliases(); purgeWhere(); purgeGroupByHaving(); break; @@ -690,6 +692,7 @@ public class SqlSimpleParser { if (((i + 1) == sublist.size()) || (sublist.get(i + 1).type == TokenType.COMMA)) { if (token.type == TokenType.ID) { + // This might produce <<0 as "a.x+b.y">>, or <<0 as "*">>, or even <<0 as "a.*">> newSelectClause.add(new Token(TokenType.ID, "0")); newSelectClause.add(new Token(TokenType.ID, "AS")); newSelectClause.add(token); http://git-wip-us.apache.org/repos/asf/calcite/blob/77b8c4db/core/src/test/java/org/apache/calcite/sql/test/SqlAdvisorTest.java ---------------------------------------------------------------------- diff --git a/core/src/test/java/org/apache/calcite/sql/test/SqlAdvisorTest.java b/core/src/test/java/org/apache/calcite/sql/test/SqlAdvisorTest.java index fa6edf6..e2c6e73 100644 --- a/core/src/test/java/org/apache/calcite/sql/test/SqlAdvisorTest.java +++ b/core/src/test/java/org/apache/calcite/sql/test/SqlAdvisorTest.java @@ -661,6 +661,12 @@ public class SqlAdvisorTest extends SqlValidatorTestCase { assertComplete(sql, list); } + @Test public void testSimplifyStarAlias() { + String sql; + sql = "select ax^ from (select * from dummy a)"; + assertSimplify(sql, "SELECT ax _suggest_ FROM ( SELECT * FROM dummy a )"); + } + @Test public void testSimlifyMinus() { String sql; sql = "select ^ from dummy a minus select * from dummy b"; @@ -909,7 +915,7 @@ public class SqlAdvisorTest extends SqlValidatorTestCase { sql = "select t. from (select 1 as x, 2 as y from (select x from sales.emp)) as t where ^"; String simplified = - "SELECT * FROM ( SELECT 0 AS x , 0 AS y FROM ( SELECT 0 AS x FROM sales.emp ) ) as t WHERE _suggest_"; + "SELECT * FROM ( SELECT 1 as x , 2 as y FROM ( SELECT x FROM sales.emp ) ) as t WHERE _suggest_"; assertSimplify(sql, simplified); assertComplete(sql, EXPR_KEYWORDS, tTable, xyColumns); @@ -1098,15 +1104,15 @@ public class SqlAdvisorTest extends SqlValidatorTestCase { + "where t.dummy=1"; expected = "SELECT t. _suggest_ " - + "FROM ( SELECT 0 AS x , 0 AS y FROM sales.emp ) as t"; + + "FROM ( SELECT 1 as x , 2 as y FROM sales.emp ) as t"; assertSimplify(sql, expected); sql = "select t. from (select 1 as x, 2 as y from " + "(select x from sales.emp)) as t where ^"; expected = - "SELECT * FROM ( SELECT 0 AS x , 0 AS y FROM " - + "( SELECT 0 AS x FROM sales.emp ) ) as t WHERE _suggest_"; + "SELECT * FROM ( SELECT 1 as x , 2 as y FROM " + + "( SELECT x FROM sales.emp ) ) as t WHERE _suggest_"; assertSimplify(sql, expected); sql = @@ -1114,8 +1120,8 @@ public class SqlAdvisorTest extends SqlValidatorTestCase { + "(select 2 as y from (select m from n where)) as t " + "where t.dummy=1"; expected = - "SELECT _suggest_ FROM ( SELECT 0 AS x , 0 AS y FROM sales.emp ) " - + ", ( SELECT 0 AS y FROM ( SELECT 0 AS m FROM n ) ) as t"; + "SELECT _suggest_ FROM ( SELECT 1 as x , 2 as y FROM sales.emp ) " + + ", ( SELECT 2 as y FROM ( SELECT m FROM n ) ) as t"; assertSimplify(sql, expected); // Note: completes the missing close paren; wipes out select clause of @@ -1126,7 +1132,7 @@ public class SqlAdvisorTest extends SqlValidatorTestCase { sql = "select t.^ from (select 1 as x, 2 as y from sales)"; expected = - "SELECT t. _suggest_ FROM ( SELECT 0 AS x , 0 AS y FROM sales )"; + "SELECT t. _suggest_ FROM ( SELECT 1 as x , 2 as y FROM sales )"; assertSimplify(sql, expected); // sub-query in where; note that: @@ -1139,7 +1145,7 @@ public class SqlAdvisorTest extends SqlValidatorTestCase { + "(select 1 as x, 2 as y from sales group by invalid stuff) as t " + "where x in (select deptno from emp where foo + t.^ < 10)"; expected = - "SELECT * FROM ( SELECT 0 AS x , 0 AS y FROM sales ) as t " + "SELECT * FROM ( SELECT 1 as x , 2 as y FROM sales ) as t " + "WHERE x in ( SELECT * FROM emp WHERE foo + t. _suggest_ < 10 )"; assertSimplify(sql, expected);
