← Back to branch summary

~drizzle-trunk/drizzle/development

1 by brian
clean slate 
1
 
# Testing of "strict" mode

2
 









3



-- source include/have_innodb.inc










4












5



set @org_mode=@@sql_mode;










6



set @@sql_mode='ansi,traditional';










7



select @@sql_mode;










8












9



--disable_warnings










10



DROP TABLE IF EXISTS t1, t2;










11



--enable_warnings










12












13



# Test INSERT with DATE










14












15



CREATE TABLE t1 (col1 date);










16



INSERT INTO t1 VALUES('2004-01-01'),('2004-02-29');










17



INSERT INTO t1 VALUES('0000-10-31');










18












19



# All test cases expected to fail should return 










20



#      SQLSTATE 22007 <invalid date value>










21



--error 1292










22



INSERT INTO t1 VALUES('2004-0-31');










23



--error 1292










24



INSERT INTO t1 VALUES('2004-01-02'),('2004-0-31');










25



--error 1292










26



INSERT INTO t1 VALUES('2004-10-0');










27



--error 1292










28



INSERT INTO t1 VALUES('2004-09-31');










29



--error 1292










30



INSERT INTO t1 VALUES('2004-10-32');










31



--error 1292










32



INSERT INTO t1 VALUES('2003-02-29');










33



--error 1292










34



INSERT INTO t1 VALUES('2004-13-15');










35



--error 1292










36



INSERT INTO t1 VALUES('0000-00-00');










37



# Standard says we should return SQLSTATE 22018










38



--error 1292










39



INSERT INTO t1 VALUES ('59');










40












41



# Test the different related modes










42



set @@sql_mode='STRICT_ALL_TABLES';










43



INSERT INTO t1 VALUES('2004-01-03'),('2004-0-31');










44



set @@sql_mode='STRICT_ALL_TABLES,NO_ZERO_IN_DATE';










45



--error 1292










46



INSERT INTO t1 VALUES('2004-0-30');










47



--error 1292










48



INSERT INTO t1 VALUES('2004-01-04'),('2004-0-31'),('2004-01-05');










49



INSERT INTO t1 VALUES('0000-00-00');










50



INSERT IGNORE INTO t1 VALUES('2004-0-29');










51



set @@sql_mode='STRICT_ALL_TABLES,NO_ZERO_DATE';










52



--error 1292










53



INSERT INTO t1 VALUES('0000-00-00');










54



INSERT IGNORE INTO t1 VALUES('0000-00-00');










55



INSERT INTO t1 VALUES ('2004-0-30');










56



--error 1292










57



INSERT INTO t1 VALUES ('2004-2-30');










58



set @@sql_mode='STRICT_ALL_TABLES,ALLOW_INVALID_DATES';










59



INSERT INTO t1 VALUES ('2004-2-30');










60



set @@sql_mode='ansi,traditional';










61



INSERT IGNORE INTO t1 VALUES('2004-02-29'),('2004-13-15'),('0000-00-00');










62












63



select * from t1;










64



drop table t1;










65












66



# Test difference in behaviour with InnoDB and MyISAM tables










67












68



set @@sql_mode='strict_trans_tables';










69



CREATE TABLE t1 (col1 date) engine=myisam;










70



--error 1292










71



INSERT INTO t1 VALUES('2004-13-31'),('2004-1-1');










72



INSERT INTO t1 VALUES ('2004-1-2'), ('2004-13-31'),('2004-1-3');










73



INSERT IGNORE INTO t1 VALUES('2004-13-31'),('2004-1-4');










74



--error 1292










75



INSERT INTO t1 VALUES ('2003-02-29');










76



INSERT ignore INTO t1 VALUES('2003-02-30');










77



set @@sql_mode='STRICT_ALL_TABLES,ALLOW_INVALID_DATES';










78



INSERT ignore INTO t1 VALUES('2003-02-31');










79



select * from t1;










80



drop table t1;










81












82



set @@sql_mode='strict_trans_tables';










83



CREATE TABLE t1 (col1 date) engine=innodb;










84



--error 1292










85



INSERT INTO t1 VALUES('2004-13-31'),('2004-1-1');










86



--error 1292










87



INSERT INTO t1 VALUES ('2004-1-2'), ('2004-13-31'),('2004-1-3');










88



INSERT IGNORE INTO t1 VALUES('2004-13-31'),('2004-1-4');










89



--error 1292










90



INSERT INTO t1 VALUES ('2003-02-29');










91



INSERT ignore INTO t1 VALUES('2003-02-30');










92



set @@sql_mode='STRICT_ALL_TABLES,ALLOW_INVALID_DATES';










93



INSERT ignore INTO t1 VALUES('2003-02-31');










94



select * from t1;










95



drop table t1;










96



set @@sql_mode='ansi,traditional';










97












98



# Test INSERT with DATETIME










99












100



CREATE TABLE t1 (col1 datetime);










101



INSERT INTO t1 VALUES('2004-10-31 15:30:00'),('2004-02-29 15:30:00');










102



INSERT INTO t1 VALUES('0000-10-31 15:30:00');










103












104



# All test cases expected to fail should return 










105



#      SQLSTATE 22007 <invalid datetime value>










106



--error 1292










107



INSERT INTO t1 VALUES('2004-0-31 15:30:00');










108



--error 1292










109



INSERT INTO t1 VALUES('2004-10-0 15:30:00');










110



--error 1292










111



INSERT INTO t1 VALUES('2004-09-31 15:30:00');










112



--error 1292










113



INSERT INTO t1 VALUES('2004-10-32 15:30:00');










114



--error 1292










115



INSERT INTO t1 VALUES('2003-02-29 15:30:00');










116



--error 1292










117



INSERT INTO t1 VALUES('2004-13-15 15:30:00');










118



--error 1292










119



INSERT INTO t1 VALUES('0000-00-00 15:30:00');










120



# Standard says we should return SQLSTATE 22018










121



--error 1292










122



INSERT INTO t1 VALUES ('59');










123



select * from t1;










124



drop table t1;










125












126



# Test INSERT with TIMESTAMP










127












128



CREATE TABLE t1 (col1 timestamp);










129



INSERT INTO t1 VALUES('2004-10-31 15:30:00'),('2004-02-29 15:30:00');










