[jira] [Created] (CALCITE-6401) JDBC adapter cannot push down joins with complex JOIN condition
Ulrich Kramer created CALCITE-6401:
--
Summary: JDBC adapter cannot push down joins with complex JOIN
condition
Key: CALCITE-6401
URL: https://issues.apache.org/jira/browse/CALCITE-6401
Project: Calcite
Issue Type: Improvement
Affects Versions: 1.36.0
Reporter: Ulrich Kramer
JDBC adapter (in JdbcJoinRule) cannot push down joins with conditions which
include operations not listed in {{JdbcJoinRule::canJoinOnCondition}}.
See also CALCITE-4907
For example the following statement is executed using an
{{EnumerableNestedLoopJoin}}
{code:SQL}
SELECT
*
FROM
A
JOIN (
SELECT
D."userId",
MAX(D."id") as "id"
FROM
D
GROUP BY
D."userId"
) B ON (
A."id" = B."id" AND A."userId" IS NOT NULL
)
OR (
A."userId" = B."userId" AND A."id" IS NOT NULL
)
{code}
Adding the cases {{IS_NULL}} and {{IS_NOT_NULL}} to
{{JdbcJoinRule::canJoinOnCondition}} fixes the problem for this statement. But
I was not able to find out which cases are also missing here.
Where could I find the associated code in {{JdbcJoin::implement}} that makes it
impossible to create an appropriate SQL statement if all operations were
allowed?
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Created] (CALCITE-6268) Support implementing custom JdbcSchema
Ulrich Kramer created CALCITE-6268:
--
Summary: Support implementing custom JdbcSchema
Key: CALCITE-6268
URL: https://issues.apache.org/jira/browse/CALCITE-6268
Project: Calcite
Issue Type: New Feature
Components: core
Affects Versions: 1.36.0
Reporter: Ulrich Kramer
Currently, it's not possible to implement a custom {{JdbcSchema}} because of
the explicit type check in {{CalciteSchema::unwrap}}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Created] (CALCITE-6251) innerEnumerator in EnumerableDefaults::correlateBatchJoin is not closed
Ulrich Kramer created CALCITE-6251: -- Summary: innerEnumerator in EnumerableDefaults::correlateBatchJoin is not closed Key: CALCITE-6251 URL: https://issues.apache.org/jira/browse/CALCITE-6251 Project: Calcite Issue Type: Bug Affects Versions: 1.36.0 Reporter: Ulrich Kramer The [innerEnumerator|https://github.com/apache/calcite/blob/f7069cc5245c22f816c565669f52b4f30b046f4d/linq4j/src/main/java/org/apache/calcite/linq4j/EnumerableDefaults.java#L1681] is only closed at the end. But if there are multiple loops, [innerEnumerator is just assigned to a different value without closing it|https://github.com/apache/calcite/blob/f7069cc5245c22f816c565669f52b4f30b046f4d/linq4j/src/main/java/org/apache/calcite/linq4j/EnumerableDefaults.java#L1720]. It should look like [here|https://github.com/apache/calcite/blob/f7069cc5245c22f816c565669f52b4f30b046f4d/linq4j/src/main/java/org/apache/calcite/linq4j/EnumerableDefaults.java#L1547-L1550]. -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Created] (CALCITE-6249) RelNode::estimatedRowCount should not be used in computeSelfCost
Ulrich Kramer created CALCITE-6249:
--
Summary: RelNode::estimatedRowCount should not be used in
computeSelfCost
Key: CALCITE-6249
URL: https://issues.apache.org/jira/browse/CALCITE-6249
Project: Calcite
Issue Type: Bug
Reporter: Ulrich Kramer
{{RelNode::estimatedRowCount}} is still used in many place inside
{{computeSelfCost}}
It it should be possible to implement the estimation of the row count
completely outside Calcite, these calls should be replaced by
{{mq.getRowCount(rel)}}.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Created] (CALCITE-6236) EnumerableBatchNestedLoopJoin uses wrong row count for cost calculation
Ulrich Kramer created CALCITE-6236:
--
Summary: EnumerableBatchNestedLoopJoin uses wrong row count for
cost calculation
Key: CALCITE-6236
URL: https://issues.apache.org/jira/browse/CALCITE-6236
Project: Calcite
Issue Type: Bug
Reporter: Ulrich Kramer
{{EnumerableBatchNestedLoopJoin}} always adds a {{Filter}} on the right
relation.
This filter reduces the number of rows by it's selectivity (in our case by a
factor of 4).
Therefore, {{RelMdUtil.getJoinRowCount}} returns a value 4 times lower compared
to the one returned for a {{JdbcJoin}}.
This leads to the fact that in most cases {{EnumerableBatchNestedLoopJoin}} is
preferred over {{JdbcJoin}}.
This is an example for the different costs
{code}
EnumerableProject rows=460.0 self_costs=460.0 cumulative_costs=1465.0
EnumerableBatchNestedLoopJoin rows=460.0 self_costs=687.5
cumulative_costs=1005.0
JdbcToEnumerableConverter rows=100.0 self_costs=10.0 cumulative_costs=190.0
JdbcProject rows=100.0 self_costs=80.0 cumulative_costs=180.0
JdbcTableScan rows=100.0 self_costs=100.0 cumulative_costs=100.0
JdbcToEnumerableConverter rows=25.0 self_costs=2.5 cumulative_costs=127.5
JdbcFilter rows=25.0 self_costs=25.0 cumulative_costs=125.0
JdbcTableScan rows=100.0 self_costs=100.0 cumulative_costs=100.0
{code}
vs.
{code}
JdbcToEnumerableConverter rows=1585.0 self_costs=158.5 cumulative_costs=2023.5
JdbcJoin rows=1585.0 self_costs=1585.0 cumulative_costs=1865.0
JdbcProject rows=100.0 self_costs=80.0 cumulative_costs=180.0
JdbcTableScan rows=100.0 self_costs=100.0 cumulative_costs=100.0
JdbcTableScan rows=100.0 self_costs=100.0 cumulative_costs=100.0
{code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Created] (CALCITE-6221) Invalid query generated when the same table is joined multiple times
Ulrich Kramer created CALCITE-6221:
--
Summary: Invalid query generated when the same table is joined
multiple times
Key: CALCITE-6221
URL: https://issues.apache.org/jira/browse/CALCITE-6221
Project: Calcite
Issue Type: Bug
Components: core
Affects Versions: 1.36.0
Environment: Local development
Reporter: Ulrich Kramer
Adding the following unit test to {{JdbcAdapterTest}}
{code:java}
@Test void testUnknownColumn() {
CalciteAssert.model(JdbcTest.SCOTT_MODEL)
.query("SELECT\n" +
"\"content-format-owner\",\n" +
"\"content-owner\"\n" +
"FROM\n" +
"(\n" +
"SELECT\n" +
"d1.dname AS \"content-format-owner\",\n" +
"d2.dname || ' ' AS \"content-owner\"\n" +
"FROM\n" +
"scott.emp e1\n" +
"left outer join scott.dept d1 on e1.deptno =
d1.deptno\n" +
"left outer join scott.dept d2 on e1.deptno =
d2.deptno\n" +
"left outer join scott.emp e2 on e1.deptno =
e2.deptno\n" +
"GROUP BY\n" +
"d1.dname,\n" +
"d2.dname\n" +
")\n" +
"WHERE\n" +
"\"content-owner\" IN (?)")
.runs();
}
{code}
Fails because the following SQL is sent to the underlying database
{code:SQL}
SELECT
"t2"."DNAME" AS "content-format-owner",
"t2"."DNAME0" || ' ' AS "content-owner"
FROM
(
SELECT
*
FROM
(
SELECT
"DEPTNO"
FROM
"SCOTT"."EMP"
) AS "t"
LEFT JOIN (
SELECT
"DEPTNO",
"DNAME"
FROM
"SCOTT"."DEPT"
) AS "t0" ON "t"."DEPTNO" = "t0"."DEPTNO"
LEFT JOIN (
SELECT
"DEPTNO",
"DNAME"
FROM
"SCOTT"."DEPT"
) AS "t1" ON "t"."DEPTNO" = "t1"."DEPTNO"
WHERE
"t1"."DNAME" || ' ' = ?
) AS "t2"
LEFT JOIN (
SELECT
"DEPTNO"
FROM
"SCOTT"."EMP"
) AS "t3" ON "t2"."DEPTNO" = "t3"."DEPTNO"
GROUP BY
"t2"."DNAME",
"t2"."DNAME0"
{code}
The column {{"t2"."DNAME0"}} does not exist.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Created] (CALCITE-6157) SqlValidatorImpl breaks join condition
Ulrich Kramer created CALCITE-6157:
--
Summary: SqlValidatorImpl breaks join condition
Key: CALCITE-6157
URL: https://issues.apache.org/jira/browse/CALCITE-6157
Project: Calcite
Issue Type: Bug
Components: core
Affects Versions: 1.36.0
Reporter: Ulrich Kramer
When switching from 1.35.0 to 1.36.0, {{SqlValidatorImpl}} breaks the join
condition on the following statement
{code:SQL}
WITH `it1` AS (
SELECT
`company`,
`area`,
`revenue`,
`regionId`
FROM
`12345678-1234-1234-1234-00010001`.`revenues`
)
SELECT
*
FROM
(
SELECT
`T1`.`company` AS `company`,
MAX(`T1`.`revenue`) AS `revenue`
FROM
`it1` AS `T1`
GROUP BY
`company`
) AS `T`
LEFT JOIN `it1` AS `T2` ON `T`.`company` = `T2`.`company`
AND `T`.`revenue` = `T2`.`revenue`
{code}
It modifies the join condition [at this
location|https://github.com/apache/calcite/blob/d3ab0bc8e4d4c9ebc0fc4e33ce478d276f5d11e4/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java#L3603-L3604]
to
{code:SQL}
`T`.`company` = `T1`.`company`
AND `T`.`revenue` = `T1`.`revenue`
{code}
which causes a {{Table `T1`not found}}.
I already tried to reproduce the problem by adding the following test to
{{SqlValidatorTest}}, but everything worked fine
{code:java}
@Test void testWith2() {
sql("with it1 as (select empno, sal, deptno from emp)\n"
+ "select * from ( select t1.empno as empno, max(t1.deptno) as deptno
from it1 as t1 group by empno) as t\n"
+ "left outer join it1 as t2 on t.empno = t2.empno and t.deptno =
t2.deptno")
.ok();
}
{code}
During debugging, I recognized that the {{DelegatingScope}} does something
different in our case [at this
location|https://github.com/apache/calcite/blob/d3ab0bc8e4d4c9ebc0fc4e33ce478d276f5d11e4/core/src/main/java/org/apache/calcite/sql/validate/DelegatingScope.java#L333-L337].
In our case the {{fromNs}} resolved to {{`it1` AS `T1`}}, which is wrong.
But I was not able to find the root cause yet.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Created] (CALCITE-6090) Jdbc adapter may create wrong sql for joins
Ulrich Kramer created CALCITE-6090:
--
Summary: Jdbc adapter may create wrong sql for joins
Key: CALCITE-6090
URL: https://issues.apache.org/jira/browse/CALCITE-6090
Project: Calcite
Issue Type: Bug
Components: core
Affects Versions: 1.35.0
Reporter: Ulrich Kramer
Adding the following unit test to {{JdbcAdapterTest}} leads to an error
{code:java}
@Test void testAmbiguousColumn() {
CalciteAssert.model(JdbcTest.FOODMART_SCOTT_MODEL)
.query("select\n" +
" \"store_id\" \"latest_id\",\n" +
" max(\"store_type\") \"latest_store_type\"\n" +
"from\n" +
" ( SELECT \"store_id\",\"store_type\" FROM
\"foodmart\".\"store\") \n" +
"group by\n" +
" \"store_id\"")
.runs()
.enable(CalciteAssert.DB == CalciteAssert.DatabaseInstance.HSQLDB)
.explainContains("JdbcToEnumerableConverter\n JdbcProject" )
.planHasSql("SELECT MAX(\"region_id\") AS \"latest_region_id\",
\"store_id\" AS \"latest_id\"\nFROM \"foodmart\".\"store\"\nGROUP BY
\"store_id\"");
}
{code}
The projection for the column {{latest_id}} is missing. The problem is related
[here|https://github.com/apache/calcite/blob/590ec85f0fcff7173c288c350c3f60e640976a34/core/src/main/java/org/apache/calcite/tools/RelBuilder.java#L2094-L2095].
If the aggregation doesn't add, remove or twist columns,
{{RexUtil.isIdentity}} returns true. Additionally the {{inputRowType}} already
contains the names of the output columns. Therefore
{{fieldNameList.equals(inputRowType.getFieldNames())}} also returns true.
Normally, this doesn't cause issues. But if this statement is used within a
join like this
{code:sql}
SELECT
DISTINCT "region_id",
"store_id",
"dummy"
FROM
(
SELECT
"region_id",
"store_id",
"dummy"
FROM
(
select
B."store_city" "store_city",
B."store_id" "store_id",
B."region_id" "region_id",
cast(null as integer) "dummy"
from
(
select
A."store_city" "store_city",
A."region_id" "region_id",
A."store_id" "store_id"
from
(
select
max("region_id") "latest_region_id",
"store_id" "latest_id"
from
( SELECT "region_id", "store_id" FROM "foodmart"."store") "C"
group by
"store_id"
) "D"
left outer join "foodmart"."store" A on (
A."store_id" = "D"."latest_id"
and A."region_id" = "D"."latest_region_id"
)
) B
)
WHERE
"store_city" IS NOT NULL
)
{code}
an invalid SQL will be generated because the left an the right side of the join
has a column "store_id". This will be fixed in {{SqlValidatorUtil::addFields}}
by appending a unique number. But in the end this will result in a statement,
which contains a {{GROUP BY ... store_id0 }} and will never be successful.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Created] (CALCITE-5832) CyclicMetadataException thrown in complex JOIN
Ulrich Kramer created CALCITE-5832:
--
Summary: CyclicMetadataException thrown in complex JOIN
Key: CALCITE-5832
URL: https://issues.apache.org/jira/browse/CALCITE-5832
Project: Calcite
Issue Type: Bug
Components: core
Affects Versions: 1.34.0
Reporter: Ulrich Kramer
Adding the following unit test to {{JdbcAdapterTest}} reproduces the error
{code:java}
@Test void testJdbcCyclicMetadata() throws Exception {
final String url = MultiJdbcSchemaJoinTest.TempDb.INSTANCE.getUrl();
Connection baseConnection = DriverManager.getConnection(url);
Statement baseStmt = baseConnection.createStatement();
baseStmt.execute("CREATE TABLE T1 (\n"
+ "\"contentViewsCount\" INTEGER,\n" +
"\"isExpired\" BOOLEAN,\n" +
"\"metadataPreviewUrl\" VARCHAR(100),\n" +
"\"format\" VARCHAR(100),\n" +
"\"description\" VARCHAR(100),\n" +
"\"language\" VARCHAR(100),\n" +
"\"assetTitle\" VARCHAR(100),\n" +
"\"assetType\" VARCHAR(100),\n" +
"\"contentType\" VARCHAR(100),\n" +
"\"doi\" VARCHAR(100),\n" +
"\"crmBpn\" VARCHAR(100),\n" +
"PRIMARY KEY(\"doi\"))");
baseStmt.execute("CREATE TABLE T2 (\n"
+ "\"doi\" VARCHAR(100),\n" +
"\"industry\" VARCHAR(100),\n" +
"PRIMARY KEY(\"doi\", \"industry\"))");
baseStmt.execute("CREATE TABLE T3 (\n"
+ "\"semaphoreId\" VARCHAR(100),\n" +
"\"name\" VARCHAR(100),\n" +
"\"industryId\" VARCHAR(100),\n" +
"PRIMARY KEY(\"semaphoreId\"))");
baseStmt.execute("CREATE TABLE T4 (\n"
+ "\"contentViewsCount\" INTEGER,\n" +
"\"CRM_Account_ID\" VARCHAR(100),\n" +
"\"CRM_Account_Name\" VARCHAR(100),\n" +
"PRIMARY KEY(\"CRM_Account_ID\"))");
baseStmt.close();
baseConnection.commit();
Properties info = new Properties();
info.put("model",
"inline:"
+ "{\n"
+ " version: '1.0',\n"
+ " defaultSchema: 'BASEJDBC',\n"
+ " schemas: [\n"
+ " {\n"
+ " type: 'jdbc',\n"
+ " name: 'BASEJDBC',\n"
+ " jdbcDriver: '" + jdbcDriver.class.getName() + "',\n"
+ " jdbcUrl: '" + url + "',\n"
+ " jdbcCatalog: null,\n"
+ " jdbcSchema: null\n"
+ " }\n"
+ " ]\n"
+ "}");
final Connection calciteConnection =
DriverManager.getConnection("jdbc:calcite:", info);
ResultSet rs = calciteConnection
.prepareStatement("SELECT \"_metadata.status\", \"doi\",
\"industry.title\", " +
"\"crm_account.crm_account_name\", \"assettitle\", \"description\",
\"assettype\", " +
"\"format\", \"contentviewscount\", \"metadatapreviewurl\",
\"language\", " +
"\"contenttype\", \"isexpired\" FROM (select\n" +
" \"A\".\"contentViewsCount\" \"contentviewscount\",\n" +
" \"A\".\"isExpired\" \"isexpired\",\n" +
" \"A\".\"metadataPreviewUrl\" \"metadatapreviewurl\",\n" +
" \"A\".\"format\" \"format\",\n" +
" \"A\".\"description\" \"description\",\n" +
" \"A\".\"language\" \"language\",\n" +
" \"A\".\"assetTitle\" \"assettitle\",\n" +
" \"A\".\"assetType\" \"assettype\",\n" +
" \"A\".\"contentType\" \"contenttype\",\n" +
" \"A\".\"doi\" \"doi\",\n" +
" null \"_metadata.status\",\n" +
" \"D\".\"industry.title\" \"industry.title\",\n" +
" \"F\".\"crm_account.crm_account_name\"
\"crm_account.crm_account_name\"\n" +
"from \"T1\" \"A\"\n" +
" left outer join \"T2\" \"B\"\n" +
"on \"A\".\"doi\" = \"B\".\"doi\"\n" +
" left outer join (\n" +
"select\n" +
" \"C\".\"semaphoreId\" \"industry.semaphoreId\",\n" +
" \"C\".\"name\" \"industry.title\"\n" +
"from \"T3\" \"C\"\n" +
" ) \"D\"\n" +
"on \"B\".\"industry\" = \"D\".\"industry.semaphoreId\"\n" +
" left outer join (\n" +
"select\n" +
" \"E\".\"CRM_Account_ID\" \"crm_account.CRM_Account_ID\",\n" +
" \"E\".\"CRM_Account_Name\"
\"crm_account.crm_account_name\"\n" +
"from \"T4\" \"E\"\n" +
" ) \"F\"\n" +
"on \"A\".\"crmBpn\" = \"F\".\"crm_account" +
".CRM_Account_ID\")\n" +
"WHERE (\"isexpired\" = ?)\n" +
"AND (\"language\" IN (?, ?))\n" +
"AND (\"contenttype\" IN (?, ?))\n" +
"AND (\"doi\" IN (?))\n" +
"ORDER BY \"doi\" ASC\n" +
"LIMIT 500 OFFSET 0").executeQuery();
assertThat(rs.next(), is(true));
[jira] [Created] (CALCITE-5693) AssertionError with UNION using CTE
Ulrich Kramer created CALCITE-5693:
--
Summary: AssertionError with UNION using CTE
Key: CALCITE-5693
URL: https://issues.apache.org/jira/browse/CALCITE-5693
Project: Calcite
Issue Type: Bug
Components: core
Affects Versions: 1.34.0
Reporter: Ulrich Kramer
Adding the following test to {{JdbcAdapterTest}}
{code:java}
@Test void testFilterUnionIncludingWithPlan() {
CalciteAssert.model(FoodmartSchema.FOODMART_MODEL)
.query(" ( with a as (select * from \"sales_fact_1997\") select *
from a)\n"
+ " union all\n"
+ " ( with b as (select * from \"sales_fact_1998\") select * from
b)\n")
.runs();
}
{code}
will throw an {{AssertionError}} in {{SqlValidatorImpl.deduceModality}}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Created] (CALCITE-5441) Unknown Types value with Postgres and NULL value in dynamic parameter
Ulrich Kramer created CALCITE-5441:
--
Summary: Unknown Types value with Postgres and NULL value in
dynamic parameter
Key: CALCITE-5441
URL: https://issues.apache.org/jira/browse/CALCITE-5441
Project: Calcite
Issue Type: Bug
Reporter: Ulrich Kramer
Calcite uses
{{preparedStatement.setObject(i, (Object)null, SqlType.ANY.id)}}
in
{{ResultSetEnumerable::setDynamicParam}} to set a NULL value.
For Postgres 42.5.1, this will throw an exception:
{{new PSQLException(GT.tr("Unknown Types value.", new Object[0]),
PSQLState.INVALID_PARAMETER_TYPE)}}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Created] (CALCITE-5409) Jdbc doesn't support EnumerableRules.ENUMERABLE_BATCH_NESTED_LOOP_JOIN_RULE
Ulrich Kramer created CALCITE-5409:
--
Summary: Jdbc doesn't support
EnumerableRules.ENUMERABLE_BATCH_NESTED_LOOP_JOIN_RULE
Key: CALCITE-5409
URL: https://issues.apache.org/jira/browse/CALCITE-5409
Project: Calcite
Issue Type: Bug
Components: core
Affects Versions: 1.32.0
Reporter: Ulrich Kramer
Adding the following unit test to {{JdbcAdapterTest}} causes an
{{NullPointerException}}
{code:java}
@Test void testBatchNestedLoopJoin() {
CalciteAssert.that()
.with(CalciteConnectionProperty.LEX, Lex.JAVA)
.with(CalciteConnectionProperty.FORCE_DECORRELATE, false)
.withSchema("s", new ReflectiveSchema(new HrSchema()))
.withModel(FoodmartSchema.FOODMART_MODEL)
.query("select e.name from emps e join foodmart.store x on e.deptno =
x.store_id")
.withHook(Hook.PLANNER, (Consumer) planner -> {
planner.removeRule(EnumerableRules.ENUMERABLE_CORRELATE_RULE);
planner.addRule(EnumerableRules.ENUMERABLE_BATCH_NESTED_LOOP_JOIN_RULE);
})
.runs();
}
{code}
{code}
Error while executing SQL "select e.name from emps e join foodmart.store x on
e.deptno = x.store_id": Unable to implement
EnumerableCalc(expr#0..2=[{inputs}], name=[$t0]): rowcount = 375.0, cumulative
cost = {1465.0 rows, 2968.0 cpu, 0.0 io}, id = 174
EnumerableBatchNestedLoopJoin(condition=[=($1, $2)], joinType=[inner],
variablesSet=[[$cor0, ... $cor99]], batchSize=[100]): rowcount = 375.0,
cumulative cost = {1090.0 rows, 1468.0 cpu, 0.0 io}, id = 170
EnumerableCalc(expr#0..4=[{inputs}], expr#5=[CAST($t1):INTEGER NOT NULL],
name=[$t2], deptno0=[$t5]): rowcount = 100.0, cumulative cost = {200.0 rows,
901.0 cpu, 0.0 io}, id = 176
EnumerableTableScan(table=[[s, emps]]): rowcount = 100.0, cumulative cost
= {100.0 rows, 101.0 cpu, 0.0 io}, id = 52
JdbcToEnumerableConverter: rowcount = 25.0, cumulative cost = {207.5 rows,
283.5 cpu, 0.0 io}, id = 168
JdbcFilter(condition=[OR(=($cor0.deptno0, $0), ...)]): rowcount = 25.0,
cumulative cost = {205.0 rows, 281.0 cpu, 0.0 io}, id = 166
JdbcProject(store_id=[$0]): rowcount = 100.0, cumulative cost = {180.0
rows, 181.0 cpu, 0.0 io}, id = 164
JdbcTableScan(table=[[foodmart, store]]): rowcount = 100.0,
cumulative cost = {100.0 rows, 101.0 cpu, 0.0 io}, id = 3
...
Suppressed: java.lang.NullPointerException: variable $cor0 is not found
{code}
See also https://issues.apache.org/jira/browse/CALCITE-5354
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Created] (CALCITE-5354) JDBC with UNNEST not working
Ulrich Kramer created CALCITE-5354:
--
Summary: JDBC with UNNEST not working
Key: CALCITE-5354
URL: https://issues.apache.org/jira/browse/CALCITE-5354
Project: Calcite
Issue Type: Bug
Components: core
Affects Versions: 1.32.0
Reporter: Ulrich Kramer
Adding the following unit test to {{JdbcAdapterTest.java}} will throw an
exception
{code:java}
@Test void testUnnest() {
CalciteAssert.model(FoodmartSchema.FOODMART_MODEL)
.query("SELECT * FROM \"store\" A\n" +
"NATURAL JOIN UNNEST(SELECT ARRAY[A.\"store_id\"])\n")
.runs();
} {code}
{code}
Caused by: java.lang.IllegalStateException: Unable to implement
EnumerableCalc(expr#0..25=[{inputs}], proj#0..23=[{exprs}], EXPR$0=[$t25]):
rowcount = 100.0, cumulative cost = {780.0 rows, 7501.0 cpu, 0.0 io}, id = 160
EnumerableCorrelate(correlation=[$cor0], joinType=[inner],
requiredColumns=[{24}]): rowcount = 100.0, cumulative cost = {680.0 rows,
2401.0 cpu, 0.0 io}, id = 156
JdbcToEnumerableConverter: rowcount = 100.0, cumulative cost = {190.0 rows,
2111.0 cpu, 0.0 io}, id = 147
JdbcProject(store_id=[$0], store_type=[$1], region_id=[$2],
store_name=[$3], store_number=[$4], store_street_address=[$5], store_city=[$6],
store_state=[$7], store_postal_code=[$8], store_country=[$9],
store_manager=[$10], store_phone=[$11], store_fax=[$12],
first_opened_date=[$13], last_remodel_date=[$14], store_sqft=[$15],
grocery_sqft=[$16], frozen_sqft=[$17], meat_sqft=[$18], coffee_bar=[$19],
video_store=[$20], salad_bar=[$21], prepared_food=[$22], florist=[$23],
$f24=[ARRAY($0)]): rowcount = 100.0, cumulative cost = {180.0 rows, 2101.0 cpu,
0.0 io}, id = 145
JdbcTableScan(table=[[foodmart, store]]): rowcount = 100.0, cumulative
cost = {100.0 rows, 101.0 cpu, 0.0 io}, id = 1
EnumerableUncollect: rowcount = 1.0, cumulative cost = {2.9004
rows, 2.9004 cpu, 0.0 io}, id = 154
JdbcToEnumerableConverter: rowcount = 1.0, cumulative cost =
{1.9001 rows, 1.9001 cpu, 0.0 io}, id = 152
JdbcProject(EXPR$0=[$cor0.$f24]): rowcount = 1.0, cumulative cost =
{1.8 rows, 1.8 cpu, 0.0 io}, id = 150
JdbcValues(tuples=[[{ 0 }]]): rowcount = 1.0, cumulative cost = {1.0
rows, 1.0 cpu, 0.0 io}, id = 115
at
org.apache.calcite.adapter.enumerable.EnumerableRelImplementor.implementRoot(EnumerableRelImplementor.java:114)
at
org.apache.calcite.adapter.enumerable.EnumerableInterpretable.toBindable(EnumerableInterpretable.java:114)
at
org.apache.calcite.prepare.CalcitePrepareImpl$CalcitePreparingStmt.implement(CalcitePrepareImpl.java:1131)
at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:324)
at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:220)
at
org.apache.calcite.prepare.CalcitePrepareImpl.prepare2_(CalcitePrepareImpl.java:648)
at
org.apache.calcite.prepare.CalcitePrepareImpl.prepare_(CalcitePrepareImpl.java:514)
at
org.apache.calcite.prepare.CalcitePrepareImpl.prepareSql(CalcitePrepareImpl.java:484)
at
org.apache.calcite.jdbc.CalciteConnectionImpl.parseQuery(CalciteConnectionImpl.java:234)
at
org.apache.calcite.jdbc.CalciteMetaImpl.prepareAndExecute(CalciteMetaImpl.java:623)
at
org.apache.calcite.avatica.AvaticaConnection.prepareAndExecuteInternal(AvaticaConnection.java:674)
at
org.apache.calcite.avatica.AvaticaStatement.executeInternal(AvaticaStatement.java:156)
... 63 more
Suppressed: java.lang.NullPointerException: variable $cor0 is not found
at java.base/java.util.Objects.requireNonNull(Objects.java:334)
at
org.apache.calcite.rel.rel2sql.SqlImplementor$BaseContext.getAliasContext(SqlImplementor.java:1451)
at
org.apache.calcite.rel.rel2sql.SqlImplementor$Context.toSql(SqlImplementor.java:643)
at
org.apache.calcite.rel.rel2sql.RelToSqlConverter.visit(RelToSqlConverter.java:446)
at
java.base/jdk.internal.reflect.DirectMethodHandleAccessor.invoke(DirectMethodHandleAccessor.java:104)
at java.base/java.lang.reflect.Method.invoke(Method.java:577)
at
org.apache.calcite.util.ReflectUtil$2.invoke(ReflectUtil.java:531)
at
org.apache.calcite.rel.rel2sql.RelToSqlConverter.dispatch(RelToSqlConverter.java:139)
at
org.apache.calcite.rel.rel2sql.RelToSqlConverter.visitInput(RelToSqlConverter.java:147)
at
org.apache.calcite.rel.rel2sql.SqlImplementor.visitInput(SqlImplementor.java:216)
at
org.apache.calcite.rel.rel2sql.SqlImplementor.visitInput(SqlImplementor.java:204)
at
org.apache.calcite.rel.rel2sql.SqlImplementor.visitRoot(SqlImplementor.java:180)
at
[jira] [Created] (CALCITE-5286) Join with parameterized LIMIT throws AssertionError "not a literal"
Ulrich Kramer created CALCITE-5286:
--
Summary: Join with parameterized LIMIT throws AssertionError "not
a literal"
Key: CALCITE-5286
URL: https://issues.apache.org/jira/browse/CALCITE-5286
Project: Calcite
Issue Type: Bug
Reporter: Ulrich Kramer
A query like the following one
{code:java}
select T."name", T."valueLeverId", T."type", T."ID", T."parentId"
from (
SELECT VD."id" as ID, VD."name", VD."typeId", VD."type", VD."valueLeverId",
VD."valueLever", VD."parentId", VDtoSC."VDtoSC_List"
FROM VD
LEFT JOIN VDtoSC
ON VD."id" = VDtoSC."Value_Driver_ID"
) AS T
where T."ID" = ? limit ?
{code}
fails with
{code}
findValue:1208, RexLiteral (org.apache.calcite.rex)
intValue:1183, RexLiteral (org.apache.calcite.rex)
getMaxRowCount:207, RelMdMaxRowCount (org.apache.calcite.rel.metadata)
getMaxRowCount_$:-1, GeneratedMetadata_MaxRowCountHandler
(org.apache.calcite.rel.metadata.janino)
getMaxRowCount:-1, GeneratedMetadata_MaxRowCountHandler
(org.apache.calcite.rel.metadata.janino)
getMaxRowCount:277, RelMetadataQuery (org.apache.calcite.rel.metadata)
alreadySmaller:914, RelMdUtil (org.apache.calcite.rel.metadata)
checkInputForCollationAndLimit:887, RelMdUtil (org.apache.calcite.rel.metadata)
onMatch:138, SortJoinTransposeRule (org.apache.calcite.rel.rules)
onMatch:223, VolcanoRuleCall (org.apache.calcite.plan.volcano)
drive:59, IterativeRuleDriver (org.apache.calcite.plan.volcano)
findBestExp:523, VolcanoPlanner (org.apache.calcite.plan.volcano)
lambda$standard$3:276, Programs (org.apache.calcite.tools)
run:-1, Programs$$Lambda$2787/0x00080121f9c0 (org.apache.calcite.tools)
run:336, Programs$SequenceProgram (org.apache.calcite.tools)
transform:373, PlannerImpl (org.apache.calcite.prepare)
{code}
The 2 tables are located in a schema where joins can't be pushed down.
See also CALCITE-5048
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Created] (CALCITE-5284) JDBC rules create incorrect plan
Ulrich Kramer created CALCITE-5284:
--
Summary: JDBC rules create incorrect plan
Key: CALCITE-5284
URL: https://issues.apache.org/jira/browse/CALCITE-5284
Project: Calcite
Issue Type: Bug
Environment: Calcite 1.31.1. on Mac
Reporter: Ulrich Kramer
The following unit test for {{JdbcAdapterTest}} fails:
{code:java}
@Test void testOffset() {
CalciteAssert.model(FoodmartSchema.FOODMART_MODEL)
.query("select * from \"sales_fact_1997\" limit 10 offset 20")
.explainContains("PLAN=JdbcToEnumerableConverter\n" +
" JdbcSort(offset=[20], fetch=[10])\n" +
"JdbcTableScan(table=[[foodmart, sales_fact_1997]])")
.runs();
}
{code}
For an offset less than 13, the correct plan is created. With an offset above
13, the plan looks like this:
{code}
EnumerableLimit(offset=[20], fetch=[10])
JdbcToEnumerableConverter
JdbcTableScan(table=[[foodmart, sales_fact_1997]])
{code}
which can lead to enormous latency times, since the entire table is loaded via
JDBC.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Created] (CALCITE-5282) JdbcValues should add CAST on NULL values
Ulrich Kramer created CALCITE-5282:
--
Summary: JdbcValues should add CAST on NULL values
Key: CALCITE-5282
URL: https://issues.apache.org/jira/browse/CALCITE-5282
Project: Calcite
Issue Type: Bug
Environment: Calcite 1.13.1 on Mac
Reporter: Ulrich Kramer
The following unit test in {{JdbcAdapterTest.java}} is working fine
{code:java}
@Test void testNullValuesPlan() {
final String sql = "select empno, ename, e.deptno, dname\n"
+ "from scott.emp e left outer join (select * from scott.dept where 0 =
1) d\n"
+ "on e.deptno = d.deptno";
final String explain = "PLAN=JdbcToEnumerableConverter\n" +
" JdbcProject(EMPNO=[$0], ENAME=[$1], DEPTNO=[$2], DNAME=[$4])\n" +
"JdbcJoin(condition=[=($2, $3)], joinType=[left])\n" +
" JdbcProject(EMPNO=[$0], ENAME=[$1], DEPTNO=[$7])\n" +
"JdbcTableScan(table=[[SCOTT, EMP]])\n" +
" JdbcValues(tuples=[[]])\n\n";
final String jdbcSql = "SELECT \"t\".\"EMPNO\", \"t\".\"ENAME\",
\"t\".\"DEPTNO\", \"t0\".\"DNAME\"\n" +
"FROM (SELECT \"EMPNO\", \"ENAME\", \"DEPTNO\"\n" +
"FROM \"SCOTT\".\"EMP\") AS \"t\"\n" +
"LEFT JOIN (SELECT *\n" +
"FROM (VALUES (NULL, NULL)) AS \"t\" (\"DEPTNO\", \"DNAME\")\n" +
"WHERE 1 = 0) AS \"t0\" ON \"t\".\"DEPTNO\" = \"t0\".\"DEPTNO\"";
CalciteAssert.model(JdbcTest.SCOTT_MODEL)
.query(sql)
.explainContains(explain)
.runs();
}
{code}
The problem is that {{JdbcValues}} is loosing the type information for each
{{NULL}} column
and postgres complains about that. Inside the join condition {{t.DEPTNO =
to.DEPTNO}}. postgres doesn't know the type of {{t.DEPTNO}}, assumes it's of
type {{TEXT}} and raises an error like {{ERROR: operator does not exist: text =
integer, Hint: No operator matches the given name and argument types. You
might need to add explicit type casts.}}
Would it be possible to add a {{CAST}} in case of {{NULL}} values in
{{JdbcValues}}.
Changing {{VALUES (NULL, NULL)}} to {{VALUES (CAST(NULL AS ...), CAST(NULL AS
...))}} in the resulting SQL statement.
If it is appreciated, we could provide a PR.
If you are asking yourself, why we are doing something strange like {{WHERE 1 =
0}}: We are applying row level access policies as WHERE condition. In this case
the user has no access to the table at all.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Created] (CALCITE-5243) SELECT NULL AS C causes NoSuchMethodException: java.sql.ResultSet.getVoid(int)
Ulrich Kramer created CALCITE-5243:
--
Summary: SELECT NULL AS C causes NoSuchMethodException:
java.sql.ResultSet.getVoid(int)
Key: CALCITE-5243
URL: https://issues.apache.org/jira/browse/CALCITE-5243
Project: Calcite
Issue Type: Bug
Environment: Calcite 1.31.0
Reporter: Ulrich Kramer
Adding the following unit test to JdbcAdapterTest will reproduce the bug
{code:java}
@Test void testNullSelect() {
final String sql = "select NULL AS C from \"days\"";
CalciteAssert.model(FoodmartSchema.FOODMART_MODEL)
.query(sql)
.runs()
.returnsCount(14);
}
{code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Created] (CALCITE-4688) SqlLibraryOperators.TO_TIMESTAMP has incorrect return type
Ulrich Kramer created CALCITE-4688:
--
Summary: SqlLibraryOperators.TO_TIMESTAMP has incorrect return type
Key: CALCITE-4688
URL: https://issues.apache.org/jira/browse/CALCITE-4688
Project: Calcite
Issue Type: Bug
Environment: All
Reporter: Ulrich Kramer
{code:java}
public static final SqlFunction TO_TIMESTAMP =
new SqlFunction("TO_TIMESTAMP",
SqlKind.OTHER_FUNCTION,
ReturnTypes.DATE_NULLABLE,
null,
OperandTypes.STRING_STRING,
SqlFunctionCategory.TIMEDATE);
{code}
should not have {{ReturnTypes.DATE_NULLABLE}} but
{{ReturnTypes.TIMESTAMP_NULLABLE}}
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Created] (CALCITE-4655) NullPointerException in JdbcTable.scan
Ulrich Kramer created CALCITE-4655:
--
Summary: NullPointerException in JdbcTable.scan
Key: CALCITE-4655
URL: https://issues.apache.org/jira/browse/CALCITE-4655
Project: Calcite
Issue Type: Improvement
Affects Versions: 1.27.0, 1.26.0
Reporter: Ulrich Kramer
Fix For: next
Calling {{JdbcTable.scan}} will result in a {{NullPointerException}} because
inside {{JdbcTable.fieldClasses}} {{protoRowType}} is null.
This could easily be fixed by replacing
{code:java}
final RelDataType rowType = requireNonNull(protoRowType,
"protoRowType").apply(typeFactory);
{code}
with
{code:java}
final RelDataType rowType = getRowType(typeFactory);
{code}
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Created] (CALCITE-4654) CloneSchema doesn't cache
Ulrich Kramer created CALCITE-4654:
--
Summary: CloneSchema doesn't cache
Key: CALCITE-4654
URL: https://issues.apache.org/jira/browse/CALCITE-4654
Project: Calcite
Issue Type: Improvement
Components: core
Affects Versions: 1.27.0, 1.26.0
Environment: All
Reporter: Ulrich Kramer
{{CloneSchema}} doesn't really cache the content of the source schema.
During the preparation of a SQL statement {{CloneSchema.getTableMap}} is called
several times and each time all tables are reloaded.
The following callstack shows the problem
{code}
:69, ArrayTable {org.apache.calcite.adapter.clone}
createCloneTable:119, CloneSchema {org.apache.calcite.adapter.clone}
createCloneTable:88, CloneSchema {org.apache.calcite.adapter.clone}
getTableMap:76, CloneSchema {org.apache.calcite.adapter.clone}
getTable:95, AbstractSchema {org.apache.calcite.schema.impl}
getImplicitTable:146, CachingCalciteSchema {org.apache.calcite.jdbc}
getTable:295, CalciteSchema {org.apache.calcite.jdbc}
resolve_:145, EmptyScope {org.apache.calcite.sql.validate}
resolveTable:101, EmptyScope {org.apache.calcite.sql.validate}
resolveTable:214, DelegatingScope {org.apache.calcite.sql.validate}
resolveImpl:116, IdentifierNamespace {org.apache.calcite.sql.validate}
validateImpl:188, IdentifierNamespace {org.apache.calcite.sql.validate}
validate:89, AbstractNamespace {org.apache.calcite.sql.validate}
validateNamespace:1098, SqlValidatorImpl {org.apache.calcite.sql.validate}
validateQuery:1069, SqlValidatorImpl {org.apache.calcite.sql.validate}
validateFrom:3380, SqlValidatorImpl {org.apache.calcite.sql.validate}
validateFrom:3362, SqlValidatorImpl {org.apache.calcite.sql.validate}
validateSelect:3644, SqlValidatorImpl {org.apache.calcite.sql.validate}
validateImpl:64, SelectNamespace {org.apache.calcite.sql.validate}
validate:89, AbstractNamespace {org.apache.calcite.sql.validate}
validateNamespace:1098, SqlValidatorImpl {org.apache.calcite.sql.validate}
validateQuery:1069, SqlValidatorImpl {org.apache.calcite.sql.validate}
validate:247, SqlSelect {org.apache.calcite.sql}
validateScopedExpression:1044, SqlValidatorImpl
{org.apache.calcite.sql.validate}
validate:750, SqlValidatorImpl {org.apache.calcite.sql.validate}
convertQuery:585, SqlToRelConverter {org.apache.calcite.sql2rel}
prepareSql:251, Prepare {org.apache.calcite.prepare}
prepareSql:215, Prepare {org.apache.calcite.prepare}
prepare2_:647, CalcitePrepareImpl {org.apache.calcite.prepare}
prepare_:513, CalcitePrepareImpl {org.apache.calcite.prepare}
prepareSql:483, CalcitePrepareImpl {org.apache.calcite.prepare}
parseQuery:249, CalciteConnectionImpl {org.apache.calcite.jdbc}
prepareAndExecute:623, CalciteMetaImpl {org.apache.calcite.jdbc}
prepareAndExecuteInternal:675, AvaticaConnection {org.apache.calcite.avatica}
executeInternal:156, AvaticaStatement {org.apache.calcite.avatica}
executeQuery:227, AvaticaStatement {org.apache.calcite.avatica}
{code}
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Created] (CALCITE-4640) Propagate table scan hints to JDBC
Ulrich Kramer created CALCITE-4640:
--
Summary: Propagate table scan hints to JDBC
Key: CALCITE-4640
URL: https://issues.apache.org/jira/browse/CALCITE-4640
Project: Calcite
Issue Type: Improvement
Components: core
Affects Versions: 1.27.0
Reporter: Ulrich Kramer
Fix For: next
We would like to use table scan hints to pass [parameters and variables to HANA
views|https://help.sap.com/viewer/88fe5f56472e40cca6ef3c3dcab4855b/2.0.04/en-US/fafb3ea432e54fca9eff11648df5bccd.html].
It should be possible to convert the following Calcite SQL
{code}
SELECT * FROM VIEW /*+ PLACEHOLDERS("$$PARAMETER_1$$"='Test') */
{code}
using a special {{SqlDialect}} to a HANA specific statement
{code}
SELECT * FROM VIEW ('PLACEHOLDER' = ('$$PARAMETER_1$$', 'Test'))
{code}
See also my [mail in the mailing
list|https://mail-archives.apache.org/mod_mbox/calcite-dev/202105.mbox/%3CD161B82F-5DEC-49A2-A873-4817F4DEB15F%40contoso.com%3E].
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
