core/src/test/resources/sql/cast-with-format.iq

# cast-with-format.iq - Tests for CAST(value AS type FORMAT formatString)
#
# Licensed to the Apache Software Foundation (ASF) under one or more
# contributor license agreements.  See the NOTICE file distributed with
# this work for additional information regarding copyright ownership.
# The ASF licenses this file to you under the Apache License, Version 2.0
# (the "License"); you may not use this file except in compliance with
# the License.  You may obtain a copy of the License at
#
# http://www.apache.org/licenses/LICENSE-2.0
#
# Unless required by applicable law or agreed to in writing, software
# distributed under the License is distributed on an "AS IS" BASIS,
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
# See the License for the specific language governing permissions and
# limitations under the License.
#
# Copied from
# https://github.com/apache/impala/blob/master/tests/query_test/test_cast_with_format.py
# (as of 2022/01/10)
#
!use post
!set outputformat csv

### basic_inputs_without_row ###############################
#

# Cast without format clause to cover the default format
select cast('2017-05-01 01:23:45.678912345' as
    timestamp);
EXPR$0
2017-05-01 01:23:45
!ok

!if (false) {
### disabled until Bug.CALCITE_6269_FIXED ###

# Basic input to cover a datetime with timezone scenario
select cast('2017-05-03 08:59:01.123456789PM 01:30'
    as timestamp FORMAT 'YYYY-MM-DD HH12:MI:SS.FF9PM TZH:TZM');
EXPR$0
2017-05-03 20:59:01.123456789
!ok

# Input that contains shuffled date without time
select cast('12-2010-05' as timestamp format
    'DD-YYYY-MM');
EXPR$0
2010-05-12 00:00:00
!ok

# Shuffle the input timestamp and the format clause
select cast('59 04-30-2017-05 01PM 01:08.123456789'
    as timestamp FORMAT 'MI DD-TZM-YYYY-MM TZHPM SS:HH12.FF9');
EXPR$0
2017-05-04 20:59:01.123456789
!ok

# Input and format without separators
# Note, 12:01 HH12 AM is 00:01 with the internal 0-23 representation.
select cast('20170501120159123456789AM-0130' as
    timestamp FORMAT 'YYYYDDMMHH12MISSFFAMTZHTZM');
EXPR$0
2017-01-05 00:01:59.123456789
!ok

# Shuffled input without separators
select cast('59043020170501PM0108123456789'
    as timestamp FORMAT 'MIDDTZMYYYYMMTZHPMSSHH12FF9');
EXPR$0
2017-05-04 20:59:01.123456789
!ok

# Separator section lengths differ between input and format
select cast('--2017----05-01-' as
    timestamp FORMAT '-YYYY--MM---DD---');
EXPR$0
2017-05-01 00:00:00
!ok

# Loose separator type matching. Checking if the input/format is surrounded by
# either single or double quotes.
select cast("2017-./,';: 06-01" as
    timestamp FORMAT "YYYY', -MM;:.DD");
EXPR$0
2017-06-01 00:00:00
!ok

select cast('2017-./,\';: 07-01' as
    timestamp FORMAT "YYYY', -MM;:.DD");
EXPR$0
2017-07-01 00:00:00
!ok

select cast("2017-./,';: 08-01" as
    timestamp FORMAT 'YYYY\', -MM;:.DD');
EXPR$0
2017-08-01 00:00:00
!ok

select cast('2017-./,\';: 09-01' as
    timestamp FORMAT 'YYYY\', -MM;:.DD');
EXPR$0
2017-09-01 00:00:00
!ok

# Escaped double quotes in the input are not taken as the escaping character for the
# following single quote.
select cast("2013\\'09-01" as
    timestamp FORMAT "YYYY'MM-DD");
EXPR$0
NULL
!ok

select cast("2013\\\'09-02" as
    timestamp FORMAT "YYYY'MM-DD");
EXPR$0
NULL
!ok

select cast("2013\\\\'09-03" as
    timestamp FORMAT "YYYY'MM-DD");
EXPR$0
NULL
!ok

# If the input string has unprocessed tokens
select cast('2017-05-01 12:30' as
    timestamp FORMAT 'YYYY-MM-DD');
EXPR$0
NULL
!ok
select cast('2017-05-01-12:30' as
    timestamp FORMAT 'YYYY-MM-DD-');
EXPR$0
NULL
!ok

# If the format string has unprocessed tokens
select cast('2017-05-01' as
    timestamp FORMAT 'YYYY-MM-DD HH12:MI');
EXPR$0
NULL
!ok
select cast('2017-05-01-' as
    timestamp FORMAT 'YYYY-MM-DD-HH12');
EXPR$0
NULL
!ok

# Timestamp to string types formatting
select cast(cast('2012-11-04 13:02:59.123456' as timestamp)
    as varchar format 'DD-MM-YYYY MI:HH12:SS A.M. FF9 DDD SSSSS HH12 HH24');
EXPR$0
04-11-2012 02:01:59 P.M. 123456000 309 46979 01 13
!ok

select cast(cast('2012-11-04 13:02:59.123456' as timestamp)
    as varchar format 'DD-MM-YYYY MI:HH12:SS A.M. FF9 DDD SSSSS HH12 HH24');
EXPR$0
04-11-2012 02:01:59 P.M. 123456000 309 46979 01 13
!ok

select cast(cast('2012-11-04 13:02:59.123456' as timestamp)
    as char(50) format 'DD-MM-YYYY MI:HH12:SS A.M. FF9 DDD SSSSS HH12 HH24');
EXPR$0
04-11-2012 02:01:59 P.M. 123456000 309 46979 01 13
!ok
!}

# Cast NULL string to timestamp
select cast(cast(NULL as varchar) as timestamp
    FORMAT 'YYYY-MM-DD');
EXPR$0
null
!ok

# Cast NULL timestamp to string
select cast(cast(NULL as timestamp) as varchar
    FORMAT 'YYYY-MM-DD');
EXPR$0
null
!ok

!if (false) {
### disabled until Bug.CALCITE_6269_FIXED ###
### iso8601_format #################################################
#

# Basic string to timestamp scenario
select cast('2018-11-10T15:11:04Z' as
    timestamp FORMAT 'YYYY-MM-DDTHH24:MI:SSZ');
EXPR$0
2018-11-10 15:11:04
!ok

# ISO 8601 format elements are case-insensitive
select cast('2018-11-09t15:11:04Z' as
    timestamp FORMAT 'YYYY-MM-DDTHH24:MI:SSz');
EXPR$0
2018-11-09 15:11:04
!ok

select cast('2018-11-08T15:11:04z' as
    timestamp FORMAT 'YYYY-MM-DDtHH24:MI:SSZ');
EXPR$0
2018-11-08 15:11:04
!ok

# Format path
select cast(cast('2018-11-10 15:11:04' as
    timestamp) as varchar format 'YYYY-MM-DDTHH24:MI:SSZ');
EXPR$0
2018-11-10T15:11:04Z
!ok
!}

!if (false) {
### disabled until Bug.CALCITE_6269_FIXED ###
### lowercase_format_elements #################################################

select cast('2019-11-20 15:59:44.123456789 01:01' as
    timestamp format 'yyyy-mm-dd hh24:mi:ss.ff9 tzh-tzm');
EXPR$0
2019-11-20 15:59:44.123456789
!ok

select cast('2019-300 15:59:44.123456789 01:01' as
    timestamp format 'yyyy-ddd hh24:mi:ss.ff9 tzh-tzm');
EXPR$0
2019-10-27 15:59:44.123456789
!ok

select cast('2019-11-21 11:59:44.123456789 p.m. 01:01'
    as timestamp format 'yyyy-mm-dd hh12:mi:ss.ff9 am tzh-tzm');
EXPR$0
2019-11-21 23:59:44.123456789
!ok

select cast('2019-11-22 10000.123456789 02:02'
    as timestamp format 'yyyy-mm-dd sssss ff9 tzh-tzm');
EXPR$0
2019-11-22 02:46:40.123456789
!ok
!}

### year #################################################
#
# YYYY with less than 4 digits in the input
!set now_string '2019-01-01 11:11:11'

select cast('095-01-31' as
    timestamp FORMAT 'YYYY-MM-DD');
EXPR$0
0095-01-31 00:00:00
!ok

select cast('95-02-28' as
    timestamp FORMAT 'YYYY-MM-DD');
EXPR$0
0095-02-28 00:00:00
!ok

select cast('5-03-31' as
    timestamp FORMAT 'YYYY-MM-DD');
EXPR$0
0005-03-31 00:00:00
!ok

!if (false) {
### disabled until Bug.CALCITE_6269_FIXED ###
# YYY with less than 3 digits in the input
select cast('95-04-30' as
    timestamp FORMAT 'YYY-MM-DD');
EXPR$0
2095-04-30 00:00:00
!ok

select cast('5-05-31' as
    timestamp FORMAT 'YYY-MM-DD');
EXPR$0
2015-05-31 00:00:00
!ok

# YY with 1 digits in the input
select cast('5-06-30' as
    timestamp FORMAT 'YY-MM-DD');
EXPR$0
2015-06-30 00:00:00
!ok

# YYY, YY, Y tokens without separators
select cast('0950731' as
    timestamp FORMAT 'YYYMMDD');
EXPR$0
2095-07-31 00:00:00
!ok

select cast('950831' as
    timestamp FORMAT 'YYMMDD');
EXPR$0
2095-08-31 00:00:00
!ok

select cast('50930' as
    timestamp FORMAT 'YMMDD');
EXPR$0
2015-09-30 00:00:00
!ok
!}

# Timestamp to string formatting
select cast(cast('2019-01-01' as timestamp) as varchar
    format 'YYYY');
EXPR$0
2019
!ok

select cast(cast('2019-01-01' as timestamp) as varchar
    format 'YY');
EXPR$0
19
!ok

!if (false) {
### disabled until Bug.CALCITE_6269_FIXED ###
select cast(cast('2019-01-01' as timestamp) as varchar
    format 'YYY');
EXPR$0
019
!ok

select cast(cast('2019-01-01' as timestamp) as varchar
    format 'Y');
EXPR$0
9
!ok
!}
!set now_string null

