~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