[jira] [Updated] (CALCITE-6091) Char that in array or map is truncated if CASE WHEN statement contains null

2023-11-03 Thread Jiajun Xie (Jira) 


 [ 
https://issues.apache.org/jira/browse/CALCITE-6091?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

Jiajun Xie updated CALCITE-6091:

Summary: Char that in array or map is truncated if CASE WHEN statement 
contains null  (was: Char that in array or map is truncated in CASE WHEN 
statement)

> Char that in array or map is truncated if CASE WHEN statement contains null
> ---
>
> Key: CALCITE-6091
> URL: https://issues.apache.org/jira/browse/CALCITE-6091
> Project: Calcite
>  Issue Type: Improvement
>  Components: core
>Reporter: Jiajun Xie
>Assignee: Jiajun Xie
>Priority: Major
>  Labels: pull-request-available
>
> {code:java}
> select case when true then array('abc')
> when false then array('d') 
> else null -- Perhaps it will be omitted
> end as c {code}
> The sql result type is {*}CHAR(1) ARRAY{*}.
> If we use the {*}CHAR(1) ARRAY{*}, *array('abc')* will be truncated.
> > Map has same issue.
>  



--
This message was sent by Atlassian Jira
(v8.20.10#820010)

[jira] [Updated] (CALCITE-6091) Char that in array or map is truncated in CASE WHEN statement

2023-11-03 Thread Jiajun Xie (Jira) 


 [ 
https://issues.apache.org/jira/browse/CALCITE-6091?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

Jiajun Xie updated CALCITE-6091:

Description: 
{code:java}
select case when true then array('abc')
when false then array('d') 
else null -- Perhaps it will be omitted
end as c {code}
The sql result type is {*}CHAR(1) ARRAY{*}.

If we use the {*}CHAR(1) ARRAY{*}, *array('abc')* will be truncated.

> Map has same issue.

 

  was:
{code:java}
select case when true then array('abc')
when false then array('d') end as c {code}
The sql result type is {*}CHAR(1) ARRAY{*}.

If we use the {*}CHAR(1) ARRAY{*}, *array('abc')* will be truncated.

> Map has same issue.

 


> Char that in array or map is truncated in CASE WHEN statement
> -
>
> Key: CALCITE-6091
> URL: https://issues.apache.org/jira/browse/CALCITE-6091
> Project: Calcite
>  Issue Type: Improvement
>  Components: core
>Reporter: Jiajun Xie
>Assignee: Jiajun Xie
>Priority: Major
>  Labels: pull-request-available
>
> {code:java}
> select case when true then array('abc')
> when false then array('d') 
> else null -- Perhaps it will be omitted
> end as c {code}
> The sql result type is {*}CHAR(1) ARRAY{*}.
> If we use the {*}CHAR(1) ARRAY{*}, *array('abc')* will be truncated.
> > Map has same issue.
>  



--
This message was sent by Atlassian Jira
(v8.20.10#820010)

[jira] [Commented] (CALCITE-6091) Char that in array or map is truncated in CASE WHEN statement

2023-11-03 Thread Jiajun Xie (Jira) 


[ 
https://issues.apache.org/jira/browse/CALCITE-6091?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17782824#comment-17782824
 ] 

Jiajun Xie commented on CALCITE-6091:
-

In fact, the issue has been resolved by 
https://issues.apache.org/jira/browse/CALCITE-4603.

But it doesn't handle null situations.

> Char that in array or map is truncated in CASE WHEN statement
> -
>
> Key: CALCITE-6091
> URL: https://issues.apache.org/jira/browse/CALCITE-6091
> Project: Calcite
>  Issue Type: Improvement
>  Components: core
>Reporter: Jiajun Xie
>Assignee: Jiajun Xie
>Priority: Major
>  Labels: pull-request-available
>
> {code:java}
> select case when true then array('abc')
> when false then array('d') end as c {code}
> The sql result type is {*}CHAR(1) ARRAY{*}.
> If we use the {*}CHAR(1) ARRAY{*}, *array('abc')* will be truncated.
> > Map has same issue.
>  



--
This message was sent by Atlassian Jira
(v8.20.10#820010)

[jira] [Updated] (CALCITE-6092) Invalid test cases in CAST String to Time

2023-11-03 Thread Jerin John (Jira) 


 [ 
https://issues.apache.org/jira/browse/CALCITE-6092?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

Jerin John updated CALCITE-6092:

Description: 
Encountered some 
[tests|https://github.com/apache/calcite/blob/590ec85f0fcff7173c288c350c3f60e640976a34/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java#L1237-L1238]
 within SqlOperatorTest file for the CAST operator on String to Datetime 
conversions, which are found to be invalid time strings on some tested 
instances of DBs like BQ, MySql, Postgres.

It seems these tests were originally ignored as `BAD_DATETIME_MESSAGE` but then 
updated to verify conversion performed by the CAST operator (refer to JIRA 
ticket: CALCITE-5554 and 
[commit|https://github.com/apache/calcite/commit/625a2e03c4c5583279350bf04e3db2a31b1ec411#diff-fdd0c725fc7c6fd56965f59b1d51e4c7a9b5b5be27da2e54b8b8273dc980cd64R1118-R1119])

Example test case (L1223):


Considering that multiple dialects catch these cases as invalid time strings 
for conversion, code should be updated to handle them as exceptions and the 
tests to be corrected to reflect this behavior.

  was:
Encountered some 
[tests|https://github.com/apache/calcite/blob/590ec85f0fcff7173c288c350c3f60e640976a34/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java#L1237-L1238]
 within SqlOperatorTest file for the CAST operator on String to Datetime 
conversions, which are found to be invalid time strings on some tested 
instances of DBs like BQ, MySql, Postgres.

It seems these tests were originally ignored as `BAD_DATETIME_MESSAGE` but then 
updated to verify conversion performed by the CAST operator (refer to JIRA 
ticket: CALCITE-5554 and 
[commit|https://github.com/apache/calcite/commit/625a2e03c4c5583279350bf04e3db2a31b1ec411#diff-fdd0c725fc7c6fd56965f59b1d51e4c7a9b5b5be27da2e54b8b8273dc980cd64R1118-R1119])

Considering that multiple dialects catch these cases as invalid time strings 
for conversion, code should be updated to handle them as exceptions and the 
tests to be corrected to reflect this behavior.


> Invalid test cases in CAST String to Time
> -
>
> Key: CALCITE-6092
> URL: https://issues.apache.org/jira/browse/CALCITE-6092
> Project: Calcite
>  Issue Type: Bug
>Reporter: Jerin John
>Priority: Major
>
> Encountered some 
> [tests|https://github.com/apache/calcite/blob/590ec85f0fcff7173c288c350c3f60e640976a34/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java#L1237-L1238]
>  within SqlOperatorTest file for the CAST operator on String to Datetime 
> conversions, which are found to be invalid time strings on some tested 
> instances of DBs like BQ, MySql, Postgres.
> It seems these tests were originally ignored as `BAD_DATETIME_MESSAGE` but 
> then updated to verify conversion performed by the CAST operator (refer to 
> JIRA ticket: CALCITE-5554 and 
> [commit|https://github.com/apache/calcite/commit/625a2e03c4c5583279350bf04e3db2a31b1ec411#diff-fdd0c725fc7c6fd56965f59b1d51e4c7a9b5b5be27da2e54b8b8273dc980cd64R1118-R1119])
> Example test case (L1223):
> Considering that multiple dialects catch these cases as invalid time strings 
> for conversion, code should be updated to handle them as exceptions and the 
> tests to be corrected to reflect this behavior.



--
This message was sent by Atlassian Jira
(v8.20.10#820010)

[jira] [Updated] (CALCITE-6092) Invalid test cases in CAST String to Time

2023-11-03 Thread Jerin John (Jira) 


 [ 
https://issues.apache.org/jira/browse/CALCITE-6092?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

Jerin John updated CALCITE-6092:

Description: 
Encountered some 
[tests|https://github.com/apache/calcite/blob/590ec85f0fcff7173c288c350c3f60e640976a34/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java#L1237-L1238]
 within SqlOperatorTest file for the CAST operator on String to Datetime 
conversions, which are found to be invalid time strings on some tested 
instances of DBs like BQ, MySql, Postgres.

It seems these tests were originally ignored as `BAD_DATETIME_MESSAGE` but then 
updated to verify conversion performed by the CAST operator (refer to JIRA 
ticket: CALCITE-5554 and 
[commit|https://github.com/apache/calcite/commit/625a2e03c4c5583279350bf04e3db2a31b1ec411#diff-fdd0c725fc7c6fd56965f59b1d51e4c7a9b5b5be27da2e54b8b8273dc980cd64R1118-R1119])

Considering that multiple dialects catch these cases as invalid time strings 
for conversion, code should be updated to handle them as exceptions and the 
tests to be corrected to reflect this behavior.

  was:
Encountered some 
[tests|https://github.com/apache/calcite/blob/590ec85f0fcff7173c288c350c3f60e640976a34/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java#L1237-L1238]
 within SqlOperatorTest file for the CAST operator on String to Datetime 
conversions, which are found to be invalid time strings on some tested 
instances of DBs like BQ, MySql, Postgres.

It seems these tests were originally ignored as `BAD_DATETIME_MESSAGE` but then 
updated to verify conversion performed by the CAST operator (refer to JIRA 
ticket: CALCITE-5554 and 
[commit)|https://github.com/apache/calcite/commit/625a2e03c4c5583279350bf04e3db2a31b1ec411#diff-fdd0c725fc7c6fd56965f59b1d51e4c7a9b5b5be27da2e54b8b8273dc980cd64R1118-R1119]

Considering that multiple dialects catch these cases as invalid time strings 
for conversion, code should be updated to handle them as exceptions and the 
tests to be corrected to reflect this behavior.


> Invalid test cases in CAST String to Time
> -
>
> Key: CALCITE-6092
> URL: https://issues.apache.org/jira/browse/CALCITE-6092
> Project: Calcite
>  Issue Type: Bug
>Reporter: Jerin John
>Priority: Major
>
> Encountered some 
> [tests|https://github.com/apache/calcite/blob/590ec85f0fcff7173c288c350c3f60e640976a34/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java#L1237-L1238]
>  within SqlOperatorTest file for the CAST operator on String to Datetime 
> conversions, which are found to be invalid time strings on some tested 
> instances of DBs like BQ, MySql, Postgres.
> It seems these tests were originally ignored as `BAD_DATETIME_MESSAGE` but 
> then updated to verify conversion performed by the CAST operator (refer to 
> JIRA ticket: CALCITE-5554 and 
> [commit|https://github.com/apache/calcite/commit/625a2e03c4c5583279350bf04e3db2a31b1ec411#diff-fdd0c725fc7c6fd56965f59b1d51e4c7a9b5b5be27da2e54b8b8273dc980cd64R1118-R1119])
> Considering that multiple dialects catch these cases as invalid time strings 
> for conversion, code should be updated to handle them as exceptions and the 
> tests to be corrected to reflect this behavior.



--
This message was sent by Atlassian Jira
(v8.20.10#820010)

[jira] [Created] (CALCITE-6092) Invalid test cases in CAST String to Time

2023-11-03 Thread Jerin John (Jira) 
Jerin John created CALCITE-6092:
---

 Summary: Invalid test cases in CAST String to Time
 Key: CALCITE-6092
 URL: https://issues.apache.org/jira/browse/CALCITE-6092
 Project: Calcite
  Issue Type: Bug
Reporter: Jerin John


Encountered some 
[tests|https://github.com/apache/calcite/blob/590ec85f0fcff7173c288c350c3f60e640976a34/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java#L1237-L1238]
 within SqlOperatorTest file for the CAST operator on String to Datetime 
conversions, which are found to be invalid time strings on some tested 
instances of DBs like BQ, MySql, Postgres.

It seems these tests were originally ignored as `BAD_DATETIME_MESSAGE` but then 
updated to verify conversion performed by the CAST operator (refer to JIRA 
ticket: CALCITE-5554 and 
[commit)|https://github.com/apache/calcite/commit/625a2e03c4c5583279350bf04e3db2a31b1ec411#diff-fdd0c725fc7c6fd56965f59b1d51e4c7a9b5b5be27da2e54b8b8273dc980cd64R1118-R1119]

Considering that multiple dialects catch these cases as invalid time strings 
for conversion, code should be updated to handle them as exceptions and the 
tests to be corrected to reflect this behavior.



--
This message was sent by Atlassian Jira
(v8.20.10#820010)

[jira] [Updated] (CALCITE-6091) Char that in array or map is truncated in CASE WHEN statement

2023-11-03 Thread Jiajun Xie (Jira) 


 [ 
https://issues.apache.org/jira/browse/CALCITE-6091?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

Jiajun Xie updated CALCITE-6091:

Description: 
{code:java}
select case when true then array('abc')
when false then array('d') end as c {code}
The sql result type is {*}CHAR(1) ARRAY{*}.

If we use the {*}CHAR(1) ARRAY{*}, *array('abc')* will be truncated.

> Map has same issue.

 

  was:
{code:java}
select case when true then array('abc')
when false then array('d') end as c {code}
The sql result type is {*}CHAR(1) ARRAY{*}.

If we use the {*}CHAR(1) ARRAY{*}, *array('abc')* will be truncated.

Summary: Char that in array or map is truncated in CASE WHEN statement  
(was: Char that in array is truncated in CASE WHEN statement)

> Char that in array or map is truncated in CASE WHEN statement
> -
>
> Key: CALCITE-6091
> URL: https://issues.apache.org/jira/browse/CALCITE-6091
> Project: Calcite
>  Issue Type: Improvement
>  Components: core
>Reporter: Jiajun Xie
>Assignee: Jiajun Xie
>Priority: Major
>  Labels: pull-request-available
>
> {code:java}
> select case when true then array('abc')
> when false then array('d') end as c {code}
> The sql result type is {*}CHAR(1) ARRAY{*}.
> If we use the {*}CHAR(1) ARRAY{*}, *array('abc')* will be truncated.
> > Map has same issue.
>  



--
This message was sent by Atlassian Jira
(v8.20.10#820010)

[jira] [Updated] (CALCITE-6091) Char that in array is truncated in CASE WHEN statement

2023-11-03 Thread ASF GitHub Bot (Jira) 


 [ 
https://issues.apache.org/jira/browse/CALCITE-6091?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

ASF GitHub Bot updated CALCITE-6091:

Labels: pull-request-available  (was: )

> Char that in array is truncated in CASE WHEN statement
> --
>
> Key: CALCITE-6091
> URL: https://issues.apache.org/jira/browse/CALCITE-6091
> Project: Calcite
>  Issue Type: Improvement
>  Components: core
>Reporter: Jiajun Xie
>Assignee: Jiajun Xie
>Priority: Major
>  Labels: pull-request-available
>
> {code:java}
> select case when true then array('abc')
> when false then array('d') end as c {code}
> The sql result type is {*}CHAR(1) ARRAY{*}.
> If we use the {*}CHAR(1) ARRAY{*}, *array('abc')* will be truncated.



--
This message was sent by Atlassian Jira
(v8.20.10#820010)

[jira] [Updated] (CALCITE-6091) Char that in array is truncated in CASE WHEN statement

2023-11-03 Thread Jiajun Xie (Jira) 


 [ 
https://issues.apache.org/jira/browse/CALCITE-6091?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

Jiajun Xie updated CALCITE-6091:

Description: 
{code:java}
select case when true then array('abc')
when false then array('d') end as c {code}
The sql result type is {*}CHAR(1) ARRAY{*}.

If we use the {*}CHAR(1) ARRAY{*}, *array('abc')* will be truncated.

  was:
{code:java}
select case when true then array('abc')
when false then array('d') end as c {code}
The sql result type is `CHAR(1) ARRAY`.

If we use the CHAR(1), `array('abc')` will be truncated.


> Char that in array is truncated in CASE WHEN statement
> --
>
> Key: CALCITE-6091
> URL: https://issues.apache.org/jira/browse/CALCITE-6091
> Project: Calcite
>  Issue Type: Improvement
>  Components: core
>Reporter: Jiajun Xie
>Assignee: Jiajun Xie
>Priority: Major
>
> {code:java}
> select case when true then array('abc')
> when false then array('d') end as c {code}
> The sql result type is {*}CHAR(1) ARRAY{*}.
> If we use the {*}CHAR(1) ARRAY{*}, *array('abc')* will be truncated.



--
This message was sent by Atlassian Jira
(v8.20.10#820010)

[jira] [Updated] (CALCITE-6091) Char that in array is truncated in CASE WHEN statement

2023-11-03 Thread Jiajun Xie (Jira) 


 [ 
https://issues.apache.org/jira/browse/CALCITE-6091?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

Jiajun Xie updated CALCITE-6091:

Affects Version/s: (was: 1.34.0)
   (was: 1.35.0)
  Description: 
{code:java}
select case when true then array('abc')
when false then array('d') end as c {code}
The sql result type is `CHAR(1) ARRAY`.

If we use the CHAR(1), `array('abc')` will be truncated.

> Char that in array is truncated in CASE WHEN statement
> --
>
> Key: CALCITE-6091
> URL: https://issues.apache.org/jira/browse/CALCITE-6091
> Project: Calcite
>  Issue Type: Improvement
>  Components: core
>Reporter: Jiajun Xie
>Assignee: Jiajun Xie
>Priority: Major
>
> {code:java}
> select case when true then array('abc')
> when false then array('d') end as c {code}
> The sql result type is `CHAR(1) ARRAY`.
> If we use the CHAR(1), `array('abc')` will be truncated.



--
This message was sent by Atlassian Jira
(v8.20.10#820010)

[jira] [Created] (CALCITE-6091) Char that in array is truncated in CASE WHEN statement

2023-11-03 Thread Jiajun Xie (Jira) 
Jiajun Xie created CALCITE-6091:
---

 Summary: Char that in array is truncated in CASE WHEN statement
 Key: CALCITE-6091
 URL: https://issues.apache.org/jira/browse/CALCITE-6091
 Project: Calcite
  Issue Type: Improvement
  Components: core
Affects Versions: 1.35.0, 1.34.0
Reporter: Jiajun Xie
Assignee: Jiajun Xie






--
This message was sent by Atlassian Jira
(v8.20.10#820010)

[jira] [Updated] (CALCITE-6090) Jdbc adapter may create wrong sql for joins

2023-11-03 Thread Ulrich Kramer (Jira) 


 [ 
https://issues.apache.org/jira/browse/CALCITE-6090?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

Ulrich Kramer updated CALCITE-6090:
---
Description: 
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 located 
[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 and 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.

  was:
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

[jira] [Updated] (CALCITE-6090) Jdbc adapter may create wrong sql for joins

2023-11-03 Thread Ulrich Kramer (Jira) 


 [ 
https://issues.apache.org/jira/browse/CALCITE-6090?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

Ulrich Kramer updated CALCITE-6090:
---
Description: 
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 and 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.


  was:
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

[jira] [Created] (CALCITE-6090) Jdbc adapter may create wrong sql for joins

2023-11-03 Thread Ulrich Kramer (Jira) 
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] [Commented] (CALCITE-6089) EnumerableSortedAggregate fails with ClassCastException: class X cannot be cast to class org.apache.calcite.runtime.FlatLists$ComparableList

2023-11-03 Thread Ruben Q L (Jira) 


[ 
https://issues.apache.org/jira/browse/CALCITE-6089?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17782466#comment-17782466
 ] 

Ruben Q L commented on CALCITE-6089:


We can see similar instances of the same exception if we force Calcite tests to 
run with EnumerableSortedAggregate instead of EnumerableAggregate, e.g. 
changing {{EnumerableRules#ENUMERABLE_RULES}}:
{code}
...
// EnumerableRules.ENUMERABLE_AGGREGATE_RULE,
EnumerableRules.ENUMERABLE_SORTED_AGGREGATE_RULE,
...
{code}

And executing {{CalciteSqlOperatorTest}}.

> EnumerableSortedAggregate fails with ClassCastException: class X cannot be 
> cast to class org.apache.calcite.runtime.FlatLists$ComparableList
> 
>
> Key: CALCITE-6089
> URL: https://issues.apache.org/jira/browse/CALCITE-6089
> Project: Calcite
>  Issue Type: Bug
>  Components: core
>Affects Versions: 1.35.0
>Reporter: Ruben Q L
>Priority: Major
>
> The problem can be reproduced with this test (to be added e.g. into 
> {{EnumerableSortedAggregateTest.java}}):
> {code}
>   @Test void sortedAggCountUnion() {
> tester(false, new HrSchema())
> .query(
> "select count(*) as c from ( "
> + "select * from emps where deptno=10 "
> + "union all "
> + "select * from emps where deptno=20)")
> .withHook(Hook.PLANNER, (Consumer) planner -> {
>   planner.removeRule(EnumerableRules.ENUMERABLE_AGGREGATE_RULE);
>   planner.addRule(EnumerableRules.ENUMERABLE_SORTED_AGGREGATE_RULE);
> })
> .explainContains(
> "EnumerableSortedAggregate(group=[{}], c=[COUNT()])\n"
> + "  EnumerableUnion(all=[true])\n"
> + "EnumerableCalc(expr#0..4=[{inputs}], 
> expr#5=[CAST($t1):INTEGER NOT NULL], expr#6=[10], expr#7=[=($t5, $t6)], 
> commission=[$t4], $condition=[$t7])\n"
> + "  EnumerableTableScan(table=[[s, emps]])\n"
> + "EnumerableCalc(expr#0..4=[{inputs}], 
> expr#5=[CAST($t1):INTEGER NOT NULL], expr#6=[20], expr#7=[=($t5, $t6)], 
> commission=[$t4], $condition=[$t7])\n"
> + "  EnumerableTableScan(table=[[s, emps]])")
> .returnsOrdered(
> "c=4");
>   }
> {code}
> Which fails with:
> {noformat}
> ...
> Caused by: java.lang.ClassCastException: class java.lang.Integer cannot be 
> cast to class org.apache.calcite.runtime.FlatLists$ComparableList 
> (java.lang.Integer is in module java.base of loader 'bootstrap'; 
> org.apache.calcite.runtime.FlatLists$ComparableList is in unnamed module of 
> loader 'app')
>   at Baz$6.compare(Unknown Source)
>   at 
> org.apache.calcite.linq4j.EnumerableDefaults$SortedAggregateEnumerator.moveNext(EnumerableDefaults.java:938)
>   at 
> org.apache.calcite.linq4j.Linq4j$EnumeratorIterator.(Linq4j.java:679)
>   at org.apache.calcite.linq4j.Linq4j.enumeratorIterator(Linq4j.java:97)
> ...
> {noformat}
> The same test with EnumerableAggregate (instead of EnumerableSortedAggregate) 
> will execute the query correctly.



--
This message was sent by Atlassian Jira
(v8.20.10#820010)

[jira] [Created] (CALCITE-6089) EnumerableSortedAggregate fails with ClassCastException: class X cannot be cast to class org.apache.calcite.runtime.FlatLists$ComparableList

2023-11-03 Thread Ruben Q L (Jira) 
Ruben Q L created CALCITE-6089:
--

 Summary: EnumerableSortedAggregate fails with ClassCastException: 
class X cannot be cast to class 
org.apache.calcite.runtime.FlatLists$ComparableList
 Key: CALCITE-6089
 URL: https://issues.apache.org/jira/browse/CALCITE-6089
 Project: Calcite
  Issue Type: Bug
  Components: core
Affects Versions: 1.35.0
Reporter: Ruben Q L


The problem can be reproduced with this test (to be added e.g. into 
{{EnumerableSortedAggregateTest.java}}):

{code}
  @Test void sortedAggCountUnion() {
tester(false, new HrSchema())
.query(
"select count(*) as c from ( "
+ "select * from emps where deptno=10 "
+ "union all "
+ "select * from emps where deptno=20)")
.withHook(Hook.PLANNER, (Consumer) planner -> {
  planner.removeRule(EnumerableRules.ENUMERABLE_AGGREGATE_RULE);
  planner.addRule(EnumerableRules.ENUMERABLE_SORTED_AGGREGATE_RULE);
})
.explainContains(
"EnumerableSortedAggregate(group=[{}], c=[COUNT()])\n"
+ "  EnumerableUnion(all=[true])\n"
+ "EnumerableCalc(expr#0..4=[{inputs}], 
expr#5=[CAST($t1):INTEGER NOT NULL], expr#6=[10], expr#7=[=($t5, $t6)], 
commission=[$t4], $condition=[$t7])\n"
+ "  EnumerableTableScan(table=[[s, emps]])\n"
+ "EnumerableCalc(expr#0..4=[{inputs}], 
expr#5=[CAST($t1):INTEGER NOT NULL], expr#6=[20], expr#7=[=($t5, $t6)], 
commission=[$t4], $condition=[$t7])\n"
+ "  EnumerableTableScan(table=[[s, emps]])")
.returnsOrdered(
"c=4");
  }
{code}

Which fails with:
{noformat}
...
Caused by: java.lang.ClassCastException: class java.lang.Integer cannot be cast 
to class org.apache.calcite.runtime.FlatLists$ComparableList (java.lang.Integer 
is in module java.base of loader 'bootstrap'; 
org.apache.calcite.runtime.FlatLists$ComparableList is in unnamed module of 
loader 'app')
at Baz$6.compare(Unknown Source)
at 
org.apache.calcite.linq4j.EnumerableDefaults$SortedAggregateEnumerator.moveNext(EnumerableDefaults.java:938)
at 
org.apache.calcite.linq4j.Linq4j$EnumeratorIterator.(Linq4j.java:679)
at org.apache.calcite.linq4j.Linq4j.enumeratorIterator(Linq4j.java:97)
...
{noformat}

The same test with EnumerableAggregate (instead of EnumerableSortedAggregate) 
will execute the query correctly.



--
This message was sent by Atlassian Jira
(v8.20.10#820010)