!if (false) {
### disabled until Bug.CALCITE_6269_FIXED ###
### round_year #################################################
#

# Test lower boundary of round year
select cast('1399-05-01' as
    timestamp FORMAT 'RRRR-MM-DD');
EXPR$0
NULL
!ok

select cast('1400-05-21' as
    timestamp FORMAT 'RRRR-MM-DD');
EXPR$0
1400-05-21 00:00:00
!ok

# RRRR with 4-digit year falls back to YYYY
!set now_string '2019-01-01 11:11:11'
select cast('2017-05-31' as
    timestamp FORMAT 'RRRR-MM-DD');
EXPR$0
2017-05-31 00:00:00
!ok

# RRRR with 3-digit year fills digits from current year
select cast('017-01-31' as
    timestamp FORMAT 'RRRR-MM-DD');
EXPR$0
2017-01-31 00:00:00
!ok

# RRRR wit 1-digit year fills digits from current year
select cast('0-07-31' as
    timestamp FORMAT 'RRRR-MM-DD');
EXPR$0
2010-07-31 00:00:00
!ok

# RR with 1-digit year fills digits from current year
select cast('9-08-31' as
    timestamp FORMAT 'RR-MM-DD');
EXPR$0
2019-08-31 00:00:00
!ok

# Round year when last 2 digits of current year is less than 50
!set now_string '2049-01-01 11:11:11'
select cast('49-03-31' as
    timestamp FORMAT 'RRRR-MM-DD');
EXPR$0
2049-03-31 00:00:00
!ok

select cast('50-03-31' as
    timestamp FORMAT 'RRRR-MM-DD');
EXPR$0
1950-03-31 00:00:00
!ok

!set now_string '2000-01-01 11:11:11'
select cast('49-03-31' as
    timestamp FORMAT 'RR-MM-DD');
EXPR$0
2049-03-31 00:00:00
!ok

select cast('50-03-31' as
    timestamp FORMAT 'RR-MM-DD');
EXPR$0
1950-03-31 00:00:00
!ok

# Round year when last 2 digits of current year is greater than 49
!set now_string '2050-01-01 11:11:11'
select cast('49-03-31' as
    timestamp FORMAT 'RRRR-MM-DD');
EXPR$0
2149-03-31 00:00:00
!ok

select cast('50-03-31' as
    timestamp FORMAT 'RRRR-MM-DD');
EXPR$0
2050-03-31 00:00:00
!ok

!set now_string '2099-01-01 11:11:11'
select cast('49-03-31' as
    timestamp FORMAT 'RR-MM-DD');
EXPR$0
2149-03-31 00:00:00
!ok

select cast('50-03-31' as
    timestamp FORMAT 'RR-MM-DD');
EXPR$0
2050-03-31 00:00:00
!ok

# In a datetime to sting cast round year act like regular 'YYYY' or 'YY' tokens.
select cast(cast('2019-01-01' as timestamp) as varchar
    format 'RRRR');
EXPR$0
2019
!ok

select cast(cast('2019-01-01' as timestamp) as varchar
    format 'RR');
EXPR$0
19
!ok
!set now_string null
!}

### month_name #################################################
#
# Test different lowercase vs uppercase scenarios with the string to datetime path.
select cast('2010-February-11' as timestamp FORMAT
    'YYYY-MONTH-DD');
EXPR$0
2010-02-11 00:00:00
!ok

select cast('2010-march-12' as timestamp FORMAT
    'YYYY-MONTH-DD');
EXPR$0
2010-03-12 00:00:00
!ok

select cast('APRIL 13 2010' as date FORMAT
    'MONTH DD YYYY');
EXPR$0
2010-04-13
!ok

select cast('2010 14 MAY' as timestamp FORMAT
    'YYYY DD MONTH');
EXPR$0
2010-05-14 00:00:00
!ok

select cast('2010 14 June' as timestamp FORMAT
    'YYYY DD MONTH');
EXPR$0
2010-06-14 00:00:00
!ok

select cast('2010 14 july' as timestamp FORMAT
    'YYYY DD MONTH');
EXPR$0
2010-07-14 00:00:00
!ok

select cast('2010 14 AUGUST' as timestamp FORMAT
    'YYYY DD MONTH');
EXPR$0
2010-08-14 00:00:00
!ok

!if (false) {
### disabled until Bug.CALCITE_6269_FIXED ###
select cast('2010 14 September' as date FORMAT
    'YYYY DD month');
EXPR$0
2010-09-14
!ok

select cast('2010 14 october' as date FORMAT
    'YYYY DD month');
EXPR$0
2010-10-14
!ok

select cast('2010 14 NOVEMBER' as date FORMAT
    'YYYY DD month');
EXPR$0
2010-11-14
!ok

select cast('2010 14 December' as date FORMAT
    'YYYY DD month');
EXPR$0
2010-12-14
!ok

select cast('2010 14 january' as date FORMAT
    'YYYY DD month');
EXPR$0
2010-01-14
!ok
!}

!if (false) {
### disabled until Bug.CALCITE_6269_FIXED ###
# Test different lowercase vs uppercase scenarios with the datetime to string path.
select cast(date'2010-10-18' as varchar FORMAT
    'MONTH Month month');
EXPR$0
OCTOBER   October   october
!ok

select cast(cast('2010-11-18' as timestamp) as varchar
    FORMAT 'MONTH Month month');
EXPR$0
NOVEMBER  November  november
!ok

select cast(date'2010-12-19' as varchar FORMAT
    'MONTH Month month');
EXPR$0
DECEMBER  December  december
!ok

select cast(date'2010-01-19' as varchar FORMAT
    'MONTH Month month');
EXPR$0
JANUARY   January   january
!ok

select cast(date'2010-02-19' as varchar FORMAT
    'MONTH Month month');
EXPR$0
FEBRUARY  February  february
!ok

select cast(date'2010-03-19' as varchar FORMAT
    'MONTH Month month');
EXPR$0
MARCH     March     march
!ok

select cast(date'2010-04-19' as varchar FORMAT
    'MONTH Month month');
EXPR$0
APRIL     April     april
!ok

select cast(date'2010-05-19' as varchar FORMAT
    'MONTH Month month');
EXPR$0
MAY       May       may
!ok

select cast(date'2010-06-19' as varchar FORMAT
    'MONTH Month month');
EXPR$0
JUNE      June      june
!ok

select cast(date'2010-07-19' as varchar FORMAT
    'MONTH Month month');
EXPR$0
JULY      July      july
!ok

select cast(date'2010-08-19' as varchar FORMAT
    'MONTH Month month');
EXPR$0
AUGUST    August    august
!ok

select cast(date'2010-09-19' as varchar FORMAT
    'MONTH Month month');
EXPR$0
SEPTEMBER September september
!ok
!}

!if (false) {
### disabled until Bug.CALCITE_6269_FIXED ###
# Test odd casing of month token.
select cast(date'2010-09-20' as varchar FORMAT
    'MOnth MONth MONTh');
EXPR$0
SEPTEMBER SEPTEMBER SEPTEMBER
!ok

select cast(date'2010-09-21' as varchar FORMAT
    'montH monTH moNTH moNTH');
EXPR$0
september september september september
!ok

# Test different lowercase vs uppercase scenarios with the datetime to string path
# when FM is provided.
select cast(date'2010-10-18' as varchar FORMAT
    'FMMONTH FMMonth FMmonth');
EXPR$0
OCTOBER October october
!ok

select cast(cast('2010-11-18' as timestamp) as varchar
    FORMAT 'FMMONTH FMMonth FMmonth');
EXPR$0
NOVEMBER November november
!ok

select cast(date'2010-12-19' as varchar FORMAT
    'FMMONTH FMMonth FMmonth');
EXPR$0
DECEMBER December december
!ok

select cast(date'2010-01-19' as varchar FORMAT
    'FMMONTH FMMonth FMmonth');
EXPR$0
JANUARY January january
!ok

select cast(date'2010-02-19' as varchar FORMAT
    'FMMONTH FMMonth FMmonth');
EXPR$0
FEBRUARY February february
!ok

select cast(date'2010-03-19' as varchar FORMAT
    'FMMONTH FMMonth FMmonth');
EXPR$0
MARCH March march
!ok

select cast(date'2010-04-19' as varchar FORMAT
    'FMMONTH FMMonth FMmonth');
EXPR$0
APRIL April april
!ok

select cast(date'2010-05-19' as varchar FORMAT
    'FMMONTH FMMonth FMmonth');
EXPR$0
MAY May may
!ok

select cast(date'2010-06-19' as varchar FORMAT
    'FMMONTH FMMonth FMmonth');
EXPR$0
JUNE June june
!ok

select cast(date'2010-07-19' as varchar FORMAT
    'FMMONTH FMMonth FMmonth');
EXPR$0
JULY July july
!ok

select cast(date'2010-08-19' as varchar FORMAT
    'FMMONTH FMMonth FMmonth');
EXPR$0
AUGUST August august
!ok

select cast(date'2010-09-19' as varchar FORMAT
    'FMMONTH FMMonth FMmonth');
EXPR$0
SEPTEMBER September september
!ok
!}

!if (false) {
# Incorrect month name.
select cast('2010 15 JU' as timestamp FORMAT
    'YYYY DD MONTH');
EXPR$0
NULL
!ok
!}

# MONTH token without surrounding separators.
select cast('2010SEPTEMBER17' as date FORMAT
    'YYYYMONTHDD');
EXPR$0
2010-09-17
!ok

select cast('2010OCTOBER17' as timestamp FORMAT
    'YYYYMONTHDD');
EXPR$0
2010-10-17 00:00:00
!ok

!if (false) {
### disabled until Bug.CALCITE_6269_FIXED ###
# Applying FX and FM modifiers on Month token.
select cast(cast('2010-07-20' as timestamp) as varchar
    FORMAT 'YYYYmonthDD');
EXPR$0
2010july     20
!ok

select cast(date'2010-09-20' as varchar
    FORMAT 'YYYYmonthDD');
EXPR$0
2010september20
!ok

select cast(cast('2010-08-20' as timestamp) as varchar
    FORMAT 'YYYYFMMonthDD');
EXPR$0
2010August20
!ok

select cast(cast('2010-10-20' as timestamp) as varchar
    FORMAT 'FXYYYYFMMONTHDD');
EXPR$0
2010OCTOBER20
!ok

select cast('2010-February-19' as timestamp FORMAT
    'FXYYYY-MONTH-DD');
EXPR$0
NULL
!ok

select cast('2010-February -21' as timestamp FORMAT
    'FXYYYY-MONTH-DD');
EXPR$0
2010-02-21 00:00:00
!ok

select cast('2010-February 22' as date FORMAT
    'FXYYYY-MONTHDD');
EXPR$0
2010-02-22
!ok

select cast('2010-February-20' as timestamp FORMAT
    'FXYYYY-FMMONTH-DD');
EXPR$0
2010-02-20 00:00:00
!ok
!}

### short_month_name #################################################
#
# Test different lowercase vs uppercase scenarios with the string to datetime path.
select cast('2015-Feb-11' as timestamp FORMAT
    'YYYY-MON-DD');
EXPR$0
2015-02-11 00:00:00
!ok

select cast('2015-mar-12' as timestamp FORMAT
    'YYYY-MON-DD');
EXPR$0
2015-03-12 00:00:00
!ok

select cast('APR 13 2015' as timestamp FORMAT
    'MON DD YYYY');
EXPR$0
2015-04-13 00:00:00
!ok

select cast('2015 14 MAY' as timestamp FORMAT
    'YYYY DD MON');
EXPR$0
2015-05-14 00:00:00
!ok

select cast('2015 14 jun' as timestamp FORMAT
    'YYYY DD MON');
EXPR$0
2015-06-14 00:00:00
!ok

select cast('2015 14 Jul' as timestamp FORMAT
    'YYYY DD MON');
EXPR$0
2015-07-14 00:00:00
!ok

select cast('2015 14 AUG' as timestamp FORMAT
    'YYYY DD MON');
EXPR$0
2015-08-14 00:00:00
!ok

