core/sql/regress/executor/TEST022

-- Test: TEST022 (Executor)
-- @@@ START COPYRIGHT @@@
--
-- 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.
--
-- @@@ END COPYRIGHT @@@
--
-- Functionality: Basic data type conversions, arithmetic and comparative
--                operations, both positive and negative tests.
-- Expected files: EXPECTED022, EXPECTED022.MX, EXPECTED022.MP
-- Table created: 
-- Limitations:

obey TEST022 (clean_up);

log LOG022 clear;
obey TEST022 (test);
log;

obey TEST022 (clean_up);

?ignore
log LOG022;
--Now we will run the same test again with PCODE CQD option 'OFF'
--to ensure PCODE and CLAUSE's evaluation are exactly similar.
Control Query Default PCODE_OPT_LEVEL 'OFF';
obey TEST022 (test);
log;

obey TEST022 (clean_up);
?ignore

exit;

-------------------------------------------------------------------------------
?section clean_up
-------------------------------------------------------------------------------
drop table t022t3;
drop table t022t4;
drop table t022t6;
drop table t022t8;
drop table t022t9;
drop table t022ta;
drop table t022tb;
drop table t022tc;
drop table t022td;
drop table t022te;
drop table t022tf;
drop table t022tg;
drop table t022th;
drop table t022ti;
drop table t022u2;
drop table $$TEST_SCHEMA$$.t022a7;
drop table t022fltcmp;
drop table t022bug cascade;
drop table t022dtime;
drop table t022nanos;

-------------------------------------------------------------------------------
?section test
-------------------------------------------------------------------------------
create table t022t3 (a int unsigned);
create table t022t4 (a smallint unsigned, b smallint); 

insert into t022t3 values (4294967295), (3000000000);
insert into t022t4 values (65535, 32767);

create table t022a7 (c1 float(22), c2 float not null);

