strict.test

视频监控网络部分的协议ddns,的模块的实现代码,请大家大胆指正.

# Testing INSERT with CHAR/VARCHAR

CREATE TABLE t1 (col1 CHAR(5), col2 VARCHAR(6));
INSERT INTO t1 VALUES ('hello', 'hello'),('he', 'he'),('hello   ', 'hello ');
--error 1406
INSERT INTO t1 (col1) VALUES ('hellobob');
--error 1406
INSERT INTO t1 (col2) VALUES ('hellobob');
INSERT INTO t1 (col2) VALUES ('hello  ');
--error 1406
UPDATE t1 SET col1 ='hellobob' WHERE col1 ='he';
--error 1406
UPDATE t1 SET col2 ='hellobob' WHERE col2 ='he';
INSERT IGNORE INTO t1 VALUES ('hellobob', 'hellobob');
UPDATE IGNORE t1 SET col2 ='hellotrudy' WHERE col2 ='he';
SELECT * FROM t1;
DROP TABLE t1;

# Testing INSERT with ENUM

CREATE TABLE t1 (col1 enum('red','blue','green'));
INSERT INTO t1 VALUES ('red'),('blue'),('green');
--error 1265
INSERT INTO t1 (col1) VALUES ('yellow');
--error 1265
INSERT INTO t1 (col1) VALUES ('redd');
--error 1265
INSERT INTO t1 VALUES ('');
--error 1265
UPDATE t1 SET col1 ='yellow' WHERE col1 ='green';
INSERT IGNORE INTO t1 VALUES ('yellow');
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 1048
INSERT INTO t1 (col1,col2,col3) VALUES (NULL, '', '2004-01-01');
--error 1048
INSERT INTO t1 (col1,col2,col3) VALUES (102, NULL, '2004-01-01');
--error 1048
INSERT INTO t1 VALUES (103,'',NULL);
--error 1048
UPDATE t1 SET col1=NULL WHERE col1 =100;
--error 1048
UPDATE t1 SET col2 =NULL WHERE col2 ='hello';
--error 1048
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);
SHOW CREATE TABLE t1;
INSERT INTO t1 VALUES (1, 'hello');
INSERT INTO t1 (col2) VALUES ('hello2');
--error 1048
INSERT INTO t1 (col2) VALUES (NULL);
--error 1364
INSERT INTO t1 (col1) VALUES (2);
--error 1364
INSERT INTO t1 VALUES(default(col1),default(col2));
--error 1364
INSERT INTO t1 (col1) SELECT 1;
--error 1048
INSERT INTO t1 SELECT 1,NULL;
INSERT IGNORE INTO t1 values (NULL,NULL);
INSERT IGNORE INTO t1 (col1) values (3);
INSERT IGNORE INTO t1 () values ();
SELECT * FROM t1;
DROP TABLE t1;

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

set sql_mode='traditional';
create table t1 (charcol char(255), varcharcol varchar(255),
binarycol binary(255), varbinarycol varbinary(255), tinytextcol tinytext,
tinyblobcol tinyblob);
--error 1406
insert into t1 (charcol) values (repeat('x',256));
--error 1406
insert into t1 (varcharcol) values (repeat('x',256));
--error 1406
insert into t1 (binarycol) values (repeat('x',256));
--error 1406
insert into t1 (varbinarycol) values (repeat('x',256));
--error 1406
insert into t1 (tinytextcol) values (repeat('x',256));
--error 1406
insert into t1 (tinyblobcol) values (repeat('x',256));
select * from t1;
drop table t1;

#
# Bug #5902: STR_TO_DATE() didn't give errors in traditional mode
#

set sql_mode='traditional';
create table t1 (col1 datetime);
--error 1292
insert into t1 values(STR_TO_DATE('31.10.2004 15.30 abc','%d.%m.%Y %H.%i'));
--error 1411
insert into t1 values(STR_TO_DATE('32.10.2004 15.30','%d.%m.%Y %H.%i'));
--error 1411
insert into t1 values(STR_TO_DATE('2004.12.12 22:22:33 AM','%Y.%m.%d %r'));
--error 1411
insert into t1 values(STR_TO_DATE('2004.12.12 abc','%Y.%m.%d %T'));
set sql_mode='';
insert into t1 values(STR_TO_DATE('31.10.2004 15.30 abc','%d.%m.%Y %H.%i'));
insert into t1 values(STR_TO_DATE('32.10.2004 15.30','%d.%m.%Y %H.%i'));
insert into t1 values(STR_TO_DATE('2004.12.12 22:22:33 AM','%Y.%m.%d %r'));
insert into t1 values(STR_TO_DATE('2004.12.12 abc','%Y.%m.%d %T'));