!if (false) {
### disabled until Bug.CALCITE_6269_FIXED ###
select cast('2015 14 Sep' as timestamp FORMAT
    'YYYY DD mon');
EXPR$0
2015-09-14 00:00:00
!ok

select cast('2015 14 oct' as timestamp FORMAT
    'YYYY DD mon');
EXPR$0
2015-10-14 00:00:00
!ok

select cast('2015 14 nov' as timestamp FORMAT
    'YYYY DD mon');
EXPR$0
2015-11-14 00:00:00
!ok

select cast('2015 14 DEC' as timestamp FORMAT
    'YYYY DD mon');
EXPR$0
2015-12-14 00:00:00
!ok

select cast('2015 14 Jan' as timestamp FORMAT
    'YYYY DD mon');
EXPR$0
2015-01-14 00:00:00
!ok

# Test different lowercase vs uppercase scenarios with the datetime to string path.
select cast(date'2015-10-18' as varchar FORMAT
    'MON Mon mon');
EXPR$0
OCT Oct oct
!ok

select cast(cast('2015-11-18' as timestamp) as varchar
    FORMAT 'MON Mon mon');
EXPR$0
NOV Nov nov
!ok

select cast(date'2015-12-19' as varchar FORMAT
    'MON Mon mon');
EXPR$0
DEC Dec dec
!ok

select cast(date'2015-01-19' as varchar FORMAT
    'MON Mon mon');
EXPR$0
JAN Jan jan
!ok

select cast(date'2015-02-19' as varchar FORMAT
    'MON Mon mon');
EXPR$0
FEB Feb feb
!ok

select cast(date'2015-03-19' as varchar FORMAT
    'MON Mon mon');
EXPR$0
MAR Mar mar
!ok

select cast(date'2015-04-19' as varchar FORMAT
    'MON Mon mon');
EXPR$0
APR Apr apr
!ok

select cast(date'2015-05-19' as varchar FORMAT
    'MON Mon mon');
EXPR$0
MAY May may
!ok

select cast(date'2015-06-19' as varchar FORMAT
    'MON Mon mon');
EXPR$0
JUN Jun jun
!ok

select cast(date'2015-07-19' as varchar FORMAT
    'MON Mon mon');
EXPR$0
JUL Jul jul
!ok

select cast(date'2015-08-19' as varchar FORMAT
    'MON Mon mon');
EXPR$0
AUG Aug aug
!ok

select cast(date'2015-09-19' as varchar FORMAT
    'MON Mon mon');
EXPR$0
SEP Sep sep
!ok

# Test odd casing of short month token.
select cast(date'2010-09-22' as varchar FORMAT
    'MOn mON moN');
EXPR$0
SEP sep sep
!ok

# Incorrect month name.
select cast('2015 15 JU' as timestamp FORMAT
    'YYYY DD MON');
EXPR$0
NULL
!ok
!}

# MON token without separators in the format.
select cast('2015AUG17' as date FORMAT
    'YYYYMONDD');
EXPR$0
2015-08-17
!ok

!if (false) {
### disabled until Bug.CALCITE_6269_FIXED ###
select cast(cast('2015-07-20' as timestamp) as varchar
    FORMAT 'YYYYmonDD');
EXPR$0
2015jul20
!ok

# FX/FM has no effect on MON.
select cast(cast('2015-08-21' as timestamp) as varchar
    FORMAT 'FXYYYYmonDD');
EXPR$0
2015aug21
!ok

select cast(date'2015-09-22' as varchar
    FORMAT 'FXYYYYFMMonDD');
EXPR$0
2015Sep22
!ok
!}

!if (false) {
### disabled until Bug.CALCITE_6269_FIXED ###
### week_of_year #################################################
#
select cast(cast('2019-01-01' as timestamp) as varchar
    FORMAT 'WW');
EXPR$0
01
!ok

select cast(date'2019-01-07' as varchar
    FORMAT 'WW');
EXPR$0
01
!ok

select cast(cast('2019-01-08' as timestamp) as varchar
    FORMAT 'WW');
EXPR$0
02
!ok

select cast(date'2019-02-01' as varchar
    FORMAT 'WW');
EXPR$0
05
!ok

select cast(cast('2019-02-05' as timestamp) as varchar
    FORMAT 'WW');
EXPR$0
06
!ok

select cast(date'2019-12-01' as varchar
    FORMAT 'WW');
EXPR$0
48
!ok

select cast(cast('2019-12-02' as timestamp) as varchar
    FORMAT 'WW');
EXPR$0
48
!ok

select cast(date'2019-12-03' as varchar
    FORMAT 'WW');
EXPR$0
49
!ok

select cast(cast('2019-12-30' as timestamp) as varchar
    FORMAT 'WW');
EXPR$0
52
!ok

select cast(date'2019-12-31' as varchar
    FORMAT 'WW');
EXPR$0
53
!ok

select cast(cast('2020-01-01' as timestamp) as varchar
    FORMAT 'WW');
EXPR$0
01
!ok
!}

!if (false) {
### disabled until Bug.CALCITE_6269_FIXED ###
### week_of_month #################################################
#
select cast(cast('2019-01-01' as timestamp) as varchar
    FORMAT 'W');
EXPR$0
1
!ok

select cast(date'2019-01-07' as varchar
    FORMAT 'W');
EXPR$0
1
!ok

select cast(cast('2019-01-08' as timestamp) as varchar
    FORMAT 'W');
EXPR$0
2
!ok

select cast(date'2019-01-14' as varchar
    FORMAT 'W');
EXPR$0
2
!ok

select cast(cast('2019-01-15' as timestamp) as varchar
    FORMAT 'W');
EXPR$0
3
!ok

select cast(date'2019-01-21' as varchar
    FORMAT 'W');
EXPR$0
3
!ok

select cast(cast('2019-01-22' as timestamp) as varchar
    FORMAT 'W');
EXPR$0
4
!ok

select cast(date'2019-01-28' as varchar
    FORMAT 'W');
EXPR$0
4
!ok

select cast(cast('2019-01-29' as timestamp) as varchar
    FORMAT 'W');
EXPR$0
5
!ok

select cast(date'2019-02-01' as varchar
    FORMAT 'W');
EXPR$0
1
!ok
!}

### day_in_year #################################################
#
# Test "day in year" token in a non leap year scenario
select cast('2019 1' as timestamp FORMAT 'YYYY DDD');
EXPR$0
2019-01-01 00:00:00
!ok

select cast('2019 31' as timestamp FORMAT 'YYYY DDD');
EXPR$0
2019-01-31 00:00:00
!ok

select cast('2019 32' as timestamp FORMAT 'YYYY DDD');
EXPR$0
2019-02-01 00:00:00
!ok

select cast('2019 60' as timestamp FORMAT 'YYYY DDD');
EXPR$0
2019-03-01 00:00:00
!ok

select cast('2019 365' as timestamp FORMAT 'YYYY DDD');
EXPR$0
2019-12-31 00:00:00
!ok

!if (false) {
### disabled until Bug.CALCITE_6269_FIXED ###
select cast('2019 366' as timestamp FORMAT 'YYYY DDD');
EXPR$0
NULL
!ok
!}

# Test "day in year" token in a leap year scenario
select cast('2000 60' as timestamp FORMAT 'YYYY DDD');
EXPR$0
2000-02-29 00:00:00
!ok

select cast('2000 61' as timestamp FORMAT 'YYYY DDD');
EXPR$0
2000-03-01 00:00:00
!ok

select cast('2000 366' as timestamp FORMAT 'YYYY DDD');
EXPR$0
2000-12-31 00:00:00
!ok

!if (false) {
### disabled until Bug.CALCITE_6269_FIXED ###
select cast('2000 367' as timestamp FORMAT 'YYYY DDD');
EXPR$0
NULL
!ok

# Test "day in year" token without separators
select cast('20190011120' as timestamp
    FORMAT 'YYYYDDDHH12MI');
EXPR$0
2019-01-01 11:20:00
!ok
!}

# Timestamp to string formatting
select cast(cast('2019-01-01' as timestamp) as varchar
    format'DDD');
EXPR$0
001
!ok

select cast(cast('2019-12-31' as timestamp) as varchar
    format'DDD');
EXPR$0
365
!ok

select cast(cast('2000-12-31' as timestamp) as varchar
    format'DDD');
EXPR$0
366
!ok

select cast(cast('2019 123' as timestamp
    format 'YYYY DDD') as varchar format'DDD');
EXPR$0
123
!ok