130












131



# All test cases expected to fail should return 










132



#      SQLSTATE 22007 <invalid datetime value>










133



# Standard says we should return ok, but we can't as this is out of range










134



--error 1292










135



INSERT INTO t1 VALUES('0000-10-31 15:30:00');










136



--error 1292










137



INSERT INTO t1 VALUES('2004-0-31 15:30:00');










138



--error 1292










139



INSERT INTO t1 VALUES('2004-10-0 15:30:00');










140



--error 1292










141



INSERT INTO t1 VALUES('2004-09-31 15:30:00');










142



--error 1292










143



INSERT INTO t1 VALUES('2004-10-32 15:30:00');










144



--error 1292










145



INSERT INTO t1 VALUES('2003-02-29 15:30:00');










146



--error 1292










147



INSERT INTO t1 VALUES('2004-13-15 15:30:00');










148



--error 1292










149



INSERT INTO t1 VALUES('2004-02-29 25:30:00');










150



--error 1292










151



INSERT INTO t1 VALUES('2004-02-29 15:65:00');










152



--error 1292










153



INSERT INTO t1 VALUES('2004-02-29 15:31:61');










154



--error 1292










155



INSERT INTO t1 VALUES('0000-00-00 15:30:00');










156



--error 1292










157



INSERT INTO t1 VALUES('0000-00-00 00:00:00');










158



INSERT IGNORE INTO t1 VALUES('0000-00-00 00:00:00');










159



# Standard says we should return SQLSTATE 22018










160



--error 1292










161



INSERT INTO t1 VALUES ('59');










162












163



set @@sql_mode='STRICT_ALL_TABLES,ALLOW_INVALID_DATES';










164



--error 1292










165



INSERT INTO t1 VALUES('2004-0-31 15:30:00');










166



--error 1292










167



INSERT INTO t1 VALUES('2004-10-0 15:30:00');










168



--error 1292










169



INSERT INTO t1 VALUES('2004-10-32 15:30:00');










170



--error 1292










171



INSERT INTO t1 VALUES('2004-02-30 15:30:04');










172



INSERT INTO t1 VALUES('0000-00-00 00:00:00');










173



set @@sql_mode='STRICT_ALL_TABLES,NO_ZERO_IN_DATE';










174



INSERT INTO t1 VALUES('0000-00-00 00:00:00');










175



set @@sql_mode='STRICT_ALL_TABLES,NO_ZERO_DATE';










176



--error 1292










177



INSERT INTO t1 VALUES('0000-00-00 00:00:00');










178



set @@sql_mode='ansi,traditional';










179



SELECT * FROM t1;










180



DROP TABLE t1;










181












182












183



#### Test INSERT with STR_TO_DATE into DATE/DATETIME/TIMESTAMP










184












185



CREATE TABLE t1 (col1 date, col2 datetime, col3 timestamp);










186












187



INSERT INTO t1 (col1) VALUES (STR_TO_DATE('15.10.2004','%d.%m.%Y'));










188



INSERT INTO t1 (col2) VALUES (STR_TO_DATE('15.10.2004 10.15','%d.%m.%Y %H.%i'));










189



INSERT INTO t1 (col3) VALUES (STR_TO_DATE('15.10.2004 10.15','%d.%m.%Y %H.%i'));










190












191



## Test INSERT with STR_TO_DATE into DATE










192



#       All test cases expected to fail should return 










193



#       SQLSTATE 22007 <invalid date value>










194












195



INSERT INTO t1 (col1) VALUES(STR_TO_DATE('31.10.0000 15.30','%d.%m.%Y %H.%i'));










196












197



--error 1292










198



INSERT INTO t1 (col1) VALUES(STR_TO_DATE('31.0.2004 15.30','%d.%m.%Y %H.%i'));










199



--error 1292










200



INSERT INTO t1 (col1) VALUES(STR_TO_DATE('0.10.2004 15.30','%d.%m.%Y %H.%i'));










201



--error 1292










202



INSERT INTO t1 (col1) VALUES(STR_TO_DATE('31.9.2004 15.30','%d.%m.%Y %H.%i'));










203



--error 1411










204



INSERT INTO t1 (col1) VALUES(STR_TO_DATE('32.10.2004 15.30','%d.%m.%Y %H.%i'));










205



--error 1292










206



INSERT INTO t1 (col1) VALUES(STR_TO_DATE('29.02.2003 15.30','%d.%m.%Y %H.%i'));










207



--error 1411










208



INSERT INTO t1 (col1) VALUES(STR_TO_DATE('15.13.2004 15.30','%d.%m.%Y %H.%i'));










209



--error 1292










210



INSERT INTO t1 (col1) VALUES(STR_TO_DATE('00.00.0000','%d.%m.%Y'));










211












212



## Test INSERT with STR_TO_DATE into DATETIME










213



#       All test cases expected to fail should return 










214



#       SQLSTATE 22007 <invalid datetime value>










215












216



INSERT INTO t1 (col2) VALUES(STR_TO_DATE('31.10.0000 15.30','%d.%m.%Y %H.%i'));










217












218



--error 1292










219



INSERT INTO t1 (col2) VALUES(STR_TO_DATE('31.0.2004 15.30','%d.%m.%Y %H.%i'));










220



--error 1292










221



INSERT INTO t1 (col2) VALUES(STR_TO_DATE('0.10.2004 15.30','%d.%m.%Y %H.%i'));










222



--error 1292










223



INSERT INTO t1 (col2) VALUES(STR_TO_DATE('31.9.2004 15.30','%d.%m.%Y %H.%i'));










224



--error 1411










225



INSERT INTO t1 (col2) VALUES(STR_TO_DATE('32.10.2004 15.30','%d.%m.%Y %H.%i'));










226



--error 1292










227



INSERT INTO t1 (col2) VALUES(STR_TO_DATE('29.02.2003 15.30','%d.%m.%Y %H.%i'));










228



--error 1411










229



INSERT INTO t1 (col2) VALUES(STR_TO_DATE('15.13.2004 15.30','%d.%m.%Y %H.%i'));










230



--error 1292










231



INSERT INTO t1 (col2) VALUES(STR_TO_DATE('00.00.0000','%d.%m.%Y'));










232












233



## Test INSERT with STR_TO_DATE into TIMESTAMP










