~drizzle-trunk/drizzle/development

drop table if exists t1, t2;

SET SQL_WARNINGS=1;

create table t1 (a int);

create table t1 (a int);

ERROR 42S01: Table 't1' already exists

show count(*) errors;

@@session.error_count

1

show errors;

Level	Code	Message

Error	1050	Table 't1' already exists

show warnings;

Level	Code	Message

Error	1050	Table 't1' already exists

create table t2(a int) default charset qwerty;

ERROR 42000: Unknown character set: 'qwerty'

show count(*) errors;

@@session.error_count

1

show errors;

Level	Code	Message

Error	1115	Unknown character set: 'qwerty'

create table t (i);

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 1

show count(*) errors;

@@session.error_count

1

show errors;

Level	Code	Message

Error	1064	You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 1

insert into t1 values (1);

insert into t1 values ("hej");

Warnings:

Warning	1366	Incorrect integer value: 'hej' for column 'a' at row 1

insert into t1 values ("hej"),("då");

Warnings:

Warning	1366	Incorrect integer value: 'hej' for column 'a' at row 1

Warning	1366	Incorrect integer value: 'd?' for column 'a' at row 2

set SQL_WARNINGS=1;

insert into t1 values ("hej");

Warnings:

Warning	1366	Incorrect integer value: 'hej' for column 'a' at row 1

insert into t1 values ("hej"),("då");

Warnings:

Warning	1366	Incorrect integer value: 'hej' for column 'a' at row 1

Warning	1366	Incorrect integer value: 'd?' for column 'a' at row 2

drop table t1;

set SQL_WARNINGS=0;

drop temporary table if exists not_exists;

Warnings:

Note	1051	Unknown table 'not_exists'

drop table if exists not_exists_table;

Warnings:

Note	1051	Unknown table 'not_exists_table'

show warnings limit 1;

Level	Code	Message

Note	1051	Unknown table 'not_exists_table'

drop database if exists not_exists_db;

Warnings:

Note	1008	Can't drop database 'not_exists_db'; database doesn't exist

show count(*) warnings;

@@session.warning_count

1

create table t1(id int);

create table if not exists t1(id int);

Warnings:

Note	1050	Table 't1' already exists

select @@warning_count;

@@warning_count

1

drop table t1;

create table t1(a tinyint, b int not null, c date, d char(5));

load data infile '../std_data_ln/warnings_loaddata.dat' into table t1 fields terminated by ',';

Warnings:

Warning	1263	Column set to default value; NULL supplied to NOT NULL column 'b' at row 2

Warning	1265	Data truncated for column 'd' at row 3

Warning	1265	Data truncated for column 'c' at row 4

Warning	1261	Row 5 doesn't contain data for all columns

Warning	1265	Data truncated for column 'b' at row 6

Warning	1262	Row 7 was truncated; it contained more data than there were input columns

Warning	1264	Out of range value for column 'a' at row 8

select @@warning_count;

@@warning_count

7

drop table t1;

create table t1(a tinyint NOT NULL, b tinyint unsigned, c char(5));

insert into t1 values(NULL,100,'mysql'),(10,-1,'mysql ab'),(500,256,'open source'),(20,NULL,'test');

Warnings:

Warning	1048	Column 'a' cannot be null

Warning	1264	Out of range value for column 'b' at row 2

Warning	1265	Data truncated for column 'c' at row 2

Warning	1264	Out of range value for column 'a' at row 3

Warning	1264	Out of range value for column 'b' at row 3

Warning	1265	Data truncated for column 'c' at row 3

alter table t1 modify c char(4);

Warnings:

Warning	1265	Data truncated for column 'c' at row 1

Warning	1265	Data truncated for column 'c' at row 2

alter table t1 add d char(2);

update t1 set a=NULL where a=10;

ERROR 23000: Column 'a' cannot be null

update t1 set c='mysql ab' where c='test';

Warnings:

Warning	1265	Data truncated for column 'c' at row 4

update t1 set d=c;

Warnings:

Warning	1265	Data truncated for column 'd' at row 1

Warning	1265	Data truncated for column 'd' at row 2

Warning	1265	Data truncated for column 'd' at row 3

Warning	1265	Data truncated for column 'd' at row 4

create table t2(a tinyint NOT NULL, b char(3));

insert into t2 select b,c from t1;

Warnings:

Warning	1265	Data truncated for column 'b' at row 1

Warning	1265	Data truncated for column 'b' at row 2

Warning	1265	Data truncated for column 'b' at row 3

Warning	1048	Column 'a' cannot be null

Warning	1265	Data truncated for column 'b' at row 4

insert into t2(b) values('mysqlab');

Warnings:

Warning	1364	Field 'a' doesn't have a default value

Warning	1265	Data truncated for column 'b' at row 1

set sql_warnings=1;

insert into t2(b) values('mysqlab');

Warnings:

Warning	1364	Field 'a' doesn't have a default value

Warning	1265	Data truncated for column 'b' at row 1

set sql_warnings=0;

drop table t1, t2;

create table t1(a char(10));

alter table t1 add b char;

set max_error_count=10;

update t1 set b=a;

Warnings:

Warning	1265	Data truncated for column 'b' at row 1

Warning	1265	Data truncated for column 'b' at row 2

Warning	1265	Data truncated for column 'b' at row 3

Warning	1265	Data truncated for column 'b' at row 4

Warning	1265	Data truncated for column 'b' at row 5

Warning	1265	Data truncated for column 'b' at row 6

Warning	1265	Data truncated for column 'b' at row 7

Warning	1265	Data truncated for column 'b' at row 8

Warning	1265	Data truncated for column 'b' at row 9

Warning	1265	Data truncated for column 'b' at row 10

select @@warning_count;

@@warning_count

50

set max_error_count=0;

show variables like 'max_error_count';

Variable_name	Value

max_error_count	0

update t1 set b='hi';

Warnings:

select @@warning_count;

@@warning_count

50

show warnings;

Level	Code	Message

set max_error_count=65535;

show variables like 'max_error_count';

Variable_name	Value

max_error_count	65535

set max_error_count=10;

show variables like 'max_error_count';

Variable_name	Value

max_error_count	10

drop table t1;

create table t1 (a int);

insert into t1 (a) values (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);

update t1 set a='abc';

Warnings:

Warning	1366	Incorrect integer value: 'abc' for column 'a' at row 1

Warning	1366	Incorrect integer value: 'abc' for column 'a' at row 2

Warning	1366	Incorrect integer value: 'abc' for column 'a' at row 3

Warning	1366	Incorrect integer value: 'abc' for column 'a' at row 4

Warning	1366	Incorrect integer value: 'abc' for column 'a' at row 5

Warning	1366	Incorrect integer value: 'abc' for column 'a' at row 6

Warning	1366	Incorrect integer value: 'abc' for column 'a' at row 7

Warning	1366	Incorrect integer value: 'abc' for column 'a' at row 8

Warning	1366	Incorrect integer value: 'abc' for column 'a' at row 9

Warning	1366	Incorrect integer value: 'abc' for column 'a' at row 10

show warnings limit 2, 1;

Level	Code	Message

Warning	1366	Incorrect integer value: 'abc' for column 'a' at row 3

show warnings limit 0, 10;

Level	Code	Message

Warning	1366	Incorrect integer value: 'abc' for column 'a' at row 1

Warning	1366	Incorrect integer value: 'abc' for column 'a' at row 2

Warning	1366	Incorrect integer value: 'abc' for column 'a' at row 3

Warning	1366	Incorrect integer value: 'abc' for column 'a' at row 4

Warning	1366	Incorrect integer value: 'abc' for column 'a' at row 5

Warning	1366	Incorrect integer value: 'abc' for column 'a' at row 6

Warning	1366	Incorrect integer value: 'abc' for column 'a' at row 7

Warning	1366	Incorrect integer value: 'abc' for column 'a' at row 8

Warning	1366	Incorrect integer value: 'abc' for column 'a' at row 9

Warning	1366	Incorrect integer value: 'abc' for column 'a' at row 10

show warnings limit 9, 1;

Level	Code	Message

Warning	1366	Incorrect integer value: 'abc' for column 'a' at row 10

show warnings limit 10, 1;

Level	Code	Message

show warnings limit 9, 2;

Level	Code	Message

Warning	1366	Incorrect integer value: 'abc' for column 'a' at row 10

show warnings limit 0, 0;

Level	Code	Message

show warnings limit 1;

Level	Code	Message

Warning	1366	Incorrect integer value: 'abc' for column 'a' at row 1

show warnings limit 0;

Level	Code	Message