!if (false) {
### disabled until Bug.CALCITE_6269_FIXED ###
### day_name #################################################
#
# String to datetime: Test different lowercase vs uppercase scenarios.
select cast('2010-08-Tuesday' as timestamp FORMAT 'IYYY-IW-DAY'),
           cast('2010-monday-08' as timestamp FORMAT 'IYYY-DAY-IW'),
           cast('2010-Wednesday-08' as date FORMAT 'IYYY-DAY-IW'),
           cast('2010 08 THURSDAY' as timestamp FORMAT 'IYYY IW DAY'),
           cast('2010 08 Friday' as date FORMAT 'IYYY IW DAY'),
           cast('2010 08 saturday' as timestamp FORMAT 'IYYY IW DAY'),
           cast('sUnDay 2010 08' as date FORMAT 'DAY IYYY IW'),
           cast('Monday 2010 09' as date FORMAT 'DAY IYYY IW');
EXPR$0, EXPR$1, EXPR$2, EXPR$3, EXPR$4, EXPR$5, EXPR$6, EXPR$7
2010-02-23 00:00:00, 2010-02-22 00:00:00, 2010-02-24, 2010-02-25 00:00:00, 2010-02-26, 2010-02-27 00:00:00, 2010-02-28, 2010-03-01
!ok

# And now with short day names.
select cast('2010-08-Tue' as timestamp FORMAT 'IYYY-IW-DY'),
           cast('2010-mon-08' as timestamp FORMAT 'IYYY-DY-IW'),
           cast('2010-Wed-08' as date FORMAT 'IYYY-DY-IW'),
           cast('2010 08 THU' as timestamp FORMAT 'IYYY IW DY'),
           cast('2010 08 Fri' as date FORMAT 'IYYY IW DY'),
           cast('2010 08 sat' as timestamp FORMAT 'IYYY IW DY'),
           cast('sUn 2010 08' as date FORMAT 'DY IYYY IW'),
           cast('Mon 2010 09' as date FORMAT 'DY IYYY IW');
EXPR$0, EXPR$1, EXPR$2, EXPR$3, EXPR$4, EXPR$5, EXPR$6, EXPR$7
2010-02-23 00:00:00, 2010-02-22 00:00:00, 2010-02-24, 2010-02-25 00:00:00, 2010-02-26, 2010-02-27 00:00:00, 2010-02-28, 2010-03-01
!ok

# String to datetime: Incorrect day name.
select cast('2010 09 Mondau' as timestamp FORMAT
    'IYYY IW DAY');
EXPR$0
NULL
!ok

# String to datetime: DAY token without surrounding separators.
select cast('2010MONDAY09' as date FORMAT 'IYYYDAYIW'),
           cast('2010WEDNESDAY9' as timestamp FORMAT 'IYYYDAYIW');
EXPR$0, EXPR$1
2010-03-01, 2010-03-03 00:00:00
!ok
# And now with short day names.
select cast('2010MON09' as date FORMAT 'IYYYDYIW'),
           cast('2010WED9' as timestamp FORMAT 'IYYYDYIW');
EXPR$0, EXPR$1
2010-03-01, 2010-03-03 00:00:00
!ok

# String to datetime: FX and FM modifiers.
select cast('2010-Monday-09' as timestamp FORMAT 'FXIYYY-DAY-IW'),
           cast('2010-Monday  X-09' as timestamp FORMAT 'FXIYYY-DAY-IW');
EXPR$0, EXPR$1
NULL, NULL
!ok

select cast('2010-Monday   -09' as timestamp FORMAT 'FXIYYY-DAY-IW'),
           cast('2010-Monday   09' as date FORMAT 'FXIYYY-DAYIW');
EXPR$0, EXPR$1
2010-03-01 00:00:00, 2010-03-01
!ok

select cast('2010-Monday-09' as timestamp FORMAT 'FXIYYY-FMDAY-IW'),
           cast('2010-Monday09' as timestamp FORMAT 'FXIYYY-FMDAYIW'),
           cast('2010Monday09' as date FORMAT 'FXIYYYFMDAYIW');
EXPR$0, EXPR$1, EXPR$2
2010-03-01 00:00:00, 2010-03-01 00:00:00, 2010-03-01
!ok

# Datetime to string: Different lowercase and uppercase scenarios.
select cast(date'2019-11-13' as varchar
    format 'DAY Day day DY Dy dy');
EXPR$0
WEDNESDAY Wednesday wednesday WED Wed wed
!ok

select cast(cast('2019-11-14' as timestamp) as varchar
    format 'DAY Day day DY Dy dy');
EXPR$0
THURSDAY  Thursday  thursday  THU Thu thu
!ok

select cast(date'2019-11-15' as varchar
    format 'DAY Day day DY Dy dy');
EXPR$0
FRIDAY    Friday    friday    FRI Fri fri
!ok

select cast(cast('2019-11-16' as timestamp) as varchar
    format 'DAY Day day DY Dy dy');
EXPR$0
SATURDAY  Saturday  saturday  SAT Sat sat
!ok

select cast(date'2019-11-17' as varchar
    format 'DAY Day day DY Dy dy');
EXPR$0
SUNDAY    Sunday    sunday    SUN Sun sun
!ok

select cast(cast('2019-11-18' as timestamp) as varchar
    format 'DAY Day day DY Dy dy');
EXPR$0
MONDAY    Monday    monday    MON Mon mon
!ok

select cast(date'2019-11-19' as varchar
    format 'DAY Day day DY Dy dy');
EXPR$0
TUESDAY   Tuesday   tuesday   TUE Tue tue
!ok

# Datetime to string: Different lowercase and uppercase scenarios when FM is provided.
select cast(cast('2019-11-13' as timestamp) as varchar
    format 'FMDAY FMDay FMday FMDY FMDy FMdy');
EXPR$0
WEDNESDAY Wednesday wednesday WED Wed wed
!ok

select cast(date'2019-11-14' as varchar
    format 'FMDAY FMDay FMday FMDY FMDy FMdy');
EXPR$0
THURSDAY Thursday thursday THU Thu thu
!ok

select cast(cast('2019-11-15' as timestamp) as varchar
    format 'FMDAY FMDay FMday FMDY FMDy FMdy');
EXPR$0
FRIDAY Friday friday FRI Fri fri
!ok

select cast(date'2019-11-16' as varchar
    format 'FMDAY FMDay FMday FMDY FMDy FMdy');
EXPR$0
SATURDAY Saturday saturday SAT Sat sat
!ok

select cast(cast('2019-11-17' as timestamp) as varchar
    format 'FMDAY FMDay FMday FMDY FMDy FMdy');
EXPR$0
SUNDAY Sunday sunday SUN Sun sun
!ok

select cast(date'2019-11-18' as varchar
    format 'FMDAY FMDay FMday FMDY FMDy FMdy');
EXPR$0
MONDAY Monday monday MON Mon mon
!ok

select cast(cast('2019-11-19' as timestamp) as varchar
    format 'FMDAY FMDay FMday FMDY FMDy FMdy');
EXPR$0
TUESDAY Tuesday tuesday TUE Tue tue
!ok

# Datetime to string: Test odd casing of day token.
select cast(date'2010-01-20' as varchar FORMAT
    'DAy dAY daY dY');
EXPR$0
WEDNESDAY wednesday wednesday wed
!ok

# Datetime to string: Day token without surrounding separators.
select cast(date'2019-11-11' as varchar
    format 'YYYYDayMonth');
EXPR$0
2019Monday   November
!ok

select cast(cast('2019-11-12' as timestamp) as varchar
    format 'YYYYDYDD');
EXPR$0
2019TUE12
!ok

select cast(date'2019-11-11' as varchar
    format 'YYYYDayMonth');
EXPR$0
2019Monday   November
!ok

select cast(cast('2019-11-12' as timestamp) as varchar
    format 'YYYYDYDD');
EXPR$0
2019TUE12
!ok

# Datetime to string: Day token with FM and FX modifiers.
select cast(cast('2019-01-01' as timestamp) as varchar
    format 'FXYYYY DAY DD');
EXPR$0
2019 TUESDAY   01
!ok

select cast(date'2019-01-01' as varchar
    format 'FXYYYY FMDAY DD');
EXPR$0
2019 TUESDAY 01
!ok

select cast(cast('2019-02-02' as timestamp) as varchar
    format 'FXYYYY DY DD');
EXPR$0
2019 SAT 02
!ok

select cast(date'2019-02-02' as varchar
    format 'FXYYYY FMDY DD');
EXPR$0
2019 SAT 02
!ok
!}

!if (false) {
### disabled until Bug.CALCITE_6269_FIXED ###
### second_of_day #################################################
#
# Check boundaries
select cast('2019-11-10 86399.11' as
    timestamp FORMAT 'YYYY-MM-DD SSSSS.FF2');
EXPR$0
2019-11-10 23:59:59.110000000
!ok

select cast('2019-11-10 0' as
    timestamp FORMAT 'YYYY-MM-DD SSSSS');
EXPR$0
2019-11-10 00:00:00
!ok

# Without separators full 5-digit "second of day" has to be given
select cast('11-10 036612019' as
    timestamp FORMAT 'MM-DD SSSSSYYYY');
EXPR$0
2019-11-10 01:01:01
!ok

# Check timezone offsets with "second of day"
select cast('2019-11-10 036611010' as
    timestamp FORMAT 'YYYY-MM-DD SSSSSTZHTZM');
EXPR$0
2019-11-10 01:01:01
!ok

# Timestamp to string formatting
select cast(cast('2019-01-01 01:01:01' as timestamp)
    as varchar format 'SSSSS');
EXPR$0
03661
!ok

select cast(cast('2019-01-01' as timestamp) as varchar
    format 'SSSSS');
EXPR$0
00000
!ok

select cast(cast('2019-01-01 23:59:59' as timestamp)
    as varchar format 'SSSSS');
EXPR$0
86399
!ok
!}

### day_of_week #################################################
#
# Sunday is 1
select cast(cast('2019-11-03' as timestamp) as varchar
    FORMAT 'D');
EXPR$0
1
!ok

select cast(cast('2019-11-03' as date) as varchar
    FORMAT 'D');
EXPR$0
1
!ok

# Wednesday is 4
select cast(cast('2019-11-06' as timestamp) as varchar
    FORMAT 'D');
EXPR$0
4
!ok

select cast(cast('2019-11-06' as date) as varchar
    FORMAT 'D');
EXPR$0
4
!ok

# Saturday is 7
select cast(cast('2019-11-09' as timestamp) as varchar
    FORMAT 'D');
EXPR$0
7
!ok

select cast(cast('2019-11-09' as date) as varchar
    FORMAT 'D');
EXPR$0
7
!ok

!if (false) {
### disabled until Bug.CALCITE_6269_FIXED ###
# FX and FM modifier does not pad day of week values with zeros.
select cast(cast('2019-12-01' as date) as varchar
    FORMAT 'FXD');
EXPR$0
1
!ok

select cast(cast('2019-12-02' as date) as varchar
    FORMAT 'FXFMD');
EXPR$0
2
!ok
!}

!if (false) {
### disabled until Bug.CALCITE_6269_FIXED ###
### fraction_seconds #################################################
#
select cast('2019-11-08 123456789' as
    timestamp FORMAT 'YYYY-MM-DD FF9');
EXPR$0
2019-11-08 00:00:00.123456789
!ok

select cast('2019-11-08 1' as
    timestamp FORMAT 'YYYY-MM-DD FF');
EXPR$0
2019-11-08 00:00:00.100000000
!ok

select cast('2019-11-08 1234567890' as
    timestamp FORMAT 'YYYY-MM-DD FF');
EXPR$0
NULL
!ok

select cast('2019-11-08' as
    timestamp FORMAT 'YYYY-MM-DD FF');
EXPR$0
NULL
!ok

# TODO: expand the following code
#  self.run_fraction_test(1)
#  self.run_fraction_test(2)
#  self.run_fraction_test(3)
#  self.run_fraction_test(4)
#  self.run_fraction_test(5)
#  self.run_fraction_test(6)
#  self.run_fraction_test(7)
#  self.run_fraction_test(8)
#  self.run_fraction_test(9)
#
#  def run_fraction_test(self, length):
#    MAX_LENGTH = 9
#    fraction_part = ""
#    for x in range(length):
#      fraction_part += str(x + 1)
#    template_input = "select cast('2019-11-08 %s' as timestamp FORMAT 'YYYY-MM-DD FF%s')"
#    input_str = template_input % (fraction_part, length)
#
#    expected = "2019-11-08 00:00:00." + fraction_part + ("0" * (MAX_LENGTH - length))
#    result = self.execute_query(input_str)
#    assert result.data == [expected]
#
#    input2_str = template_input % (fraction_part + str(length + 1), length)
#    result = self.execute_query(input2_str)
#    assert result.data == ["NULL"]
!}

!if (false) {
### disabled until Bug.CALCITE_6269_FIXED ###
### meridiem_indicator #################################################
#
# Check 12 hour diff between AM and PM
select cast('2017-05-03 08 AM' as
    timestamp FORMAT 'YYYY-MM-DD HH12 AM');
EXPR$0
2017-05-03 08:00:00
!ok

select cast('2017-05-04 08 PM' as
    timestamp FORMAT 'YYYY-MM-DD HH12 PM');
EXPR$0
2017-05-04 20:00:00
!ok

# Check that any meridiem indicator in the pattern matches any meridiem indicator in
# the input
select cast('2017-05-05 12AM' as
    timestamp FORMAT 'YYYY-MM-DD HH12PM');
EXPR$0
2017-05-05 00:00:00
!ok

select cast('2017-05-06 P.M.12' as
    timestamp FORMAT 'YYYY-MM-DD AMHH12');
EXPR$0
2017-05-06 12:00:00
!ok

select cast('2017-05-07 PM 01' as
    timestamp FORMAT 'YYYY-MM-DD A.M. HH12');
EXPR$0
2017-05-07 13:00:00
!ok

# Test lowercase indicator in input
select cast('2017-05-08 pm09' as
    timestamp FORMAT 'YYYY-MM-DD P.M.HH12');
EXPR$0
2017-05-08 21:00:00
!ok

select cast('2017-05-09 10a.m.' as
    timestamp FORMAT 'YYYY-MM-DD HH12PM');
EXPR$0
2017-05-09 10:00:00
!ok

# Test that '.' in indicator doesn't conflict with '.' as separator
select cast('2017-05-11 9.AM.10' as
    timestamp FORMAT 'YYYY-MM-DD HH12.P.M..MI');
EXPR$0
2017-05-11 09:10:00
!ok

select cast('2017-05-10.P.M..10' as
    timestamp FORMAT 'YYYY-MM-DD.AM.HH12');
EXPR$0
2017-05-10 22:00:00
!ok

# Timestamp to string formatting
select cast(cast('2019-01-01 00:15:10' as timestamp)
    as varchar format 'HH12 P.M.');
EXPR$0
12 A.M.
!ok

select cast(cast('2019-01-01 12:15:10' as timestamp)
    as varchar format 'HH12 AM');
EXPR$0
12 PM
!ok

select cast(cast('2019-01-01 13:15:10' as timestamp)
    as varchar format 'HH12 a.m.');
EXPR$0
01 p.m.
!ok

select cast(cast('2019-01-01 23:15:10' as timestamp)
    as varchar format 'HH12 p.m.');
EXPR$0
11 p.m.
!ok
!}

