Elizaveta Batanina
04/27/2023, 3:19 PMt_env.execute_sql("""
CREATE VIEW response_time AS (
SELECT KEY_COUNTRY_CODE, GLOBAL_ENTITY_ID, VENDOR_CODE, TM,
TIMESTAMPDIFF(SECOND,`ORDER_PARSED`.SENT_TO_VENDOR_AT, `ORDER_PARSED`.ACCEPTED_BY_VENDOR_AT) as RESPONSE_TIME
from delivery_events_liza)
""")
An error occurred while calling o498.executeSql.
: java.lang.AssertionError: Conversion to relational algebra failed to preserve datatypes:
validated type:
RecordType(VARCHAR(2147483647) CHARACTER SET "UTF-16LE" KEY_COUNTRY_CODE, VARCHAR(2147483647) CHARACTER SET "UTF-16LE" GLOBAL_ENTITY_ID, VARCHAR(2147483647) CHARACTER SET "UTF-16LE" VENDOR_CODE, TIMESTAMP(3) *ROWTIME* TM, INTEGER RESPONSE_TIME) NOT NULL
converted type:
RecordType(VARCHAR(2147483647) CHARACTER SET "UTF-16LE" KEY_COUNTRY_CODE, VARCHAR(2147483647) CHARACTER SET "UTF-16LE" GLOBAL_ENTITY_ID, VARCHAR(2147483647) CHARACTER SET "UTF-16LE" VENDOR_CODE, TIMESTAMP(3) TM, INTEGER RESPONSE_TIME) NOT NULL
rel:
LogicalProject(KEY_COUNTRY_CODE=[$2], GLOBAL_ENTITY_ID=[$3], VENDOR_CODE=[JSON_VALUE($1, _UTF-16LE'$.VENDOR_CODE')], TM=[CAST(parse_bq_datetime_udf($0)):TIMESTAMP(3)], RESPONSE_TIME=[CAST(/INT(Reinterpret(-(parse_order($1).ACCEPTED_BY_VENDOR_AT, parse_order($1).SENT_TO_VENDOR_AT)), 1000)):INTEGER])
LogicalTableScan(table=[[default_catalog, default_database, delivery_events_liza]])
How to fix this problem?
If I just remove TM field, which is a watermark, my query finishes without an error.
Thanks for help!