[jira] [Created] (CALCITE-6101) SqlCollectionTypeNameSpec should preserve the nullability property of the element type
Dmitry Sysolyatin created CALCITE-6101: -- Summary: SqlCollectionTypeNameSpec should preserve the nullability property of the element type Key: CALCITE-6101 URL: https://issues.apache.org/jira/browse/CALCITE-6101 Project: Calcite Issue Type: Bug Components: core Affects Versions: 1.35.0, 1.36.0 Reporter: Dmitry Sysolyatin SqlCollectionTypeNameSpec uses SqlTypeNameSpec for the elementType property, but SqlTypeNameSpec does not provide information about nullability. Therefore, SqlDataTypeSpec should be used for elementType instead of SqlTypeNameSpec, as was done for SqlMapTypeNameSpec and SqlRowTypeNameSpec. -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Created] (CALCITE-6100) The equalsDeep of SqlRowTypeNameSpec returns wrong result
Dmitry Sysolyatin created CALCITE-6100:
--
Summary: The equalsDeep of SqlRowTypeNameSpec returns wrong result
Key: CALCITE-6100
URL: https://issues.apache.org/jira/browse/CALCITE-6100
Project: Calcite
Issue Type: Bug
Components: core
Affects Versions: 1.35.0, 1.36.0
Reporter: Dmitry Sysolyatin
SqlRowTypeNameSpec#equalsDeep method uses reference comparison for objects
inside fieldTypes list instead of using SqlDataTypeSpec#equalsDeep.
Testcase: Add to `SqlEqualsDeepTest` the following lines of code:
{code:java}
@Test
void testRowEqualsDeep() throws SqlParseException {
assertEqualsDeep("CAST(a AS ROW(field INTEGER))",
"CAST(a AS ROW(field INTEGER))", true);
}
{code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Created] (CALCITE-5779) Implicit column alias for single-column table function does not work
Dmitry Sysolyatin created CALCITE-5779:
--
Summary: Implicit column alias for single-column table function
does not work
Key: CALCITE-5779
URL: https://issues.apache.org/jira/browse/CALCITE-5779
Project: Calcite
Issue Type: Bug
Components: core
Affects Versions: 1.34.0
Reporter: Dmitry Sysolyatin
Implicit column alias for single-column table function works only for UNNEST.
But should work for every table function.
For example, PostgreSQL returns the same result for
{code:java}
SELECT i FROM UNNEST(ARRAY(1,2)) as i;
SELECT i FROM generate_series(1,2) as i;
i
---
1
2
(2 rows)
{code}
Calcite throws"Column 'i' not found in any table" exception for
{code:java}
SELECT i FROM generate_series(1,2) as i
{code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Created] (CALCITE-5668) When parsing SQL in PostgreSQL dialect, allow unquoted table names to contain dollar sign
Dmitry Sysolyatin created CALCITE-5668: -- Summary: When parsing SQL in PostgreSQL dialect, allow unquoted table names to contain dollar sign Key: CALCITE-5668 URL: https://issues.apache.org/jira/browse/CALCITE-5668 Project: Calcite Issue Type: Bug Components: babel Affects Versions: 1.34.0 Reporter: Dmitry Sysolyatin According PostgreSQL documentation [1]: ??SQL identifiers and key words must begin with a letter (a-z, but also letters with diacritical marks and non-Latin letters) or an underscore (_). Subsequent characters in an identifier or key word can be letters, underscores, digits (0-9), or dollar signs ($).?? To implement it, I suggest using the same approach as in CALCITE-4246. [1] https://www.postgresql.org/docs/8.1/sql-syntax.html#SQL-SYNTAX-IDENTIFIERS -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Created] (CALCITE-5634) Add LEAST, GREATEST for PostgreSQL
Dmitry Sysolyatin created CALCITE-5634: -- Summary: Add LEAST, GREATEST for PostgreSQL Key: CALCITE-5634 URL: https://issues.apache.org/jira/browse/CALCITE-5634 Project: Calcite Issue Type: Bug Components: core Affects Versions: 1.34.0 Reporter: Dmitry Sysolyatin The LEAST and GREATEST functions have been implemented for BigQuery and Oracle, but haven't been added for PostgreSQL. PostgreSQL supports LEAST, GREATEST as well [1] [1] https://www.postgresql.org/docs/15/functions-conditional.html#FUNCTIONS-GREATEST-LEAST -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Created] (CALCITE-5633) Allow table functions to be inside select list
Dmitry Sysolyatin created CALCITE-5633:
--
Summary: Allow table functions to be inside select list
Key: CALCITE-5633
URL: https://issues.apache.org/jira/browse/CALCITE-5633
Project: Calcite
Issue Type: Bug
Affects Versions: 1.34.0
Reporter: Dmitry Sysolyatin
Currently, it is impossible to use table functions inside select list.
For example:
{code}
SELECT UNNEST(ARRAY[1,2,3]);
{code}
{code}
SELECT table(generate_series(1,2)); // generate_series is user defined table
function
{code}
Engines like PostgreSQL support it[1]:
{quote}Functions returning sets can also be called in the select list of a
query. For each row that the query generates by itself, the set-returning
function is invoked, and an output row is generated for each element of the
function's result set
{quote}
[1] https://www.postgresql.org/docs/current/xfunc-sql.html
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Created] (CALCITE-5612) Babel parser should support PostgreSQL's SET TRANSACTION command
Dmitry Sysolyatin created CALCITE-5612:
--
Summary: Babel parser should support PostgreSQL's SET TRANSACTION
command
Key: CALCITE-5612
URL: https://issues.apache.org/jira/browse/CALCITE-5612
Project: Calcite
Issue Type: Bug
Components: babel
Affects Versions: 1.34.0
Reporter: Dmitry Sysolyatin
SET TRANSACTION — set the characteristics of the current transaction
Specification [1]:
{code}
SET TRANSACTION transaction_mode [, ...]
SET TRANSACTION SNAPSHOT snapshot_id
SET SESSION CHARACTERISTICS AS TRANSACTION transaction_mode [, ...]
where transaction_mode is one of:
ISOLATION LEVEL { SERIALIZABLE | REPEATABLE READ | READ COMMITTED | READ
UNCOMMITTED }
READ WRITE | READ ONLY
[ NOT ] DEFERRABLE
{code}
[1] https://www.postgresql.org/docs/current/sql-set-transaction.html
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Created] (CALCITE-5532) CompositeOperandsTypeChecking should check operands without type coercion first
Dmitry Sysolyatin created CALCITE-5532:
--
Summary: CompositeOperandsTypeChecking should check operands
without type coercion first
Key: CALCITE-5532
URL: https://issues.apache.org/jira/browse/CALCITE-5532
Project: Calcite
Issue Type: Bug
Components: core
Affects Versions: 1.33.0
Reporter: Dmitry Sysolyatin
If define an operator with the following type checker:
{code}
SqlSingleOperandTypeChecker operandTypeChecker = OperandTypes.or(
OperandTypes.family(SqlTypeFamily.STRING, SqlTypeFamily.STRING)
.and(OperandTypes.SAME_SAME),
OperandTypes.family(SqlTypeFamily.INTEGER, SqlTypeFamily.INTEGER)
.and(OperandTypes.SAME_SAME));
{code}
and pass two operands with INTEGER type to this type checker. Then they will be
wrapped into CAST operator which will cast them to VARCHAR. But they shouldn't
be casted to VARCHAR.
Testcase:
{code:java}
@Test void testCompositeOperandTypeWithoutCast() {
SqlValidator validator = SqlTestFactory.INSTANCE.createValidator();
SqlSingleOperandTypeChecker operandTypeChecker = OperandTypes.or(
OperandTypes.family(SqlTypeFamily.STRING, SqlTypeFamily.STRING)
.and(OperandTypes.SAME_SAME),
OperandTypes.family(SqlTypeFamily.INTEGER, SqlTypeFamily.INTEGER)
.and(OperandTypes.SAME_SAME));
SqlBinaryOperator op = new SqlBinaryOperator(
"~",
SqlKind.OTHER,
60,
true,
null,
null,
null);
List args = ImmutableList.of(
SqlLiteral.createExactNumeric("20", SqlParserPos.ZERO),
SqlLiteral.createExactNumeric("30", SqlParserPos.ZERO));
SqlCallBinding binding = new SqlCallBinding(
validator,
new EmptyScope((SqlValidatorImpl) validator),
new SqlBasicCall(op, args, SqlParserPos.ZERO));
List typesBeforeChecking =
ImmutableList.of(binding.getOperandType(0), binding.getOperandType(1));
operandTypeChecker.checkOperandTypes(binding, false);
# It fails
assertEquals(typesBeforeChecking.get(0), binding.getOperandType(0));
assertEquals(typesBeforeChecking.get(1), binding.getOperandType(1));
}
{code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Created] (CALCITE-5468) SqlToRelConverter should register sub-queries inside ORDER BY clause for queries without aggregation
Dmitry Sysolyatin created CALCITE-5468:
--
Summary: SqlToRelConverter should register sub-queries inside
ORDER BY clause for queries without aggregation
Key: CALCITE-5468
URL: https://issues.apache.org/jira/browse/CALCITE-5468
Project: Calcite
Issue Type: Bug
Components: core
Affects Versions: 1.32.0
Reporter: Dmitry Sysolyatin
At the moment the following query throws exception:
{code}
@Test void testOrderByWithSubQuery() {
String sql = "SELECT empno\n"
+ "FROM emp\n"
+ "ORDER BY\n"
+ "CASE WHEN empno IN (1,2) THEN 0 ELSE 1 END";
sql(sql).ok();
}
{code}
{code}
while converting CASE WHEN `EMP`.`EMPNO` IN (1, 2) THEN 0 ELSE 1 END
java.lang.RuntimeException: while converting CASE WHEN `EMP`.`EMPNO` IN (1, 2)
THEN 0 ELSE 1 END
at
org.apache.calcite.sql2rel.ReflectiveConvertletTable.lambda$registerNodeTypeMethod$1(ReflectiveConvertletTable.java:98)
{code}
It happens because SqlToRelConverter does not register sub-query inside ORDER
BY if query does not have aggregation
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Created] (CALCITE-5460) Types.className should replace '$' to '.' if name of class contains $
Dmitry Sysolyatin created CALCITE-5460: -- Summary: Types.className should replace '$' to '.' if name of class contains $ Key: CALCITE-5460 URL: https://issues.apache.org/jira/browse/CALCITE-5460 Project: Calcite Issue Type: Bug Components: linq4j Affects Versions: 1.32.0 Reporter: Dmitry Sysolyatin I faced with this problem when I was trying to use Types.className with scala object. Internally, scala object is instance of class "$". If Types.className is applied to this class then $ will be replaced by '.'. But it is wrong behaviour The same behaviour will be for any class name which contain symbol $. -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Created] (CALCITE-5426) BlockBuilder should not reuse mutable objects
Dmitry Sysolyatin created CALCITE-5426:
--
Summary: BlockBuilder should not reuse mutable objects
Key: CALCITE-5426
URL: https://issues.apache.org/jira/browse/CALCITE-5426
Project: Calcite
Issue Type: Bug
Components: linq4j
Affects Versions: 1.32.0
Reporter: Dmitry Sysolyatin
Inside BlockBuilder there is an optimization that replaces an expression with a
variable if the expressions are equal and have the final modifier.
But this optimization can cause problems when used with a mutable objects (One
of the problems has been found in CALCITE-5388):
For example:
{code:java}
@Test void testReuseCollectionExpression() throws NoSuchMethodException {
Method putMethod = HashMap.class.getMethod("put", Object.class,
Object.class);
Method sizeMethod = HashMap.class.getMethod("size");
Expression multiMapParent = b.append("multiMap",
Expressions.new_(Types.of(HashMap.class)));
b.add(Expressions.statement(
Expressions.call(multiMapParent, putMethod, Expressions.box(ONE),
Expressions.box(ONE;
BlockBuilder nested = new BlockBuilder(true, b);
Expression multiMapNested = nested.append("multiMap",
Expressions.new_(Types.of(HashMap.class)));
nested.add(Expressions.statement(
Expressions.call(multiMapParent, putMethod, Expressions.box(TWO),
Expressions.box(TWO;
nested.add(Expressions.call(multiMapNested, sizeMethod));
b.add(nested.toBlock());
b.append(Expressions.call(multiMapParent, sizeMethod));
// It is wrong output. Map should be reused
assertEquals(
"{\n"
+ " final java.util.HashMap multiMap = new java.util.HashMap();\n"
+ " multiMap.put(Integer.valueOf(1), Integer.valueOf(1));\n"
+ " {\n"
+ "multiMap.put(Integer.valueOf(2), Integer.valueOf(2));\n"
+ "return multiMap.size();\n"
+ " }\n"
+ " return multiMap.size();\n"
+ "}\n",
b.toBlock().toString());
}
{code}
Are there any tests that prove that this optimization significantly improves
performance?
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Created] (CALCITE-5403) PostgreSQL dialect should support SET, RESET, BEGIN, SHOW, ROLLBACK, COMMIT
Dmitry Sysolyatin created CALCITE-5403:
--
Summary: PostgreSQL dialect should support SET, RESET, BEGIN,
SHOW, ROLLBACK, COMMIT
Key: CALCITE-5403
URL: https://issues.apache.org/jira/browse/CALCITE-5403
Project: Calcite
Issue Type: Bug
Components: babel
Reporter: Dmitry Sysolyatin
Assignee: Dmitry Sysolyatin
Babel parser can not parse BEGIN [1], SHOW [2], ROLLBACK [3], COMMIT [4]
commands at the moment.
It can parse SET [5] and RESET [6], but not completely. For instance, it can
not parse the following statement:
{code:java}
SET SCHEMA public,public,"$user"
{code}
The syntax for these commands is as follows:
* BEGIN [1]:
{code:java}
BEGIN [ WORK | TRANSACTION ] [ transaction_mode [, ...] ]
where transaction_mode is one of: ISOLATION LEVEL { SERIALIZABLE | REPEATABLE
READ | READ COMMITTED | READ UNCOMMITTED } READ WRITE | READ ONLY [ NOT ]
DEFERRABLE
{code}
* SHOW [2]:
{code:java}
SHOW ( | )
{code}
* ROLLBACK [3]:
{code:java}
ROLLBACK [ WORK | TRANSACTION ] [ AND [ NO ] CHAIN ]
{code}
* COMMIT [4]:
{code:java}
COMMIT [ WORK | TRANSACTION ] [ AND [ NO ] CHAIN ]
{code}
* SET [5]
{code:java}
SET [ SESSION | LOCAL ] configuration_parameter { TO | = } { value | 'value' |
DEFAULT }
SET [ SESSION | LOCAL ] TIME ZONE { value | 'value' | LOCAL | DEFAULT }
SET [ SESSION | LOCAL ] { SCHEMA | SEED | NAMES } value
Values can be specified as string constants, identifiers, numbers, or
comma-separated lists of these
{code}
* RESET [6]
{code:java}
RESET ;
{code}
[1] [https://www.postgresql.org/docs/current/sql-begin.html]
[2] [https://www.postgresql.org/docs/current/sql-show.html]
[3] [https://www.postgresql.org/docs/current/sql-rollback.html]
[4] [https://www.postgresql.org/docs/current/sql-commit.html]
[5] [https://www.postgresql.org/docs/current/sql-set.html]
[6] [https://www.postgresql.org/docs/current/sql-reset.html]
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Created] (CALCITE-5400) EnumerableCalc should not generate Enumerable.current with a non-idempotent function inside
Dmitry Sysolyatin created CALCITE-5400:
--
Summary: EnumerableCalc should not generate Enumerable.current
with a non-idempotent function inside
Key: CALCITE-5400
URL: https://issues.apache.org/jira/browse/CALCITE-5400
Project: Calcite
Issue Type: Bug
Components: core
Affects Versions: 1.32.0
Reporter: Dmitry Sysolyatin
The problem has been found inside CALCITE-5388. The following query can return
wrong result:
{code:java}
with
CTE1(rand1, val1) as ( select RAND_INTEGER(2), id from (values (1), (2)) as
Vals1(id) ),
CTE2(rand2, val2) as ( select RAND_INTEGER(2), id from (values (1), (2)) as
Vals2(id) )
select
CTE1.rand1,CTE1.val1,
CTE2.rand2,
CTE2.val2
from
CTE1,
CTE2
where
CTE1.rand1 = CTE2.rand2
{code}
For instance it can return:
|RAND1|VAL1|RAND2|VAL2|
|1|1|1|1|
|*{color:#ff}0{color}*|1|*{color:#ff}1{color}*|2|
The problem is that EnumerableCalc generates Enumerable class that uses
non-idempotent function `randInteger` inside 'current()' method:
{code:java}
new org.apache.calcite.linq4j.AbstractEnumerable() {
public org.apache.calcite.linq4j.Enumerator enumerator() {
return new org.apache.calcite.linq4j.Enumerator() {
<.>
public Object current() {
return new Object[]{
$L4J$C$new_org_apache_calcite_runtime_RandomFunction_.randInteger(2),
org.apache.calcite.runtime.SqlFunctions.toInt(inputEnumerator.current())};
}
static final org.apache.calcite.runtime.RandomFunction
$L4J$C$new_org_apache_calcite_runtime_RandomFunction_ = new
org.apache.calcite.runtime.RandomFunction();
};
}
};
{code}
if current() is called twice it can produce different results. What exactly
happens inside EnumerableDefault.hashEquiJoin_ function (outers.current() is
called inside moveNext() and current())
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Created] (CALCITE-5390) RelCorrelator throws NullPointerException
Dmitry Sysolyatin created CALCITE-5390:
--
Summary: RelCorrelator throws NullPointerException
Key: CALCITE-5390
URL: https://issues.apache.org/jira/browse/CALCITE-5390
Project: Calcite
Issue Type: Bug
Components: core
Reporter: Dmitry Sysolyatin
The current query throws NullPointerException
{code}
SELECT
(SELECT 1 FROM emp d WHERE d.job = a.job LIMIT 1) AS t1,
(SELECT a.job = 'PRESIDENT' FROM emp s LIMIT 1) as t2
FROM emp a;
{code}
Test case -
https://github.com/apache/calcite/commit/46fe9bc456f2d34cf7dccd29829c9e85abe69d5f
Logical plan before it fails:
{code}
LogicalProject(T1=[$8], T2=[$9])
LogicalCorrelate(correlation=[$cor0], joinType=[left], requiredColumns=[{2}])
LogicalCorrelate(correlation=[$cor0], joinType=[left],
requiredColumns=[{2}])
LogicalTableScan(table=[[scott, EMP]])
LogicalAggregate(group=[{}], agg#0=[SINGLE_VALUE($0)])
LogicalSort(fetch=[1])
LogicalProject(EXPR$0=[1])
LogicalFilter(condition=[=($2, $cor0.JOB)])
LogicalTableScan(table=[[scott, EMP]])
LogicalAggregate(group=[{}], agg#0=[SINGLE_VALUE($0)])
LogicalSort(fetch=[1])
LogicalProject(EXPR$0=[=($cor0.JOB, 'PRESIDENT')])
LogicalTableScan(table=[[scott, EMP]])
{code}
Stack trace:
{code}
Caused by: java.lang.NullPointerException
at java.util.Objects.requireNonNull(Objects.java:203)
at
org.apache.calcite.sql2rel.RelDecorrelator.createValueGenerator(RelDecorrelator.java:833)
at
org.apache.calcite.sql2rel.RelDecorrelator.decorrelateInputWithValueGenerator(RelDecorrelator.java:1028)
at
org.apache.calcite.sql2rel.RelDecorrelator.decorrelateRel(RelDecorrelator.java:764)
at
org.apache.calcite.sql2rel.RelDecorrelator.decorrelateRel(RelDecorrelator.java:738)
at sun.reflect.GeneratedMethodAccessor9.invoke(Unknown Source)
at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.apache.calcite.util.ReflectUtil$2.invoke(ReflectUtil.java:531)
at
org.apache.calcite.sql2rel.RelDecorrelator.getInvoke(RelDecorrelator.java:707)
at
org.apache.calcite.sql2rel.RelDecorrelator.decorrelateRel(RelDecorrelator.java:464)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.apache.calcite.util.ReflectUtil$2.invoke(ReflectUtil.java:531)
at
org.apache.calcite.sql2rel.RelDecorrelator.getInvoke(RelDecorrelator.java:707)
at
org.apache.calcite.sql2rel.RelDecorrelator.decorrelateRel(RelDecorrelator.java:512)
at
org.apache.calcite.sql2rel.RelDecorrelator.decorrelateRel(RelDecorrelator.java:495)
at sun.reflect.GeneratedMethodAccessor13.invoke(Unknown Source)
at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.apache.calcite.util.ReflectUtil$2.invoke(ReflectUtil.java:531)
at
org.apache.calcite.sql2rel.RelDecorrelator.getInvoke(RelDecorrelator.java:707)
at
org.apache.calcite.sql2rel.RelDecorrelator.decorrelateRel(RelDecorrelator.java:1187)
at
org.apache.calcite.sql2rel.RelDecorrelator.decorrelateRel(RelDecorrelator.java:1169)
at sun.reflect.GeneratedMethodAccessor12.invoke(Unknown Source)
at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.apache.calcite.util.ReflectUtil$2.invoke(ReflectUtil.java:531)
at
org.apache.calcite.sql2rel.RelDecorrelator.getInvoke(RelDecorrelator.java:707)
at
org.apache.calcite.sql2rel.RelDecorrelator.decorrelateRel(RelDecorrelator.java:749)
at
org.apache.calcite.sql2rel.RelDecorrelator.decorrelateRel(RelDecorrelator.java:738)
at sun.reflect.GeneratedMethodAccessor9.invoke(Unknown Source)
at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.apache.calcite.util.ReflectUtil$2.invoke(ReflectUtil.java:531)
at
org.apache.calcite.sql2rel.RelDecorrelator.getInvoke(RelDecorrelator.java:707)
at
org.apache.calcite.sql2rel.RelDecorrelator.decorrelateRel(RelDecorrelator.java:749)
at
org.apache.calcite.sql2rel.RelDecorrelator.decorrelateRel(RelDecorrelator.java:738)
at sun.reflect.GeneratedMethodAccessor9.invoke(Unknown Source)
at
[jira] [Created] (CALCITE-5375) Support gapfilling and interpolation
Dmitry Sysolyatin created CALCITE-5375: -- Summary: Support gapfilling and interpolation Key: CALCITE-5375 URL: https://issues.apache.org/jira/browse/CALCITE-5375 Project: Calcite Issue Type: Bug Components: core Reporter: Dmitry Sysolyatin A lot of modern timeseries databases support gap filling and interpolation. For example: * Clickhouse has "ORDER BY WITH FILL [INTERPOLATE]" operator * Timescaledb has time_bucket_gapfill [2] and interpolate [3] functions * Vertica has TIMESERIES clause [4] It would be good if calcite also supports gapfilling and interpolation. Most probably the best syntax for that is Vertica's TIMESERIES Clause [4]. [1] [https://docs.timescale.com/api/latest/hyperfunctions/gapfilling-interpolation/interpolate/] [2] [https://docs.timescale.com/api/latest/hyperfunctions/gapfilling-interpolation/time_bucket_gapfill/] [3] [https://docs.timescale.com/api/latest/hyperfunctions/gapfilling-interpolation/interpolate/] [4] [https://www.vertica.com/docs/11.1.x/HTML/Content/Authoring/SQLReferenceManual/Statements/SELECT/TIMESERIESClause.htm] -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Created] (CALCITE-5305) Support string constants with c-style escapes
Dmitry Sysolyatin created CALCITE-5305: -- Summary: Support string constants with c-style escapes Key: CALCITE-5305 URL: https://issues.apache.org/jira/browse/CALCITE-5305 Project: Calcite Issue Type: Improvement Components: core Reporter: Dmitry Sysolyatin Support string constants with c-style escapes. [4.1.2.2. String Constants With C-Style Escapes|https://www.postgresql.org/docs/14/sql-syntax-lexical.html#SQL-SYNTAX-CONSTANTS] -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Created] (CALCITE-5297) Casting dynamic variable twice throws exception
Dmitry Sysolyatin created CALCITE-5297:
--
Summary: Casting dynamic variable twice throws exception
Key: CALCITE-5297
URL: https://issues.apache.org/jira/browse/CALCITE-5297
Project: Calcite
Issue Type: Bug
Components: core
Reporter: Dmitry Sysolyatin
Executing of {code}SELECT CAST(CAST(? AS INTEGER) AS CHAR){code} query throws
exception:
{code}
Conversion to relational algebra failed to preserve datatypes:
validated type:
RecordType(CHAR(1) NOT NULL EXPR$0) NOT NULL
converted type:
RecordType(CHAR(1) EXPR$0) NOT NULL
rel:
LogicalProject(EXPR$0=[CAST(?0):CHAR(1)])
LogicalValues(tuples=[[{ 0 }]])
{code}
Testcase -
https://github.com/apache/calcite/commit/4e32d2997b80effda66d46af06ec376b44856ead
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Created] (CALCITE-5266) Increase precision of TIMESTAMP, INTERVAL types to microseconds, nanoseconds
Dmitry Sysolyatin created CALCITE-5266: -- Summary: Increase precision of TIMESTAMP, INTERVAL types to microseconds, nanoseconds Key: CALCITE-5266 URL: https://issues.apache.org/jira/browse/CALCITE-5266 Project: Calcite Issue Type: Improvement Components: core Reporter: Dmitry Sysolyatin TIMESTAMP, INTERVAL types can preserve only milliseconds but it would be good to extend precision to microseconds and nanoseconds. I suggest to use standard java.time classes for representing TIMESTAMP and INTERVAL type internally: # TIMESTAMP type can be represent like java.time.Instant # INTERVAL type can be represent like java.time.Period + java.time.Duration What do you guys think ? -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Created] (CALCITE-5259) Add getParameterRowType method to Planner interface
Dmitry Sysolyatin created CALCITE-5259: -- Summary: Add getParameterRowType method to Planner interface Key: CALCITE-5259 URL: https://issues.apache.org/jira/browse/CALCITE-5259 Project: Calcite Issue Type: Improvement Components: core Reporter: Dmitry Sysolyatin Assignee: Dmitry Sysolyatin Fix For: 1.32.0 It is impossible to getParameterRowType from Planner instance at the moment, but it can be useful for use cases when dynamic parameters are used. -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Created] (CALCITE-5244) Postgres dialect should support traditional postgres interval format
Dmitry Sysolyatin created CALCITE-5244:
--
Summary: Postgres dialect should support traditional postgres
interval format
Key: CALCITE-5244
URL: https://issues.apache.org/jira/browse/CALCITE-5244
Project: Calcite
Issue Type: Bug
Components: babel
Reporter: Dmitry Sysolyatin
Postgres dialect should support "Traditional Postgres format" for interval.
https://www.postgresql.org/docs/current/datatype-datetime.html#DATATYPE-INTERVAL-INPUT
Query {code}select interval '1 year 3 hours 20 minutes 10 seconds 5
milliseconds'{code} throws exception:
{code}Caused by: java.lang.RuntimeException:
org.apache.calcite.sql.parser.SqlParseException: Encountered "\'1 year 3 hours
20 minutes 10 seconds 5 milliseconds\' " at line 1, column 17.{code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Created] (CALCITE-5160) ANY, SOME functions should support subquery and scalar arrays
Dmitry Sysolyatin created CALCITE-5160:
--
Summary: ANY, SOME functions should support subquery and scalar
arrays
Key: CALCITE-5160
URL: https://issues.apache.org/jira/browse/CALCITE-5160
Project: Calcite
Issue Type: Bug
Components: core
Reporter: Dmitry Sysolyatin
At the moment ANY, SOME functions support subquery:
{code}
SELECT 1 = SOME (SELECT * FROM UNNEST(ARRAY[1,2,3]))
{code}
But if input argument is array, then query fails
{code}
SELECT 1 = SOME (ARRAY[1,2,3])
SELECT 1 = SOME(. FROM )
{code}
[https://www.postgresql.org/docs/current/functions-comparisons.html#id-1.5.8.30.16]
It is useful for checking if a value exists in an array.
--
This message was sent by Atlassian Jira
(v8.20.7#820007)
[jira] [Created] (CALCITE-5159) PG dialect should support implicit cast from string to array
Dmitry Sysolyatin created CALCITE-5159:
--
Summary: PG dialect should support implicit cast from string to
array
Key: CALCITE-5159
URL: https://issues.apache.org/jira/browse/CALCITE-5159
Project: Calcite
Issue Type: Improvement
Components: core
Reporter: Dmitry Sysolyatin
Assignee: Dmitry Sysolyatin
According to the documentation
[https://www.postgresql.org/docs/current/arrays.html]
Postgres dialect should support implicit cast from string to array:
{code:java}
SELECT ARRAY[1,2,3] = '{1,2,3}'
{code}
--
This message was sent by Atlassian Jira
(v8.20.7#820007)
[jira] [Created] (CALCITE-5150) Parser should parse subquery with order by inside array constructor
Dmitry Sysolyatin created CALCITE-5150:
--
Summary: Parser should parse subquery with order by inside array
constructor
Key: CALCITE-5150
URL: https://issues.apache.org/jira/browse/CALCITE-5150
Project: Calcite
Issue Type: Bug
Components: core
Reporter: Dmitry Sysolyatin
Assignee: Dmitry Sysolyatin
Parser can't parse query that looks like:
{code}
SELECT array(select x from (VALUES(1)) x ORDER BY x)
{code}
{code}
Caused by: org.apache.calcite.sql.parser.babel.ParseException: Encountered
"ORDER" at line 1, column 42.
Was expecting one of:
")" ...
"NATURAL" ...
{code}
--
This message was sent by Atlassian Jira
(v8.20.7#820007)
[jira] [Created] (CALCITE-5137) EnumerableUncollect throws NPE if input has ((List) null)
Dmitry Sysolyatin created CALCITE-5137:
--
Summary: EnumerableUncollect throws NPE if input has ((List) null)
Key: CALCITE-5137
URL: https://issues.apache.org/jira/browse/CALCITE-5137
Project: Calcite
Issue Type: Bug
Components: core, linq4j
Reporter: Dmitry Sysolyatin
Assignee: Dmitry Sysolyatin
EnumerableUncollect throws NPE when an input has element = ((List) null)
Example:
{code}
SELECT * FROM UNNEST(CAST(null AS INTEGER ARRAY))
{code}
In a real situation, it can appear if to use left join. For example:
{code}
SELECT ARRAY(SELECT * FROM UNNEST(t.x)) FROM (VALUES(1)) LEFT JOIN (SELECT
ARRAY[1] as x, 2 as y) t ON t.y = 1
{code}
--
This message was sent by Atlassian Jira
(v8.20.7#820007)
[jira] [Created] (CALCITE-5134) Queries with subquery inside select list does not work if subquery uses table from left join
Dmitry Sysolyatin created CALCITE-5134:
--
Summary: Queries with subquery inside select list does not work if
subquery uses table from left join
Key: CALCITE-5134
URL: https://issues.apache.org/jira/browse/CALCITE-5134
Project: Calcite
Issue Type: Bug
Components: core
Reporter: Dmitry Sysolyatin
{code:java}
!use bookstore
SELECT array(SELECT lau."books")
FROM "bookstore"."authors" au
LEFT JOIN "bookstore"."authors" lau ON (lau."name" = au."name");
{code}
Exception:
{code:java}
> java.lang.AssertionError: Conversion to relational algebra failed to preserve
> datatypes:
> validated type:
> RecordType(RecordType(RecordType(JavaType(class java.lang.String) title,
> JavaType(int) NOT NULL publishYear, RecordType(JavaType(int) NOT NULL pageNo,
> JavaType(class java.lang.String) contentType) NOT NULL ARRAY NOT NULL pages)
> NOT NULL ARRAY books) NOT NULL ARRAY NOT NULL EXPR$0) NOT NULL
> converted type:
> RecordType(RecordType(RecordType(JavaType(class java.lang.String) title,
> JavaType(int) NOT NULL publishYear, RecordType(JavaType(int) NOT NULL pageNo,
> JavaType(class java.lang.String) contentType) NOT NULL ARRAY NOT NULL pages)
> NOT NULL ARRAY NOT NULL books) NOT NULL ARRAY NOT NULL EXPR$0) NOT NULL
> rel:
> LogicalProject(EXPR$0=[ARRAY({
> LogicalProject(books=[$cor0.books0])
> LogicalValues(tuples=[[{ 0 }]])
> })])
> LogicalJoin(condition=[=($9, $4)], joinType=[left])
> LogicalProject(aid=[$0], name=[$1], birthPlace=[$2], books=[$3],
> name0=[CAST($1):VARCHAR])
> LogicalTableScan(table=[[bookstore, authors]])
> LogicalProject(aid=[$0], name=[$1], birthPlace=[$2], books=[$3],
> name0=[CAST($1):VARCHAR])
> LogicalTableScan(table=[[bookstore, authors]])
>
{code}
{code:java}
!use bookstore
SELECT array(SELECT lau."name" || 'test')
FROM "bookstore"."authors" au
LEFT JOIN "bookstore"."authors" lau ON (lau."name" = au."name");
{code}
Exception:
{code:java}
> java.sql.SQLException: Error while executing SQL "SELECT array(SELECT
> lau."name" || 'test')
> FROM "bookstore"."authors" au
> LEFT JOIN "bookstore"."authors" lau ON (lau."name" = au."name")": field
> ordinal [5] out of range; input fields are: [name0, name00]
{code}
{code:java}
SELECT array(SELECT * FROM UNNEST(lau."books"))
FROM "bookstore"."authors" au
LEFT JOIN "bookstore"."authors" lau ON (lau."name" = au."name");
{code}
Exception:
{code:java}
Suppressed: java.lang.IndexOutOfBoundsException: Index 7 out of bounds for
length 2
{code}
Initially, I faced this issue when tried to execute the following query and got
NPE exception:
{code:java}
SELECT c.relchecks, c.relkind, c.relhasindex, c.relhasrules, c.relhastriggers,
c.relrowsecurity, c.relforcerowsecurity, c.relhasoids, c.relispartition,
pg_catalog.array_to_string(c.reloptions || array(select 'toast.' || x from
pg_catalog.unnest(tc.reloptions) x), ', '), c.reltablespace, CASE WHEN
c.reloftype = 0 THEN '' ELSE c.reloftype::pg_catalog.regtype::pg_catalog.text
END, c.relpersistence, c.relreplident
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_class tc ON (c.reltoastrelid =
tc.oid)
WHERE c.oid = '1';
{code}
I started to break down this query into pieces and managed to reduce it to
queries described above.
--
This message was sent by Atlassian Jira
(v8.20.7#820007)
[jira] [Created] (CALCITE-5127) Error when executing query with subquery in select list that uses outer column of array type
Dmitry Sysolyatin created CALCITE-5127:
--
Summary: Error when executing query with subquery in select list
that uses outer column of array type
Key: CALCITE-5127
URL: https://issues.apache.org/jira/browse/CALCITE-5127
Project: Calcite
Issue Type: Bug
Reporter: Dmitry Sysolyatin
The following queries fail:
{code}
CalciteAssert.that()
.query("SELECT ARRAY(SELECT s.x)\n" +
"FROM (SELECT ARRAY[1,2,3] as x) s")
.returnsCount(1);
CalciteAssert.that()
.query("SELECT ARRAY(SELECT * FROM UNNEST(s.x) y)\n" +
"FROM (SELECT ARRAY[1,2,3] as x) s")
.returnsCount(1);
CalciteAssert.that()
.query("SELECT (SELECT CARDINALITY(s.x) LIMIT 1)\n" +
"FROM (SELECT ARRAY[1,2,3] as x) s")
.returnsUnordered("EXPR$0=3");
{code}
With exception:
Caused by: java.lang.ClassCastException: java.lang.Integer cannot be cast to
java.util.List
You can find test cases for this task in
https://github.com/apache/calcite/commit/27e68ded2c3bea7d7af73dd1dc156e46fb3591a8
--
This message was sent by Atlassian Jira
(v8.20.7#820007)
[jira] [Created] (CALCITE-5126) Implicit column alias for single-column UNNEST should work with any single-column UNNEST’s input
Dmitry Sysolyatin created CALCITE-5126:
--
Summary: Implicit column alias for single-column UNNEST should
work with any single-column UNNEST’s input
Key: CALCITE-5126
URL: https://issues.apache.org/jira/browse/CALCITE-5126
Project: Calcite
Issue Type: Bug
Components: core
Reporter: Dmitry Sysolyatin
Assignee: Dmitry Sysolyatin
Implicit column alias works only in case with scalar array, but it should work
with any single-column UNNEST’s input
There are the following lines in PostgresSQL documentation(
[https://www.postgresql.org/docs/current/queries-table-expressions.html])
regarding table functions like “UNNEST”:
{quote}If column aliases are not supplied, then for a function returning a base
data type, the column name is also the same as the function name.
{quote}
>From BigQuery documentation
>([https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#implicit_aliases):]
{quote}For input ARRAYs of most element types, the output of UNNEST generally
has one column. This single column has an optional alias, which you can use to
refer to the column elsewhere in the query
{quote}
Examples:
{code:java}
psql => SELECT * FROM UNNEST(array(select 1 as x)) y;
y
---
1
(1 row)
{code}
{code:java}
CREATE TABLE testtable (
strings text[]
);
INSERT INTO testtable VALUES(ARRAY['1', '2', '3'])
SELECT array(SELECT 'toast' || x FROM unnest(tt.strings) x) FROM testtable as
tt;
array
{toast1,toast2,toast3}
(1 row)
{code}
--
This message was sent by Atlassian Jira
(v8.20.7#820007)
[jira] [Created] (CALCITE-5125) "||" operator can be used for array concatenation
Dmitry Sysolyatin created CALCITE-5125: -- Summary: "||" operator can be used for array concatenation Key: CALCITE-5125 URL: https://issues.apache.org/jira/browse/CALCITE-5125 Project: Calcite Issue Type: Improvement Components: core Reporter: Dmitry Sysolyatin "||" operator can be used only for string concatenation but it would be good to use it also for array concatenation as PostgreSQL -- This message was sent by Atlassian Jira (v8.20.7#820007)
[jira] [Created] (CALCITE-4999) `least restrictive` type inference does not work when one operand is scalar and another is row with one field
Dmitry Sysolyatin created CALCITE-4999: -- Summary: `least restrictive` type inference does not work when one operand is scalar and another is row with one field Key: CALCITE-4999 URL: https://issues.apache.org/jira/browse/CALCITE-4999 Project: Calcite Issue Type: Bug Components: core Reporter: Dmitry Sysolyatin Assignee: Dmitry Sysolyatin I faced with a issue that the following query: "SELECT ARRAY_CONCAT(ARRAY['1', '2'], array(select 'toast.' || x from unnest(ARRAY['1','2']) x))" didn't work, because of: "java.lang.IllegalArgumentException: Cannot infer return type for ARRAY_CONCAT; operand types: [CHAR(1) ARRAY, RecordType(CHAR(7) EXPR$0) ARRAY]" `least restrictive` type inference does not work when one operand is scalar and another is row with one field -- This message was sent by Atlassian Jira (v8.20.1#820001)
[jira] [Created] (CALCITE-4630) Wrong logical plan for INNER JOIN with subquery
Dmitry Sysolyatin created CALCITE-4630:
--
Summary: Wrong logical plan for INNER JOIN with subquery
Key: CALCITE-4630
URL: https://issues.apache.org/jira/browse/CALCITE-4630
Project: Calcite
Issue Type: Improvement
Components: core
Affects Versions: 1.26.0
Reporter: Dmitry Sysolyatin
I tried to execute the following query:
{code:sql}
SELECT
d.name,
d.timestamp_ns
FROM trucks t
INNER JOIN LATERAL (
SELECT
name, timestamp_ns, fuel_state
FROM
trucks_diagnostics
WHERE
trucks_diagnostics.name = t.name
ORDER BY timestamp_ns DESC
LIMIT 1) d ON true WHERE s.fleet = 'South' AND d.fuel_state < 0.1
{code}
calcite generates me the following logical plan:
{code:java}
LogicalProject(name=[$8], timestamp_ns=[$9])
LogicalCorrelate(correlation=[$cor0], joinType=[inner], requiredColumns=[{4}])
LogicalFilter(condition=[=(CAST($6):VARCHAR, 'South')])
DataTableScan
LogicalFilter(condition=[<($2, 0.1:DECIMAL(2, 1))])
LogicalSort(sort0=[$1], dir0=[DESC], fetch=[1])
LogicalProject(name=[$4], timestamp_ns=[$0], fuel_state=[$1])
LogicalFilter(condition=[=(CAST($4):VARCHAR,
CAST($cor0.name):VARCHAR)])
DataTableScan
{code}
But `LogicalFilter(condition=[<($2, 0.1:DECIMAL(2, 1))])` should not be inside
LogicalCorrelate . It should be upper than LogicalCorrelate
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