!if (false) {
### disabled until Bug.CALCITE_6269_FIXED ###
### timezone_offsets #################################################
#
# Test positive timezone offset.
select cast('2018-01-01 10:00 AM +15:59' as
    timestamp FORMAT 'YYYY-MM-DD HH12:MI A.M. TZH:TZM');
EXPR$0
2018-01-01 10:00:00
!ok

# Test negative timezone offset.
select cast('2018-12-31 08:00 PM -15:59' as
    timestamp FORMAT 'YYYY-MM-DD HH12:MI A.M. TZH:TZM');
EXPR$0
2018-12-31 20:00:00
!ok

# Minus sign before TZM.
select cast('2018-12-31 08:00 AM 01:-59' as
    timestamp FORMAT 'YYYY-MM-DD HH12:MI A.M. TZH:TZM');
EXPR$0
2018-12-31 08:00:00
!ok

# Minus sign right before one digit TZH.
select cast('2018-12-31 08:00 AM--1:10' as
    timestamp FORMAT 'YYYY-MM-DD HH12:MI A.M. TZH:TZM');
EXPR$0
2018-12-31 08:00:00
!ok

select cast('2018-12-31 08:00 AM-5:00' as
    timestamp FORMAT 'YYYY-MM-DD HH12:MI A.M.TZH:TZM');
EXPR$0
2018-12-31 08:00:00
!ok

select cast('2018-12-31 08:00 AM-+1:10' as
    timestamp FORMAT 'YYYY-MM-DD HH12:MI A.M. TZH:TZM');
EXPR$0
2018-12-31 08:00:00
!ok

# Invalid TZH and TZM
select cast('2016-01-01 10:00 AM +16:00' as
    timestamp FORMAT 'YYYY-MM-DD HH12:MI A.M. TZH:TZM');
EXPR$0
NULL
!ok

select cast('2016-01-01 11:00 AM -16:00' as
    timestamp FORMAT 'YYYY-MM-DD HH12:MI A.M. TZH:TZM');
EXPR$0
NULL
!ok

select cast('2016-01-01 10:00 AM 16:00' as
    timestamp FORMAT 'YYYY-MM-DD HH12:MI A.M. TZH:TZM');
EXPR$0
NULL
!ok

select cast('2016-01-01 10:00 AM +15:60' as
    timestamp FORMAT 'YYYY-MM-DD HH12:MI A.M. TZH:TZM');
EXPR$0
NULL
!ok

# One digit negative TZH at the end of the input string.
select cast('2018-12-31 12:01 -1' as timestamp
    FORMAT 'YYYY-MM-DD HH24:MI TZH');
EXPR$0
2018-12-31 12:01:00
!ok

# Test timezone offset parsing without separators
select cast('201812310800AM+0515' as
    timestamp FORMAT 'YYYYMMDDHH12MIA.M.TZHTZM');
EXPR$0
2018-12-31 08:00:00
!ok

select cast('201812310800AM0515' as
    timestamp FORMAT 'YYYYMMDDHH12MIA.M.TZHTZM');
EXPR$0
2018-12-31 08:00:00
!ok

select cast('201812310800AM-0515' as
    timestamp FORMAT 'YYYYMMDDHH12MIA.M.TZHTZM');
EXPR$0
2018-12-31 08:00:00
!ok

# Test signed zero TZH with not null TZM
select cast('2018-01-01 10:00 AM +00:59' as
    timestamp FORMAT 'YYYY-MM-DD HH12:MI A.M. TZH:TZM');
EXPR$0
2018-01-01 10:00:00
!ok

select cast('2018-01-01 10:00 AM -00:59' as
    timestamp FORMAT 'YYYY-MM-DD HH12:MI A.M. TZH:TZM');
EXPR$0
2018-01-01 10:00:00
!ok

# Shuffle TZH and TZM into other elements
select cast('2018-01-01 15 10:00 1 AM' as
    timestamp FORMAT 'YYYY-MM-DD TZM HH12:MI TZH A.M.');
EXPR$0
2018-01-01 10:00:00
!ok

select cast('2018-01-011510:00-01AM' as
    timestamp FORMAT 'YYYY-MM-DDTZMHH12:MITZHA.M.');
EXPR$0
2018-01-01 10:00:00
!ok

# Timezone offset with default time
select cast('2018-01-01 01:30' as timestamp
    FORMAT 'YYYY-MM-DD TZH:TZM');
EXPR$0
2018-01-01 00:00:00
!ok

# Single minus sign before two digit TZH.
select cast('2018-09-11 15:30:10-10' as timestamp
    FORMAT 'YYYY-MM-DD HH24:MI:SS-TZH');
EXPR$0
2018-09-11 15:30:10
!ok

# Non-digit TZH and TZM.
select cast('2018-09-11 17:30:10 ab:10' as timestamp
    FORMAT 'YYYY-MM-DD HH24:MI:SS TZH:TZM');
EXPR$0
NULL
!ok

select cast('2018-09-11 17:30:10 -ab:10' as timestamp
    FORMAT 'YYYY-MM-DD HH24:MI:SS TZH:TZM');
EXPR$0
NULL
!ok

select cast('2018-09-11 17:30:10 +ab:10' as timestamp
    FORMAT 'YYYY-MM-DD HH24:MI:SS TZH:TZM');
EXPR$0
NULL
!ok

select cast('2018-09-11 18:30:10 10:ab' as timestamp
    FORMAT 'YYYY-MM-DD HH24:MI:SS TZH:TZM');
EXPR$0
NULL
!ok
!}

