[jira] [Created] (CALCITE-6450) Postgres CONCAT_WS function throws exception when parameter type is (, )
xiong duan created CALCITE-6450:
---
Summary: Postgres CONCAT_WS function throws exception when
parameter type is (, )
Key: CALCITE-6450
URL: https://issues.apache.org/jira/browse/CALCITE-6450
Project: Calcite
Issue Type: Bug
Components: core
Affects Versions: 1.37.0
Reporter: xiong duan
The SQL can run success in Postgres:
{code:java}
select concat_ws(',',ARRAY[1, 1, 1, 1]); {code}
But in Calcite, It will throw exception:
{code:java}
@Test void testConcatFunction() {
final String sql = "select concat_ws(',',ARRAY[1, 1, 1, 1]) as
c";
fixture()
.withFactory(c ->
c.withOperatorTable(t ->
SqlValidatorTest.operatorTableFor(SqlLibrary.POSTGRESQL)))
.withCatalogReader(MockCatalogReaderExtended::create)
.withSql(sql)
.ok();
}{code}
{code:java}
>From line 1, column 8 to line 1, column 55: Cannot apply 'CONCAT_WS' to
>arguments of type 'CONCAT_WS(, )'. Supported form(s):
>'CONCAT_WS()'{code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Created] (CALCITE-6434) Specify identifier quoting for HiveSqlDialect and SparkSqlDialect
xiong duan created CALCITE-6434:
---
Summary: Specify identifier quoting for HiveSqlDialect and
SparkSqlDialect
Key: CALCITE-6434
URL: https://issues.apache.org/jira/browse/CALCITE-6434
Project: Calcite
Issue Type: Improvement
Components: core
Affects Versions: 1.37.0
Reporter: xiong duan
Assignee: xiong duan
Fix For: 1.38.0
The SQL:
{code:java}
SELECT product.product_class_id C
FROM foodmart.product
LEFT JOIN (SELECT CASE COUNT(*) WHEN 0 THEN NULL WHEN 1 THEN
MIN(product_class_id) ELSE (SELECT NULL
UNION ALL
SELECT NULL) END $f0
FROM foodmart.product) t0 ON TRUE
WHERE product.net_weight > t0.$f0{code}
Generate by SINGLE_VALUE agg function.
This SQL will parse failed in Spark Unless we add the identifier quoting like
`t0`.`$f0`
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Created] (CALCITE-6431) Implement the SINGLE_VALUE aggregation in HiveSqlDialect And SparkSQLDialect
xiong duan created CALCITE-6431: --- Summary: Implement the SINGLE_VALUE aggregation in HiveSqlDialect And SparkSQLDialect Key: CALCITE-6431 URL: https://issues.apache.org/jira/browse/CALCITE-6431 Project: Calcite Issue Type: Improvement Components: core Affects Versions: 1.37.0 Reporter: xiong duan Assignee: xiong duan Fix For: 1.38.0 When we use the SQL including the scalar query, converting this SQL Rel to HiveSQL will include the SINGLE_VALUE aggregation function(HiveSQL can't handle it) in the dialect SQL. -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Created] (CALCITE-6430) SINGLE_VALUE rewrite to wrong sql when the sub-query return one not-null value and NULL value in PostgreSQL、MySQL、HSQL dialect
xiong duan created CALCITE-6430:
---
Summary: SINGLE_VALUE rewrite to wrong sql when the sub-query
return one not-null value and NULL value in PostgreSQL、MySQL、HSQL dialect
Key: CALCITE-6430
URL: https://issues.apache.org/jira/browse/CALCITE-6430
Project: Calcite
Issue Type: Improvement
Components: core
Affects Versions: 1.37.0
Reporter: xiong duan
Assignee: xiong duan
Fix For: 1.38.0
Calcite rewrite the SINGLE_VALUE to different sql :
For example in HSQL:
{code:java}
CASE COUNT(*)
WHEN 0 THEN NULL
WHEN 1 THEN MIN()
ELSE (VALUES 1 UNION ALL VALUES 1)
END{code}
This is right. But Calcite will generate：
{code:java}
CASE COUNT(result)
WHEN 0 THEN NULL
WHEN 1 THEN MIN()
ELSE (VALUES 1 UNION ALL VALUES 1)
END{code}
This sql will return wrong result.
For Example:
tableA:
||c1||c2||
|4|1|
|NULL|NULL|
|NULL|NULL|
|NULL|NULL|
TheSQL:
{code:java}
select *
from tableA
where c1 > (select c2 from tableA);{code}
will throw : [21000][1242] Subquery returns more than 1 row
But SQL:
{code:java}
select c1 as column1C1
from column3
left join (select case count(c2)
when 0 then null
when 1 then min(c2)
else (select cast(null as integer) union all select cast(null as integer)) end
as alias
from column3 ) as t1 on true
where column3.c1 > t1.alias;{code}
will return one row value.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Created] (CALCITE-6424) Enable RLIKE function in MySQL library
xiong duan created CALCITE-6424: --- Summary: Enable RLIKE function in MySQL library Key: CALCITE-6424 URL: https://issues.apache.org/jira/browse/CALCITE-6424 Project: Calcite Issue Type: Improvement Components: core Affects Versions: 1.37.0 Reporter: xiong duan Assignee: xiong duan Fix For: 1.38.0 According to [https://sqlfiddle.com/mysql/online-compiler?id=1bca82cf-ebb9-4100-8cec-302b688f7ba8]. And the Official Document about RLIKE https://dev.mysql.com/doc/refman/8.4/en/regexp.html#operator_regexp -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Created] (CALCITE-6423) Invalid unparse for CHAR without precision in MySQLDialect
xiong duan created CALCITE-6423:
---
Summary: Invalid unparse for CHAR without precision in MySQLDialect
Key: CALCITE-6423
URL: https://issues.apache.org/jira/browse/CALCITE-6423
Project: Calcite
Issue Type: Improvement
Affects Versions: 1.37.0
Reporter: xiong duan
Assignee: xiong duan
Fix For: 1.38.0
As code review comment in [https://github.com/apache/calcite/pull/3799.]
Execute SQL :
{code:java}
select cast(product_id as char) from product{code}
Exppected MySQL SQL should be:
{code:java}
select cast(product_id as char) from product{code}
But is:
{code:java}
select cast(product_id as char(1)) from product{code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Created] (CALCITE-6419) Invalid unparse for VARCHAR without precision in HiveSqlDialect And SparkSqlDialect
xiong duan created CALCITE-6419:
---
Summary: Invalid unparse for VARCHAR without precision in
HiveSqlDialect And SparkSqlDialect
Key: CALCITE-6419
URL: https://issues.apache.org/jira/browse/CALCITE-6419
Project: Calcite
Issue Type: Improvement
Components: core
Affects Versions: 1.37.0
Reporter: xiong duan
Assignee: xiong duan
Fix For: 1.38.0
When we execute SQL in Calcite:
{code:java}
select cast(product_id as varchar) from product;
{code}
Generage the HiveSQL\SparkSQL:
{code:java}
select cast(product_id as varchar) from product;
{code}
According to the
[https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Types#LanguageManualTypes-VarcharvarcharVarchar].
In Hive, the varchar must have a precision.So when unpare VARCHAR without
precision, I will convert VARCHAR to String. VARCHAR with precison do nothing.
According to the [https://spark.apache.org/docs/latest/sql-ref-datatypes.html].
In Spark, Same as Hive. But as note, It can only be used in table schema, not
functions/operators. So I will convert VARCHAR with or without precision to
String;
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Created] (CALCITE-6417) Map value constructor and Array value constructor unparsed incorrectly for HiveSqlDialect
xiong duan created CALCITE-6417:
---
Summary: Map value constructor and Array value constructor
unparsed incorrectly for HiveSqlDialect
Key: CALCITE-6417
URL: https://issues.apache.org/jira/browse/CALCITE-6417
Project: Calcite
Issue Type: Improvement
Components: core
Affects Versions: 1.37.0
Reporter: xiong duan
Assignee: xiong duan
Fix For: 1.38.0
The SQL：
{code:java}
SELECT MAP['k1', 'v1', 'k2', 'v2'],ARRAY[1, 2, 3]{code}
Generate Hive SQL should be：
{code:java}
SELECT MAP ('k1', 'v1', 'k2', 'v2'),ARRAY (1, 2, 3){code}
But is:
{code:java}
SELECT MAP['k1', 'v1', 'k2', 'v2'],ARRAY[1, 2, 3]{code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Created] (CALCITE-6416) Remove unnecessary SUBSTRING rewrite in SparkSqlDialect
xiong duan created CALCITE-6416: --- Summary: Remove unnecessary SUBSTRING rewrite in SparkSqlDialect Key: CALCITE-6416 URL: https://issues.apache.org/jira/browse/CALCITE-6416 Project: Calcite Issue Type: Improvement Components: core Affects Versions: 1.37.0 Reporter: xiong duan Assignee: xiong duan Fix For: 1.38.0 SparkSqlDialect have a unnecessary rewrite about SUBSTRING func. In CALCITE-3247, We handle the SUBSTRING rewrite in HiveSqlDialect. In CALCITE-3072, We handle the SUBSTRING rewrite in SparkSqlDialect. In CALCITE-5677, We refactor the SUBSTRING as the default behaviour and remove the SUBSTRING rewrite in HiveSqlDialect. This PR will remove the Spark. -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Created] (CALCITE-6415) Invalid unparse for TIMESTAMP with HiveSqlDialect
xiong duan created CALCITE-6415:
---
Summary: Invalid unparse for TIMESTAMP with HiveSqlDialect
Key: CALCITE-6415
URL: https://issues.apache.org/jira/browse/CALCITE-6415
Project: Calcite
Issue Type: Improvement
Components: core
Affects Versions: 1.37.0
Reporter: xiong duan
Assignee: xiong duan
Fix For: 1.38.0
When parsing
{code:java}
SELECT CAST("2023-11-10" AS TIMESTAMP) {code}
The unparsed Hive SQL query gives:
{code:java}
SELECT CAST("2023-11-10" AS TIMESTAMP(0)) {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Created] (CALCITE-5929) Improve LogicalWindow print plan to add the constant value
xiong duan created CALCITE-5929:
---
Summary: Improve LogicalWindow print plan to add the constant value
Key: CALCITE-5929
URL: https://issues.apache.org/jira/browse/CALCITE-5929
Project: Calcite
Issue Type: Improvement
Components: core
Affects Versions: 1.35.0
Reporter: xiong duan
In general, we can extract the original SQL from the plan. But when the SQL
includes the LogicalWindow with a constant value, the Plan didn't print it.
The SQL:
{code:java}
select COUNT(*) over (
ORDER BY empno
ROWS BETWEEN 5 + 5 PRECEDING AND 1 PRECEDING) AS w_count from emp{code}
The Plan:
{code:java}
LogicalProject($0=[$1])
LogicalWindow(window#0=[window(order by [0] rows between $1 PRECEDING and $2
PRECEDING aggs [COUNT()])])
LogicalProject(EMPNO=[$0])
LogicalTableScan(table=[[CATALOG, SALES, EMP]]) {code}
In this plan, we don't know the $1 and $2 values.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Created] (CALCITE-5797) Release Calcite 1.35.0
xiong duan created CALCITE-5797: --- Summary: Release Calcite 1.35.0 Key: CALCITE-5797 URL: https://issues.apache.org/jira/browse/CALCITE-5797 Project: Calcite Issue Type: Test Reporter: xiong duan Assignee: xiong duan Fix For: 1.35.0 Release Calcite 1.35.0. -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Created] (CALCITE-5769) Optimizing 'CAST(e AS t) IS NOT NULL' to 'e IS NOT NULL'
xiong duan created CALCITE-5769: --- Summary: Optimizing 'CAST(e AS t) IS NOT NULL' to 'e IS NOT NULL' Key: CALCITE-5769 URL: https://issues.apache.org/jira/browse/CALCITE-5769 Project: Calcite Issue Type: Bug Components: core Affects Versions: 1.34.0 Reporter: xiong duan According to CALCITE-5156. We should support optimize: * 'CAST(e AS t) IS NOT NULL' to 'e IS NOT NULL' * 'CAST(e AS t) IS NULL' to 'e IS NULL' -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Created] (CALCITE-5750) SQL throws exception when the Filter include id = ARRAY [1,2,3]
xiong duan created CALCITE-5750:
---
Summary: SQL throws exception when the Filter include id = ARRAY
[1,2,3]
Key: CALCITE-5750
URL: https://issues.apache.org/jira/browse/CALCITE-5750
Project: Calcite
Issue Type: Bug
Components: core
Affects Versions: 1.34.0
Reporter: xiong duan
Assignee: xiong duan
When we create a PostgreSQL table as:
{code:java}
create table "arrayTest"
(
id integer[]
); {code}
Execute the SQL :
{code:java}
select * from arrayTest where id = array [1,2,3] and id = array [2] {code}
Will throws the exception:
{code:java}
java.lang.UnsupportedOperationException: Unsupported type when
convertTypeToSpec: ANY
Suppressed: java.lang.Throwable: Error while converting RelNode to SqlNode:
JdbcFilter(condition=[AND(=($0, ARRAY(CAST(1:BIGINT):ANY, CAST(2:BIGINT):ANY,
CAST(3:BIGINT):ANY)), =($0, ARRAY(CAST(2:BIGINT):ANY)))])
JdbcTableScan(table=[[FOODMART, arrayTest]]){code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Created] (CALCITE-5749) Join table with different database name and same table name will throw exception
xiong duan created CALCITE-5749:
---
Summary: Join table with different database name and same table
name will throw exception
Key: CALCITE-5749
URL: https://issues.apache.org/jira/browse/CALCITE-5749
Project: Calcite
Issue Type: Bug
Components: core
Affects Versions: 1.34.0
Reporter: xiong duan
Assignee: xiong duan
When we execute SQL:
{code:java}
select * from test1.tablea,test2.tablea {code}
Calcite will throw exception:
{code:java}
Duplicate relation name 'tablea' in FROM clause {code}
Because when we generate alias for this table, just use the last value in the
names.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Created] (CALCITE-5711) Implement the SINGLE_VALUE aggregation in PostgreSQL Dialect
xiong duan created CALCITE-5711:
---
Summary: Implement the SINGLE_VALUE aggregation in PostgreSQL
Dialect
Key: CALCITE-5711
URL: https://issues.apache.org/jira/browse/CALCITE-5711
Project: Calcite
Issue Type: Bug
Components: core
Affects Versions: 1.34.0
Reporter: xiong duan
Assignee: xiong duan
Fix For: 1.35.0
When we use the SQL include the scalar query for example:
{code:java}
select * from table where id = (select code from table){code}
Convert this SQL Rel to PostgreSQL will include the SINGLE_VALUE aggregation
function(PostgreSQL can't handle it) in the dialect SQL.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Created] (CALCITE-5181) Sum aggregate function return wong result
xiong duan created CALCITE-5181:
---
Summary: Sum aggregate function return wong result
Key: CALCITE-5181
URL: https://issues.apache.org/jira/browse/CALCITE-5181
Project: Calcite
Issue Type: Bug
Components: core
Affects Versions: 1.30.0
Reporter: xiong duan
To reproduce(dummy.iq):
{code:java}
!use scott
!set outputformat mysql
SELECT sum(empno) from "scott".emp; {code}
Should return 108172, but return -22900.
This is because RelDataTypeSystemImpl deriveSumType return a wrong datatype. We
presume when the parameter is smllInt, the return datatype stay same as the
parameter. So the result out of range.
--
This message was sent by Atlassian Jira
(v8.20.7#820007)
[jira] [Created] (CALCITE-5156) Support implicit type cast for IN Sub-query
xiong duan created CALCITE-5156:
---
Summary: Support implicit type cast for IN Sub-query
Key: CALCITE-5156
URL: https://issues.apache.org/jira/browse/CALCITE-5156
Project: Calcite
Issue Type: Bug
Components: core
Affects Versions: 1.30.0
Reporter: xiong duan
The SQL:
{code:java}
select * from dept where deptno + 20 in (select deptno from dept);{code}
Calcite returns the wrong answer.
but the SQL
{code:java}
select * from dept where deptno + 20 in (select cast(deptno as integer) from
dept);{code}
Calcite returns the correct answer.
So when we generate the RelNode, we can add the type cast.
Before the type cast:
{noformat}
LogicalProject(DEPTNO=[$0], DNAME=[$1], LOC=[$2])
LogicalFilter(condition=[IN(+($0, 20), {
LogicalProject(DEPTNO=[$0])
LogicalTableScan(table=[[scott, DEPT]])
})])
LogicalTableScan(table=[[scott, DEPT]]){noformat}
After the type cast:
{noformat}
LogicalProject(DEPTNO=[$0], DNAME=[$1], LOC=[$2])
LogicalFilter(condition=[IN(+($0, 20), {
LogicalProject(EXPR$0=[CAST($0):INTEGER NOT NULL])
LogicalTableScan(table=[[scott, DEPT]])
})])
LogicalTableScan(table=[[scott, DEPT]]){noformat}
--
This message was sent by Atlassian Jira
(v8.20.7#820007)
[jira] [Created] (CALCITE-5140) The Github CI failed in Linux (OpenJ9 8)
xiong duan created CALCITE-5140:
---
Summary: The Github CI failed in Linux (OpenJ9 8)
Key: CALCITE-5140
URL: https://issues.apache.org/jira/browse/CALCITE-5140
Project: Calcite
Issue Type: Bug
Components: core
Affects Versions: 1.30.0
Reporter: xiong duan
Many PR filed in this process. This failure first appeared on May 2, 2022
[6303017566|https://github.com/apache/calcite/runs/6303017566?check_suite_focus=true].
The info:
{noformat}
Caused by: javax.security.auth.login.LoginException: unable to find
LoginModule class: com.ibm.security.auth.module.LinuxLoginModule
11591at
javax.security.auth.login.LoginContext.invoke(LoginContext.java:794)
11592at
javax.security.auth.login.LoginContext.access$000(LoginContext.java:195)
11593at
javax.security.auth.login.LoginContext$4.run(LoginContext.java:682)
11594at
javax.security.auth.login.LoginContext$4.run(LoginContext.java:680)
11595at
java.security.AccessController.doPrivileged(AccessController.java:783)
11596at
javax.security.auth.login.LoginContext.invokePriv(LoginContext.java:680)
11597at
javax.security.auth.login.LoginContext.login(LoginContext.java:587)
11598at
org.apache.hadoop.security.UserGroupInformation.loginUserFromSubject(UserGroupInformation.java:814)
11599... 66 more{noformat}
I check the CALCITE-5003 change, The failure is irrelevant to this PR. This
happened before we upgrade the JDK 18.
--
This message was sent by Atlassian Jira
(v8.20.7#820007)
[jira] [Created] (CALCITE-5139) Improve Join print plan to add the CorrelationId info
xiong duan created CALCITE-5139:
---
Summary: Improve Join print plan to add the CorrelationId info
Key: CALCITE-5139
URL: https://issues.apache.org/jira/browse/CALCITE-5139
Project: Calcite
Issue Type: Bug
Components: core
Affects Versions: 1.30.0
Reporter: xiong duan
When the Filter plan has CorrelationId info, The Filter output plan will be:
{noformat}
LogicalFilter(condition=[=($10, $SCALAR_QUERY({
LogicalAggregate(group=[{}], EXPR$0=[MAX($0)])
LogicalProject(NAME=[$1])
LogicalFilter(condition=[=($0, $cor0.DEPTNO0)])
LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
}))], variablesSet=[[$cor0]]){noformat}
This plan makes the user know this plan includes the variables set. But when
the Join include the CorrelationId info, it didn't output it.
--
This message was sent by Atlassian Jira
(v8.20.7#820007)
[jira] [Created] (CALCITE-5138) Join on condition generates wrong plan when the condition is sub-query
xiong duan created CALCITE-5138:
---
Summary: Join on condition generates wrong plan when the condition
is sub-query
Key: CALCITE-5138
URL: https://issues.apache.org/jira/browse/CALCITE-5138
Project: Calcite
Issue Type: Bug
Components: core
Affects Versions: 1.30.0
Reporter: xiong duan
The SQL:
{code:java}
SELECT emp.deptno, emp.sal
FROM dept
LEFT JOIN emp ON (SELECT AVG(emp.sal) > 0 FROM emp) {code}
Calcite generates the wrong plan:
{noformat}
EnumerableCalc(expr#0..4=[{inputs}], DEPTNO=[$t3], SAL=[$t2])
EnumerableNestedLoopJoin(condition=[$0], joinType=[left])
EnumerableCalc(expr#0..2=[{inputs}], DEPTNO=[$t0])
EnumerableTableScan(table=[[scott, DEPT]])
EnumerableNestedLoopJoin(condition=[true], joinType=[left])
EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0], SAL=[$t5],
DEPTNO=[$t7])
EnumerableTableScan(table=[[scott, EMP]])
EnumerableAggregate(group=[{}], DUMMY=[COUNT()])
EnumerableAggregate(group=[{}], agg#0=[$SUM0($5)], agg#1=[COUNT($5)])
EnumerableTableScan(table=[[scott, EMP]]){noformat}
As above plan, the out NestedLoopJoin condition will be deptno column, not the
AVG(emp.sal) > 0 condition.
--
This message was sent by Atlassian Jira
(v8.20.7#820007)
[jira] [Created] (CALCITE-5132) When the IN-list that include NUll value is converted to Values as a SCALA subquery will return wrong result
xiong duan created CALCITE-5132:
---
Summary: When the IN-list that include NUll value is converted to
Values as a SCALA subquery will return wrong result
Key: CALCITE-5132
URL: https://issues.apache.org/jira/browse/CALCITE-5132
Project: Calcite
Issue Type: Bug
Affects Versions: 1.30.0
Reporter: xiong duan
The SQL：
{code:java}
select empno, deptno, (empno, deptno) in ((7521, null)) from "scott".emp;{code}
{noformat}
+---+++
| EMPNO | DEPTNO | EXPR$2 |
+---+++
| 7369 | 20 | |
| 7499 | 30 | |
| 7521 | 30 | |
| 7566 | 20 | |
| 7654 | 30 | |
| 7698 | 30 | |
| 7782 | 10 | |
| 7788 | 20 | |
| 7839 | 10 | |
| 7844 | 30 | |
| 7876 | 20 | |
| 7900 | 30 | |
| 7902 | 20 | |
| 7934 | 10 | |
+---+++
(14 rows)
{noformat}
In PG、MySQL, will return :
{noformat}
+-+--++
|empno|deptno|?column?|
+-+--++
|7369 |20 |false |
|7499 |30 |false |
|7521 |30 |NULL |
|7566 |20 |false |
|7654 |30 |false |
|7698 |30 |false |
|7782 |10 |false |
|7788 |20 |false |
|7839 |10 |false |
|7844 |30 |false |
|7876 |20 |false |
|7900 |30 |false |
|7902 |20 |false |
|7934 |10 |false |
+-+--++{noformat}
Same SQL include:
{code:java}
select empno, deptno, (empno, deptno) in (values(7521, null)) from "scott".emp;
{code}
{code:java}
select empno, deptno, (empno, deptno) in (select deptno,deptno from
"scott".dept) from "scott".emp;{code}
This is because The Calcite evaluates the (7521, null) eq (3456, null) is
UNKNOW, But it should be FALSE;
--
This message was sent by Atlassian Jira
(v8.20.7#820007)
[jira] [Created] (CALCITE-5117) Optimize the EXISTS sub-query by Metadata RowCount
xiong duan created CALCITE-5117:
---
Summary: Optimize the EXISTS sub-query by Metadata RowCount
Key: CALCITE-5117
URL: https://issues.apache.org/jira/browse/CALCITE-5117
Project: Calcite
Issue Type: Bug
Components: core
Affects Versions: 1.30.0
Reporter: xiong duan
As same as the UNIQUE sub-query, before we convert the sub-query to RelNode. we
use the Metadata to optimize it.
EXISTS sub-query, If the sub-query is guaranteed to produce at least one row,
just return TRUE. If the sub-query is guaranteed to produce no row, just return
FALSE.
For example:
{code:java}
select *
from dept as d
where EXISTS (select count(*) from emp e where d.deptno = e.deptno){code}
We can optimize it to:
{code:java}
EnumerableTableScan(table=[[scott, DEPT]]){code}
--
This message was sent by Atlassian Jira
(v8.20.7#820007)
[jira] [Created] (CALCITE-5112) Upgrade Jetty version to 9.4.44.v202109272
xiong duan created CALCITE-5112: --- Summary: Upgrade Jetty version to 9.4.44.v202109272 Key: CALCITE-5112 URL: https://issues.apache.org/jira/browse/CALCITE-5112 Project: Calcite Issue Type: Bug Components: core Affects Versions: 1.30.0 Reporter: xiong duan Fix For: 1.31.0 Now The Calcite Jetty version is 9.4.15.v20190215. According to w[JETTY-CPU-BUG|https://github.com/eclipse/jetty.project/security/advisories/GHSA-26vr-8j45-3r4w]. we need to upgrade the jetty version. Why is 9.4.44.v202109272? According to the bug info, the bug has been fixed in this version and The Calcite has some module dependency on this. -- This message was sent by Atlassian Jira (v8.20.7#820007)
[jira] [Created] (CALCITE-5021) Double JOIN is created for NOT IN when IN-list that the values all non-nullable is converted to Values
xiong duan created CALCITE-5021:
---
Summary: Double JOIN is created for NOT IN when IN-list that the
values all non-nullable is converted to Values
Key: CALCITE-5021
URL: https://issues.apache.org/jira/browse/CALCITE-5021
Project: Calcite
Issue Type: Bug
Components: core
Affects Versions: 1.29.0
Reporter: xiong duan
Fix For: 1.30.0
Double JOIN is created for NOT IN when IN-list that the values all non-nullable
is converted to Values.
For example:
{code:java}
select * from "scott".emp where (empno, deptno) not in ((7369, 20), (7499,
30));{code}
Before fix(Double Join):
{noformat}
EnumerableCalc(expr#0..14=[{inputs}], expr#15=[0:BIGINT], expr#16=[=($t8,
$t15)], expr#17=[IS NULL($t14)], expr#18=[>=($t9, $t8)], expr#19=[IS NOT
NULL($t11)], expr#20=[AND($t17, $t18, $t19)], expr#21=[OR($t16, $t20)],
proj#0..7=[{exprs}], $condition=[$t21])
EnumerableMergeJoin(condition=[AND(=($10, $12), =($11, $13))],
joinType=[left])
EnumerableSort(sort0=[$10], sort1=[$11], dir0=[ASC], dir1=[ASC])
EnumerableCalc(expr#0..9=[{inputs}], proj#0..9=[{exprs}], EMPNO0=[$t0],
DEPTNO0=[/$t7])
EnumerableNestedLoopJoin(condition=[true], joinType=[inner])
EnumerableTableScan(table=[[scott, EMP]])
EnumerableAggregate(group=[{}], agg#0=[COUNT()], agg#1=[COUNT($1)])
EnumerableValues(tuples=[[{ 7369, 20 }, { 7499, 30 }]])
EnumerableSort(sort0=[$0], sort1=[$1], dir0=[ASC], dir1=[ASC])
EnumerableCalc(expr#0..1=[{inputs}], expr#2=[true], proj#0..2=[{exprs}])
EnumerableValues(tuples=[[{ 7369, 20 }, { 7499, 30 }]]){noformat}
After Fix：
{noformat}
EnumerableCalc(expr#0..12=[{inputs}], expr#13=[IS NOT TRUE($t12)], expr#14=[IS
NULL($t9)], expr#15=[OR($t13, $t14)], proj#0..7=[{exprs}], $condition=[$t15])
EnumerableMergeJoin(condition=[AND(=($8, $10), =($9, $11))], joinType=[left])
EnumerableSort(sort0=[$8], sort1=[$9], dir0=[ASC], dir1=[ASC])
EnumerableCalc(expr#0..7=[{inputs}], proj#0..7=[{exprs}], EMPNO0=[$t0],
DEPTNO0=[$t7])
EnumerableTableScan(table=[[scott, EMP]])
EnumerableSort(sort0=[$0], sort1=[$1], dir0=[ASC], dir1=[ASC])
EnumerableCalc(expr#0..1=[{inputs}], expr#2=[true], proj#0..2=[{exprs}])
EnumerableValues(tuples=[[{ 7369, 20 }, { 7499, 30 }]]){noformat}
--
This message was sent by Atlassian Jira
(v8.20.1#820001)
[jira] [Created] (CALCITE-4993) Simplify EQUALS or NOT-EQUALS with other number comparison
xiong duan created CALCITE-4993:
---
Summary: Simplify EQUALS or NOT-EQUALS with other number comparison
Key: CALCITE-4993
URL: https://issues.apache.org/jira/browse/CALCITE-4993
Project: Calcite
Issue Type: Bug
Components: core
Affects Versions: 1.29.0
Reporter: xiong duan
For example(SELECT expression FROM table), The Plan is EnumerableCalc
description:
1:
{code:java}
"cust_id"<>5 and "cust_id">3 and "cust_id"< 10{code}
Before:
{noformat}
expr#0..1=[{inputs}], expr#2=[CAST($t0):INTEGER NOT NULL], expr#3=[5],
expr#4=[<>($t2, $t3)], expr#5=[3], expr#6=[>($t0, $t5)], expr#7=[10],
expr#8=[<($t0, $t7)], expr#9=[AND($t4, $t6, $t8)], EXPR$0=[$t9]{noformat}
After:
{noformat}
(expr#0..1=[{inputs}], expr#2=[Sarg[(3..5), (5..10)]], expr#3=[SEARCH($t0,
$t2)], EXPR$0=[$t3]){noformat}
2:
{code:java}
"cust_id"=5 and "cust_id">3 and "cust_id"< 10{code}
Before:
{noformat}
expr#0..1=[{inputs}], expr#2=[CAST($t0):INTEGER NOT NULL], expr#3=[5],
expr#4=[=($t2, $t3)], expr#5=[3], expr#6=[>($t0, $t5)], expr#7=[10],
expr#8=[<($t0, $t7)], expr#9=[AND($t4, $t6, $t8)], EXPR$0=[$t9]{noformat}
After:
{noformat}
expr#0..1=[{inputs}], expr#2=[5], expr#3=[=($t0, $t2)], EXPR$0=[$t3]{noformat}
3:
{code:java}
"cust_id"=5 and "cust_id">6 and "cust_id"< 10{code}
Before:
{noformat}
expr#0..1=[{inputs}], expr#2=[CAST($t0):INTEGER NOT NULL], expr#3=[5],
expr#4=[=($t2, $t3)], expr#5=[6], expr#6=[>($t0, $t5)], expr#7=[10],
expr#8=[<($t0, $t7)], expr#9=[AND($t4, $t6, $t8)], EXPR$0=[$t9]{noformat}
After:
{noformat}
expr#0..1=[{inputs}], expr#2=[false], EXPR$0=[$t2]{noformat}
--
This message was sent by Atlassian Jira
(v8.20.1#820001)
[jira] [Created] (CALCITE-4988) ((A IS NOT NULL OR B) AND A) can't be simplify to (A) When A is deterministic
xiong duan created CALCITE-4988:
---
Summary: ((A IS NOT NULL OR B) AND A) can't be simplify to (A)
When A is deterministic
Key: CALCITE-4988
URL: https://issues.apache.org/jira/browse/CALCITE-4988
Project: Calcite
Issue Type: Bug
Affects Versions: 1.29.0
Reporter: xiong duan
Assignee: xiong duan
Now In Calcite:
{code:java}
((A IS NOT NULL OR B) AND A) can be simplify to (A){code}
When A is a node representing an input reference or field access.
For example:
{code:java}
(name is null or empid = 1) and name is nul -> name is null{code}
But When A is a deterministic node, It can't reduce.
For example:
(replace(name,'e','a') is not null or replace(name,'a','c') is not null)
and replace(name,'e','a') is not null
Can't be simplify to:
{code:java}
replace(name,'e','a') is not null{code}
This issue originated in CALCITE-4910.
--
This message was sent by Atlassian Jira
(v8.20.1#820001)
