~drizzle-trunk/drizzle/development

# Testing of "strict" mode

-- source include/have_innodb.inc

# Test INSERT with INT

--disable_warnings

DROP TABLE IF EXISTS t1;

--enable_warnings

CREATE TABLE t1 (col1 INT);

INSERT INTO t1 VALUES(-2147483648);

INSERT INTO t1 VALUES (0);

INSERT INTO t1 VALUES (2147483647);

INSERT INTO t1 VALUES ('-2147483648');

INSERT INTO t1 VALUES ('2147483647');

INSERT INTO t1 VALUES (-2147483648.0);

INSERT INTO t1 VALUES (2147483647.0);

--error ER_WARN_DATA_OUT_OF_RANGE

INSERT INTO t1 (col1) VALUES(-2147483649);

--error ER_WARN_DATA_OUT_OF_RANGE

INSERT INTO t1 (col1) VALUES(2147643648);

--error ER_WARN_DATA_OUT_OF_RANGE

INSERT INTO t1 (col1) VALUES('-2147483649');

--error ER_WARN_DATA_OUT_OF_RANGE

INSERT INTO t1 (col1) VALUES('2147643648');

--error ER_WARN_DATA_OUT_OF_RANGE

INSERT INTO t1 (col1) VALUES(-2147483649.0);

--error ER_WARN_DATA_OUT_OF_RANGE

INSERT INTO t1 (col1) VALUES(2147643648.0);

--error ER_WARN_DATA_OUT_OF_RANGE

UPDATE t1 SET col1 = col1 - 50 WHERE col1 < 0;

--error ER_DIVISION_BY_ZERO

UPDATE t1 SET col1 =col1 / 0 WHERE col1 > 0;

--error ER_DIVISION_BY_ZERO

UPDATE t1 SET col1= MOD(col1,0) WHERE col1 > 0;

--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD

INSERT INTO t1 (col1) VALUES ('');

--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD

INSERT INTO t1 (col1) VALUES ('a59b');

--error ER_WARN_DATA_TRUNCATED

INSERT INTO t1 (col1) VALUES ('1a');

INSERT IGNORE INTO t1 (col1) VALUES ('2a');

--error ER_DIVISION_BY_ZERO

INSERT IGNORE INTO t1 values (1/0);

INSERT IGNORE INTO t1 values (-2147483649);

INSERT IGNORE INTO t1 values (2147643648);

INSERT IGNORE INTO t1 values ('-2147483649');

INSERT IGNORE INTO t1 values ('2147643648');

INSERT IGNORE INTO t1 values (-2147483649.0);

INSERT IGNORE INTO t1 values (2147643648.0);

SELECT * FROM t1;

DROP TABLE t1;

# Test INSERT with BIGINT

# Note that this doesn't behave 100 % to standard as we rotate

# integers when it's too big/small (just like C)

CREATE TABLE t1 (col1 BIGINT);

INSERT INTO t1 VALUES (-9223372036854775808);

INSERT INTO t1 VALUES (0);

INSERT INTO t1 VALUES (9223372036854775807);

INSERT INTO t1 VALUES ('-9223372036854775808');

INSERT INTO t1 VALUES ('9223372036854775807');

INSERT INTO t1 VALUES (-9223372036854774000.0);

INSERT INTO t1 VALUES (9223372036854775700.0);

--error ER_WARN_DATA_OUT_OF_RANGE

INSERT INTO t1 (col1) VALUES(-9223372036854775809);

# https://bugs.launchpad.net/drizzle/+bug/316221

 --error ER_WARN_DATA_OUT_OF_RANGE

INSERT INTO t1 (col1) VALUES(9223372036854775808);

--error ER_WARN_DATA_OUT_OF_RANGE

INSERT INTO t1 (col1) VALUES('-9223372036854775809');

--error ER_WARN_DATA_OUT_OF_RANGE

INSERT INTO t1 (col1) VALUES('9223372036854775808');

# Note that the following two double numbers are slighty bigger than max/min

# bigint becasue of rounding errors when converting it to bigint

--error ER_WARN_DATA_OUT_OF_RANGE

INSERT INTO t1 (col1) VALUES(-9223372036854785809.0);

--error ER_WARN_DATA_OUT_OF_RANGE