234



#       All test cases expected to fail should return 










235



#       SQLSTATE 22007 <invalid datetime value>










236












237



--error 1292










238



INSERT INTO t1 (col3) VALUES(STR_TO_DATE('31.10.0000 15.30','%d.%m.%Y %H.%i'));










239



--error 1292










240



INSERT INTO t1 (col3) VALUES(STR_TO_DATE('31.0.2004 15.30','%d.%m.%Y %H.%i'));










241



--error 1292










242



INSERT INTO t1 (col3) VALUES(STR_TO_DATE('0.10.2004 15.30','%d.%m.%Y %H.%i'));










243



--error 1292










244



INSERT INTO t1 (col3) VALUES(STR_TO_DATE('31.9.2004 15.30','%d.%m.%Y %H.%i'));










245



--error 1411










246



INSERT INTO t1 (col3) VALUES(STR_TO_DATE('32.10.2004 15.30','%d.%m.%Y %H.%i'));










247



--error 1292










248



INSERT INTO t1 (col3) VALUES(STR_TO_DATE('29.02.2003 15.30','%d.%m.%Y %H.%i'));










249



--error 1411










250



INSERT INTO t1 (col3) VALUES(STR_TO_DATE('15.13.2004 15.30','%d.%m.%Y %H.%i'));










251



--error 1292










252



INSERT INTO t1 (col3) VALUES(STR_TO_DATE('00.00.0000','%d.%m.%Y'));










253












254



drop table t1;










255












256












257



#### Test INSERT with CAST AS DATE/DATETIME into DATE/DATETIME/TIMESTAMP










258












259



CREATE TABLE t1 (col1 date, col2 datetime, col3 timestamp);










260












261



INSERT INTO t1 (col1) VALUES (CAST('2004-10-15' AS DATE));










262



INSERT INTO t1 (col2) VALUES (CAST('2004-10-15 10:15' AS DATETIME));










263



INSERT INTO t1 (col3) VALUES (CAST('2004-10-15 10:15' AS DATETIME));










264












265












266



## Test INSERT with CAST AS DATE into DATE










267



#       All test cases expected to fail should return 










268



#       SQLSTATE 22007 <invalid date value>










269












270



INSERT INTO t1 (col1) VALUES(CAST('0000-10-31' AS DATE));










271












272



--error 1292










273



INSERT INTO t1 (col1) VALUES(CAST('2004-10-0' AS DATE));










274



--error 1292










275



INSERT INTO t1 (col1) VALUES(CAST('2004-0-10' AS DATE));










276












277



# deactivated because of Bug#8294










278



# Bug#8294 Traditional: Misleading error message for invalid CAST to DATE










279



# --error 1292










280



# INSERT INTO t1 (col1) VALUES(CAST('2004-9-31' AS DATE));










281



# --error 1292










282



# INSERT INTO t1 (col1) VALUES(CAST('2004-10-32' AS DATE));










283



# --error 1292










284



# INSERT INTO t1 (col1) VALUES(CAST('2003-02-29' AS DATE));










285



# --error 1292










286



# INSERT INTO t1 (col1) VALUES(CAST('2004-13-15' AS DATE));










287












288



# deactivated because of Bug#6145










289



#  Bug#6145: Traditional: CONVERT and CAST should reject zero DATE values










290



--error 1292










291



INSERT INTO t1 (col1) VALUES(CAST('0000-00-00' AS DATE));










292












293



## Test INSERT with CAST AS DATETIME into DATETIME










294



#       All test cases expected to fail should return 










295



#       SQLSTATE 22007 <invalid datetime value>










296












297



INSERT INTO t1 (col2) VALUES(CAST('0000-10-31 15:30' AS DATETIME));










298












299



--error 1292










300



INSERT INTO t1 (col2) VALUES(CAST('2004-10-0 15:30' AS DATETIME));










301



--error 1292










302



INSERT INTO t1 (col2) VALUES(CAST('2004-0-10 15:30' AS DATETIME));










303












304



# deactivated because of Bug#8294










305



# Bug#8294 Traditional: Misleading error message for invalid CAST to DATE










306



#--error 1292










307



#INSERT INTO t1 (col2) VALUES(CAST('2004-9-31 15:30' AS DATETIME));










308



#--error 1292










309



#INSERT INTO t1 (col2) VALUES(CAST('2004-10-32 15:30' AS DATETIME));










310



#--error 1292










311



#INSERT INTO t1 (col2) VALUES(CAST('2003-02-29 15:30' AS DATETIME));










312



#--error 1292










313



#INSERT INTO t1 (col2) VALUES(CAST('2004-13-15 15:30' AS DATETIME));










314












315



# Bug#6145: Traditional: CONVERT and CAST should reject zero DATE values










316



--error 1292










317



INSERT INTO t1 (col2) VALUES(CAST('0000-00-00' AS DATETIME));










318












319



## Test INSERT with CAST AS DATETIME into TIMESTAMP










320



#       All test cases expected to fail should return 










321



#       SQLSTATE 22007 <invalid datetime value>










322



--error 1292










323



INSERT INTO t1 (col3) VALUES(CAST('0000-10-31 15:30' AS DATETIME));










324



# should return OK










325



# We accept this to be a failure










326












327



--error 1292










328



INSERT INTO t1 (col3) VALUES(CAST('2004-10-0 15:30' AS DATETIME));










329



--error 1292










330



INSERT INTO t1 (col3) VALUES(CAST('2004-0-10 15:30' AS DATETIME));










331



# should return SQLSTATE 22007 <invalid datetime value>










332












333



# deactivated because of Bug#8294










334



# Bug#8294 Traditional: Misleading error message for invalid CAST to DATE










335



#--error 1292










336



#INSERT INTO t1 (col3) VALUES(CAST('2004-9-31 15:30' AS DATETIME));










337



#--error 1292










338



#INSERT INTO t1 (col3) VALUES(CAST('2004-10-32 15:30' AS DATETIME));










339



#--error 1292










340



#INSERT INTO t1 (col3) VALUES(CAST('2003-02-29 15:30' AS DATETIME));










341



#--error 1292










342



#INSERT INTO t1 (col3) VALUES(CAST('2004-13-15 15:30' AS DATETIME));










343












344