# Some correct values, just to test the functions
insert into t1 values(STR_TO_DATE('31.10.2004 15.30','%d.%m.%Y %H.%i'));
insert into t1 values(STR_TO_DATE('2004.12.12 11:22:33 AM','%Y.%m.%d %r'));
insert into t1 values(STR_TO_DATE('2004.12.12 10:22:59','%Y.%m.%d %T'));

select * from t1;

# Check that select don't abort even in strict mode (for now)
set sql_mode='traditional';

select count(*) from t1 where STR_TO_DATE('2004.12.12 10:22:61','%Y.%m.%d %T') IS NULL;

drop table t1;

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

create table t1 (col1 char(3), col2 integer);
--error 1292
insert into t1 (col1) values (cast(1000 as char(3)));
--error 1292
insert into t1 (col1) values (cast(1000E+0 as char(3)));
--error 1292
insert into t1 (col1) values (cast(1000.0 as char(3)));
--error 1292
insert into t1 (col2) values (cast('abc' as signed integer));
--error 1292
insert into t1 (col2) values (10E+0 + 'a');
--error 1292
insert into t1 (col2) values (cast('10a' as unsigned integer));
insert into t1 (col2) values (cast('10' as unsigned integer));
insert into t1 (col2) values (cast('10' as signed integer));
insert into t1 (col2) values (10E+0 + '0 ');
select * from t1;
drop table t1;

#
# Zero dates using numbers was not checked properly (Bug #5933 & #6145)
#

create table t1 (col1 date, col2 datetime, col3 timestamp);
--error 1292
insert into t1 values (0,0,0);
--error 1292
insert into t1 values (0.0,0.0,0.0);
--error 1292
insert into t1 (col1) values (convert('0000-00-00',date));
--error 1292
insert into t1 (col1) values (cast('0000-00-00' as date));

set sql_mode='no_zero_date';
insert into t1 values (0,0,0);
insert into t1 values (0.0,0.0,0.0);
drop table t1;
set sql_mode='traditional';
create table t1 (col1 date);
insert ignore into t1 values ('0000-00-00');
--error 1292
insert into t1 select * from t1;
insert ignore into t1 values ('0000-00-00');
insert ignore into t1 (col1) values (cast('0000-00-00' as date));
--error 1292
insert into t1 select * from t1;
--error 1292
alter table t1 modify col1 datetime;
alter ignore table t1 modify col1 datetime;
--error 1292
insert into t1 select * from t1;
select * from t1;
drop table t1;

#
# Test of inserting an invalid value via a stored procedure (Bug #5907)
#
create table t1 (col1 tinyint);
drop procedure if exists t1;
delimiter |;
create procedure t1 () begin declare exit handler for sqlexception
select'a'; insert into t1 values (200); end;|
delimiter ;|
call t1();
select * from t1;
drop procedure t1;
drop table t1;

#
# Restore mode
#
set sql_mode=@org_mode;

# Test fields with no default value that are NOT NULL (Bug #5986)
SET @@sql_mode = 'traditional';
CREATE TABLE t1 (i int not null);
--error 1364
INSERT INTO t1 VALUES ();
--error 1364
INSERT INTO t1 VALUES (DEFAULT);
--error 1364
INSERT INTO t1 VALUES (DEFAULT(i));
ALTER TABLE t1 ADD j int;
--error 1364
INSERT INTO t1 SET j = 1;
--error 1364
INSERT INTO t1 SET j = 1, i = DEFAULT;
--error 1364
INSERT INTO t1 SET j = 1, i = DEFAULT(i);
--error 1364
INSERT INTO t1 VALUES (DEFAULT,1);
DROP TABLE t1;
SET @@sql_mode = '';
CREATE TABLE t1 (i int not null);
INSERT INTO t1 VALUES ();
INSERT INTO t1 VALUES (DEFAULT);
# DEFAULT(i) is an error even with the default sql_mode
--error 1364
INSERT INTO t1 VALUES (DEFAULT(i));
ALTER TABLE t1 ADD j int;
INSERT INTO t1 SET j = 1;
INSERT INTO t1 SET j = 1, i = DEFAULT;
--error 1364
INSERT INTO t1 SET j = 1, i = DEFAULT(i);
INSERT INTO t1 VALUES (DEFAULT,1);
DROP TABLE t1;

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