INSERT INTO t1 (col1) VALUES(9223372036854785808.0);

# The following doesn't give an error as it's done in integer context

# UPDATE t1 SET col1=col1 - 5000 WHERE col1 < 0;

# UPDATE t1 SET col2 =col2 + 5000 WHERE col2 > 0;

--error ER_DIVISION_BY_ZERO

UPDATE t1 SET col1 =col1 / 0 WHERE col1 > 0;

--error ER_DIVISION_BY_ZERO

UPDATE t1 SET col1= MOD(col1,0) WHERE col1 > 0;

--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD

INSERT INTO t1 (col1) VALUES ('');

--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD

INSERT INTO t1 (col1) VALUES ('a59b');

--error ER_WARN_DATA_TRUNCATED

INSERT INTO t1 (col1) VALUES ('1a');

INSERT IGNORE INTO t1 (col1) VALUES ('2a');

--error ER_DIVISION_BY_ZERO

INSERT IGNORE INTO t1 values (1/0);

INSERT IGNORE INTO t1 VALUES (-9223372036854775809);

INSERT IGNORE INTO t1 VALUES (9223372036854775808);

INSERT IGNORE INTO t1 VALUES ('-9223372036854775809');

INSERT IGNORE INTO t1 VALUES ('9223372036854775808');

INSERT IGNORE INTO t1 VALUES (-9223372036854785809.0);

INSERT IGNORE INTO t1 VALUES (9223372036854785808.0);

SELECT * FROM t1;

DROP TABLE t1;

# Test INSERT with NUMERIC

CREATE TABLE t1 (col1 NUMERIC(4,2));

# The following INSERT statements used to look as follows before 

# the fix for bug#337038 was implemented:

# INSERT INTO t1 

# VALUES (10.55),(10.5555),(0),(-10.55),(-10.5555),(11),(1e+01);

# Now that decimal truncation gives an error instead of a warning, we will

# get an error on certain INSERT statements below about decimal truncation.

INSERT INTO t1 VALUES (10.55);

# this statement errors due to decimal truncation. The number

# used in insertion is chosen to test that this this error does

# in fact occur

--error ER_WARN_DATA_TRUNCATED 

INSERT INTO t1 VALUES (10.5555);

INSERT INTO t1 VALUES (0);

INSERT INTO t1 VALUES (-10.55);

# this statement errors due to decimal truncation. The number

# used in insertion is chosen to test that this this error does

# in fact occur

--error ER_WARN_DATA_TRUNCATED

INSERT INTO t1 VALUES (-10.5555);

INSERT INTO t1 VALUES (11);

INSERT INTO t1 VALUES (1e+01);

# The following INSERT statements used to look as follows before 

# the fix for bug#337038 was implemented:

# INSERT INTO t1 VALUES ('10.55'),('10.5555'),('-10.55'),('-10.5555'),('11'),('1e+01');

# Now that decimal truncation gives an error instead of a warning, we will

# get an error on certain INSERT statements below about decimal truncation.

INSERT INTO t1 VALUES ('10.55');

# this statement errors due to decimal truncation. The number

# used in insertion is chosen to test that this this error does

# in fact occur

--error ER_WARN_DATA_TRUNCATED 

INSERT INTO t1 VALUES ('10.5555');

INSERT INTO t1 VALUES ('-10.55');

# this statement errors due to decimal truncation. The number 

# used in insertion is chosen to test that this error does 

# in fact occur

--error ER_WARN_DATA_TRUNCATED 

INSERT INTO t1 VALUES ('-10.5555');

INSERT INTO t1 VALUES ('11');

INSERT INTO t1 VALUES ('1e+01');

# The 2 following inserts should generate a warning, but doesn't yet

# because NUMERIC works like DECIMAL

--error ER_WARN_DATA_OUT_OF_RANGE

INSERT INTO t1 VALUES (101.55);

--error ER_WARN_DATA_OUT_OF_RANGE

INSERT INTO t1 VALUES (101);

--error ER_WARN_DATA_OUT_OF_RANGE

INSERT INTO t1 VALUES (-101.55);

--error ER_WARN_DATA_OUT_OF_RANGE

INSERT INTO t1 VALUES (1010.55);

