[jira] [Resolved] (CALCITE-6231) JDBC adapter generates "UNNEST" when it should generate "UNNEST ... WITH ORDINALITY"
[
https://issues.apache.org/jira/browse/CALCITE-6231?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
hongyu guo resolved CALCITE-6231.
-
Fix Version/s: 1.37.0
Resolution: Fixed
> JDBC adapter generates "UNNEST" when it should generate "UNNEST ... WITH
> ORDINALITY"
>
>
> Key: CALCITE-6231
> URL: https://issues.apache.org/jira/browse/CALCITE-6231
> Project: Calcite
> Issue Type: Bug
>Reporter: EveyWu
>Assignee: EveyWu
>Priority: Major
> Labels: pull-request-available
> Fix For: 1.37.0
>
>
> The syntax UNNEST() WITH ORDINALITY is missing the ORDINALITY keyword.
> For example sql：
> {code:java}
> select did + 1 from
> unnest(select collect("department_id") as deptid from "department")
> with ordinality as t(did, pos){code}
>
> current planned sql：
> {code:java}
> SELECT DEPTID + 1 FROM UNNEST (
> SELECT COLLECT("department_id") AS "DEPTID" FROM "foodmart"."department")
> AS "t0" ("DEPTID", "ORDINALITY") {code}
>
> fixed planned sql：
> {code:java}
> SELECT "DEPTID" + 1 FROM UNNEST (
> SELECT COLLECT("department_id") AS "DEPTID" FROM "foodmart"."department")
> WITH ORDINALITY AS "t0" ("DEPTID", "ORDINALITY") {code}
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Comment Edited] (CALCITE-6231) JDBC adapter generates "UNNEST" when it should generate "UNNEST ... WITH ORDINALITY"
[
https://issues.apache.org/jira/browse/CALCITE-6231?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17812562#comment-17812562
]
hongyu guo edited comment on CALCITE-6231 at 1/31/24 7:15 AM:
--
Fixed in
[351ddeb4|https://github.com/apache/calcite/commit/351ddeb47b8dfb5c196c563920290a79575e9864].
[~eveywu] Thanks for your contribution!
was (Author: JIRAUSER300840):
Fix in
[351ddeb4|https://github.com/apache/calcite/commit/351ddeb47b8dfb5c196c563920290a79575e9864].
[~eveywu] Thanks for your contribution!
> JDBC adapter generates "UNNEST" when it should generate "UNNEST ... WITH
> ORDINALITY"
>
>
> Key: CALCITE-6231
> URL: https://issues.apache.org/jira/browse/CALCITE-6231
> Project: Calcite
> Issue Type: Bug
>Reporter: EveyWu
>Assignee: EveyWu
>Priority: Major
> Labels: pull-request-available
>
> The syntax UNNEST() WITH ORDINALITY is missing the ORDINALITY keyword.
> For example sql：
> {code:java}
> select did + 1 from
> unnest(select collect("department_id") as deptid from "department")
> with ordinality as t(did, pos){code}
>
> current planned sql：
> {code:java}
> SELECT DEPTID + 1 FROM UNNEST (
> SELECT COLLECT("department_id") AS "DEPTID" FROM "foodmart"."department")
> AS "t0" ("DEPTID", "ORDINALITY") {code}
>
> fixed planned sql：
> {code:java}
> SELECT "DEPTID" + 1 FROM UNNEST (
> SELECT COLLECT("department_id") AS "DEPTID" FROM "foodmart"."department")
> WITH ORDINALITY AS "t0" ("DEPTID", "ORDINALITY") {code}
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6231) JDBC adapter generates "UNNEST" when it should generate "UNNEST ... WITH ORDINALITY"
[
https://issues.apache.org/jira/browse/CALCITE-6231?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17812562#comment-17812562
]
hongyu guo commented on CALCITE-6231:
-
Fix in
[351ddeb4|https://github.com/apache/calcite/commit/351ddeb47b8dfb5c196c563920290a79575e9864].
[~eveywu] Thanks for your contribution!
> JDBC adapter generates "UNNEST" when it should generate "UNNEST ... WITH
> ORDINALITY"
>
>
> Key: CALCITE-6231
> URL: https://issues.apache.org/jira/browse/CALCITE-6231
> Project: Calcite
> Issue Type: Bug
>Reporter: EveyWu
>Assignee: EveyWu
>Priority: Major
> Labels: pull-request-available
>
> The syntax UNNEST() WITH ORDINALITY is missing the ORDINALITY keyword.
> For example sql：
> {code:java}
> select did + 1 from
> unnest(select collect("department_id") as deptid from "department")
> with ordinality as t(did, pos){code}
>
> current planned sql：
> {code:java}
> SELECT DEPTID + 1 FROM UNNEST (
> SELECT COLLECT("department_id") AS "DEPTID" FROM "foodmart"."department")
> AS "t0" ("DEPTID", "ORDINALITY") {code}
>
> fixed planned sql：
> {code:java}
> SELECT "DEPTID" + 1 FROM UNNEST (
> SELECT COLLECT("department_id") AS "DEPTID" FROM "foodmart"."department")
> WITH ORDINALITY AS "t0" ("DEPTID", "ORDINALITY") {code}
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Updated] (CALCITE-6235) The MAP_CONTAINS_KEY function result does not necessarily correspond to the Spark result
[ https://issues.apache.org/jira/browse/CALCITE-6235?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Caican Cai updated CALCITE-6235: Summary: The MAP_CONTAINS_KEY function result does not necessarily correspond to the Spark result (was: MAP_CONTAINS_KEY function results correspond to Spark results) > The MAP_CONTAINS_KEY function result does not necessarily correspond to the > Spark result > > > Key: CALCITE-6235 > URL: https://issues.apache.org/jira/browse/CALCITE-6235 > Project: Calcite > Issue Type: Bug > Components: core >Affects Versions: 1.36.0 >Reporter: Caican Cai >Priority: Minor > > map_contains_key(map(1, 2), cast(1 as double) returns false but spark returns > true. > This may involve some node type conversion bug > But I personally tend to think that this is a bug of Spark itself. Calcite > does not necessarily need to return true. > > https://github.com/apache/calcite/pull/3655 -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Commented] (CALCITE-6235) The MAP_CONTAINS_KEY function result does not necessarily correspond to the Spark result
[ https://issues.apache.org/jira/browse/CALCITE-6235?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17812524#comment-17812524 ] Caican Cai commented on CALCITE-6235: - ok， thank you > The MAP_CONTAINS_KEY function result does not necessarily correspond to the > Spark result > > > Key: CALCITE-6235 > URL: https://issues.apache.org/jira/browse/CALCITE-6235 > Project: Calcite > Issue Type: Bug > Components: core >Affects Versions: 1.36.0 >Reporter: Caican Cai >Priority: Minor > > map_contains_key(map(1, 2), cast(1 as double) returns false but spark returns > true. > This may involve some node type conversion bug > But I personally tend to think that this is a bug of Spark itself. Calcite > does not necessarily need to return true. > > https://github.com/apache/calcite/pull/3655 -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Resolved] (CALCITE-6208) JSON_VALUE 'RETURNING' syntax support for arrays with nullable elements
[
https://issues.apache.org/jira/browse/CALCITE-6208?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Mihai Budiu resolved CALCITE-6208.
--
Fix Version/s: 1.37.0
Resolution: Fixed
Resolved in
https://github.com/apache/calcite/commit/de39888754759d3e6b7ce35770fcff3d8d0e6a4c
Thank you, [~cwylie]
> JSON_VALUE 'RETURNING' syntax support for arrays with nullable elements
> ---
>
> Key: CALCITE-6208
> URL: https://issues.apache.org/jira/browse/CALCITE-6208
> Project: Calcite
> Issue Type: Bug
> Components: core
>Reporter: Clint Wylie
>Priority: Minor
> Labels: pull-request-available
> Fix For: 1.37.0
>
>
> The JSON_VALUE function RETURNING syntax is not currently super array
> friendly because it creates an array type with non-null elements. This is
> primarily due to the interaction between SqlDataTypeSpec which creates a not
> null array with not null elements, and the
> SqlJsonValueFunction which forces nullable of the type, but that in turn does
> not force nullable for the element type, so it results in a nullable array
> but still with not null elements.
>
> In a lot of cases this isn't a huge problem, but coupled with unnest and
> filtering, it results in filters being incorrectly eliminated after being
> incorrectly classified as 'all true' or 'all false'.
> For example, given a query such as
>
> {code:java}
> select c
> from table, unnest(json_value(nested, '$.c' returning bigint array)) as u(c)
> where c is not null{code}
> c is inferred to be 'BIGINT NOT NULL', which results in 'c is not null' being
> dropped.
> I believe the solution to this is to modify the return type inference to
> include special handling for when the SqlDataTypeSpec is an array type to
> force both the array and its elements to be nullable.
> I tested this out and it appears to work as expected.
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6206) Add library operator mapping for unparsing
[ https://issues.apache.org/jira/browse/CALCITE-6206?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17812427#comment-17812427 ] Tanner Clary commented on CALCITE-6206: --- I'm beginning to work on this, I was thinking maybe each dialect has a map with `SqlKind` as the key and `SqlOperator` as the value. There's actually a small PoC in https://github.com/apache/calcite/pull/3649 if either of you wanted to look. I didn't want to do all the libraries in case there were issues with my approach. > Add library operator mapping for unparsing > -- > > Key: CALCITE-6206 > URL: https://issues.apache.org/jira/browse/CALCITE-6206 > Project: Calcite > Issue Type: Improvement >Reporter: Tanner Clary >Assignee: Tanner Clary >Priority: Major > > [~julianhyde] and I have talked about adding the ability to reference > cross-dialect operator aliases during unparsing so it does not need to be > "manually added". > For instance, if unparsing a call for the "STARTS_WITH" operator for the > Snowflake dialect, it would be nice if there was some sort of map that could > be referenced to see that the call should be unparsed with "STARTSWITH" > instead. -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Comment Edited] (CALCITE-6221) JDBC adapter generates invalid query when the same table is joined multiple times
[
https://issues.apache.org/jira/browse/CALCITE-6221?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17812425#comment-17812425
]
Julian Hyde edited comment on CALCITE-6221 at 1/30/24 5:39 PM:
---
Thank you for reducing the number of cosmetic changes. It is now easier to
review and see the impact.
I agree that the subselect is necessary for the outer join case,
{code:java}
SELECT * FROM (
SELECT * FROM A
LEFT OUTER JOIN ( SELECT ID AS ID0 FROM B ) D ON A.ID = D.ID0
WHERE ...
) AS C{code}
However, there seem to be changes for inner join cases with narrower SELECT
clauses, e.g.
{code:java}
SELECT X, X0 FROM (
SELECT * FROM A
INNER JOIN ( SELECT ID AS ID0, X AS X0 FROM B ) D ON A.ID = D.ID0
WHERE ...
) AS C{code}
Those cases don't seem necessary. The following would suffice:
{code:java}
SELECT A.X, B.X AS X0
FROM A
INNER JOIN B ON A.ID = B.ID
WHERE ...{code}
was (Author: julianhyde):
I agree that the subselect is necessary for the outer join case,
{code:java}
SELECT * FROM (
SELECT * FROM A
LEFT OUTER JOIN ( SELECT ID AS ID0 FROM B ) D ON A.ID = D.ID0
WHERE ...
) AS C{code}
However, there seem to be changes for inner join cases with narrower SELECT
clauses, e.g.
{code:java}
SELECT X, X0 FROM (
SELECT * FROM A
INNER JOIN ( SELECT ID AS ID0, X AS X0 FROM B ) D ON A.ID = D.ID0
WHERE ...
) AS C{code}
Those cases don't seem necessary. The following would suffice:
{code:java}
SELECT A.X, B.X AS X0
FROM A
INNER JOIN B ON A.ID = B.ID
WHERE ...{code}
> JDBC adapter generates invalid query 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
>Priority: Major
> Labels: pull-request-available
>
> 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] [Commented] (CALCITE-6221) JDBC adapter generates invalid query when the same table is joined multiple times
[
https://issues.apache.org/jira/browse/CALCITE-6221?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17812425#comment-17812425
]
Julian Hyde commented on CALCITE-6221:
--
I agree that the subselect is necessary for the outer join case,
{code:java}
SELECT * FROM (
SELECT * FROM A
LEFT OUTER JOIN ( SELECT ID AS ID0 FROM B ) D ON A.ID = D.ID0
WHERE ...
) AS C{code}
However, there seem to be changes for inner join cases with narrower SELECT
clauses, e.g.
{code:java}
SELECT X, X0 FROM (
SELECT * FROM A
INNER JOIN ( SELECT ID AS ID0, X AS X0 FROM B ) D ON A.ID = D.ID0
WHERE ...
) AS C{code}
Those cases don't seem necessary. The following would suffice:
{code:java}
SELECT A.X, B.X AS X0
FROM A
INNER JOIN B ON A.ID = B.ID
WHERE ...{code}
> JDBC adapter generates invalid query 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
>Priority: Major
> Labels: pull-request-available
>
> 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] [Commented] (CALCITE-6235) MAP_CONTAINS_KEY function results correspond to Spark results
[ https://issues.apache.org/jira/browse/CALCITE-6235?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17812415#comment-17812415 ] Julian Hyde commented on CALCITE-6235: -- That expression doesn't have a valid type. You can't use a DOUBLE key to search within a map whose key is INTEGER. Also, can you fix the summary? Right now it says that we are consistent with Spark. > MAP_CONTAINS_KEY function results correspond to Spark results > - > > Key: CALCITE-6235 > URL: https://issues.apache.org/jira/browse/CALCITE-6235 > Project: Calcite > Issue Type: Bug > Components: core >Affects Versions: 1.36.0 >Reporter: Caican Cai >Priority: Minor > > map_contains_key(map(1, 2), cast(1 as double) returns false but spark returns > true. > This may involve some node type conversion bug > But I personally tend to think that this is a bug of Spark itself. Calcite > does not necessarily need to return true. > > https://github.com/apache/calcite/pull/3655 -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Created] (CALCITE-6235) MAP_CONTAINS_KEY function results correspond to Spark results
Caican Cai created CALCITE-6235: --- Summary: MAP_CONTAINS_KEY function results correspond to Spark results Key: CALCITE-6235 URL: https://issues.apache.org/jira/browse/CALCITE-6235 Project: Calcite Issue Type: Bug Components: core Affects Versions: 1.36.0 Reporter: Caican Cai map_contains_key(map(1, 2), cast(1 as double) returns false but spark returns true. This may involve some node type conversion bug But I personally tend to think that this is a bug of Spark itself. Calcite does not necessarily need to return true. https://github.com/apache/calcite/pull/3655 -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Updated] (CALCITE-6234) Add Test on SqlOperatorTest for format_date function(enable Bigquery and pg)
[ https://issues.apache.org/jira/browse/CALCITE-6234?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] ASF GitHub Bot updated CALCITE-6234: Labels: pull-request-available (was: ) > Add Test on SqlOperatorTest for format_date function(enable Bigquery and pg) > > > Key: CALCITE-6234 > URL: https://issues.apache.org/jira/browse/CALCITE-6234 > Project: Calcite > Issue Type: Test > Components: tests >Affects Versions: 1.36.0 >Reporter: Caican Cai >Priority: Trivial > Labels: pull-request-available > Fix For: 1.37.0 > > > Since the implementation of pg's format_date and big_query's format_date are > the same, but their usage is different, I will add the corresponding test > display. -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Created] (CALCITE-6234) Add Test on SqlOperatorTest for format_date function(enable Bigquery and pg)
Caican Cai created CALCITE-6234: --- Summary: Add Test on SqlOperatorTest for format_date function(enable Bigquery and pg) Key: CALCITE-6234 URL: https://issues.apache.org/jira/browse/CALCITE-6234 Project: Calcite Issue Type: Test Components: tests Affects Versions: 1.36.0 Reporter: Caican Cai Fix For: 1.37.0 Since the implementation of pg's format_date and big_query's format_date are the same, but their usage is different, I will add the corresponding test display. -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Comment Edited] (CALCITE-6221) JDBC adapter generates invalid query when the same table is joined multiple times
[
https://issues.apache.org/jira/browse/CALCITE-6221?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17812318#comment-17812318
]
Ulrich Kramer edited comment on CALCITE-6221 at 1/30/24 1:39 PM:
-
I also thought about applying {{FilterIntoJoinRule}} only for none JDBC
relations. But this could lead to query plans, which are much more expensive.
In our application, the issue is also fixed by disabling this rule.
was (Author: kramerul):
I also thought about applying {{FilterIntoJoinRule}} only for none JDBC rules.
But this could lead to query plans, which are much more expensive.
In our application, the issue is also fixed by disabling this rule.
> JDBC adapter generates invalid query 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
>Priority: Major
> Labels: pull-request-available
>
> 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] [Comment Edited] (CALCITE-6221) JDBC adapter generates invalid query when the same table is joined multiple times
[
https://issues.apache.org/jira/browse/CALCITE-6221?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17812318#comment-17812318
]
Ulrich Kramer edited comment on CALCITE-6221 at 1/30/24 1:38 PM:
-
I also thought about applying {{FilterIntoJoinRule}} only for none JDBC rules.
But this could lead to query plans, which are much more expensive.
In our application, the issue is also fixed by disabling this rule.
was (Author: kramerul):
I also thought about applying {{FilterIntoJoinRule}} only for none JDBC rules.
But this could lead to query plans, which are much more expensive.
> JDBC adapter generates invalid query 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
>Priority: Major
> Labels: pull-request-available
>
> 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] [Commented] (CALCITE-6221) JDBC adapter generates invalid query when the same table is joined multiple times
[
https://issues.apache.org/jira/browse/CALCITE-6221?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17812318#comment-17812318
]
Ulrich Kramer commented on CALCITE-6221:
I also thought about applying {{FilterIntoJoinRule}} only for none JDBC rules.
But this could lead to query plans, which are much more expensive.
> JDBC adapter generates invalid query 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
>Priority: Major
> Labels: pull-request-available
>
> 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] [Comment Edited] (CALCITE-6221) JDBC adapter generates invalid query when the same table is joined multiple times
[
https://issues.apache.org/jira/browse/CALCITE-6221?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17812296#comment-17812296
]
Ulrich Kramer edited comment on CALCITE-6221 at 1/30/24 1:14 PM:
-
Now, I restricted the renaming of the fields in SQL to our special case. [Only
if there is a {{Filter}} around a
{{Join}}|https://github.com/sap-contributions/calcite/blob/e8f266dceb27841b07ed00aa697bfe8c9c6aa270/core/src/main/java/org/apache/calcite/rel/rel2sql/RelToSqlConverter.java#L268-L274]
an additional sub-select is inserted.
Before the fix, a statement like the following one was generated for a filter
around a join:
{code:SQL}
SELECT * FROM (
SELECT * FROM A
LEFT OUTER JOIN B ON A.ID = B.ID
WHERE ...
) AS C
{code}
As {{A}} and {{B}} are no longer visible outside this sub-select, it's not
possible to use them in a {{GROUP BY}}. Before the the fix a {{GROUP BY B.ID}}
was translated to {{GROUP BY C.ID0}}, which is not correct since the column
{{ID0}} does not exist.
The fix modifies the generated statement like this
{code:SQL}
SELECT * FROM (
SELECT * FROM A
LEFT OUTER JOIN ( SELECT ID AS ID0 FROM B ) D ON A.ID = D.ID0
WHERE ...
) AS C
{code}
Now {{C.ID0}} exists and can be used for grouping and other stuff.
I would be really grateful if someone has a better idea to detect or handle
this special case.
was (Author: kramerul):
Now, I restricted the renaming of the fields in SQL to our special case. [Only
if there is a {{Filter}} around a
{{Join}}|https://github.com/sap-contributions/calcite/blob/e8f266dceb27841b07ed00aa697bfe8c9c6aa270/core/src/main/java/org/apache/calcite/rel/rel2sql/RelToSqlConverter.java#L268-L274]
an additional sub-select is inserted.
Before the fix, a statement like the following one was generated for a filter
around a join:
{code:SQL}
SELECT * FROM (
SELECT * FROM A
LEFT OUTER JOIN B ON A.ID = B.ID
WHERE ...
) AS C
{code}
As {{A}} and {{B}} are no longer visible outside this sub-select, it's not
possible to use them in a {{GROUP BY}}. Before the the fix a {{GROUP BY B.ID}}
was translated to {{GROUP BY C.ID0}}, which is not correct since the column
{{ID0}} does not exist.
The fix modifies the generated statement like this
{code:SQL}
SELECT * FROM (
SELECT * FROM A
LEFT OUTER JOIN ( SELECT ID AS ID0 FROM B ) D ON A.ID = D.ID0
WHERE ...
) AS C
{code}
Now {{C.ID0}} exists and can be used for grouping and other stuff.
I would be really grateful if someone has a better idea to detect this special
case.
> JDBC adapter generates invalid query 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
>Priority: Major
> Labels: pull-request-available
>
> 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
>
[jira] [Comment Edited] (CALCITE-6221) JDBC adapter generates invalid query when the same table is joined multiple times
[
https://issues.apache.org/jira/browse/CALCITE-6221?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17812296#comment-17812296
]
Ulrich Kramer edited comment on CALCITE-6221 at 1/30/24 1:09 PM:
-
Now, I restricted the renaming of the fields in SQL to our special case. [Only
if there is a {{Filter}} around a
{{Join}}|https://github.com/sap-contributions/calcite/blob/e8f266dceb27841b07ed00aa697bfe8c9c6aa270/core/src/main/java/org/apache/calcite/rel/rel2sql/RelToSqlConverter.java#L268-L274]
an additional sub-select is inserted.
Before the fix, a statement like the following one was generated for a filter
around a join:
{code:SQL}
SELECT * FROM (
SELECT * FROM A
LEFT OUTER JOIN B ON A.ID = B.ID
WHERE ...
) AS C
{code}
As {{A}} and {{B}} are no longer visible outside this sub-select, it's not
possible to use them in a {{GROUP BY}}. Before the the fix a {{GROUP BY B.ID}}
was translated to {{GROUP BY C.ID0}}, which is not correct since the column
{{ID0}} does not exist.
The fix modifies the generated statement like this
{code:SQL}
SELECT * FROM (
SELECT * FROM A
LEFT OUTER JOIN ( SELECT ID AS ID0 FROM B ) D ON A.ID = D.ID0
WHERE ...
) AS C
{code}
Now {{C.ID0}} exists and can be used for grouping and other stuff.
I would be really grateful if someone has a better idea to detect this special
case.
was (Author: kramerul):
Now, I restricted the renaming of the fields in SQL to our special case. [Only
if there is a {{Filter}} around a
{{Join}}|https://github.com/sap-contributions/calcite/blob/e8f266dceb27841b07ed00aa697bfe8c9c6aa270/core/src/main/java/org/apache/calcite/rel/rel2sql/RelToSqlConverter.java#L268-L274]
an additional sub-select is inserted.
Before the fix, a statement like the following one was generated:
{code:SQL}
SELECT * FROM (
SELECT * FROM A
LEFT OUTER JOIN B ON A.ID = B.ID
WHERE ...
) AS C
{code}
As {{A}} and {{B}} are no longer visible outside this sub-select, it's not
possible to use them in a {{GROUP BY}}. Before the the fix a {{GROUP BY B.ID}}
was translated to {{GROUP BY C.ID0}}, which is not correct since the column
{{ID0}} does not exist.
The fix modifies the generated statement like this
{code:SQL}
SELECT * FROM (
SELECT * FROM A
LEFT OUTER JOIN ( SELECT ID AS ID0 FROM B ) D ON A.ID = D.ID0
WHERE ...
) AS C
{code}
Now {{C.ID0}} exists and can be used for grouping and other stuff.
I would be really grateful if someone has a better idea to detect this special
case.
> JDBC adapter generates invalid query 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
>Priority: Major
> Labels: pull-request-available
>
> 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"
>
[jira] [Comment Edited] (CALCITE-6221) JDBC adapter generates invalid query when the same table is joined multiple times
[
https://issues.apache.org/jira/browse/CALCITE-6221?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17812296#comment-17812296
]
Ulrich Kramer edited comment on CALCITE-6221 at 1/30/24 1:09 PM:
-
Now, I restricted the renaming of the fields in SQL to our special case. [Only
if there is a {{Filter}} around a
{{Join}}|https://github.com/sap-contributions/calcite/blob/e8f266dceb27841b07ed00aa697bfe8c9c6aa270/core/src/main/java/org/apache/calcite/rel/rel2sql/RelToSqlConverter.java#L268-L274]
an additional sub-select is inserted.
Before the fix, a statement like the following one was generated:
{code:SQL}
SELECT * FROM (
SELECT * FROM A
LEFT OUTER JOIN B ON A.ID = B.ID
WHERE ...
) AS C
{code}
As {{A}} and {{B}} are no longer visible outside this sub-select, it's not
possible to use them in a {{GROUP BY}}. Before the the fix a {{GROUP BY B.ID}}
was translated to {{GROUP BY C.ID0}}, which is not correct since the column
{{ID0}} does not exist.
The fix modifies the generated statement like this
{code:SQL}
SELECT * FROM (
SELECT * FROM A
LEFT OUTER JOIN ( SELECT ID AS ID0 FROM B ) D ON A.ID = D.ID0
WHERE ...
) AS C
{code}
Now {{C.ID0}} exists and can be used for grouping and other stuff.
I would be really grateful if someone has a better idea to detect this special
case.
was (Author: kramerul):
Now, I restricted the renaming of the fields in SQL to our special case. [Only
if there is a {{Filter}} around a
{{Join}}|https://github.com/sap-contributions/calcite/blob/e8f266dceb27841b07ed00aa697bfe8c9c6aa270/core/src/main/java/org/apache/calcite/rel/rel2sql/RelToSqlConverter.java#L268-L274]
an additional sub-select is inserted.
Before the fix, a statement like the following one was generated:
{code:SQL}
SELECT * FROM (
SELECT * FROM A
LEFT OUTER JOIN B ON A.ID = B.ID
WHERE ...
) AS C
{code}
As {{A}} and {{B}} are no longer visible outside this sub-select, it's not
possible to use them in a {{GROUP BY}}. Before the the fix a {{GROUP BY B.ID}}
was translated to {{GROUP BY C.ID0}}, which is not correct since the column
{{ID0}} does not exist.
The fix modifies the generated statement like this
{code:SQL}
SELECT * FROM (
SELECT * FROM A
LEFT OUTER JOIN ( SELECT ID AS ID0 FROM B ) D ON A.ID = D.ID0
WHERE ...
) AS C
{code}
Now {{C.ID0}} exists and can be used for grouping and other stuff.
> JDBC adapter generates invalid query 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
>Priority: Major
> Labels: pull-request-available
>
> 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"
>
[jira] [Comment Edited] (CALCITE-6221) JDBC adapter generates invalid query when the same table is joined multiple times
[
https://issues.apache.org/jira/browse/CALCITE-6221?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17812296#comment-17812296
]
Ulrich Kramer edited comment on CALCITE-6221 at 1/30/24 1:06 PM:
-
Now, I restricted the renaming of the fields in SQL to our special case. [Only
if there is a {{Filter}} around a
{{Join}}|https://github.com/sap-contributions/calcite/blob/e8f266dceb27841b07ed00aa697bfe8c9c6aa270/core/src/main/java/org/apache/calcite/rel/rel2sql/RelToSqlConverter.java#L268-L274]
an additional sub-select is inserted.
Before the fix, a statement like the following one was generated:
{code:SQL}
SELECT * FROM (
SELECT * FROM A
LEFT OUTER JOIN B ON A.ID = B.ID
WHERE ...
) AS C
{code}
As {{A}} and {{B}} are no longer visible outside this sub-select, it's not
possible to use them in a {{GROUP BY}}. Before the the fix a {{GROUP BY B.ID}}
was translated to {{GROUP BY C.ID0}}, which is not correct since the column
{{ID0}} does not exist.
The fix modifies the generated statement like this
{code:SQL}
SELECT * FROM (
SELECT * FROM A
LEFT OUTER JOIN ( SELECT ID AS ID0 FROM B ) D ON A.ID = D.ID0
WHERE ...
) AS C
{code}
Now {{C.ID0}} exists and can be used for grouping and other stuff.
was (Author: kramerul):
Now, I restricted the renaming of the fields in SQL to our special case. If
there is a {{Filter}} around a {{Join}} an additional sub-select is inserted.
Before the fix, a statement like the following one was generated:
{code:SQL}
SELECT * FROM (
SELECT * FROM A
LEFT OUTER JOIN B ON A.ID = B.ID
WHERE ...
) AS C
{code}
As {{A}} and {{B}} are no longer visible outside this sub-select, it's not
possible to use them in a {{GROUP BY}}. Before the the fix a {{GROUP BY B.ID}}
was translated to {{GROUP BY C.ID0}}, which is not correct since the column
{{ID0}} does not exist.
The fix modifies the generated statement like this
{code:SQL}
SELECT * FROM (
SELECT * FROM A
LEFT OUTER JOIN ( SELECT ID AS ID0 FROM B ) D ON A.ID = D.ID0
WHERE ...
) AS C
{code}
Now {{C.ID0}} exists and can be used for grouping and other stuff.
> JDBC adapter generates invalid query 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
>Priority: Major
> Labels: pull-request-available
>
> 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"
>
[jira] [Commented] (CALCITE-6221) JDBC adapter generates invalid query when the same table is joined multiple times
[
https://issues.apache.org/jira/browse/CALCITE-6221?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17812296#comment-17812296
]
Ulrich Kramer commented on CALCITE-6221:
Now, I restricted the renaming of the fields in SQL to our special case. If
there is a {{Filter}} around a {{Join}} an additional sub-select is inserted.
Before the fix, a statement like the following one was generated:
{code:SQL}
SELECT * FROM (
SELECT * FROM A
LEFT OUTER JOIN B ON A.ID = B.ID
WHERE ...
) AS C
{code}
As {{A}} and {{B}} are no longer visible outside this sub-select, it's not
possible to use them in a {{GROUP BY}}. Before the the fix a {{GROUP BY B.ID}}
was translated to {{GROUP BY C.ID0}}, which is not correct since the column
{{ID0}} does not exist.
The fix modifies the generated statement like this
{code:SQL}
SELECT * FROM (
SELECT * FROM A
LEFT OUTER JOIN ( SELECT ID AS ID0 FROM B ) D ON A.ID = D.ID0
WHERE ...
) AS C
{code}
Now {{C.ID0}} exists and can be used for grouping and other stuff.
> JDBC adapter generates invalid query 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
>Priority: Major
> Labels: pull-request-available
>
> 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] [Comment Edited] (CALCITE-6221) JDBC adapter generates invalid query when the same table is joined multiple times
[
https://issues.apache.org/jira/browse/CALCITE-6221?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17812256#comment-17812256
]
Ulrich Kramer edited comment on CALCITE-6221 at 1/30/24 12:29 PM:
--
I will try to keep the changes as small as possible.
The issue in general is caused by the fact, that the columns are renamed inside
the {{Join}} class inside {{SqlValidatorUtil.deriveJoinRowType}} and this
change is not reflected in the generated SQL. This works in most cases except
when a filter is pushed down into a join. In this case an additional SELECT
wrapper is generated which causes a re-aliasing (in the issue above {{t2}})
Therefore, I tried to reflect the renaming of the fields also in the generated
SQL statement.
But it seems that this change will also cause other issues (at least in our
application)
was (Author: kramerul):
I will try to keep the changes as small as possible.
The issue in general is caused by the fact, that the columns are renamed inside
the {{Join}} class inside {{SqlValidatorUtil.deriveJoinRowType}} and this
change is not reflected in the generated SQL. This works in most cases except
when a filter is pushed down into a join. In this case an additional SELECT
wrapper is generated which causes a re-aliasing (in the issue above {{t2}})
Therefore, I tried to reflect the rename of the fields also in the generated
SQL statement.
But it seems that this change will also cause other issues (at least in our
application)
> JDBC adapter generates invalid query 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
>Priority: Major
> Labels: pull-request-available
>
> 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-6233) Support parse of ISOWEEK on BigQuery library
Rodrigo Rueda created CALCITE-6233:
--
Summary: Support parse of ISOWEEK on BigQuery library
Key: CALCITE-6233
URL: https://issues.apache.org/jira/browse/CALCITE-6233
Project: Calcite
Issue Type: Bug
Components: core
Affects Versions: 1.36.0, 1.35.0, 1.34.0
Reporter: Rodrigo Rueda
Add support to:
{code:java}
PARSE_DATE('%G-%V-%a', '2022-52-Sun'){code}
that results in
{code:java}
2023-01-01{code}
Currently, the FormatElementEnum.IW doesn't handle the parsing, because to do
so it needs to set the calendar in the DateFormat class to a ISO8601 calendar,
but the current design only allows it to specify the pattern.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6231) JDBC adapter generates "UNNEST" when it should generate "UNNEST ... WITH ORDINALITY"
[
https://issues.apache.org/jira/browse/CALCITE-6231?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17812268#comment-17812268
]
EveyWu commented on CALCITE-6231:
--
[~julianhyde] thanks for the modification，I have changed the pr commit message
to the latest summary
> JDBC adapter generates "UNNEST" when it should generate "UNNEST ... WITH
> ORDINALITY"
>
>
> Key: CALCITE-6231
> URL: https://issues.apache.org/jira/browse/CALCITE-6231
> Project: Calcite
> Issue Type: Bug
>Reporter: EveyWu
>Assignee: EveyWu
>Priority: Major
> Labels: pull-request-available
>
> The syntax UNNEST() WITH ORDINALITY is missing the ORDINALITY keyword.
> For example sql：
> {code:java}
> select did + 1 from
> unnest(select collect("department_id") as deptid from "department")
> with ordinality as t(did, pos){code}
>
> current planned sql：
> {code:java}
> SELECT DEPTID + 1 FROM UNNEST (
> SELECT COLLECT("department_id") AS "DEPTID" FROM "foodmart"."department")
> AS "t0" ("DEPTID", "ORDINALITY") {code}
>
> fixed planned sql：
> {code:java}
> SELECT "DEPTID" + 1 FROM UNNEST (
> SELECT COLLECT("department_id") AS "DEPTID" FROM "foodmart"."department")
> WITH ORDINALITY AS "t0" ("DEPTID", "ORDINALITY") {code}
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6221) JDBC adapter generates invalid query when the same table is joined multiple times
[
https://issues.apache.org/jira/browse/CALCITE-6221?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17812256#comment-17812256
]
Ulrich Kramer commented on CALCITE-6221:
I will try to keep the changes as small as possible.
The issue in general is caused by the fact, that the columns are renamed inside
the {{Join}} class inside {{SqlValidatorUtil.deriveJoinRowType}} and this
change is not reflected in the generated SQL. This works in most cases except
when a filter is pushed down into a join. In this case an additional SELECT
wrapper is generated which causes a re-aliasing (in the issue above {{t2}})
Therefore, I tried to reflect the rename of the field also in the generated SQL
statement.
But it seems that this change will also cause other issues (at least in our
application)
> JDBC adapter generates invalid query 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
>Priority: Major
> Labels: pull-request-available
>
> 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] [Comment Edited] (CALCITE-6221) JDBC adapter generates invalid query when the same table is joined multiple times
[
https://issues.apache.org/jira/browse/CALCITE-6221?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17812256#comment-17812256
]
Ulrich Kramer edited comment on CALCITE-6221 at 1/30/24 10:35 AM:
--
I will try to keep the changes as small as possible.
The issue in general is caused by the fact, that the columns are renamed inside
the {{Join}} class inside {{SqlValidatorUtil.deriveJoinRowType}} and this
change is not reflected in the generated SQL. This works in most cases except
when a filter is pushed down into a join. In this case an additional SELECT
wrapper is generated which causes a re-aliasing (in the issue above {{t2}})
Therefore, I tried to reflect the rename of the fields also in the generated
SQL statement.
But it seems that this change will also cause other issues (at least in our
application)
was (Author: kramerul):
I will try to keep the changes as small as possible.
The issue in general is caused by the fact, that the columns are renamed inside
the {{Join}} class inside {{SqlValidatorUtil.deriveJoinRowType}} and this
change is not reflected in the generated SQL. This works in most cases except
when a filter is pushed down into a join. In this case an additional SELECT
wrapper is generated which causes a re-aliasing (in the issue above {{t2}})
Therefore, I tried to reflect the rename of the field also in the generated SQL
statement.
But it seems that this change will also cause other issues (at least in our
application)
> JDBC adapter generates invalid query 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
>Priority: Major
> Labels: pull-request-available
>
> 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)