# Bug#6145: Traditional: CONVERT and CAST should reject zero DATE values










345



--error 1292










346



INSERT INTO t1 (col3) VALUES(CAST('0000-00-00' AS DATETIME));










347












348



drop table t1;










349












350












351



#### Test INSERT with CONVERT to DATE/DATETIME into DATE/DATETIME/TIMESTAMP










352












353



CREATE TABLE t1 (col1 date, col2 datetime, col3 timestamp);










354












355



INSERT INTO t1 (col1) VALUES (CONVERT('2004-10-15',DATE));










356



INSERT INTO t1 (col2) VALUES (CONVERT('2004-10-15 10:15',DATETIME));










357



INSERT INTO t1 (col3) VALUES (CONVERT('2004-10-15 10:15',DATETIME));










358












359












360



## Test INSERT with CONVERT to DATE into DATE










361



#       All test cases expected to fail should return 










362



#       SQLSTATE 22007 <invalid date value>










363












364



INSERT INTO t1 (col1) VALUES(CONVERT('0000-10-31' , DATE));










365












366



--error 1292










367



INSERT INTO t1 (col1) VALUES(CONVERT('2004-10-0' , DATE));










368



--error 1292










369



INSERT INTO t1 (col1) VALUES(CONVERT('2004-0-10' , DATE));










370












371



# deactivated because of Bug#8294










372



# Bug#8294 Traditional: Misleading error message for invalid CAST to DATE










373



#--error 1292










374



#INSERT INTO t1 (col1) VALUES(CONVERT('2004-9-31' , DATE));










375



#--error 1292










376



#INSERT INTO t1 (col1) VALUES(CONVERT('2004-10-32' , DATE));










377



#--error 1292










378



#INSERT INTO t1 (col1) VALUES(CONVERT('2003-02-29' , DATE));










379



#--error 1292










380



#INSERT INTO t1 (col1) VALUES(CONVERT('2004-13-15',DATE));










381












382



# Bug#6145: Traditional: CONVERT and CAST should reject zero DATE values










383



--error 1292










384



INSERT INTO t1 (col1) VALUES(CONVERT('0000-00-00',DATE));










385












386



## Test INSERT with CONVERT to DATETIME into DATETIME










387



#       All test cases expected to fail should return 










388



#       SQLSTATE 22007 <invalid datetime value>










389












390



INSERT INTO t1 (col2) VALUES(CONVERT('0000-10-31 15:30',DATETIME));










391












392



--error 1292










393



INSERT INTO t1 (col2) VALUES(CONVERT('2004-10-0 15:30',DATETIME));










394



--error 1292










395



INSERT INTO t1 (col2) VALUES(CONVERT('2004-0-10 15:30',DATETIME));










396












397



# deactivated because of Bug#8294










398



# Bug#8294 Traditional: Misleading error message for invalid CAST to DATE










399



#--error 1292










400



#INSERT INTO t1 (col2) VALUES(CONVERT('2004-9-31 15:30',DATETIME));










401



#--error 1292










402



#INSERT INTO t1 (col2) VALUES(CONVERT('2004-10-32 15:30',DATETIME));










403



#--error 1292










404



#INSERT INTO t1 (col2) VALUES(CONVERT('2003-02-29 15:30',DATETIME));










405



#--error 1292










406



#INSERT INTO t1 (col2) VALUES(CONVERT('2004-13-15 15:30',DATETIME));










407












408



# Bug#6145: Traditional: CONVERT and CAST should reject zero DATE values










409



--error 1292










410



INSERT INTO t1 (col2) VALUES(CONVERT('0000-00-00',DATETIME));










411












412



## Test INSERT with CONVERT to DATETIME into DATETIME










413



#       All test cases expected to fail should return 










414



#       SQLSTATE 22007 <invalid datetime value>










415



--error 1292










416



INSERT INTO t1 (col3) VALUES(CONVERT('0000-10-31 15:30',DATETIME));










417



# should return OK










418



# We accept this to be a failure










419












420



--error 1292










421



INSERT INTO t1 (col3) VALUES(CONVERT('2004-10-0 15:30',DATETIME));










422



--error 1292










423



INSERT INTO t1 (col3) VALUES(CONVERT('2004-0-10 15:30',DATETIME));










424












425



# deactivated because of Bug#8294










426



# Bug#8294 Traditional: Misleading error message for invalid CAST to DATE










427



#--error 1292










428



#INSERT INTO t1 (col3) VALUES(CONVERT('2004-9-31 15:30',DATETIME));










429



#--error 1292










430



#INSERT INTO t1 (col3) VALUES(CONVERT('2004-10-32 15:30',DATETIME));










431



#--error 1292










432



#INSERT INTO t1 (col3) VALUES(CONVERT('2003-02-29 15:30',DATETIME));










433



#--error 1292










434



#INSERT INTO t1 (col3) VALUES(CONVERT('2004-13-15 15:30',DATETIME));










435












436



# Bug#6145: Traditional: CONVERT and CAST should reject zero DATE values










437



--error 1292










438



INSERT INTO t1 (col3) VALUES(CONVERT('0000-00-00',DATETIME));










439












440



drop table t1;










441












442












443



# Test INSERT with TINYINT










444












445



CREATE TABLE t1(col1 TINYINT, col2 TINYINT UNSIGNED);










446



INSERT INTO t1 VALUES(-128,0),(0,0),(127,255),('-128','0'),('0','0'),('127','255'),(-128.0,0.0),(0.0,0.0),(127.0,255.0);










447



# Test that we restored the mode checking properly after an ok query










448



SELECT MOD(col1,0) FROM t1 WHERE col1 > 0 LIMIT 2;










449



-- error 1264










450



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










451



-- error 1264










452



INSERT INTO t1 (col1) VALUES(128);










453



-- error 1264










454



INSERT INTO t1 (col2) VALUES(-1);










455



-- error 1264










456



INSERT INTO t1 (col2) VALUES(256);










457



-- error 1264










458



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










459



-- error 1264










460



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










461



-- error 1264










462



INSERT INTO t1 (col2) VALUES('-1');










463



-- error 1264










464



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










465



-- error 1264










466



INSERT INTO t1 (col1) VALUES(128.0);










467



-- error 1264










468



INSERT INTO t1 (col2) VALUES(-1.0);










