diff options
Diffstat (limited to 'mysql-test/r')
| -rw-r--r-- | mysql-test/r/bigint.result | 2 | ||||
| -rw-r--r-- | mysql-test/r/cast.result | 68 | ||||
| -rw-r--r-- | mysql-test/r/create.result | 18 | ||||
| -rw-r--r-- | mysql-test/r/ctype_uca.result | 4 | ||||
| -rw-r--r-- | mysql-test/r/drop.result | 14 | ||||
| -rw-r--r-- | mysql-test/r/func_if.result | 17 | ||||
| -rw-r--r-- | mysql-test/r/func_misc.result | 2 | ||||
| -rw-r--r-- | mysql-test/r/func_str.result | 10 | ||||
| -rw-r--r-- | mysql-test/r/func_time.result | 14 | ||||
| -rw-r--r-- | mysql-test/r/information_schema.result | 42 | ||||
| -rw-r--r-- | mysql-test/r/lowercase_view.result | 105 | ||||
| -rw-r--r-- | mysql-test/r/ndb_index_unique.result | 8 | ||||
| -rw-r--r-- | mysql-test/r/ps_1general.result | 16 | ||||
| -rw-r--r-- | mysql-test/r/row.result | 9 | ||||
| -rw-r--r-- | mysql-test/r/rpl_session_var.result | 6 | ||||
| -rw-r--r-- | mysql-test/r/strict.result | 84 | ||||
| -rw-r--r-- | mysql-test/r/subselect.result | 12 | ||||
| -rw-r--r-- | mysql-test/r/trigger.result | 16 | ||||
| -rw-r--r-- | mysql-test/r/type_ranges.result | 6 | ||||
| -rw-r--r-- | mysql-test/r/union.result | 17 | ||||
| -rw-r--r-- | mysql-test/r/view.result | 144 |
21 files changed, 554 insertions, 60 deletions
diff --git a/mysql-test/r/bigint.result b/mysql-test/r/bigint.result index 50e99e3ecd7..e372c307ba7 100644 --- a/mysql-test/r/bigint.result +++ b/mysql-test/r/bigint.result @@ -87,6 +87,8 @@ drop table t1; SELECT '0x8000000000000001'+0; '0x8000000000000001'+0 0 +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: '0x8000000000000001' create table t1 ( value64 bigint unsigned not null, value32 integer not null, diff --git a/mysql-test/r/cast.result b/mysql-test/r/cast.result index b015648534b..79d903737bf 100644 --- a/mysql-test/r/cast.result +++ b/mysql-test/r/cast.result @@ -7,6 +7,9 @@ CAST(CAST(1-2 AS UNSIGNED) AS SIGNED INTEGER) select CONVERT('-1',UNSIGNED); CONVERT('-1',UNSIGNED) 18446744073709551615 +select CAST('10 ' as unsigned integer); +CAST('10 ' as unsigned integer) +10 select cast(-5 as unsigned) | 1, cast(-5 as unsigned) & -1; cast(-5 as unsigned) | 1 cast(-5 as unsigned) & -1 18446744073709551611 18446744073709551611 @@ -42,6 +45,15 @@ cast("1:2:3" as TIME) select CONVERT("2004-01-22 21:45:33",DATE); CONVERT("2004-01-22 21:45:33",DATE) 2004-01-22 +select 10+'10'; +10+'10' +20 +select 10.0+'10'; +10.0+'10' +20 +select 10E+0+'10'; +10E+0+'10' +20 select CONVERT(DATE "2004-01-22 21:45:33" USING latin1); CONVERT(DATE "2004-01-22 21:45:33" USING latin1) 2004-01-22 21:45:33 @@ -51,12 +63,43 @@ CONVERT(DATE "2004-01-22 21:45:33",CHAR) select CONVERT(DATE "2004-01-22 21:45:33",CHAR(4)); CONVERT(DATE "2004-01-22 21:45:33",CHAR(4)) 2004 +Warnings: +Warning 1292 Truncated incorrect CHAR(4) value: '2004-01-22 21:45:33' select CONVERT(DATE "2004-01-22 21:45:33",BINARY(4)); CONVERT(DATE "2004-01-22 21:45:33",BINARY(4)) 2004 +Warnings: +Warning 1292 Truncated incorrect CHAR(4) value: '2004-01-22 21:45:33' select CAST(DATE "2004-01-22 21:45:33" AS BINARY(4)); CAST(DATE "2004-01-22 21:45:33" AS BINARY(4)) 2004 +Warnings: +Warning 1292 Truncated incorrect CHAR(4) value: '2004-01-22 21:45:33' +select cast('-10a' as signed integer); +cast('-10a' as signed integer) +-10 +Warnings: +Warning 1292 Truncated incorrect INTEGER value: '-10a' +select cast('a10' as unsigned integer); +cast('a10' as unsigned integer) +0 +Warnings: +Warning 1292 Truncated incorrect INTEGER value: 'a10' +select 10+'a'; +10+'a' +10 +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: 'a' +select 10.0+cast('a' as decimal); +10.0+cast('a' as decimal) +10.00 +Warnings: +Error 1366 Incorrect decimal value: '' for column '' at row -1 +select 10E+0+'a'; +10E+0+'a' +10 +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: 'a' set names binary; select cast(_latin1'test' as char character set latin2); cast(_latin1'test' as char character set latin2) @@ -79,12 +122,23 @@ cast(_latin1'a ' AS char(2)) as c4, cast(_latin1'a' AS char(2)) as c5; c1 c2 c3 c4 c5 ab a ab a a +Warnings: +Warning 1292 Truncated incorrect CHAR(2) value: 'abc' +Warning 1292 Truncated incorrect CHAR(2) value: 'a ' +select cast(1000 as CHAR(3)); +cast(1000 as CHAR(3)) +100 +Warnings: +Warning 1292 Truncated incorrect CHAR(3) value: '1000' create table t1 select cast(_latin1'ab' AS char) as c1, cast(_latin1'a ' AS char) as c2, cast(_latin1'abc' AS char(2)) as c3, cast(_latin1'a ' AS char(2)) as c4, cast(_latin1'a' AS char(2)) as c5; +Warnings: +Warning 1292 Truncated incorrect CHAR(2) value: 'abc' +Warning 1292 Truncated incorrect CHAR(2) value: 'a ' select * from t1; c1 c2 c3 c4 c5 ab a ab a a @@ -106,12 +160,18 @@ cast(_koi8r'Æ ' AS nchar(2)) as c4, cast(_koi8r'Æ' AS nchar(2)) as c5; c1 c2 c3 c4 c5 фг Ñ„ фг Ñ„ Ñ„ +Warnings: +Warning 1292 Truncated incorrect CHAR(4) value: 'фгх' +Warning 1292 Truncated incorrect CHAR(3) value: 'Ñ„ ' create table t1 select cast(_koi8r'ÆÇ' AS nchar) as c1, cast(_koi8r'Æ ' AS nchar) as c2, cast(_koi8r'ÆÇÈ' AS nchar(2)) as c3, cast(_koi8r'Æ ' AS nchar(2)) as c4, cast(_koi8r'Æ' AS nchar(2)) as c5; +Warnings: +Warning 1292 Truncated incorrect CHAR(4) value: 'фгх' +Warning 1292 Truncated incorrect CHAR(3) value: 'Ñ„ ' select * from t1; c1 c2 c3 c4 c5 фг Ñ„ фг Ñ„ Ñ„ @@ -167,6 +227,10 @@ a CAST(a AS CHAR(3)) aac aac aab aab aaa aaa +Warnings: +Warning 1292 Truncated incorrect CHAR(2) value: 'aaa' +Warning 1292 Truncated incorrect CHAR(2) value: 'aab' +Warning 1292 Truncated incorrect CHAR(2) value: 'aac' SELECT a, CAST(a AS UNSIGNED) FROM t1 ORDER BY CAST(a AS CHAR) ; a CAST(a AS UNSIGNED) aaa 3 @@ -177,6 +241,10 @@ a CAST(a AS CHAR(2)) aaa aa aab aa aac aa +Warnings: +Warning 1292 Truncated incorrect CHAR(2) value: 'aaa' +Warning 1292 Truncated incorrect CHAR(2) value: 'aab' +Warning 1292 Truncated incorrect CHAR(2) value: 'aac' DROP TABLE t1; select date_add(cast('2004-12-30 12:00:00' as date), interval 0 hour); date_add(cast('2004-12-30 12:00:00' as date), interval 0 hour) diff --git a/mysql-test/r/create.result b/mysql-test/r/create.result index 1c378f88e5c..a323669ac97 100644 --- a/mysql-test/r/create.result +++ b/mysql-test/r/create.result @@ -44,12 +44,24 @@ create table `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa ERROR 42000: Incorrect table name 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' create table a (`aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa` int); ERROR 42000: Identifier name 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' is too long -create table test (a datetime default now()); +create table t1 (a datetime default now()); ERROR 42000: Invalid default value for 'a' -create table test (a datetime on update now()); +create table t1 (a datetime on update now()); ERROR HY000: Invalid ON UPDATE clause for 'a' column -create table test (a int default 100 auto_increment); +create table t1 (a int default 100 auto_increment); ERROR 42000: Invalid default value for 'a' +create table t1 (a tinyint default 1000); +ERROR 42000: Invalid default value for 'a' +create table t1 (a varchar(5) default 'abcdef'); +ERROR 42000: Invalid default value for 'a' +create table t1 (a varchar(5) default 'abcde'); +insert into t1 values(); +select * from t1; +a +abcde +alter table t1 alter column a set default 'abcdef'; +ERROR 42000: Invalid default value for 'a' +drop table t1; create table 1ea10 (1a20 int,1e int); insert into 1ea10 values(1,1); select 1ea10.1a20,1e+ 1e+10 from 1ea10; diff --git a/mysql-test/r/ctype_uca.result b/mysql-test/r/ctype_uca.result index dd5a7ebf6a4..c6e803904a3 100644 --- a/mysql-test/r/ctype_uca.result +++ b/mysql-test/r/ctype_uca.result @@ -1759,8 +1759,8 @@ P,p Ƥ,Æ¥ Q,q ĸ -R,RR,Rr,r,rr,Å”,Å•,Å–,Å—,Ř,Å™ -rR +R,r,Å”,Å•,Å–,Å—,Ř,Å™ +RR,Rr,rR,rr Ʀ S,s,Åš,Å›,Åœ,Å,Åž,ÅŸ,Å ,Å¡,Å¿ SS,Ss,sS,ss,ß diff --git a/mysql-test/r/drop.result b/mysql-test/r/drop.result index 901871f437e..b91cc89cfea 100644 --- a/mysql-test/r/drop.result +++ b/mysql-test/r/drop.result @@ -24,6 +24,20 @@ n drop database if exists mysqltest; affected rows: 1 create database mysqltest; +use mysqltest; +drop table table1, table2, table3, table4, table5, table6, +table7, table8, table9, table10, table11, table12, table13, +table14, table15, table16, table17, table18, table19, table20, +table21, table22, table23, table24, table25, table26, table27, +table28; +ERROR 42S02: Unknown table 'table1,table2,table3,table4,table5,table6,table7,table8,table9,table10,table11,table12,table13,table14,table15,table16,table17,table18,table19,table20,table21,table22,table23,table' +drop table table1, table2, table3, table4, table5, table6, +table7, table8, table9, table10, table11, table12, table13, +table14, table15, table16, table17, table18, table19, table20, +table21, table22, table23, table24, table25, table26, table27, +table28, table29, table30; +ERROR 42S02: Unknown table 'table1,table2,table3,table4,table5,table6,table7,table8,table9,table10,table11,table12,table13,table14,table15,table16,table17,table18,table19,table20,table21,table22,table23,table' +use test; drop database mysqltest; flush tables with read lock; create database mysqltest; diff --git a/mysql-test/r/func_if.result b/mysql-test/r/func_if.result index 0ee7412cbe9..4db31121756 100644 --- a/mysql-test/r/func_if.result +++ b/mysql-test/r/func_if.result @@ -44,21 +44,26 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 7 Using where; Using filesort Warnings: Note 1003 select if((`test`.`t1`.`u` = 1),`test`.`t1`.`st`,cast(`test`.`t1`.`st` as char charset binary)) AS `s` from `test`.`t1` where (`test`.`t1`.`st` like _latin1'%a%') order by if((`test`.`t1`.`u` = 1),`test`.`t1`.`st`,cast(`test`.`t1`.`st` as char charset binary)) -select nullif(u=0, 'test') from t1; -nullif(u=0, 'test') +select nullif(u, 1) from t1; +nullif(u, 1) NULL NULL NULL NULL NULL -1 -1 -explain extended select nullif(u=0, 'test') from t1; +0 +0 +explain extended select nullif(u, 1) from t1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 7 Warnings: -Note 1003 select nullif((`test`.`t1`.`u` = 0),_latin1'test') AS `nullif(u=0, 'test')` from `test`.`t1` +Note 1003 select nullif(`test`.`t1`.`u`,1) AS `nullif(u, 1)` from `test`.`t1` drop table t1; +select nullif(1,'test'); +nullif(1,'test') +1 +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: 'test' select NULLIF(NULL,NULL), NULLIF(NULL,1), NULLIF(NULL,1.0), NULLIF(NULL,"test"); NULLIF(NULL,NULL) NULLIF(NULL,1) NULLIF(NULL,1.0) NULLIF(NULL,"test") NULL NULL NULL NULL diff --git a/mysql-test/r/func_misc.result b/mysql-test/r/func_misc.result index 2d464c891bf..1d9b813e68a 100644 --- a/mysql-test/r/func_misc.result +++ b/mysql-test/r/func_misc.result @@ -25,6 +25,8 @@ length(uuid()) charset(uuid()) length(unhex(replace(uuid(),_utf8'-',_utf8''))) select length(format('nan', 2)) > 0; length(format('nan', 2)) > 0 1 +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: 'nan' select concat("$",format(2500,2)); concat("$",format(2500,2)) $2,500.00 diff --git a/mysql-test/r/func_str.result b/mysql-test/r/func_str.result index 1c2cfeca01e..bbb5a94606e 100644 --- a/mysql-test/r/func_str.result +++ b/mysql-test/r/func_str.result @@ -1,4 +1,4 @@ -drop table if exists t1; +drop table if exists t1,t2; set names latin1; select 'hello',"'hello'",'""hello""','''h''e''l''l''o''',"hel""lo",'hel\'lo'; hello 'hello' ""hello"" 'h'e'l'l'o' hel"lo hel'lo @@ -236,6 +236,8 @@ Warning 1301 Result of repeat() was larger than max_allowed_packet (1048576) - t select position("0" in "baaa" in (1)),position("0" in "1" in (1,2,3)),position("sql" in ("mysql")); position("0" in "baaa" in (1)) position("0" in "1" in (1,2,3)) position("sql" in ("mysql")) 1 0 3 +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: 'baaa' select position(("1" in (1,2,3)) in "01"); position(("1" in (1,2,3)) in "01") 2 @@ -393,9 +395,9 @@ select FIELD(_latin2'b','A','B'); ERROR HY000: Illegal mix of collations (latin2_general_ci,COERCIBLE), (latin1_swedish_ci,COERCIBLE), (latin1_swedish_ci,COERCIBLE) for operation 'field' select FIELD('b',_latin2'A','B'); ERROR HY000: Illegal mix of collations (latin1_swedish_ci,COERCIBLE), (latin2_general_ci,COERCIBLE), (latin1_swedish_ci,COERCIBLE) for operation 'field' -select FIELD('b',_latin2'A','B',1); -FIELD('b',_latin2'A','B',1) -1 +select FIELD('1',_latin2'3','2',1); +FIELD('1',_latin2'3','2',1) +3 select POSITION(_latin1'B' IN _latin1'abcd'); POSITION(_latin1'B' IN _latin1'abcd') 2 diff --git a/mysql-test/r/func_time.result b/mysql-test/r/func_time.result index b30fddb8de0..4293ef5bd85 100644 --- a/mysql-test/r/func_time.result +++ b/mysql-test/r/func_time.result @@ -304,6 +304,8 @@ NULL select date_sub("0000-00-00 00:00:00",INTERVAL 1 SECOND); date_sub("0000-00-00 00:00:00",INTERVAL 1 SECOND) NULL +Warnings: +Warning 1292 Truncated incorrect datetime value: '0000-00-00 00:00:00' select date_add('1998-01-30',Interval 1 month); date_add('1998-01-30',Interval 1 month) 1998-02-28 @@ -424,6 +426,9 @@ insert into t1 values ("0000-00-00", "0000-00-00", "0000-00-00", "0000-00-00"); select dayofyear("0000-00-00"),dayofyear(d),dayofyear(dt),dayofyear(t),dayofyear(c) from t1; dayofyear("0000-00-00") dayofyear(d) dayofyear(dt) dayofyear(t) dayofyear(c) NULL NULL NULL NULL NULL +Warnings: +Warning 1292 Truncated incorrect datetime value: '0000-00-00' +Warning 1292 Truncated incorrect datetime value: '0000-00-00' select dayofmonth("0000-00-00"),dayofmonth(d),dayofmonth(dt),dayofmonth(t),dayofmonth(c) from t1; dayofmonth("0000-00-00") dayofmonth(d) dayofmonth(dt) dayofmonth(t) dayofmonth(c) 0 0 0 0 0 @@ -436,15 +441,24 @@ quarter("0000-00-00") quarter(d) quarter(dt) quarter(t) quarter(c) select week("0000-00-00"),week(d),week(dt),week(t),week(c) from t1; week("0000-00-00") week(d) week(dt) week(t) week(c) NULL NULL NULL NULL NULL +Warnings: +Warning 1292 Truncated incorrect datetime value: '0000-00-00' +Warning 1292 Truncated incorrect datetime value: '0000-00-00' select year("0000-00-00"),year(d),year(dt),year(t),year(c) from t1; year("0000-00-00") year(d) year(dt) year(t) year(c) 0 0 0 0 0 select yearweek("0000-00-00"),yearweek(d),yearweek(dt),yearweek(t),yearweek(c) from t1; yearweek("0000-00-00") yearweek(d) yearweek(dt) yearweek(t) yearweek(c) NULL NULL NULL NULL NULL +Warnings: +Warning 1292 Truncated incorrect datetime value: '0000-00-00' +Warning 1292 Truncated incorrect datetime value: '0000-00-00' select to_days("0000-00-00"),to_days(d),to_days(dt),to_days(t),to_days(c) from t1; to_days("0000-00-00") to_days(d) to_days(dt) to_days(t) to_days(c) NULL NULL NULL NULL NULL +Warnings: +Warning 1292 Truncated incorrect datetime value: '0000-00-00' +Warning 1292 Truncated incorrect datetime value: '0000-00-00' select extract(MONTH FROM "0000-00-00"),extract(MONTH FROM d),extract(MONTH FROM dt),extract(MONTH FROM t),extract(MONTH FROM c) from t1; extract(MONTH FROM "0000-00-00") extract(MONTH FROM d) extract(MONTH FROM dt) extract(MONTH FROM t) extract(MONTH FROM c) 0 0 0 0 0 diff --git a/mysql-test/r/information_schema.result b/mysql-test/r/information_schema.result index 03c2cd8817c..55a299e335f 100644 --- a/mysql-test/r/information_schema.result +++ b/mysql-test/r/information_schema.result @@ -133,7 +133,7 @@ c varchar(64) utf8_general_ci NO select,insert,update,references select * from information_schema.COLUMNS where table_name="t1" and column_name= "a"; TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME ORDINAL_POSITION COLUMN_DEFAULT IS_NULLABLE DATA_TYPE CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION NUMERIC_SCALE CHARACTER_SET_NAME COLLATION_NAME COLUMN_TYPE COLUMN_KEY EXTRA PRIVILEGES COLUMN_COMMENT -NULL testtets t1 a 1 NULL YES int 11 11 11 0 NULL NULL int(11) select,insert,update,references +NULL testtets t1 a 1 NULL YES int NULL NULL 11 0 NULL NULL int(11) select,insert,update,references show columns from testtets.t1 where field like "%a%"; Field Type Null Key Default Extra a int(11) YES NULL @@ -476,15 +476,15 @@ select COLUMN_NAME,COLUMN_TYPE, CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE from information_schema.columns where table_name= 't1'; COLUMN_NAME COLUMN_TYPE CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION NUMERIC_SCALE -a decimal(5,3) 7 7 5 3 -b decimal(5,1) 7 7 5 1 -c float(5,2) 5 5 5 2 -d decimal(6,4) 8 8 6 4 -e float 12 12 12 NULL -f decimal(6,3) 8 8 6 3 -g int(11) 11 11 11 0 -h double(10,3) 10 10 10 3 -i double 22 22 22 NULL +a decimal(5,3) NULL NULL 5 3 +b decimal(5,1) NULL NULL 5 1 +c float(5,2) NULL NULL 5 2 +d decimal(6,4) NULL NULL 6 4 +e float NULL NULL 12 NULL +f decimal(6,3) NULL NULL 6 3 +g int(11) NULL NULL 11 0 +h double(10,3) NULL NULL 10 3 +i double NULL NULL 22 NULL drop table t1; create table t115 as select table_name, column_name, column_type from information_schema.columns where table_name = 'proc'; @@ -678,3 +678,25 @@ WHERE A.TABLE_SCHEMA = B.TABLE_SCHEMA AND A.TABLE_NAME = B.TABLE_NAME); COUNT(*) 0 +create table t1 +( x_bigint BIGINT, +x_integer INTEGER, +x_smallint SMALLINT, +x_decimal DECIMAL(5,3), +x_numeric NUMERIC(5,3), +x_real REAL, +x_float FLOAT, +x_double_precision DOUBLE PRECISION ); +SELECT COLUMN_NAME, CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_LENGTH +FROM INFORMATION_SCHEMA.COLUMNS +WHERE TABLE_NAME= 't1'; +COLUMN_NAME CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH +x_bigint NULL NULL +x_integer NULL NULL +x_smallint NULL NULL +x_decimal NULL NULL +x_numeric NULL NULL +x_real NULL NULL +x_float NULL NULL +x_double_precision NULL NULL +drop table t1; diff --git a/mysql-test/r/lowercase_view.result b/mysql-test/r/lowercase_view.result index 0644b32015c..37252c6dde7 100644 --- a/mysql-test/r/lowercase_view.result +++ b/mysql-test/r/lowercase_view.result @@ -11,16 +11,109 @@ vie CREATE ALGORITHM=UNDEFINED VIEW `mysqltest`.`vie` AS select `mysqltest`.`tab drop database MySQLTest; use test; create table t1Aa (col1 int); -create table t2Aa (col1 int); -create view v1Aa as select * from t1Aa; -create view v2Aa as select * from v1Aa; -update v2aA set col1 = (select max(col1) from v1aA); +create table t2aA (col1 int); +create view v1Aa as select * from t1aA; +create view v2aA as select * from v1aA; +create view v3Aa as select v2Aa.col1 from v2aA,t2Aa where v2Aa.col1 = t2aA.col1; +update v2aA set col1 = (select max(col1) from v1Aa); ERROR HY000: You can't specify target table 'v2aa' for update in FROM clause -delete from v2aA where col1 = (select max(col1) from v1aA); +update v2Aa set col1 = (select max(col1) from t1Aa); ERROR HY000: You can't specify target table 'v2aa' for update in FROM clause +update v2aA set col1 = (select max(col1) from v2Aa); +ERROR HY000: You can't specify target table 'v2aa' for update in FROM clause +update v2aA,t2Aa set v2Aa.col1 = (select max(col1) from v1aA) where v2aA.col1 = t2aA.col1; +ERROR HY000: You can't specify target table 'v2aa' for update in FROM clause +update t1aA,t2Aa set t1Aa.col1 = (select max(col1) from v1Aa) where t1aA.col1 = t2aA.col1; +ERROR HY000: You can't specify target table 't1aa' for update in FROM clause +update v1aA,t2Aa set v1Aa.col1 = (select max(col1) from v1aA) where v1Aa.col1 = t2aA.col1; +ERROR HY000: You can't specify target table 'v1aa' for update in FROM clause +update t2Aa,v2Aa set v2aA.col1 = (select max(col1) from v1aA) where v2Aa.col1 = t2aA.col1; +ERROR HY000: You can't specify target table 't2aa' for update in FROM clause +update t2Aa,t1Aa set t1aA.col1 = (select max(col1) from v1Aa) where t1Aa.col1 = t2aA.col1; +ERROR HY000: You can't specify target table 't2aa' for update in FROM clause +update t2Aa,v1aA set v1Aa.col1 = (select max(col1) from v1aA) where v1Aa.col1 = t2aA.col1; +ERROR HY000: You can't specify target table 't2aa' for update in FROM clause +update v2aA,t2Aa set v2Aa.col1 = (select max(col1) from t1aA) where v2aA.col1 = t2aA.col1; +ERROR HY000: You can't specify target table 'v2aa' for update in FROM clause +update t1Aa,t2Aa set t1aA.col1 = (select max(col1) from t1Aa) where t1aA.col1 = t2aA.col1; +ERROR HY000: You can't specify target table 't1aa' for update in FROM clause +update v1aA,t2Aa set v1Aa.col1 = (select max(col1) from t1Aa) where v1aA.col1 = t2aA.col1; +ERROR HY000: You can't specify target table 'v1aa' for update in FROM clause +update t2Aa,v2Aa set v2aA.col1 = (select max(col1) from t1aA) where v2Aa.col1 = t2aA.col1; +ERROR HY000: You can't specify target table 't2aa' for update in FROM clause +update t2Aa,t1Aa set t1aA.col1 = (select max(col1) from t1Aa) where t1aA.col1 = t2aA.col1; +ERROR HY000: You can't specify target table 't2aa' for update in FROM clause +update t2Aa,v1Aa set v1aA.col1 = (select max(col1) from t1Aa) where v1Aa.col1 = t2aA.col1; +ERROR HY000: You can't specify target table 't2aa' for update in FROM clause +update v2aA,t2Aa set v2Aa.col1 = (select max(col1) from v2aA) where v2Aa.col1 = t2aA.col1; +ERROR HY000: You can't specify target table 'v2aa' for update in FROM clause +update t1aA,t2Aa set t1Aa.col1 = (select max(col1) from v2aA) where t1aA.col1 = t2aA.col1; +ERROR HY000: You can't specify target table 't1aa' for update in FROM clause +update v1aA,t2Aa set v1Aa.col1 = (select max(col1) from v2Aa) where v1aA.col1 = t2aA.col1; +ERROR HY000: You can't specify target table 'v1aa' for update in FROM clause +update t2Aa,v2aA set v2Aa.col1 = (select max(col1) from v2aA) where v2Aa.col1 = t2aA.col1; +ERROR HY000: You can't specify target table 't2aa' for update in FROM clause +update t2Aa,t1Aa set t1aA.col1 = (select max(col1) from v2aA) where t1Aa.col1 = t2aA.col1; +ERROR HY000: You can't specify target table 't2aa' for update in FROM clause +update t2Aa,v1Aa set v1aA.col1 = (select max(col1) from v2Aa) where v1Aa.col1 = t2aA.col1; +ERROR HY000: You can't specify target table 't2aa' for update in FROM clause +update v3aA set v3Aa.col1 = (select max(col1) from v1aA); +ERROR HY000: You can't specify target table 'v3aa' for update in FROM clause +update v3aA set v3Aa.col1 = (select max(col1) from t1aA); +ERROR HY000: You can't specify target table 'v3aa' for update in FROM clause +update v3aA set v3Aa.col1 = (select max(col1) from v2aA); +ERROR HY000: You can't specify target table 'v3aa' for update in FROM clause +update v3aA set v3Aa.col1 = (select max(col1) from v3aA); +ERROR HY000: You can't specify target table 'v3aa' for update in FROM clause +delete from v2Aa where col1 = (select max(col1) from v1Aa); +ERROR HY000: You can't specify target table 'v2aa' for update in FROM clause +delete from v2aA where col1 = (select max(col1) from t1Aa); +ERROR HY000: You can't specify target table 'v2aa' for update in FROM clause +delete from v2Aa where col1 = (select max(col1) from v2aA); +ERROR HY000: You can't specify target table 'v2aa' for update in FROM clause +delete v2Aa from v2aA,t2Aa where (select max(col1) from v1aA) > 0 and v2Aa.col1 = t2aA.col1; +ERROR HY000: You can't specify target table 'v2aa' for update in FROM clause +delete t1aA from t1Aa,t2Aa where (select max(col1) from v1Aa) > 0 and t1aA.col1 = t2aA.col1; +ERROR HY000: You can't specify target table 't1aa' for update in FROM clause +delete v1aA from v1Aa,t2Aa where (select max(col1) from v1aA) > 0 and v1Aa.col1 = t2aA.col1; +ERROR HY000: You can't specify target table 'v1aa' for update in FROM clause +delete v2aA from v2Aa,t2Aa where (select max(col1) from t1Aa) > 0 and v2aA.col1 = t2aA.col1; +ERROR HY000: You can't specify target table 'v2aa' for update in FROM clause +delete t1aA from t1Aa,t2Aa where (select max(col1) from t1aA) > 0 and t1Aa.col1 = t2aA.col1; +ERROR HY000: You can't specify target table 't1aa' for update in FROM clause +delete v1aA from v1Aa,t2Aa where (select max(col1) from t1aA) > 0 and v1aA.col1 = t2aA.col1; +ERROR HY000: You can't specify target table 'v1aa' for update in FROM clause +delete v2Aa from v2aA,t2Aa where (select max(col1) from v2Aa) > 0 and v2aA.col1 = t2aA.col1; +ERROR HY000: You can't specify target table 'v2aa' for update in FROM clause +delete t1Aa from t1aA,t2Aa where (select max(col1) from v2Aa) > 0 and t1Aa.col1 = t2aA.col1; +ERROR HY000: You can't specify target table 't1aa' for update in FROM clause +delete v1Aa from v1aA,t2Aa where (select max(col1) from v2aA) > 0 and v1Aa.col1 = t2aA.col1; +ERROR HY000: You can't specify target table 'v1aa' for update in FROM clause +insert into v2Aa values ((select max(col1) from v1aA)); +ERROR HY000: You can't specify target table 'v2aa' for update in FROM clause +insert into t1aA values ((select max(col1) from v1Aa)); +ERROR HY000: You can't specify target table 't1aa' for update in FROM clause insert into v2aA values ((select max(col1) from v1aA)); ERROR HY000: You can't specify target table 'v2aa' for update in FROM clause -drop view v2Aa,v1Aa; +insert into v2Aa values ((select max(col1) from t1Aa)); +ERROR HY000: You can't specify target table 'v2aa' for update in FROM clause +insert into t1aA values ((select max(col1) from t1Aa)); +ERROR HY000: You can't specify target table 't1aa' for update in FROM clause +insert into v2aA values ((select max(col1) from t1aA)); +ERROR HY000: You can't specify target table 'v2aa' for update in FROM clause +insert into v2Aa values ((select max(col1) from v2aA)); +ERROR HY000: You can't specify target table 'v2aa' for update in FROM clause +insert into t1Aa values ((select max(col1) from v2Aa)); +ERROR HY000: You can't specify target table 't1aa' for update in FROM clause +insert into v2aA values ((select max(col1) from v2Aa)); +ERROR HY000: You can't specify target table 'v2aa' for update in FROM clause +insert into v3Aa (col1) values ((select max(col1) from v1Aa)); +ERROR HY000: You can't specify target table 'v3aa' for update in FROM clause +insert into v3aA (col1) values ((select max(col1) from t1aA)); +ERROR HY000: You can't specify target table 'v3aa' for update in FROM clause +insert into v3Aa (col1) values ((select max(col1) from v2aA)); +ERROR HY000: You can't specify target table 'v3aa' for update in FROM clause +drop view v3aA,v2Aa,v1aA; drop table t1Aa,t2Aa; create table t1Aa (col1 int); create view v1Aa as select col1 from t1Aa as AaA; diff --git a/mysql-test/r/ndb_index_unique.result b/mysql-test/r/ndb_index_unique.result index 517d09ddf29..1401ae26ddb 100644 --- a/mysql-test/r/ndb_index_unique.result +++ b/mysql-test/r/ndb_index_unique.result @@ -248,8 +248,8 @@ INSERT INTO t3 VALUES (1,'V1',NULL); CREATE TABLE t4 ( uid bigint(20) unsigned NOT NULL default '0', gid bigint(20) unsigned NOT NULL, -rid bigint(20) unsigned NOT NULL default '-1', -cid bigint(20) unsigned NOT NULL default '-1', +rid bigint(20) unsigned NOT NULL, +cid bigint(20) unsigned NOT NULL, UNIQUE KEY m (uid,gid,rid,cid) ) engine=ndbcluster; INSERT INTO t4 VALUES (1,1,2,4); @@ -275,8 +275,8 @@ CREATE TABLE t7 ( mid bigint(20) unsigned NOT NULL PRIMARY KEY, uid bigint(20) unsigned NOT NULL default '0', gid bigint(20) unsigned NOT NULL, -rid bigint(20) unsigned NOT NULL default '-1', -cid bigint(20) unsigned NOT NULL default '-1', +rid bigint(20) unsigned NOT NULL, +cid bigint(20) unsigned NOT NULL, UNIQUE KEY m (uid,gid,rid,cid) ) engine=ndbcluster; INSERT INTO t7 VALUES(1, 1, 1, 1, 1); diff --git a/mysql-test/r/ps_1general.result b/mysql-test/r/ps_1general.result index a04fc6fef25..9a9da7cae09 100644 --- a/mysql-test/r/ps_1general.result +++ b/mysql-test/r/ps_1general.result @@ -460,17 +460,17 @@ select 'a' || 'b' ; ab prepare stmt4 from ' SET sql_mode="" '; execute stmt4; -select 'a' || 'b' ; -'a' || 'b' -0 -prepare stmt5 from ' select ''a'' || ''b'' ' ; +select '2' || '3' ; +'2' || '3' +1 +prepare stmt5 from ' select ''2'' || ''3'' ' ; execute stmt5; -'a' || 'b' -0 +'2' || '3' +1 SET sql_mode=ansi; execute stmt5; -'a' || 'b' -0 +'2' || '3' +1 SET sql_mode=""; prepare stmt1 from ' flush local privileges ' ; ERROR HY000: This command is not supported in the prepared statement protocol yet diff --git a/mysql-test/r/row.result b/mysql-test/r/row.result index 40a31563604..0c75e80cb77 100644 --- a/mysql-test/r/row.result +++ b/mysql-test/r/row.result @@ -16,12 +16,15 @@ row('a',1.5,3) IN (row(1,2,3), row('a',1.5,3), row('a','a','a')) 1 Warnings: Error 1366 Incorrect decimal value: '' for column '' at row -1 -select row('a',0,3) IN (row(3,2,3), row('a','0','3'), row(1,3,3)); -row('a',0,3) IN (row(3,2,3), row('a','0','3'), row(1,3,3)) -1 +Warning 1292 Truncated incorrect INTEGER value: 'a' select row('a',0,3) IN (row(3,2,3), row('a','a','3'), row(1,3,3)); row('a',0,3) IN (row(3,2,3), row('a','a','3'), row(1,3,3)) 1 +Warnings: +Warning 1292 Truncated incorrect INTEGER value: 'a' +select row('a',0,3) IN (row(3,2,3), row('a','0','3'), row(1,3,3)); +row('a',0,3) IN (row(3,2,3), row('a','0','3'), row(1,3,3)) +1 select row('a',1.5,3) IN (row(3,NULL,3), row('a',1.5,3), row(1,3,3)); row('a',1.5,3) IN (row(3,NULL,3), row('a',1.5,3), row(1,3,3)) 1 diff --git a/mysql-test/r/rpl_session_var.result b/mysql-test/r/rpl_session_var.result index f1f79ffa597..b5b4b815ade 100644 --- a/mysql-test/r/rpl_session_var.result +++ b/mysql-test/r/rpl_session_var.result @@ -11,14 +11,14 @@ create table t1(a varchar(100),b int); set @@session.sql_mode=pipes_as_concat; insert into t1 values('My'||'SQL', 1); set @@session.sql_mode=default; -insert into t1 values('My'||'SQL', 2); +insert into t1 values('1'||'2', 2); select * from t1 where b<3 order by a; a b -0 2 +1 2 MySQL 1 select * from t1 where b<3 order by a; a b -0 2 +1 2 MySQL 1 set @@session.sql_mode=ignore_space; insert into t1 values(password ('MySQL'), 3); diff --git a/mysql-test/r/strict.result b/mysql-test/r/strict.result index 47dbb87b990..6f332b4e087 100644 --- a/mysql-test/r/strict.result +++ b/mysql-test/r/strict.result @@ -259,17 +259,23 @@ INSERT INTO t1 (col1) VALUES(CAST('2004-10-0' AS DATE)); ERROR 22007: Incorrect date value: '2004-10-00' for column 'col1' at row 1 INSERT INTO t1 (col1) VALUES(CAST('2004-0-10' AS DATE)); ERROR 22007: Incorrect date value: '2004-00-10' for column 'col1' at row 1 +INSERT INTO t1 (col1) VALUES(CAST('0000-00-00' AS DATE)); +ERROR 22007: Truncated incorrect datetime value: '0000-00-00' INSERT INTO t1 (col2) VALUES(CAST('0000-10-31 15:30' AS DATETIME)); INSERT INTO t1 (col2) VALUES(CAST('2004-10-0 15:30' AS DATETIME)); ERROR 22007: Incorrect datetime value: '2004-10-00 15:30:00' for column 'col2' at row 1 INSERT INTO t1 (col2) VALUES(CAST('2004-0-10 15:30' AS DATETIME)); ERROR 22007: Incorrect datetime value: '2004-00-10 15:30:00' for column 'col2' at row 1 +INSERT INTO t1 (col2) VALUES(CAST('0000-00-00' AS DATETIME)); +ERROR 22007: Truncated incorrect datetime value: '0000-00-00' INSERT INTO t1 (col3) VALUES(CAST('0000-10-31 15:30' AS DATETIME)); ERROR 22007: Incorrect datetime value: '0000-10-31 15:30:00' for column 'col3' at row 1 INSERT INTO t1 (col3) VALUES(CAST('2004-10-0 15:30' AS DATETIME)); ERROR 22007: Incorrect datetime value: '2004-10-00 15:30:00' for column 'col3' at row 1 INSERT INTO t1 (col3) VALUES(CAST('2004-0-10 15:30' AS DATETIME)); ERROR 22007: Incorrect datetime value: '2004-00-10 15:30:00' for column 'col3' at row 1 +INSERT INTO t1 (col3) VALUES(CAST('0000-00-00' AS DATETIME)); +ERROR 22007: Truncated incorrect datetime value: '0000-00-00' drop table t1; CREATE TABLE t1 (col1 date, col2 datetime, col3 timestamp); INSERT INTO t1 (col1) VALUES (CONVERT('2004-10-15',DATE)); @@ -280,17 +286,23 @@ INSERT INTO t1 (col1) VALUES(CONVERT('2004-10-0' , DATE)); ERROR 22007: Incorrect date value: '2004-10-00' for column 'col1' at row 1 INSERT INTO t1 (col1) VALUES(CONVERT('2004-0-10' , DATE)); ERROR 22007: Incorrect date value: '2004-00-10' for column 'col1' at row 1 +INSERT INTO t1 (col1) VALUES(CONVERT('0000-00-00',DATE)); +ERROR 22007: Truncated incorrect datetime value: '0000-00-00' INSERT INTO t1 (col2) VALUES(CONVERT('0000-10-31 15:30',DATETIME)); INSERT INTO t1 (col2) VALUES(CONVERT('2004-10-0 15:30',DATETIME)); ERROR 22007: Incorrect datetime value: '2004-10-00 15:30:00' for column 'col2' at row 1 INSERT INTO t1 (col2) VALUES(CONVERT('2004-0-10 15:30',DATETIME)); ERROR 22007: Incorrect datetime value: '2004-00-10 15:30:00' for column 'col2' at row 1 +INSERT INTO t1 (col2) VALUES(CONVERT('0000-00-00',DATETIME)); +ERROR 22007: Truncated incorrect datetime value: '0000-00-00' INSERT INTO t1 (col3) VALUES(CONVERT('0000-10-31 15:30',DATETIME)); ERROR 22007: Incorrect datetime value: '0000-10-31 15:30:00' for column 'col3' at row 1 INSERT INTO t1 (col3) VALUES(CONVERT('2004-10-0 15:30',DATETIME)); ERROR 22007: Incorrect datetime value: '2004-10-00 15:30:00' for column 'col3' at row 1 INSERT INTO t1 (col3) VALUES(CONVERT('2004-0-10 15:30',DATETIME)); ERROR 22007: Incorrect datetime value: '2004-00-10 15:30:00' for column 'col3' at row 1 +INSERT INTO t1 (col3) VALUES(CONVERT('0000-00-00',DATETIME)); +ERROR 22007: Truncated incorrect datetime value: '0000-00-00' drop table t1; CREATE TABLE t1(col1 TINYINT, col2 TINYINT UNSIGNED); 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); @@ -1089,4 +1101,76 @@ Error 1411 Incorrect datetime value: '2004.12.12 10:22:61' for function str_to_t Error 1411 Incorrect datetime value: '2004.12.12 10:22:61' for function str_to_time Error 1411 Incorrect datetime value: '2004.12.12 10:22:61' for function str_to_time drop table t1; +create table t1 (col1 char(3), col2 integer); +insert into t1 (col1) values (cast(1000 as char(3))); +ERROR 22007: Truncated incorrect CHAR(3) value: '1000' +insert into t1 (col1) values (cast(1000E+0 as char(3))); +ERROR 22007: Truncated incorrect CHAR(3) value: '1000' +insert into t1 (col1) values (cast(1000.0 as char(3))); +ERROR 22007: Truncated incorrect CHAR(3) value: '1000.0' +insert into t1 (col2) values (cast('abc' as signed integer)); +ERROR 22007: Truncated incorrect INTEGER value: 'abc' +insert into t1 (col2) values (10E+0 + 'a'); +ERROR 22007: Truncated incorrect DOUBLE value: 'a' +insert into t1 (col2) values (cast('10a' as unsigned integer)); +ERROR 22007: Truncated incorrect INTEGER value: '10a' +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; +col1 col2 +NULL 10 +NULL 10 +NULL 10 +drop table t1; +create table t1 (col1 date, col2 datetime, col3 timestamp); +insert into t1 values (0,0,0); +ERROR 22007: Incorrect date value: '0' for column 'col1' at row 1 +insert into t1 values (0.0,0.0,0.0); +ERROR 22007: Incorrect date value: '0' for column 'col1' at row 1 +insert into t1 (col1) values (convert('0000-00-00',date)); +ERROR 22007: Truncated incorrect datetime value: '0000-00-00' +insert into t1 (col1) values (cast('0000-00-00' as date)); +ERROR 22007: Truncated incorrect datetime value: '0000-00-00' +set sql_mode='no_zero_date'; +insert into t1 values (0,0,0); +Warnings: +Warning 1264 Out of range value adjusted for column 'col1' at row 1 +Warning 1264 Out of range value adjusted for column 'col2' at row 1 +Warning 1265 Data truncated for column 'col3' at row 1 +insert into t1 values (0.0,0.0,0.0); +Warnings: +Warning 1264 Out of range value adjusted for column 'col1' at row 1 +Warning 1264 Out of range value adjusted for column 'col2' at row 1 +Warning 1265 Data truncated for column 'col3' at row 1 +drop table t1; +set sql_mode='traditional'; +create table t1 (col1 date); +insert ignore into t1 values ('0000-00-00'); +Warnings: +Warning 1265 Data truncated for column 'col1' at row 1 +insert into t1 select * from t1; +ERROR 22007: Incorrect date value: '0000-00-00' for column 'col1' at row 1 +insert ignore into t1 values ('0000-00-00'); +Warnings: +Warning 1265 Data truncated for column 'col1' at row 1 +insert ignore into t1 (col1) values (cast('0000-00-00' as date)); +Warnings: +Warning 1292 Truncated incorrect datetime value: '0000-00-00' +insert into t1 select * from t1; +ERROR 22007: Incorrect date value: '0000-00-00' for column 'col1' at row 1 +alter table t1 modify col1 datetime; +ERROR 22007: Incorrect datetime value: '0000-00-00' for column 'col1' at row 1 +alter ignore table t1 modify col1 datetime; +Warnings: +Warning 1264 Out of range value adjusted for column 'col1' at row 1 +Warning 1264 Out of range value adjusted for column 'col1' at row 2 +insert into t1 select * from t1; +ERROR 22007: Incorrect datetime value: '0000-00-00 00:00:00' for column 'col1' at row 1 +select * from t1; +col1 +0000-00-00 00:00:00 +0000-00-00 00:00:00 +NULL +drop table t1; set sql_mode=@org_mode; diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index 036d1631592..0e8ba49d88d 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -117,15 +117,17 @@ SELECT (SELECT 1.5,2,'a') = ROW(1.5,2,'a'); SELECT (SELECT 1.5,2,'a') = ROW(1.5,2,'b'); (SELECT 1.5,2,'a') = ROW(1.5,2,'b') 0 -SELECT (SELECT 1.5,2,'a') = ROW('b',2,'b'); -(SELECT 1.5,2,'a') = ROW('b',2,'b') +SELECT (SELECT 1.5,2,'a') = ROW('1.5b',2,'b'); +(SELECT 1.5,2,'a') = ROW('1.5b',2,'b') 0 +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: '1.5b' SELECT (SELECT 'b',2,'a') = ROW(1.5,2,'a'); (SELECT 'b',2,'a') = ROW(1.5,2,'a') 0 -SELECT (SELECT 1.5,2,'a') = ROW(1.5,'c','a'); -(SELECT 1.5,2,'a') = ROW(1.5,'c','a') -0 +SELECT (SELECT 1.5,2,'a') = ROW(1.5,'2','a'); +(SELECT 1.5,2,'a') = ROW(1.5,'2','a') +1 SELECT (SELECT 1.5,'c','a') = ROW(1.5,2,'a'); (SELECT 1.5,'c','a') = ROW(1.5,2,'a') 0 diff --git a/mysql-test/r/trigger.result b/mysql-test/r/trigger.result index 4a85097cfce..cf0e0e8f564 100644 --- a/mysql-test/r/trigger.result +++ b/mysql-test/r/trigger.result @@ -1,5 +1,6 @@ drop table if exists t1, t2; drop view if exists v1; +drop database if exists mysqltest; create table t1 (i int); create trigger trg before insert on t1 for each row set @a:=1; set @a:=0; @@ -190,3 +191,18 @@ select @del_before, @del_after; drop trigger t1.trg1; drop trigger t1.trg2; drop table t1; +create table t1 (a int); +create trigger trg1 before insert on t1 for each row set new.a= 10; +drop table t1; +create table t1 (a int); +insert into t1 values (); +select * from t1; +a +NULL +drop table t1; +create database mysqltest; +use mysqltest; +create table t1 (i int); +create trigger trg1 before insert on t1 for each row set @a:= 1; +drop database mysqltest; +use test; diff --git a/mysql-test/r/type_ranges.result b/mysql-test/r/type_ranges.result index 85eb5b3230c..0e8c721adf3 100644 --- a/mysql-test/r/type_ranges.result +++ b/mysql-test/r/type_ranges.result @@ -133,7 +133,7 @@ ALTER TABLE t1 add new_field char(10) default "new" not null, change blob_col new_blob_col varchar(20), change date_field date_field char(10), -alter column string set default "new default", +alter column string set default "newdefault", alter short drop default, DROP INDEX utiny, DROP INDEX ushort, @@ -211,7 +211,7 @@ update t2 set string="changed" where auto=16; show full columns from t1; Field Type Collation Null Key Default Extra Privileges Comment auto int(5) unsigned NULL NO MUL NULL auto_increment # -string char(10) latin1_swedish_ci YES new defaul # +string char(10) latin1_swedish_ci YES newdefault # tiny tinyint(4) NULL NO MUL 0 # short smallint(6) NULL NO MUL 0 # medium mediumint(8) NULL NO MUL 0 # @@ -237,7 +237,7 @@ new_field char(10) latin1_swedish_ci NO new # show full columns from t2; Field Type Collation Null Key Default Extra Privileges Comment auto int(5) unsigned NULL NO 0 # -string char(10) latin1_swedish_ci YES new defaul # +string char(10) latin1_swedish_ci YES newdefault # tiny tinyint(4) NULL NO 0 # short smallint(6) NULL NO 0 # medium mediumint(8) NULL NO 0 # diff --git a/mysql-test/r/union.result b/mysql-test/r/union.result index 8c8e12d8838..c5295048126 100644 --- a/mysql-test/r/union.result +++ b/mysql-test/r/union.result @@ -1235,3 +1235,20 @@ show columns from t2; Field Type Null Key Default Extra a varchar(3) YES NULL drop table t2, t1; +create table t1 (a varchar(5)); +create table t2 select * from t1 union select 'abcdefghijkl'; +show create table t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `a` varchar(12) default NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +select row_format from information_schema.TABLES where table_schema="test" and table_name="t2"; +row_format +Dynamic +alter table t2 ROW_FORMAT=fixed; +show create table t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `a` varchar(12) default NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 ROW_FORMAT=FIXED +drop table t1,t2; diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result index c5fe4bf8565..3162a3b5061 100644 --- a/mysql-test/r/view.result +++ b/mysql-test/r/view.result @@ -237,13 +237,17 @@ grant select on mysqltest.t1 to mysqltest_1@localhost; grant create view,select on test.* to mysqltest_1@localhost; create view v1 as select * from mysqltest.t1; alter view v1 as select * from mysqltest.t1; -ERROR 42000: DELETE command denied to user 'mysqltest_1'@'localhost' for table 'v1' +ERROR 42000: DROP command denied to user 'mysqltest_1'@'localhost' for table 'v1' create or replace view v1 as select * from mysqltest.t1; -ERROR 42000: DELETE command denied to user 'mysqltest_1'@'localhost' for table 'v1' +ERROR 42000: DROP command denied to user 'mysqltest_1'@'localhost' for table 'v1' create view mysqltest.v2 as select * from mysqltest.t1; ERROR 42000: CREATE VIEW command denied to user 'mysqltest_1'@'localhost' for table 'v2' create view v2 as select * from mysqltest.t2; ERROR 42000: ANY command denied to user 'mysqltest_1'@'localhost' for table 't2' +grant create view,drop,select on test.* to mysqltest_1@localhost; +use test; +alter view v1 as select * from mysqltest.t1; +create or replace view v1 as select * from mysqltest.t1; revoke all privileges on mysqltest.t1 from mysqltest_1@localhost; revoke all privileges on test.* from mysqltest_1@localhost; drop database mysqltest; @@ -1176,13 +1180,121 @@ create table t1 (col1 int); create table t2 (col1 int); create view v1 as select * from t1; create view v2 as select * from v1; +create view v3 as select v2.col1 from v2,t2 where v2.col1 = t2.col1; update v2 set col1 = (select max(col1) from v1); ERROR HY000: You can't specify target table 'v2' for update in FROM clause +update v2 set col1 = (select max(col1) from t1); +ERROR HY000: You can't specify target table 'v2' for update in FROM clause +update v2 set col1 = (select max(col1) from v2); +ERROR HY000: You can't specify target table 'v2' for update in FROM clause +update v2,t2 set v2.col1 = (select max(col1) from v1) where v2.col1 = t2.col1; +ERROR HY000: You can't specify target table 'v2' for update in FROM clause +update t1,t2 set t1.col1 = (select max(col1) from v1) where t1.col1 = t2.col1; +ERROR HY000: You can't specify target table 't1' for update in FROM clause +update v1,t2 set v1.col1 = (select max(col1) from v1) where v1.col1 = t2.col1; +ERROR HY000: You can't specify target table 'v1' for update in FROM clause +update t2,v2 set v2.col1 = (select max(col1) from v1) where v2.col1 = t2.col1; +ERROR HY000: You can't specify target table 't2' for update in FROM clause +update t2,t1 set t1.col1 = (select max(col1) from v1) where t1.col1 = t2.col1; +ERROR HY000: You can't specify target table 't2' for update in FROM clause +update t2,v1 set v1.col1 = (select max(col1) from v1) where v1.col1 = t2.col1; +ERROR HY000: You can't specify target table 't2' for update in FROM clause +update v2,t2 set v2.col1 = (select max(col1) from t1) where v2.col1 = t2.col1; +ERROR HY000: You can't specify target table 'v2' for update in FROM clause +update t1,t2 set t1.col1 = (select max(col1) from t1) where t1.col1 = t2.col1; +ERROR HY000: You can't specify target table 't1' for update in FROM clause +update v1,t2 set v1.col1 = (select max(col1) from t1) where v1.col1 = t2.col1; +ERROR HY000: You can't specify target table 'v1' for update in FROM clause +update t2,v2 set v2.col1 = (select max(col1) from t1) where v2.col1 = t2.col1; +ERROR HY000: You can't specify target table 't2' for update in FROM clause +update t2,t1 set t1.col1 = (select max(col1) from t1) where t1.col1 = t2.col1; +ERROR HY000: You can't specify target table 't2' for update in FROM clause +update t2,v1 set v1.col1 = (select max(col1) from t1) where v1.col1 = t2.col1; +ERROR HY000: You can't specify target table 't2' for update in FROM clause +update v2,t2 set v2.col1 = (select max(col1) from v2) where v2.col1 = t2.col1; +ERROR HY000: You can't specify target table 'v2' for update in FROM clause +update t1,t2 set t1.col1 = (select max(col1) from v2) where t1.col1 = t2.col1; +ERROR HY000: You can't specify target table 't1' for update in FROM clause +update v1,t2 set v1.col1 = (select max(col1) from v2) where v1.col1 = t2.col1; +ERROR HY000: You can't specify target table 'v1' for update in FROM clause +update t2,v2 set v2.col1 = (select max(col1) from v2) where v2.col1 = t2.col1; +ERROR HY000: You can't specify target table 't2' for update in FROM clause +update t2,t1 set t1.col1 = (select max(col1) from v2) where t1.col1 = t2.col1; +ERROR HY000: You can't specify target table 't2' for update in FROM clause +update t2,v1 set v1.col1 = (select max(col1) from v2) where v1.col1 = t2.col1; +ERROR HY000: You can't specify target table 't2' for update in FROM clause +update v3 set v3.col1 = (select max(col1) from v1); +ERROR HY000: You can't specify target table 'v3' for update in FROM clause +update v3 set v3.col1 = (select max(col1) from t1); +ERROR HY000: You can't specify target table 'v3' for update in FROM clause +update v3 set v3.col1 = (select max(col1) from v2); +ERROR HY000: You can't specify target table 'v3' for update in FROM clause +update v3 set v3.col1 = (select max(col1) from v3); +ERROR HY000: You can't specify target table 'v3' for update in FROM clause delete from v2 where col1 = (select max(col1) from v1); ERROR HY000: You can't specify target table 'v2' for update in FROM clause +delete from v2 where col1 = (select max(col1) from t1); +ERROR HY000: You can't specify target table 'v2' for update in FROM clause +delete from v2 where col1 = (select max(col1) from v2); +ERROR HY000: You can't specify target table 'v2' for update in FROM clause +delete v2 from v2,t2 where (select max(col1) from v1) > 0 and v2.col1 = t2.col1; +ERROR HY000: You can't specify target table 'v2' for update in FROM clause +delete t1 from t1,t2 where (select max(col1) from v1) > 0 and t1.col1 = t2.col1; +ERROR HY000: You can't specify target table 't1' for update in FROM clause +delete v1 from v1,t2 where (select max(col1) from v1) > 0 and v1.col1 = t2.col1; +ERROR HY000: You can't specify target table 'v1' for update in FROM clause +delete v2 from v2,t2 where (select max(col1) from t1) > 0 and v2.col1 = t2.col1; +ERROR HY000: You can't specify target table 'v2' for update in FROM clause +delete t1 from t1,t2 where (select max(col1) from t1) > 0 and t1.col1 = t2.col1; +ERROR HY000: You can't specify target table 't1' for update in FROM clause +delete v1 from v1,t2 where (select max(col1) from t1) > 0 and v1.col1 = t2.col1; +ERROR HY000: You can't specify target table 'v1' for update in FROM clause +delete v2 from v2,t2 where (select max(col1) from v2) > 0 and v2.col1 = t2.col1; +ERROR HY000: You can't specify target table 'v2' for update in FROM clause +delete t1 from t1,t2 where (select max(col1) from v2) > 0 and t1.col1 = t2.col1; +ERROR HY000: You can't specify target table 't1' for update in FROM clause +delete v1 from v1,t2 where (select max(col1) from v2) > 0 and v1.col1 = t2.col1; +ERROR HY000: You can't specify target table 'v1' for update in FROM clause insert into v2 values ((select max(col1) from v1)); ERROR HY000: You can't specify target table 'v2' for update in FROM clause -drop view v2,v1; +insert into t1 values ((select max(col1) from v1)); +ERROR HY000: You can't specify target table 't1' for update in FROM clause +insert into v2 values ((select max(col1) from v1)); +ERROR HY000: You can't specify target table 'v2' for update in FROM clause +insert into v2 values ((select max(col1) from t1)); +ERROR HY000: You can't specify target table 'v2' for update in FROM clause +insert into t1 values ((select max(col1) from t1)); +ERROR HY000: You can't specify target table 't1' for update in FROM clause +insert into v2 values ((select max(col1) from t1)); +ERROR HY000: You can't specify target table 'v2' for update in FROM clause +insert into v2 values ((select max(col1) from v2)); +ERROR HY000: You can't specify target table 'v2' for update in FROM clause +insert into t1 values ((select max(col1) from v2)); +ERROR HY000: You can't specify target table 't1' for update in FROM clause +insert into v2 values ((select max(col1) from v2)); +ERROR HY000: You can't specify target table 'v2' for update in FROM clause +insert into v3 (col1) values ((select max(col1) from v1)); +ERROR HY000: You can't specify target table 'v3' for update in FROM clause +insert into v3 (col1) values ((select max(col1) from t1)); +ERROR HY000: You can't specify target table 'v3' for update in FROM clause +insert into v3 (col1) values ((select max(col1) from v2)); +ERROR HY000: You can't specify target table 'v3' for update in FROM clause +insert into v3 (col1) values ((select CONVERT_TZ('20050101000000','UTC','MET') from v2)); +ERROR HY000: You can't specify target table 'v3' for update in FROM clause +insert into v3 (col1) values ((select CONVERT_TZ('20050101000000','UTC','MET') from t2)); +insert into mysql.time_zone values ('', (select CONVERT_TZ('20050101000000','UTC','MET') from t2)); +ERROR 23000: Column 'Use_leap_seconds' cannot be null +create algorithm=temptable view v4 as select * from t1; +insert into t1 values (1),(2),(3); +insert into t1 (col1) values ((select max(col1) from v4)); +select * from t1; +col1 +NULL +1 +2 +3 +3 +drop view v4,v3,v2,v1; drop table t1,t2; create table t1 (s1 int); create view v1 as select * from t1; @@ -1687,6 +1799,8 @@ delete from v3; ERROR HY000: Can not delete from join view 'test.v3' delete v3,t1 from v3,t1; ERROR HY000: Can not delete from join view 'test.v3' +delete t1,v3 from t1,v3; +ERROR HY000: Can not delete from join view 'test.v3' delete from t1; prepare stmt1 from "insert into v3(a) values (?);"; set @a= 100; @@ -1778,3 +1892,27 @@ select s2 from v1 vq1 where 2 = (select count(*) aa from v1 vq2 having vq1.s2 = s2 drop view v1; drop table t1; +CREATE TABLE t1 (a1 int); +CREATE TABLE t2 (a2 int); +INSERT INTO t1 VALUES (1), (2), (3), (4); +INSERT INTO t2 VALUES (1), (2), (3); +CREATE VIEW v1(a,b) AS SELECT a1,a2 FROM t1 JOIN t2 ON a1=a2 WHERE a1>1; +SELECT * FROM v1; +a b +2 2 +3 3 +CREATE TABLE t3 SELECT * FROM v1; +SELECT * FROM t3; +a b +2 2 +3 3 +DROP VIEW v1; +DROP TABLE t1,t2,t3; +create table t1 (a int); +create table t2 like t1; +create table t3 like t1; +create view v1 as select t1.a x, t2.a y from t1 join t2 where t1.a=t2.a; +insert into t3 select x from v1; +insert into t2 select x from v1; +drop view v1; +drop table t1,t2,t3; |