create schema sql_mode_strict;
set current_schema to 'sql_mode_strict';
set dolphin.sql_mode = 'pad_char_to_full_length';
create table test_tint(a tinyint);
create table test_sint(a smallint);
create table test_int(a int);
create table test_bint(a bigint);
create table test_float4(a float4);
create table test_float8(a float8);
create table test_char(a char);
create table test_varchar7(a varchar(7));
--insert into table values ...
insert into test_tint(a) values(-55);
insert into test_tint(a) values(55);
insert into test_tint(a) values(355);
WARNING: tinyint out of range
CONTEXT: referenced column: a
insert into test_tint(a) values(-355);
WARNING: tinyint out of range
CONTEXT: referenced column: a
insert into test_tint(a) values('a888');
WARNING: invalid input syntax for type tinyint: "a888"
LINE 1: insert into test_tint(a) values('a888');
^
CONTEXT: referenced column: a
insert into test_tint(a) values('888aa');
WARNING: value "888aa" is out of range for type tinyint
LINE 1: insert into test_tint(a) values('888aa');
^
CONTEXT: referenced column: a
insert into test_tint(a) values('-88aa');
WARNING: invalid input syntax for type tinyint: "-88aa"
LINE 1: insert into test_tint(a) values('-88aa');
^
CONTEXT: referenced column: a
insert into test_tint(a) values(123423.55);
WARNING: tinyint out of range
CONTEXT: referenced column: a
insert into test_tint(a) values(12.55);
insert into test_tint(a) values(-12.55);
insert into test_tint(a) values(-12123451.55);
WARNING: tinyint out of range
CONTEXT: referenced column: a
insert into test_tint(a) values(1.4e300);
WARNING: tinyint out of range
CONTEXT: referenced column: a
insert into test_tint(a) values(-1.4e300);
WARNING: tinyint out of range
CONTEXT: referenced column: a
select * from test_tint;
a
------
-55
55
127
-128
0
127
-88
127
13
-13
-128
127
-128
(13 rows)
insert into test_sint(a) values(-55);
insert into test_sint(a) values(55);
insert into test_sint(a) values(35589898923423423423423423);
WARNING: smallint out of range
CONTEXT: referenced column: a
insert into test_sint(a) values(-3553434343434343432434);
WARNING: smallint out of range
CONTEXT: referenced column: a
insert into test_sint(a) values('a888');
WARNING: invalid input syntax for type smallint: "a888"
LINE 1: insert into test_sint(a) values('a888');
^
CONTEXT: referenced column: a
insert into test_sint(a) values('88123423433834343423434aa');
WARNING: value "88123423433834343423434aa" is out of range for type smallint
LINE 1: insert into test_sint(a) values('88123423433834343423434aa')...
^
CONTEXT: referenced column: a
insert into test_sint(a) values('-88aa');
WARNING: invalid input syntax for type smallint: "-88aa"
LINE 1: insert into test_sint(a) values('-88aa');
^
CONTEXT: referenced column: a
insert into test_sint(a) values(1234233434343434343434343434343.55);
WARNING: smallint out of range
CONTEXT: referenced column: a
insert into test_sint(a) values(12.55);
insert into test_sint(a) values(-12.55);
insert into test_sint(a) values(-12123451343434343434343434343434.55);
WARNING: smallint out of range
CONTEXT: referenced column: a
insert into test_sint(a) values(1.4e300);
WARNING: smallint out of range
CONTEXT: referenced column: a
insert into test_sint(a) values(-1.4e300);
WARNING: smallint out of range
CONTEXT: referenced column: a
select * from test_sint;
a
--------
-55
55
32767
-32768
0
32767
-88
32767
13
-13
-32768
32767
-32768
(13 rows)
insert into test_int(a) values(-55);
insert into test_int(a) values(55);
insert into test_int(a) values(35589898923423423423423423);
WARNING: integer out of range
CONTEXT: referenced column: a
insert into test_int(a) values(-3553434343434343432434);
WARNING: integer out of range
CONTEXT: referenced column: a
insert into test_int(a) values('a888');
WARNING: invalid input syntax for type integer: "a888"
LINE 1: insert into test_int(a) values('a888');
^
CONTEXT: referenced column: a
insert into test_int(a) values('88123423433834343423434aa');
WARNING: value "88123423433834343423434aa" is out of range for type integer
LINE 1: insert into test_int(a) values('88123423433834343423434aa');
^
CONTEXT: referenced column: a
insert into test_int(a) values('-88aa');
WARNING: invalid input syntax for type integer: "-88aa"
LINE 1: insert into test_int(a) values('-88aa');
^
CONTEXT: referenced column: a
insert into test_int(a) values(1234233434343434343434343434343.55);
WARNING: integer out of range
CONTEXT: referenced column: a
insert into test_int(a) values(12.55);
insert into test_int(a) values(-12.55);
insert into test_int(a) values(-12123451343434343434343434343434.55);
WARNING: integer out of range
CONTEXT: referenced column: a
insert into test_int(a) values(1.4e300);
WARNING: integer out of range
CONTEXT: referenced column: a
insert into test_int(a) values(-1.4e300);
WARNING: integer out of range
CONTEXT: referenced column: a
select * from test_int;
a
-------------
-55
55
2147483647
-2147483648
0
2147483647
-88
2147483647
13
-13
-2147483648
2147483647
-2147483648
(13 rows)
insert into test_bint(a) values(-55);
insert into test_bint(a) values(55);
insert into test_bint(a) values(35589898923423423423423423);
WARNING: bigint out of range
CONTEXT: referenced column: a
insert into test_bint(a) values(-3553434343434343432434);
WARNING: bigint out of range
CONTEXT: referenced column: a
insert into test_bint(a) values('a888');
WARNING: invalid input syntax for type bigint: "a888"
LINE 1: insert into test_bint(a) values('a888');
^
CONTEXT: referenced column: a
insert into test_bint(a) values('88123423433834343423434aa');
WARNING: value "88123423433834343423434aa" is out of range for type bigint
LINE 1: insert into test_bint(a) values('88123423433834343423434aa')...
^
CONTEXT: referenced column: a
insert into test_bint(a) values('-88aa');
WARNING: invalid input syntax for type bigint: "-88aa"
LINE 1: insert into test_bint(a) values('-88aa');
^
CONTEXT: referenced column: a
insert into test_bint(a) values(1234233434343434343434343434343.55);
WARNING: bigint out of range
CONTEXT: referenced column: a
insert into test_bint(a) values(12.55);
insert into test_bint(a) values(-12.55);
insert into test_bint(a) values(-12123451343434343434343434343434.55);
WARNING: bigint out of range
CONTEXT: referenced column: a
insert into test_bint(a) values(1.4e300);
WARNING: bigint out of range
CONTEXT: referenced column: a
insert into test_bint(a) values(-1.4e300);
WARNING: bigint out of range
CONTEXT: referenced column: a
select * from test_bint;
a
----------------------
-55
55
9223372036854775807
-9223372036854775808
0
9223372036854775807
-88
9223372036854775807
13
-13
-9223372036854775808
9223372036854775807
-9223372036854775808
(13 rows)
insert into test_float4(a) values(3.4e37);
insert into test_float4(a) values(-3.4e37);
select * from test_float4;
a
----------
3.4e+37
-3.4e+37
(2 rows)
insert into test_float8(a) values(3.4e307);
insert into test_float8(a) values(-3.4e307);
select * from test_float8;
a
-----------
3.4e+307
-3.4e+307
(2 rows)
insert into test_char(a) values('adsfsdaf');
insert into test_char(a) values(-1234998.55);
insert into test_char(a) values(13434.77);
insert into test_char(a) values(13);
select * from test_char;
a
---
a
-
1
1
(4 rows)
insert into test_varchar7(a) values('adsfsdaf');
WARNING: value too long for type character varying(7)
CONTEXT: referenced column: a
insert into test_varchar7(a) values('-1234998.55');
WARNING: value too long for type character varying(7)
CONTEXT: referenced column: a
insert into test_varchar7(a) values(13434.77);
WARNING: value too long for type character varying(7)
CONTEXT: referenced column: a
insert into test_varchar7(a) values(13434.8);
select * from test_varchar7;
a
---------
adsfsda
-123499
13434.7
13434.8
(4 rows)
--insert into table select ...
create table test_tint1(a tinyint);
create table test_sint1(a smallint);
create table test_int1(a int);
create table test_bint1(a bigint);
create table test_char1(a char);
create table test_varchar7_1(a varchar(7));
insert into test_tint1 select * from test_sint;
WARNING: tinyint out of range
CONTEXT: referenced column: a
WARNING: tinyint out of range
CONTEXT: referenced column: a
WARNING: tinyint out of range
CONTEXT: referenced column: a
WARNING: tinyint out of range
CONTEXT: referenced column: a
WARNING: tinyint out of range
CONTEXT: referenced column: a
WARNING: tinyint out of range
CONTEXT: referenced column: a
WARNING: tinyint out of range
CONTEXT: referenced column: a
select * from test_tint1;
a
------
-55
55
127
-128
0
127
-88
127
13
-13
-128
127
-128
(13 rows)
delete from test_tint1;
insert into test_tint1 select * from test_int;
WARNING: tinyint out of range
CONTEXT: referenced column: a
WARNING: tinyint out of range
CONTEXT: referenced column: a
WARNING: tinyint out of range
CONTEXT: referenced column: a
WARNING: tinyint out of range
CONTEXT: referenced column: a
WARNING: tinyint out of range
CONTEXT: referenced column: a
WARNING: tinyint out of range
CONTEXT: referenced column: a
WARNING: tinyint out of range
CONTEXT: referenced column: a
select * from test_tint1;
a
------
-55
55
127
-128
0
127
-88
127
13
-13
-128
127
-128
(13 rows)
delete from test_tint1;
insert into test_tint1 select * from test_bint;
WARNING: tinyint out of range
CONTEXT: referenced column: a
WARNING: tinyint out of range
CONTEXT: referenced column: a
WARNING: tinyint out of range
CONTEXT: referenced column: a
WARNING: tinyint out of range
CONTEXT: referenced column: a
WARNING: tinyint out of range
CONTEXT: referenced column: a
WARNING: tinyint out of range
CONTEXT: referenced column: a
WARNING: tinyint out of range
CONTEXT: referenced column: a
select * from test_tint1;
a
------
-55
55
127
-128
0
127
-88
127
13
-13
-128
127
-128
(13 rows)
delete from test_tint1;
insert into test_tint1 select * from test_float4;
WARNING: tinyint out of range
CONTEXT: referenced column: a
WARNING: tinyint out of range
CONTEXT: referenced column: a
select * from test_tint1;
a
------
127
-128
(2 rows)
delete from test_tint1;
insert into test_tint1 select * from test_float8;
WARNING: tinyint out of range
CONTEXT: referenced column: a
WARNING: tinyint out of range
CONTEXT: referenced column: a
select * from test_tint1;
a
------
127
-128
(2 rows)
delete from test_tint1;
insert into test_tint1 select * from test_varchar7;
WARNING: invalid input syntax for type tinyint: "adsfsda"
CONTEXT: referenced column: a
WARNING: value "-123499" is out of range for type tinyint
CONTEXT: referenced column: a
WARNING: value "13434.7" is out of range for type tinyint
CONTEXT: referenced column: a
WARNING: value "13434.8" is out of range for type tinyint
CONTEXT: referenced column: a
select * from test_tint1;
a
------
0
-128
127
127
(4 rows)
delete from test_tint1;
insert into test_tint1 select * from test_char;
WARNING: invalid input syntax for type tinyint: "a"
CONTEXT: referenced column: a
WARNING: invalid input syntax for type tinyint: "-"
CONTEXT: referenced column: a
select * from test_tint1;
a
---
0
0
1
1
(4 rows)
insert into test_sint1 select * from test_tint;
select * from test_sint1;
a
------
-55
55
127
-128
0
127
-88
127
13
-13
-128
127
-128
(13 rows)
delete from test_sint1;
insert into test_sint1 select * from test_int;
WARNING: smallint out of range
CONTEXT: referenced column: a
WARNING: smallint out of range
CONTEXT: referenced column: a
WARNING: smallint out of range
CONTEXT: referenced column: a
WARNING: smallint out of range
CONTEXT: referenced column: a
WARNING: smallint out of range
CONTEXT: referenced column: a
WARNING: smallint out of range
CONTEXT: referenced column: a
WARNING: smallint out of range
CONTEXT: referenced column: a
select * from test_sint1;
a
--------
-55
55
32767
-32768
0
32767
-88
32767
13
-13
-32768
32767
-32768
(13 rows)
delete from test_sint1;
insert into test_sint1 select * from test_bint;
WARNING: smallint out of range
CONTEXT: referenced column: a
WARNING: smallint out of range
CONTEXT: referenced column: a
WARNING: smallint out of range
CONTEXT: referenced column: a
WARNING: smallint out of range
CONTEXT: referenced column: a
WARNING: smallint out of range
CONTEXT: referenced column: a
WARNING: smallint out of range
CONTEXT: referenced column: a
WARNING: smallint out of range
CONTEXT: referenced column: a
select * from test_sint1;
a
--------
-55
55
32767
-32768
0
32767
-88
32767
13
-13
-32768
32767
-32768
(13 rows)
delete from test_sint1;
insert into test_sint1 select * from test_float4;
WARNING: smallint out of range
CONTEXT: referenced column: a
WARNING: smallint out of range
CONTEXT: referenced column: a
select * from test_sint1;
a
--------
32767
-32768
(2 rows)
delete from test_sint1;
insert into test_sint1 select * from test_float8;
WARNING: smallint out of range
CONTEXT: referenced column: a
WARNING: smallint out of range
CONTEXT: referenced column: a
select * from test_sint1;
a
--------
32767
-32768
(2 rows)
delete from test_sint1;
insert into test_sint1 select * from test_varchar7;
WARNING: invalid input syntax for type smallint: "adsfsda"
CONTEXT: referenced column: a
WARNING: value "-123499" is out of range for type smallint
CONTEXT: referenced column: a
select * from test_sint1;
a
--------
0
-32768
13435
13435
(4 rows)
delete from test_sint1;
insert into test_sint1 select * from test_char;
WARNING: invalid input syntax for type smallint: "a"
CONTEXT: referenced column: a
WARNING: invalid input syntax for type smallint: "-"
CONTEXT: referenced column: a
select * from test_sint1;
a
---
0
0
1
1
(4 rows)
insert into test_int1 select * from test_tint;
select * from test_int1;
a
------
-55
55
127
-128
0
127
-88
127
13
-13
-128
127
-128
(13 rows)
delete from test_int1;
insert into test_int1 select * from test_sint;
select * from test_int1;
a
--------
-55
55
32767
-32768
0
32767
-88
32767
13
-13
-32768
32767
-32768
(13 rows)
delete from test_int1;
insert into test_int1 select * from test_bint;
WARNING: integer out of range
CONTEXT: referenced column: a
WARNING: integer out of range
CONTEXT: referenced column: a
WARNING: integer out of range
CONTEXT: referenced column: a
WARNING: integer out of range
CONTEXT: referenced column: a
WARNING: integer out of range
CONTEXT: referenced column: a
WARNING: integer out of range
CONTEXT: referenced column: a
WARNING: integer out of range
CONTEXT: referenced column: a
select * from test_int1;
a
-------------
-55
55
2147483647
-2147483648
0
2147483647
-88
2147483647
13
-13
-2147483648
2147483647
-2147483648
(13 rows)
delete from test_int1;
insert into test_int1 select * from test_float4;
WARNING: integer out of range
CONTEXT: referenced column: a
WARNING: integer out of range
CONTEXT: referenced column: a
select * from test_int1;
a
-------------
2147483647
-2147483648
(2 rows)
delete from test_int1;
insert into test_int1 select * from test_float8;
WARNING: integer out of range
CONTEXT: referenced column: a
WARNING: integer out of range
CONTEXT: referenced column: a
select * from test_int1;
a
-------------
2147483647
-2147483648
(2 rows)
delete from test_int1;
insert into test_int1 select * from test_varchar7;
WARNING: invalid input syntax for type integer: "adsfsda"
CONTEXT: referenced column: a
select * from test_int1;
a
---------
0
-123499
13435
13435
(4 rows)
delete from test_int1;
insert into test_int1 select * from test_char;
WARNING: invalid input syntax for type integer: "a"
CONTEXT: referenced column: a
WARNING: invalid input syntax for type integer: "-"
CONTEXT: referenced column: a
select * from test_int1;
a
---
0
0
1
1
(4 rows)
insert into test_bint1 select * from test_tint;
select * from test_bint1;
a
------
-55
55
127
-128
0
127
-88
127
13
-13
-128
127
-128
(13 rows)
delete from test_bint1;
insert into test_bint1 select * from test_sint;
select * from test_bint1;
a
--------
-55
55
32767
-32768
0
32767
-88
32767
13
-13
-32768
32767
-32768
(13 rows)
delete from test_bint1;
insert into test_bint1 select * from test_int;
select * from test_bint1;
a
-------------
-55
55
2147483647
-2147483648
0
2147483647
-88
2147483647
13
-13
-2147483648
2147483647
-2147483648
(13 rows)
delete from test_bint1;
insert into test_bint1 select * from test_float4;
WARNING: bigint out of range
CONTEXT: referenced column: a
WARNING: bigint out of range
CONTEXT: referenced column: a
select * from test_bint1;
a
----------------------
9223372036854775807
-9223372036854775808
(2 rows)
delete from test_bint1;
insert into test_bint1 select * from test_float8;
WARNING: bigint out of range
CONTEXT: referenced column: a
WARNING: bigint out of range
CONTEXT: referenced column: a
select * from test_bint1;
a
----------------------
9223372036854775807
-9223372036854775808
(2 rows)
delete from test_bint1;
insert into test_bint1 select * from test_varchar7;
WARNING: invalid input syntax for type bigint: "adsfsda"
CONTEXT: referenced column: a
select * from test_bint1;
a
---------
0
-123499
13435
13435
(4 rows)
delete from test_bint1;
insert into test_bint1 select * from test_char;
WARNING: invalid input syntax for type bigint: "a"
CONTEXT: referenced column: a
WARNING: invalid input syntax for type bigint: "-"
CONTEXT: referenced column: a
select * from test_bint1;
a
---
0
0
1
1
(4 rows)
insert into test_char1 select * from test_tint;
select * from test_char1;
a
---
-
5
1
-
0
1
-
1
1
-
-
1
-
(13 rows)
delete from test_char1;
insert into test_char1 select * from test_sint;
select * from test_char1;
a
---
-
5
3
-
0
3
-
3
1
-
-
3
-
(13 rows)
delete from test_char1;
insert into test_char1 select * from test_int;
select * from test_char1;
a
---
-
5
2
-
0
2
-
2
1
-
-
2
-
(13 rows)
delete from test_char1;
insert into test_char1 select * from test_bint;
select * from test_char1;
a
---
-
5
9
-
0
9
-
9
1
-
-
9
-
(13 rows)
delete from test_char1;
insert into test_char1 select * from test_varchar7;
select * from test_char1;
a
---
a
-
1
1
(4 rows)
insert into test_varchar7_1 select * from test_tint;
select * from test_varchar7_1;
a
------
-55
55
127
-128
0
127
-88
127
13
-13
-128
127
-128
(13 rows)
delete from test_varchar7_1;
insert into test_varchar7_1 select * from test_sint;
select * from test_varchar7_1;
a
--------
-55
55
32767
-32768
0
32767
-88
32767
13
-13
-32768
32767
-32768
(13 rows)
delete from test_varchar7_1;
insert into test_varchar7_1 select * from test_int;
WARNING: value too long for type character varying(7)
CONTEXT: referenced column: a
WARNING: value too long for type character varying(7)
CONTEXT: referenced column: a
WARNING: value too long for type character varying(7)
CONTEXT: referenced column: a
WARNING: value too long for type character varying(7)
CONTEXT: referenced column: a
WARNING: value too long for type character varying(7)
CONTEXT: referenced column: a
WARNING: value too long for type character varying(7)
CONTEXT: referenced column: a
WARNING: value too long for type character varying(7)
CONTEXT: referenced column: a
select * from test_varchar7_1;
a
---------
-55
55
2147483
-214748
0
2147483
-88
2147483
13
-13
-214748
2147483
-214748
(13 rows)
delete from test_varchar7_1;
insert into test_varchar7_1 select * from test_bint;
WARNING: value too long for type character varying(7)
CONTEXT: referenced column: a
WARNING: value too long for type character varying(7)
CONTEXT: referenced column: a
WARNING: value too long for type character varying(7)
CONTEXT: referenced column: a
WARNING: value too long for type character varying(7)
CONTEXT: referenced column: a
WARNING: value too long for type character varying(7)
CONTEXT: referenced column: a
WARNING: value too long for type character varying(7)
CONTEXT: referenced column: a
WARNING: value too long for type character varying(7)
CONTEXT: referenced column: a
select * from test_varchar7_1;
a
---------
-55
55
9223372
-922337
0
9223372
-88
9223372
13
-13
-922337
9223372
-922337
(13 rows)
delete from test_varchar7_1;
insert into test_varchar7_1 select * from test_char;
select * from test_varchar7_1;
a
---
a
-
1
1
(4 rows)
--A non-null column is not inserted when executing insert into clause, and this non-null column has no default value
create table test_notnull_tint(a tinyint, b tinyint not null);
create table test_notnull_sint(a smallint, b smallint not null);
create table test_notnull_int(a int, b int not null);
create table test_notnull_bint(a bigint, b bigint not null);
create table test_notnull_char(a char, b char not null);
create table test_notnull_varchar(a varchar(5), b varchar(5) not null);
create table test_notnull_clob(a clob, b clob not null);
create table test_notnull_text(a text, b text not null);
create table test_notnull_real(a real, b real not null);
create table test_notnull_double(a double, b double not null);
create table test_notnull_numeric(a numeric(5,2), b numeric(5,2) not null);
insert into test_notnull_tint(a) values(1);
select * from test_notnull_tint;
a | b
---+---
1 | 0
(1 row)
insert into test_notnull_sint(a) values(1);
select * from test_notnull_sint;
a | b
---+---
1 | 0
(1 row)
insert into test_notnull_int(a) values(1);
select * from test_notnull_int;
a | b
---+---
1 | 0
(1 row)
insert into test_notnull_bint(a) values(1);
select * from test_notnull_bint;
a | b
---+---
1 | 0
(1 row)
insert into test_notnull_char(a) values(1);
select * from test_notnull_char;
a | b
---+---
1 |
(1 row)
insert into test_notnull_varchar(a) values(1);
select * from test_notnull_varchar;
a | b
---+---
1 |
(1 row)
insert into test_notnull_clob(a) values(1);
select * from test_notnull_clob;
a | b
---+---
1 |
(1 row)
insert into test_notnull_text(a) values(1);
select * from test_notnull_text;
a | b
---+---
1 |
(1 row)
insert into test_notnull_real(a) values(1);
select * from test_notnull_real;
a | b
---+---
1 | 0
(1 row)
insert into test_notnull_double(a) values(1);
select * from test_notnull_double;
a | b
---+---
1 | 0
(1 row)
insert into test_notnull_numeric(a) values(1);
WARNING: invalid input syntax for numeric: ""
CONTEXT: referenced column: b
select * from test_notnull_numeric;
a | b
------+------
1.00 | 0.00
(1 row)
--insert into table values(null)
insert into test_notnull_tint(b) values(null);
ERROR: null value in column "b" violates not-null constraint
DETAIL: Failing row contains (null, null).
insert into test_notnull_sint(b) values(null);
ERROR: null value in column "b" violates not-null constraint
DETAIL: Failing row contains (null, null).
insert into test_notnull_int(b) values(null);
ERROR: null value in column "b" violates not-null constraint
DETAIL: Failing row contains (null, null).
insert into test_notnull_bint(b) values(null);
ERROR: null value in column "b" violates not-null constraint
DETAIL: Failing row contains (null, null).
insert into test_notnull_char(b) values(null);
ERROR: null value in column "b" violates not-null constraint
DETAIL: Failing row contains (null, null).
insert into test_notnull_varchar(b) values(null);
ERROR: null value in column "b" violates not-null constraint
DETAIL: Failing row contains (null, null).
insert into test_notnull_clob(b) values(null);
ERROR: null value in column "b" violates not-null constraint
DETAIL: Failing row contains (null, null).
insert into test_notnull_text(b) values(null);
ERROR: null value in column "b" violates not-null constraint
DETAIL: Failing row contains (null, null).
insert into test_notnull_real(b) values(null);
ERROR: null value in column "b" violates not-null constraint
DETAIL: Failing row contains (null, null).
insert into test_notnull_double(b) values(null);
ERROR: null value in column "b" violates not-null constraint
DETAIL: Failing row contains (null, null).
insert into test_notnull_numeric(b) values(null);
ERROR: null value in column "b" violates not-null constraint
DETAIL: Failing row contains (null, null).
--insert into table(a) values(...,...)
insert into test_notnull_tint(a) values(2,2);
ERROR: INSERT has more expressions than target columns
LINE 1: insert into test_notnull_tint(a) values(2,2);
^
insert into test_notnull_sint(a) values(2,2);
ERROR: INSERT has more expressions than target columns
LINE 1: insert into test_notnull_sint(a) values(2,2);
^
insert into test_notnull_int(a) values(2,2);
ERROR: INSERT has more expressions than target columns
LINE 1: insert into test_notnull_int(a) values(2,2);
^
insert into test_notnull_bint(a) values(2,2);
ERROR: INSERT has more expressions than target columns
LINE 1: insert into test_notnull_bint(a) values(2,2);
^
insert into test_notnull_char(a) values(2,2);
ERROR: INSERT has more expressions than target columns
LINE 1: insert into test_notnull_char(a) values(2,2);
^
insert into test_notnull_varchar(a) values(2,2);
ERROR: INSERT has more expressions than target columns
LINE 1: insert into test_notnull_varchar(a) values(2,2);
^
insert into test_notnull_clob(a) values(2,2);
ERROR: INSERT has more expressions than target columns
LINE 1: insert into test_notnull_clob(a) values(2,2);
^
insert into test_notnull_text(a) values(2,2);
ERROR: INSERT has more expressions than target columns
LINE 1: insert into test_notnull_text(a) values(2,2);
^
insert into test_notnull_real(a) values(2,2);
ERROR: INSERT has more expressions than target columns
LINE 1: insert into test_notnull_real(a) values(2,2);
^
insert into test_notnull_double(a) values(2,2);
ERROR: INSERT has more expressions than target columns
LINE 1: insert into test_notnull_double(a) values(2,2);
^
insert into test_notnull_numeric(a) values(2,2);
ERROR: INSERT has more expressions than target columns
LINE 1: insert into test_notnull_numeric(a) values(2,2);
^
--insert into table values(...,default,...)
insert into test_notnull_tint values(2,default);
select * from test_notnull_tint;
a | b
---+---
1 | 0
2 | 0
(2 rows)
insert into test_notnull_sint values(2,default);
select * from test_notnull_sint;
a | b
---+---
1 | 0
2 | 0
(2 rows)
insert into test_notnull_int values(2,default);
select * from test_notnull_int;
a | b
---+---
1 | 0
2 | 0
(2 rows)
insert into test_notnull_bint values(2,default);
select * from test_notnull_bint;
a | b
---+---
1 | 0
2 | 0
(2 rows)
insert into test_notnull_char values(2,default);
select * from test_notnull_char;
a | b
---+---
1 |
2 |
(2 rows)
insert into test_notnull_varchar values(2,default);
select * from test_notnull_varchar;
a | b
---+---
1 |
2 |
(2 rows)
insert into test_notnull_clob values(2,default);
select * from test_notnull_clob;
a | b
---+---
1 |
2 |
(2 rows)
insert into test_notnull_text values(2,default);
select * from test_notnull_text;
a | b
---+---
1 |
2 |
(2 rows)
insert into test_notnull_real values(2,default);
select * from test_notnull_real;
a | b
---+---
1 | 0
2 | 0
(2 rows)
insert into test_notnull_double values(2,default);
select * from test_notnull_double;
a | b
---+---
1 | 0
2 | 0
(2 rows)
insert into test_notnull_numeric values(2,default);
WARNING: invalid input syntax for numeric: ""
CONTEXT: referenced column: b
select * from test_notnull_numeric;
a | b
------+------
1.00 | 0.00
2.00 | 0.00
(2 rows)
create table test_multi_default(a numeric not null,b decimal not null,c real not null,d double precision not null, e float not null, f varchar(20) not null);
insert into test_multi_default values(default,default,default,default,default,default);
WARNING: invalid input syntax for numeric: ""
CONTEXT: referenced column: a
WARNING: invalid input syntax for numeric: ""
CONTEXT: referenced column: b
select * from test_multi_default;
a | b | c | d | e | f
---+---+---+---+---+---
0 | 0 | 0 | 0 | 0 |
(1 row)
--insert into table values(...),(...)
insert into test_notnull_tint(a) values(3),(4),(5);
select * from test_notnull_tint;
a | b
---+---
1 | 0
2 | 0
3 | 0
4 | 0
5 | 0
(5 rows)
insert into test_notnull_sint(a) values(3),(4),(5);
select * from test_notnull_sint;
a | b
---+---
1 | 0
2 | 0
3 | 0
4 | 0
5 | 0
(5 rows)
insert into test_notnull_int(a) values(3),(4),(5);
select * from test_notnull_int;
a | b
---+---
1 | 0
2 | 0
3 | 0
4 | 0
5 | 0
(5 rows)
insert into test_notnull_bint(a) values(3),(4),(5);
select * from test_notnull_bint;
a | b
---+---
1 | 0
2 | 0
3 | 0
4 | 0
5 | 0
(5 rows)
insert into test_notnull_char(a) values(3),(4),(5);
select * from test_notnull_char;
a | b
---+---
1 |
2 |
3 |
4 |
5 |
(5 rows)
insert into test_notnull_varchar(a) values(3),(4),(5);
select * from test_notnull_varchar;
a | b
---+---
1 |
2 |
3 |
4 |
5 |
(5 rows)
insert into test_notnull_clob(a) values(3),(4),(5);
select * from test_notnull_clob;
a | b
---+---
1 |
2 |
3 |
4 |
5 |
(5 rows)
insert into test_notnull_text(a) values(3),(4),(5);
select * from test_notnull_text;
a | b
---+---
1 |
2 |
3 |
4 |
5 |
(5 rows)
insert into test_notnull_real(a) values(3),(4),(5);
select * from test_notnull_real;
a | b
---+---
1 | 0
2 | 0
3 | 0
4 | 0
5 | 0
(5 rows)
insert into test_notnull_double(a) values(3),(4),(5);
select * from test_notnull_double;
a | b
---+---
1 | 0
2 | 0
3 | 0
4 | 0
5 | 0
(5 rows)
insert into test_notnull_numeric(a) values(3),(4),(5);
WARNING: invalid input syntax for numeric: ""
CONTEXT: referenced column: b
WARNING: invalid input syntax for numeric: ""
CONTEXT: referenced column: b
WARNING: invalid input syntax for numeric: ""
CONTEXT: referenced column: b
select * from test_notnull_numeric;
a | b
------+------
1.00 | 0.00
2.00 | 0.00
3.00 | 0.00
4.00 | 0.00
5.00 | 0.00
(5 rows)
create table mysql_strictsqlmode_t1(c1 int,c2 double precision);
insert into mysql_strictsqlmode_t1 values(1,0);
insert into mysql_strictsqlmode_t1 values(2,-99);
insert into mysql_strictsqlmode_t1 values(3,-999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999);
WARNING: "-999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999" is out of range for type double precision
CONTEXT: referenced column: c2
insert into mysql_strictsqlmode_t1 values(4,99);
insert into mysql_strictsqlmode_t1 values(5,999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999);
WARNING: "999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999" is out of range for type double precision
CONTEXT: referenced column: c2
select * from mysql_strictsqlmode_t1 order by c1;
c1 | c2
----+-------------------------
1 | 0
2 | -99
3 | -1.797693134862316e+308
4 | 99
5 | 1.797693134862316e+308
(5 rows)
drop table mysql_strictsqlmode_t1;
--test sql_mode = 'sql_mode_strict'
set dolphin.sql_mode = 'sql_mode_strict';
create table test_tint_strict(a tinyint);
create table test_sint_strict(a smallint);
create table test_int_strict(a int);
create table test_bint_strict(a bigint);
create table test_float4_strict(a float4);
create table test_float8_strict(a float8);
create table test_char_strict(a char);
create table test_varchar7_strict(a varchar(7));
--insert into table values ...
insert into test_tint_strict(a) values(-55);
insert into test_tint_strict(a) values(55);
insert into test_tint_strict(a) values(355);
ERROR: tinyint out of range
CONTEXT: referenced column: a
insert into test_tint_strict(a) values(-355);
ERROR: tinyint out of range
CONTEXT: referenced column: a
insert into test_tint_strict(a) values('a888');
ERROR: invalid input syntax for type tinyint: "a888"
LINE 1: insert into test_tint_strict(a) values('a888');
^
CONTEXT: referenced column: a
insert into test_tint_strict(a) values('888aa');
ERROR: value "888aa" is out of range for type tinyint
LINE 1: insert into test_tint_strict(a) values('888aa');
^
CONTEXT: referenced column: a
insert into test_tint_strict(a) values('-88aa');
ERROR: invalid input syntax for type tinyint: "-88aa"
LINE 1: insert into test_tint_strict(a) values('-88aa');
^
CONTEXT: referenced column: a
insert into test_tint_strict(a) values(123423.55);
ERROR: tinyint out of range
CONTEXT: referenced column: a
insert into test_tint_strict(a) values(12.55);
insert into test_tint_strict(a) values(-12.55);
insert into test_tint_strict(a) values(-12123451.55);
ERROR: tinyint out of range
CONTEXT: referenced column: a
insert into test_tint_strict(a) values(1.4e300);
ERROR: tinyint out of range
CONTEXT: referenced column: a
insert into test_tint_strict(a) values(-1.4e300);
ERROR: tinyint out of range
CONTEXT: referenced column: a
select * from test_tint_strict;
a
-----
-55
55
13
-13
(4 rows)
insert into test_sint_strict(a) values(-55);
insert into test_sint_strict(a) values(55);
insert into test_sint_strict(a) values(35589898923423423423423423);
ERROR: smallint out of range
CONTEXT: referenced column: a
insert into test_sint_strict(a) values(-3553434343434343432434);
ERROR: smallint out of range
CONTEXT: referenced column: a
insert into test_sint_strict(a) values('a888');
ERROR: invalid input syntax for type smallint: "a888"
LINE 1: insert into test_sint_strict(a) values('a888');
^
CONTEXT: referenced column: a
insert into test_sint_strict(a) values('88123423433834343423434aa');
ERROR: value "88123423433834343423434aa" is out of range for type smallint
LINE 1: insert into test_sint_strict(a) values('88123423433834343423...
^
CONTEXT: referenced column: a
insert into test_sint_strict(a) values('-88aa');
ERROR: invalid input syntax for type smallint: "-88aa"
LINE 1: insert into test_sint_strict(a) values('-88aa');
^
CONTEXT: referenced column: a
insert into test_sint_strict(a) values(1234233434343434343434343434343.55);
ERROR: smallint out of range
CONTEXT: referenced column: a
insert into test_sint_strict(a) values(12.55);
insert into test_sint_strict(a) values(-12.55);
insert into test_sint_strict(a) values(-12123451343434343434343434343434.55);
ERROR: smallint out of range
CONTEXT: referenced column: a
insert into test_sint_strict(a) values(1.4e300);
ERROR: smallint out of range
CONTEXT: referenced column: a
insert into test_sint_strict(a) values(-1.4e300);
ERROR: smallint out of range
CONTEXT: referenced column: a
select * from test_sint_strict;
a
-----
-55
55
13
-13
(4 rows)
insert into test_int_strict(a) values(-55);
insert into test_int_strict(a) values(55);
insert into test_int_strict(a) values(35589898923423423423423423);
ERROR: integer out of range
CONTEXT: referenced column: a
insert into test_int_strict(a) values(-3553434343434343432434);
ERROR: integer out of range
CONTEXT: referenced column: a
insert into test_int_strict(a) values('a888');
ERROR: invalid input syntax for type integer: "a888"
LINE 1: insert into test_int_strict(a) values('a888');
^
CONTEXT: referenced column: a
insert into test_int_strict(a) values('88123423433834343423434aa');
ERROR: value "88123423433834343423434aa" is out of range for type integer
LINE 1: insert into test_int_strict(a) values('881234234338343434234...
^
CONTEXT: referenced column: a
insert into test_int_strict(a) values('-88aa');
ERROR: invalid input syntax for type integer: "-88aa"
LINE 1: insert into test_int_strict(a) values('-88aa');
^
CONTEXT: referenced column: a
insert into test_int_strict(a) values(1234233434343434343434343434343.55);
ERROR: integer out of range
CONTEXT: referenced column: a
insert into test_int_strict(a) values(12.55);
insert into test_int_strict(a) values(-12.55);
insert into test_int_strict(a) values(-12123451343434343434343434343434.55);
ERROR: integer out of range
CONTEXT: referenced column: a
insert into test_int_strict(a) values(1.4e300);
ERROR: integer out of range
CONTEXT: referenced column: a
insert into test_int_strict(a) values(-1.4e300);
ERROR: integer out of range
CONTEXT: referenced column: a
select * from test_int_strict;
a
-----
-55
55
13
-13
(4 rows)
insert into test_bint_strict(a) values(-55);
insert into test_bint_strict(a) values(55);
insert into test_bint_strict(a) values(35589898923423423423423423);
ERROR: bigint out of range
CONTEXT: referenced column: a
insert into test_bint_strict(a) values(-3553434343434343432434);
ERROR: bigint out of range
CONTEXT: referenced column: a
insert into test_bint_strict(a) values('a888');
ERROR: invalid input syntax for type bigint: "a888"
LINE 1: insert into test_bint_strict(a) values('a888');
^
CONTEXT: referenced column: a
insert into test_bint_strict(a) values('88123423433834343423434aa');
ERROR: value "88123423433834343423434aa" is out of range for type bigint
LINE 1: insert into test_bint_strict(a) values('88123423433834343423...
^
CONTEXT: referenced column: a
insert into test_bint_strict(a) values('-88aa');
ERROR: invalid input syntax for type bigint: "-88aa"
LINE 1: insert into test_bint_strict(a) values('-88aa');
^
CONTEXT: referenced column: a
insert into test_bint_strict(a) values(1234233434343434343434343434343.55);
ERROR: bigint out of range
CONTEXT: referenced column: a
insert into test_bint_strict(a) values(12.55);
insert into test_bint_strict(a) values(-12.55);
insert into test_bint_strict(a) values(-12123451343434343434343434343434.55);
ERROR: bigint out of range
CONTEXT: referenced column: a
insert into test_bint_strict(a) values(1.4e300);
ERROR: bigint out of range
CONTEXT: referenced column: a
insert into test_bint_strict(a) values(-1.4e300);
ERROR: bigint out of range
CONTEXT: referenced column: a
select * from test_bint_strict;
a
-----
-55
55
13
-13
(4 rows)
insert into test_float4_strict(a) values(3.4e37);
insert into test_float4_strict(a) values(-3.4e37);
select * from test_float4_strict;
a
----------
3.4e+37
-3.4e+37
(2 rows)
insert into test_float8_strict(a) values(3.4e307);
insert into test_float8_strict(a) values(-3.4e307);
select * from test_float8_strict;
a
-----------
3.4e+307
-3.4e+307
(2 rows)
insert into test_char_strict(a) values('adsfsdaf');
ERROR: value too long for type character(1)
CONTEXT: referenced column: a
insert into test_char_strict(a) values(-1234998.55);
ERROR: value too long for type character(1)
CONTEXT: referenced column: a
insert into test_char_strict(a) values(13434.77);
ERROR: value too long for type character(1)
CONTEXT: referenced column: a
insert into test_char_strict(a) values(13);
ERROR: value too long for type character(1)
CONTEXT: referenced column: a
select * from test_char_strict;
a
---
(0 rows)
insert into test_varchar7_strict(a) values('adsfsdaf');
ERROR: value too long for type character varying(7)
CONTEXT: referenced column: a
insert into test_varchar7_strict(a) values('-1234998.55');
ERROR: value too long for type character varying(7)
CONTEXT: referenced column: a
insert into test_varchar7_strict(a) values(13434.77);
ERROR: value too long for type character varying(7)
CONTEXT: referenced column: a
insert into test_varchar7_strict(a) values(13434.8);
select * from test_varchar7_strict;
a
---------
13434.8
(1 row)
--insert into table select ...
create table test_tint_strict1(a tinyint);
create table test_sint_strict1(a smallint);
create table test_int_strict1(a int);
create table test_bint_strict1(a bigint);
create table test_char_strict1(a char);
create table test_varchar7_strict_1(a varchar(7));
insert into test_tint_strict1 select * from test_sint_strict;
select * from test_tint_strict1;
a
-----
-55
55
13
-13
(4 rows)
delete from test_tint_strict1;
insert into test_tint_strict1 select * from test_int_strict;
select * from test_tint_strict1;
a
-----
-55
55
13
-13
(4 rows)
delete from test_tint_strict1;
insert into test_tint_strict1 select * from test_bint_strict;
select * from test_tint_strict1;
a
-----
-55
55
13
-13
(4 rows)
delete from test_tint_strict1;
insert into test_tint_strict1 select * from test_float4_strict;
ERROR: tinyint out of range
CONTEXT: referenced column: a
select * from test_tint_strict1;
a
---
(0 rows)
delete from test_tint_strict1;
insert into test_tint_strict1 select * from test_float8_strict;
ERROR: tinyint out of range
CONTEXT: referenced column: a
select * from test_tint_strict1;
a
---
(0 rows)
delete from test_tint_strict1;
insert into test_tint_strict1 select * from test_varchar7_strict;
ERROR: value "13434.8" is out of range for type tinyint
CONTEXT: referenced column: a
select * from test_tint_strict1;
a
---
(0 rows)
delete from test_tint_strict1;
insert into test_tint_strict1 select * from test_char_strict;
select * from test_tint_strict1;
a
---
(0 rows)
insert into test_sint_strict1 select * from test_tint_strict;
select * from test_sint_strict1;
a
-----
-55
55
13
-13
(4 rows)
delete from test_sint_strict1;
insert into test_sint_strict1 select * from test_int_strict;
select * from test_sint_strict1;
a
-----
-55
55
13
-13
(4 rows)
delete from test_sint_strict1;
insert into test_sint_strict1 select * from test_bint_strict;
select * from test_sint_strict1;
a
-----
-55
55
13
-13
(4 rows)
delete from test_sint_strict1;
insert into test_sint_strict1 select * from test_float4_strict;
ERROR: smallint out of range
CONTEXT: referenced column: a
select * from test_sint_strict1;
a
---
(0 rows)
delete from test_sint_strict1;
insert into test_sint_strict1 select * from test_float8_strict;
ERROR: smallint out of range
CONTEXT: referenced column: a
select * from test_sint_strict1;
a
---
(0 rows)
delete from test_sint_strict1;
insert into test_sint_strict1 select * from test_varchar7_strict;
select * from test_sint_strict1;
a
-------
13435
(1 row)
delete from test_sint_strict1;
insert into test_sint_strict1 select * from test_char_strict;
select * from test_sint_strict1;
a
---
(0 rows)
insert into test_int_strict1 select * from test_tint_strict;
select * from test_int_strict1;
a
-----
-55
55
13
-13
(4 rows)
delete from test_int_strict1;
insert into test_int_strict1 select * from test_sint_strict;
select * from test_int_strict1;
a
-----
-55
55
13
-13
(4 rows)
delete from test_int_strict1;
insert into test_int_strict1 select * from test_bint_strict;
select * from test_int_strict1;
a
-----
-55
55
13
-13
(4 rows)
delete from test_int_strict1;
insert into test_int_strict1 select * from test_float4_strict;
ERROR: integer out of range
CONTEXT: referenced column: a
select * from test_int_strict1;
a
---
(0 rows)
delete from test_int_strict1;
insert into test_int_strict1 select * from test_float8_strict;
ERROR: integer out of range
CONTEXT: referenced column: a
select * from test_int_strict1;
a
---
(0 rows)
delete from test_int_strict1;
insert into test_int_strict1 select * from test_varchar7_strict;
select * from test_int_strict1;
a
-------
13435
(1 row)
delete from test_int_strict1;
insert into test_int_strict1 select * from test_char_strict;
select * from test_int_strict1;
a
---
(0 rows)
insert into test_bint_strict1 select * from test_tint_strict;
select * from test_bint_strict1;
a
-----
-55
55
13
-13
(4 rows)
delete from test_bint_strict1;
insert into test_bint_strict1 select * from test_sint_strict;
select * from test_bint_strict1;
a
-----
-55
55
13
-13
(4 rows)
delete from test_bint_strict1;
insert into test_bint_strict1 select * from test_int_strict;
select * from test_bint_strict1;
a
-----
-55
55
13
-13
(4 rows)
delete from test_bint_strict1;
insert into test_bint_strict1 select * from test_float4_strict;
ERROR: bigint out of range
CONTEXT: referenced column: a
select * from test_bint_strict1;
a
---
(0 rows)
delete from test_bint_strict1;
insert into test_bint_strict1 select * from test_float8_strict;
ERROR: bigint out of range
CONTEXT: referenced column: a
select * from test_bint_strict1;
a
---
(0 rows)
delete from test_bint_strict1;
insert into test_bint_strict1 select * from test_varchar7_strict;
select * from test_bint_strict1;
a
-------
13435
(1 row)
delete from test_bint_strict1;
insert into test_bint_strict1 select * from test_char_strict;
select * from test_bint_strict1;
a
---
(0 rows)
insert into test_char_strict1 select * from test_tint_strict;
ERROR: value too long for type character(1)
CONTEXT: referenced column: a
select * from test_char_strict1;
a
---
(0 rows)
delete from test_char_strict1;
insert into test_char_strict1 select * from test_sint_strict;
ERROR: value too long for type character(1)
CONTEXT: referenced column: a
select * from test_char_strict1;
a
---
(0 rows)
delete from test_char_strict1;
insert into test_char_strict1 select * from test_int_strict;
ERROR: value too long for type character(1)
CONTEXT: referenced column: a
select * from test_char_strict1;
a
---
(0 rows)
delete from test_char_strict1;
insert into test_char_strict1 select * from test_bint_strict;
ERROR: value too long for type character(1)
CONTEXT: referenced column: a
select * from test_char_strict1;
a
---
(0 rows)
delete from test_char_strict1;
insert into test_char_strict1 select * from test_varchar7_strict;
ERROR: value too long for type character(1)
CONTEXT: referenced column: a
select * from test_char_strict1;
a
---
(0 rows)
insert into test_varchar7_strict_1 select * from test_tint_strict;
select * from test_varchar7_strict_1;
a
-----
-55
55
13
-13
(4 rows)
delete from test_varchar7_strict_1;
insert into test_varchar7_strict_1 select * from test_sint_strict;
select * from test_varchar7_strict_1;
a
-----
-55
55
13
-13
(4 rows)
delete from test_varchar7_strict_1;
insert into test_varchar7_strict_1 select * from test_int_strict;
select * from test_varchar7_strict_1;
a
-----
-55
55
13
-13
(4 rows)
delete from test_varchar7_strict_1;
insert into test_varchar7_strict_1 select * from test_bint_strict;
select * from test_varchar7_strict_1;
a
-----
-55
55
13
-13
(4 rows)
delete from test_varchar7_strict_1;
insert into test_varchar7_strict_1 select * from test_char_strict;
select * from test_varchar7_strict_1;
a
---
(0 rows)
--A non-null column is not inserted when executing insert into clause, and this non-null column has no default value
create table test_notnull_tint_strict(a tinyint, b tinyint not null);
create table test_notnull_sint_strict(a smallint, b smallint not null);
create table test_notnull_int_strict(a int, b int not null);
create table test_notnull_bint_strict(a bigint, b bigint not null);
create table test_notnull_char_strict(a char, b char not null);
create table test_notnull_varchar_strict(a varchar(5), b varchar(5) not null);
create table test_notnull_clob_strict(a clob, b clob not null);
create table test_notnull_text_strict(a text, b text not null);
create table test_notnull_real_strict(a real, b real not null);
create table test_notnull_double_strict(a double, b double not null);
create table test_notnull_numeric_strict(a numeric(5,2), b numeric(5,2) not null);
insert into test_notnull_tint_strict(a) values(1);
ERROR: null value in column "b" violates not-null constraint
DETAIL: Failing row contains (1, null).
select * from test_notnull_tint_strict;
a | b
---+---
(0 rows)
insert into test_notnull_sint_strict(a) values(1);
ERROR: null value in column "b" violates not-null constraint
DETAIL: Failing row contains (1, null).
select * from test_notnull_sint_strict;
a | b
---+---
(0 rows)
insert into test_notnull_int_strict(a) values(1);
ERROR: null value in column "b" violates not-null constraint
DETAIL: Failing row contains (1, null).
select * from test_notnull_int_strict;
a | b
---+---
(0 rows)
insert into test_notnull_bint_strict(a) values(1);
ERROR: null value in column "b" violates not-null constraint
DETAIL: Failing row contains (1, null).
select * from test_notnull_bint_strict;
a | b
---+---
(0 rows)
insert into test_notnull_char_strict(a) values(1);
ERROR: null value in column "b" violates not-null constraint
DETAIL: Failing row contains (1, null).
select * from test_notnull_char_strict;
a | b
---+---
(0 rows)
insert into test_notnull_varchar_strict(a) values(1);
ERROR: null value in column "b" violates not-null constraint
DETAIL: Failing row contains (1, null).
select * from test_notnull_varchar_strict;
a | b
---+---
(0 rows)
insert into test_notnull_clob_strict(a) values(1);
ERROR: null value in column "b" violates not-null constraint
DETAIL: Failing row contains (1, null).
select * from test_notnull_clob_strict;
a | b
---+---
(0 rows)
insert into test_notnull_text_strict(a) values(1);
ERROR: null value in column "b" violates not-null constraint
DETAIL: Failing row contains (1, null).
select * from test_notnull_text_strict;
a | b
---+---
(0 rows)
insert into test_notnull_real_strict(a) values(1);
ERROR: null value in column "b" violates not-null constraint
DETAIL: Failing row contains (1, null).
select * from test_notnull_real_strict;
a | b
---+---
(0 rows)
insert into test_notnull_double_strict(a) values(1);
ERROR: null value in column "b" violates not-null constraint
DETAIL: Failing row contains (1, null).
select * from test_notnull_double_strict;
a | b
---+---
(0 rows)
insert into test_notnull_numeric_strict(a) values(1);
ERROR: null value in column "b" violates not-null constraint
DETAIL: Failing row contains (1.00, null).
select * from test_notnull_numeric_strict;
a | b
---+---
(0 rows)
--insert into table values(null)
insert into test_notnull_tint_strict(b) values(null);
ERROR: null value in column "b" violates not-null constraint
DETAIL: Failing row contains (null, null).
insert into test_notnull_sint_strict(b) values(null);
ERROR: null value in column "b" violates not-null constraint
DETAIL: Failing row contains (null, null).
insert into test_notnull_int_strict(b) values(null);
ERROR: null value in column "b" violates not-null constraint
DETAIL: Failing row contains (null, null).
insert into test_notnull_bint_strict(b) values(null);
ERROR: null value in column "b" violates not-null constraint
DETAIL: Failing row contains (null, null).
insert into test_notnull_char_strict(b) values(null);
ERROR: null value in column "b" violates not-null constraint
DETAIL: Failing row contains (null, null).
insert into test_notnull_varchar_strict(b) values(null);
ERROR: null value in column "b" violates not-null constraint
DETAIL: Failing row contains (null, null).
insert into test_notnull_clob_strict(b) values(null);
ERROR: null value in column "b" violates not-null constraint
DETAIL: Failing row contains (null, null).
insert into test_notnull_text_strict(b) values(null);
ERROR: null value in column "b" violates not-null constraint
DETAIL: Failing row contains (null, null).
insert into test_notnull_real_strict(b) values(null);
ERROR: null value in column "b" violates not-null constraint
DETAIL: Failing row contains (null, null).
insert into test_notnull_double_strict(b) values(null);
ERROR: null value in column "b" violates not-null constraint
DETAIL: Failing row contains (null, null).
insert into test_notnull_numeric_strict(b) values(null);
ERROR: null value in column "b" violates not-null constraint
DETAIL: Failing row contains (null, null).
--test some bug fix
create table test_space_to_int1(a tinyint);
insert into test_space_to_int1 values('34 55');
ERROR: invalid input syntax for type tinyint: "34 55"
LINE 1: insert into test_space_to_int1 values('34 55');
^
CONTEXT: referenced column: a
create table test_space_to_int2(a smallint);
insert into test_space_to_int2 values('34 55');
ERROR: invalid input syntax for type smallint: "34 55"
LINE 1: insert into test_space_to_int2 values('34 55');
^
CONTEXT: referenced column: a
create table test_space_to_int4(a int);
insert into test_space_to_int4 values('34 55');
ERROR: invalid input syntax for type integer: "34 55"
LINE 1: insert into test_space_to_int4 values('34 55');
^
CONTEXT: referenced column: a
create table test_space_to_int8(a bigint);
insert into test_space_to_int8 values('34 55');
ERROR: invalid input syntax for type bigint: "34 55"
LINE 1: insert into test_space_to_int8 values('34 55');
^
CONTEXT: referenced column: a
set dolphin.sql_mode = '';
insert into test_space_to_int1 values('34 55');
WARNING: invalid input syntax for type tinyint: "34 55"
LINE 1: insert into test_space_to_int1 values('34 55');
^
CONTEXT: referenced column: a
insert into test_space_to_int2 values('34 55');
WARNING: invalid input syntax for type smallint: "34 55"
LINE 1: insert into test_space_to_int2 values('34 55');
^
CONTEXT: referenced column: a
insert into test_space_to_int4 values('34 55');
WARNING: invalid input syntax for type integer: "34 55"
LINE 1: insert into test_space_to_int4 values('34 55');
^
CONTEXT: referenced column: a
insert into test_space_to_int8 values('34 55');
WARNING: invalid input syntax for type bigint: "34 55"
LINE 1: insert into test_space_to_int8 values('34 55');
^
CONTEXT: referenced column: a
select * from test_space_to_int1;
a
----
34
(1 row)
select * from test_space_to_int2;
a
----
34
(1 row)
select * from test_space_to_int4;
a
----
34
(1 row)
select * from test_space_to_int8;
a
----
34
(1 row)
drop schema sql_mode_strict cascade;
NOTICE: drop cascades to 55 other objects
DETAIL: drop cascades to table test_tint
drop cascades to table test_sint
drop cascades to table test_int
drop cascades to table test_bint
drop cascades to table test_float4
drop cascades to table test_float8
drop cascades to table test_char
drop cascades to table test_varchar7
drop cascades to table test_tint1
drop cascades to table test_sint1
drop cascades to table test_int1
drop cascades to table test_bint1
drop cascades to table test_char1
drop cascades to table test_varchar7_1
drop cascades to table test_notnull_tint
drop cascades to table test_notnull_sint
drop cascades to table test_notnull_int
drop cascades to table test_notnull_bint
drop cascades to table test_notnull_char
drop cascades to table test_notnull_varchar
drop cascades to table test_notnull_clob
drop cascades to table test_notnull_text
drop cascades to table test_notnull_real
drop cascades to table test_notnull_double
drop cascades to table test_notnull_numeric
drop cascades to table test_multi_default
drop cascades to table test_tint_strict
drop cascades to table test_sint_strict
drop cascades to table test_int_strict
drop cascades to table test_bint_strict
drop cascades to table test_float4_strict
drop cascades to table test_float8_strict
drop cascades to table test_char_strict
drop cascades to table test_varchar7_strict
drop cascades to table test_tint_strict1
drop cascades to table test_sint_strict1
drop cascades to table test_int_strict1
drop cascades to table test_bint_strict1
drop cascades to table test_char_strict1
drop cascades to table test_varchar7_strict_1
drop cascades to table test_notnull_tint_strict
drop cascades to table test_notnull_sint_strict
drop cascades to table test_notnull_int_strict
drop cascades to table test_notnull_bint_strict
drop cascades to table test_notnull_char_strict
drop cascades to table test_notnull_varchar_strict
drop cascades to table test_notnull_clob_strict
drop cascades to table test_notnull_text_strict
drop cascades to table test_notnull_real_strict
drop cascades to table test_notnull_double_strict
drop cascades to table test_notnull_numeric_strict
drop cascades to table test_space_to_int1
drop cascades to table test_space_to_int2
drop cascades to table test_space_to_int4
drop cascades to table test_space_to_int8
reset current_schema;