469



-- error 1264










470



INSERT INTO t1 (col2) VALUES(256.0);










471



SELECT MOD(col1,0) FROM t1 WHERE col1 > 0 LIMIT 1;










472



--error 1264










473



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










474



--error 1264










475



UPDATE t1 SET col2=col2 + 50 WHERE col2 > 0;










476



--error 1365










477



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










478



set @@sql_mode='ERROR_FOR_DIVISION_BY_ZERO';










479



INSERT INTO t1 values (1/0,1/0);










480



set @@sql_mode='ansi,traditional';










481



SELECT MOD(col1,0) FROM t1 WHERE col1 > 0 LIMIT 2;










482



# Should return SQLSTATE 22018 invalid character value for cast










483



--error 1366










484



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










485



--error 1366










486



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










487



--error 1265










488



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










489



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










490



INSERT IGNORE INTO t1 values (1/0,1/0);










491



set @@sql_mode='ansi';










492



INSERT INTO t1 values (1/0,1/0);










493



set @@sql_mode='ansi,traditional';










494



INSERT IGNORE INTO t1 VALUES('-129','-1'),('128','256');










495



INSERT IGNORE INTO t1 VALUES(-129.0,-1.0),(128.0,256.0);










496



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










497












498



SELECT * FROM t1;










499



DROP TABLE t1;










500












501



# Test INSERT with SMALLINT










502












503



CREATE TABLE t1(col1 SMALLINT, col2 SMALLINT UNSIGNED);










504



INSERT INTO t1 VALUES(-32768,0),(0,0),(32767,65535),('-32768','0'),('32767','65535'),(-32768.0,0.0),(32767.0,65535.0);










505












506



--error 1264










507



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










508



--error 1264










509



INSERT INTO t1 (col1) VALUES(32768);










510



--error 1264










511



INSERT INTO t1 (col2) VALUES(-1);










512



--error 1264










513



INSERT INTO t1 (col2) VALUES(65536);










514



--error 1264










515



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










516



--error 1264










517



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










518



--error 1264










519



INSERT INTO t1 (col2) VALUES('-1');










520



--error 1264










521



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










522



--error 1264










523



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










524



--error 1264










525



INSERT INTO t1 (col1) VALUES(32768.0);










526



--error 1264










527



INSERT INTO t1 (col2) VALUES(-1.0);










528



--error 1264










529



INSERT INTO t1 (col2) VALUES(65536.0);










530



--error 1264










531



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










532



--error 1264










533



UPDATE t1 SET col2 = col2 + 50 WHERE col2 > 0;










534



--error 1365










535



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










536



--error 1365










537



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










538



--error 1366










539



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










540



--error 1366










541



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










542



--error 1265










543



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










544



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










545



INSERT IGNORE INTO t1 values (1/0,1/0);










546



INSERT IGNORE INTO t1 VALUES(-32769,-1),(32768,65536);










547



INSERT IGNORE INTO t1 VALUES('-32769','-1'),('32768','65536');










548



INSERT IGNORE INTO t1 VALUES(-32769,-1.0),(32768.0,65536.0);










549



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










550












551



SELECT * FROM t1;










552



DROP TABLE t1;










553












554



# Test INSERT with MEDIUMINT










555












556



CREATE TABLE t1 (col1 MEDIUMINT, col2 MEDIUMINT UNSIGNED);










557



INSERT INTO t1 VALUES(-8388608,0),(0,0),(8388607,16777215),('-8388608','0'),('8388607','16777215'),(-8388608.0,0.0),(8388607.0,16777215.0);










558



--error 1264










559



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










560



--error 1264










561



INSERT INTO t1 (col1) VALUES(8388608);










562



--error 1264










563



INSERT INTO t1 (col2) VALUES(-1);










564



--error 1264










565



INSERT INTO t1 (col2) VALUES(16777216);










566



--error 1264










567



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










568



--error 1264










569



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










570



--error 1264










571



INSERT INTO t1 (col2) VALUES('-1');










572



--error 1264










573



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










574



--error 1264










575



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










576



--error 1264










577



INSERT INTO t1 (col1) VALUES(8388608.0);










578



--error 1264










579



INSERT INTO t1 (col2) VALUES(-1.0);










580



--error 1264










581



INSERT INTO t1 (col2) VALUES(16777216.0);










582












583



--error 1264










584



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










585



--error 1264










586



UPDATE t1 SET col2 = col2 + 50 WHERE col2 > 0;










587



--error 1365










588



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










589



--error 1365










590



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










591



--error 1366










592



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










593



--error 1366










594



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










595



--error 1265










596



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










597



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










598



INSERT IGNORE INTO t1 values (1/0,1/0);










599



INSERT IGNORE INTO t1 VALUES(-8388609,-1),(8388608,16777216);










600



INSERT IGNORE INTO t1 VALUES('-8388609','-1'),('8388608','16777216');










601



INSERT IGNORE INTO t1 VALUES(-8388609.0,-1.0),(8388608.0,16777216.0);










602



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










603












604



SELECT * FROM t1;










605



DROP TABLE t1;










606












607



# Test INSERT with INT










608












609



CREATE TABLE t1 (col1 INT, col2 INT UNSIGNED);










610



INSERT INTO t1 VALUES(-2147483648,0),(0,0),(2147483647,4294967295),('-2147483648','0'),('2147483647','4294967295'),(-2147483648.0,0.0),(2147483647.0,4294967295.0);










611



--error 1264










612



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










613



--error 1264










614



INSERT INTO t1 (col1) VALUES(2147643648);










615



--error 1264










616



INSERT INTO t1 (col2) VALUES(-1);










617



--error 1264










618



INSERT INTO t1 (col2) VALUES(4294967296);










619



--error 1264










620



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










621



--error 1264










622



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










623



--error 1264










624



INSERT INTO t1 (col2) VALUES('-1');










625



--error 1264










626



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










627



--error 1264










628



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










629



--error 1264










630



INSERT INTO t1 (col1) VALUES(2147643648.0);










631



--error 1264










632



INSERT INTO t1 (col2) VALUES(-1.0);










633



--error 1264










634



INSERT INTO t1 (col2) VALUES(4294967296.0);










635












636



--error 1264










637



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










638



--error 1264










