~drizzle-trunk/drizzle/development

#

# Test some warnings

#

--disable_warnings

drop table if exists t1, t2;

--enable_warnings

SET SQL_WARNINGS=1;

create table t1 (a int);

--error 1050

create table t1 (a int);

show count(*) errors;

show errors;

show warnings;

--error 1115

create table t2(a int) default charset qwerty;

show count(*) errors;

show errors;

--error 1064

create table t (i);

show count(*) errors;

show errors;

insert into t1 values (1);

insert into t1 values ("hej");

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

set SQL_WARNINGS=1;

insert into t1 values ("hej");

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

drop table t1;

set SQL_WARNINGS=0;

#

# Test other warnings

#

drop temporary table if exists not_exists;

drop table if exists not_exists_table;

show warnings limit 1;

drop database if exists not_exists_db;

show count(*) warnings;

create table t1(id int);

create table if not exists t1(id int);

--disable_ps_protocol

select @@warning_count;

--enable_ps_protocol

drop table t1;

#

# Test warnings for LOAD DATA INFILE

#

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 ',';

# PS doesn't work good with @@warning_count

--disable_ps_protocol

select @@warning_count;

--enable_ps_protocol

drop table t1;

#

# Warnings from basic INSERT, UPDATE and ALTER commands

#

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');

alter table t1 modify c char(4);

alter table t1 add d char(2);

--error 1048

update t1 set a=NULL where a=10;

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

update t1 set d=c;

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

insert into t2 select b,c from t1;

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

set sql_warnings=1;

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

set sql_warnings=0;

drop table t1, t2;

#

# Test for max_error_count

#

create table t1(a char(10));

let $1=50;

disable_query_log;

while ($1)

{

  eval insert into t1 values('mysql ab');

  dec $1;

}

enable_query_log;

alter table t1 add b char;

set max_error_count=10;

update t1 set b=a;

--disable_ps_protocol

select @@warning_count;

--enable_ps_protocol

# Bug#9072

set max_error_count=0;

show variables like 'max_error_count';

update t1 set b='hi';

--disable_ps_protocol

select @@warning_count;

--enable_ps_protocol

show warnings;

set max_error_count=65535;

show variables like 'max_error_count';

set max_error_count=10;

show variables like 'max_error_count';

drop table t1;

#

# Tests for show warnings limit a, b

#

create table t1 (a int);

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

# should generate 10 warnings

update t1 set a='abc';

show warnings limit 2, 1;

show warnings limit 0, 10;

show warnings limit 9, 1;

show warnings limit 10, 1;

show warnings limit 9, 2;

show warnings limit 0, 0;

show warnings limit 1;

show warnings limit 0;

show warnings limit 1, 0;

# make sure behaviour is consistent with select ... limit

select * from t1 limit 0;

select * from t1 limit 1, 0;

select * from t1 limit 0, 0;

drop table t1;

--echo End of 4.1 tests

#

# Bug#20778: strange characters in warning message 1366 when called in SP

#

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;

DROP PROCEDURE IF EXISTS sp2;

DROP PROCEDURE IF EXISTS sp3;

delimiter //;

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//

delimiter ;//

CALL sp1();

CALL sp2();

CALL sp3();

DROP PROCEDURE IF EXISTS sp1;

delimiter //;

CREATE PROCEDURE sp1()

BEGIN

declare x numeric unsigned zerofill;

SELECT f1 into x from t2 limit 1;

END//

delimiter ;//

CALL sp1();

DROP TABLE t1;

DROP TABLE t2;

DROP TABLE t3;

DROP PROCEDURE sp1;

DROP PROCEDURE sp2;

DROP PROCEDURE sp3;

#

# Bug#30059: End-space truncation warnings are inconsistent or incorrect

#

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

create table t2 (c_tinyblob tinyblob); # not affected by bug, for regression testing

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

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

set sql_mode = '';

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

insert into t2 values(@c);

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

insert into t2 values(@q);

set sql_mode = 'traditional';

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

--error 1406

insert into t2 values(@c);

--error 1406

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

--error 1406

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

--error 1406

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

--error 1406

insert into t2 values(@q);

drop table t1, t2;

--echo End of 5.0 tests