[jira] [Created] (CALCITE-6223) Add MAP_CONTAINS_KEY function (enabled in Spark library)
Caican Cai created CALCITE-6223: --- Summary: Add MAP_CONTAINS_KEY function (enabled in Spark library) Key: CALCITE-6223 URL: https://issues.apache.org/jira/browse/CALCITE-6223 Project: Calcite Issue Type: New Feature Components: core Affects Versions: 1.36.0 Reporter: Caican Cai Fix For: 1.37.0 Add MAP_CONTAINS_KEY function (enabled in Spark library) [https://spark.apache.org/docs/3.4.0/api/sql/index.html#map_contains_key] {code:java} > SELECT map_contains_key(map(1, 'a', 2, 'b'), 1); true > SELECT map_contains_key(map(1, 'a', 2, 'b'), 3); false {code} -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Updated] (CALCITE-6223) Add MAP_CONTAINS_KEY function (enabled in Spark library)
[ https://issues.apache.org/jira/browse/CALCITE-6223?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] ASF GitHub Bot updated CALCITE-6223: Labels: pull-request-available (was: ) > Add MAP_CONTAINS_KEY function (enabled in Spark library) > > > Key: CALCITE-6223 > URL: https://issues.apache.org/jira/browse/CALCITE-6223 > Project: Calcite > Issue Type: New Feature > Components: core >Affects Versions: 1.36.0 >Reporter: Caican Cai >Priority: Minor > Labels: pull-request-available > Fix For: 1.37.0 > > > Add MAP_CONTAINS_KEY function (enabled in Spark library) > [https://spark.apache.org/docs/3.4.0/api/sql/index.html#map_contains_key] > > > {code:java} > > SELECT map_contains_key(map(1, 'a', 2, 'b'), 1); > true > > SELECT map_contains_key(map(1, 'a', 2, 'b'), 3); > false {code} > > -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Created] (CALCITE-6224) Add LOG2 function on SqlStdOperatorTable
Caican Cai created CALCITE-6224: --- Summary: Add LOG2 function on SqlStdOperatorTable Key: CALCITE-6224 URL: https://issues.apache.org/jira/browse/CALCITE-6224 Project: Calcite Issue Type: New Feature Components: core Affects Versions: 1.36.0 Reporter: Caican Cai Fix For: 1.37.0 The log2 function is supported in many databases, such as spark, mysql, postgres, etc. {code:java} > SELECT log2(2); 1.0 {code} links: [https://spark.apache.org/docs/3.4.0/api/sql/index.html#log2] https://dev.mysql.com/doc/refman/8.0/en/mathematical-functions.html#function_log2 -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Updated] (CALCITE-6224) Add LOG2 function on SqlStdOperatorTable
[ https://issues.apache.org/jira/browse/CALCITE-6224?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] ASF GitHub Bot updated CALCITE-6224: Labels: pull-request-available (was: ) > Add LOG2 function on SqlStdOperatorTable > > > Key: CALCITE-6224 > URL: https://issues.apache.org/jira/browse/CALCITE-6224 > Project: Calcite > Issue Type: New Feature > Components: core >Affects Versions: 1.36.0 >Reporter: Caican Cai >Priority: Major > Labels: pull-request-available > Fix For: 1.37.0 > > > The log2 function is supported in many databases, such as spark, mysql, > postgres, etc. > {code:java} > > SELECT log2(2); > 1.0 {code} > links: > [https://spark.apache.org/docs/3.4.0/api/sql/index.html#log2] > https://dev.mysql.com/doc/refman/8.0/en/mathematical-functions.html#function_log2 -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Created] (CALCITE-6225) Unparse LOGICAL_AND/OR as BOOLAND_AGG/BOOLOR_AGG for Snowflake
Tanner Clary created CALCITE-6225: - Summary: Unparse LOGICAL_AND/OR as BOOLAND_AGG/BOOLOR_AGG for Snowflake Key: CALCITE-6225 URL: https://issues.apache.org/jira/browse/CALCITE-6225 Project: Calcite Issue Type: Improvement Reporter: Tanner Clary Assignee: Tanner Clary Very similar to CALCITE-6205, LOGICAL_AND and LOGICAL_OR map to BOOLAND_AGG and BOOLOR_AGG for Snowflake, according to https://cloud.google.com/bigquery/docs/migration/snowflake-sql#functions -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Updated] (CALCITE-6225) Unparse LOGICAL_* as BOOLAND_AGG/BOOLOR_AGG for Snowflake
[ https://issues.apache.org/jira/browse/CALCITE-6225?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Tanner Clary updated CALCITE-6225: -- Summary: Unparse LOGICAL_* as BOOLAND_AGG/BOOLOR_AGG for Snowflake (was: Unparse LOGICAL_AND/OR as BOOLAND_AGG/BOOLOR_AGG for Snowflake) > Unparse LOGICAL_* as BOOLAND_AGG/BOOLOR_AGG for Snowflake > - > > Key: CALCITE-6225 > URL: https://issues.apache.org/jira/browse/CALCITE-6225 > Project: Calcite > Issue Type: Improvement >Reporter: Tanner Clary >Assignee: Tanner Clary >Priority: Major > > Very similar to CALCITE-6205, LOGICAL_AND and LOGICAL_OR map to BOOLAND_AGG > and BOOLOR_AGG for Snowflake, according to > https://cloud.google.com/bigquery/docs/migration/snowflake-sql#functions -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Updated] (CALCITE-6225) Unparse LOGICAL_AND/OR as BOOLAND_AGG/BOOLOR_AGG for Snowflake
[ https://issues.apache.org/jira/browse/CALCITE-6225?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Tanner Clary updated CALCITE-6225: -- Summary: Unparse LOGICAL_AND/OR as BOOLAND_AGG/BOOLOR_AGG for Snowflake (was: Unparse LOGICAL_* as BOOLAND_AGG/BOOLOR_AGG for Snowflake) > Unparse LOGICAL_AND/OR as BOOLAND_AGG/BOOLOR_AGG for Snowflake > -- > > Key: CALCITE-6225 > URL: https://issues.apache.org/jira/browse/CALCITE-6225 > Project: Calcite > Issue Type: Improvement >Reporter: Tanner Clary >Assignee: Tanner Clary >Priority: Major > > Very similar to CALCITE-6205, LOGICAL_AND and LOGICAL_OR map to BOOLAND_AGG > and BOOLOR_AGG for Snowflake, according to > https://cloud.google.com/bigquery/docs/migration/snowflake-sql#functions -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Updated] (CALCITE-6225) Unparse LOGICAL_AND/OR as MIN/MAX for Snowflake
[ https://issues.apache.org/jira/browse/CALCITE-6225?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Tanner Clary updated CALCITE-6225: -- Summary: Unparse LOGICAL_AND/OR as MIN/MAX for Snowflake (was: Unparse LOGICAL_AND/OR as BOOLAND_AGG/BOOLOR_AGG for Snowflake) > Unparse LOGICAL_AND/OR as MIN/MAX for Snowflake > --- > > Key: CALCITE-6225 > URL: https://issues.apache.org/jira/browse/CALCITE-6225 > Project: Calcite > Issue Type: Improvement >Reporter: Tanner Clary >Assignee: Tanner Clary >Priority: Major > > Very similar to CALCITE-6205, LOGICAL_AND and LOGICAL_OR map to BOOLAND_AGG > and BOOLOR_AGG for Snowflake, according to > https://cloud.google.com/bigquery/docs/migration/snowflake-sql#functions -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Updated] (CALCITE-6225) Unparse LOGICAL_AND/OR as MIN/MAX for Snowflake
[ https://issues.apache.org/jira/browse/CALCITE-6225?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Tanner Clary updated CALCITE-6225: -- Description: Because Snowflake does not support BigQuery's LOGICAL_AND/OR functions, they can be rewritten as MIN/MAX to achieve the same effect. For instance, LOGICAL_AND(users.age > 3) will return {{TRUE}} iff every age is > 3. This is identical to MIN(users.age > 3), because if even one age is <= 3, it will return {{FALSE}}. Similarly, for LOGICAL_OR(users.age > 3) will return {{TRUE}} iff there exists at least one age > 3. This is identical to MAX(users.age > 3) which will only return {{FALSE}} iff every age is <= 3. was:Very similar to CALCITE-6205, LOGICAL_AND and LOGICAL_OR map to BOOLAND_AGG and BOOLOR_AGG for Snowflake, according to https://cloud.google.com/bigquery/docs/migration/snowflake-sql#functions > Unparse LOGICAL_AND/OR as MIN/MAX for Snowflake > --- > > Key: CALCITE-6225 > URL: https://issues.apache.org/jira/browse/CALCITE-6225 > Project: Calcite > Issue Type: Improvement >Reporter: Tanner Clary >Assignee: Tanner Clary >Priority: Major > > Because Snowflake does not support BigQuery's LOGICAL_AND/OR functions, they > can be rewritten as MIN/MAX to achieve the same effect. > For instance, LOGICAL_AND(users.age > 3) will return {{TRUE}} iff every age > is > 3. This is identical to MIN(users.age > 3), because if even one age is > <= 3, it will return {{FALSE}}. > Similarly, for LOGICAL_OR(users.age > 3) will return {{TRUE}} iff there > exists at least one age > 3. This is identical to MAX(users.age > 3) which > will only return {{FALSE}} iff every age is <= 3. -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Updated] (CALCITE-6225) Unparse LOGICAL_AND/OR as MIN/MAX for Snowflake
[ https://issues.apache.org/jira/browse/CALCITE-6225?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] ASF GitHub Bot updated CALCITE-6225: Labels: pull-request-available (was: ) > Unparse LOGICAL_AND/OR as MIN/MAX for Snowflake > --- > > Key: CALCITE-6225 > URL: https://issues.apache.org/jira/browse/CALCITE-6225 > Project: Calcite > Issue Type: Improvement >Reporter: Tanner Clary >Assignee: Tanner Clary >Priority: Major > Labels: pull-request-available > > Because Snowflake does not support BigQuery's LOGICAL_AND/OR functions, they > can be rewritten as MIN/MAX to achieve the same effect. > For instance, LOGICAL_AND(users.age > 3) will return {{TRUE}} iff every age > is > 3. This is identical to MIN(users.age > 3), because if even one age is > <= 3, it will return {{FALSE}}. > Similarly, for LOGICAL_OR(users.age > 3) will return {{TRUE}} iff there > exists at least one age > 3. This is identical to MAX(users.age > 3) which > will only return {{FALSE}} iff every age is <= 3. -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Resolved] (CALCITE-6220) Rewrite MIN/MAX(BOOLEAN) as BOOL_AND/BOOL_OR for Postgres, Redshift
[ https://issues.apache.org/jira/browse/CALCITE-6220?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Tanner Clary resolved CALCITE-6220. --- Resolution: Fixed Merged via [26b5e9b|https://github.com/apache/calcite/commit/26b5e9be6af1be61f4665c75e913b0d1f3459dfd], thanks for the review, [~julianhyde] > Rewrite MIN/MAX(BOOLEAN) as BOOL_AND/BOOL_OR for Postgres, Redshift > --- > > Key: CALCITE-6220 > URL: https://issues.apache.org/jira/browse/CALCITE-6220 > Project: Calcite > Issue Type: Improvement >Reporter: Tanner Clary >Assignee: Tanner Clary >Priority: Major > Labels: pull-request-available > > Postgres and Redshift do not allow for MIN/MAX on columns of type BOOLEAN, > instead they use the BOOL_AND/BOOL_OR functions, which Calcite already > supports. > While CALCITE-6206 is in progress, I plan on just adding this as an override > during unparsing for now unless there are objections. After CALCITE-6206 is > resolved this could be added to the mapping I described there. -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Created] (CALCITE-6226) Wrong ISOWEEK and no ISOYEAR on BigQuery FORMAT_DATE
Rodrigo Rueda created CALCITE-6226: -- Summary: Wrong ISOWEEK and no ISOYEAR on BigQuery FORMAT_DATE Key: CALCITE-6226 URL: https://issues.apache.org/jira/browse/CALCITE-6226 Project: Calcite Issue Type: Bug Components: core Affects Versions: 1.36.0, 1.35.0, 1.34.0 Reporter: Rodrigo Rueda Fix For: 1.37.0 Doing: {code:java} FORMAT_DATE('%G-%V', DATE '2023-01-01'){code} results in: {code:java} '%G-01'{code} but should be: {code:java} 2022-52{code} The week is wrong because the ISOWEEK format function is not setting the minimalDaysInFirstWeek to 4 and the ISOYEAR (%g or %G) was not implemented. -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Commented] (CALCITE-6226) Wrong ISOWEEK and no ISOYEAR on BigQuery FORMAT_DATE
[ https://issues.apache.org/jira/browse/CALCITE-6226?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17811031#comment-17811031 ] Tanner Clary commented on CALCITE-6226: --- Feel free to tag me on any PRs, would be happy to help review. > Wrong ISOWEEK and no ISOYEAR on BigQuery FORMAT_DATE > > > Key: CALCITE-6226 > URL: https://issues.apache.org/jira/browse/CALCITE-6226 > Project: Calcite > Issue Type: Bug > Components: core >Affects Versions: 1.34.0, 1.35.0, 1.36.0 >Reporter: Rodrigo Rueda >Priority: Major > Fix For: 1.37.0 > > > > Doing: > {code:java} > FORMAT_DATE('%G-%V', DATE '2023-01-01'){code} > results in: > {code:java} > '%G-01'{code} > but should be: > {code:java} > 2022-52{code} > > The week is wrong because the ISOWEEK format function is not setting the > minimalDaysInFirstWeek to 4 and the ISOYEAR (%g or %G) was not implemented. -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Created] (CALCITE-6227) ELEMENT(NULL) causes an assertion failure
Mihai Budiu created CALCITE-6227: Summary: ELEMENT(NULL) causes an assertion failure Key: CALCITE-6227 URL: https://issues.apache.org/jira/browse/CALCITE-6227 Project: Calcite Issue Type: Bug Components: core Affects Versions: 1.36.0 Reporter: Mihai Budiu Adding the following test to SqlValidatorTest: {code:java} @Test void testElement() { expr("element(null)").columnType("null"); } {code} causes an assertion failure. The bottom of the stack trace is the following: {code} at org.apache.calcite.sql.type.ReturnTypes$2.get(ReturnTypes.java:577) at org.apache.calcite.sql.type.ReturnTypes$2.get(ReturnTypes.java:571) at org.apache.calcite.sql.ExplicitOperatorBinding.getOperandType(ExplicitOperatorBinding.java:78) at org.apache.calcite.sql.SqlOperatorBinding$1.get(SqlOperatorBinding.java:255) at org.apache.calcite.sql.SqlOperatorBinding$1.get(SqlOperatorBinding.java:253) at org.apache.calcite.sql.type.SqlTypeFactoryImpl.leastRestrictive(SqlTypeFactoryImpl.java:174) at org.apache.calcite.rel.type.RelDataTypeFactory.leastRestrictive(RelDataTypeFactory.java:222) at org.apache.calcite.sql.type.ReturnTypes.lambda$static$3(ReturnTypes.java:537) at org.apache.calcite.sql.type.ReturnTypes.lambda$static$6(ReturnTypes.java:586) at org.apache.calcite.sql.type.SqlTypeTransformCascade.inferReturnType(SqlTypeTransformCascade.java:58) at org.apache.calcite.sql.SqlOperator.inferReturnType(SqlOperator.java:534) {code} -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Commented] (CALCITE-6219) Support SQL Validation for Tables with columns tagged as 'filter required'
[ https://issues.apache.org/jira/browse/CALCITE-6219?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17811049#comment-17811049 ] Oliver Lee commented on CALCITE-6219: - Can you comment on the last bullet point ? {{" If any 'filter required' columns are not filtered and not in the SELECT clause, it is an error.}} " Are you saying if a 'filter required' column is not filtered but it _is_ in the SELECT clause, it wouldn't error? > Support SQL Validation for Tables with columns tagged as 'filter required' > -- > > Key: CALCITE-6219 > URL: https://issues.apache.org/jira/browse/CALCITE-6219 > Project: Calcite > Issue Type: New Feature >Reporter: Oliver Lee >Assignee: Oliver Lee >Priority: Major > > Suppose that a user's Table definitions come in with a tag on certain columns > that indicates the the column is required to have a filter on it for all > incoming queries. > > I would like to add support to validate that incoming queries satisfies the > table condition. > If all of the table's specified fields has a filter on it (present in a WHERE > or HAVING clause for the query), then it will not error. > > ex. > {{EMP}} table specifies that {{EMPNO}} requires a filter > > {{select * from emp where empno = 1}} -> No error > {{select * from emp where ename = 'bob' -> Error}} > {{select * from emp -> Error}} > > The validation would occur after the namespace validation in > {{SqlValidatorImpl}} as a separate pass. > > I am envisioning that the full filter validation algorithm will form a couple > of key steps > * Scanning the catalog/schema/tables and determining which fields are > tagged to always require filters > * A pass through the SQL statement to see if a certain field needs to be > filtered multiple times (potentially for CTE, joins? needs further > investigation) > * A pass through the SQL statement to discover filters on the statement > > In determining whether a {{WHERE}} or {{HAVING}} clause contains a certain > field identifier, there will need to be a helper visitor for WHERE or HAVING > SqlNodes to collect all of the SqlIdentifiers that could be nested within the > {{operandList}} > > Special considerations: > * joins > * CTEs > * subqueries -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Created] (CALCITE-6228) ELEMENT function infers incorrect return type
Mihai Budiu created CALCITE-6228: Summary: ELEMENT function infers incorrect return type Key: CALCITE-6228 URL: https://issues.apache.org/jira/browse/CALCITE-6228 Project: Calcite Issue Type: Bug Components: core Affects Versions: 1.36.0 Reporter: Mihai Budiu The ELEMENT function is defined in the documentation as follows: [https://calcite.apache.org/docs/reference.html#collection-functions] {quote}Returns the sole element of an array or multiset; null if the collection is empty; throws if it has more than one element. {quote} However, the type inference returns just the type of the element of the collection, without changing its nullability. The type inference is implemented as follows in SqlStdOperatorTable: {code:java} public static final SqlFunction ELEMENT = SqlBasicFunction.create("ELEMENT", ReturnTypes.MULTISET_ELEMENT_NULLABLE, OperandTypes.COLLECTION); {code} However, reading the definition of MULTISET_ELEMENT_NULLABLE in ReturnTypes.java: {code:java} public static final SqlReturnTypeInference MULTISET_ELEMENT_NULLABLE = MULTISET.andThen(SqlTypeTransforms.TO_COLLECTION_ELEMENT_TYPE); {code} we notice that it is not forced to be nullable. Probably the correct implementation would be {code:java} public static final SqlReturnTypeInference MULTISET_ELEMENT_NULLABLE = MULTISET.andThen(SqlTypeTransforms.TO_COLLECTION_ELEMENT_TYPE) .andThen(SqlTypeTransforms.FORCE_NULLABLE); {code} -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Commented] (CALCITE-6224) Add LOG2 function on SqlStdOperatorTable
[ https://issues.apache.org/jira/browse/CALCITE-6224?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17811050#comment-17811050 ] Julian Hyde commented on CALCITE-6224: -- Should be in SqlLibraryOperators, not SqlStdOperatorTable. Can you change the Jira subject to be consistent with similar functions. > Add LOG2 function on SqlStdOperatorTable > > > Key: CALCITE-6224 > URL: https://issues.apache.org/jira/browse/CALCITE-6224 > Project: Calcite > Issue Type: New Feature > Components: core >Affects Versions: 1.36.0 >Reporter: Caican Cai >Priority: Major > Labels: pull-request-available > Fix For: 1.37.0 > > > The log2 function is supported in many databases, such as spark, mysql, > postgres, etc. > {code:java} > > SELECT log2(2); > 1.0 {code} > links: > [https://spark.apache.org/docs/3.4.0/api/sql/index.html#log2] > https://dev.mysql.com/doc/refman/8.0/en/mathematical-functions.html#function_log2 -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Updated] (CALCITE-6228) ELEMENT function infers incorrect return type
[ https://issues.apache.org/jira/browse/CALCITE-6228?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] ASF GitHub Bot updated CALCITE-6228: Labels: pull-request-available (was: ) > ELEMENT function infers incorrect return type > - > > Key: CALCITE-6228 > URL: https://issues.apache.org/jira/browse/CALCITE-6228 > Project: Calcite > Issue Type: Bug > Components: core >Affects Versions: 1.36.0 >Reporter: Mihai Budiu >Priority: Minor > Labels: pull-request-available > > The ELEMENT function is defined in the documentation as follows: > [https://calcite.apache.org/docs/reference.html#collection-functions] > {quote}Returns the sole element of an array or multiset; null if the > collection is empty; throws if it has more than one element. > {quote} > However, the type inference returns just the type of the element of the > collection, without changing its nullability. > The type inference is implemented as follows in SqlStdOperatorTable: > {code:java} > public static final SqlFunction ELEMENT = > SqlBasicFunction.create("ELEMENT", > ReturnTypes.MULTISET_ELEMENT_NULLABLE, > OperandTypes.COLLECTION); > {code} > However, reading the definition of MULTISET_ELEMENT_NULLABLE in > ReturnTypes.java: > {code:java} > public static final SqlReturnTypeInference MULTISET_ELEMENT_NULLABLE = > MULTISET.andThen(SqlTypeTransforms.TO_COLLECTION_ELEMENT_TYPE); > {code} > we notice that it is not forced to be nullable. Probably the correct > implementation would be > {code:java} > public static final SqlReturnTypeInference MULTISET_ELEMENT_NULLABLE = > MULTISET.andThen(SqlTypeTransforms.TO_COLLECTION_ELEMENT_TYPE) > .andThen(SqlTypeTransforms.FORCE_NULLABLE); > {code} -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Resolved] (CALCITE-6215) Support century format datetime/timestamp in pg
[ https://issues.apache.org/jira/browse/CALCITE-6215?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Mihai Budiu resolved CALCITE-6215. -- Resolution: Fixed Fixed in https://github.com/apache/calcite/commits/main/ Thank you [~caicancai] > Support century format datetime/timestamp in pg > --- > > Key: CALCITE-6215 > URL: https://issues.apache.org/jira/browse/CALCITE-6215 > Project: Calcite > Issue Type: Wish > Components: core >Affects Versions: 1.36.0 >Reporter: Caican Cai >Assignee: Caican Cai >Priority: Minor > Labels: pull-request-available > Fix For: 1.37.0 > > > Show support for century format in pg document > {code:java} > SELECT to_char(current_timestamp, 'CC') AS formatted_datetime; > formatted_datetime > > 21 > (1 row) > SELECT to_char(current_date, 'CC') AS formatted_datetime; > formatted_datetime > > 21 > (1 row) {code} -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Commented] (CALCITE-6225) Unparse LOGICAL_AND/OR as MIN/MAX for Snowflake
[ https://issues.apache.org/jira/browse/CALCITE-6225?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17811057#comment-17811057 ] Julian Hyde commented on CALCITE-6225: -- This case (and fix) is too BigQuery-centric. LOGICAL_AND is (if you read SqlLibraryOperators) BigQuery's version of standard EVERY. Let's aim to make BOOLEAN min and max translate from every dialect to every dialect. > Unparse LOGICAL_AND/OR as MIN/MAX for Snowflake > --- > > Key: CALCITE-6225 > URL: https://issues.apache.org/jira/browse/CALCITE-6225 > Project: Calcite > Issue Type: Improvement >Reporter: Tanner Clary >Assignee: Tanner Clary >Priority: Major > Labels: pull-request-available > > Because Snowflake does not support BigQuery's LOGICAL_AND/OR functions, they > can be rewritten as MIN/MAX to achieve the same effect. > For instance, LOGICAL_AND(users.age > 3) will return {{TRUE}} iff every age > is > 3. This is identical to MIN(users.age > 3), because if even one age is > <= 3, it will return {{FALSE}}. > Similarly, for LOGICAL_OR(users.age > 3) will return {{TRUE}} iff there > exists at least one age > 3. This is identical to MAX(users.age > 3) which > will only return {{FALSE}} iff every age is <= 3. -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Created] (CALCITE-6229) Upgrade json-path version to 2.9.0
Will Noble created CALCITE-6229: --- Summary: Upgrade json-path version to 2.9.0 Key: CALCITE-6229 URL: https://issues.apache.org/jira/browse/CALCITE-6229 Project: Calcite Issue Type: Bug Reporter: Will Noble There is a CVE: https://osv.dev/vulnerability/GHSA-pfh2-hfmq-phg5 Probably not critical for most users since it would be strange for unsanitized user input to make it into the JSON parser through Calcite, but should be easy to patch. -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Updated] (CALCITE-6229) Upgrade json-path version to 2.9.0
[ https://issues.apache.org/jira/browse/CALCITE-6229?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] ASF GitHub Bot updated CALCITE-6229: Labels: pull-request-available (was: ) > Upgrade json-path version to 2.9.0 > -- > > Key: CALCITE-6229 > URL: https://issues.apache.org/jira/browse/CALCITE-6229 > Project: Calcite > Issue Type: Bug >Reporter: Will Noble >Priority: Minor > Labels: pull-request-available > > There is a CVE: https://osv.dev/vulnerability/GHSA-pfh2-hfmq-phg5 > Probably not critical for most users since it would be strange for > unsanitized user input to make it into the JSON parser through Calcite, but > should be easy to patch. -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Updated] (CALCITE-6227) ELEMENT(NULL) causes an assertion failure
[ https://issues.apache.org/jira/browse/CALCITE-6227?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] ASF GitHub Bot updated CALCITE-6227: Labels: pull-request-available (was: ) > ELEMENT(NULL) causes an assertion failure > - > > Key: CALCITE-6227 > URL: https://issues.apache.org/jira/browse/CALCITE-6227 > Project: Calcite > Issue Type: Bug > Components: core >Affects Versions: 1.36.0 >Reporter: Mihai Budiu >Priority: Minor > Labels: pull-request-available > > Adding the following test to SqlValidatorTest: > {code:java} > @Test void testElement() { > expr("element(null)").columnType("null"); > } > {code} > causes an assertion failure. The bottom of the stack trace is the following: > {code} > at org.apache.calcite.sql.type.ReturnTypes$2.get(ReturnTypes.java:577) > at org.apache.calcite.sql.type.ReturnTypes$2.get(ReturnTypes.java:571) > at > org.apache.calcite.sql.ExplicitOperatorBinding.getOperandType(ExplicitOperatorBinding.java:78) > at > org.apache.calcite.sql.SqlOperatorBinding$1.get(SqlOperatorBinding.java:255) > at > org.apache.calcite.sql.SqlOperatorBinding$1.get(SqlOperatorBinding.java:253) > at > org.apache.calcite.sql.type.SqlTypeFactoryImpl.leastRestrictive(SqlTypeFactoryImpl.java:174) > at > org.apache.calcite.rel.type.RelDataTypeFactory.leastRestrictive(RelDataTypeFactory.java:222) > at > org.apache.calcite.sql.type.ReturnTypes.lambda$static$3(ReturnTypes.java:537) > at > org.apache.calcite.sql.type.ReturnTypes.lambda$static$6(ReturnTypes.java:586) > at > org.apache.calcite.sql.type.SqlTypeTransformCascade.inferReturnType(SqlTypeTransformCascade.java:58) > at > org.apache.calcite.sql.SqlOperator.inferReturnType(SqlOperator.java:534) > {code} -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Resolved] (CALCITE-6168) RexExecutor can throw during compilation
[ https://issues.apache.org/jira/browse/CALCITE-6168?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Mihai Budiu resolved CALCITE-6168. -- Fix Version/s: 1.37.0 Resolution: Fixed Fixed in https://github.com/apache/calcite/commit/ba9c840b7516acaccee20d778726c818928c40e2 > RexExecutor can throw during compilation > > > Key: CALCITE-6168 > URL: https://issues.apache.org/jira/browse/CALCITE-6168 > Project: Calcite > Issue Type: Bug > Components: core >Affects Versions: 1.36.0 >Reporter: Mihai Budiu >Priority: Minor > Labels: pull-request-available > Fix For: 1.37.0 > > > The RexExecutor is supposed to evaluate expressions at compilation time; if > an expression causes an exception, it should be caught and the expression > should be unchanged. The goal is to have the exception be reported at > runtime. The executor does catch exceptions during its "execute" phase, but > some exceptions can be caught during its "compile" phase. > The following SqlOperatorTest will exhibit such an instance: > {code:java} > @Test > void testCast() { > final SqlOperatorFixture f = fixture(); > f.checkFails("CAST(200 as TINYINT)", "", true); > } > } > {code} > This is the relevant portion of the stack trace: > {code} > at > org.apache.calcite.linq4j.tree.Primitive.checkRoundedRange(Primitive.java:383) > at > org.apache.calcite.linq4j.tree.Primitive.numberValue(Primitive.java:398) > at > org.apache.calcite.linq4j.tree.Expressions.constant(Expressions.java:575) > at > org.apache.calcite.linq4j.tree.OptimizeShuttle.visit(OptimizeShuttle.java:305) > at > org.apache.calcite.linq4j.tree.UnaryExpression.accept(UnaryExpression.java:39) > at > org.apache.calcite.linq4j.tree.TernaryExpression.accept(TernaryExpression.java:47) > at > org.apache.calcite.linq4j.tree.Expressions.acceptExpressions(Expressions.java:3214) > at > org.apache.calcite.linq4j.tree.NewArrayExpression.accept(NewArrayExpression.java:49) > at > org.apache.calcite.linq4j.tree.GotoStatement.accept(GotoStatement.java:64) > at > org.apache.calcite.linq4j.tree.BlockBuilder.optimize(BlockBuilder.java:455) > at > org.apache.calcite.linq4j.tree.BlockBuilder.toBlock(BlockBuilder.java:340) > at > org.apache.calcite.rex.RexExecutorImpl.compile(RexExecutorImpl.java:102) > at > org.apache.calcite.rex.RexExecutorImpl.compile(RexExecutorImpl.java:68) > at > org.apache.calcite.rex.RexExecutorImpl.reduce(RexExecutorImpl.java:133) > at > org.apache.calcite.rex.RexSimplify.simplifyCast(RexSimplify.java:2265) > at org.apache.calcite.rex.RexSimplify.simplify(RexSimplify.java:292) > at > org.apache.calcite.rex.RexSimplify.simplifyUnknownAs(RexSimplify.java:250) > at > org.apache.calcite.rex.RexSimplify.simplifyPreservingType(RexSimplify.java:189) > at > org.apache.calcite.rex.RexSimplify.simplifyPreservingType(RexSimplify.java:184) > at > org.apache.calcite.tools.RelBuilder.lambda$project_$7(RelBuilder.java:2050) > {code} > Notice that this happens during the compile stage: > at > org.apache.calcite.rex.RexExecutorImpl.compile(RexExecutorImpl.java:68) > The simplest fix is probably to move the try/catch block earlier in the flow. -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Resolved] (CALCITE-6229) Upgrade json-path version to 2.9.0
[ https://issues.apache.org/jira/browse/CALCITE-6229?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] hongyu guo resolved CALCITE-6229. - Fix Version/s: 1.37.0 Resolution: Fixed Fixed in [e17098d47|https://github.com/apache/calcite/commit/e17098d47f3c31e4d90cc17e6e1da1175bf49ae4]. [~wnoble] Thanks for your contribution! > Upgrade json-path version to 2.9.0 > -- > > Key: CALCITE-6229 > URL: https://issues.apache.org/jira/browse/CALCITE-6229 > Project: Calcite > Issue Type: Bug >Reporter: Will Noble >Priority: Minor > Labels: pull-request-available > Fix For: 1.37.0 > > > There is a CVE: https://osv.dev/vulnerability/GHSA-pfh2-hfmq-phg5 > Probably not critical for most users since it would be strange for > unsanitized user input to make it into the JSON parser through Calcite, but > should be easy to patch. -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Commented] (CALCITE-6204) When do not expand the plan when SqlToRel, the final plan with SET_SEMANTICS_TABLE is wrong
[ https://issues.apache.org/jira/browse/CALCITE-6204?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17811147#comment-17811147 ] xuyang commented on CALCITE-6204: - Hi, [~libenchao] . I put the related JIRA in Flink here: https://issues.apache.org/jira/browse/FLINK-24024 In Flink, we always do not expand the subquery, and then hit this bug when try to introduce the SESSION window tvf (the first SET SEMANTICS TABLE in Flink). I just worked around it temporarily by always expanding the subquery about SET_SEMANTICS_TABLE. > When do not expand the plan when SqlToRel, the final plan with > SET_SEMANTICS_TABLE is wrong > -- > > Key: CALCITE-6204 > URL: https://issues.apache.org/jira/browse/CALCITE-6204 > Project: Calcite > Issue Type: Bug >Reporter: xuyang >Priority: Major > Attachments: image-2024-01-16-10-12-37-047.png, > image-2024-01-16-10-14-04-036.png, image-2024-01-16-10-15-20-310.png, > image-2024-01-16-10-16-54-471.png > > > Modify the test SqlToRelConverterTest#testTableFunctionWithPartitionKey to > reproduce this bug: > {code:java} > @Test void testTableFunctionWithPartitionKey() { > final String sql = "select *\n" > + "from table(topn(table orders partition by productid, 3))"; > sql(sql).withConfig(c -> c.withExpand(false)).ok(); > } {code} > The final plan is like: > {code:java} > LogicalProject(ROWTIME=[$0], PRODUCTID=[$1], ORDERID=[$2], RANK_NUMBER=[$3]) > LogicalTableFunctionScan(invocation=[TOPN(3)], > rowType=[RecordType(TIMESTAMP(0) ROWTIME, INTEGER PRODUCTID, INTEGER ORDERID, > BIGINT RANK_NUMBER)]) > {code} > We can see that in the final plan the TableFunctionScan lost the partition > keys and lost the subquery that contains the original table source `orders`. > = > {color:#de350b}The following is my attempt to find out where the bug lies. > Some pictures with variables for some key steps are attached to make it > easier for others to understand.{color} > When I dug into the code, I found that the problem may be in the following > steps: > 1. When not expanding plan, we will retain subquery in > SqlToRelConverter#convertCollectionTable > 2. When converting the SqlCall of the "topn" function in > StandardConvertletTable, the first SET_SEMANTICS_TABLE operand will be > forcibly ignored, so only the arg "3" in the "topn" function is retained, and > the original table "order" and partition key are removed. For details, please > refer to StandardConvertletTable#convertOperands > 3. After converting the expression with "topn" SqlCall to RexCall, when > building LogicalTableFunctionScan, we only use the RexCall that does not > contain the original table "order" and partition key, and the complete > subquery retained by step 1 is not used.. > The plan for the relevant steps is as follows: > 1. Subquery retained when bb is not expanded > !image-2024-01-16-10-12-37-047.png|width=656,height=290! > 2.1 > SqlCall before entering StandardConvertletTable > !image-2024-01-16-10-14-04-036.png|width=653,height=183! > 2.2 > RexCall after StandardConvertletTable processing > !image-2024-01-16-10-15-20-310.png|width=578,height=146! > 3. The constructed LogicalTableFunctionScan > !image-2024-01-16-10-16-54-471.png|width=682,height=262! > > -- This message was sent by Atlassian Jira (v8.20.10#820010)