639



UPDATE t1 SET col2 =col2 + 50 WHERE col2 > 0;










640



--error 1365










641



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










642



--error 1365










643



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










644



--error 1366










645



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










646



--error 1366










647



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










648



--error 1265










649



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










650



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










651



INSERT IGNORE INTO t1 values (1/0,1/0);










652



INSERT IGNORE INTO t1 values (-2147483649, -1),(2147643648,4294967296);










653



INSERT IGNORE INTO t1 values ('-2147483649', '-1'),('2147643648','4294967296');










654



INSERT IGNORE INTO t1 values (-2147483649.0, -1.0),(2147643648.0,4294967296.0);










655



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










656



SELECT * FROM t1;










657



DROP TABLE t1;










658












659



# Test INSERT with BIGINT










660



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










661



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










662












663



CREATE TABLE t1 (col1 BIGINT, col2 BIGINT UNSIGNED);










664



INSERT INTO t1 VALUES(-9223372036854775808,0),(0,0),(9223372036854775807,18446744073709551615);










665



INSERT INTO t1 VALUES('-9223372036854775808','0'),('9223372036854775807','18446744073709551615');










666



INSERT INTO t1 VALUES(-9223372036854774000.0,0.0),(9223372036854775700.0,1844674407370954000.0);










667












668



--error 1264










669



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










670



--error 1264










671



INSERT INTO t1 (col1) VALUES(9223372036854775808);










672



--error 1264










673



INSERT INTO t1 (col2) VALUES(-1);










674












675



--error 1264










676



INSERT INTO t1 (col2) VALUES(18446744073709551616);










677



--error 1264










678



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










679



--error 1264










680



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










681



--error 1264










682



INSERT INTO t1 (col2) VALUES('-1');










683



--error 1264










684



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










685












686



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










687



# bigint becasue of rounding errors when converting it to bigint










688



--error 1264










689



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










690



--error 1264










691



INSERT INTO t1 (col1) VALUES(9223372036854785808.0);










692



--error 1264










693



INSERT INTO t1 (col2) VALUES(-1.0);










694



--error 1264










695



INSERT INTO t1 (col2) VALUES(18446744073709551616.0);










696












697



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










698



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










699



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










700












701



--error 1365










702



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










703



--error 1365










704



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










705



--error 1366










706



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










707



--error 1366










708



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










709



--error 1265










710



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










711



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










712



INSERT IGNORE INTO t1 values (1/0,1/0);










713



INSERT IGNORE INTO t1 VALUES(-9223372036854775809,-1),(9223372036854775808,18446744073709551616);










714



INSERT IGNORE INTO t1 VALUES('-9223372036854775809','-1'),('9223372036854775808','18446744073709551616');










715



INSERT IGNORE INTO t1 VALUES(-9223372036854785809.0,-1.0),(9223372036854785808.0,18446744073709551616.0);










716



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










717



SELECT * FROM t1;










718



DROP TABLE t1;










719












720



# Test INSERT with NUMERIC










721












722



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










723



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










724



# Note that the +/-10.5555 is inserted as +/-10.55, not +/-10.56 !










725



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










726












727



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










728



# because NUMERIC works like DECIMAL










729



--error 1264










730



INSERT INTO t1 VALUES (101.55);










731



--error 1264










732



INSERT INTO t1 VALUES (101);










733



--error 1264










734



INSERT INTO t1 VALUES (-101.55);










735



--error 1264










736



INSERT INTO t1 VALUES (1010.55);










737



--error 1264










738



INSERT INTO t1 VALUES (1010);










739



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










740



# because NUMERIC works like DECIMAL










741



--error 1264










742



INSERT INTO t1 VALUES ('101.55');










743



--error 1264










744



INSERT INTO t1 VALUES ('101');










745



--error 1264










746



INSERT INTO t1 VALUES ('-101.55');










747



--error 1264










748



INSERT INTO t1 VALUES ('-1010.55');










749



--error 1264










750



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










751



--error 1366










752



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










753



--error 1264










754



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










755



--error 1365










756



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










757



--error 1365










758



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










759



#--error 1265










760



--error 1366










761



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










762



#--error 1265










763



--error 1366










764



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










765



--error 1366










766



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










767



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










768



INSERT IGNORE INTO t1 values (1/0);










769



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










770



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










771



INSERT IGNORE INTO t1 VALUES(1000.0),(-1000.0);










772



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










773



SELECT * FROM t1;










774



DROP TABLE t1;










775












776



# Test INSERT with FLOAT










777












778



CREATE TABLE t1 (col1 FLOAT, col2 FLOAT UNSIGNED);










779



INSERT INTO t1 VALUES (-1.1E-37,0),(+3.4E+38,+3.4E+38);










780



INSERT INTO t1 VALUES ('-1.1E-37',0),('+3.4E+38','+3.4E+38');










781



# We don't give warnings for underflow










782



INSERT INTO t1 (col1) VALUES (3E-46);










783



--error 1264










784



INSERT INTO t1 (col1) VALUES (+3.4E+39);










785



--error 1264










786



INSERT INTO t1 (col2) VALUES (-1.1E-3);










787



--error 1264










788



INSERT INTO t1 (col1) VALUES ('+3.4E+39');










789



--error 1264










790



INSERT INTO t1 (col2) VALUES ('-1.1E-3');










791



--error 1264










792



UPDATE t1 SET col1 =col1 * 5000 WHERE col1 > 0;










793



--error 1365










794



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










795



--error 1365










796



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










797



--error 1265










798



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










799



--error 1265










800



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










801



--error 1265










802



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










803



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










804



INSERT IGNORE INTO t1 (col1) VALUES (1/0);










805



INSERT IGNORE INTO t1 VALUES (+3.4E+39,-3.4E+39);










806



INSERT IGNORE INTO t1 VALUES ('+3.4E+39','-3.4E+39');










807



SELECT * FROM t1;










808



DROP TABLE t1;










809












810



# Test INSERT with DOUBLE










811












812



CREATE TABLE t1 (col1 DOUBLE PRECISION, col2 DOUBLE PRECISION UNSIGNED);










813



INSERT INTO t1 VALUES (-2.2E-307,0),(2E-307,0),(+1.7E+308,+1.7E+308);










814



