wnob commented on code in PR #3054:
URL: https://github.com/apache/calcite/pull/3054#discussion_r1091259053
##########
babel/src/test/resources/sql/big-query.iq:
##########
@@ -659,6 +659,8 @@ SELECT LENGTH("hello") as length;
#####################################################################
# DATE
#
+# 0. DATE(string)
+# Shorthand for 'CAST(string AS DATE)'
Review Comment:
These overloads are actually ambiguous due to BigQuery's type system. String
literals are implicitly converted to date/time literals if they have the
correct format. Here are some queries I tried to illuminate the situation:
This is actually an example of the `DATE(timestamp_expression[, time_zone])`
overload. The first argument must be a string literal:
```
SELECT DATE('2023-01-30', 'America/Los_Angeles');
=> 2023-01-29
```
If we get rid of the time zone argument, we can operate on a non-literal
string:
```
SELECT DATE(SUBSTRING('x2023-01-30', 2));
=> 2023-01-30
```
However, if we try a non-literal string with the time zone, it's an error:
```
SELECT DATE(SUBSTRING('x2023-01-30', 2), 'America/Los_Angeles');
=> No matching signature for function DATE for argument types: STRING,
STRING. Supported signatures: DATE(TIMESTAMP, [STRING]); DATE(DATETIME);
DATE(INT64, INT64, INT64); DATE(DATE); DATE(STRING) at [1:8]
```
So, it's true that there is a legitimate `DATE(STRING)` function that I
steamrolled because it's not documented, but in general there is some ambiguity
in GoogleSQL about what is a string vs. a timestamp or datetime or date literal
(a string can be implicitly coerced into any of these types). I think we should
proceed with these changes and file a bug to address this implicit coercion
throughout the BQ function library later.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