show warnings limit 1, 0;

Level	Code	Message

select * from t1 limit 0;

a

select * from t1 limit 1, 0;

a

select * from t1 limit 0, 0;

a

drop table t1;

End of 4.1 tests

CREATE TABLE t1( f1 CHAR(20) );

CREATE TABLE t2( f1 CHAR(20), f2 CHAR(25) );

CREATE TABLE t3( f1 CHAR(20), f2 CHAR(25), f3 DATE );

INSERT INTO t1 VALUES ( 'a`' );

INSERT INTO t2 VALUES ( 'a`', 'a`' );

INSERT INTO t3 VALUES ( 'a`', 'a`', '1000-01-1' );

DROP PROCEDURE IF EXISTS sp1;

Warnings:

Note	1305	PROCEDURE sp1 does not exist

DROP PROCEDURE IF EXISTS sp2;

Warnings:

Note	1305	PROCEDURE sp2 does not exist

DROP PROCEDURE IF EXISTS sp3;

Warnings:

Note	1305	PROCEDURE sp3 does not exist

CREATE PROCEDURE sp1()

BEGIN

DECLARE x NUMERIC ZEROFILL;

SELECT f1 INTO x FROM t1 LIMIT 1;

END//

CREATE PROCEDURE sp2()

BEGIN

DECLARE x NUMERIC ZEROFILL;

SELECT f1 INTO x FROM t2 LIMIT 1;

END//

CREATE PROCEDURE sp3()

BEGIN

DECLARE x NUMERIC ZEROFILL;

SELECT f1 INTO x FROM t3 LIMIT 1;

END//

CALL sp1();

Warnings:

Warning	1366	Incorrect decimal value: 'a`' for column 'x' at row 1

CALL sp2();

Warnings:

Warning	1366	Incorrect decimal value: 'a`' for column 'x' at row 1

CALL sp3();

Warnings:

Warning	1366	Incorrect decimal value: 'a`' for column 'x' at row 1

DROP PROCEDURE IF EXISTS sp1;

CREATE PROCEDURE sp1()

BEGIN

declare x numeric unsigned zerofill;

SELECT f1 into x from t2 limit 1;

END//

CALL sp1();

Warnings:

Warning	1366	Incorrect decimal value: 'a`' for column 'x' at row 1

DROP TABLE t1;

DROP TABLE t2;

DROP TABLE t3;

DROP PROCEDURE sp1;

DROP PROCEDURE sp2;

DROP PROCEDURE sp3;

create table t1 (c_char char(255), c_varchar varchar(255), c_tinytext tinytext);

create table t2 (c_tinyblob tinyblob);

set @c = repeat(' ', 256);

set @q = repeat('q', 256);

set sql_mode = '';

insert into t1 values(@c, @c, @c);

Warnings:

Note	1265	Data truncated for column 'c_char' at row 1

Note	1265	Data truncated for column 'c_varchar' at row 1

Note	1265	Data truncated for column 'c_tinytext' at row 1

insert into t2 values(@c);

Warnings:

Warning	1265	Data truncated for column 'c_tinyblob' at row 1

insert into t1 values(@q, @q, @q);

Warnings:

Warning	1265	Data truncated for column 'c_char' at row 1

Warning	1265	Data truncated for column 'c_varchar' at row 1

Warning	1265	Data truncated for column 'c_tinytext' at row 1

insert into t2 values(@q);

Warnings:

Warning	1265	Data truncated for column 'c_tinyblob' at row 1

set sql_mode = 'traditional';

insert into t1 values(@c, @c, @c);

Warnings:

Note	1265	Data truncated for column 'c_char' at row 1

Note	1265	Data truncated for column 'c_varchar' at row 1

Note	1265	Data truncated for column 'c_tinytext' at row 1

insert into t2 values(@c);

ERROR 22001: Data too long for column 'c_tinyblob' at row 1

insert into t1 values(@q, NULL, NULL);

ERROR 22001: Data too long for column 'c_char' at row 1

insert into t1 values(NULL, @q, NULL);

ERROR 22001: Data too long for column 'c_varchar' at row 1

insert into t1 values(NULL, NULL, @q);

ERROR 22001: Data too long for column 'c_tinytext' at row 1

insert into t2 values(@q);

ERROR 22001: Data too long for column 'c_tinyblob' at row 1

drop table t1, t2;

End of 5.0 tests