--error ER_WARN_DATA_OUT_OF_RANGE

INSERT INTO t1 VALUES (1010);

# The 2 following inserts should generate a warning, but doesn't yet

# because NUMERIC works like DECIMAL

--error ER_WARN_DATA_OUT_OF_RANGE

INSERT INTO t1 VALUES ('101.55');

--error ER_WARN_DATA_OUT_OF_RANGE

INSERT INTO t1 VALUES ('101');

--error ER_WARN_DATA_OUT_OF_RANGE

INSERT INTO t1 VALUES ('-101.55');

--error ER_WARN_DATA_OUT_OF_RANGE

INSERT INTO t1 VALUES ('-1010.55');

--error ER_WARN_DATA_OUT_OF_RANGE

INSERT INTO t1 VALUES ('-100E+1');

--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD

INSERT INTO t1 VALUES ('-100E');

--error ER_WARN_DATA_OUT_OF_RANGE

UPDATE t1 SET col1 =col1 * 50000 WHERE col1 =11;

--error ER_DIVISION_BY_ZERO

UPDATE t1 SET col1 =col1 / 0 WHERE col1 > 0;

--error ER_DIVISION_BY_ZERO

UPDATE t1 SET col1= MOD(col1,0) WHERE col1 > 0;

#--error ER_WARN_DATA_TRUNCATED

--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD

INSERT INTO t1 (col1) VALUES ('');

#--error ER_WARN_DATA_TRUNCATED

--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD

INSERT INTO t1 (col1) VALUES ('a59b');

--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD

INSERT INTO t1 (col1) VALUES ('1a');

INSERT IGNORE INTO t1 (col1) VALUES ('2a');

--error ER_DIVISION_BY_ZERO

INSERT IGNORE INTO t1 values (1/0);

INSERT IGNORE INTO t1 VALUES (1000);

INSERT IGNORE INTO t1 VALUES (-1000);

INSERT IGNORE INTO t1 VALUES ('1000');

INSERT IGNORE INTO t1 VALUES ('-1000');

INSERT IGNORE INTO t1 VALUES (1000.0);

INSERT IGNORE INTO t1 VALUES (-1000.0);

UPDATE IGNORE t1 SET col1=1/NULL where col1=0;

--sorted_result

SELECT * FROM t1;

DROP TABLE t1;

# Testing INSERT with CHAR/VARCHAR

CREATE TABLE t1 (col1 CHAR(5), col2 VARCHAR(6));

INSERT INTO t1 VALUES ('hello', 'hello');

INSERT INTO t1 VALUES ('he', 'he');

--error ER_DATA_TOO_LONG

INSERT INTO t1 VALUES ('hello   ', 'hello ');

--error ER_DATA_TOO_LONG

INSERT INTO t1 (col1) VALUES ('hellobob');

--error ER_DATA_TOO_LONG

INSERT INTO t1 (col2) VALUES ('hellobob');

--error ER_DATA_TOO_LONG

INSERT INTO t1 (col2) VALUES ('hello  ');

--error ER_DATA_TOO_LONG

UPDATE t1 SET col1 ='hellobob' WHERE col1 ='he';

--error ER_DATA_TOO_LONG

UPDATE t1 SET col2 ='hellobob' WHERE col2 ='he';

INSERT IGNORE INTO t1 VALUES ('hellobob', 'hellobob');

UPDATE IGNORE t1 SET col2 ='hellotrudy' WHERE col2 ='he';

--sorted_result

SELECT * FROM t1;

DROP TABLE t1;

# Testing INSERT with ENUM

CREATE TABLE t1 (col1 enum('red','blue','green'));

INSERT INTO t1 VALUES ('red');

INSERT INTO t1 VALUES ('blue');

INSERT INTO t1 VALUES ('green');

--error ER_INVALID_ENUM_VALUE # Bad enum

INSERT INTO t1 (col1) VALUES ('yellow');

--error ER_INVALID_ENUM_VALUE # Bad enum

INSERT INTO t1 (col1) VALUES ('redd');

--error ER_INVALID_ENUM_VALUE # Bad enum

INSERT INTO t1 VALUES ('');

--error ER_INVALID_ENUM_VALUE # Bad enum

UPDATE t1 SET col1 ='yellow' WHERE col1 ='green';