!if (false) {
### disabled until Bug.CALCITE_6269_FIXED ###
### text_token #################################################
#
# Parse ISO:8601 tokens using the text token.
select cast('1985-11-19T01:02:03Z' as timestamp
    format 'YYYY-MM-DD"T"HH24:MI:SS"Z"');
EXPR$0
1985-11-19 01:02:03
!ok

# Free text at the end of the input
select cast('1985-11-19text' as timestamp
    format 'YYYY-MM-DD"text"');
EXPR$0
1985-11-19 00:00:00
!ok

# Free text at the beginning of the input
select cast('19801985-11-20' as timestamp
    format '"1980"YYYY-MM-DD');
EXPR$0
1985-11-20 00:00:00
!ok

# Empty text in format
select cast('1985-11-21' as timestamp
    format '""YYYY""-""MM""-""DD""');
EXPR$0
1985-11-21 00:00:00
!ok

select cast('1985-11-22' as timestamp
    format 'YYYY-MM-DD""""""');
EXPR$0
1985-11-22 00:00:00
!ok

select cast('1985-12-09-' as timestamp
    format 'YYYY-MM-DD-""');
EXPR$0
1985-12-09 00:00:00
!ok

select cast('1985-12-10-' as date
    format 'FXYYYY-MM-DD-""');
EXPR$0
1985-12-10
!ok

select cast('1985-11-23' as timestamp
    format 'YYYY-MM-DD""""""HH24');
EXPR$0
NULL
!ok

# Text in input doesn't match with the text in format
select cast('1985-11-24Z01:02:03Z' as timestamp
    format 'YYYY-MM-DD"T"HH24:MI:SS"Z"');
EXPR$0
NULL
!ok

select cast('1985-11-24T01:02:04T' as timestamp
    format 'YYYY-MM-DD"T"HH24:MI:SS"Z"');
EXPR$0
NULL
!ok

select cast('1985-11-2401:02:05Z' as timestamp
    format 'YYYY-MM-DD"T"HH24:MI:SS"Z"');
EXPR$0
NULL
!ok

select cast('1985-11-24T01:02:06' as timestamp
    format 'YYYY-MM-DD"T"HH24:MI:SS"Z"');
EXPR$0
NULL
!ok

select cast('1985-11-24 01:02:07te' as timestamp
    format 'YYYY-MM-DD HH24:MI:SS"text"');
EXPR$0
NULL
!ok

select cast('1985-11-24 01:02:08text' as
    timestamp format 'YYYY-MM-DD HH24:MI:SS"te"');
EXPR$0
NULL
!ok

# Consecutive text tokens
select cast('1985-11text1text2-25' as timestamp
    format 'YYYY-MM"text1""text2"-DD');
EXPR$0
1985-11-25 00:00:00
!ok

# Separators in text token
select cast("1985-11 -'./,:-25" as date
    format "YYYY-MM\" -'./,:\"-DD");
EXPR$0
1985-11-25
!ok

# Known limitation: If a text token containing separator characters at the beginning
# is right after a separator token sequence then parsing can't find where to stop when
# parsing the consecutive separators. Use FX modifier in this case for strict
# matching.
select cast("1986-11'25" as date
    format "YYYY-MM\"'\"DD");
EXPR$0
1986-11-25
!ok

select cast("1986-11-'25" as timestamp
    format "YYYY-MM-\"'\"DD");
EXPR$0
NULL
!ok

select cast("1986-10-'25" as timestamp
    format "FXYYYY-MM-\"'\"DD");
EXPR$0
1986-10-25 00:00:00
!ok

# Escaped quotation mark is in the text token.
select cast('1985-11a"b26' as timestamp
    format 'YYYY-MM"a\"b"DD');
EXPR$0
1985-11-26 00:00:00
!ok

# Format part is surrounded by double quotes so the quotes indicating the start and
# end of the text token has to be escaped.
select cast("year: 1985, month: 11, day: 27" as date
    r''' format "\"year: \"YYYY\", month: \"MM\", day: \"DD")'';
EXPR$0
1985-11-27
!ok

# Scenario when there is an escaped double quote inside a text token that is itself
# surrounded by escaped double quotes.
select cast("1985 some \"text 11-28" as date
     format "YYYY\" some \\\"text \"MM-DD");
EXPR$0
1985-11-28
!ok

# When format is surrounded by single quotes and there is a single quote inside the
# text token that has to be escaped.
select cast("1985 some 'text 11-29" as date
     format 'YYYY" some \'text "MM-DD');
EXPR$0
1985-11-29
!ok
select cast("1985 some 'text 11-29" as timestamp
     format 'YYYY" some \'text "MM-DD');
EXPR$0
1985-11-29 00:00:00
!ok

# Datetime to string path: Simple text token.
select cast(cast("1985-11-30" as date) as varchar
    format "YYYY-\"text\"MM-DD");
EXPR$0
1985-text11-30
!ok

# Datetime to string path: Consecutive text tokens.
select cast(cast("1985-12-01" as date) as varchar
    format "YYYY-\"text1\"\"text2\"MM-DD");
EXPR$0
1985-text1text212-01
!ok
select cast(cast("1985-12-01" as timestamp) as
    string format "YYYY-\"text1\"\"text2\"MM-DD");
EXPR$0
1985-text1text212-01
!ok

# Datetime to string path: Text token containing separators.
select cast(cast("1985-12-02" as date) as
    string format "YYYY-\" -'./,:\"MM-DD");
EXPR$0
1985- -'./,:12-02
!ok
select cast(cast("1985-12-02" as timestamp) as
    string format "YYYY-\" -'./,:\"MM-DD");
EXPR$0
1985- -'./,:12-02
!ok

# Datetime to string path: Text token containing a double quote.
select cast(cast('1985-12-03' as date) as varchar
    format 'YYYY-"some \"text"MM-DD');
EXPR$0
1985-some "text12-03
!ok
select cast(cast('1985-12-03' as timestamp) as
    string format 'YYYY-"some \"text"MM-DD');
EXPR$0
1985-some "text12-03
!ok

# Datetime to string path: Text token containing a double quote where the text token
# itself is covered by escaped double quotes.
select cast(cast("1985-12-04" as date) as varchar
    format "YYYY-\"some \\\"text\"MM-DD");
EXPR$0
1985-some "text12-04
!ok
select cast(cast("1985-12-04" as timestamp) as
    string format "YYYY-\"some \\\"text\"MM-DD");
EXPR$0
1985-some "text12-04
!ok
!}

!if (false) {
### disabled until Bug.CALCITE_6269_FIXED ###
# Backslash in format that escapes non-special chars.
select cast("1985- some \ text12-05" as date
    format 'YYYY-"some \ text"MM-DD');
EXPR$0
1985-12-05
!ok
select cast(cast("1985-12-06" as date) as varchar
    format 'YYYY-"some \ text"MM-DD');
EXPR$0
1985-some  text12-06
!ok

select cast("1985-some text12-07" as date
    format 'YYYY-"\some text"MM-DD');
EXPR$0
1985-12-07
!ok
select cast(cast("1985-12-08" as date) as varchar
    format 'YYYY-"\some text"MM-DD');
EXPR$0
1985-some text12-08
!ok

# Backslash in format that escapes special chars.
select cast("1985-\b\n\r\t12-09" as
    date format 'YYYY-"\b\n\r\t"MM-DD');
EXPR$0
1985-12-09
!ok
# lint:skip 25 (tab characters)
select cast(cast("1985-12-10" as date) as varchar
    format 'YYYY"\ttext\n"MM-DD');
EXPR$0
1985	text
12-10
!ok
select cast(cast("1985-12-11" as date) as varchar
    format "YYYY\"\ttext\n\"MM-DD");
EXPR$0
1985	text
12-11
!ok
select cast(cast("1985-12-12" as timestamp) as
    string format 'YYYY"\ttext\n"MM-DD');
EXPR$0
1985	text
12-12
!ok
select cast(cast("1985-12-13" as timestamp) as
    string format "YYYY\"\ttext\n\"MM-DD");
EXPR$0
1985	text
12-13
!ok

# Escaped backslash in text token.
select cast(cast("1985-12-14" as date) as varchar
    format 'YYYY"some\\text"MM-DD');
EXPR$0
1985some\text12-14
!ok
select cast(cast("1985-12-15" as timestamp) as
    string format 'YYYY"\\"MM"\\"DD');
EXPR$0
1985\12\15
!ok
select cast("1985\\12\\14 01:12:10" as timestamp
    format 'YYYY"\\"MM"\\"DD HH12:MI:SS');
EXPR$0
1985-12-14 01:12:10
!ok
# Known limitation: When the format token is surrounded by escaped quotes then an
# escaped backslash at the end of the token together with the closing double quote is
# taken as a double escaped quote.
select cast(cast("1985-12-16" as timestamp) as varchar format
    "YYYY\"\\\"MM\"\\\"DD");
Bad date/time conversion format
!error

# Free text token where an escaped backslash precedes an escaped single quote.
select cast("2010-\\'-02-01" as date format
     'FXYYYY-"\\\'"-MM-DD') ;
EXPR$0
2010-02-01
!ok

# Test error message where format contains text token with escaped double quote.
select cast('1985-AB"CD11-23' as date format 'YYYY-"AB\"C"MM-DD');
String to Date parse failed. Input '1985-AB"CD11-23' doesn't match with format 'YYYY-"AB\"C"MM-DD'
!error
!}

!if (false) {
### disabled until Bug.CALCITE_6269_FIXED ###
### iso8601_week_based_date_tokens #################################################
#
# Format 0001-01-01 and 9999-12-31 dates.
# 0001-01-01 is Monday, belongs to the 1st week of year 1.
# 9999-12-31 is Friday, belongs to the 52nd week of year 9999.
select cast(date'0001-01-01' as varchar format 'IYYY/IW/ID'),
           cast(date'9999-12-31' as varchar format 'IYYY/IW/ID');
EXPR$0, EXPR$1
0001/01/01, 9999/52/05
!ok

# Parse 0001-01-01 and 9999-12-31 dates.
select cast('0001/01/01' as date format 'IYYY/IW/ID'),
           cast('9999/52/05' as date format 'IYYY/IW/ID');
EXPR$0, EXPR$1
0001-01-01, 9999-12-31
!ok

# Parse out-of-range dates.
# Year 9999 has 52 weeks. 9999-12-31 is Friday.
select cast('9999/52/06' as date format 'IYYY/IW/ID');
String to Date parse failed. Input '9999/52/06' doesn't match with format 'IYYY/IW/ID'
!error
select cast('9999/53/01' as date format 'IYYY/IW/ID');
String to Date parse failed. Input '9999/53/01' doesn't match with format 'IYYY/IW/ID'
!error

# Format 1400-01-01 and 9999-12-31 timestamps.
# 1400-01-01 is Wednesday, belongs to the 1st week of year 1400.
# 9999-12-31 is Friday, belongs to the 52nd week of year 9999.
select cast(cast('1400-01-01' as timestamp) as varchar format 'IYYY/IW/ID'),
           cast(cast('9999-12-31' as timestamp) as varchar format 'IYYY/IW/ID');
EXPR$0, EXPR$1
1400/01/03, 9999/52/05
!ok

# Parse 1400-01-01 and 9999-12-31 timestamps.
select cast('1400/01/03' as timestamp format 'IYYY/IW/ID'),
           cast('9999/52/05' as timestamp format 'IYYY/IW/ID');
EXPR$0, EXPR$1
1400-01-01 00:00:00, 9999-12-31 00:00:00
!ok

# Parse out-of-range timestamps.
# - Tuesday of the 1st week of year 1400 is 1399-12-31, which is out of the valid
# timestamp range.
# - Year 9999 has 52 weeks. 9999-12-31 is Friday.
select cast('1400/01/02' as timestamp format 'IYYY/IW/ID'),
           cast('9999/52/06' as timestamp format 'IYYY/IW/ID'),
           cast('9999/53/01' as timestamp format 'IYYY/IW/ID');
EXPR$0, EXPR$1, EXPR$2
NULL, NULL, NULL
!ok

# Formatting dates arond Dec 31.
# 2019-12-31 is Tuesday, belongs to 1st week of year 2020.
# 2020-12-31 is Thursday, belongs to 53rd week of year 2020.
select cast(date'2019-12-29' as varchar format 'IYYY/IW/ID'),
           cast(date'2019-12-30' as varchar format 'IYYY/IW/ID'),
           cast(date'2019-12-31' as varchar format 'IYYY/IW/ID'),
           cast(date'2020-01-01' as varchar format 'IYYY/IW/ID'),
           cast(date'2020-12-31' as varchar format 'IYYY/IW/ID'),
           cast(date'2021-01-01' as varchar format 'IYYY/IW/ID');
EXPR$0, EXPR$1, EXPR$2, EXPR$3, EXPR$4, EXPR$5
2019/52/07, 2020/01/01, 2020/01/02, 2020/01/03, 2020/53/04, 2020/53/05
!ok

# Parsing dates around Dec 31.
select cast('2019/52/07' as date format 'IYYY/IW/ID'),
           cast('2020/01/01' as date format 'IYYY/IW/ID'),
           cast('2020/01/02' as date format 'IYYY/IW/ID'),
           cast('2020/01/03' as date format 'IYYY/IW/ID'),
           cast('2020/53/04' as date format 'IYYY/IW/ID'),
           cast('2020/53/05' as date format 'IYYY/IW/ID');
EXPR$0, EXPR$1, EXPR$2, EXPR$3, EXPR$4, EXPR$5
2019-12-29, 2019-12-30, 2019-12-31, 2020-01-01, 2020-12-31, 2021-01-01
!ok

select cast('2019/53/01' as date format 'IYYY/IW/ID');
String to Date parse failed. Input '2019/53/01' doesn't match with format 'IYYY/IW/ID'
!error

# Format 4, 3, 2, 1-digit week numbering year.
# 2020-01-01 is Wednesday, belongs to week 1 of year 2020.
!set now_string '2019-01-01 11:11:11'

select cast(date'2020-01-01' as varchar format 'IYYY/IW/ID'),
           cast(date'2020-01-01' as varchar format 'IYY/IW/ID'),
           cast(date'2020-01-01' as varchar format 'IY/IW/ID'),
           cast(date'2020-01-01' as varchar format 'I/IW/ID');
EXPR$0, EXPR$1, EXPR$2, EXPR$3
2020/01/03, 020/01/03, 20/01/03, 0/01/03
!ok

# Parse 4, 3, 2, 1-digit week numbering year.
select cast('2020/01/03' as date format 'IYYY/IW/ID'),
           cast('020/01/03' as date format 'IYYY/IW/ID'),
           cast('20/01/03' as date format 'IYYY/IW/ID'),
           cast('0/01/03' as date format 'IYYY/IW/ID'),
           cast('020/01/03' as date format 'IYY/IW/ID'),
           cast('20/01/03' as date format 'IYY/IW/ID'),
           cast('0/01/03' as date format 'IYY/IW/ID'),
           cast('20/01/03' as date format 'IY/IW/ID'),
           cast('0/01/03' as date format 'IY/IW/ID'),
           cast('0/01/03' as date format 'I/IW/ID');
EXPR$0, EXPR$1, EXPR$2, EXPR$3, EXPR$4, EXPR$5, EXPR$6, EXPR$7, EXPR$8, EXPR$9
2020-01-01, 2020-01-01, 2020-01-01, 2010-01-06, 2020-01-01, 2020-01-01, 2010-01-06, 2020-01-01, 2010-01-06, 2010-01-06
!ok

# 2000-01-01 is Saturday, so it belongs to the 1999 ISO 8601 week-numbering year.
# Test that 1999 is used for prefixing 3, 2, 1-digit week numbering year.
!set now_string '2000-01-01 11:11:11'

select cast('2005/01/01' as date format 'IYYY/IW/ID'),
           cast('005/01/01' as date format 'IYYY/IW/ID'),
           cast('05/01/01' as date format 'IYYY/IW/ID'),
           cast('5/01/01' as date format 'IYYY/IW/ID'),
           cast('05/01/01' as date format 'IY/IW/ID'),
           cast('5/01/01' as date format 'IY/IW/ID'),
           cast('5/01/01' as date format 'I/IW/ID');
EXPR$0, EXPR$1, EXPR$2, EXPR$3, EXPR$4, EXPR$5, EXPR$6
2005-01-03, 1004-12-31, 1905-01-02, 1995-01-02, 1905-01-02, 1995-01-02, 1995-01-02
!ok

!set now_string null

# Parse 1-digit week of year and 1-digit week day.
select cast('2020/53/4' as date format 'IYYY/IW/ID'),
           cast('2020/1/3' as date format 'IYYY/IW/ID');
EXPR$0, EXPR$1
2020-12-31, 2020-01-01
!ok

# Parse dayname with week-based tokens
select cast('2020/wed/1' as date format 'IYYY/DY/IW'),
           cast('2020/wed1' as date format 'iyyy/dyiw'),
           cast('2020wed1' as date format 'IYYYDYIW'),
           cast('2020WEd1' as date format 'iyyydyiw'),
           cast('2020/wednesday/1' as date format 'IYYY/DAY/IW'),
           cast('2020/wednesday1' as date format 'iyyy/dayiw'),
           cast('2020wednesday1' as date format 'IYYYDAYIW'),
           cast('2020wEdnESday1' as date format 'iyyydayiw');
EXPR$0, EXPR$1, EXPR$2, EXPR$3, EXPR$4, EXPR$5, EXPR$6, EXPR$7
2020-01-01, 2020-01-01, 2020-01-01, 2020-01-01, 2020-01-01, 2020-01-01, 2020-01-01, 2020-01-01
!ok
!}

!if (false) {
### disabled until Bug.CALCITE_6269_FIXED ###
### fm_fx_modifiers #################################################
#
# Exact mathcing for the whole format.
select cast('2001-03-01 03:10:15.123456 -01:30' as
    timestamp format 'FXYYYY-MM-DD HH12:MI:SS.FF6 TZH:TZM');
EXPR$0
2001-03-01 03:10:15.123456000
!ok

# Strict separator matching.
select cast('2001-03-02 03:10:15' as timestamp format
    'FXYYYY MM-DD HH12:MI:SS');
EXPR$0
NULL
!ok

select cast('2001-03-03 03:10:15' as timestamp format
    'FXYYYY-MM-DD HH12::MI:SS');
EXPR$0
NULL
!ok

select cast('2001-03-04    ' as timestamp format
    'FXYYYY-MM-DD ');
EXPR$0
NULL
!ok

# Strict matching of single quote separator.
select cast('2001\'04-01' as timestamp format
     'FXYYYY\'MM-DD');
EXPR$0
2001-04-01 00:00:00
!ok

select cast("2001'04-02" as date format
     'FXYYYY\'MM-DD');
EXPR$0
2001-04-02
!ok

select cast('2001\'04-03' as timestamp format
     "FXYYYY'MM-DD");
EXPR$0
2001-04-03 00:00:00
!ok

select cast("2001'04-04" as date format
     "FXYYYY'MM-DD");
EXPR$0
2001-04-04
!ok

# Strict token length matching.
select cast('2001-3-05' as timestamp format
    'FXYYYY-MM-DD');
EXPR$0
NULL
!ok

select cast('15-03-06' as timestamp format
    'FXYYYY-MM-DD');
EXPR$0
NULL
!ok

select cast('15-03-07' as date format 'FXYY-MM-DD');
EXPR$0
2015-03-07
!ok

select cast('2001-03-08 03:15:00 AM' as timestamp
    format 'FXYYYY-MM-DD HH12:MI:SS PM');
EXPR$0
2001-03-08 03:15:00
!ok

select cast('2001-03-08 03:15:00 AM' as timestamp
    format 'FXYYYY-MM-DD HH12:MI:SS P.M.');
EXPR$0
NULL
!ok

select cast('2001-03-09 03:15:00.1234' as timestamp
    format 'FXYYYY-MM-DD HH12:MI:SS.FF4');
EXPR$0
2001-03-09 03:15:00.123400000
!ok

select cast('2001-03-09 03:15:00.12345' as timestamp
    format 'FXYYYY-MM-DD HH12:MI:SS.FF4');
EXPR$0
NULL
!ok

select cast('2001-03-09 03:15:00.12345' as timestamp
    format 'FXYYYY-MM-DD HH12:MI:SS.FF');
EXPR$0
NULL
!ok

# Strict week-based token length matching.
select cast('2015/3/05' as timestamp format 'FXIYYY/IW/ID'),
           cast('2015/03/5' as timestamp format 'FXIYYY/IW/ID'),
           cast('015/03/05' as timestamp format 'FXIYYY/IW/ID'),
           cast('15/03/05' as timestamp format 'FXIYYY/IW/ID'),
           cast('5/03/05' as timestamp format 'FXIYYY/IW/ID');
EXPR$0, EXPR$1, EXPR$2, EXPR$3, EXPR$4
NULL, NULL, NULL, NULL, NULL
!ok
select cast('2015/3/05' as date format 'FXIYYY/IW/ID');
String to Date parse failed. Input '2015/3/05' doesn't match with format 'FXIYYY/IW/ID'
!error

!set now_string '2019-01-01 11:11:11'
select cast('2015/03/05' as timestamp format 'FXIYYY/IW/ID'),
           cast('015/03/05' as timestamp format 'FXIYY/IW/ID'),
           cast('15/03/05' as timestamp format 'FXIY/IW/ID'),
           cast('5/03/05' as timestamp format 'FXI/IW/ID'),
           cast('2015/03/05' as date format 'FXIYYY/IW/ID'),
           cast('015/03/05' as date format 'FXIYY/IW/ID'),
           cast('15/03/05' as date format 'FXIY/IW/ID'),
           cast('5/03/05' as date format 'FXI/IW/ID');
EXPR$0, EXPR$1, EXPR$2, EXPR$3, EXPR$4, EXPR$5, EXPR$6, EXPR$7
2015-01-16 00:00:00, 2015-01-16 00:00:00, 2015-01-16 00:00:00, 2015-01-16 00:00:00, 2015-01-16, 2015-01-16, 2015-01-16, 2015-01-16
!ok
!set now_string null

# Strict token length matching with text token containing escaped double quote.
select cast('2001-03-09 some "text03:25:00'
    as timestamp format "FXYYYY-MM-DD \"some \\\"text\"HH12:MI:SS");
EXPR$0
2001-03-09 03:25:00
!ok

# Use FM to ignore FX modifier for some of the tokens.
select cast('2001-03-10 03:15:00.12345' as timestamp
    format 'FXYYYY-MM-DD HH12:MI:SS.FMFF');
EXPR$0
2001-03-10 03:15:00.123450000
!ok

select cast('019-03-10 04:15:00' as timestamp
    format 'FXFMYYYY-MM-DD HH12:MI:SS');
EXPR$0
2019-03-10 04:15:00
!ok

select cast('2004-03-08 03:15:00 AM' as timestamp
    format 'FXYYYY-MM-DD HH12:MI:SS FMP.M.');
EXPR$0
2004-03-08 03:15:00
!ok

# Multiple FM modifiers in a format.
select cast('2001-3-11 3:15:00.12345' as timestamp
    format 'FXYYYY-FMMM-DD FMHH12:MI:SS.FMFF');
EXPR$0
2001-03-11 03:15:00.123450000
!ok

select cast('2001-3-11 3:15:30' as timestamp
    format 'FXYYYY-FMMM-DD FMFMHH12:MI:SS');
EXPR$0
2001-03-11 03:15:30
!ok

# FM modifier effects only the next token.
select cast('2001-3-12 3:1:00.12345' as timestamp
    format 'FXYYYY-FMMM-DD FMHH12:MI:SS.FMFF');
EXPR$0
NULL
!ok

# FM modifier before text token is valid for the text token and not for the token
# right after the text token.
select cast('1999-10text1' as timestamp format
    ''' 'FXYYYY-MMFM"text"DD');
EXPR$0
NULL
!ok

# FM modifier skips the separators and affects the next non-separator token.
select cast('1999-10-2' as timestamp format
    ''' 'FXYYYY-MMFM-DD');
EXPR$0
1999-10-02 00:00:00
!ok

# FM modifier at the end has no effect.
select cast('2001-03-13 03:01:00' as timestamp
    format 'FXYYYY-MM-DD HH12:MI:SSFM');
EXPR$0
2001-03-13 03:01:00
!ok

select cast('2001-03-13 03:01:0' as timestamp
    format 'FXYYYY-MM-DD HH12:MI:SSFM');
EXPR$0
NULL
!ok

# In a datetime to string path FX is the default so it works with FX as it would
# without.
select cast(cast('2001-03-05 03:10:15.123456' as
    timestamp) as varchar format 'FXYYYY-MM-DD HH24:MI:SS.FF7');
EXPR$0
2001-03-05 03:10:15.1234560
!ok

select cast(date'0001-01-10' as varchar format 'FXIYYY-IW-ID'),
           cast(date'0001-10-10' as varchar format 'FXIYYY-IW-ID');
EXPR$0, EXPR$1
0001-02-03, 0001-41-03
!ok

# Datetime to string path: Tokens with FM modifier don't pad output to a given
# length.
select cast(cast('2001-03-14 03:06:08' as timestamp)
    as varchar format 'YYYY-MM-DD FMHH24:FMMI:FMSS');
EXPR$0
2001-03-14 3:6:8
!ok

select cast(cast('0001-03-09' as date)
    as varchar format 'FMYYYY-FMMM-FMDD');
EXPR$0
1-3-9
!ok

select cast(date'0001-03-10' as varchar format
    'FMYY-FMMM-FMDD');
EXPR$0
1-3-10
!ok

select cast(date'0001-01-10' as varchar format 'FMIYYY-FMIW-FMID'),
           cast(date'0001-10-10' as varchar format 'FMIYYY-FMIW-FMID');
EXPR$0, EXPR$1
1-2-3, 1-41-3
!ok

# Datetime to string path: FM modifier is effective even if FX modifier is also
# given.
select cast(cast('2001-03-15 03:06:08' as
    timestamp) as varchar format 'FXYYYY-MM-DD FMHH24:FMMI:FMSS');
EXPR$0
2001-03-15 3:6:8
!ok

select cast(cast('0001-04-09' as date)
    as varchar format 'FXYYYY-FMMM-FMDD');
EXPR$0
0001-4-9
!ok

select cast(cast('0001-04-10' as date)
    as varchar format 'FXFMYYYY-FMMM-FMDD');
EXPR$0
1-4-10
!ok

select cast(date'0001-01-10' as varchar format 'FXFMIYYY-FMIW-FMID'),
           cast(date'0001-10-10' as varchar format 'FXFMIYYY-FMIW-FMID');
EXPR$0, EXPR$1
1-2-3, 1-41-3
!ok

# FX and FM modifiers are case-insensitive.
select cast('2019-5-10' as date format
    'fxYYYY-fmMM-DD');
EXPR$0
2019-05-10
!ok
!}

### quarter #################################################
#
select cast(date'2001-01-01' as varchar
    FORMAT 'YYYY Q MM');
EXPR$0
2001 1 01
!ok

select cast(date'2001-03-31' as varchar
    FORMAT 'YYYY Q MM');
EXPR$0
2001 1 03
!ok

select cast(date'2001-4-1' as varchar
    FORMAT 'YYYY Q MM');
EXPR$0
2001 2 04
!ok

select cast(date'2001-6-30' as varchar
    FORMAT 'YYYY Q MM');
EXPR$0
2001 2 06
!ok

select cast(date'2001-7-1' as varchar
    FORMAT 'YYYY Q MM');
EXPR$0
2001 3 07
!ok

select cast(date'2001-9-30' as varchar
    FORMAT 'YYYY Q MM');
EXPR$0
2001 3 09
!ok

select cast(date'2001-10-1' as varchar
    FORMAT 'YYYY Q MM');
EXPR$0
2001 4 10
!ok

select cast(date'2001-12-31' as varchar
    FORMAT 'YYYY Q MM');
EXPR$0
2001 4 12
!ok

!if (false) {
### disabled until Bug.CALCITE_6269_FIXED ###
### format_parse_errors #################################################
#
# Invalid format
select cast('2017-05-01' as timestamp format 'XXXX-dd-MM');
Bad date/time conversion format: XXXX-dd-MM
!error
!}

# Invalid use of SimpleDateFormat
select cast('2017-05-01 15:10' as timestamp format 'yyyy-MM-dd +hh:mm');
Invalid format: 'yyyy-MM-dd +hh:mm' for datetime string: '2017-05-01 15:10'
!error

# Duplicate format element
select cast('2017-05-01' as timestamp format 'YYYY-MM-DD MM');
Invalid format: 'YYYY-MM-DD MM' for datetime string: '2017-05-01'
!error

select cast('2017-05-01' as timestamp format 'YYYY-MM-DD-YYYY');
Invalid format: 'YYYY-MM-DD-YYYY' for datetime string: '2017-05-01'
!error

# Multiple year tokens provided
select cast('2017-05-01' as timestamp format 'YYYY-MM-DD-YY');
Invalid format: 'YYYY-MM-DD-YY' for datetime string: '2017-05-01'
!error

select cast('2017-05-01' as timestamp format 'YYY-MM-DD-Y');
Invalid format: 'YYY-MM-DD-Y' for datetime string: '2017-05-01'
!error

!if (false) {
# Year and round year conflict
select cast('2017-05-01' as timestamp format 'YY-MM-DD-RRRR');
Both year and round year are provided
!error

select cast('2017-05-01' as timestamp format 'RR-MM-DD-YYY');
Both year and round year are provided
!error
!}

# Quarter token not allowed in a string to datetime conversion.
select cast('2017-1-01' as timestamp format 'YYYY-Q-DDD');
Cannot convert 'Q' FormatElement to Java pattern
!error

# Conflict between MM, MONTH and MON tokens
select cast('2017-05-01' as timestamp format 'YYYY-MM-DD-MONTH');
Invalid format: 'YYYY-MM-DD-MONTH' for datetime string: '2017-05-01'
!error

select cast('2017-05-01' as timestamp format 'YYYY-MM-DD-MON');
Invalid format: 'YYYY-MM-DD-MON' for datetime string: '2017-05-01'
!error

select cast('2017-05-01' as timestamp format 'YYYY-MONTH-DD-MON');
Invalid format: 'YYYY-MONTH-DD-MON' for datetime string: '2017-05-01'
!error

!if (false) {
# Conflict between DAY, DY and ID tokens.
select cast('2017-05-01-Monday' as timestamp format 'IYYY-IW-ID-DAY');
Multiple day of week tokens provided
!error

select cast('2017-05-01-Mon' as timestamp format 'IYYY-IW-ID-DY');
Multiple day of week tokens provided
!error

select cast('2017-05-Monday-Mon' as timestamp format 'IYYY-IW-DAY-DY');
Multiple day of week tokens provided
!error

# Week of year token not allowed in a string to datetime conversion.
select cast('2017-1-01' as timestamp format 'YYYY-WW-DD');
Week number token is not allowed in a string to datetime conversion
!error

# Week of month token not allowed in a string to datetime conversion.
select cast('2017-1-01' as timestamp format 'YYYY-W-DD');
Week number token is not allowed in a string to datetime conversion
!error

# Day of year conflict
select cast('2017-05-01' as timestamp format 'YYYY-MM-DDD');
Day of year provided with day or month token
!error

select cast('2017-05-01' as timestamp format 'YYYY-DD-DDD');
Day of year provided with day or month token
!error

select cast('2017-MAY-01' as timestamp format 'YYYY-MONTH-DDD');
Day of year provided with day or month token
!error

select cast('2017-JUN-01' as timestamp format 'YYYY-MON-DDD');
Day of year provided with day or month token
!error

# Day of week token not allowed in a string to datetime conversion.
select cast('2017-1-02' as timestamp format 'YYYY-D-MM');
Day of week token is not allowed in a string to datetime conversion
!error

# Day name token not allowed in a string to datetime conversion.
select cast('2017-1-02 Monday' as timestamp format 'YYYY-DD-MM DAY');
Day name token is not allowed in a string to datetime conversion except with IYYY|IYY|IY|I and IW tokens
!error
!}

# Conflict between hour tokens
select cast('2017-05-01' as timestamp format 'YYYY-MM-DD HH:HH24');
Invalid format: 'YYYY-MM-DD HH:HH24' for datetime string: '2017-05-01'
!error

select cast('2017-05-01' as timestamp format 'YYYY-MM-DD HH12:HH24');
Invalid format: 'YYYY-MM-DD HH12:HH24' for datetime string: '2017-05-01'
!error

select cast('2017-05-01' as timestamp format 'YYYY-MM-DD HH12:HH');
Invalid format: 'YYYY-MM-DD HH12:HH' for datetime string: '2017-05-01'
!error

!if (false) {
# Conflict with median indicator
select cast('2017-05-01' as timestamp format 'YYYY-MM-DD AM HH:MI A.M.');
Multiple median indicator tokens provided
!error

select cast('2017-05-01' as timestamp format 'YYYY-MM-DD PM HH:MI am');
Multiple median indicator tokens provided
!error

select cast('2017-05-01' as timestamp format 'YYYY-MM-DD HH24:MI a.m.');
Conflict between median indicator and hour token
!error

select cast('2017-05-01' as timestamp format 'YYYY-MM-DD p.m.');
Missing hour token
!error

# Conflict with second of day
select cast('2017-05-01' as timestamp format 'YYYY-MM-DD SSSSS HH');
Second of day token conflicts with other token(s)
!error

select cast('2017-05-01' as timestamp format 'YYYY-MM-DD HH12:SSSSS');
Second of day token conflicts with other token(s)
!error

select cast('2017-05-01' as timestamp format 'YYYY-MM-DD HH24SSSSS');
Second of day token conflicts with other token(s)
!error

select cast('2017-05-01' as timestamp format 'YYYY-MM-DD MI SSSSS');
Second of day token conflicts with other token(s)
!error

select cast('2017-05-01' as timestamp format 'YYYY-MM-DD SS SSSSS');
Second of day token conflicts with other token(s)
!error
!}

# Too long format
# (Format string consists of 's' 101 times)
select cast('2017-05-01' as timestamp
    format 'sssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssss');
Invalid format: 'sssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssss' for datetime string: '2017-05-01'
!error

!if (false) {
# Timezone offsets in a datetime to string formatting
select cast(cast('2017-05-01 01:15' as timestamp format 'YYYY-MM-DD TZH:TZM')
    as varchar format 'TZH');
Timezone offset not allowed in a datetime to string conversion
!error

select cast(cast('2017-05-01 01:15' as timestamp format 'YYYY-MM-DD TZH:TZM')
    as varchar format 'TZM');
Timezone offset not allowed in a datetime to string conversion
!error

select cast(cast('2017-05-01 01:15' as timestamp format 'YYYY-MM-DD TZH:TZM')
    as varchar format 'YYYY-MM-DD HH24:MI:SS TZH:TZM');
Timezone offset not allowed in a datetime to string conversion
!error

# TZM requires TZH
select cast('2018-12-31 08:00 AM 59' as timestamp FORMAT
    'YYYY-MM-DD HH12:MI A.M. TZM');
TZH token is required for TZM
!error

select cast('2018-12-31 08:00 AM -59' as timestamp FORMAT
    'YYYY-MM-DD HH12:MI A.M. TZM');
TZH token is required for TZM
!error

# Multiple fraction second token conflict
select cast('2018-10-10' as timestamp format 'YYYY-MM-DD FF FF1');
Multiple fractional second tokens provided.
!error

select cast('2018-10-10' as timestamp format 'YYYY-MM-DD FF2 FF3');
Multiple fractional second tokens provided.
!error

select cast('2018-10-10' as timestamp format 'YYYY-MM-DD FF4 FF5');
Multiple fractional second tokens provided.
!error

select cast('2018-10-10' as timestamp format 'YYYY-MM-DD FF6 FF7');
Multiple fractional second tokens provided.
!error

select cast('2018-10-10' as timestamp format 'YYYY-MM-DD FF8 FF9');
Multiple fractional second tokens provided.
!error

# No date token
select cast('2020-05-05' as timestamp format 'FF1');
No date tokens provided.
!error

select cast('2020-05-05' as timestamp format 'SSSSS');
No date tokens provided.
!error
!}

select cast('2020-05-05' as timestamp format 'HH:MI:SS');
Invalid format: 'HH:MI:SS' for datetime string: '2020-05-05'
!error

!if (false) {
# ISO 8601 Week-based and normal date pattern tokens must not be mixed.
select cast('2018-10-01' as date format 'IYYY-MM-ID');
ISO 8601 week-based date tokens (i.e. IYYY, IW, ID) are not allowed to be used with regular date tokens.
!error
select cast('2018-10-01 01:00' as timestamp format 'IYYY-MM-ID HH24:MI');
ISO 8601 week-based date tokens (i.e. IYYY, IW, ID) are not allowed to be used with regular date tokens.
!error

select cast('2018-10-01' as date format 'YYYY-IW-DD');
ISO 8601 week-based date tokens (i.e. IYYY, IW, ID) are not allowed to be used with regular date tokens.
!error
select cast('2018-10-01' as timestamp format 'IYYY-IW-DD');
ISO 8601 week-based date tokens (i.e. IYYY, IW, ID) are not allowed to be used with regular date tokens.
!error

# Missing ISO 8601 week-based pattern tokens.
select cast('2018-10' as date format 'IYYY-IW');
One or more required ISO 8601 week-based date tokens (i.e. IYYY, IW, ID) are missing.
!error
select cast('2018-10 01:00' as timestamp format 'IYYY-IW HH24:MI');
One or more required ISO 8601 week-based date tokens (i.e. IYYY, IW, ID) are missing.
!error

select cast('18-07' as date format 'IY-ID');
One or more required ISO 8601 week-based date tokens (i.e. IYYY, IW, ID) are missing.
!error
select cast('18-07 01:00' as timestamp format 'IY-ID HH24:MI');
One or more required ISO 8601 week-based date tokens (i.e. IYYY, IW, ID) are missing.
!error

# ISO 8601 Week numbering year conflict
select cast('2018-018-10-01' as date format 'IYYY-IYY-IW-DD');
Multiple year tokens provided
!error
select cast('2018-018-10-01 01:00' as timestamp format
    'IYYY-IYY-IW-DD HH24:MI');
Multiple year tokens provided
!error

select cast('018-8-10-01' as date format 'IYY-I-IW-DD');
Multiple year tokens provided
!error
select cast('018-8-10-01 01:00' as timestamp format 'IYY-I-IW-DD HH24:MI');
Multiple year tokens provided
!error

# Verify that conflict check is not skipped when format ends with separators.
select cast('2017-05-01' as timestamp format 'YYYY-MM-DD-RR--');
Both year and round year are provided
!error

# Unclosed quotation in text pattern
select cast('1985-11-20text' as timestamp format 'YYYY-MM-DD"text');
Missing closing quotation mark.
!error

select cast('1985-11-21text' as timestamp format 'YYYY-MM-DD\"text"');
Missing closing quotation mark.
!error

select cast(date"1985-12-08" as varchar format 'YYYY-MM-DD \"X"');;
Missing closing quotation mark.
!error

select cast(date"1985-12-09" as varchar format 'YYYY-MM-DD "X');;
Missing closing quotation mark.
!error

# Format containing text token only.
select cast("1985-11-29" as date format '" some text "');
No datetime tokens provided.
!error

select cast(cast("1985-12-02" as date) as varchar format "\"free text\"");
No datetime tokens provided.
!error

# FX modifier not at the begining of the format.
select cast("2001-03-01 00:10:02" as timestamp format
    "YYYY-MM-DD FXHH12:MI:SS");
FX modifier should be at the beginning of the format string.
!error

select cast("2001-03-01 00:10:02" as timestamp format
    "YYYY-MM-DD HH12:MI:SS FX");
FX modifier should be at the beginning of the format string.
!error

select cast(date"2001-03-01" as varchar format "YYYYFX-MM-DD");
FX modifier should be at the beginning of the format string.
!error

select cast(date"2001-03-02" as varchar format "FXFMFXYYYY-MM-DD");
FX modifier should be at the beginning of the format string.
!error

select cast(date"2001-03-03" as varchar format "FXFXYYYY-MM-DD");
FX modifier should be at the beginning of the format string.
!error

select cast(date"2001-03-04" as varchar format "FMFXYYYY-MM-DD");
FX modifier should be at the beginning of the format string.
!error

select cast(date"2001-03-03" as varchar format "-FXYYYY-MM-DD");
FX modifier should be at the beginning of the format string.
!error

select cast(date"2001-03-03" as varchar format '"text"FXYYYY-MM-DD');
FX modifier should be at the beginning of the format string.
!error

!}

# End cast-with-format.iq