prepare insertIntoT022A7 from insert into t022a7 values (?,?);
sh sh -c 'rm -f t022xxx > /dev/null 2>&1 ';
-- Must use "sh sh -c" if we want the command to work on NT platforms.
-- The space between t022xxx and the single quote (') is required by the SQLCI sh command.
-- If the space is not specified, the output is going into the file "t022xxx'" - Strange!
sh sh -c 'echo "begin work;"                                      > t022xxx ';
sh sh -c 'echo "execute insertIntoT022A7 using 0 , 1. ; "        >> t022xxx ';
sh sh -c 'echo "execute insertIntoT022A7 using 1 , 2.E3 ; "      >> t022xxx ';
sh sh -c 'echo "execute insertIntoT022A7 using 3 , 123.456E2 ; " >> t022xxx ';
sh sh -c 'echo "commit work;"                                    >> t022xxx ';

-- Positive cases to test the limits of each data type.

select cast (-9 as numeric(1)) from dual;
select cast (9 as numeric(1)) from dual;
select cast (-9999 as numeric(4)) from dual;
select cast (9999 as numeric(4)) from dual;
select cast (-32768 as smallint) from dual;
select cast (32767 as smallint) from dual;

select cast (0 as numeric(1) unsigned) from dual;
select cast (9 as numeric(1) unsigned) from dual;
select cast (0 as numeric(4) unsigned) from dual;
select cast (9999 as numeric(4) unsigned) from dual;
select cast (0 as smallint unsigned) from dual;
select cast (65535 as smallint unsigned) from dual;

select cast (-99999 as numeric(5)) from dual;
select cast (99999 as numeric(5)) from dual;
select cast (-999999999 as numeric(9)) from dual;
select cast (999999999 as numeric(9)) from dual;
select cast (-2147483648 as int) from dual;
select cast (2147483647 as int) from dual;

select cast (0 as numeric(5) unsigned) from dual;
select cast (99999 as numeric(5) unsigned) from dual;
select cast (0 as numeric(9) unsigned) from dual;
select cast (999999999 as numeric(9) unsigned) from dual;
select cast (0 as int unsigned) from dual;
select cast (4294967295 as int unsigned) from dual;

select cast (-9999999999 as numeric(10)) from dual;
select cast (9999999999 as numeric(10)) from dual;
select cast (-999999999999999999 as numeric(18)) from dual;
select cast (999999999999999999 as numeric(18)) from dual;
select cast (-9223372036854775808 as largeint) from dual;
select cast (9223372036854775807 as largeint) from dual;

select cast (-9 as decimal(1)) from dual;
select cast (9 as decimal(1)) from dual;
select cast (-999999999999999999 as decimal(18)) from dual;
select cast (999999999999999999 as decimal(18)) from dual;

select cast (0 as decimal(1) unsigned) from dual;
select cast (9 as decimal(1) unsigned) from dual;
select cast (0 as decimal(9) unsigned) from dual;
select cast (999999999 as decimal(9) unsigned) from dual;

select cast (10 as tinyint) from dual;
select cast (-10 as tinyint) from dual;
select cast (127 as tinyint) from dual;
select cast (-128 as tinyint) from dual;
select cast (10 as tinyint unsigned) from dual;
select cast (0 as tinyint unsigned) from dual;
select cast (255 as tinyint unsigned) from dual;

select cast (date'12/31/9999' as timestamp(3)) from dual;
select cast (timestamp'12/31/9999 11:59:59.999999 pm' as date) from dual;
select cast (timestamp'12/31/9999 11:59:59.999999 pm' as time(3)) from dual;

select interval'0.1'second + timestamp'12/31/9998 11:59:59.9 pm' from dual;
select timestamp'12/31/9998 11:59:59.9 pm' + interval'0.1'second from dual;
select timestamp'01/01/9999 00:00:00.0' + interval-'0.1'second from dual;
select timestamp'01/01/9999 00:00:00.0' - interval'0.1'second from dual;
select (date'12/31/9999' - date'12/31/9998') day(3) from dual;
select (date'12/31/9998' - date'12/31/9999') day(3) from dual;

select interval'1'month + timestamp'12/31/9998 11:59:59.999 pm' from dual;
select timestamp'12/31/9998 11:59:59.999 pm' + interval'1'month from dual;
select timestamp'01/31/9999 11:59:59.999 pm' + interval-'1'month from dual;
select timestamp'01/31/9999 11:59:59.999 pm' - interval'1'month from dual;
select (date'12/31/9999' - date'12/31/9998') month from dual;
select (date'12/31/9998' - date'12/31/9999') month from dual;
select (date'12/31/9999' - date'12/31/9998') year to month from dual;
select (date'12/31/9998' - date'12/31/9999') year to month from dual;

select interval'1'second + time'11:59:59 pm' from dual;
select time'11:59:59 pm' + interval'1'second from dual;
select time'12:00:00 am' + interval-'1'second from dual;
select time'12:00:00 am' - interval'1'second from dual;

select timestamp '2015-05-31T10:11:12' from dual;
select timestamp '2015-05-31T10:11:12Z' from dual;
select timestamp '2015-05-31T10:11:12-05:00' from dual;
select timestamp '2015-05-31T10:11:12+05:00' from dual;
select timestamp '2015-05-31T10:11:12.123-05:00' from dual;
select timestamp '2015-05-31T10:11:12.123+05:00' from dual;

select cast('2015-05-31T10:11:12' as timestamp) from dual;
select cast('2015-05-31T10:11:12Z' as timestamp) from dual;
select cast('2015-05-31T10:11:12-05:00' as timestamp) from dual;
select cast('2015-05-31T10:11:12+05:00' as timestamp) from dual;
select cast('2015-05-31T10:11:12.123-05:00' as timestamp) from dual;
select cast('2015-05-31T10:11:12.123+05:00' as timestamp) from dual;

select time '10:11:12' from dual;
select time '10:11:12Z' from dual;
select time '10:11:12-05:00' from dual;
select time '10:11:12+05:00' from dual;
select time '10:11:12.123-05:00' from dual;
select time '10:11:12.123+05:00' from dual;

select cast('10:11:12' as time) from dual;
select cast('10:11:12Z' as time) from dual;
select cast('10:11:12-05:00' as time) from dual;
select cast('10:11:12+05:00' as time) from dual;
select cast('10:11:12.123-05:00' as time) from dual;
select cast('10:11:12.123+05:00' as time) from dual;

select time '23:11:06.123' from dual;
select cast ('23:11:06.123' as time(2)) from dual;

select cast('2010-01-01' as timestamp(3)) from dual;
select cast('2010-01-01 10' as timestamp) from dual;

create table if not exists t022dtime(a date not null, b time, c timestamp);
prepare s from upsert into t022dtime values (?, ?, ?);
execute s using '2010-01-01', '10:10:10', '2010-01-01 10:10:10.123';
select * from t022dtime;

-- negative cases
select cast('2016-01-29Z' as date) from dual;
select cast('2016-01-29+05:00' as date) from dual;
select cast('10:11:12-05:000' as time) from dual;
select date '2016-01-29Z' from dual;
select date '2016-01-29+05:00' from dual;
select time '25:11:11' from dual;
select time '23:11:11.1234567890' from dual;
select cast ('23:11:61' as time) from dual;
select cast ('23:11:06' as timestamp) from dual;
select cast('2010-01-01' as time) from dual;
select timestamp'2010-01-01 10' from dual;

-- Prove these cases work since they're used in the succeeding negative tests.

select cast (cast (-10 as smallint) as numeric(2)) from dual;
select cast (cast (10 as smallint) as numeric(2)) from dual;
select cast (cast (-10 as numeric(2)) as smallint) from dual;
select cast (cast (10 as numeric(2)) as smallint) from dual;
select cast (-1 as smallint) from dual;

select cast (32768 as smallint unsigned) from dual;
select cast (10 as smallint unsigned) from dual;

select cast (-32769 as int) from dual;
select cast (32768 as int) from dual;
select cast (-10 as int) from dual;
select cast (10 as int) from dual;
select cast (-1 as int) from dual;
select cast (65536 as int) from dual;
select cast (-100000 as int) from dual;
select cast (100000 as int) from dual;

select cast (32768 as int unsigned) from dual;
select cast (10 as int unsigned) from dual;
select cast (65536 as int unsigned) from dual;
select cast (2147483648 as int unsigned) from dual;
select cast (100000 as int unsigned) from dual;

select cast (-32769 as largeint) from dual;
select cast (32768 as largeint) from dual;
select cast (-10 as largeint) from dual;
select cast (10 as largeint) from dual;
select cast (-1 as largeint) from dual;
select cast (65536 as largeint) from dual;
select cast (-2147483649 as largeint) from dual;
select cast (2147483648 as largeint) from dual;
select cast (-100000 as largeint) from dual;
select cast (100000 as largeint) from dual;
select cast (4294967296 as largeint) from dual;
select cast (-10000000000 as largeint) from dual;
select cast (10000000000 as largeint) from dual;

select cast (-32769 as decimal(5)) from dual;
select cast (32768 as decimal(5)) from dual;
select cast (-10 as decimal(2)) from dual;
select cast (10 as decimal(2)) from dual;
select cast (-1 as decimal(1)) from dual;
select cast (65536 as decimal(5)) from dual;
select cast (-2147483649 as decimal(10)) from dual;
select cast (2147483648 as decimal(10)) from dual;
select cast (-100000 as decimal(6)) from dual;
select cast (100000 as decimal(6)) from dual;
select cast (4294967296 as decimal(10)) from dual;
select cast (-10000000000 as decimal(11)) from dual;
select cast (10000000000 as decimal(11)) from dual;

select cast (32768 as decimal(5) unsigned) from dual;
select cast (10 as decimal(2) unsigned) from dual;
select cast (65536 as decimal(5) unsigned) from dual;
select cast (100000 as decimal(6) unsigned) from dual;

select cast (-32769 as real) from dual;
select cast (32768 as real) from dual;
select cast (-10 as real) from dual;
select cast (10 as real) from dual;
select cast (-1 as real) from dual;
select cast (65536 as real) from dual;
select cast (-2147483649 as real) from dual;
select cast (9999999999 as real) from dual;
select cast (-100000 as real) from dual;
select cast (100000 as real) from dual;
select cast (9999999999 as real) from dual;

select cast (-32769 as double precision) from dual;
select cast (32768 as double precision) from dual;
select cast (-10 as double precision) from dual;
select cast (10 as double precision) from dual;
select cast (-1 as double precision) from dual;
select cast (65536 as double precision) from dual;
select cast (-2147483649 as double precision) from dual;
select cast (2147483648 as double precision) from dual;
select cast (-100000 as double precision) from dual;
select cast (100000 as double precision) from dual;
select cast (4294967296 as double precision) from dual;

select cast ('Hello, world!' as char(13)) from dual;
select cast ('Hello, world!' as varchar(13)) from dual;
select cast (cast ('Hello        ' as char(13)) as char(5)) from dual;
select cast (cast ('Hello        ' as char(13)) as varchar(5)) from dual;
select cast (cast ('Hello, world!' as char(13)) as char(5)) from dual;
select cast (cast ('Hello, world!' as char(13)) as varchar(5)) from dual;
select cast (cast ('Hello        ' as varchar(13)) as char(5)) from dual;
select cast (cast ('Hello        ' as varchar(13)) as varchar(5)) from dual;
select cast (cast ('Hello, world!' as varchar(13)) as char(5)) from dual;
select cast (cast ('Hello, world!' as varchar(13)) as varchar(5)) from dual;
select cast (cast (-2147483649 as double precision) as largeint) from dual;
select cast (cast (2147483648 as double precision) as largeint) from dual;

-- Positive cases to test division.

select cast (1 as numeric(1)) / cast (1 as numeric(1)) from dual;
select cast (1 as numeric(5)) / cast (1 as numeric(1)) from dual;
select cast (1 as numeric(1)) / cast (1 as numeric(5)) from dual;
select cast (1 as numeric(5)) / cast (1 as numeric(5)) from dual;
select cast (1 as numeric(10)) / cast (1 as numeric(10)) from dual;

select cast (1 as numeric(1) unsigned) /
       cast (1 as numeric(1) unsigned) from dual;
select cast (1 as numeric(5) unsigned) /
       cast (1 as numeric(1) unsigned) from dual;
select cast (1 as numeric(1) unsigned) /
       cast (1 as numeric(5) unsigned) from dual;
select cast (1 as numeric(5) unsigned) /
       cast (1 as numeric(5) unsigned) from dual;
select cast (1 as numeric(9) unsigned) /
       cast (1 as numeric(9) unsigned) from dual;

select cast (1 as double precision) / cast (1 as double precision) from dual;

-- Negative tests.

select cast (cast (-10 as smallint) as numeric(1)) from dual;
select cast (cast (10 as smallint) as numeric(1)) from dual;
select cast (cast (-10 as numeric(2)) as numeric(1)) from dual;
select cast (cast (10 as numeric(2)) as numeric(1)) from dual;
select cast (cast (-1 as smallint) as smallint unsigned) from dual;
select cast (cast (10 as smallint) as numeric(1) unsigned) from dual;
select cast (cast (-1 as smallint) as int unsigned) from dual;
select cast (cast (-10 as smallint) as decimal(1)) from dual;
select cast (cast (10 as smallint) as decimal(1)) from dual;
select cast (cast (-1 as smallint) as decimal(1) unsigned) from dual;
select cast (cast (10 as smallint) as decimal(1) unsigned) from dual;

select cast (cast (32768 as smallint unsigned) as smallint) from dual;
select cast (cast (10 as smallint unsigned) as numeric(1)) from dual;
select cast (cast (10 as smallint unsigned) as numeric(1)unsigned) from dual;
select cast (cast (10 as smallint unsigned) as decimal(1)) from dual;
select cast (cast (10 as smallint unsigned) as decimal(1)unsigned) from dual;

select cast (cast (-32769 as int) as smallint) from dual;
select cast (cast (32768 as int) as smallint) from dual;
select cast (cast (-10 as int) as numeric(1)) from dual;
select cast (cast (10 as int) as numeric(1)) from dual;
select cast (cast (-1 as int) as smallint unsigned) from dual;
select cast (cast (65536 as int) as smallint unsigned) from dual;
select cast (cast (10 as int) as numeric(1) unsigned) from dual;
select cast (cast (-100000 as int) as numeric(5)) from dual;
select cast (cast (100000 as int) as numeric(5)) from dual;
select cast (cast (-1 as int) as int unsigned) from dual;
select cast (cast (100000 as int) as numeric(5) unsigned) from dual;
select cast (cast (-10 as int) as decimal(1)) from dual;
select cast (cast (10 as int) as decimal(1)) from dual;
select cast (cast (-1 as int) as decimal(1) unsigned) from dual;
select cast (cast (10 as int) as decimal(1) unsigned) from dual;

select cast (cast (32768 as int unsigned) as smallint) from dual;
select cast (cast (10 as int unsigned) as numeric(1)) from dual;
select cast (cast (65536 as int unsigned) as smallint unsigned) from dual;
select cast (cast (10 as int unsigned) as numeric(1) unsigned) from dual;
select cast (cast (2147483648 as int unsigned) as int) from dual;
select cast (cast (100000 as int unsigned) as numeric(5)) from dual;
select cast (cast (100000 as int unsigned) as numeric(5) unsigned) from dual;
select cast (cast (10 as int unsigned) as decimal(1)) from dual;
select cast (cast (10 as int unsigned) as decimal(1) unsigned) from dual;

select cast (cast (-32769 as largeint) as smallint) from dual;
select cast (cast (32768 as largeint) as smallint) from dual;
select cast (cast (-10 as largeint) as numeric(1)) from dual;
select cast (cast (10 as largeint) as numeric(1)) from dual;
select cast (cast (-1 as largeint) as smallint unsigned) from dual;
select cast (cast (65536 as largeint) as smallint unsigned) from dual;
select cast (cast (10 as largeint) as numeric(1) unsigned) from dual;
select cast (cast (-2147483649 as largeint) as int) from dual;
select cast (cast (2147483648 as largeint) as int) from dual;
select cast (cast (-100000 as largeint) as numeric(5)) from dual;
select cast (cast (100000 as largeint) as numeric(5)) from dual;
select cast (cast (-1 as largeint) as int unsigned) from dual;
select cast (cast (4294967296 as largeint) as int unsigned) from dual;
select cast (cast (100000 as largeint) as numeric(5) unsigned) from dual;
select cast (cast (-10000000000 as largeint) as numeric(10)) from dual;
select cast (cast (10000000000 as largeint) as numeric(10)) from dual;
select cast (cast (10 as largeint) as decimal(1)) from dual;
select cast (cast (-1 as largeint) as decimal(1) unsigned) from dual;
select cast (cast (10 as largeint) as decimal(1) unsigned) from dual;

select cast (cast (-32769 as decimal(5)) as smallint) from dual;
select cast (cast (32768 as decimal(5)) as smallint) from dual;
select cast (cast (-10 as decimal(2)) as numeric(1)) from dual;
select cast (cast (10 as decimal(2)) as numeric(1)) from dual;
select cast (cast (-1 as decimal(1)) as smallint unsigned) from dual;
select cast (cast (65536 as decimal(5)) as smallint unsigned) from dual;
select cast (cast (10 as decimal(2)) as numeric(1) unsigned) from dual;
select cast (cast (-2147483649 as decimal(10)) as int) from dual;
select cast (cast (2147483648 as decimal(10)) as int) from dual;
select cast (cast (-100000 as decimal(6)) as numeric(5)) from dual;
select cast (cast (100000 as decimal(6)) as numeric(5)) from dual;
select cast (cast (-1 as decimal(1)) as int unsigned) from dual;
select cast (cast (4294967296 as decimal(10)) as int unsigned) from dual;
select cast (cast (100000 as decimal(6)) as numeric(5) unsigned) from dual;
select cast (cast (-10000000000 as decimal(11)) as numeric(10)) from dual;
select cast (cast (10000000000 as decimal(11)) as numeric(10)) from dual;
select cast (cast (10 as decimal(2)) as decimal(1)) from dual;
select cast (cast (-1 as decimal(1)) as decimal(1) unsigned) from dual;
select cast (cast (10 as decimal(2)) as decimal(1) unsigned) from dual;

select cast (cast (32768 as decimal(5) unsigned) as smallint) from dual;
select cast (cast (10 as decimal(2) unsigned) as numeric(1)) from dual;
select cast (cast (65536 as decimal(5) unsigned) as smallint unsigned) from dual;
select cast (cast (10 as decimal(2) unsigned) as numeric(1) unsigned) from dual;
select cast (cast (100000 as decimal(6) unsigned) as numeric(5)) from dual;
select cast (cast (100000 as decimal(6) unsigned) as numeric(5) unsigned) from dual;
select cast (cast (10 as decimal(2) unsigned) as decimal(1)) from dual;
select cast (cast (10 as decimal(2) unsigned) as decimal(1) unsigned) from dual;

select cast (cast (-32769 as real) as smallint) from dual;
select cast (cast (32768 as real) as smallint) from dual;
select cast (cast (-10 as real) as numeric(1)) from dual;
select cast (cast (10 as real) as numeric(1)) from dual;
select cast (cast (-1 as real) as smallint unsigned) from dual;
select cast (cast (65536 as real) as smallint unsigned) from dual;
select cast (cast (10 as real) as numeric(1) unsigned) from dual;
select cast (cast (-2147484160 as real) as int) from dual;
select cast (cast (9999999999 as real) as int) from dual;
select cast (cast (-100000 as real) as numeric(5)) from dual;
select cast (cast (100000 as real) as numeric(5)) from dual;
select cast (cast (-1 as real) as int unsigned) from dual;
select cast (cast (9999999999 as real) as int unsigned) from dual;
select cast (cast (100000 as real) as numeric(5) unsigned) from dual;
select cast (cast (10 as real) as decimal(1)) from dual;
select cast (cast (-1 as real) as decimal(1) unsigned) from dual;
select cast (cast (10 as real) as decimal(1) unsigned) from dual;

select cast (cast (-32769 as double precision) as smallint) from dual;
select cast (cast (32768 as double precision) as smallint) from dual;
select cast (cast (-10 as double precision) as numeric(1)) from dual;
select cast (cast (10 as double precision) as numeric(1)) from dual;
select cast (cast (-1 as double precision) as smallint unsigned) from dual;
select cast (cast (65536 as double precision) as smallint unsigned) from dual;
select cast (cast (10 as double precision) as numeric(1) unsigned) from dual;
select cast (cast (-2147483649 as double precision) as int) from dual;
select cast (cast (2147483648 as double precision) as int) from dual;
select cast (cast (-100000 as double precision) as numeric(5)) from dual;
select cast (cast (100000 as double precision) as numeric(5)) from dual;
select cast (cast (-1 as double precision) as int unsigned) from dual;
select cast (cast (4294967296 as double precision) as int unsigned) from dual;
select cast (cast (100000 as double precision) as numeric(5) unsigned) from dual;
select cast (cast (10 as double precision) as decimal(1)) from dual;
select cast (cast (-1 as double precision) as decimal(1) unsigned) from dual;
select cast (cast (10 as double precision) as decimal(1) unsigned) from dual;

-- negative tests for tinyint
select cast(-129 as tinyint) from dual;
select cast(128 as tinyint) from dual;
select cast(256 as tinyint unsigned) from dual;
select cast(-1 as tinyint unsigned) from dual;

-- tests for DATE, TIME, TIMESTAMP
select cast ('12.07.1961 03.04.55.123456' as timestamp) from dual;
select cast ('12.07.1961 03.04.55.123456' as timestamp(3)) from dual;
select cast (cast ('12.07.1961 03.04.55.123456' as timestamp) as DATE) from dual;
select cast (cast ('12.07.1961 03.04.55.123456' as timestamp) as char(21)) from dual;
select cast ('12/13/1987' as date) from dual;

-- some negative DATE, TIME, TIMESTAMP
-- illegal european format
select cast ('12.13.1987' as date) from dual;

-- illegal US format
select cast ('13/12/1987' as date) from dual;

-- string overflow
select cast (cast ('12.07.1961 03:04:55.123456' as timestamp) as char(18)) from dual;

-- Feb. 30th doesn't exist
select cast('1997-30-02' as DATE) from dual;

select interval'0.1'second + timestamp'12/31/9999 11:59:59.9 pm' from dual;
select timestamp'12/31/9999 11:59:59.9 pm' + interval'0.1'second from dual;
select timestamp'01/01/0001 00:00:00.0' + interval-'0.1'second from dual;
select timestamp'01/01/0001 00:00:00.0' - interval'0.1'second from dual;

select interval'1'month + date'12/31/9999' from dual;
select date'12/31/9999' + interval'1'month from dual;
select date'01/01/0001' + interval-'1'month from dual;
select date'01/01/0001' - interval'1'month from dual;

select interval'1'month + date'01/31/9999' from dual;
select date'01/31/9999' + interval'1'month from dual;
select date'03/30/9999' + interval-'1'month from dual;
select date'03/30/9999' - interval'1'month from dual;

-- Negative cases to test division by zero.

select cast (1 as numeric(1)) / cast (0 as numeric(1)) from dual;
select cast (1 as numeric(5)) / cast (0 as numeric(1)) from dual;
select cast (1 as numeric(1)) / cast (0 as numeric(5)) from dual;
select cast (1 as numeric(5)) / cast (0 as numeric(5)) from dual;
select cast (1 as numeric(10)) / cast (0 as numeric(10)) from dual;

select cast (1 as numeric(1) unsigned) /
       cast (0 as numeric(1) unsigned) from dual;
select cast (1 as numeric(5) unsigned) /
       cast (0 as numeric(1) unsigned) from dual;
select cast (1 as numeric(1) unsigned) /
       cast (0 as numeric(5) unsigned) from dual;
select cast (1 as numeric(5) unsigned) /
       cast (0 as numeric(5) unsigned) from dual;
select cast (1 as numeric(9) unsigned) /
       cast (0 as numeric(9) unsigned) from dual;

select cast (1 as double precision) / cast (0 as double precision) from dual;

-- results:  1 row selected
select cast('66.2' as float) from dual where cast('66.2' as float) = 66.2;

-- to test comparisons between signed and unsigned numbers.

select * from t022t3 where 2147483647 < a;
select * from t022t3 where 2147483647 <= a;
select * from t022t3 where a < 2147483647;
select * from t022t3 where a <= 2147483647;
select * from t022t3 where a < 0;
select * from t022t3 where a <= 0;
select * from t022t3 where -1 > a;
select * from t022t3 where a > 0;
select * from t022t3 where a >= 2147483647;
select * from t022t3 where 2147483647 >= a;
select * from t022t3 where a >= 32767;

select * from t022t4 where b < 3000000000;
select * from t022t4 where b <= 3000000000;
select * from t022t4 where b > 3000000000;
select * from t022t4 where b >= 3000000000;


-- negative test cases
create table t022t6 (t022t6_c1 decimal( 9) unsigned,
                     t022t6_c2 decimal(10) unsigned,
                     t022t7_c1 dec    ( 9) unsigned,
                     t022t7_c2 dec    (10) unsigned);

select cast(1 as numeric(10) unsigned) from (values(0))x;
select cast(1 as decimal(10) unsigned) from (values(0))x;
select cast(1 as dec    (10) unsigned) from (values(0))x;


-- time/timestamp AM/PM tests (test convDoIt with timestamps)
create table t022t8 (c1 timestamp, c2 time);

set param ?p '12/05/1998 03:15:08.123456 pm';
insert into t022t8 (c1) values ( ?p );

set param ?p '2015-05-31T14:15:16Z';
insert into t022t8 (c1) values ( ?p );

set param ?p '2015-05-31T14:15:16';
insert into t022t8 (c1) values ( ?p );

set param ?p '2100-01-01:00:00:47.250000';
insert into t022t8 (c1) values ( ?p );

set param ?p '12/05/1998 10:15:08.889 pm';
insert into t022t8 (c1) values ( ?p );

set param ?p '06/28/1998 03:15:08 pm';
insert into t022t8 (c1) values ( ?p );

set param ?p '02/10/1998 12:15:08.123456 am';
insert into t022t8 (c1) values ( ?p );

set param ?p '12/05/1998 03:15:08.123456 am';
insert into t022t8 (c1) values ( ?p );

set param ?p '12/05/1998 03:15:08.123456';
insert into t022t8 (c1) values ( ?p );

-- expect error since 15 hours and am don't match
set param ?p '11/20/1997 15:15:08.123456 am';
insert into t022t8 (c1) values ( ?p );

-- expect an error
set param ?p '2100-01-01:ab:00:47.250000';
insert into t022t8 (c1) values ( ?p );

-- expect error since the date is not in US format
set param ?p '1998-03-12 03:15:08.123456 pm';
insert into t022t8 (c1) values ( ?p );

-- expect error since the date is not in US format
set param ?p '1996-05-23 16:15:08.123 am';
insert into t022t8 (c1) values ( ?p );

select c1 from t022t8;

-- test convDoIt with time
set param ?p '03:15:08 pm';
insert into t022t8 (c2) values ( ?p );

set param ?p '11:15:08 pm';
insert into t022t8 (c2) values ( ?p );

set param ?p '10:15:08 am';
insert into t022t8 (c2) values ( ?p );

set param ?p '12:59:59 am';
insert into t022t8 (c2) values ( ?p );

set param ?p '10:05:59 am';
insert into t022t8 (c2) values ( ?p );

set param ?p '09:00:00 am';
insert into t022t8 (c2) values ( ?p );

set param ?p '11:00:00 am';
insert into t022t8 (c2) values ( ?p );

set param ?p '08:00:00';
insert into t022t8 (c2) values ( ?p );

set param ?p '00:01:00';
insert into t022t8 (c2) values ( ?p );

-- expect error
set param ?p '00:a1:00';
insert into t022t8 (c2) values ( ?p );

-- expect error since 14 and am don't match
set param ?p '14:59:59 am';
insert into t022t8 (c2) values ( ?p );

-- expect error 
set param ?p '11:60:59 am';
insert into t022t8 (c2) values ( ?p );

-- expect error
set param ?p '11:03:33';
insert into t022t8 (c2) values ( ?p );

-- expect an error
set param ?p '00:01:00.03';
insert into t022t8 (c2) values ( ?p );

-- expect an error
set param ?p '10:36:52.0781 pm';
insert into t022t8 (c2) values ( ?p );

select c2 from t022t8;

-- test type casting involving the ordering of digits and other symbols 
-- like - . +

-- positive cases 
select cast('-.3' as int), cast ('+1.4' as int) , cast('-32.3' as int) 
      from t022t8;
select c1 from t022t8 where cast('-.3' as int) < 1 and cast('+1.5' as int) <2;

select cast ('123.' as real) ,
       cast ('123.12' as real),
       cast ('123.E2' as real),
       cast ('123.12E2' as real)from (values(1)) as T;

obey t022xxx;
select * from $$TEST_SCHEMA$$.t022a7;

-- negative cases (expect errors)
select cast('.-2' as int) from t022t8;
select cast('.+3' as int) from t022t8;
select cast('.4-' as numeric(4)) from t022t8;
select cast('-. 3' as int) from t022t8;
select cast('-3 . ' as int) from t022t8;

select c1 from t022t8 where cast('.-2' as int) < 1000;
select c1 from t022t8 where cast('.+3' as int) < 1000;
select c1 from t022t8 where cast('.4-' as numeric(4)) < 1;
select c1 from t022t8 where cast('-. 3' as int) < 1;
select c1 from t022t8 where cast('-3 .' as int) < 1;

select cast ('123..' as real) from (values(1)) as T;
select cast ('123..12' as real)from (values(1)) as T;

-- Testing solution 10-030707-7648
-- Catch overflow while manipulating interval internal type (decimal)
create table t022t9 (h10s4 interval hour(10) to second(4));
insert into t022t9 values (-interval '1111111111:12:00.1234'
   hour(10) to second(4));
insert into t022t9 values (interval '1111111111:12:00.1234'
   hour(10) to second(4));

-- Should not overflow.
update t022t9 set h10s4 = h10s4 * 1;
select * from t022t9;

update t022t9 set h10s4 = h10s4 * 1.0;

select * from t022t9;

-- Should overflow.
update t022t9 set h10s4 = h10s4 * 10;

select * from t022t9;

-- End of testing solution 10-030707-7648

-- Numeric to interval converion overflow checking
create table t022ta(a interval second not null primary key);
insert into t022ta values (interval '12.200' second(2,3));
insert into t022ta values (interval '12.300' second(2,4));

select cast (10 + 90 as interval hour(3)) from t022ta;
select cast (10 + 89 as interval hour) from t022ta;
select cast (10 + 89 as interval second) from t022ta;
-- should overflow
select cast (10 + 90 as interval second) from t022ta;
-- should overflow
select cast (100 as interval second) from t022ta;
select cast (99 as interval second) from t022ta where a < interval '12.3' second;

create table t022tb(a integer);
insert into t022tb values (10);

select cast (a + 89 as interval second) from t022tb;
-- should overflow
select cast (a + 100.0 as interval second) from t022tb;
select cast (a + 100.00 as interval second(3)) from t022tb;
select cast (a + 100.00 as interval second(3,1)) from t022tb;
select cast (a + 100.00 as interval second(3,2)) from t022tb;
select cast (a + 100.00 as interval second(3,3)) from t022tb;
select time '11:58:59.236' + interval '1.12' second from t022tb;

select a*1 from (values (interval '10.0' second)) x(a);
select cast (a as numeric (2)) from (values(interval '12.00' second)) t(a);
select cast (a as numeric (18)) from (values(interval '12.00' second)) t(a);
-- should overflow
select cast (a as numeric (1)) from (values(interval '12.00' second)) t(a);
-- should overflow
select cast (a as numeric (19)) from (values(interval '12.00' second)) t(a);

-- End of numeric to interval convertion overflow checking

-- Testing for ALM bug 4419 (Feb 2012) 
--  - overflow check with mismatched fractional precision
create table t022th(inter interval hour to second not null primary key);
insert into t022th values(interval '001:23:45' hour(3) to second); -- OK
insert into t022th values(interval '901:23:45' hour(3) to second);
insert into t022th values(interval '90123:45' minute(5) to second(3));

create table t022ti(inter interval hour(3) to second(3) not null primary key);
insert into t022ti values(interval '90123:45' minute(5) to second(6));

-- End of ALM bug 4419

-- Testing for solution 10-040302-3733

-- RG: Build FCS040225, Select interval_second_col * 0.02 gets numeric overflow.

create table t022tc (ivsc interval second);
insert into t022tc values (interval '12' second);

---  This select should get 0.1440 second.

select ivsc * 0.02 from t022tc;

create table t022td (D_to_F      INTERVAL    DAY TO SECOND(3));
insert into t022td values (interval '18 15:17:02.123' day to second(3));
insert into t022td values (interval '27 04:57:58.999' day to second(3));
select D_to_F / 1  from t022td;

-- End of testing solution 10-040302-3733

-- Testing Solution 10-040226-3566
--RG:0218 'select cast from integer to interval second'got numeric overflow

create table t022te (inum integer, dnum numeric (8,6));
insert into t022te values (99, 23.123456);

-- should not overflow
select CAST (dnum as INTERVAL second) from t022te;

create table t022tf (
    ivyr interval year (4),
    ivmt interval month,
    ivdy interval day,
    ivhr interval hour,
    ivmn interval minute,
    ivsc interval second (2,6)
    );
insert into t022tf values (         -- normal values
    interval '2000' year (4),
    interval '10' month,
    interval '2' day,
    interval '15' hour,
    interval '60' minute,
    interval '12' second
    );

-- should not overflow
select ivsc, ivsc * 0.02 from t022tf;

-- should not overflow
update t022tf set ivyr = ivyr * 1.5;

select * from t022tf;

delete from t022tf;

insert into t022tf values (         -- min values
    interval '0' year,
    interval '0' month,
    interval '0' day,
    interval '0' hour,
    interval '0' minute,
    interval '0' second
    );

-- should not overflow
update t022tf set
    IVYR = IVYR + interval '25' year,
    IVMT = IVMT + interval '18' month,
    IVDY = IVDY + interval '40' day,
    IVHR = IVHR + interval '24' hour,
    IVMN = IVMN + interval '60' minute,
    IVSC = IVSC + interval '3.99' second;

select * from t022tf;

-- End of testing Solution 10-040226-3566

-- negative tests on casts between CHAR/NCHAR 
select cast (N'Hello, world!' as char(13)) from dual;
select cast (N'Hello, world!' as varchar(13)) from dual;
select cast ('Hello, world!' as nchar(13)) from dual;
select cast ('Hello, world!' as nchar varying(13)) from dual;

-- tests on cast VAR/NCHAR to VAR/NCHAR
select cast (N'Hello, world!' as nchar(13)) from dual;
select cast (N'Hello, world!' as nchar varying(13)) from dual;
select cast (cast (N'Hello        ' as nchar(13)) as nchar(5)) from dual;
select cast (cast (N'Hello        ' as nchar(13)) as nchar varying(5)) from dual;
select cast (cast (N'Hello, world!' as nchar(13)) as nchar(5)) from dual;
select cast (cast (N'Hello, world!' as nchar(13)) as nchar varying(5)) from dual;
select cast (cast (N'Hello        ' as nchar varying(13)) as nchar(5)) from dual;
select cast (cast (N'Hello        ' as nchar varying(13)) as nchar varying(5)) from dual;
select cast (cast (N'Hello, world!' as nchar varying(13)) as nchar(5)) from dual;
select cast (cast (N'Hello, world!' as nchar varying(13)) as nchar varying(5)) from dual;

-- tests on cast VAR/NCHAR to DATETIME
select cast (N'12.07.1961 03.04.55.123456' as timestamp) from dual;
select cast (N'12.07.1961 03.04.55.123456' as timestamp(3)) from dual;
select cast (cast (N'12.07.1961 03.04.55.123456' as timestamp) as DATE) 
from dual;
select cast (cast (N'12.07.1961 03.04.55.123456' as timestamp) as char(21)) from dual;
select cast (N'12/13/1987' as date) from dual;

-- some negative DATE, TIME, TIMESTAMP
-- illegal european format
select cast (N'12.13.1987' as date) from dual;

-- illegal US format
select cast (N'13/12/1987' as date) from dual;

-- string overflow
select cast (cast (N'12/07/1961 03:04:55.123456' as timestamp) as char(18)) from dual;

-- Feb. 32th doesn't exist
select cast(N'1997-02-32' as DATE) from dual;

-- test on cast VAR/NCAR to smallint
select cast(N'2' as SMALLINT) from dual;
select cast(N'-32768' as SMALLINT) from dual;
select cast(N'32767' as SMALLINT) from dual;
select cast(N'0' as SMALLINT UNSIGNED) from dual;
select cast(N'65535' as SMALLINT UNSIGNED) from dual;

-- negative test on cast VAR/NCAR to smallint
select cast(N'-1' as SMALLINT UNSIGNED) from dual;
select cast(N'-32769' as SMALLINT) from dual;
select cast(N'50000' as SMALLINT) from dual;
select cast(N'-32769' as SMALLINT UNSIGNED) from dual;
select cast(N'70000' as SMALLINT UNSIGNED) from dual;
select cast(N'70000' as SMALLINT UNSIGNED) from dual;

-- test on cast VAR/NCAR to bit precision integer(X) unsigned
select cast(N'1000' as bit precision integer(15) UNSIGNED) from dual;
select cast(N'0' as bit precision integer(15) UNSIGNED) from dual;
select cast(N'32767' as bit precision integer(15) UNSIGNED) from dual;

-- negative test on cast VAR/NCAR to bit precision integer(X) unsigned
select cast(N'32768' as bit precision integer(15) UNSIGNED) from dual;
select cast(N'-1' as bit precision integer(15) UNSIGNED) from dual;

-- test on cast VAR/NCAR to integer
select cast(N'4294967295' as integer unsigned) from dual;
select cast(N'0' as integer unsigned) from dual;
select cast(N'2147483647' as integer) from dual;
select cast(N'-2147483648' as integer) from dual;

-- negative test on cast VAR/NCAR to integer
select cast(N'4294967296' as integer unsigned) from dual;
select cast(N'-1' as integer unsigned) from dual;
select cast(N'2147483648' as integer) from dual;
select cast(N'-2147483649' as integer) from dual;

-- test on cast VAR/NCAR to largeint
select cast(N'42949672960000' as largeint) from dual;

-- test on cast VAR/NCAR to DEC 
select cast(N'42949672960000' as DEC) from dual;
select cast(N'12345.56' as DEC (7,2) unsigned) from dual;

-- negative test on cast VAR/NCAR to DEC 
select cast(N'1009a' as  DEC) from dual;

-- test on cast VAR/NCAR to float/real/double 
select cast(N'-107' as float) from dual;
select cast(N'1.2E-001' as real) from dual;
select cast(N'12E-001' as double precision) from dual;

-- negative test on cast VAR/NCAR to float/real/double 
select cast(N'this is not a number.' as float) from dual;
select cast(N'1023u' as real) from dual;
select cast(N'''\2' as double precision) from dual;

-- test on cast VAR/NCAR to interval 
select cast(N'-07' as interval year) from dual;
select cast(N'5' as interval year) from dual;

-- test on cast smallint to VAR/NCAR 
select cast(cast(N'65535' as SMALLINT UNSIGNED) as NCHAR(10)) from dual;
select cast(cast(N'-5535' as SMALLINT SIGNED) as NCHAR(10)) from dual;
select cast(cast(N'65535' as SMALLINT UNSIGNED) as NCHAR(10)) from dual;
select cast(cast(N'65535' as SMALLINT UNSIGNED) as NCHAR VARYING(10)) from dual;
select cast(cast(N'-5535' as SMALLINT SIGNED) as NCHAR VARYING(10)) from dual;

-- negative test on cast smallint to VAR/NCAR 
select cast(cast(N'65535' as SMALLINT UNSIGNED) as NCHAR(1)) from dual;
select cast(cast(N'-5535' as SMALLINT SIGNED) as NCHAR VARYING(1)) from dual;

-- test on cast integer to VAR/NCAR 
select cast(cast(N'4294967295' as integer unsigned) as NCHAR VARYING(10)) from dual;
select cast(cast(N'0' as integer unsigned) as NCHAR(2)) from dual;
select cast(cast(N'2147483647' as integer) as NCHAR(12)) from dual;
select cast(cast(N'-2147483648' as integer) as NCHAR(12)) from dual;

-- negative test on cast integer to VAR/NCAR
select cast(cast(N'4294967296' as integer unsigned) as nchar(1)) from dual;
select cast(cast(N'-10' as integer) as nchar(1)) from dual;
select cast(cast(N'2147483648' as integer) as nchar varying(1)) from dual;
select cast(cast(N'-2147483649' as integer) as nchar(1)) from dual;

-- test on cast DEC to VAR/NCAR 
select cast(cast(N'42949672960000' as DEC) as nchar(10)) from dual;
select cast(cast(N'12345.56' as DEC (7,2) unsigned) as nchar(20)) from dual;

-- "timestamp - timestamp" should return result in seconds
select timestamp '2018-10-10 10:10:10' - timestamp '2017-10-10 10:10:10' from dual;

-- negative test on cast DEC to VAR/NCAR 
select cast(cast(N'12345.56' as DEC (7,2) unsigned) as nchar(2)) from dual;

-- test on cast date, time and timestamp to VAR/NCAR 
select cast(date '1998-03-02' as nchar varying(11)) from dual;
select cast(date '1998-03-02' as nchar(11)) from dual;
select cast(time '08:03:02' as nchar(11)) from dual;
select cast(time '08:03:02 pm' as nchar(11)) from dual;
select cast(timestamp 
'1998-06-03 08:03:02.000000' as nchar varying(21)) from dual;
select cast(timestamp '1998-06-03 08:03:02.000000' as nchar(21)) from dual;
-- Genesis case-10-050208-6275
-- Test on ascii to time

select cast('10:13:14' as time(4)),cast('10:13:14' as time(4)) from (values(1)) as x;
select cast('10:13:14' as time(4)),cast('10:13:14' as time(4)),current_timestamp from (values(1)) as x;
--test on precision of time
select cast('10:13:14' as time(10)),cast('10:13:14' as time(4)) from dual;
--
-- testing translate.  1998 6/12
--
--  Syntax: translate(expr using translation)
--
-- 	where translation can be 
--  		1. ISO88591TOUCS2
--  		2. UCS2TOISO88591 
--  For case 1 and 2, the expr has to be [VAR]CHAR(X) CHARACTERSET ISO88591. 
--  The result is NCHAR VARYING(Y) CHARACTERSET UNICODE, where Y = X for both 
--  case 1 and 2.
--
--  IF expr is NULL, the result is NULL.
--

create table t022u2 (a char(10) default NULL,
		     b nchar(10) default NULL,
		     c int
		    );

insert into t022u2(c) values(1);
insert into t022u2(c) values(2);
insert into t022u2 values('abcdefg', N'abcdefg', 3);
insert into t022u2 values('hijkl', N'hijkl', 4);

select translate(a using iso88591ToUcs2) from t022u2;
select translate(a using iso88591ToUCS2) from t022u2;
select translate(a using ISO88591TOUCS2) from t022u2;

select translate(b using ucs2Toiso88591) from t022u2;

-- some negative tests
select translate(b using ucs2Toiso88591) from t022u2; -- wrong mapping name
select translate(a using ucs2Toiso88591) from t022u2; -- character set mismatch 
select translate(b using ISO88591TOUCS2) from t022u2; -- charactet set mismatch
select translate(123 using ISO88591TOUCS2) from t022u2; -- not a char data type
select translate(12E-01 using ISO88591TOUCS2) from t022u2; -- not a char data type
select translate(-123 using ucs2Toiso88591) from t022u2; -- not a char data type
select translate(123.3 using ucs2Toiso88591) from t022u2; -- not a char data type
select translate(date '1998-06-15' using ucs2Toiso88591) from t022u2; -- not a char 
select translate(time '08:03:02 pm' using iso88591ToUcs) from t022u2; -- not a char 
select translate(timestamp '1998-06-03 08:03:02.000000' using iso88591ToUcs2) 
from t022u2; -- not a char 

-- New PCode comparison instructions for float32 and float64
create table t022fltcmp(c0 char(23), c1 real not null, c2 float not null);
-- Insert some rows
insert into t022fltcmp values ('unaligned float access', 0, 20.12345),
                              ('unaligned', 0, 20.56789);
insert into t022fltcmp values('unaligned float', 10.12345,
                               1.7976931348623157e+308);
insert into t022fltcmp values('unaligned access', 3.40282346e+38, 10.12345);
insert into t022fltcmp values('float access', 1.17549436e-38,
                               2.2250738585072014e-308);

select * from t022fltcmp;

-- Start the basic tests
-- New PCode instructions for FLOAT64 and FLOAT32: GT and LE
select c1 - 20.00, c2 + 20.00 from t022fltcmp where c1 > 10.02 and c2 <= 30.02;
select c1 - 20.00, c2 + 20.00 from t022fltcmp where c1 > c1 and c2 <= 30.02;
select c1 - 20.00, c2 + 20.00 from t022fltcmp where c1 <= 20.02 and c2 > 30.02;
select c1 - 20.00, c2 + 20.00 from t022fltcmp where c1 <= c1 and c2 > 30.02;

-- New PCode instructions for FLOAT64 and FLOAT32: GE and LT
select c2 * 20.00 from t022fltcmp where c1 >= 20.12345 and c2 < 1.7976E208;
select c2 * 20.00 from t022fltcmp where c1 >= c1 and c2 < 1.7976E208;
select c1 * 20.00 from t022fltcmp where c1 < 20.12345
                                    and c2 >= 2.2250738585072014e-308;
select c1 * 20.00 from t022fltcmp where c1 < c1
                                    and c2 >= 2.2250738585072014e-308;

-- New PCode instructions for FLOAT64 and FLOAT32: EQ and NE
select c2 from t022fltcmp where c1 = 1.17549436e-38 and
                                c2 <> 1.7976931348623157e+308;
select c2 from t022fltcmp where c1 = c1 and
                                c2 <> 1.7976931348623157e+308;
select c1 from t022fltcmp where c1 <> 10.12345
                            and c2 = 2.2250738585072014e-308;
select c1 from t022fltcmp where c1 <> c1
                            and c2 = 2.2250738585072014e-308;

-- PCode bug fix validation
-- PCode constant and jump table setup for ALM CR5679 (64-bit)
create table t022bug (t1  date  not null
                    , f2  real not null
                    , z   char (8)
                    , vc1 varchar(15)
                    , primary key (t1)) no partition;
create view vt022bug as select * from t022bug where trim(z) < 'Row05' or
                       vc1 in ('~!#$%^\&', '~', '~');
insert into t022bug values ( date '1959-12-31', 9, null, '~!#$%^\&');

-- Expect 1 row selected
select f2 from vt022bug where f2 > 8.2399999E-1 and f2 < 9.1234567E01
       group by vc1, f2 having vc1 in ('~', '~!#$%^\&');

--to test comparison between shorter signed and longer unsigned
--comparisons that involve implicit typecast of both operands
--to signed int.
create table t022tg (iu int unsigned, ss smallint);
insert into t022tg values(4294967295, -1),(3000000000,-1),(4294967295,32765);

--Must return zero rows.
select * from t022tg where -1>iu;

--Must return 3 rows.
select * from t022tg where iu>ss;

--Must return zero rows.
select * from t022tg where iu<=ss;

--Must return 3 rows.
select * from t022tg where iu>=ss;

--Must return 3 rows.
select * from t022tg where ss<iu;

--Must return zero rows.
select * from t022tg where ss>iu;

--Must return 3 rows.
select * from t022tg where ss<=iu;

--Must return zero rows.
select * from t022tg where ss>=iu;

-- check for string truncation, with and without auto truncate
delete from t022u2;
insert into t022u2 (c) values (1);

-- should return truncation error
insert into t022u2 (a) values ('abcdefghijkl');
update t022u2 set a = 'abcdefghijkl';

-- should not return truncation errors.
cqd traf_string_auto_truncate 'ON';
delete from t022u2;
insert into t022u2 (a) values ('abcdefghijkl');
select * from t022u2;
update t022u2 set a = 'lkjihgfedcba';
select * from t022u2;

-- return warnings if truncation occurs
cqd traf_string_auto_truncate_warning 'ON';
delete from t022u2;
insert into t022u2 (a) values ('abcdefghijkl');
select * from t022u2;
update t022u2 set a = 'lkjihgfedcba';
select * from t022u2;

-- tests for nanosecond precision for datetime and interval
create table t022nanos(a timestamp(9), b time(9), c interval hour to second(9));
invoke t022nanos;
insert into t022nanos values (timestamp '2018-01-01 10:10:10.123456789',
                              time '10:10:10.123456789',
                              interval '10:10:10.123456789' hour to second(9));
prepare s from insert into t022nanos values (?, ?, ?);
execute s using '2019-02-02 11:11:11.987654321', '01:01:01.112233445',
                 '02:02:02.111111111';
select * from t022nanos;
select a - a, b - b, c-c, c+c, a+c, a-c from t022nanos;
select cast(a as char(30)), cast(b as char(20)), cast(c as char(20)) from t022nanos;
select * from t022nanos where a = timestamp '2018-01-01 10:10:10.123456789';
select * from t022nanos where a = timestamp '2018-01-01 10:10:10.123456788';

drop table if exists hive.hive.thivets;
create table hive.hive.thivets (a timestamp);
insert into hive.hive.thivets values (timestamp '2018-01-01 10:10:10.123456789');
select * from hive.hive.thivets;
select * from hive.hive.thivets where a = timestamp '2018-01-01 10:10:10.123456788' + interval '0.000000001' second (1,9);

select extract(second from a), extract(second from b), extract(second from c)
  from t022nanos;


-- ERROR CASES

-- total precision exceeds Int64 max.
create table t022interr(a interval second(12, 9));

select timestamp '9999-01-01 10:10:10.123456789' - timestamp '2018-01-01 10:10:10.123456' from dual;
select timestamp '2100-01-01 10:10:10.123456789' - timestamp '2018-01-01 10:10:10.123456' from dual;

-- invalid timestamp, time, interval values
execute s using '2019-02-02 11:11:11.98765432115', '01:01:01.1122334455',
                 '02:02:02.1111111115';

-- done