INSERT INTO t1 VALUES ('-2.2E-307',0),('-2E-307',0),('+1.7E+308','+1.7E+308');










815



# We don't give warnings for underflow










816



INSERT INTO t1 (col1) VALUES (-2.2E-330);










817



--error 1367,1264










818



INSERT INTO t1 (col1) VALUES (+1.7E+309);










819



--error 1264










820



INSERT INTO t1 (col2) VALUES (-1.1E-3);










821



--error 1264










822



INSERT INTO t1 (col1) VALUES ('+1.8E+309');










823



--error 1264










824



INSERT INTO t1 (col2) VALUES ('-1.2E-3');










825



UPDATE t1 SET col1 =col1 * 5000 WHERE col1 > 0;










826



--error 1365










827



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










828



--error 1365










829



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










830



--error 1265










831



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










832



--error 1265










833



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










834



--error 1265










835



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










836



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










837



INSERT IGNORE INTO t1 (col1) values (1/0);










838



--error 1367










839



INSERT IGNORE INTO t1 VALUES (+1.9E+309,-1.9E+309);










840



INSERT IGNORE INTO t1 VALUES ('+2.0E+309','-2.0E+309');










841



# stupid...










842



--replace_result -0 0 1.7976931348623e+308 1.79769313486232e+308










843



SELECT * FROM t1;










844



DROP TABLE t1;










845












846



# Testing INSERT with CHAR/VARCHAR










847












848



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










849



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










850



--error 1406










851



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










852



--error 1406










853



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










854



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










855



--error 1406










856



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










857



--error 1406










858



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










859



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










860



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










861



SELECT * FROM t1;










862



DROP TABLE t1;










863












864



# Testing INSERT with ENUM










865












866



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










867



INSERT INTO t1 VALUES ('red'),('blue'),('green');










868



--error 1265










869



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










870



--error 1265










871



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










872



--error 1265










873



INSERT INTO t1 VALUES ('');










874



--error 1265










875



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










876



INSERT IGNORE INTO t1 VALUES ('yellow');










877



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










878



SELECT * FROM t1;










879



DROP TABLE t1;










880












881



# Testing of insert of NULL in not NULL column










882












883



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










884



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










885



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










886



--error 1048










887



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










888



--error 1048










889



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










890



--error 1048










891



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










892



--error 1048










893



UPDATE t1 SET col1=NULL WHERE col1 =100;










894



--error 1048










895



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










896



--error 1048










897



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










898



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










899



SELECT * FROM t1;










900



DROP TABLE t1;










901












902



# Testing of default values










903












904



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










905



SHOW CREATE TABLE t1;










906



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










907



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










908



--error 1048










909



INSERT INTO t1 (col2) VALUES (NULL);










910



--error 1364










911



INSERT INTO t1 (col1) VALUES (2);










912



--error 1364










913



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










914



--error 1364










915



INSERT INTO t1 (col1) SELECT 1;










916



--error 1048










917



INSERT INTO t1 SELECT 1,NULL;










918



INSERT IGNORE INTO t1 values (NULL,NULL);










919



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










920



INSERT IGNORE INTO t1 () values ();










921



SELECT * FROM t1;










922



DROP TABLE t1;










923












924



#










925



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










926



#










927












928



set sql_mode='traditional';










929



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










930



binarycol binary(255), varbinarycol varbinary(255), tinytextcol tinytext,










931



tinyblobcol tinyblob);










932



--error 1406










933



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










934



--error 1406










935



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










936



--error 1406










937



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










938



--error 1406










939



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










940



--error 1406










941



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










942



--error 1406










943



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










944



select * from t1;










945



drop table t1;










946












947



#










948



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










949



#










950












951



set sql_mode='traditional';










952



create table t1 (col1 datetime);










953



--error 1292










954



insert into t1 values(STR_TO_DATE('31.10.2004 15.30 abc','%d.%m.%Y %H.%i'));










955



--error 1411










956



insert into t1 values(STR_TO_DATE('32.10.2004 15.30','%d.%m.%Y %H.%i'));










957



--error 1411










958



insert into t1 values(STR_TO_DATE('2004.12.12 22:22:33 AM','%Y.%m.%d %r'));










959



--error 1411










960



insert into t1 values(STR_TO_DATE('2004.12.12 abc','%Y.%m.%d %T'));










961



set sql_mode='';










962



insert into t1 values(STR_TO_DATE('31.10.2004 15.30 abc','%d.%m.%Y %H.%i'));










963



insert into t1 values(STR_TO_DATE('32.10.2004 15.30','%d.%m.%Y %H.%i'));










964



insert into t1 values(STR_TO_DATE('2004.12.12 22:22:33 AM','%Y.%m.%d %r'));










965



insert into t1 values(STR_TO_DATE('2004.12.12 abc','%Y.%m.%d %T'));










966












967



# Some correct values, just to test the functions










968



insert into t1 values(STR_TO_DATE('31.10.2004 15.30','%d.%m.%Y %H.%i'));










969



insert into t1 values(STR_TO_DATE('2004.12.12 11:22:33 AM','%Y.%m.%d %r'));










970



insert into t1 values(STR_TO_DATE('2004.12.12 10:22:59','%Y.%m.%d %T'));










971












972



select * from t1;










973












974



# Check that select don't abort even in strict mode (for now)










975



set sql_mode='traditional';










976












977



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










978












979



drop table t1;










980












981



#










982



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










983



#










984












985



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










986



--error 1292










987



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










988



--error 1292










989



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










990



--error 1292










991



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










992



--error 1292










993



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










994



--error 1292










995



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










996



--error 1292










997



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










998



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










999



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










1000



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










1001



select * from t1;










1002



drop table t1;










1003












1004



#










1005



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










1006



#










1007












1008



create table t1 (col1 date, col2 datetime, col3 timestamp);










1009



--error 1292










1010



insert into t1 values (0,0,0);










1011



--error 1292










1012



insert into t1 values (0.0,0.0,0.0);










1013



--error 1292










1014



insert into t1 (col1) values (convert('0000-00-00',date));










1015



--error 1292










1016



insert into t1 (col1) values (cast('0000-00-00' as date));










1017












1018



set sql_mode='no_zero_date';










1019



insert into t1 values (0,0,0);










1020