--error ER_INVALID_ENUM_VALUE # Bad enum

INSERT IGNORE INTO t1 VALUES ('yellow');

--error ER_INVALID_ENUM_VALUE # Bad enum

UPDATE IGNORE t1 SET col1 ='yellow' WHERE col1 ='blue';

SELECT * FROM t1;

DROP TABLE t1;

# Testing of insert of NULL in not NULL column

CREATE TABLE t1 (col1 INT NOT NULL, col2 CHAR(5) NOT NULL, col3 DATE NOT NULL);

INSERT INTO t1 VALUES (100, 'hello', '2004-08-20');

INSERT INTO t1 (col1,col2,col3) VALUES (101, 'hell2', '2004-08-21');

--error ER_BAD_NULL_ERROR

INSERT INTO t1 (col1,col2,col3) VALUES (NULL, '', '2004-01-01');

--error ER_BAD_NULL_ERROR

INSERT INTO t1 (col1,col2,col3) VALUES (102, NULL, '2004-01-01');

--error ER_BAD_NULL_ERROR

INSERT INTO t1 VALUES (103,'',NULL);

--error ER_BAD_NULL_ERROR

UPDATE t1 SET col1=NULL WHERE col1 =100;

--error ER_BAD_NULL_ERROR

UPDATE t1 SET col2 =NULL WHERE col2 ='hello';

--error ER_BAD_NULL_ERROR

UPDATE t1 SET col2 =NULL where col3 IS NOT NULL;

INSERT IGNORE INTO t1 values (NULL,NULL,NULL);

SELECT * FROM t1;

DROP TABLE t1;

# Testing of default values

CREATE TABLE t1 (col1 INT NOT NULL default 99, col2 CHAR(6) NOT NULL);

--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/

SHOW CREATE TABLE t1;

INSERT INTO t1 VALUES (1, 'hello');

INSERT INTO t1 (col2) VALUES ('hello2');

--error ER_BAD_NULL_ERROR

INSERT INTO t1 (col2) VALUES (NULL);

--error ER_NO_DEFAULT_FOR_FIELD

INSERT INTO t1 (col1) VALUES (2);

--error ER_NO_DEFAULT_FOR_FIELD

INSERT INTO t1 VALUES(default(col1),default(col2));

--error ER_NO_DEFAULT_FOR_FIELD

INSERT INTO t1 (col1) SELECT 1;

--error ER_BAD_NULL_ERROR

INSERT INTO t1 SELECT 1,NULL;

INSERT IGNORE INTO t1 values (NULL,NULL);

--error ER_NO_DEFAULT_FOR_FIELD

INSERT IGNORE INTO t1 (col1) values (3);

--error ER_NO_DEFAULT_FOR_FIELD

INSERT IGNORE INTO t1 () values ();

SELECT * FROM t1;

DROP TABLE t1;

#

# Bug #9029 Traditional: Wrong SQLSTATE returned for string truncation

#

create table t1 (charcol char(255), varcharcol varchar(255),

       varbinarycol varbinary(255));

--error ER_DATA_TOO_LONG

insert into t1 (charcol) values (repeat('x',256));

--error ER_DATA_TOO_LONG

insert into t1 (varcharcol) values (repeat('x',256));

--error ER_DATA_TOO_LONG

insert into t1 (varbinarycol) values (repeat('x',256));

select * from t1;

drop table t1;

#

# Check insert with wrong CAST() (Bug #5912)

#

create table t1 (col1 char(3), col2 integer);

--error ER_TRUNCATED_WRONG_VALUE

insert into t1 (col1) values (cast(1000 as char(3)));

--error ER_TRUNCATED_WRONG_VALUE

insert into t1 (col1) values (cast(1000E+0 as char(3)));

--error ER_TRUNCATED_WRONG_VALUE

insert into t1 (col1) values (cast(1000.0 as char(3)));

--error ER_TRUNCATED_WRONG_VALUE

insert into t1 (col2) values (cast('abc' as DECIMAL));

--error ER_TRUNCATED_WRONG_VALUE

insert into t1 (col2) values (10E+0 + 'a');

--error ER_WARN_DATA_TRUNCATED

insert into t1 (col2) values ('10a');

