[jira] [Created] (CALCITE-6599) RelMdPredicates pull up wrong predicate from VALUES
xiong duan created CALCITE-6599:
---
Summary: RelMdPredicates pull up wrong predicate from VALUES
Key: CALCITE-6599
URL: https://issues.apache.org/jira/browse/CALCITE-6599
Project: Calcite
Issue Type: Bug
Reporter: xiong duan
For example:
{code:java}
{VALUES(1,2,3)}{code}
Predicates should be ($0=1,$1=2,$2=3), but now is $0=1.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Created] (CALCITE-6592) RelMdPredicates pull up wrong predicate from UNION when it's input include NULL VALUE
xiong duan created CALCITE-6592:
---
Summary: RelMdPredicates pull up wrong predicate from UNION when
it's input include NULL VALUE
Key: CALCITE-6592
URL: https://issues.apache.org/jira/browse/CALCITE-6592
Project: Calcite
Issue Type: Bug
Reporter: xiong duan
The RelNode:
{code:java}
LogicalUnion(all=[true])
LogicalValues(tuples=[[{ NULL }]])
LogicalValues(tuples=[[{ 131 }]]){code}
The predicate is '$0 == 131'
{code:java}
LogicalUnion(all=[true])
LogicalValues(tuples=[[{ 11 }]])
LogicalValues(tuples=[[{ 131 }]])
{pulled[SEARCH($0, Sarg[11, 131])]}{code}
The Predicate is SEARCH($0, Sarg[11, 131]).
So the first RelNode's Predicate should be NONE or SEARCH($0, Sarg[null, 131]).
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Created] (CALCITE-6578) RelToSql throws exception when unparse TIMESTAMPDIFF function
xiong duan created CALCITE-6578:
---
Summary: RelToSql throws exception when unparse TIMESTAMPDIFF
function
Key: CALCITE-6578
URL: https://issues.apache.org/jira/browse/CALCITE-6578
Project: Calcite
Issue Type: Improvement
Reporter: xiong duan
Unit test in RelToSqlConverterTest:
{code:java}
@Test void testTimestampdiff() {
String query = "select TIMESTAMPDIFF(day,date '2029-05-06',date '2029-05-07')";
String expected = "";
sql(query).ok(expected);
}{code}
The RelNode is:
{code:java}
LogicalProject(EXPR$0=[CAST(/INT(Reinterpret(-(2029-05-07, 2029-05-06)),
8640)):INTEGER NOT NULL])
LogicalValues(tuples=[[{ 0 }]]){code}
Exception:
{code:java}
class org.apache.calcite.sql.SqlSyntax$7: SPECIAL
java.lang.UnsupportedOperationException: class
org.apache.calcite.sql.SqlSyntax$7: SPECIAL{code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Created] (CALCITE-6560) Add supportsNegativeScale RelDataTypeSystem
xiong duan created CALCITE-6560: --- Summary: Add supportsNegativeScale RelDataTypeSystem Key: CALCITE-6560 URL: https://issues.apache.org/jira/browse/CALCITE-6560 Project: Calcite Issue Type: Improvement Reporter: xiong duan [https://www.postgresql.org/docs/current/datatype-numeric.html] According to the document, a numeric column with a negative scale can be declared beginning in PostgreSQL 15. This PR will add some tests to cover supportsNegativeScale is false and will throw an exception if set supportsNegativeScale is true. -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Created] (CALCITE-6548) Result type inferred for COVAR_SAMP、COVAR_POP is incorrect
xiong duan created CALCITE-6548: --- Summary: Result type inferred for COVAR_SAMP、COVAR_POP is incorrect Key: CALCITE-6548 URL: https://issues.apache.org/jira/browse/CALCITE-6548 Project: Calcite Issue Type: Improvement Reporter: xiong duan Assignee: xiong duan COVAR_SAMP can return NULL. If the function is applied to an empty set, it returns null. E.g. see the Oracle documentation: [https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/VAR_SAMP.html|https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/COVAR_SAMP.html] . COVAR_POP see the Oracle documentation: [https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/COVAR_POP.html|https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/COVAR_POP.html]. -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Created] (CALCITE-6546) Hive dialect does not support a sub-query in the FROM clause without alias
xiong duan created CALCITE-6546:
---
Summary: Hive dialect does not support a sub-query in the FROM
clause without alias
Key: CALCITE-6546
URL: https://issues.apache.org/jira/browse/CALCITE-6546
Project: Calcite
Issue Type: Improvement
Components: core
Environment: In Hive, the SQL:
{code:java}
select * from (select * from table1);
{code}
will throw an exception:
{code:java}
Error: Error while compiling statement: FAILED: ParseException line 1:36 cannot
recognize input near '' '' '' in subquery source
(state=42000,code=4)
{code}
The SQL：
{code:java}
select * from (select * from table1) as table2;
{code}
works well.
So when we generate the Hive SQL in the Jdbc adapter, we need to create an
alias for the sub-query in the From clause.
Reporter: xiong duan
Assignee: xiong duan
Fix For: 1.38.0
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Created] (CALCITE-6542) Jdbc adapter support use a unified alias when generating SQL
xiong duan created CALCITE-6542:
---
Summary: Jdbc adapter support use a unified alias when generating
SQL
Key: CALCITE-6542
URL: https://issues.apache.org/jira/browse/CALCITE-6542
Project: Calcite
Issue Type: Improvement
Reporter: xiong duan
For example:
Postgresql:
{code:java}
select avg(1) from table;{code}
{code:java}
+---+
|avg|
+---+
|1 |
+---+{code}
HSQLDB:
{code:java}
select avg(1) from table;{code}
{code:java}
+---+
|C1 |
+---+
|1 |
+---+{code}
When I use Calcite to query different databases and don't want users to notice
the difference, I want to add a configuration in SqlDialect to support
generating a unified name.
When the configuration is open, generate SQL will be:
{code:java}
select svg(1) as EXPR$0 from table;{code}
This issue needs to be evaluated to determine whether it is reasonable.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Created] (CALCITE-6515) Remove constants describing which bugs have been fixed in Bug
xiong duan created CALCITE-6515:
---
Summary: Remove constants describing which bugs have been fixed in
Bug
Key: CALCITE-6515
URL: https://issues.apache.org/jira/browse/CALCITE-6515
Project: Calcite
Issue Type: Improvement
Components: core
Affects Versions: 1.37.0
Reporter: xiong duan
The constants in Bug.java:
{code:java}
/** Whether
* https://issues.apache.org/jira/browse/CALCITE-6375";>[CALCITE-6375]
* Fix failing quidem tests for FORMAT in CAST is fixed. */
public static final boolean CALCITE_6375_FIXED = false; {code}
{code:java}
/** Whether * https://issues.apache.org/jira/browse/CALCITE/issues/CALCITE-6295";> *
[CALCITE-6295] Support IS NOT NULL in Arrow adapter is fixed. */ public
static final boolean CALCITE_6295_FIXED = false;
{code}
{code:java}
/** Whether
* https://issues.apache.org/jira/browse/CALCITE/issues/CALCITE-6296";>
* [CALCITE-6296] Support IS NULL in Arrow adapter is fixed. */
public static final boolean CALCITE_6296_FIXED = false;{code}
These bugs have been fixed, so need to remove it.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Created] (CALCITE-6514) Enable tests about AssertionError while translating IN list that contains null
xiong duan created CALCITE-6514:
---
Summary: Enable tests about AssertionError while translating IN
list that contains null
Key: CALCITE-6514
URL: https://issues.apache.org/jira/browse/CALCITE-6514
Project: Calcite
Issue Type: Improvement
Reporter: xiong duan
{code:java}
/**
* Whether http://issues.eigenbase.org/browse/FRG-327";>issue
* FRG-327: AssertionError while translating IN list that contains null
* is fixed.
*/
public static final boolean FRG327_FIXED = false; {code}
After the test, this issue has been fixed.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Created] (CALCITE-6491) SqlOperatorFixture's checkString doesn't check the actual value against the expected result
xiong duan created CALCITE-6491:
---
Summary: SqlOperatorFixture's checkString doesn't check the actual
value against the expected result
Key: CALCITE-6491
URL: https://issues.apache.org/jira/browse/CALCITE-6491
Project: Calcite
Issue Type: Improvement
Reporter: xiong duan
The Unit Test:
{code:java}
@Test void testOverlayFunc() {
final SqlOperatorFixture f = fixture();
f.setFor(SqlStdOperatorTable.OVERLAY, VmName.EXPAND);
f.checkString("overlay('ABCdef' placing 'abc' from 1)",
"abcd", "VARCHAR(9) NOT NULL");
} {code}
{code:java}
@Test void testOverlayFunc() {
final SqlOperatorFixture f = fixture();
f.setFor(SqlStdOperatorTable.OVERLAY, VmName.EXPAND);
f.checkString("overlay('ABCdef' placing 'abc' from 1)",
"abcdef", "VARCHAR(9) NOT NULL");
} {code}
Both test cases can run successfully. I checked the code and the default
implementation only verifies the result type.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Created] (CALCITE-6485) IN-list contains NULL throws Exception
xiong duan created CALCITE-6485:
---
Summary: IN-list contains NULL throws Exception
Key: CALCITE-6485
URL: https://issues.apache.org/jira/browse/CALCITE-6485
Project: Calcite
Issue Type: Improvement
Reporter: xiong duan
{code:java}
@Test void testInOperation() {
sql("select 1 in (null, '2', '3') as f0 from (values (true, true,
true))").ok();
}
{code}
{code:java}
Conversion to relational algebra failed to preserve datatypes:
validated type:
RecordType(BOOLEAN NOT NULL F0) NOT NULL
converted type:
RecordType(BOOLEAN F0) NOT NULL
rel:
LogicalValues(tuples=[[{ null }]]) {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Created] (CALCITE-6481) Optimize 'VALUES...UNION ALL...VALUES' to a single 'VALUES' when IN-list that the values include NULL is converted to Values
xiong duan created CALCITE-6481:
---
Summary: Optimize 'VALUES...UNION ALL...VALUES' to a single
'VALUES' when IN-list that the values include NULL is converted to Values
Key: CALCITE-6481
URL: https://issues.apache.org/jira/browse/CALCITE-6481
Project: Calcite
Issue Type: Improvement
Reporter: xiong duan
The SQL:
{code:java}
with
t1(a,y) as (select * from (values (1, 2), (3,
null),(7369,null),(7499,30),(null, 20),(null, 5)) as t1)
select *
from t1
where (t1.a,t1.y) in ((1, 2), (3, null),(7369,null),(7499,30),(null, 20),(null,
5));
EnumerableHashJoin(condition=[AND(=($0, $2), =($1, $3))], joinType=[semi])
EnumerableValues(tuples=[[{ 1, 2 }, { 3, null }, { 7369, null }, { 7499, 30
}, { null, 20 }, { null, 5 }]])
EnumerableUnion(all=[true])
EnumerableValues(tuples=[[{ 3, null }]])
EnumerableValues(tuples=[[{ 7369, null }]])
EnumerableValues(tuples=[[{ null, 20 }]])
EnumerableValues(tuples=[[{ null, 5 }]])
EnumerableValues(tuples=[[{ 1, 2 }, { 7499, 30 }]])
!plan
with
t1(a,y) as (select * from (values (1, 2), (3, 5),(7369,6),(7499,30),(2, 20),(3,
5)) as t1)
select *
from t1
where (t1.a,t1.y) in ((1, 2), (3, 3),(7369,3),(7499,30),(1, 20),(3, 5));
EnumerableHashJoin(condition=[AND(=($0, $2), =($1, $3))], joinType=[semi])
EnumerableValues(tuples=[[{ 1, 2 }, { 3, 5 }, { 7369, 6 }, { 7499, 30 }, { 2,
20 }, { 3, 5 }]])
EnumerableValues(tuples=[[{ 1, 2 }, { 3, 3 }, { 7369, 3 }, { 7499, 30 }, { 1,
20 }, { 3, 5 }]])
!plan {code}
If the IN-list includes NULL, Calcite will convert VALUES to UNION ALL.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Created] (CALCITE-6478) JSON functions should return NULL when input is NULL
xiong duan created CALCITE-6478:
---
Summary: JSON functions should return NULL when input is NULL
Key: CALCITE-6478
URL: https://issues.apache.org/jira/browse/CALCITE-6478
Project: Calcite
Issue Type: Improvement
Reporter: xiong duan
Here is the test in H2:
{code:java}
SELECT js,
js IS JSON"json?",
js IS JSON value "json?",
js IS JSON SCALAR "scalar?",
js IS JSON OBJECT "object?",
js IS JSON ARRAY "array?"
FROM (VALUES ('123'),
('"abc"'),
('{"a": "b"}'),
('[1,2]'),
('abc'), NULL) foo(js);
{code}
{code:java}
+--+-+-+---+---+--+
|JS|json?|json?|scalar?|object?|array?|
+--+-+-+---+---+--+
|123 |true |true |true |false |false |
|"abc" |true |true |true |false |false |
|{"a": "b"}|true |true |false |true |false |
|[1,2] |true |true |false |false |true |
|abc |false|false|false |false |false |
|null |null |null |null |null |null |
+--+-+-+---+---+--+
{code}
Same SQL in Calcite:
{code:java}
++---+---+-+-++
| JS | json? | json? | scalar? | object? | array? |
++---+---+-+-++
| "abc" | true | true | true| false | false |
| 123| true | true | true| false | false |
| [1,2] | true | true | false | false | true |
| abc| false | false | false | false | false |
| {"a": "b"} | true | true | false | true| false |
|| false | false | false | false | false |
++---+---+-+-++
{code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Created] (CALCITE-6475) RelToSql converter fails when IN-list that the values include NULL is converted to Values
xiong duan created CALCITE-6475:
---
Summary: RelToSql converter fails when IN-list that the values
include NULL is converted to Values
Key: CALCITE-6475
URL: https://issues.apache.org/jira/browse/CALCITE-6475
Project: Calcite
Issue Type: Bug
Reporter: xiong duan
The SQL
{code:java}
select * from "product" where ("product_id") in (12, null){code}
When we convert the IN-list to OR condition, it can run successfully. But when
we convert it to VALUES, it will throw NPE:
{code:java}
java.lang.NullPointerException
at
org.apache.calcite.rel.rel2sql.SqlImplementor.toSql(SqlImplementor.java:1468)
at
org.apache.calcite.rel.rel2sql.SqlImplementor.toSql(SqlImplementor.java:1416)
at
org.apache.calcite.rel.rel2sql.SqlImplementor$Context.toSql(SqlImplementor.java:702)
at
org.apache.calcite.rel.rel2sql.RelToSqlConverter.lambda$exprList$17(RelToSqlConverter.java:1158){code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Created] (CALCITE-6469) Join on condition generates wrong plan when the condition is IN sub-query
xiong duan created CALCITE-6469:
---
Summary: Join on condition generates wrong plan when the condition
is IN sub-query
Key: CALCITE-6469
URL: https://issues.apache.org/jira/browse/CALCITE-6469
Project: Calcite
Issue Type: Bug
Affects Versions: 1.37.0
Reporter: xiong duan
Assignee: xiong duan
Fix For: 1.38.0
Now Calcite will throw an exception when we execute the SQL:
{code:java}
select * from scott.bonus b left join scott.emp e on e.deptno in (select
deptno from scott.dept b){code}
I will try to rewrite this SQL become:
{code:java}
SELECT *
FROM "SCOTT"."BONUS"
LEFT JOIN ("SCOTT"."EMP" INNER JOIN (SELECT "DEPTNO"
FROM "SCOTT"."DEPT"
GROUP BY "DEPTNO") AS "t" ON "EMP"."DEPTNO" = "t"."DEPTNO") ON TRUE{code}
The SQL :
{code:java}
select * from scott.emp e left join scott.bonus b on e.deptno in (select
deptno from scott.dept b) {code}
I will try to rewrite this SQL become:
{code:java}
SELECT *
FROM "SCOTT"."EMP"
LEFT JOIN ("SCOTT"."BONUS" CROSS JOIN (SELECT "DEPTNO"
FROM "SCOTT"."DEPT"
GROUP BY "DEPTNO") AS "t") ON "EMP"."DEPTNO" = "t"."DEPTNO" {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Created] (CALCITE-6462) VolcanoPlanner internal valid may throw exception when log trace is enabled
xiong duan created CALCITE-6462:
---
Summary: VolcanoPlanner internal valid may throw exception when
log trace is enabled
Key: CALCITE-6462
URL: https://issues.apache.org/jira/browse/CALCITE-6462
Project: Calcite
Issue Type: Bug
Components: core
Affects Versions: 1.37.0
Reporter: xiong duan
Unit test in JdbcAdapterTest:
{code:java}
@Test void testAioob_5() {
CalciteAssert.model(JdbcTest.SCOTT_MODEL)
.query("select *\n"
+ "from scott.emp e left join scott.dept d\n"
+ "on 'job' in (select job from scott.bonus b)")
.runs();
}{code}
Exceptions:
{code:java}
RelSubset [rel#108:RelSubset#7.ENUMERABLE.[]] has wrong best cost {1254.5 rows,
340.5 cpu, 0.0 io}. Correct cost is {1752.0 rows, 337.5 cpu, 0.0 io}{code}
The best RelNode and the bestCode is different.
When log trace is enabled, Tte code will be run:
{code:java}
if (LOGGER.isDebugEnabled()) {
assert isValid(Litmus.THROW);
}{code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[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)
