[
https://issues.apache.org/jira/browse/CALCITE-6362?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17836274#comment-17836274
]
Mihai Budiu commented on CALCITE-6362:
--------------------------------------
I guess that the spec for JSON_OBJECT is not really clear about what happens to
data types that do not "exist" in JSON. And Calcite warns about relying on
implicit casts. Perhaps the following would fare better (haven't tried it, but
I assume the behavior for strings is clearer):
SELECT JSON_OBJECT('a': CAST(CAST('2010-01-01' AS DATE) AS VARCHAR)) as c1
> JSON_OBJECT. Internal representation DATE values leaks into resulting JSON
> object
> ---------------------------------------------------------------------------------
>
> Key: CALCITE-6362
> URL: https://issues.apache.org/jira/browse/CALCITE-6362
> Project: Calcite
> Issue Type: Bug
> Components: core
> Reporter: Maksim Zhuravkov
> Priority: Minor
>
> When a CAST function is used inside a JSON_OBJECT, then resulting JSON
> contains a number of days instead
> {code}
> SELECT JSON_OBJECT('a': CAST('2010-01-01' AS DATE)) as c1
> {"a":14610}
> {code}
> I expected to get a date in a string form as many databases do in such case:
> Bigquery:
> {code}
> SELECT JSON_OBJECT('a', CAST('2010-01-10' AS DATE))
> {"a":"2010-01-10"}
> {code}
> PostgreSQL
> {code}
> SELECT json_build_object('a', '2010-01-01'::DATE)
> {"a":"2010-01-01"}
> {code}
> MySql
> {code}
> SELECT json_object('a', DATE '2010-01-01')
> {"a": "2010-01-01"}
> {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
