select '101111101'::datetime;
timestamp
---------------------
2010-11-11 10:01:00
(1 row)
select '2010117.5678'::datetime;
WARNING: date/time field value out of range: "2010117.5678"
CONTEXT: referenced column: timestamp
timestamp
-----------
(1 row)
select '2010117.1234'::datetime;
timestamp
---------------------
2020-10-11 07:12:34
(1 row)
select '101111101'::timestamp;
timestamptz
------------------------
2010-11-11 10:01:00-08
(1 row)
select '2010117.5678'::timestamp;
WARNING: date/time field value out of range: "2010117.5678"
CONTEXT: referenced column: timestamptz
timestamptz
-------------
(1 row)
select '2010117.1234'::timestamp;
timestamptz
------------------------
2020-10-11 07:12:34-07
(1 row)
set dolphin.b_compatibility_mode to on;
select '2022-01-01'::bit(64)::datetime;
WARNING: bit string length 80 does not match type bit(64)
CONTEXT: referenced column: timestamp
WARNING: invalid input syntax for type timestamp: "��������"
CONTEXT: referenced column: timestamp
SQL function "bit_cast_datetime" statement 1
referenced column: timestamp
timestamp
-----------
(1 row)
select '2022-01-01'::bit(64)::timestamp;
WARNING: bit string length 80 does not match type bit(64)
CONTEXT: referenced column: timestamptz
WARNING: invalid input syntax for type timestamp: "��������"
CONTEXT: referenced column: timestamptz
SQL function "bit_cast_timestamp" statement 1
referenced column: timestamptz
timestamptz
-------------
(1 row)
select 8385958.999999::bit(64)::time;
WARNING: invalid input syntax for type time: "酾"
CONTEXT: referenced column: time
SQL function "bit_cast_time" statement 1
referenced column: time
time
----------
00:00:00
(1 row)
set dolphin.sql_mode='';
-- true
select '2022-1-12 12:23:23'::timestamp and '20220112122324':: timestamp;
?column?
----------
t
(1 row)
select '2023-1-12 12:23:23'::datetime and '20230112122324':: datetime;
?column?
----------
t
(1 row)
select '2022-1-12 12:23:23'::timestamp and '20220112122324':: datetime;
?column?
----------
t
(1 row)
select '2022-1-12 12:23:23'::datetime and '20220112122324':: timestamp;
?column?
----------
t
(1 row)
select timestamp '2022-1-12 12:23:23' and timestamp '20220112122324';
?column?
----------
t
(1 row)
select datetime '2023-1-12 12:23:23' and datetime '20230112122324';
?column?
----------
t
(1 row)
select timestamp '2022-1-12 12:23:23' and datetime '20220112122324';
?column?
----------
t
(1 row)
select datetime '2022-1-12 12:23:23' and timestamp '20220112122324';
?column?
----------
t
(1 row)
-- false
select '0000-00-00 00:00:00'::timestamp and '20220112122324':: timestamp;
?column?
----------
f
(1 row)
select '2023-1-12 12:23:23'::datetime and '00000000000000':: datetime;
?column?
----------
f
(1 row)
select '2022-1-12 12:23:23'::timestamp and '00000000000000':: datetime;
?column?
----------
f
(1 row)
select '2022-1-12 12:23:23'::datetime and '00000000000000':: timestamp;
?column?
----------
f
(1 row)
select timestamp '2022-1-12 12:23:23' and timestamp '00000000000000';
?column?
----------
f
(1 row)
select datetime '2023-1-12 12:23:23' and datetime '00000000000000';
?column?
----------
f
(1 row)
select timestamp '2022-1-12 12:23:23' and datetime '00000000000000';
?column?
----------
f
(1 row)
select datetime '2022-1-12 12:23:23' and timestamp '00000000000000';
?column?
----------
f
(1 row)
-- true
select '2022-1-12 12:23:23'::timestamp or '20220112122324':: timestamp;
?column?
----------
t
(1 row)
select '2023-1-12 12:23:23'::datetime or '20230112122324':: datetime;
?column?
----------
t
(1 row)
select '2022-1-12 12:23:23'::timestamp or '20220112122324':: datetime;
?column?
----------
t
(1 row)
select '2022-1-12 12:23:23'::datetime or '20220112122324':: timestamp;
?column?
----------
t
(1 row)
select timestamp '2022-1-12 12:23:23' or timestamp '20220112122324';
?column?
----------
t
(1 row)
select datetime '2023-1-12 12:23:23' or datetime '20230112122324';
?column?
----------
t
(1 row)
select timestamp '2022-1-12 12:23:23' or datetime '20220112122324';
?column?
----------
t
(1 row)
select datetime '2022-1-12 12:23:23' or timestamp '20220112122324';
?column?
----------
t
(1 row)
select '0000-00-00 00:00:00'::timestamp or '20220112122324':: timestamp;
?column?
----------
t
(1 row)
select '2023-1-12 12:23:23'::datetime or '00000000000000':: datetime;
?column?
----------
t
(1 row)
select '2022-1-12 12:23:23'::timestamp or '00000000000000':: datetime;
?column?
----------
t
(1 row)
select '2022-1-12 12:23:23'::datetime or '00000000000000':: timestamp;
?column?
----------
t
(1 row)
select timestamp '2022-1-12 12:23:23' or timestamp '00000000000000';
?column?
----------
t
(1 row)
select datetime '2023-1-12 12:23:23' or datetime '00000000000000';
?column?
----------
t
(1 row)
select timestamp '2022-1-12 12:23:23' or datetime '00000000000000';
?column?
----------
t
(1 row)
select datetime '2022-1-12 12:23:23' or timestamp '00000000000000';
?column?
----------
t
(1 row)
reset dolphin.sql_mode;
create table t_set0004(c1 int not null auto_increment primary key, c2 set('2011-11-11', '2023-02-28 11:23:00', '2024-01', '2025/01/01') default null, c3 set('red', 'yellow', 'blue') not null, c4 set('0', '1', '1.01314'));
NOTICE: CREATE TABLE will create implicit sequence "t_set0004_c1_seq" for serial column "t_set0004.c1"
NOTICE: CREATE TABLE will create implicit set "t_set0004_c2_set" for column "t_set0004.c2"
NOTICE: CREATE TABLE will create implicit set "t_set0004_c3_set" for column "t_set0004.c3"
NOTICE: CREATE TABLE will create implicit set "t_set0004_c4_set" for column "t_set0004.c4"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "t_set0004_pkey" for table "t_set0004"
insert into t_set0004(c2, c3, c4) values ('2025/01/01', 'blue', '0');
insert into t_set0004(c2, c3, c4) values ('2011-11-11,2023-02-28 11:23:00', 'red,yellow', '0,1');
insert into t_set0004(c2, c3, c4) values ('2024-01,2011-11-11,2025/01/01', 'red,blue', '0,1.01314');
insert into t_set0004(c2, c3) values ('2023-02-28 11:23:00', 'red');
insert into t_set0004(c2, c3) values ('2023-02-28 11:23:00,2025/01/01,2025/01/01', 'blue,blue,yellow');
insert into t_set0004(c3) values ('yellow');
insert into t_set0004(c3) values ('yellow,yellow,yellow,yellow');
insert into t_set0004(c3) values ('blue,yellow,red,red');
insert into t_set0004(c3) values ('blue,red');
insert into t_set0004(c3, c4) values ('red', '1');
insert into t_set0004(c3, c4) values ('red,red', '1.01314,1.01314');
insert into t_set0004(c3, c4) values ('red,blue', '0,1,1.01314');
select cast(c1 as date), cast(c2 as date), cast(c3 as date), cast(c4 as date) from t_set0004 order by 1,2,3,4;
WARNING: Out of range value for date
CONTEXT: referenced column: c1
WARNING: invalid input syntax for type date: "blue"
CONTEXT: referenced column: date
SQL function "set_date" statement 1
referenced column: c3
WARNING: invalid input syntax for type timestamp: "0"
CONTEXT: referenced column: date
SQL function "set_date" statement 1
referenced column: c4
WARNING: invalid input syntax for type date: "0"
CONTEXT: referenced column: date
SQL function "set_date" statement 1
referenced column: c4
WARNING: Out of range value for date
CONTEXT: referenced column: c1
WARNING: invalid input syntax for type date: "2011-11-11,2023-02-28 11:23:00"
CONTEXT: referenced column: date
SQL function "set_date" statement 1
referenced column: c2
WARNING: invalid input syntax for type date: "red,yellow"
CONTEXT: referenced column: date
SQL function "set_date" statement 1
referenced column: c3
WARNING: date/time field value out of range: "0,1"
CONTEXT: referenced column: date
SQL function "set_date" statement 1
referenced column: c4
WARNING: Out of range value for date
CONTEXT: referenced column: c1
WARNING: invalid input syntax for type date: "2011-11-11,2024-01,2025/01/01"
CONTEXT: referenced column: date
SQL function "set_date" statement 1
referenced column: c2
WARNING: invalid input syntax for type date: "red,blue"
CONTEXT: referenced column: date
SQL function "set_date" statement 1
referenced column: c3
WARNING: invalid input syntax for type date: "0,1.01314"
CONTEXT: referenced column: date
SQL function "set_date" statement 1
referenced column: c4
WARNING: Out of range value for date
CONTEXT: referenced column: c1
WARNING: invalid input syntax for type date: "red"
CONTEXT: referenced column: date
SQL function "set_date" statement 1
referenced column: c3
WARNING: Out of range value for date
CONTEXT: referenced column: c1
WARNING: invalid input syntax for type date: "yellow,blue"
CONTEXT: referenced column: date
SQL function "set_date" statement 1
referenced column: c3
WARNING: Out of range value for date
CONTEXT: referenced column: c1
WARNING: invalid input syntax for type date: "yellow"
CONTEXT: referenced column: date
SQL function "set_date" statement 1
referenced column: c3
WARNING: Out of range value for date
CONTEXT: referenced column: c1
WARNING: invalid input syntax for type date: "yellow"
CONTEXT: referenced column: date
SQL function "set_date" statement 1
referenced column: c3
WARNING: Out of range value for date
CONTEXT: referenced column: c1
WARNING: invalid input syntax for type date: "red,yellow,blue"
CONTEXT: referenced column: date
SQL function "set_date" statement 1
referenced column: c3
WARNING: Out of range value for date
CONTEXT: referenced column: c1
WARNING: invalid input syntax for type date: "red,blue"
CONTEXT: referenced column: date
SQL function "set_date" statement 1
referenced column: c3
WARNING: Out of range value for date
CONTEXT: referenced column: c1
WARNING: invalid input syntax for type date: "red"
CONTEXT: referenced column: date
SQL function "set_date" statement 1
referenced column: c3
WARNING: invalid input syntax for type timestamp: "1"
CONTEXT: referenced column: date
SQL function "set_date" statement 1
referenced column: c4
WARNING: invalid input syntax for type date: "1"
CONTEXT: referenced column: date
SQL function "set_date" statement 1
referenced column: c4
WARNING: Out of range value for date
CONTEXT: referenced column: c1
WARNING: invalid input syntax for type date: "red"
CONTEXT: referenced column: date
SQL function "set_date" statement 1
referenced column: c3
WARNING: Out of range value for date
CONTEXT: referenced column: c1
WARNING: invalid input syntax for type date: "red,blue"
CONTEXT: referenced column: date
SQL function "set_date" statement 1
referenced column: c3
WARNING: invalid input syntax for type date: "0,1,1.01314"
CONTEXT: referenced column: date
SQL function "set_date" statement 1
referenced column: c4
c1 | c2 | c3 | c4
----+------------+----+------------
| | |
| | |
| | |
| | |
| | |
| | |
| | |
| | |
| | | 2001-01-31
| 2023-02-28 | |
| 2023-02-28 | |
| 2025-01-01 | |
(12 rows)
select cast(c1 as datetime), cast(c2 as datetime), cast(c3 as datetime), cast(c4 as datetime) from t_set0004 order by 1,2,3,4;
WARNING: timestamp out of range
CONTEXT: referenced column: c1
WARNING: invalid input syntax for type timestamp: "blue"
CONTEXT: referenced column: c3
WARNING: invalid input syntax for type timestamp: "0"
CONTEXT: referenced column: c4
WARNING: timestamp out of range
CONTEXT: referenced column: c1
WARNING: invalid input syntax for type timestamp: "2011-11-11,2023-02-28 11:23:00"
CONTEXT: referenced column: c2
WARNING: invalid input syntax for type timestamp: "red,yellow"
CONTEXT: referenced column: c3
WARNING: date/time field value out of range: "0,1"
CONTEXT: referenced column: c4
WARNING: timestamp out of range
CONTEXT: referenced column: c1
WARNING: invalid input syntax for type timestamp: "2011-11-11,2024-01,2025/01/01"
CONTEXT: referenced column: c2
WARNING: invalid input syntax for type timestamp: "red,blue"
CONTEXT: referenced column: c3
WARNING: invalid input syntax for type timestamp: "0,1.01314"
CONTEXT: referenced column: c4
WARNING: timestamp out of range
CONTEXT: referenced column: c1
WARNING: invalid input syntax for type timestamp: "red"
CONTEXT: referenced column: c3
WARNING: timestamp out of range
CONTEXT: referenced column: c1
WARNING: invalid input syntax for type timestamp: "yellow,blue"
CONTEXT: referenced column: c3
WARNING: timestamp out of range
CONTEXT: referenced column: c1
WARNING: invalid input syntax for type timestamp: "yellow"
CONTEXT: referenced column: c3
WARNING: timestamp out of range
CONTEXT: referenced column: c1
WARNING: invalid input syntax for type timestamp: "yellow"
CONTEXT: referenced column: c3
WARNING: timestamp out of range
CONTEXT: referenced column: c1
WARNING: invalid input syntax for type timestamp: "red,yellow,blue"
CONTEXT: referenced column: c3
WARNING: timestamp out of range
CONTEXT: referenced column: c1
WARNING: invalid input syntax for type timestamp: "red,blue"
CONTEXT: referenced column: c3
WARNING: timestamp out of range
CONTEXT: referenced column: c1
WARNING: invalid input syntax for type timestamp: "red"
CONTEXT: referenced column: c3
WARNING: invalid input syntax for type timestamp: "1"
CONTEXT: referenced column: c4
WARNING: timestamp out of range
CONTEXT: referenced column: c1
WARNING: invalid input syntax for type timestamp: "red"
CONTEXT: referenced column: c3
WARNING: timestamp out of range
CONTEXT: referenced column: c1
WARNING: invalid input syntax for type timestamp: "red,blue"
CONTEXT: referenced column: c3
WARNING: invalid input syntax for type timestamp: "0,1,1.01314"
CONTEXT: referenced column: c4
c1 | c2 | c3 | c4
----+---------------------+----+---------------------
| | |
| | |
| | |
| | |
| | |
| | |
| | |
| | |
| | | 2001-01-31 04:00:00
| 2023-02-28 11:23:00 | |
| 2023-02-28 11:23:00 | |
| 2025-01-01 00:00:00 | |
(12 rows)
create table t_tinyint0003(
c1 tinyint,
c2 tinyint(1),
c3 tinyint(255),
c4 int1,
c5 tinyint,
c6 tinyint(1) default '0',
c7 tinyint(10) not null default '0',
c8 int1 default '0');
insert into t_tinyint0003 values (0, 0, 0, 0, 0, 0, 0, 0);
create table t_tinyint0004(
c1 int1,
c2 int2,
c3 int4,
c4 int8,
c5 uint1,
c6 uint2,
c7 uint4,
c8 uint8,
c9 float4,
c10 float8,
c11 numeric
);
insert into t_tinyint0004 values (0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0);
select cast(0 as datetime);
WARNING: timestamp out of range
CONTEXT: referenced column: timestamp
timestamp
-----------
(1 row)
select cast(0 as timestamp);
WARNING: timestamp out of range
CONTEXT: referenced column: timestamptz
timestamptz
-------------
(1 row)
select cast(c1 as datetime), cast(c2 as datetime), cast(c3 as datetime),cast(c4 as datetime), cast(c5 as datetime), cast(c6 as datetime), cast(c7 as datetime), cast(c8 as datetime) from t_tinyint0003 order by 1;
WARNING: timestamp out of range
CONTEXT: referenced column: c1
WARNING: timestamp out of range
CONTEXT: referenced column: c2
WARNING: timestamp out of range
CONTEXT: referenced column: c3
WARNING: timestamp out of range
CONTEXT: referenced column: c4
WARNING: timestamp out of range
CONTEXT: referenced column: c5
WARNING: timestamp out of range
CONTEXT: referenced column: c6
WARNING: timestamp out of range
CONTEXT: referenced column: c7
WARNING: timestamp out of range
CONTEXT: referenced column: c8
c1 | c2 | c3 | c4 | c5 | c6 | c7 | c8
----+----+----+----+----+----+----+----
| | | | | | |
(1 row)
select cast(c1 as datetime), cast(c2 as datetime), cast(c3 as datetime),cast(c4 as datetime), cast(c5 as datetime), cast(c6 as datetime), cast(c7 as datetime), cast(c8 as datetime), cast(c9 as datetime), cast(c10 as datetime), cast(c11 as datetime) from t_tinyint0004 order by 1;
WARNING: timestamp out of range
CONTEXT: referenced column: c1
WARNING: timestamp out of range
CONTEXT: referenced column: c2
WARNING: timestamp out of range
CONTEXT: referenced column: c3
WARNING: timestamp out of range
CONTEXT: referenced column: c4
WARNING: timestamp out of range
CONTEXT: referenced column: c5
WARNING: timestamp out of range
CONTEXT: referenced column: c6
WARNING: timestamp out of range
CONTEXT: referenced column: c7
WARNING: timestamp out of range
CONTEXT: referenced column: c8
WARNING: date/time field value out of range: "000000"
CONTEXT: referenced column: c9
WARNING: date/time field value out of range: "000000"
CONTEXT: referenced column: c10
WARNING: date/time field value out of range: "000000"
CONTEXT: referenced column: c11
c1 | c2 | c3 | c4 | c5 | c6 | c7 | c8 | c9 | c10 | c11
----+----+----+----+----+----+----+----+----+-----+-----
| | | | | | | | | |
(1 row)
set dolphin.sql_mode = 'sql_mode_strict,sql_mode_full_group,pipes_as_concat,ansi_quotes,pad_char_to_full_length,auto_recompile_function,error_for_division_by_zero';
select cast(0 as datetime);
timestamp
---------------------
0000-00-00 00:00:00
(1 row)
select cast(0 as timestamp);
timestamptz
------------------------
0000-00-00 00:00:00-08
(1 row)
select cast(c1 as datetime), cast(c2 as datetime), cast(c3 as datetime),cast(c4 as datetime), cast(c5 as datetime), cast(c6 as datetime), cast(c7 as datetime) from t_tinyint0003 order by 1;
c1 | c2 | c3 | c4 | c5 | c6 | c7
---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------
0000-00-00 00:00:00 | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 | 0000-00-00 00:00:00
(1 row)
select cast(c1 as datetime), cast(c2 as datetime), cast(c3 as datetime),cast(c4 as datetime), cast(c5 as datetime), cast(c6 as datetime), cast(c7 as datetime), cast(c8 as datetime), cast(c9 as datetime), cast(c10 as datetime), cast(c11 as datetime) from t_tinyint0004 order by 1;
c1 | c2 | c3 | c4 | c5 | c6 | c7 | c8 | c9 | c10 | c11
---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------
0000-00-00 00:00:00 | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 | 0000-00-00 00:00:00
(1 row)
set dolphin.sql_mode = 'no_zero_date';
select cast(0 as datetime);
WARNING: timestamp out of range
CONTEXT: referenced column: timestamp
timestamp
-----------
(1 row)
select cast(0 as timestamp);
WARNING: timestamp out of range
CONTEXT: referenced column: timestamptz
timestamptz
-------------
(1 row)
select cast(c1 as datetime), cast(c2 as datetime), cast(c3 as datetime),cast(c4 as datetime), cast(c5 as datetime), cast(c6 as datetime), cast(c7 as datetime) from t_tinyint0003 order by 1;
WARNING: timestamp out of range
CONTEXT: referenced column: c1
WARNING: timestamp out of range
CONTEXT: referenced column: c2
WARNING: timestamp out of range
CONTEXT: referenced column: c3
WARNING: timestamp out of range
CONTEXT: referenced column: c4
WARNING: timestamp out of range
CONTEXT: referenced column: c5
WARNING: timestamp out of range
CONTEXT: referenced column: c6
WARNING: timestamp out of range
CONTEXT: referenced column: c7
c1 | c2 | c3 | c4 | c5 | c6 | c7
----+----+----+----+----+----+----
| | | | | |
(1 row)
select cast(c1 as datetime), cast(c2 as datetime), cast(c3 as datetime),cast(c4 as datetime), cast(c5 as datetime), cast(c6 as datetime), cast(c7 as datetime), cast(c8 as datetime), cast(c9 as datetime), cast(c10 as datetime), cast(c11 as datetime) from t_tinyint0004 order by 1;
WARNING: timestamp out of range
CONTEXT: referenced column: c1
WARNING: timestamp out of range
CONTEXT: referenced column: c2
WARNING: timestamp out of range
CONTEXT: referenced column: c3
WARNING: timestamp out of range
CONTEXT: referenced column: c4
WARNING: timestamp out of range
CONTEXT: referenced column: c5
WARNING: timestamp out of range
CONTEXT: referenced column: c6
WARNING: timestamp out of range
CONTEXT: referenced column: c7
WARNING: timestamp out of range
CONTEXT: referenced column: c8
WARNING: date/time field value out of range: "000000"
CONTEXT: referenced column: c9
WARNING: date/time field value out of range: "000000"
CONTEXT: referenced column: c10
WARNING: date/time field value out of range: "000000"
CONTEXT: referenced column: c11
c1 | c2 | c3 | c4 | c5 | c6 | c7 | c8 | c9 | c10 | c11
----+----+----+----+----+----+----+----+----+-----+-----
| | | | | | | | | |
(1 row)
drop table t_tinyint0003;
drop table t_tinyint0004;
reset dolphin.sql_mode;
