Re: the projected columns are getting converted to a star for create statement

[Yanjing Wang]

Maybe we need to add a config interface and default instance like
SqlToRelConverter to signify this conversion.

Saurabh Gupta  于2021年7月27日周二 下午11:15写道：

> Hi Team,
> There is one scenario for ex: create table temp1 as select c1,c2 from
> table1
> .And table1 has only c1 , c2 columns so this query is getting translated as
> :
>  create table temp1 as select * from table1.
>
> In this scenario we don't want the column to be converted to a star .
>
> One of the approaches is to add one more condition in isstar if block
> if (!isStar(e.getProjects(), e.getInput().getRowType(), e.getRowType()))
>
> Can you please suggest any better approach to solve this problem ,
>
> if the above approach looks good how to pass a boolean check to bypass
> this condition
>
>
> Thanks,
>
> Saurabh Gupta
>

the projected columns are getting converted to a star for create statement

[Saurabh Gupta]

Hi Team,
There is one scenario for ex: create table temp1 as select c1,c2 from table1
.And table1 has only c1 , c2 columns so this query is getting translated as
:
 create table temp1 as select * from table1.

In this scenario we don't want the column to be converted to a star .

One of the approaches is to add one more condition in isstar if block
if (!isStar(e.getProjects(), e.getInput().getRowType(), e.getRowType()))

Can you please suggest any better approach to solve this problem ,

if the above approach looks good how to pass a boolean check to bypass
this condition


Thanks,

Saurabh Gupta

[jira] [Created] (CALCITE-4705) calcite should support hint like /*+ skewjoin(a(c0, c1)) */

[jibiyr (Jira)]

jibiyr created CALCITE-4705:
---

 Summary: calcite should support hint like /*+ skewjoin(a(c0, c1)) 
*/
 Key: CALCITE-4705
 URL: https://issues.apache.org/jira/browse/CALCITE-4705
 Project: Calcite
  Issue Type: Improvement
  Components: core
Affects Versions: 1.27.0
Reporter: jibiyr
Assignee: jibiyr
 Fix For: 1.28.0


Dear community,

calcite parse support hint now,but it can not parse hint like follew,

_*select /*+ skewjoin(a(c0, c1)) */ * from T0 a join T1 b on a.c0 = b.c0,*_

this is the bnf which is calcite supported now,
{code:java}
// 
hint:
  hintName
  |   hintName '(' hintOptions ')'

hintOptions:
  hintKVOption [, hintKVOption ]*
  |   optionName [, optionName ]*
  |   optionValue [, optionValue ]*

hintKVOption:
  optionName '=' stringLiteral
  |   stringLiteral '=' stringLiteral

optionValue:
  stringLiteral
  |   numericLiteral
{code}
I think the hintOptions should support the function call pattern,like a(c0,c1). 
 and this pattern can be parsed by spark/presto now.



--
This message was sent by Atlassian Jira
(v8.3.4#803005)

[jira] [Created] (CALCITE-4704) Log produced plan after rule application using explain formatting

[Stamatis Zampetakis (Jira)]

Stamatis Zampetakis created CALCITE-4704:


 Summary: Log produced plan after rule application using explain 
formatting
 Key: CALCITE-4704
 URL: https://issues.apache.org/jira/browse/CALCITE-4704
 Project: Calcite
  Issue Type: Improvement
  Components: core
Affects Versions: 1.27.0
Reporter: Stamatis Zampetakis
Assignee: Stamatis Zampetakis
 Fix For: 1.28.0


In many cases, we want to identify which rule lead to a certain transformation 
in the plan or need to observe how the query plan evolves by applying some 
rules in order to fix some bug or find the right place to introduce another 
optimization step.

Currently there are some logs during the application of a rule triggered by the 
[HepPlanner|https://github.com/apache/calcite/blob/e04f3b08dcfb6910ff4df3810772c346b25ed424/core/src/main/java/org/apache/calcite/plan/AbstractRelOptPlanner.java#L367]
 and 
[VolcanoPlanner|https://github.com/apache/calcite/blob/e04f3b08dcfb6910ff4df3810772c346b25ed424/core/src/main/java/org/apache/calcite/plan/volcano/VolcanoRuleCall.java#L126]
 but they more or less display only the top operator of the transformation and 
not the whole subtree. Moreover, the {{RelNode#toString}} used in these logs, 
is not self-contained, so in order to check the transformation the log entry 
needs to be matched with other logs.

It would help if instead of displaying only the top operator we logged the 
equivalent of explain (i.e., {{RelOptUtil.toString}}) on the transformed 
sub-tree. 

You can find below some extracts from the current logs and how the proposed log 
could look like. 

*HepPlanner*
 +Current logging+
{noformat}
2021-07-27 14:37:58,252 [ForkJoinPool-1-worker-9] DEBUG - call#0: Rule 
FilterIntoJoinRule arguments 
[rel#9:LogicalFilter.NONE.[](input=HepRelVertex#8,condition==($7, $8)), 
rel#7:LogicalJoin.NONE.[](left=HepRelVertex#5,right=HepRelVertex#6,condition=true,joinType=left)]
 produced 
rel#14:LogicalProject.NONE.[](input=LogicalJoin#13,inputs=0..7,exprs=[CAST($8):TINYINT,
 $9, $10])
2021-07-27 14:37:58,266 [ForkJoinPool-1-worker-9] DEBUG - call#1: Rule 
ProjectJoinTransposeRule arguments 
[rel#16:LogicalProject.NONE.[](input=HepRelVertex#15,inputs=0..7,exprs=[CAST($8):TINYINT,
 $9, $10]), 
rel#13:LogicalJoin.NONE.[](left=HepRelVertex#5,right=HepRelVertex#6,condition==($7,
 $8),joinType=inner)] produced 