set @@sql_mode='traditional';
--error 1074
create table t1(a varchar(65537));
--error 1074
create table t1(a varbinary(65537));

#
# Bug #9881: problem with altering table
#

set @@sql_mode='traditional';
create table t1(a int, b date not null);                                       
alter table t1 modify a bigint unsigned not null;
show create table t1;
drop table t1;

#
# Bug #5906: handle invalid date due to conversion
#
set @@sql_mode='traditional';
create table t1 (d date);
--error 1292
insert into t1 values ('2000-10-00');
--error 1292
insert into t1 values (1000);
insert into t1 values ('2000-10-01');
--error 1292
update t1 set d = 1100;
select * from t1;
drop table t1;

#
# Bug #11964: alter table with timestamp field
#

set @@sql_mode='traditional';
create table t1(a int, b timestamp);
alter table t1 add primary key(a);
show create table t1;
drop table t1;
create table t1(a int, b timestamp default 20050102030405);
alter table t1 add primary key(a);
show create table t1;
drop table t1;

#
# BIT fields
#

set @@sql_mode='traditional';
create table t1(a bit(2));
--error 1406
insert into t1 values(b'101');
select * from t1;
drop table t1;

#
# Bug#17626 CREATE TABLE ... SELECT failure with TRADITIONAL SQL mode
#
set sql_mode='traditional';
create table t1 (date date not null);
create table t2 select date from t1;
show create table t2;
drop table t2,t1;
set @@sql_mode= @org_mode;

#
# Bug #13934 Silent truncation of table comments
#
set @@sql_mode='traditional';
--error 1105
create table t1 (i int)
comment '123456789*123456789*123456789*123456789*123456789*
         123456789*123456789*123456789*123456789*123456789*';
--error 1105
create table t1 (
i int comment
'123456789*123456789*123456789*123456789*
 123456789*123456789*123456789*123456789*
 123456789*123456789*123456789*123456789*
 123456789*123456789*123456789*123456789*
 123456789*123456789*123456789*123456789*
 123456789*123456789*123456789*123456789*
 123456789*123456789*123456789*123456789*');
set @@sql_mode= @org_mode;
create table t1
(i int comment
 '123456789*123456789*123456789*123456789*
  123456789*123456789*123456789*123456789*
  123456789*123456789*123456789*123456789*
  123456789*123456789*123456789*123456789*
  123456789*123456789*123456789*123456789*
  123456789*123456789*123456789*123456789*
  123456789*123456789*123456789*123456789*');

select column_name, column_comment from information_schema.columns where
table_schema = 'test' and table_name = 't1';
drop table t1;

set names utf8;
create table t1 (i int)
comment '123456789*123456789*123456789*123456789*123456789*123456789*';
show create table t1;
drop table t1;

#
# Bug #26359: Strings becoming truncated and converted to numbers under STRICT mode
#
set sql_mode= 'traditional';
create table t1(col1 tinyint, col2 tinyint unsigned, 
  col3 smallint, col4 smallint unsigned,
  col5 mediumint, col6 mediumint unsigned,
  col7 int, col8 int unsigned,
  col9 bigint, col10 bigint unsigned);
--error 1366
insert into t1(col1) values('-');
--error 1366
insert into t1(col2) values('+');
--error 1366
insert into t1(col3) values('-');
--error 1366
insert into t1(col4) values('+');
--error 1366
insert into t1(col5) values('-');
--error 1366
insert into t1(col6) values('+');
--error 1366
insert into t1(col7) values('-');
--error 1366
insert into t1(col8) values('+');
--error 1366
insert into t1(col9) values('-');
--error 1366
insert into t1(col10) values('+');
drop table t1;

#
# Bug #27176: Assigning a string to an year column has unexpected results
#
set sql_mode='traditional';
create table t1(a year);
--error 1366
insert into t1 values ('-');
--error 1366
insert into t1 values ('+');
--error 1366
insert into t1 values ('');
--error 1265
insert into t1 values ('2000a');
--error 1265
insert into t1 values ('2E3x');
drop table t1;

#
# Bug#27069 set with identical elements are created
#
set sql_mode='traditional';
--error 1291
create table t1 (f1 set('a','a'));
--error 1291
create table t1 (f1 enum('a','a'));

#
# Bug #22824: strict, datetime, NULL, wrong warning
#
set @@sql_mode='NO_ZERO_DATE';
create table t1(a datetime not null);
select count(*) from t1 where a is null;
drop table t1;

--echo End of 5.0 tests