insert into t1 values (0.0,0.0,0.0);










1021



drop table t1;










1022



set sql_mode='traditional';










1023



create table t1 (col1 date);










1024



insert ignore into t1 values ('0000-00-00');










1025



--error 1292










1026



insert into t1 select * from t1;










1027



insert ignore into t1 values ('0000-00-00');










1028



insert ignore into t1 (col1) values (cast('0000-00-00' as date));










1029



--error 1292










1030



insert into t1 select * from t1;










1031



--error 1292










1032



alter table t1 modify col1 datetime;










1033



alter ignore table t1 modify col1 datetime;










1034



--error 1292










1035



insert into t1 select * from t1;










1036



select * from t1;










1037



drop table t1;










1038












1039



#










1040



# Test of inserting an invalid value via a stored procedure (Bug #5907)










1041



#










1042



create table t1 (col1 tinyint);










1043



drop procedure if exists t1;










1044



delimiter |;










1045



create procedure t1 () begin declare exit handler for sqlexception










1046



select'a'; insert into t1 values (200); end;|










1047



delimiter ;|










1048



call t1();










1049



select * from t1;










1050



drop procedure t1;










1051



drop table t1;










1052












1053



#










1054



# Restore mode










1055



#










1056



set sql_mode=@org_mode;










1057












1058



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










1059



SET @@sql_mode = 'traditional';










1060



CREATE TABLE t1 (i int not null);










1061



--error 1364










1062



INSERT INTO t1 VALUES ();










1063



--error 1364










1064



INSERT INTO t1 VALUES (DEFAULT);










1065



--error 1364










1066



INSERT INTO t1 VALUES (DEFAULT(i));










1067



ALTER TABLE t1 ADD j int;










1068



--error 1364










1069



INSERT INTO t1 SET j = 1;










1070



--error 1364










1071



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










1072



--error 1364










1073



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










1074



--error 1364










1075



INSERT INTO t1 VALUES (DEFAULT,1);










1076



DROP TABLE t1;










1077



SET @@sql_mode = '';










1078



CREATE TABLE t1 (i int not null);










1079



INSERT INTO t1 VALUES ();










1080



INSERT INTO t1 VALUES (DEFAULT);










1081



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










1082



--error 1364










1083



INSERT INTO t1 VALUES (DEFAULT(i));










1084



ALTER TABLE t1 ADD j int;










1085



INSERT INTO t1 SET j = 1;










1086



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










1087



--error 1364










1088



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










1089



INSERT INTO t1 VALUES (DEFAULT,1);










1090



DROP TABLE t1;










1091












1092



#










1093



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










1094



#










1095












1096



set @@sql_mode='traditional';










1097



--error 1074










1098



create table t1(a varchar(65537));










1099



--error 1074










1100



create table t1(a varbinary(65537));










1101












1102



#










1103



# Bug #9881: problem with altering table










1104



#










1105












1106



set @@sql_mode='traditional';










1107



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










1108



alter table t1 modify a bigint unsigned not null;










1109



show create table t1;










1110



drop table t1;










1111












1112



#










1113



# Bug #5906: handle invalid date due to conversion










1114



#










1115



set @@sql_mode='traditional';










1116



create table t1 (d date);










1117



--error 1292










1118



insert into t1 values ('2000-10-00');










1119



--error 1292










1120



insert into t1 values (1000);










1121



insert into t1 values ('2000-10-01');










1122



--error 1292










1123



update t1 set d = 1100;










1124



select * from t1;










1125



drop table t1;










1126












1127



#










1128



# Bug #11964: alter table with timestamp field










1129



#










1130












1131



set @@sql_mode='traditional';










1132



create table t1(a int, b timestamp);










1133



alter table t1 add primary key(a);










1134



show create table t1;










1135



drop table t1;










1136



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










1137



alter table t1 add primary key(a);










1138



show create table t1;










1139



drop table t1;










1140












1141



#










1142



# BIT fields










1143



#










1144












1145



set @@sql_mode='traditional';










1146



create table t1(a bit(2));










1147



--error 1406










1148



insert into t1 values(b'101');










1149



select * from t1;










1150



drop table t1;










1151












1152



#










1153



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










1154



#










1155



set sql_mode='traditional';










1156



create table t1 (date date not null);










1157



create table t2 select date from t1;










1158



show create table t2;










1159



drop table t2,t1;










1160



set @@sql_mode= @org_mode;










1161












1162



set names utf8;










1163



create table t1 (i int)










1164



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










1165



show create table t1;










1166



drop table t1;










1167












1168



#










1169



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










1170



#










1171



set sql_mode= 'traditional';










1172



create table t1(col1 tinyint, col2 tinyint unsigned, 










1173



  col3 smallint, col4 smallint unsigned,










1174



  col5 mediumint, col6 mediumint unsigned,










1175



  col7 int, col8 int unsigned,










1176



  col9 bigint, col10 bigint unsigned);










1177



--error 1366










1178



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










1179



--error 1366










1180



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










1181



--error 1366










1182



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










1183



--error 1366










1184



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










1185



--error 1366










1186



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










1187



--error 1366










1188



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










1189



--error 1366










1190



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










1191



--error 1366










1192



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










1193



--error 1366










1194



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










1195



--error 1366










1196



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










1197



drop table t1;










1198












1199



#










1200



# Bug #27176: Assigning a string to an year column has unexpected results










1201



#










1202



set sql_mode='traditional';










1203



create table t1(a year);










1204



--error 1366










1205



insert into t1 values ('-');










1206



--error 1366










1207



insert into t1 values ('+');










1208



--error 1366










1209



insert into t1 values ('');










1210



--error 1265










1211



insert into t1 values ('2000a');










1212



--error 1265










1213



insert into t1 values ('2E3x');










1214



drop table t1;










1215












1216



#










1217



# Bug#27069 set with identical elements are created










1218



#










1219



set sql_mode='traditional';










1220



--error 1291










1221



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










1222



--error 1291










1223



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










1224












1225



#










1226



# Bug #22824: strict, datetime, NULL, wrong warning










1227



#










1228



set @@sql_mode='NO_ZERO_DATE';










1229



create table t1(a datetime not null);










1230



select count(*) from t1 where a is null;










1231



drop table t1;










1232












1233



--echo End of 5.0 tests