[jira] [Comment Edited] (CALCITE-5636) Consult type mappings when coercing types
[ https://issues.apache.org/jira/browse/CALCITE-5636?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17762525#comment-17762525 ] Will Noble edited comment on CALCITE-5636 at 9/7/23 12:58 AM: -- Finally getting back to this. Hopefully this is a much better description of the problem. It occurs when using BigQuery's catalog type map (where {{TIMESTAMP}} maps to {{TIMESTAMP WITH LOCAL TIME ZONE}}) and {{SqlToRelConverter}} encounters a query like this: {code:sql} SELECT TIMESTAMP_ADD(DATE(1900, 1, 1), INTERVAL (1) DAY) AS `ts` {code} The parsed {{SqlNode}} has validated type {{TIMESTAMP(0) NOT NULL}}, and it ends up converting into the following relational expression: {code:java} LogicalProject(ts=[+(CAST(DATE(1900, 1, 1)):TIMESTAMP_WITH_LOCAL_TIME_ZONE(0) NOT NULL, *(8640:INTERVAL DAY, 1))]) {code} which of course has converted type {{TIMESTAMP_WITH_LOCAL_TIME_ZONE(0) NOT NULL}}, so the type of the {{SqlNode}} does not match the type of the {{{}RelNode{}}}, and it triggers [this exception|https://github.com/apache/calcite/blob/50c3edfc3d6630528ab51fe836bd50df82cc7db8/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java#L489]. Btw, despite the fact that a BQ {{TIMESTAMP}} is really a timestamp w/ ltz, the {{TIMESTAMP_ADD()}} function actually returns a {{DATETIME}} object (ISO timestamp) when the first argument is a {{DATE}}, so the validated type of the {{SqlNode}} is actually correct, as verified by manually running the query against BQ. My original PR was mistaken in believing that it should be timestamp w/ ltz, as the name seems to imply. Whoops. There may actually be a couple bugs at play here. Anyway, the reason for the discrepancy can be seen in {{SqlValidatorImpl.validateSelectList()}}. This invokes {{expandSelectItem()}} [here|https://github.com/apache/calcite/blob/bcf6bd8577b25c563b1c597c70704594a18ca1a3/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java#L4638], which eventually tries to derive the type of the {{TIMESTAMP_ADD()}} call [here|https://github.com/apache/calcite/blob/bcf6bd8577b25c563b1c597c70704594a18ca1a3/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java#L6555], which must validate the operands in order to derive the type of the function call, and in so doing coerces the type of the first operand by casting it to an ISO timestamp. All of this results in a value in {{expandedSelectItems}} that looks like this: {code:sql} TIMESTAMP_ADD(CAST(DATE(1900, 1, 1) AS TIMESTAMP(0)), INTERVAL 1 DAY) AS `ts` {code} [A few lines down|https://github.com/apache/calcite/blob/bcf6bd8577b25c563b1c597c70704594a18ca1a3/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java#L4659] in {{validateSelectList()}}, we invoke {{validateExpr()}} on that expanded node, and this eventually tries to derive the type of of that {{CAST}} expression, which results in visiting the inserted {{TIMESTAMP(0)}} expression (which is a {{SqlDataTypeSpec}}) [here|https://github.com/apache/calcite/blob/bcf6bd8577b25c563b1c597c70704594a18ca1a3/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java#L6657], and in so doing switches it from {{TIMESTAMP}} to {{TIMESTAMP WITH LOCAL TIME ZONE}}, because it thinks that the {{CAST}} expression was written by the user, and must therefore be translated according to the catalog reader. I'm still thinking about how to handle this. My [original PR|https://github.com/apache/calcite/pull/3144] tried to handle it by consulting the type map when the {{CAST}} is inserted during coercion, but what we probably actually want is for the type map to be consulted during validation IFF the {{SqlDataTypeSpec}} was actually written by the user, as opposed to being inserted during expansion. Perhaps we should introduce a boolean flag to {{SqlDataTypeSpec}} that indicates that the type is final? was (Author: wnoble): Finally getting back to this. Hopefully this is a much better description of the problem. It occurs when using BigQuery's catalog type map (where {{TIMESTAMP}} maps to {{TIMESTAMP WITH LOCAL TIME ZONE}}) and {{SqlToRelConverter}} encounters a query like this: {code:sql} SELECT TIMESTAMP_ADD(DATE(1900, 1, 1), INTERVAL (1) DAY) AS `ts` {code} The parsed {{SqlNode}} has validated type {{TIMESTAMP(0) NOT NULL}}, and it ends up converting into the following relational expression: {code:java} LogicalProject(ts=[+(CAST(DATE(1900, 1, 1)):TIMESTAMP_WITH_LOCAL_TIME_ZONE(0) NOT NULL, *(8640:INTERVAL DAY, 1))]) {code} which of course has converted type {{TIMESTAMP_WITH_LOCAL_TIME_ZONE(0) NOT NULL}}, so the type of the {{SqlNode}} does not match the type of the {{{}RelNode{}}}, and it triggers [this exception|https://github.com/apache/calcite/blob/50c3edfc3d6630528ab51fe836bd50df82cc7db8/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java#L489].
[jira] [Comment Edited] (CALCITE-5636) Consult type mappings when coercing types
[ https://issues.apache.org/jira/browse/CALCITE-5636?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17762525#comment-17762525 ] Will Noble edited comment on CALCITE-5636 at 9/7/23 12:54 AM: -- Finally getting back to this. Hopefully this is a much better description of the problem. It occurs when using BigQuery's catalog type map (where {{TIMESTAMP}} maps to {{TIMESTAMP WITH LOCAL TIME ZONE}}) and {{SqlToRelConverter}} encounters a query like this: {code:sql} SELECT TIMESTAMP_ADD(DATE(1900, 1, 1), INTERVAL (1) DAY) AS `ts` {code} The parsed {{SqlNode}} has validated type {{TIMESTAMP(0) NOT NULL}}, and it ends up converting into the following relational expression: {code:java} LogicalProject(ts=[+(CAST(DATE(1900, 1, 1)):TIMESTAMP_WITH_LOCAL_TIME_ZONE(0) NOT NULL, *(8640:INTERVAL DAY, 1))]) {code} which of course has converted type {{TIMESTAMP_WITH_LOCAL_TIME_ZONE(0) NOT NULL}}, so the type of the {{SqlNode}} does not match the type of the {{{}RelNode{}}}, and it triggers [this exception|https://github.com/apache/calcite/blob/50c3edfc3d6630528ab51fe836bd50df82cc7db8/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java#L489]. Btw, despite the fact that a BQ {{TIMESTAMP}} is really a timestamp w/ ltz, the {{TIMESTAMP_ADD()}} function actually returns a {{DATETIME}} object (ISO timestamp) when the first argument is a {{DATE}}, so the validated type of the {{SqlNode}} is actually correct, as verified by manually running the query against BQ. My original PR was mistaken in believing that it should be timestamp w/ ltz, as the name seems to imply. Whoops. There may actually be a couple bugs at play here. Anyway, the reason for the discrepancy can be seen in {{SqlValidatorImpl.validateSelectList()}}. This invokes {{expandSelectItem()}} [here|https://github.com/apache/calcite/blob/bcf6bd8577b25c563b1c597c70704594a18ca1a3/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java#L4638], which eventually tries to derive the type of the {{TIMESTAMP_ADD()}} call [here|https://github.com/apache/calcite/blob/bcf6bd8577b25c563b1c597c70704594a18ca1a3/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java#L6555], which must validate the operands in order to derive the type of the function call, and in so doing coerces the type of the first operand by casting it to an ISO timestamp. All of this results in a value in {{expandedSelectItems}} that looks like this: {code:sql} TIMESTAMP_ADD(CAST(DATE(1900, 1, 1) AS TIMESTAMP(0)), INTERVAL 1 DAY) AS `ts` {code} [A few lines down|https://github.com/apache/calcite/blob/bcf6bd8577b25c563b1c597c70704594a18ca1a3/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java#L4659] in {{validateSelectList()}}, we invoke {{validateExpr()}} on that expanded node, and this eventually tries to derive the type of of that {{CAST}} expression, which results in visiting the inserted {{TIMESTAMP(0)}} expression (which is a {{SqlDataTypeSpec}}) [here|https://github.com/apache/calcite/blob/bcf6bd8577b25c563b1c597c70704594a18ca1a3/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java#L6657], and in so doing switches it from {{TIMESTAMP}} to {{TIMESTAMP WITH LOCAL TIME ZONE}}, because it thinks that the {{CAST}} expression was written by the user, and must therefore be translated according to the catalog reader. I'm still thinking about how to handle this. was (Author: wnoble): Finally getting back to this. Hopefully this is a much better description of the problem. It occurs when using BigQuery's catalog type map (where {{TIMESTAMP}} maps to {{TIMESTAMP WITH LOCAL TIME ZONE}}) and {{SqlToRelConverter}} encounters a query like this: {code:sql} SELECT TIMESTAMP_ADD(DATE(1900, 1, 1), INTERVAL (1) DAY) AS `ts` {code} The parsed {{SqlNode}} has validated type {{TIMESTAMP(0) NOT NULL}}, and it ends up converting into the following relational expression: {code:java} LogicalProject(ts=[+(CAST(DATE(1900, 1, 1)):TIMESTAMP_WITH_LOCAL_TIME_ZONE(0) NOT NULL, *(8640:INTERVAL DAY, 1))]) {code} which of course has converted type {{TIMESTAMP_WITH_LOCAL_TIME_ZONE(0) NOT NULL}}, so the type of the {{SqlNode}} does not match the type of the {{{}RelNode{}}}, and it triggers [this exception|https://github.com/apache/calcite/blob/50c3edfc3d6630528ab51fe836bd50df82cc7db8/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java#L489]. Btw, despite the fact that a BQ {{TIMESTAMP}} is really a timestamp w/ ltz, the {{TIMESTAMP_ADD()}} function actually returns a {{DATETIME}} object (ISO timestamp) when the first argument is a {{DATE}}, so the validated type of the {{SqlNode}} is actually correct, as verified by manually running the query against BQ. My original PR was mistaken in believing that it should be timestamp w/ ltz, as the name seems to imply. Whoops. There may actually be a
[jira] [Comment Edited] (CALCITE-5636) Consult type mappings when coercing types
[ https://issues.apache.org/jira/browse/CALCITE-5636?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17762525#comment-17762525 ] Will Noble edited comment on CALCITE-5636 at 9/7/23 12:52 AM: -- Finally getting back to this. Hopefully this is a much better description of the problem. It occurs when using BigQuery's catalog type map (where {{TIMESTAMP}} maps to {{TIMESTAMP WITH LOCAL TIME ZONE}}) and {{SqlToRelConverter}} encounters a query like this: {code:sql} SELECT TIMESTAMP_ADD(DATE(1900, 1, 1), INTERVAL (1) DAY) AS `ts` {code} The parsed {{SqlNode}} has validated type {{TIMESTAMP(0) NOT NULL}}, and it ends up converting into the following relational expression: {code:java} LogicalProject(ts=[+(CAST(DATE(1900, 1, 1)):TIMESTAMP_WITH_LOCAL_TIME_ZONE(0) NOT NULL, *(8640:INTERVAL DAY, 1))]) {code} which of course has converted type {{TIMESTAMP_WITH_LOCAL_TIME_ZONE(0) NOT NULL}}, so the type of the {{SqlNode}} does not match the type of the {{{}RelNode{}}}, and it triggers [this exception|https://github.com/apache/calcite/blob/50c3edfc3d6630528ab51fe836bd50df82cc7db8/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java#L489]. Btw, despite the fact that a BQ {{TIMESTAMP}} is really a timestamp w/ ltz, the {{TIMESTAMP_ADD()}} function actually returns a {{DATETIME}} object (ISO timestamp) when the first argument is a {{DATE}}, so the validated type of the {{SqlNode}} is actually correct, as verified by manually running the query against BQ. My original PR was mistaken in believing that it should be timestamp w/ ltz, as the name seems to imply. Whoops. There may actually be a couple bugs at play here. Anyway, the reason for the discrepancy can be seen in {{SqlValidatorImpl.validateSelectList()}}. This invokes {{expandSelectItem()}} [here|https://github.com/apache/calcite/blob/bcf6bd8577b25c563b1c597c70704594a18ca1a3/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java#L4638], which eventually tries to derive the type of the {{TIMESTAMP_ADD()}} call [here|https://github.com/apache/calcite/blob/bcf6bd8577b25c563b1c597c70704594a18ca1a3/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java#L6555], which must validate the operands in order to derive the type of the function call, and in so doing coerces the type of the first operand by casting it to an ISO timestamp. All of this results in a value in {{expandedSelectItems}} that looks like this: {code:sql} TIMESTAMP_ADD(CAST(DATE(1900, 1, 1) AS TIMESTAMP(0)), INTERVAL 1 DAY) AS `ts` {code} [A few lines down|https://github.com/apache/calcite/blob/bcf6bd8577b25c563b1c597c70704594a18ca1a3/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java#L4659] in {{validateSelectList()}}, we invoke {{validateExpr()}} on that expanded node, and this eventually tries to derive the type of of that {{CAST}} expression, which results in visiting the inserted {{SqlDataTypeSpec}} [here|https://github.com/apache/calcite/blob/bcf6bd8577b25c563b1c597c70704594a18ca1a3/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java#L6657], and in so doing switches it from {{TIMESTAMP}} to {{TIMESTAMP WITH LOCAL TIME ZONE}}, because it thinks that the {{CAST}} expression was written by the user, and must therefore be translated according to the catalog reader. I'm still thinking about how to handle this. was (Author: wnoble): Finally getting back to this. Hopefully this is a much better description of the problem. It occurs when using BigQuery's catalog type map (where {{TIMESTAMP}} maps to {{TIMESTAMP WITH LOCAL TIME ZONE}}) and {{SqlToRelConverter}} encounters a query like this: {code:sql} SELECT TIMESTAMP_ADD(DATE(1900, 1, 1), INTERVAL (1) DAY) AS `ts` {code} The parsed {{SqlNode}} has validated type {{TIMESTAMP(0) NOT NULL}}, and it ends up converting into the following relational expression: {code:java} LogicalProject(ts=[+(CAST(DATE(1900, 1, 1)):TIMESTAMP_WITH_LOCAL_TIME_ZONE(0) NOT NULL, *(8640:INTERVAL DAY, 1))]) {code} which of course has converted type {{TIMESTAMP_WITH_LOCAL_TIME_ZONE(0) NOT NULL}}, so the type of the {{SqlNode}} does not match the type of the {{{}RelNode{}}}, and it triggers [this exception|https://github.com/apache/calcite/blob/50c3edfc3d6630528ab51fe836bd50df82cc7db8/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java#L489]. Btw, despite the fact that a BQ {{TIMESTAMP}} is really a timestamp w/ ltz, the {{TIMESTAMP_ADD()}} function actually returns a {{DATETIME}} object (ISO timestamp) when the first argument is a {{DATE}}, so the validated type of the {{SqlNode}} is actually correct, as verified by manually running the query against BQ. My original PR was mistaken in believing that it should be timestamp w/ ltz, as the name seems to imply. Whoops. There may actually be a couple bugs at play here. The reason for
[jira] [Comment Edited] (CALCITE-5636) Consult type mappings when coercing types
[ https://issues.apache.org/jira/browse/CALCITE-5636?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17762525#comment-17762525 ] Will Noble edited comment on CALCITE-5636 at 9/7/23 12:48 AM: -- Finally getting back to this. Hopefully this is a much better description of the problem. It occurs when using BigQuery's catalog type map (where {{TIMESTAMP}} maps to {{TIMESTAMP WITH LOCAL TIME ZONE}}) and {{SqlToRelConverter}} encounters a query like this: {code:sql} SELECT TIMESTAMP_ADD(DATE(1900, 1, 1), INTERVAL (1) DAY) AS `ts` {code} The parsed {{SqlNode}} has validated type {{TIMESTAMP(0) NOT NULL}}, and it ends up converting into the following relational expression: {code:java} LogicalProject(ts=[+(CAST(DATE(1900, 1, 1)):TIMESTAMP_WITH_LOCAL_TIME_ZONE(0) NOT NULL, *(8640:INTERVAL DAY, 1))]) {code} which of course has converted type {{TIMESTAMP_WITH_LOCAL_TIME_ZONE(0) NOT NULL}}, so the type of the {{SqlNode}} does not match the type of the {{{}RelNode{}}}, and it triggers [this exception|https://github.com/apache/calcite/blob/50c3edfc3d6630528ab51fe836bd50df82cc7db8/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java#L489]. Btw, despite the fact that a BQ {{TIMESTAMP}} is really a timestamp w/ ltz, the {{TIMESTAMP_ADD()}} function actually returns a {{DATETIME}} object (ISO timestamp) when the first argument is a {{DATE}}, so the validated type of the {{SqlNode}} is actually correct, as verified by manually running the query against BQ. My original PR was mistaken in believing that it should be timestamp w/ ltz, as the name seems to imply. Whoops. There may actually be a couple bugs at play here. The reason for the discrepancy can be seen in {{SqlValidatorImpl.validateSelectList()}}. This invokes {{expandSelectItem()}} [here|https://github.com/apache/calcite/blob/bcf6bd8577b25c563b1c597c70704594a18ca1a3/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java#L4638], which eventually tries to derive the type of the {{TIMESTAMP_ADD()}} call [here|https://github.com/apache/calcite/blob/bcf6bd8577b25c563b1c597c70704594a18ca1a3/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java#L6555], which must validate the operands in order to derive the type of the function call, and in so doing coerces the type of the first operand by casting it to an ISO timestamp. All of this results in a value in {{expandedSelectItems}} that looks like this: {code:sql} TIMESTAMP_ADD(CAST(DATE(1900, 1, 1) AS TIMESTAMP(0)), INTERVAL 1 DAY) AS `ts` {code} [A few lines down|https://github.com/apache/calcite/blob/bcf6bd8577b25c563b1c597c70704594a18ca1a3/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java#L4659] in {{validateSelectList()}}, we invoke {{validateExpr()}} on that expanded node, and this eventually tries to derive the type of of that {{CAST}} expression, which results in visiting the inserted {{SqlDataTypeSpec}} [here|https://github.com/apache/calcite/blob/bcf6bd8577b25c563b1c597c70704594a18ca1a3/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java#L6657], and in so doing switches it from {{TIMESTAMP}} to {{TIMESTAMP WITH LOCAL TIME ZONE}}, because it thinks that the {{CAST}} expression was written by the user, and must therefore be translated according to the catalog reader. I'm still thinking about how to handle this. was (Author: wnoble): Finally getting back to this. Hopefully this is a much better description of the problem. It occurs when using BigQuery's catalog type map (where {{TIMESTAMP}} maps to {{TIMESTAMP WITH LOCAL TIME ZONE}}) and {{SqlToRelConverter}} encounters a query like this: {code:sql} SELECT TIMESTAMP_ADD(DATE(1900, 1, 1), INTERVAL (1) DAY) AS `ts` {code} The parsed {{SqlNode}} has validated type {{TIMESTAMP(0) NOT NULL}}, and it ends up converting into the following relational expression: {code:java} LogicalProject(ts=[+(CAST(DATE(1900, 1, 1)):TIMESTAMP_WITH_LOCAL_TIME_ZONE(0) NOT NULL, *(8640:INTERVAL DAY, 1))]) {code} which of course has converted type {{TIMESTAMP_WITH_LOCAL_TIME_ZONE(0) NOT NULL}}, so the type of the {{SqlNode}} does not match the type of the {{{}RelNode{}}}, and it triggers [this exception|https://github.com/apache/calcite/blob/50c3edfc3d6630528ab51fe836bd50df82cc7db8/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java#L489]. Btw, despite the fact that a BQ {{TIMESTAMP}} is really a timestamp w/ ltz, the {{TIMESTAMP_ADD()}} function actually returns a {{DATETIME}} object (ISO timestamp) when the first argument is a {{DATE}}, so the validated type of the {{SqlNode}} is actually correct, as verified by manually running the query against BQ. My original PR was mistaken in believing that it should be timestamp w/ ltz, as the name seems to imply. Whoops. The reason for the discrepancy can be seen in
[jira] [Comment Edited] (CALCITE-5636) Consult type mappings when coercing types
[ https://issues.apache.org/jira/browse/CALCITE-5636?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17762525#comment-17762525 ] Will Noble edited comment on CALCITE-5636 at 9/7/23 12:45 AM: -- Finally getting back to this. Hopefully this is a much better description of the problem. It occurs when using BigQuery's catalog type map (where {{TIMESTAMP}} maps to {{TIMESTAMP WITH LOCAL TIME ZONE}}) and {{SqlToRelConverter}} encounters a query like this: {code:sql} SELECT TIMESTAMP_ADD(DATE(1900, 1, 1), INTERVAL (1) DAY) AS `ts` {code} The parsed {{SqlNode}} has validated type {{TIMESTAMP(0) NOT NULL}}, and it ends up converting into the following relational expression: {code:java} LogicalProject(ts=[+(CAST(DATE(1900, 1, 1)):TIMESTAMP_WITH_LOCAL_TIME_ZONE(0) NOT NULL, *(8640:INTERVAL DAY, 1))]) {code} which of course has converted type {{TIMESTAMP_WITH_LOCAL_TIME_ZONE(0) NOT NULL}}, so the type of the {{SqlNode}} does not match the type of the {{{}RelNode{}}}, and it triggers [this exception|https://github.com/apache/calcite/blob/50c3edfc3d6630528ab51fe836bd50df82cc7db8/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java#L489]. Btw, despite the fact that a BQ {{TIMESTAMP}} is really a timestamp w/ ltz, the {{TIMESTAMP_ADD()}} function actually returns a {{DATETIME}} object (ISO timestamp) when the first argument is a {{DATE}}, so the validated type of the {{SqlNode}} is actually correct, as verified by manually running the query against BQ. My original PR was mistaken in believing that it should be timestamp w/ ltz, as the name seems to imply. Whoops. The reason for the discrepancy can be seen in {{SqlValidatorImpl.validateSelectList()}}. This invokes {{expandSelectItem()}} [here|https://github.com/apache/calcite/blob/bcf6bd8577b25c563b1c597c70704594a18ca1a3/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java#L4638], which eventually tries to derive the type of the {{TIMESTAMP_ADD()}} call [here|https://github.com/apache/calcite/blob/bcf6bd8577b25c563b1c597c70704594a18ca1a3/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java#L6555], which must validate the operands in order to derive the type of the function call, and in so doing coerces the type of the first operand by casting it to an ISO timestamp. All of this results in a value in {{expandedSelectItems}} that looks like this: {code:sql} TIMESTAMP_ADD(CAST(DATE(1900, 1, 1) AS TIMESTAMP(0)), INTERVAL 1 DAY) AS `ts` {code} [A few lines down|https://github.com/apache/calcite/blob/bcf6bd8577b25c563b1c597c70704594a18ca1a3/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java#L4659] in {{validateSelectList()}}, we invoke {{validateExpr()}} on that expanded node, and this eventually tries to derive the type of of that {{CAST}} expression, which results in visiting the inserted {{SqlDataTypeSpec}} [here|https://github.com/apache/calcite/blob/bcf6bd8577b25c563b1c597c70704594a18ca1a3/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java#L6657], and in so doing switches it from {{TIMESTAMP}} to {{TIMESTAMP WITH LOCAL TIME ZONE}}, because it thinks that the {{CAST}} expression was written by the user, and must therefore be translated according to the catalog reader. I'm still thinking about how to handle this. was (Author: wnoble): Finally getting back to this. Hopefully this is a much better description of the problem. It occurs when using BigQuery's catalog type map (where {{TIMESTAMP}} maps to {{TIMESTAMP WITH LOCAL TIME ZONE}}) and {{SqlToRelConverter}} encounters a query like this: {code:sql} SELECT TIMESTAMP_ADD(DATE(1900, 1, 1), INTERVAL (1) DAY) AS `ts` {code} The parsed {{SqlNode}} has validated type {{TIMESTAMP(0) NOT NULL}}, and it ends up converting into the following relational expression: {code:java} LogicalProject(ts=[+(CAST(DATE(1900, 1, 1)):TIMESTAMP_WITH_LOCAL_TIME_ZONE(0) NOT NULL, *(8640:INTERVAL DAY, 1))]) {code} which of course has converted type {{TIMESTAMP_WITH_LOCAL_TIME_ZONE(0) NOT NULL}}, so the type of the {{SqlNode}} does not match the type of the {{{}RelNode{}}}, and it triggers [this exception|https://github.com/apache/calcite/blob/50c3edfc3d6630528ab51fe836bd50df82cc7db8/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java#L489]. Btw, despite the fact that a BQ {{TIMESTAMP}} is really a timestamp w/ ltz, the {{TIMESTAMP_ADD()}} function actually returns a {{DATETIME}} object (ISO timestamp) when the first argument is a {{DATE}}, so the validated type of the {{SqlNode}} is actually correct, as verified by manually running the query against BQ. My original PR was mistaken in believing that it should be timestamp w/ ltz, as the name seems to imply. Whoops. The reason for the discrepancy can be seen in {{SqlValidatorImpl.validateSelectList()}}. This invokes