rel#21:LogicalProject.NONE.[](input=LogicalJoin#20,inputs=0..7,exprs=[$11, $9, 
$10])
2021-07-27 14:37:58,269 [ForkJoinPool-1-worker-9] DEBUG - call#3: Rule 
ProjectMergeRule arguments 
[rel#11:LogicalProject.NONE.[](input=HepRelVertex#27,exprs=[$1]), 
rel#26:LogicalProject.NONE.[](input=HepRelVertex#25,inputs=0..7,exprs=[$11, $9, 
$10])] produced rel#28:LogicalProject.NONE.[](input=HepRelVertex#25,exprs=[$1])
2021-07-27 14:37:58,272 [ForkJoinPool-1-worker-9] DEBUG - call#4: Rule 
ProjectJoinTransposeRule arguments 
[rel#28:LogicalProject.NONE.[](input=HepRelVertex#25,exprs=[$1]), 
rel#24:LogicalJoin.NONE.[](left=HepRelVertex#22,right=HepRelVertex#23,condition==($7,
 $8),joinType=inner)] produced 
rel#33:LogicalProject.NONE.[](input=LogicalJoin#32,inputs=0)
2021-07-27 14:37:58,274 [ForkJoinPool-1-worker-9] DEBUG - call#6: Rule 
ProjectMergeRule arguments 
[rel#30:LogicalProject.NONE.[](input=HepRelVertex#22,exprs=[$1, $7]), 
rel#18:LogicalProject.NONE.[0](input=HepRelVertex#5,inputs=0..7)] produced 
rel#40:LogicalProject.NONE.[](input=HepRelVertex#5,exprs=[$1, $7])
2021-07-27 14:37:58,275 [ForkJoinPool-1-worker-9] DEBUG - call#7: Rule 
ProjectMergeRule arguments 
[rel#31:LogicalProject.NONE.[0](input=HepRelVertex#23,inputs=0), 
rel#19:LogicalProject.NONE.[[0], 
[3]](input=HepRelVertex#6,inputs=0..2,exprs=[CAST($0):TINYINT])] produced 
rel#42:LogicalProject.NONE.[0](input=HepRelVertex#6,inputs=0)
{noformat}
+Proposed logging+
{noformat}
2021-07-27 14:37:58,260 [ForkJoinPool-1-worker-9] DEBUG - Rule 
FilterIntoJoinRule produced:
 LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], 
SAL=[$5], COMM=[$6], DEPTNO=[$7], DEPTNO0=[CAST($8):TINYINT], DNAME=[$9], 
LOC=[$10])
  LogicalJoin(condition=[=($7, $8)], joinType=[inner])
LogicalTableScan(table=[[scott, EMP]])
LogicalTableScan(table=[[scott, DEPT]])
2021-07-27 14:37:58,267 [ForkJoinPool-1-worker-9] DEBUG - Rule 
ProjectJoinTransposeRule produced:
 LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], 
SAL=[$5], COMM=[$6], DEPTNO=[$7], DEPTNO0=[$11], DNAME=[$9], LOC=[$10])
  LogicalJoin(condition=[=($7, $8)], joinType=[inner])
LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], 
SAL=[$5], COMM=[$6], DEPTNO=[$7])
  LogicalTableScan(table=[[scott, EMP]])
LogicalProject(DEPTNO=[$0], DNAME=[$1], LOC=[$2], EXPR$0=[CAST($0):TINYINT])
  LogicalTableScan(table=[[scott, DEPT]])
2021-07-27 14:37:58,270 [ForkJoinPool-1-worker-9] DEBUG - Rule 

ClassCastException: FamilyOperandTypeChecker cannot be cast to SqlOperandMetadata

[wrstrs]

Hi, all

how to solve this problem?




my code:
String sql = "select UNIX_TIMESTAMP(concat(substr('2021-07-27 
12:12:12',0,11),'09:00:00'))";

SqlOperatorTable opTab =

SqlLibraryOperatorTableFactory.INSTANCE.getOperatorTable(EnumSet.of(
SqlLibrary.HIVE, 
SqlLibrary.SPARK, SqlLibrary.BIG_QUERY, SqlLibrary.ORACLE, SqlLibrary.MYSQL, 
SqlLibrary.STANDARD));
SchemaPlus rootSchema = Frameworks.createRootSchema(true);

FrameworkConfig frameworkConfig = Frameworks.newConfigBuilder()
.defaultSchema(rootSchema)
.operatorTable(opTab)
.build();

Planner planner = Frameworks.getPlanner(frameworkConfig);
SqlNode sqlNode = planner.parse(sql);
SqlNode validateSqlNode = planner.validate(sqlNode);



throw exception:

Exception in thread "main" org.apache.calcite.tools.ValidationException: 
java.lang.ClassCastException: 
org.apache.calcite.sql.type.FamilyOperandTypeChecker cannot be cast to 
org.apache.calcite.sql.type.SqlOperandMetadata

at org.apache.calcite.prepare.PlannerImpl.validate(PlannerImpl.java:228)

Caused by: java.lang.ClassCastException: 
org.apache.calcite.sql.type.FamilyOperandTypeChecker cannot be cast to 
org.apache.calcite.sql.type.SqlOperandMetadata

at 
org.apache.calcite.sql.validate.implicit.TypeCoercionImpl.userDefinedFunctionCoercion(TypeCoercionImpl.java:589)

at org.apache.calcite.sql.SqlFunction.deriveType(SqlFunction.java:303)

at org.apache.calcite.sql.SqlFunction.deriveType(SqlFunction.java:231)