insert into t1 (col2) values (cast('10a' as DECIMAL));

insert into t1 (col2) values (cast('10' as DECIMAL));

insert into t1 (col2) values (cast('10' as DECIMAL));

insert into t1 (col2) values (10E+0 + '0 ');

select * from t1;

drop table t1;

# Test fields with no default value that are NOT NULL (Bug #5986)

CREATE TABLE t1 (i int not null);

--error ER_NO_DEFAULT_FOR_FIELD

INSERT INTO t1 VALUES ();

--error ER_NO_DEFAULT_FOR_FIELD

INSERT INTO t1 VALUES (DEFAULT);

--error ER_NO_DEFAULT_FOR_FIELD

INSERT INTO t1 VALUES (DEFAULT(i));

ALTER TABLE t1 ADD j int;

--error ER_NO_DEFAULT_FOR_FIELD

INSERT INTO t1 SET j = 1;

--error ER_NO_DEFAULT_FOR_FIELD

INSERT INTO t1 SET j = 1, i = DEFAULT;

--error ER_NO_DEFAULT_FOR_FIELD

INSERT INTO t1 SET j = 1, i = DEFAULT(i);

--error ER_NO_DEFAULT_FOR_FIELD

INSERT INTO t1 VALUES (DEFAULT,1);

DROP TABLE t1;

CREATE TABLE t1 (i int not null);

--error ER_NO_DEFAULT_FOR_FIELD

INSERT INTO t1 VALUES ();

--error ER_NO_DEFAULT_FOR_FIELD

INSERT INTO t1 VALUES (DEFAULT);

# DEFAULT(i) is an error even with the default sql_mode

--error ER_NO_DEFAULT_FOR_FIELD

INSERT INTO t1 VALUES (DEFAULT(i));

ALTER TABLE t1 ADD j int;

--error ER_NO_DEFAULT_FOR_FIELD

INSERT INTO t1 SET j = 1;

--error ER_NO_DEFAULT_FOR_FIELD

INSERT INTO t1 SET j = 1, i = DEFAULT;

--error ER_NO_DEFAULT_FOR_FIELD

INSERT INTO t1 SET j = 1, i = DEFAULT(i);

--error ER_NO_DEFAULT_FOR_FIELD

INSERT INTO t1 VALUES (DEFAULT,1);

DROP TABLE t1;

#

# Bugs #8295 and #8296: varchar and varbinary conversion

#

--error ER_TOO_BIG_FIELDLENGTH

create table t1(a varchar(65537));

--error ER_TOO_BIG_FIELDLENGTH

create table t1(a varbinary(65537));

#

# Bug #9881: problem with altering table

#

create table t1(a int, b date not null);                                       

alter table t1 modify a bigint not null;

--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/

show create table t1;

drop table t1;

#

# Bug #11964: alter table with timestamp field

#

create table t1(a int, b timestamp);

alter table t1 add primary key(a);

--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/

show create table t1;

drop table t1;

create table t1(a int, b timestamp default 20050102030405);

alter table t1 add primary key(a);

--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/

show create table t1;

drop table t1;

#

# Bug#17626 CREATE TABLE ... SELECT failure with TRADITIONAL SQL mode

#

create table t1 (date date not null);

create table t2 select date from t1;

--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/

show create table t2;

drop table t2,t1;

create table t1 (i int)

comment='123456789*123456789*123456789*123456789*123456789*123456789*';

--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/

show create table t1;

drop table t1;

#

# Bug #26359: Strings becoming truncated and converted to numbers under STRICT mode

#

create table t1(col1 int, col2 int, 

  col3 int, col4 int,

  col7 int, col8 int,

  col9 bigint, col10 bigint);

--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD

insert into t1(col1) values('-');

--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD

insert into t1(col2) values('+');

--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD

insert into t1(col3) values('-');

--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD

insert into t1(col4) values('+');

--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD

insert into t1(col7) values('-');

--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD

insert into t1(col8) values('+');

--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD

insert into t1(col9) values('-');

--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD

insert into t1(col10) values('+');

drop table t1;

#

# Bug#27069 set with identical elements are created

#

--error ER_DUPLICATED_VALUE_IN_TYPE

create table t1 (f1 enum('a','a'));

--echo End of 5.0 tests