[jira] [Created] (CALCITE-6306) FILTER clauses for aggregate functions are not supported in MySQL, MariaDB and Starrocks
hongyu guo created CALCITE-6306:
---
Summary: FILTER clauses for aggregate functions are not supported
in MySQL, MariaDB and Starrocks
Key: CALCITE-6306
URL: https://issues.apache.org/jira/browse/CALCITE-6306
Project: Calcite
Issue Type: Bug
Reporter: hongyu guo
{code:sql}
mysql> select sum(x) filter (where x = 1) from t;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near
'(where x = 1) from t' at line 1 {code}
See details in https://modern-sql.com/feature/filter
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Created] (CALCITE-6305) Increase coverage of unit tests for arrow adapter
hongyu guo created CALCITE-6305: --- Summary: Increase coverage of unit tests for arrow adapter Key: CALCITE-6305 URL: https://issues.apache.org/jira/browse/CALCITE-6305 Project: Calcite Issue Type: Sub-task Reporter: hongyu guo Add more unit tests for arrow adapter. for example * casts, including lossy casts * complex filter condition * complex SQL Note: Calcite's support for the arrow feather format is not complete, so before adding the testing process, we may need to implement new features first. -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Created] (CALCITE-6304) Improve the Arrow adapter
hongyu guo created CALCITE-6304:
---
Summary: Improve the Arrow adapter
Key: CALCITE-6304
URL: https://issues.apache.org/jira/browse/CALCITE-6304
Project: Calcite
Issue Type: Improvement
Reporter: hongyu guo
After merging [CALCITE-2040], we obtained the initial version of the arrow
adapter. But, we encountered issues with certain queries throwing an exception
or producing incorrect results in the arrow adapter. For example, consider the
following query:
{code:java}
select "intField", "stringField"
from arrowdata
where "intField" in (0, 1, 2)
{code}
This query will throw an exception, So we need to improve it.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Created] (CALCITE-6299) Support JOIN in arrow adapter
hongyu guo created CALCITE-6299: --- Summary: Support JOIN in arrow adapter Key: CALCITE-6299 URL: https://issues.apache.org/jira/browse/CALCITE-6299 Project: Calcite Issue Type: Sub-task Reporter: hongyu guo -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Created] (CALCITE-6298) Support UNION in arrow adapter
hongyu guo created CALCITE-6298: --- Summary: Support UNION in arrow adapter Key: CALCITE-6298 URL: https://issues.apache.org/jira/browse/CALCITE-6298 Project: Calcite Issue Type: Sub-task Reporter: hongyu guo -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Created] (CALCITE-6297) Support sub-queries in arrow adapter
hongyu guo created CALCITE-6297: --- Summary: Support sub-queries in arrow adapter Key: CALCITE-6297 URL: https://issues.apache.org/jira/browse/CALCITE-6297 Project: Calcite Issue Type: Sub-task Reporter: hongyu guo -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Created] (CALCITE-6296) Support IS NULL in arrow adapter
hongyu guo created CALCITE-6296: --- Summary: Support IS NULL in arrow adapter Key: CALCITE-6296 URL: https://issues.apache.org/jira/browse/CALCITE-6296 Project: Calcite Issue Type: Sub-task Reporter: hongyu guo -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Created] (CALCITE-6295) Support IS NOT NULL in arrow adapter
hongyu guo created CALCITE-6295: --- Summary: Support IS NOT NULL in arrow adapter Key: CALCITE-6295 URL: https://issues.apache.org/jira/browse/CALCITE-6295 Project: Calcite Issue Type: Sub-task Reporter: hongyu guo -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Created] (CALCITE-6294) Support IN filter in arrow adapter
hongyu guo created CALCITE-6294: --- Summary: Support IN filter in arrow adapter Key: CALCITE-6294 URL: https://issues.apache.org/jira/browse/CALCITE-6294 Project: Calcite Issue Type: Sub-task Reporter: hongyu guo -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Created] (CALCITE-6293) Support OR condition for arrow adapter
hongyu guo created CALCITE-6293:
---
Summary: Support OR condition for arrow adapter
Key: CALCITE-6293
URL: https://issues.apache.org/jira/browse/CALCITE-6293
Project: Calcite
Issue Type: Sub-task
Reporter: hongyu guo
for example
{code:java}
String sql = "select \"intField\", \"stringField\"\n"
+ "from arrowdata\n"
+ "where \"intField\"=12 or \"stringField\"='12'"; {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Created] (CALCITE-6292) Support more arrow type
hongyu guo created CALCITE-6292:
---
Summary: Support more arrow type
Key: CALCITE-6292
URL: https://issues.apache.org/jira/browse/CALCITE-6292
Project: Calcite
Issue Type: Sub-task
Reporter: hongyu guo
All arrow type:
{code:java}
public static enum ArrowTypeID {
Null(Type.Null),
Struct(Type.Struct_),
List(Type.List),
LargeList(Type.LargeList),
FixedSizeList(Type.FixedSizeList),
Union(Type.Union),
Map(Type.Map),
Int(Type.Int),
FloatingPoint(Type.FloatingPoint),
Utf8(Type.Utf8),
LargeUtf8(Type.LargeUtf8),
Binary(Type.Binary),
LargeBinary(Type.LargeBinary),
FixedSizeBinary(Type.FixedSizeBinary),
Bool(Type.Bool),
Decimal(Type.Decimal),
Date(Type.Date),
Time(Type.Time),
Timestamp(Type.Timestamp),
Interval(Type.Interval),
Duration(Type.Duration),
NONE(Type.NONE);
}
{code}
we support now:
{code:java}
public static ArrowFieldType of(ArrowType arrowType) {
switch (arrowType.getTypeID()) {
case Int:
int bitWidth = ((ArrowType.Int) arrowType).getBitWidth();
switch (bitWidth) {
case 64:
return LONG;
case 32:
return INT;
case 16:
return SHORT;
case 8:
return BYTE;
default:
throw new IllegalArgumentException("Unsupported Int bit width: " +
bitWidth);
}
case Bool:
return BOOLEAN;
case Utf8:
return STRING;
case FloatingPoint:
FloatingPointPrecision precision = ((ArrowType.FloatingPoint)
arrowType).getPrecision();
switch (precision) {
case SINGLE:
return FLOAT;
case DOUBLE:
return DOUBLE;
default:
throw new IllegalArgumentException("Unsupported Floating point
precision: " + precision);
}
case Date:
return DATE;
case Decimal:
return DECIMAL;
default:
throw new IllegalArgumentException("Unsupported type: " + arrowType);
}
}
{code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Created] (CALCITE-6291) Support converting ArrowTable to Queryable
hongyu guo created CALCITE-6291: --- Summary: Support converting ArrowTable to Queryable Key: CALCITE-6291 URL: https://issues.apache.org/jira/browse/CALCITE-6291 Project: Calcite Issue Type: Sub-task Reporter: hongyu guo -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Created] (CALCITE-6230) Jira case collections
hongyu guo created CALCITE-6230: --- Summary: Jira case collections Key: CALCITE-6230 URL: https://issues.apache.org/jira/browse/CALCITE-6230 Project: Calcite Issue Type: Task Reporter: hongyu guo syntax: CALCITE-5852: MERGE INTO CALCITE-5301: AT TIME ZONE CALCITE-5386: LIKE (ANY | SOME | ALL) CALCITE-5216: Cannot parse parenthesized nested WITH clause CALCITE-5205: Supports hint option as string and numeric literal CALCITE-5168: Allow AS after parenthesized JOIN CALCITE-5084: Support ROWS syntax with TABLESAMPLE CALCITE-5066: Support variables with “@" and “@@" prefixes (like MySQL) CALCITE-4705: Support hints like /*+ skewjoin(a(c0, c1)) */ CALCITE-4455: Babel parser support Spark INSERT OVERWRITE TABLE/DIRECTORY statement CALCITE-3970: Table-valued function TUMBLE uses non-standard syntax CALCITE-5681: Support authorization via GRANT and REVOKE DDL commands data type: CALCITE-5346: type aliases CALCITE-4918: Add a VARIANT data type rule and optimization: CALCITE-4843: optimize ALL,SOME sub-query list when op is the <, <=,> or >= CALCITE-4052: Enable Top-down Optimization dialect: CALCITE-4782: Allow 'CAST(numeric AS BOOLEAN)' (if enabled by conformance) function: CALCITE-5809: enable already available functions in Apache Spark Library CALCITE-5087: Support bitwise operators CALCITE-4521: Support User Defined Table-valued Function CALCITE-4484: Add UNIQUE_VALUE(x) aggregate function, that throws if x is not unique CALCITE-3683: Enhanced MATH Function CALCITE-3646: MySQL compression functions CALCITE-2031: Implement more OpenGIS functions CALCITE-2871: Implement JSON_TABLE table function -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Created] (CALCITE-6183) The second parameter of RexProgramBuilder#registerInternal is always false
hongyu guo created CALCITE-6183: --- Summary: The second parameter of RexProgramBuilder#registerInternal is always false Key: CALCITE-6183 URL: https://issues.apache.org/jira/browse/CALCITE-6183 Project: Calcite Issue Type: Improvement Reporter: hongyu guo -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Created] (CALCITE-6116) Add EXISTS function (enabled in Spark library)
hongyu guo created CALCITE-6116:
---
Summary: Add EXISTS function (enabled in Spark library)
Key: CALCITE-6116
URL: https://issues.apache.org/jira/browse/CALCITE-6116
Project: Calcite
Issue Type: Improvement
Components: core
Affects Versions: 1.36.0
Reporter: hongyu guo
Assignee: hongyu guo
Fix For: 1.37.0
exists(expr, pred) - Tests whether a predicate holds for one or more elements
in the array.
{code:sql}
> SELECT `EXISTS`(array(1, 2, 3), x -> x % 2 == 0);
true
> SELECT `EXISTS`(array(1, 2, 3), x -> x % 2 == 10);
false
> SELECT `EXISTS`(array(1, null, 3), x -> x % 2 == 0);
NULL
> SELECT `EXISTS`(array(0, null, 2, 3, null), x -> x IS NULL);
true
> SELECT `EXISTS`(array(1, 2, 3), x -> x IS NULL);
false
{code}
In Calcite, EXISTS is a keyword, so we need to specify the function with back
quotes.
Moreover, `EXISTS` is a higher-order function, and if we want to support
higher-order functions in Calcite, we must first support lambda expressions
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Created] (CALCITE-6081) Remove bouncycastle dependency
hongyu guo created CALCITE-6081: --- Summary: Remove bouncycastle dependency Key: CALCITE-6081 URL: https://issues.apache.org/jira/browse/CALCITE-6081 Project: Calcite Issue Type: Improvement Affects Versions: 1.35.0 Reporter: hongyu guo Assignee: hongyu guo Fix For: 1.36.0 Bouncycastle is unused in Calcite -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Created] (CALCITE-6076) Table function can not refer other table columns
hongyu guo created CALCITE-6076:
---
Summary: Table function can not refer other table columns
Key: CALCITE-6076
URL: https://issues.apache.org/jira/browse/CALCITE-6076
Project: Calcite
Issue Type: Bug
Components: core
Affects Versions: 1.35.0
Reporter: hongyu guo
SQL:
{code:java}
select rmp.i from emp, table(ramp(emp.deptno)) as rmp {code}
will throw an error:
{code:java}
Table 'EMP' not found {code}
during the validation.
this SQL fails because the scope of the table function during the verification
is CatalogScope, not JoinScope.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Created] (CALCITE-6022) Support "CREATE TABLE ... LIKE" DDL
hongyu guo created CALCITE-6022:
---
Summary: Support "CREATE TABLE ... LIKE" DDL
Key: CALCITE-6022
URL: https://issues.apache.org/jira/browse/CALCITE-6022
Project: Calcite
Issue Type: New Feature
Components: server
Affects Versions: 1.35.0
Reporter: hongyu guo
Fix For: 1.36.0
"CREATE TABLE ... LIKE" is SQL standard syntax, and some other dbms(mysql,
postgresql, sparksql) support it. So I think it is worth implementing.
syntax:
{code:java}
CREATE TABLE [IF NOT EXISTS] table_name
LIKE source_table [ like_option ... ]{code}
And for the `like_option`, I think the syntax of postgreSQL is more flexible
and powerful, and I believe it is reasonable to be similar to postgreSQL. But
there are not so many attributes in calcite, just keep CONSTRAINTS DEFAULTS and
ALL.
{code:java}
{ INCLUDING | EXCLUDING } { CONSTRAINTS | DEFAULTS | ALL }{code}
Some related link
[https://www.postgresql.org/docs/current/sql-createtable.html]
[https://ronsavage.github.io/SQL/sql-2003-2.bnf.html#table%20definition]
[https://dev.mysql.com/doc/refman/8.0/en/create-table-like.html]
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Created] (CALCITE-5993) Add CODE_POINTS_TO_STRING, TO_CODE_POINTS function (enabled in BigQuery library)
hongyu guo created CALCITE-5993: --- Summary: Add CODE_POINTS_TO_STRING, TO_CODE_POINTS function (enabled in BigQuery library) Key: CALCITE-5993 URL: https://issues.apache.org/jira/browse/CALCITE-5993 Project: Calcite Issue Type: New Feature Components: core Affects Versions: 1.35.0 Reporter: hongyu guo Assignee: hongyu guo Fix For: 1.36.0 CODE_POINTS_TO_STRING: Takes an array of Unicode code points as ARRAY and returns a STRING. TO_CODE_POINTS: Takes a STRING or BYTES value and returns an array of INT64 values that represent code points or extended ASCII character values. If value is a STRING, each element in the returned array represents a code point. Each code point falls within the range of [0, 0xD7FF] and [0xE000, 0x10]. If value is BYTES, each element in the array is an extended ASCII character value in the range of [0, 255]. See more in [code_points_to_string|https://cloud.google.com/bigquery/docs/reference/standard-sql/functions-and-operators#code_points_to_string] and [to_code_points|https://cloud.google.com/bigquery/docs/reference/standard-sql/functions-and-operators#to_code_points] -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Created] (CALCITE-5963) Simplify 'x OR NOT(x)' to TRUE
hongyu guo created CALCITE-5963: --- Summary: Simplify 'x OR NOT(x)' to TRUE Key: CALCITE-5963 URL: https://issues.apache.org/jira/browse/CALCITE-5963 Project: Calcite Issue Type: New Feature Reporter: hongyu guo When unknownAs is TRUE, or type of x is not nullable, Calcite can simpify: x OR NOT(x) ==> TRUE -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Created] (CALCITE-5956) Support parsing non-standand collection type
hongyu guo created CALCITE-5956:
---
Summary: Support parsing non-standand collection type
Key: CALCITE-5956
URL: https://issues.apache.org/jira/browse/CALCITE-5956
Project: Calcite
Issue Type: New Feature
Components: core
Affects Versions: 1.35.0
Reporter: hongyu guo
Assignee: hongyu guo
Fix For: 1.36.0
Calcite support SQL standard collection type, for example
{code:java}
INTEGER ARRAY;
INTEGER ARRAY ARRAY;
VARCHAR(5) MULTISET;
INTEGER MULTISET ARRAY;{code}
A lots of DBMS support a dialect `ARRAY` or `MULITSET`
I think Calcite also can support this dialect in Parser.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Created] (CALCITE-5954) Support MERGE in RelToSqlConverter
hongyu guo created CALCITE-5954: --- Summary: Support MERGE in RelToSqlConverter Key: CALCITE-5954 URL: https://issues.apache.org/jira/browse/CALCITE-5954 Project: Calcite Issue Type: New Feature Components: core Affects Versions: 1.35.0 Reporter: hongyu guo Assignee: hongyu guo Fix For: 1.36.0 We have implemented validation for the *MERGE INTO* clause, but Calcite does not support converting TableModify to SqlMerge. -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Created] (CALCITE-5935) Add CODE_POINTS_TO_BYTES function(enabled in BigQuery library)
hongyu guo created CALCITE-5935: --- Summary: Add CODE_POINTS_TO_BYTES function(enabled in BigQuery library) Key: CALCITE-5935 URL: https://issues.apache.org/jira/browse/CALCITE-5935 Project: Calcite Issue Type: New Feature Reporter: hongyu guo -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Created] (CALCITE-5922) POSITION signature incorrect for SparkSQL
hongyu guo created CALCITE-5922:
---
Summary: POSITION signature incorrect for SparkSQL
Key: CALCITE-5922
URL: https://issues.apache.org/jira/browse/CALCITE-5922
Project: Calcite
Issue Type: Bug
Components: core
Affects Versions: 1.35.0
Reporter: hongyu guo
Fix For: 1.36.0
In SparkSQL, POSITION(substr, str[, pos]) function only accept comma-separated
when there are 3 arguments.
For example:
{code:java}
// SparkSQL accepted SQL
select POSITION('a', 'abc', 1);{code}
Calcite will use the IN and FROM keyword to separate the input arguments when
unparsing.
{code:java}
// Calcite accepted and unparsed SQL
select POSITION('a' IN 'abc' FROM 1){code}
For 2 augument inputs, SparkSQL accept both syntaxes, So I think we should
write a rule in SparkSqlDialect to convert keyword-separated syntax to
comma-separted syntax for POSITION function.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Created] (CALCITE-5852) Enhance for MERGE INTO statement
hongyu guo created CALCITE-5852:
---
Summary: Enhance for MERGE INTO statement
Key: CALCITE-5852
URL: https://issues.apache.org/jira/browse/CALCITE-5852
Project: Calcite
Issue Type: Improvement
Reporter: hongyu guo
In Calcite, MERGE INTO statement:
{code:sql}
merge:
MERGE INTO tablePrimary [ [ AS ] alias ]
USING tablePrimary
ON booleanExpression
[ WHEN MATCHED THEN UPDATE SET assign [, assign ]* ]
[ WHEN NOT MATCHED THEN INSERT VALUES '(' value [ , value ]* ')' ]
{code}
SQL-2011 and postgres support for :
{code:sql}
-- when matched clause
WHEN MATCHED [ AND ]
THEN
-- when not matched clause
WHEN NOT MATCHED [ AND ]
THEN
{code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Created] (CALCITE-5817) The lead field for the interval type should not be validated
hongyu guo created CALCITE-5817:
---
Summary: The lead field for the interval type should not be
validated
Key: CALCITE-5817
URL: https://issues.apache.org/jira/browse/CALCITE-5817
Project: Calcite
Issue Type: Bug
Components: core
Affects Versions: 1.34.0
Reporter: hongyu guo
Assignee: hongyu guo
Calcite restricts the lead field of the interval type to a maximum of three
digits through the
[SqlIntervalQualifier#checkLeadFieldInRange|https://github.com/apache/calcite/blob/main/core/src/main/java/org/apache/calcite/sql/SqlIntervalQualifier.java#L472].
There are some example
{code:sql}
0: jdbc:calcite:model=model.json> select interval '100' day;
Error: Error while executing SQL "select interval '100' day": From line 1,
column 8 to line 1, column 25: Interval field value 100 exceeds precision of
DAY(2) field (state=,code=0)
0: jdbc:calcite:model=model.json> select INTERVAL '100:50' HOUR TO MINUTE;
Error: Error while executing SQL "select INTERVAL '100:50' HOUR TO MINUTE":
From line 1, column 8 to line 1, column 39: Interval field value 100 exceeds
precision of HOUR(2) field (state=,code=0)
{code}
In postgres and spark-sql, this restriction does not exist.
{code:sql}
-- postgres
postgres=# select INTERVAL '100' second;
interval
--
00:01:40
(1 row)
postgres=# select INTERVAL '100:5' HOUR TO MINUTE;
interval
---
100:05:00
(1 row)
-- mysql (In mysql, directly using the INTERVAL keyword with a string value
cannot be executed)
mysql> select current_date + INTERVAL '100' HOUR;
+-+
| current_date + INTERVAL '100' HOUR |
+-+
| 2023-07-08 04:00:00 |
+-+
1 row in set (0.00 sec)
-- spark-sql
spark-sql> select INTERVAL '100:5' HOUR TO MINUTE;
INTERVAL '100:05' HOUR TO MINUTE
4 04:05:00.0
Time taken: 0.041 seconds, Fetched 1 row(s)
{code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Created] (CALCITE-5816) Wrong result for query with LEFT SEMI JOIN
hongyu guo created CALCITE-5816:
---
Summary: Wrong result for query with LEFT SEMI JOIN
Key: CALCITE-5816
URL: https://issues.apache.org/jira/browse/CALCITE-5816
Project: Calcite
Issue Type: Bug
Components: babel, core
Affects Versions: 1.34.0
Reporter: hongyu guo
Assignee: hongyu guo
A LEFT SEMI JOIN can only return columns from the left-hand table.
But calcite also return right-hand table columns.
[select.iq|https://github.com/apache/calcite/blob/c0f2e9b50e7e61fe732a07d04786d5df95aa557e/babel/src/test/resources/sql/select.iq]
{code:sql}
# LEFT SEMI JOIN (Hive only)
SELECT *
FROM emp LEFT SEMI JOIN dept ON emp.deptno = dept.deptno;
SELECT "EMP"."EMPNO", "EMP"."ENAME", "EMP"."JOB", "EMP"."MGR",
"EMP"."HIREDATE", "EMP"."SAL", "EMP"."COMM", "EMP"."DEPTNO", "DEPT"."DEPTNO" AS
"DEPTNO0", "DEPT"."DNAME", "DEPT"."LOC"
FROM "scott"."EMP" AS "EMP"
LEFT SEMI JOIN "scott"."DEPT" AS "DEPT" ON "EMP"."DEPTNO" = "DEPT"."DEPTNO"
!explain-validated-on hive
{code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
