diff options
Diffstat (limited to 'mysql-test')
37 files changed, 1008 insertions, 69 deletions
diff --git a/mysql-test/r/delete.result b/mysql-test/r/delete.result index 9d337a1ed34..ba4e9386312 100644 --- a/mysql-test/r/delete.result +++ b/mysql-test/r/delete.result @@ -176,6 +176,14 @@ create table t1 (a int); delete `4.t1` from t1 as `4.t1` where `4.t1`.a = 5; delete FROM `4.t1` USING t1 as `4.t1` where `4.t1`.a = 5; drop table t1; +create table t1(f1 int primary key); +insert into t1 values (4),(3),(1),(2); +delete from t1 where (@a:= f1) order by f1 limit 1; +select @a; +@a +1 +drop table t1; +End of 4.1 tests CREATE TABLE t1 (a int not null,b int not null); CREATE TABLE t2 (a int not null, b int not null, primary key (a,b)); CREATE TABLE t3 (a int not null, b int not null, primary key (a,b)); diff --git a/mysql-test/r/func_in.result b/mysql-test/r/func_in.result index 7336665fa40..cf44c56f061 100644 --- a/mysql-test/r/func_in.result +++ b/mysql-test/r/func_in.result @@ -380,7 +380,7 @@ Warnings: Warning 1292 Truncated incorrect DOUBLE value: 'b' explain select f1 from t1 where f1 in ('a',1); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index NULL t1f1_idx 2 NULL 3 Using where; Using index +1 SIMPLE t1 index t1f1_idx t1f1_idx 2 NULL 3 Using where; Using index select f1 from t1 where f1 in ('a','b'); f1 a @@ -409,7 +409,7 @@ Warning 1292 Truncated incorrect DOUBLE value: 'a' Warning 1292 Truncated incorrect DOUBLE value: 'a' explain select f2 from t2 where f2 in ('a',2); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 index NULL t2f2 5 NULL 3 Using where; Using index +1 SIMPLE t2 index t2f2 t2f2 5 NULL 3 Using where; Using index select f2 from t2 where f2 in ('a','b'); f2 0 @@ -431,6 +431,6 @@ Warning 1292 Truncated incorrect DOUBLE value: 'b' Warning 1292 Truncated incorrect DOUBLE value: 'b' explain select f2 from t2 where f2 in (1,'b'); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 index NULL t2f2 5 NULL 3 Using where; Using index +1 SIMPLE t2 index t2f2 t2f2 5 NULL 3 Using where; Using index drop table t1, t2; End of 5.1 tests diff --git a/mysql-test/r/func_math.result b/mysql-test/r/func_math.result index 9a2c55b802d..565c283ee83 100644 --- a/mysql-test/r/func_math.result +++ b/mysql-test/r/func_math.result @@ -177,11 +177,46 @@ drop table t1; select abs(-2) * -2; abs(-2) * -2 -4 -create table t1 (i int); -insert into t1 values (1); -select rand(i) from t1; -ERROR HY000: Incorrect arguments to RAND -drop table t1; +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1),(1),(1),(2); +SELECT CAST(RAND(2) * 1000 AS UNSIGNED), CAST(RAND(a) * 1000 AS UNSIGNED) +FROM t1; +CAST(RAND(2) * 1000 AS UNSIGNED) CAST(RAND(a) * 1000 AS UNSIGNED) +656 405 +122 405 +645 405 +858 656 +SELECT CAST(RAND(2) * 1000 AS UNSIGNED), CAST(RAND(a) * 1000 AS UNSIGNED) +FROM t1 WHERE a = 1; +CAST(RAND(2) * 1000 AS UNSIGNED) CAST(RAND(a) * 1000 AS UNSIGNED) +656 405 +122 405 +645 405 +INSERT INTO t1 VALUES (3); +SELECT CAST(RAND(2) * 1000 AS UNSIGNED), CAST(RAND(a) * 1000 AS UNSIGNED) +FROM t1; +CAST(RAND(2) * 1000 AS UNSIGNED) CAST(RAND(a) * 1000 AS UNSIGNED) +656 405 +122 405 +645 405 +858 656 +354 906 +SELECT CAST(RAND(2) * 1000 AS UNSIGNED), CAST(RAND(a) * 1000 AS UNSIGNED) +FROM t1 WHERE a = 1; +CAST(RAND(2) * 1000 AS UNSIGNED) CAST(RAND(a) * 1000 AS UNSIGNED) +656 405 +122 405 +645 405 +PREPARE stmt FROM +"SELECT CAST(RAND(2) * 1000 AS UNSIGNED), CAST(RAND(?) * 1000 AS UNSIGNED) + FROM t1 WHERE a = 1"; +set @var=2; +EXECUTE stmt USING @var; +CAST(RAND(2) * 1000 AS UNSIGNED) CAST(RAND(?) * 1000 AS UNSIGNED) +656 656 +122 122 +645 645 +DROP TABLE t1; create table t1 (a varchar(90), ts datetime not null, index (a)) engine=innodb default charset=utf8; insert into t1 values ('http://www.foo.com/', now()); select a from t1 where a='http://www.foo.com/' order by abs(timediff(ts, 0)); diff --git a/mysql-test/r/func_str.result b/mysql-test/r/func_str.result index 0496c3eb91f..e6c323276ea 100644 --- a/mysql-test/r/func_str.result +++ b/mysql-test/r/func_str.result @@ -1304,6 +1304,18 @@ id select_type table type possible_keys key key_len ref rows filtered Extra Warnings: Note 1003 select `test`.`t1`.`s` AS `s` from `test`.`t1` where (trim(both _latin1'y' from `test`.`t1`.`s`) > _latin1'ab') DROP TABLE t1; +create table t1(f1 varchar(4)); +explain extended select encode(f1,'zxcv') as 'enc' from t1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 system NULL NULL NULL NULL 0 0.00 const row not found +Warnings: +Note 1003 select encode(`test`.`t1`.`f1`,_latin1'zxcv') AS `enc` from `test`.`t1` +explain extended select decode(f1,'zxcv') as 'enc' from t1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 system NULL NULL NULL NULL 0 0.00 const row not found +Warnings: +Note 1003 select decode(`test`.`t1`.`f1`,_latin1'zxcv') AS `enc` from `test`.`t1` +drop table t1; End of 4.1 tests create table t1 (d decimal default null); insert into t1 values (null); diff --git a/mysql-test/r/gis.result b/mysql-test/r/gis.result index b7f0e4b6b80..8cbc344f062 100644 --- a/mysql-test/r/gis.result +++ b/mysql-test/r/gis.result @@ -718,3 +718,14 @@ desc t1; Field Type Null Key Default Extra GeomFromText('point(1 1)') geometry NO drop table t1; +create table t1 (f1 tinyint(1), f2 char(1), f3 varchar(1), f4 geometry, f5 datetime); +create view v1 as select * from t1; +desc v1; +Field Type Null Key Default Extra +f1 tinyint(1) YES NULL +f2 char(1) YES NULL +f3 varchar(1) YES NULL +f4 geometry YES NULL +f5 datetime YES NULL +drop view v1; +drop table t1; diff --git a/mysql-test/r/group_by.result b/mysql-test/r/group_by.result index 663ef6cced4..d583eb02aeb 100644 --- a/mysql-test/r/group_by.result +++ b/mysql-test/r/group_by.result @@ -933,6 +933,108 @@ b sum(1) 18 6 19 6 DROP TABLE t1; +CREATE TABLE t1 (a INT PRIMARY KEY, b INT); +INSERT INTO t1 VALUES (1,1),(2,1),(3,2),(4,2),(5,3),(6,3); +SET SQL_MODE = 'ONLY_FULL_GROUP_BY'; +SELECT MAX(a)-MIN(a) FROM t1 GROUP BY b; +MAX(a)-MIN(a) +1 +1 +1 +SELECT CEILING(MIN(a)) FROM t1 GROUP BY b; +CEILING(MIN(a)) +1 +3 +5 +SELECT CASE WHEN AVG(a)>=0 THEN 'Positive' ELSE 'Negative' END FROM t1 +GROUP BY b; +CASE WHEN AVG(a)>=0 THEN 'Positive' ELSE 'Negative' END +Positive +Positive +Positive +SELECT a + 1 FROM t1 GROUP BY a; +a + 1 +2 +3 +4 +5 +6 +7 +SELECT a + b FROM t1 GROUP BY b; +ERROR 42000: 'test.t1.a' isn't in GROUP BY +SELECT (SELECT t1_outer.a FROM t1 AS t1_inner GROUP BY b LIMIT 1) +FROM t1 AS t1_outer; +(SELECT t1_outer.a FROM t1 AS t1_inner GROUP BY b LIMIT 1) +1 +2 +3 +4 +5 +6 +SELECT 1 FROM t1 as t1_outer GROUP BY a +HAVING (SELECT t1_outer.a FROM t1 AS t1_inner GROUP BY b LIMIT 1); +1 +1 +1 +1 +1 +1 +1 +SELECT (SELECT t1_outer.a FROM t1 AS t1_inner LIMIT 1) +FROM t1 AS t1_outer GROUP BY t1_outer.b; +ERROR 42000: 'test.t1_outer.a' isn't in GROUP BY +SELECT 1 FROM t1 as t1_outer GROUP BY a +HAVING (SELECT t1_outer.b FROM t1 AS t1_inner LIMIT 1); +ERROR 42S22: Unknown column 'test.t1_outer.b' in 'field list' +SELECT (SELECT SUM(t1_inner.a) FROM t1 AS t1_inner LIMIT 1) +FROM t1 AS t1_outer GROUP BY t1_outer.b; +(SELECT SUM(t1_inner.a) FROM t1 AS t1_inner LIMIT 1) +21 +21 +21 +SELECT (SELECT SUM(t1_inner.a) FROM t1 AS t1_inner GROUP BY t1_inner.b LIMIT 1) +FROM t1 AS t1_outer; +(SELECT SUM(t1_inner.a) FROM t1 AS t1_inner GROUP BY t1_inner.b LIMIT 1) +3 +3 +3 +3 +3 +3 +SELECT (SELECT SUM(t1_outer.a) FROM t1 AS t1_inner LIMIT 1) +FROM t1 AS t1_outer GROUP BY t1_outer.b; +ERROR 42000: 'test.t1_outer.a' isn't in GROUP BY +SELECT 1 FROM t1 as t1_outer +WHERE (SELECT t1_outer.b FROM t1 AS t1_inner GROUP BY t1_inner.b LIMIT 1); +1 +1 +1 +1 +1 +1 +1 +SELECT b FROM t1 GROUP BY b HAVING CEILING(b) > 0; +b +1 +2 +3 +SELECT 1 FROM t1 GROUP BY b HAVING b = 2 OR b = 3 OR SUM(a) > 12; +1 +1 +1 +SELECT 1 FROM t1 GROUP BY b HAVING ROW (b,b) = ROW (1,1); +1 +1 +SELECT 1 FROM t1 GROUP BY b HAVING a = 2; +ERROR 42S22: Unknown column 'a' in 'having clause' +SELECT 1 FROM t1 GROUP BY SUM(b); +ERROR HY000: Invalid use of group function +SELECT b FROM t1 AS t1_outer GROUP BY a HAVING t1_outer.a IN +(SELECT SUM(t1_inner.b)+t1_outer.b FROM t1 AS t1_inner GROUP BY t1_inner.a +HAVING SUM(t1_inner.b)+t1_outer.b > 5); +ERROR 42000: 'test.t1_outer.b' isn't in GROUP BY +DROP TABLE t1; +SET SQL_MODE = ''; CREATE TABLE t1 (a INT, b INT, KEY(a)); INSERT INTO t1 VALUES (1, 1), (2, 2), (3,3), (4,4); EXPLAIN SELECT a, SUM(b) FROM t1 GROUP BY a LIMIT 2; diff --git a/mysql-test/r/information_schema_inno.result b/mysql-test/r/information_schema_inno.result index b9c0cae5efb..2ea2a0bec0b 100644 --- a/mysql-test/r/information_schema_inno.result +++ b/mysql-test/r/information_schema_inno.result @@ -31,11 +31,11 @@ CREATE TABLE t2(b1 INT, b2 INT, INDEX (b1, b2), CONSTRAINT A1 FOREIGN KEY (b1, b2) REFERENCES t1(a1, a2) ON UPDATE CASCADE ON DELETE NO ACTION) ENGINE=INNODB; -CREATE TABLE t3(b1 INT, b2 INT, INDEX (b1, b2), +CREATE TABLE t3(b1 INT, b2 INT, INDEX t3_indx (b1, b2), CONSTRAINT A2 FOREIGN KEY (b1, b2) REFERENCES t2(b1, b2) ON UPDATE SET NULL ON DELETE RESTRICT) ENGINE=INNODB; -CREATE TABLE t4(b1 INT, b2 INT, INDEX (b1, b2), +CREATE TABLE t4(b1 INT, b2 INT, UNIQUE KEY t4_ukey (b1, b2), CONSTRAINT A3 FOREIGN KEY (b1, b2) REFERENCES t3(b1, b2) ON UPDATE NO ACTION ON DELETE SET NULL) ENGINE=INNODB; @@ -45,16 +45,16 @@ FOREIGN KEY (b1, b2) REFERENCES t4(b1, b2) ON UPDATE RESTRICT ON DELETE CASCADE) ENGINE=INNODB; select a.CONSTRAINT_SCHEMA, b.TABLE_NAME, CONSTRAINT_TYPE, b.CONSTRAINT_NAME, UNIQUE_CONSTRAINT_SCHEMA, UNIQUE_CONSTRAINT_NAME, -MATCH_OPTION, UPDATE_RULE, DELETE_RULE +MATCH_OPTION, UPDATE_RULE, DELETE_RULE, b.REFERENCED_TABLE_NAME from information_schema.TABLE_CONSTRAINTS a, information_schema.REFERENTIAL_CONSTRAINTS b where a.CONSTRAINT_SCHEMA = 'test' and a.CONSTRAINT_SCHEMA = b.CONSTRAINT_SCHEMA and a.CONSTRAINT_NAME = b.CONSTRAINT_NAME; -CONSTRAINT_SCHEMA TABLE_NAME CONSTRAINT_TYPE CONSTRAINT_NAME UNIQUE_CONSTRAINT_SCHEMA UNIQUE_CONSTRAINT_NAME MATCH_OPTION UPDATE_RULE DELETE_RULE -test t2 FOREIGN KEY A1 test t1 NONE CASCADE NO ACTION -test t3 FOREIGN KEY A2 test t2 NONE SET NULL RESTRICT -test t4 FOREIGN KEY A3 test t3 NONE NO ACTION SET NULL -test t5 FOREIGN KEY A4 test t4 NONE RESTRICT CASCADE +CONSTRAINT_SCHEMA TABLE_NAME CONSTRAINT_TYPE CONSTRAINT_NAME UNIQUE_CONSTRAINT_SCHEMA UNIQUE_CONSTRAINT_NAME MATCH_OPTION UPDATE_RULE DELETE_RULE REFERENCED_TABLE_NAME +test t2 FOREIGN KEY A1 test PRIMARY NONE CASCADE NO ACTION t1 +test t3 FOREIGN KEY A2 test b1 NONE SET NULL RESTRICT t2 +test t4 FOREIGN KEY A3 test t3_indx NONE NO ACTION SET NULL t3 +test t5 FOREIGN KEY A4 test t4_ukey NONE RESTRICT CASCADE t4 drop tables t5, t4, t3, t2, t1; create database `db-1`; use `db-1`; diff --git a/mysql-test/r/innodb.result b/mysql-test/r/innodb.result index e7fa950a131..9032ea00e11 100644 --- a/mysql-test/r/innodb.result +++ b/mysql-test/r/innodb.result @@ -3447,8 +3447,6 @@ SELECT p0.a FROM t2 p0 WHERE BINARY p0.b = 'customer_over'; a 1 drop table t2, t1; -create table t1 (g geometry not null, spatial gk(g)) engine=innodb; -ERROR HY000: The used table type doesn't support SPATIAL indexes CREATE TABLE t1 ( a int ) ENGINE=innodb; BEGIN; INSERT INTO t1 VALUES (1); diff --git a/mysql-test/r/innodb_gis.result b/mysql-test/r/innodb_gis.result index 09f0aebaefa..e714dbeffc0 100644 --- a/mysql-test/r/innodb_gis.result +++ b/mysql-test/r/innodb_gis.result @@ -460,3 +460,5 @@ ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field insert into t1 (fl) values (pointfromtext('point(1,1)')); ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field drop table t1; +create table t1 (g geometry not null, spatial gk(g)) engine=innodb; +ERROR HY000: The used table type doesn't support SPATIAL indexes diff --git a/mysql-test/r/insert.result b/mysql-test/r/insert.result index ef2ed6f0acf..b039ee49235 100644 --- a/mysql-test/r/insert.result +++ b/mysql-test/r/insert.result @@ -347,6 +347,27 @@ select row_count(); row_count() 1 drop table t1; +create table t1 (f1 int unique, f2 int); +create table t2 (f3 int, f4 int); +create view v1 as select * from t1, t2 where f1= f3; +insert into t1 values (1,11), (2,22); +insert into t2 values (1,12), (2,24); +insert into v1 (f1) values (3) on duplicate key update f3= f3 + 10; +ERROR HY000: Can not modify more than one base table through a join view 'test.v1' +insert into v1 (f1) values (3) on duplicate key update f1= f3 + 10; +select * from t1; +f1 f2 +1 11 +2 22 +3 NULL +insert into v1 (f1) values (3) on duplicate key update f1= f3 + 10; +select * from t1; +f1 f2 +1 11 +2 22 +12 NULL +drop view v1; +drop table t1,t2; create table t1 (id int primary key auto_increment, data int, unique(data)); insert ignore into t1 values(NULL,100),(NULL,110),(NULL,120); insert ignore into t1 values(NULL,10),(NULL,20),(NULL,110),(NULL,120),(NULL,100),(NULL,90); diff --git a/mysql-test/r/order_by.result b/mysql-test/r/order_by.result index 8d09686b7d3..a056e1244e6 100644 --- a/mysql-test/r/order_by.result +++ b/mysql-test/r/order_by.result @@ -926,3 +926,12 @@ NULL 2 3 DROP TABLE t1,t2,t3,t4; +CREATE TABLE t1 (a INT, b INT, PRIMARY KEY (a), UNIQUE KEY b (b)); +INSERT INTO t1 VALUES (1,1),(2,2); +CREATE TABLE t2 (a INT, b INT, KEY a (a,b)); +INSERT INTO t2 VALUES (1,1),(1,2),(2,1),(2,2); +EXPLAIN SELECT 1 FROM t1,t2 WHERE t1.b=2 AND t1.a=t2.a ORDER BY t2.b; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 const PRIMARY,b b 5 const 1 +1 SIMPLE t2 ref a a 5 const 2 Using where; Using index +DROP TABLE t1,t2; diff --git a/mysql-test/r/partition.result b/mysql-test/r/partition.result index 55ba8380665..f7eda649dd2 100644 --- a/mysql-test/r/partition.result +++ b/mysql-test/r/partition.result @@ -1138,4 +1138,85 @@ PARTITION p_100 VALUES LESS THAN (100), PARTITION p_X VALUES LESS THAN MAXVALUE ); drop table t1; +CREATE TABLE t2 ( +taken datetime NOT NULL DEFAULT '0000-00-00 00:00:00', +id int(11) NOT NULL DEFAULT '0', +PRIMARY KEY (id,taken), +KEY taken (taken) +) ENGINE=MyISAM DEFAULT CHARSET=latin1; +INSERT INTO t2 VALUES +('2006-09-27 21:50:01',16421), +('2006-10-02 21:50:01',16421), +('2006-09-27 21:50:01',19092), +('2006-09-28 21:50:01',19092), +('2006-09-29 21:50:01',19092), +('2006-09-30 21:50:01',19092), +('2006-10-01 21:50:01',19092), +('2006-10-02 21:50:01',19092), +('2006-09-27 21:50:01',22589), +('2006-09-29 21:50:01',22589); +CREATE TABLE t1 ( +id int(8) NOT NULL, +PRIMARY KEY (id) +) ENGINE=MyISAM DEFAULT CHARSET=latin1; +INSERT INTO t1 VALUES +(16421), +(19092), +(22589); +CREATE TABLE t4 ( +taken datetime NOT NULL DEFAULT '0000-00-00 00:00:00', +id int(11) NOT NULL DEFAULT '0', +PRIMARY KEY (id,taken), +KEY taken (taken) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +PARTITION BY RANGE (to_days(taken)) +( +PARTITION p01 VALUES LESS THAN (732920) , +PARTITION p02 VALUES LESS THAN (732950) , +PARTITION p03 VALUES LESS THAN MAXVALUE ) ; +INSERT INTO t4 select * from t2; +set @f_date='2006-09-28'; +set @t_date='2006-10-02'; +SELECT t1.id AS MyISAM_part +FROM t1 +WHERE t1.id IN ( +SELECT distinct id +FROM t4 +WHERE taken BETWEEN @f_date AND date_add(@t_date, INTERVAL 1 DAY)) +ORDER BY t1.id +; +MyISAM_part +16421 +19092 +22589 +drop table t1, t2, t4; +CREATE TABLE t1 ( +taken datetime NOT NULL DEFAULT '0000-00-00 00:00:00', +id int(11) NOT NULL DEFAULT '0', +status varchar(20) NOT NULL DEFAULT '', +PRIMARY KEY (id,taken) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +PARTITION BY RANGE (to_days(taken)) +( +PARTITION p15 VALUES LESS THAN (732950) , +PARTITION p16 VALUES LESS THAN MAXVALUE ) ; +INSERT INTO t1 VALUES +('2006-09-27 21:50:01',22589,'Open'), +('2006-09-29 21:50:01',22589,'Verified'); +DROP TABLE IF EXISTS t2; +Warnings: +Note 1051 Unknown table 't2' +CREATE TABLE t2 ( +id int(8) NOT NULL, +severity tinyint(4) NOT NULL DEFAULT '0', +priority tinyint(4) NOT NULL DEFAULT '0', +status varchar(20) DEFAULT NULL, +alien tinyint(4) NOT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1; +INSERT INTO t2 VALUES +(22589,1,1,'Need Feedback',0); +SELECT t2.id FROM t2 WHERE t2.id IN (SELECT id FROM t1 WHERE status = 'Verified'); +id +22589 +drop table t1, t2; End of 5.1 tests diff --git a/mysql-test/r/range.result b/mysql-test/r/range.result index 7a280c2201c..5cd7e3ebfbd 100644 --- a/mysql-test/r/range.result +++ b/mysql-test/r/range.result @@ -674,6 +674,49 @@ select a from t1 where a > 'x'; a xx drop table t1; +CREATE TABLE t1 ( +OXID varchar(32) COLLATE latin1_german2_ci NOT NULL DEFAULT '', +OXPARENTID varchar(32) COLLATE latin1_german2_ci NOT NULL DEFAULT 'oxrootid', +OXLEFT int NOT NULL DEFAULT '0', +OXRIGHT int NOT NULL DEFAULT '0', +OXROOTID varchar(32) COLLATE latin1_german2_ci NOT NULL DEFAULT '', +PRIMARY KEY (OXID), +KEY OXNID (OXID), +KEY OXLEFT (OXLEFT), +KEY OXRIGHT (OXRIGHT), +KEY OXROOTID (OXROOTID) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci; +INSERT INTO t1 VALUES +('d8c4177d09f8b11f5.52725521','oxrootid',1,40,'d8c4177d09f8b11f5.52725521'), +('d8c4177d151affab2.81582770','d8c4177d09f8b11f5.52725521',2,3, +'d8c4177d09f8b11f5.52725521'), +('d8c4177d206a333d2.74422679','d8c4177d09f8b11f5.52725521',4,5, +'d8c4177d09f8b11f5.52725521'), +('d8c4177d225791924.30714720','d8c4177d09f8b11f5.52725521',6,7, +'d8c4177d09f8b11f5.52725521'), +('d8c4177d2380fc201.39666693','d8c4177d09f8b11f5.52725521',8,9, +'d8c4177d09f8b11f5.52725521'), +('d8c4177d24ccef970.14957924','d8c4177d09f8b11f5.52725521',10,11, +'d8c4177d09f8b11f5.52725521'); +EXPLAIN +SELECT s.oxid FROM t1 v, t1 s +WHERE s.oxrootid = 'd8c4177d09f8b11f5.52725521' AND +v.oxrootid ='d8c4177d09f8b11f5.52725521' AND +s.oxleft > v.oxleft AND s.oxleft < v.oxright; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE v ref OXLEFT,OXRIGHT,OXROOTID OXROOTID 34 const 5 Using where +1 SIMPLE s ALL OXLEFT NULL NULL NULL 6 Range checked for each record (index map: 0x4) +SELECT s.oxid FROM t1 v, t1 s +WHERE s.oxrootid = 'd8c4177d09f8b11f5.52725521' AND +v.oxrootid ='d8c4177d09f8b11f5.52725521' AND +s.oxleft > v.oxleft AND s.oxleft < v.oxright; +oxid +d8c4177d151affab2.81582770 +d8c4177d206a333d2.74422679 +d8c4177d225791924.30714720 +d8c4177d2380fc201.39666693 +d8c4177d24ccef970.14957924 +DROP TABLE t1; End of 4.1 tests CREATE TABLE t1 ( id int(11) NOT NULL auto_increment, diff --git a/mysql-test/r/select.result b/mysql-test/r/select.result index cf84f197c8e..6704842320f 100644 --- a/mysql-test/r/select.result +++ b/mysql-test/r/select.result @@ -3628,6 +3628,14 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 range si,ai si 5 NULL 2 Using where 1 SIMPLE t3 eq_ref PRIMARY,ci PRIMARY 4 test.t2.a 1 Using where DROP TABLE t1,t2,t3; +CREATE TABLE t1 ( f1 int primary key, f2 int, f3 int, f4 int, f5 int, f6 int, checked_out int); +CREATE TABLE t2 ( f11 int PRIMARY KEY ); +INSERT INTO t1 VALUES (1,1,1,0,0,0,0),(2,1,1,3,8,1,0),(3,1,1,4,12,1,0); +INSERT INTO t2 VALUES (62); +SELECT * FROM t1 LEFT JOIN t2 ON f11 = t1.checked_out GROUP BY f1 ORDER BY f2, f3, f4, f5 LIMIT 0, 1; +f1 f2 f3 f4 f5 f6 checked_out f11 +1 1 1 0 0 0 0 NULL +DROP TABLE t1, t2; DROP TABLE IF EXISTS t1; CREATE TABLE t1(a int); INSERT into t1 values (1), (2), (3); diff --git a/mysql-test/r/sp-code.result b/mysql-test/r/sp-code.result index 0b0ad802b54..67b030f87a4 100644 --- a/mysql-test/r/sp-code.result +++ b/mysql-test/r/sp-code.result @@ -187,7 +187,7 @@ Pos Instruction 32 set v_dig@4 (v_dig@4 + 1) 33 stmt 4 "update sudoku_work set dig = v_dig wh..." 34 set v_tcounter@6 (v_tcounter@6 + 1) -35 jump_if_not 37(37) not(`test`.`sudoku_digit_ok`(v_row@7,v_col@8,v_dig@4)) +35 jump_if_not 37(37) (not(`test`.`sudoku_digit_ok`(v_row@7,v_col@8,v_dig@4))) 36 jump 15 37 set v_i@3 (v_i@3 + 1) 38 jump 15 diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index 377870696e1..ae828cf06fc 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -1464,7 +1464,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 index NULL s1 6 NULL 3 100.00 Using index 2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 6 func 2 100.00 Using index Warnings: -Note 1003 select `test`.`t1`.`s1` AS `s1`,not(<in_optimizer>(`test`.`t1`.`s1`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`s1`) in t2 on s1 checking NULL)))) AS `s1 NOT IN (SELECT s1 FROM t2)` from `test`.`t1` +Note 1003 select `test`.`t1`.`s1` AS `s1`,(not(<in_optimizer>(`test`.`t1`.`s1`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`s1`) in t2 on s1 checking NULL))))) AS `s1 NOT IN (SELECT s1 FROM t2)` from `test`.`t1` explain extended select s1, s1 = ANY (SELECT s1 FROM t2) from t1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 index NULL s1 6 NULL 3 100.00 Using index @@ -1476,13 +1476,13 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 index NULL s1 6 NULL 3 100.00 Using index 2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 6 func 2 100.00 Using index Warnings: -Note 1003 select `test`.`t1`.`s1` AS `s1`,not(<in_optimizer>(`test`.`t1`.`s1`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`s1`) in t2 on s1 checking NULL)))) AS `s1 <> ALL (SELECT s1 FROM t2)` from `test`.`t1` +Note 1003 select `test`.`t1`.`s1` AS `s1`,(not(<in_optimizer>(`test`.`t1`.`s1`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`s1`) in t2 on s1 checking NULL))))) AS `s1 <> ALL (SELECT s1 FROM t2)` from `test`.`t1` explain extended select s1, s1 NOT IN (SELECT s1 FROM t2 WHERE s1 < 'a2') from t1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 index NULL s1 6 NULL 3 100.00 Using index 2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 6 func 2 100.00 Using index; Using where Warnings: -Note 1003 select `test`.`t1`.`s1` AS `s1`,not(<in_optimizer>(`test`.`t1`.`s1`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`s1`) in t2 on s1 checking NULL where (`test`.`t2`.`s1` < _latin1'a2'))))) AS `s1 NOT IN (SELECT s1 FROM t2 WHERE s1 < 'a2')` from `test`.`t1` +Note 1003 select `test`.`t1`.`s1` AS `s1`,(not(<in_optimizer>(`test`.`t1`.`s1`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`s1`) in t2 on s1 checking NULL where (`test`.`t2`.`s1` < _latin1'a2')))))) AS `s1 NOT IN (SELECT s1 FROM t2 WHERE s1 < 'a2')` from `test`.`t1` drop table t1,t2; create table t2 (a int, b int); create table t3 (a int); @@ -1737,14 +1737,14 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 12 100.00 Using where 2 DEPENDENT SUBQUERY t1 unique_subquery PRIMARY PRIMARY 4 func 1 100.00 Using index; Using where Warnings: -Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`text` AS `text` from `test`.`t1` where not(<in_optimizer>(`test`.`t1`.`id`,<exists>(<primary_index_lookup>(<cache>(`test`.`t1`.`id`) in t1 on PRIMARY where (`test`.`t1`.`id` < 8))))) +Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`text` AS `text` from `test`.`t1` where (not(<in_optimizer>(`test`.`t1`.`id`,<exists>(<primary_index_lookup>(<cache>(`test`.`t1`.`id`) in t1 on PRIMARY where (`test`.`t1`.`id` < 8)))))) explain extended select * from t1 as tt where not exists (select id from t1 where id < 8 and (id = tt.id or id is null) having id is not null); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY tt ALL NULL NULL NULL NULL 12 100.00 Using where 2 DEPENDENT SUBQUERY t1 eq_ref PRIMARY PRIMARY 4 test.tt.id 1 100.00 Using where; Using index Warnings: Note 1276 Field or reference 'tt.id' of SELECT #2 was resolved in SELECT #1 -Note 1003 select `test`.`tt`.`id` AS `id`,`test`.`tt`.`text` AS `text` from `test`.`t1` `tt` where not(exists(select `test`.`t1`.`id` AS `id` from `test`.`t1` where ((`test`.`t1`.`id` < 8) and (`test`.`t1`.`id` = `test`.`tt`.`id`)) having (`test`.`t1`.`id` is not null))) +Note 1003 select `test`.`tt`.`id` AS `id`,`test`.`tt`.`text` AS `text` from `test`.`t1` `tt` where (not(exists(select `test`.`t1`.`id` AS `id` from `test`.`t1` where ((`test`.`t1`.`id` < 8) and (`test`.`t1`.`id` = `test`.`tt`.`id`)) having (`test`.`t1`.`id` is not null)))) insert into t1 (id, text) values (1000, 'text1000'), (1001, 'text1001'); create table t2 (id int not null, text varchar(20) not null default '', primary key (id)); insert into t2 (id, text) values (1, 'text1'), (2, 'text2'), (3, 'text3'), (4, 'text4'), (5, 'text5'), (6, 'text6'), (7, 'text7'), (8, 'text8'), (9, 'text9'), (10, 'text10'), (11, 'text1'), (12, 'text2'), (13, 'text3'), (14, 'text4'), (15, 'text5'), (16, 'text6'), (17, 'text7'), (18, 'text8'), (19, 'text9'), (20, 'text10'),(21, 'text1'), (22, 'text2'), (23, 'text3'), (24, 'text4'), (25, 'text5'), (26, 'text6'), (27, 'text7'), (28, 'text8'), (29, 'text9'), (30, 'text10'), (31, 'text1'), (32, 'text2'), (33, 'text3'), (34, 'text4'), (35, 'text5'), (36, 'text6'), (37, 'text7'), (38, 'text8'), (39, 'text9'), (40, 'text10'), (41, 'text1'), (42, 'text2'), (43, 'text3'), (44, 'text4'), (45, 'text5'), (46, 'text6'), (47, 'text7'), (48, 'text8'), (49, 'text9'), (50, 'text10'); @@ -3605,6 +3605,39 @@ FROM t1) t; COUNT(*) 3000 DROP TABLE t1,t2; +CREATE TABLE t1 (id char(4) PRIMARY KEY, c int); +CREATE TABLE t2 (c int); +INSERT INTO t1 VALUES ('aa', 1); +INSERT INTO t2 VALUES (1); +SELECT * FROM t1 +WHERE EXISTS (SELECT c FROM t2 WHERE c=1 +UNION +SELECT c from t2 WHERE c=t1.c); +id c +aa 1 +INSERT INTO t1 VALUES ('bb', 2), ('cc', 3), ('dd',1); +SELECT * FROM t1 +WHERE EXISTS (SELECT c FROM t2 WHERE c=1 +UNION +SELECT c from t2 WHERE c=t1.c); +id c +aa 1 +bb 2 +cc 3 +dd 1 +INSERT INTO t2 VALUES (2); +CREATE TABLE t3 (c int); +INSERT INTO t3 VALUES (1); +SELECT * FROM t1 +WHERE EXISTS (SELECT t2.c FROM t2 JOIN t3 ON t2.c=t3.c WHERE t2.c=1 +UNION +SELECT c from t2 WHERE c=t1.c); +id c +aa 1 +bb 2 +cc 3 +dd 1 +DROP TABLE t1,t2,t3; CREATE TABLE t1 (s1 char(1)); INSERT INTO t1 VALUES ('a'); SELECT * FROM t1 WHERE _utf8'a' = ANY (SELECT s1 FROM t1); diff --git a/mysql-test/r/trigger.result b/mysql-test/r/trigger.result index e302f5def1b..dcd582fb55e 100644 --- a/mysql-test/r/trigger.result +++ b/mysql-test/r/trigger.result @@ -1278,4 +1278,36 @@ a b 2 b 3 c drop table t1; +CREATE TABLE t1 ( +id int NOT NULL DEFAULT '0', +a varchar(10) NOT NULL, +b varchar(10), +c varchar(10), +d timestamp NOT NULL, +PRIMARY KEY (id, a) +); +CREATE TABLE t2 ( +fubar_id int unsigned NOT NULL DEFAULT '0', +last_change_time datetime NOT NULL DEFAULT '0000-00-00 00:00:00', +PRIMARY KEY (fubar_id) +); +CREATE TRIGGER fubar_change +AFTER UPDATE ON t1 +FOR EACH ROW +BEGIN +INSERT INTO t2 (fubar_id, last_change_time) +SELECT DISTINCT NEW.id AS fubar_id, NOW() AS last_change_time +FROM t1 WHERE (id = NEW.id) AND (OLD.c != NEW.c) +ON DUPLICATE KEY UPDATE +last_change_time = +IF((fubar_id = NEW.id)AND(OLD.c != NEW.c),NOW(),last_change_time); +END +| +INSERT INTO t1 (id,a, b,c,d) VALUES +(1,'a','b','c',now()),(2,'a','b','c',now()); +UPDATE t1 SET c='Bang!' WHERE id=1; +SELECT fubar_id FROM t2; +fubar_id +1 +DROP TABLE t1,t2; End of 5.0 tests diff --git a/mysql-test/r/udf.result b/mysql-test/r/udf.result index dbbe432e614..593ac63ca80 100644 --- a/mysql-test/r/udf.result +++ b/mysql-test/r/udf.result @@ -278,3 +278,37 @@ drop table bug18761; select is_const((1,2,3)); ERROR 21000: Operand should contain 1 column(s) drop function if exists is_const; +CREATE FUNCTION metaphon RETURNS STRING SONAME "UDF_EXAMPLE_LIB"; +CREATE FUNCTION myfunc_double RETURNS REAL SONAME "UDF_EXAMPLE_LIB"; +CREATE FUNCTION myfunc_int RETURNS INTEGER SONAME "UDF_EXAMPLE_LIB"; +create function f1(p1 varchar(255)) +returns varchar(255) +begin +return metaphon(p1); +end// +create function f2(p1 varchar(255)) +returns double +begin +return myfunc_double(p1); +end// +create function f3(p1 varchar(255)) +returns double +begin +return myfunc_int(p1); +end// +select f3(NULL); +f3(NULL) +0 +select f2(NULL); +f2(NULL) +NULL +select f1(NULL); +f1(NULL) +NULL +drop function f1; +drop function f2; +drop function f3; +drop function metaphon; +drop function myfunc_double; +drop function myfunc_int; +End of 5.0 tests. diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result index b338ba12685..8e37774f70d 100644 --- a/mysql-test/r/view.result +++ b/mysql-test/r/view.result @@ -2060,17 +2060,6 @@ CALL p1(); DROP PROCEDURE p1; DROP VIEW v1; DROP TABLE t1; -create table t1 (f1 tinyint(1), f2 char(1), f3 varchar(1), f4 geometry, f5 datetime); -create view v1 as select * from t1; -desc v1; -Field Type Null Key Default Extra -f1 tinyint(1) YES NULL -f2 char(1) YES NULL -f3 varchar(1) YES NULL -f4 geometry YES NULL -f5 datetime YES NULL -drop view v1; -drop table t1; create table t1(f1 datetime); insert into t1 values('2005.01.01 12:0:0'); create view v1 as select f1, subtime(f1, '1:1:1') as sb from t1; @@ -2970,16 +2959,6 @@ UPDATE t1 SET i= f1(); DROP FUNCTION f1; DROP VIEW v1; DROP TABLE t1; -CREATE TABLE t1(id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, val INT UNSIGNED NOT NULL); -CREATE VIEW v1 AS SELECT id, val FROM t1 WHERE val >= 1 AND val <= 5 WITH CHECK OPTION; -INSERT INTO v1 (val) VALUES (2); -INSERT INTO v1 (val) VALUES (4); -INSERT INTO v1 (val) VALUES (6); -ERROR HY000: CHECK OPTION failed 'test.v1' -UPDATE v1 SET val=6 WHERE id=2; -ERROR HY000: CHECK OPTION failed 'test.v1' -DROP VIEW v1; -DROP TABLE t1; DROP VIEW IF EXISTS v1, v2; DROP TABLE IF EXISTS t1; CREATE TABLE t1 (i INT AUTO_INCREMENT PRIMARY KEY, j INT); @@ -3014,6 +2993,17 @@ i j 6 3 DROP VIEW v1, v2; DROP TABLE t1; +CREATE VIEW v AS SELECT !0 * 5 AS x FROM DUAL; +SHOW CREATE VIEW v; +View Create View +v CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v` AS select ((not(0)) * 5) AS `x` +SELECT !0 * 5 AS x FROM DUAL; +x +5 +SELECT * FROM v; +x +5 +DROP VIEW v; DROP VIEW IF EXISTS v1; CREATE VIEW v1 AS SELECT 'The\ZEnd'; SELECT * FROM v1; @@ -3024,6 +3014,50 @@ View Create View v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select _latin1'The\ZEnd' AS `TheEnd` DROP VIEW v1; End of 5.0 tests. +CREATE TABLE t1(id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, val INT UNSIGNED NOT NULL); +CREATE VIEW v1 AS SELECT id, val FROM t1 WHERE val >= 1 AND val <= 5 WITH CHECK OPTION; +INSERT INTO v1 (val) VALUES (2); +INSERT INTO v1 (val) VALUES (4); +INSERT INTO v1 (val) VALUES (6); +ERROR HY000: CHECK OPTION failed 'test.v1' +UPDATE v1 SET val=6 WHERE id=2; +ERROR HY000: CHECK OPTION failed 'test.v1' +DROP VIEW v1; +DROP TABLE t1; +DROP VIEW IF EXISTS v1, v2; +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 (i INT AUTO_INCREMENT PRIMARY KEY, j INT); +CREATE VIEW v1 AS SELECT j FROM t1; +CREATE VIEW v2 AS SELECT * FROM t1; +INSERT INTO t1 (j) VALUES (1); +SELECT LAST_INSERT_ID(); +LAST_INSERT_ID() +1 +INSERT INTO v1 (j) VALUES (2); +# LAST_INSERT_ID() should not change. +SELECT LAST_INSERT_ID(); +LAST_INSERT_ID() +1 +INSERT INTO v2 (j) VALUES (3); +# LAST_INSERT_ID() should be updated. +SELECT LAST_INSERT_ID(); +LAST_INSERT_ID() +3 +INSERT INTO v1 (j) SELECT j FROM t1; +# LAST_INSERT_ID() should not change. +SELECT LAST_INSERT_ID(); +LAST_INSERT_ID() +3 +SELECT * FROM t1; +i j +1 1 +2 2 +3 3 +4 1 +5 2 +6 3 +DROP VIEW v1, v2; +DROP TABLE t1; DROP DATABASE IF EXISTS `d-1`; CREATE DATABASE `d-1`; USE `d-1`; diff --git a/mysql-test/t/delete.test b/mysql-test/t/delete.test index 865e1746fd3..306447dbd5a 100644 --- a/mysql-test/t/delete.test +++ b/mysql-test/t/delete.test @@ -163,6 +163,17 @@ delete `4.t1` from t1 as `4.t1` where `4.t1`.a = 5; delete FROM `4.t1` USING t1 as `4.t1` where `4.t1`.a = 5; drop table t1; +# +# Bug#17711: DELETE doesn't use index when ORDER BY, LIMIT and +# non-restricting WHERE is present. +# +create table t1(f1 int primary key); +insert into t1 values (4),(3),(1),(2); +delete from t1 where (@a:= f1) order by f1 limit 1; +select @a; +drop table t1; + +--echo End of 4.1 tests # End of 4.1 tests # diff --git a/mysql-test/t/func_in.test b/mysql-test/t/func_in.test index 75d85065419..b5afdc122a3 100644 --- a/mysql-test/t/func_in.test +++ b/mysql-test/t/func_in.test @@ -261,6 +261,44 @@ select some_id from t1 where some_id not in('-1', '0'); drop table t1; +# TODO:Disabled until re-resolution of bug #20420 for 5.1. +# Results must be the same as in 5.0 +## +## BUG#20420: optimizer reports wrong keys on left join with IN +## +#CREATE TABLE t1 (a int, b int, PRIMARY KEY (a)); +#INSERT INTO t1 VALUES (1,1),(2,1),(3,1),(4,1),(5,1),(6,1); +# +#CREATE TABLE t2 (a int, b int, PRIMARY KEY (a)); +#INSERT INTO t2 VALUES (3,2),(4,2),(100,100),(101,201),(102,102); +# +#CREATE TABLE t3 (a int PRIMARY KEY); +#INSERT INTO t3 VALUES (1),(2),(3),(4); +# +#CREATE TABLE t4 (a int PRIMARY KEY,b int); +#INSERT INTO t4 VALUES (1,1),(2,2),(1000,1000),(1001,1001),(1002,1002),(1003,1003),(1004,1004); +# +#EXPLAIN SELECT STRAIGHT_JOIN * FROM t3 +# JOIN t1 ON t3.a=t1.a +# JOIN t2 ON t3.a=t2.a +# JOIN t4 WHERE t4.a IN (t1.b, t2.b); +# +#SELECT STRAIGHT_JOIN * FROM t3 +# JOIN t1 ON t3.a=t1.a +# JOIN t2 ON t3.a=t2.a +# JOIN t4 WHERE t4.a IN (t1.b, t2.b); +# +#EXPLAIN SELECT STRAIGHT_JOIN +# (SELECT SUM(t4.a) FROM t4 WHERE t4.a IN (t1.b, t2.b)) +# FROM t3, t1, t2 +# WHERE t3.a=t1.a AND t3.a=t2.a; +# +#SELECT STRAIGHT_JOIN +# (SELECT SUM(t4.a) FROM t4 WHERE t4.a IN (t1.b, t2.b)) +# FROM t3, t1, t2 +# WHERE t3.a=t1.a AND t3.a=t2.a; +# +#DROP TABLE t1,t2,t3,t4; --echo End of 5.0 tests diff --git a/mysql-test/t/func_math.test b/mysql-test/t/func_math.test index 09aa5751301..639ced6a1c0 100644 --- a/mysql-test/t/func_math.test +++ b/mysql-test/t/func_math.test @@ -115,11 +115,25 @@ select abs(-2) * -2; # # Bug #6172 RAND(a) should only accept constant values as arguments # -create table t1 (i int); -insert into t1 values (1); ---error 1210 -select rand(i) from t1; -drop table t1; +CREATE TABLE t1 (a INT); + +INSERT INTO t1 VALUES (1),(1),(1),(2); +SELECT CAST(RAND(2) * 1000 AS UNSIGNED), CAST(RAND(a) * 1000 AS UNSIGNED) + FROM t1; +SELECT CAST(RAND(2) * 1000 AS UNSIGNED), CAST(RAND(a) * 1000 AS UNSIGNED) + FROM t1 WHERE a = 1; +INSERT INTO t1 VALUES (3); +SELECT CAST(RAND(2) * 1000 AS UNSIGNED), CAST(RAND(a) * 1000 AS UNSIGNED) + FROM t1; +SELECT CAST(RAND(2) * 1000 AS UNSIGNED), CAST(RAND(a) * 1000 AS UNSIGNED) + FROM t1 WHERE a = 1; +PREPARE stmt FROM + "SELECT CAST(RAND(2) * 1000 AS UNSIGNED), CAST(RAND(?) * 1000 AS UNSIGNED) + FROM t1 WHERE a = 1"; +set @var=2; +EXECUTE stmt USING @var; + +DROP TABLE t1; # # Bug #14009: use of abs() on null value causes problems with filesort diff --git a/mysql-test/t/func_str.test b/mysql-test/t/func_str.test index ab072f0e692..535af6907ad 100644 --- a/mysql-test/t/func_str.test +++ b/mysql-test/t/func_str.test @@ -774,6 +774,14 @@ EXPLAIN EXTENDED SELECT s FROM t1 WHERE TRIM(BOTH 'y' FROM s) > 'ab'; DROP TABLE t1; +# +# Bug#23409: ENCODE() and DECODE() functions aren't printed correctly +# +create table t1(f1 varchar(4)); +explain extended select encode(f1,'zxcv') as 'enc' from t1; +explain extended select decode(f1,'zxcv') as 'enc' from t1; +drop table t1; + --echo End of 4.1 tests # diff --git a/mysql-test/t/gis.test b/mysql-test/t/gis.test index 196ff5b8ff0..d1ea8dbe18f 100644 --- a/mysql-test/t/gis.test +++ b/mysql-test/t/gis.test @@ -428,3 +428,13 @@ drop table t1; create table t1 select GeomFromText('point(1 1)'); desc t1; drop table t1; + +# +# Bug #11335 View redefines column types +# +create table t1 (f1 tinyint(1), f2 char(1), f3 varchar(1), f4 geometry, f5 datetime); +create view v1 as select * from t1; +desc v1; +drop view v1; +drop table t1; + diff --git a/mysql-test/t/group_by.test b/mysql-test/t/group_by.test index 7f887335753..76e4af8f610 100644 --- a/mysql-test/t/group_by.test +++ b/mysql-test/t/group_by.test @@ -703,6 +703,57 @@ SELECT SQL_BIG_RESULT b, sum(1) FROM t1 GROUP BY b; DROP TABLE t1; # +# Bug #23417: Too strict checks against GROUP BY in the ONLY_FULL_GROUP_BY mode +# +CREATE TABLE t1 (a INT PRIMARY KEY, b INT); +INSERT INTO t1 VALUES (1,1),(2,1),(3,2),(4,2),(5,3),(6,3); + +SET SQL_MODE = 'ONLY_FULL_GROUP_BY'; +SELECT MAX(a)-MIN(a) FROM t1 GROUP BY b; +SELECT CEILING(MIN(a)) FROM t1 GROUP BY b; +SELECT CASE WHEN AVG(a)>=0 THEN 'Positive' ELSE 'Negative' END FROM t1 + GROUP BY b; +SELECT a + 1 FROM t1 GROUP BY a; +--error ER_WRONG_FIELD_WITH_GROUP +SELECT a + b FROM t1 GROUP BY b; +SELECT (SELECT t1_outer.a FROM t1 AS t1_inner GROUP BY b LIMIT 1) + FROM t1 AS t1_outer; +SELECT 1 FROM t1 as t1_outer GROUP BY a + HAVING (SELECT t1_outer.a FROM t1 AS t1_inner GROUP BY b LIMIT 1); +--error ER_WRONG_FIELD_WITH_GROUP +SELECT (SELECT t1_outer.a FROM t1 AS t1_inner LIMIT 1) + FROM t1 AS t1_outer GROUP BY t1_outer.b; +--error ER_BAD_FIELD_ERROR +SELECT 1 FROM t1 as t1_outer GROUP BY a + HAVING (SELECT t1_outer.b FROM t1 AS t1_inner LIMIT 1); +SELECT (SELECT SUM(t1_inner.a) FROM t1 AS t1_inner LIMIT 1) + FROM t1 AS t1_outer GROUP BY t1_outer.b; +SELECT (SELECT SUM(t1_inner.a) FROM t1 AS t1_inner GROUP BY t1_inner.b LIMIT 1) + FROM t1 AS t1_outer; +--error ER_WRONG_FIELD_WITH_GROUP +SELECT (SELECT SUM(t1_outer.a) FROM t1 AS t1_inner LIMIT 1) + FROM t1 AS t1_outer GROUP BY t1_outer.b; + +SELECT 1 FROM t1 as t1_outer + WHERE (SELECT t1_outer.b FROM t1 AS t1_inner GROUP BY t1_inner.b LIMIT 1); + +SELECT b FROM t1 GROUP BY b HAVING CEILING(b) > 0; + +SELECT 1 FROM t1 GROUP BY b HAVING b = 2 OR b = 3 OR SUM(a) > 12; +SELECT 1 FROM t1 GROUP BY b HAVING ROW (b,b) = ROW (1,1); + +--error ER_BAD_FIELD_ERROR +SELECT 1 FROM t1 GROUP BY b HAVING a = 2; +--error ER_INVALID_GROUP_FUNC_USE +SELECT 1 FROM t1 GROUP BY SUM(b); +--error ER_WRONG_FIELD_WITH_GROUP +SELECT b FROM t1 AS t1_outer GROUP BY a HAVING t1_outer.a IN + (SELECT SUM(t1_inner.b)+t1_outer.b FROM t1 AS t1_inner GROUP BY t1_inner.a + HAVING SUM(t1_inner.b)+t1_outer.b > 5); +DROP TABLE t1; +SET SQL_MODE = ''; + +# # Bug #21174: Index degrades sort performance and # optimizer does not honor IGNORE INDEX # diff --git a/mysql-test/t/information_schema_inno.test b/mysql-test/t/information_schema_inno.test index 014bdacfeea..e73f4ba2792 100644 --- a/mysql-test/t/information_schema_inno.test +++ b/mysql-test/t/information_schema_inno.test @@ -32,11 +32,11 @@ CREATE TABLE t2(b1 INT, b2 INT, INDEX (b1, b2), CONSTRAINT A1 FOREIGN KEY (b1, b2) REFERENCES t1(a1, a2) ON UPDATE CASCADE ON DELETE NO ACTION) ENGINE=INNODB; -CREATE TABLE t3(b1 INT, b2 INT, INDEX (b1, b2), +CREATE TABLE t3(b1 INT, b2 INT, INDEX t3_indx (b1, b2), CONSTRAINT A2 FOREIGN KEY (b1, b2) REFERENCES t2(b1, b2) ON UPDATE SET NULL ON DELETE RESTRICT) ENGINE=INNODB; -CREATE TABLE t4(b1 INT, b2 INT, INDEX (b1, b2), +CREATE TABLE t4(b1 INT, b2 INT, UNIQUE KEY t4_ukey (b1, b2), CONSTRAINT A3 FOREIGN KEY (b1, b2) REFERENCES t3(b1, b2) ON UPDATE NO ACTION ON DELETE SET NULL) ENGINE=INNODB; @@ -45,10 +45,9 @@ CREATE TABLE t5(b1 INT, b2 INT, INDEX (b1, b2), FOREIGN KEY (b1, b2) REFERENCES t4(b1, b2) ON UPDATE RESTRICT ON DELETE CASCADE) ENGINE=INNODB; - select a.CONSTRAINT_SCHEMA, b.TABLE_NAME, CONSTRAINT_TYPE, b.CONSTRAINT_NAME, UNIQUE_CONSTRAINT_SCHEMA, UNIQUE_CONSTRAINT_NAME, - MATCH_OPTION, UPDATE_RULE, DELETE_RULE + MATCH_OPTION, UPDATE_RULE, DELETE_RULE, b.REFERENCED_TABLE_NAME from information_schema.TABLE_CONSTRAINTS a, information_schema.REFERENTIAL_CONSTRAINTS b where a.CONSTRAINT_SCHEMA = 'test' and a.CONSTRAINT_SCHEMA = b.CONSTRAINT_SCHEMA and diff --git a/mysql-test/t/innodb.test b/mysql-test/t/innodb.test index e1e74ac8a11..bbecd787324 100644 --- a/mysql-test/t/innodb.test +++ b/mysql-test/t/innodb.test @@ -2489,12 +2489,6 @@ SELECT p0.a FROM t2 p0 WHERE BINARY p0.b = 'customer_over'; drop table t2, t1; # -# Bug #15680 (SPATIAL key in innodb) -# ---error ER_TABLE_CANT_HANDLE_SPKEYS -create table t1 (g geometry not null, spatial gk(g)) engine=innodb; - -# # Test optimize on table with open transaction # diff --git a/mysql-test/t/innodb_gis.test b/mysql-test/t/innodb_gis.test index 142b526af92..9675b6b69dc 100644 --- a/mysql-test/t/innodb_gis.test +++ b/mysql-test/t/innodb_gis.test @@ -1,3 +1,9 @@ --source include/have_innodb.inc SET storage_engine=innodb; --source include/gis_generic.inc + +# +# Bug #15680 (SPATIAL key in innodb) +# +--error ER_TABLE_CANT_HANDLE_SPKEYS +create table t1 (g geometry not null, spatial gk(g)) engine=innodb; diff --git a/mysql-test/t/insert.test b/mysql-test/t/insert.test index 398b0bc89d8..0a6b081e7c3 100644 --- a/mysql-test/t/insert.test +++ b/mysql-test/t/insert.test @@ -225,6 +225,23 @@ insert into t1 values (5, 5) on duplicate key update data= data + 10; select row_count(); drop table t1; +# +# Bug#25123: ON DUPLICATE KEY clause allows fields not from the insert table +# +create table t1 (f1 int unique, f2 int); +create table t2 (f3 int, f4 int); +create view v1 as select * from t1, t2 where f1= f3; +insert into t1 values (1,11), (2,22); +insert into t2 values (1,12), (2,24); +--error 1393 +insert into v1 (f1) values (3) on duplicate key update f3= f3 + 10; +insert into v1 (f1) values (3) on duplicate key update f1= f3 + 10; +select * from t1; +insert into v1 (f1) values (3) on duplicate key update f1= f3 + 10; +select * from t1; +drop view v1; +drop table t1,t2; + # Test of INSERT IGNORE and re-using auto_increment values create table t1 (id int primary key auto_increment, data int, unique(data)); insert ignore into t1 values(NULL,100),(NULL,110),(NULL,120); diff --git a/mysql-test/t/order_by.test b/mysql-test/t/order_by.test index 012b38ff8b7..15fb6771101 100644 --- a/mysql-test/t/order_by.test +++ b/mysql-test/t/order_by.test @@ -640,3 +640,16 @@ SELECT t2.b FROM t1 LEFT JOIN (t2, t3 LEFT JOIN t4 ON t3.a=t4.a) ON (t1.a=t2.a AND t1.b=t3.b) order by t2.b; DROP TABLE t1,t2,t3,t4; + +# +# BUG#16590: Optimized does not do right "const" table pre-read +# +CREATE TABLE t1 (a INT, b INT, PRIMARY KEY (a), UNIQUE KEY b (b)); +INSERT INTO t1 VALUES (1,1),(2,2); + +CREATE TABLE t2 (a INT, b INT, KEY a (a,b)); +INSERT INTO t2 VALUES (1,1),(1,2),(2,1),(2,2); + +EXPLAIN SELECT 1 FROM t1,t2 WHERE t1.b=2 AND t1.a=t2.a ORDER BY t2.b; + +DROP TABLE t1,t2; diff --git a/mysql-test/t/partition.test b/mysql-test/t/partition.test index 02c7ca0b05c..7d7ef95626a 100644 --- a/mysql-test/t/partition.test +++ b/mysql-test/t/partition.test @@ -1370,4 +1370,97 @@ REORGANIZE PARTITION p_X INTO ( drop table t1; +# +# Bug #24186 (nested query across partitions returns fewer records) +# + +CREATE TABLE t2 ( + taken datetime NOT NULL DEFAULT '0000-00-00 00:00:00', + id int(11) NOT NULL DEFAULT '0', + PRIMARY KEY (id,taken), + KEY taken (taken) +) ENGINE=MyISAM DEFAULT CHARSET=latin1; + +INSERT INTO t2 VALUES +('2006-09-27 21:50:01',16421), +('2006-10-02 21:50:01',16421), +('2006-09-27 21:50:01',19092), +('2006-09-28 21:50:01',19092), +('2006-09-29 21:50:01',19092), +('2006-09-30 21:50:01',19092), +('2006-10-01 21:50:01',19092), +('2006-10-02 21:50:01',19092), +('2006-09-27 21:50:01',22589), +('2006-09-29 21:50:01',22589); + +CREATE TABLE t1 ( + id int(8) NOT NULL, + PRIMARY KEY (id) +) ENGINE=MyISAM DEFAULT CHARSET=latin1; + +INSERT INTO t1 VALUES +(16421), +(19092), +(22589); + +CREATE TABLE t4 ( + taken datetime NOT NULL DEFAULT '0000-00-00 00:00:00', + id int(11) NOT NULL DEFAULT '0', + PRIMARY KEY (id,taken), + KEY taken (taken) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +PARTITION BY RANGE (to_days(taken)) +( +PARTITION p01 VALUES LESS THAN (732920) , +PARTITION p02 VALUES LESS THAN (732950) , +PARTITION p03 VALUES LESS THAN MAXVALUE ) ; + +INSERT INTO t4 select * from t2; + +set @f_date='2006-09-28'; +set @t_date='2006-10-02'; + +SELECT t1.id AS MyISAM_part +FROM t1 +WHERE t1.id IN ( + SELECT distinct id + FROM t4 + WHERE taken BETWEEN @f_date AND date_add(@t_date, INTERVAL 1 DAY)) +ORDER BY t1.id +; + +drop table t1, t2, t4; + +CREATE TABLE t1 ( + taken datetime NOT NULL DEFAULT '0000-00-00 00:00:00', + id int(11) NOT NULL DEFAULT '0', + status varchar(20) NOT NULL DEFAULT '', + PRIMARY KEY (id,taken) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +PARTITION BY RANGE (to_days(taken)) +( +PARTITION p15 VALUES LESS THAN (732950) , +PARTITION p16 VALUES LESS THAN MAXVALUE ) ; + + +INSERT INTO t1 VALUES +('2006-09-27 21:50:01',22589,'Open'), +('2006-09-29 21:50:01',22589,'Verified'); + +DROP TABLE IF EXISTS t2; +CREATE TABLE t2 ( + id int(8) NOT NULL, + severity tinyint(4) NOT NULL DEFAULT '0', + priority tinyint(4) NOT NULL DEFAULT '0', + status varchar(20) DEFAULT NULL, + alien tinyint(4) NOT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1; + +INSERT INTO t2 VALUES +(22589,1,1,'Need Feedback',0); + +SELECT t2.id FROM t2 WHERE t2.id IN (SELECT id FROM t1 WHERE status = 'Verified'); + +drop table t1, t2; + --echo End of 5.1 tests diff --git a/mysql-test/t/range.test b/mysql-test/t/range.test index af90b6cd4f0..b1cec049ab2 100644 --- a/mysql-test/t/range.test +++ b/mysql-test/t/range.test @@ -525,6 +525,49 @@ explain select a from t1 where a > 'x'; select a from t1 where a > 'x'; drop table t1; +# +# Bug #24776: assertion abort for 'range checked for each record' +# + +CREATE TABLE t1 ( + OXID varchar(32) COLLATE latin1_german2_ci NOT NULL DEFAULT '', + OXPARENTID varchar(32) COLLATE latin1_german2_ci NOT NULL DEFAULT 'oxrootid', + OXLEFT int NOT NULL DEFAULT '0', + OXRIGHT int NOT NULL DEFAULT '0', + OXROOTID varchar(32) COLLATE latin1_german2_ci NOT NULL DEFAULT '', + PRIMARY KEY (OXID), + KEY OXNID (OXID), + KEY OXLEFT (OXLEFT), + KEY OXRIGHT (OXRIGHT), + KEY OXROOTID (OXROOTID) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci; + +INSERT INTO t1 VALUES +('d8c4177d09f8b11f5.52725521','oxrootid',1,40,'d8c4177d09f8b11f5.52725521'), +('d8c4177d151affab2.81582770','d8c4177d09f8b11f5.52725521',2,3, + 'd8c4177d09f8b11f5.52725521'), +('d8c4177d206a333d2.74422679','d8c4177d09f8b11f5.52725521',4,5, + 'd8c4177d09f8b11f5.52725521'), +('d8c4177d225791924.30714720','d8c4177d09f8b11f5.52725521',6,7, + 'd8c4177d09f8b11f5.52725521'), +('d8c4177d2380fc201.39666693','d8c4177d09f8b11f5.52725521',8,9, + 'd8c4177d09f8b11f5.52725521'), +('d8c4177d24ccef970.14957924','d8c4177d09f8b11f5.52725521',10,11, + 'd8c4177d09f8b11f5.52725521'); + +EXPLAIN +SELECT s.oxid FROM t1 v, t1 s + WHERE s.oxrootid = 'd8c4177d09f8b11f5.52725521' AND + v.oxrootid ='d8c4177d09f8b11f5.52725521' AND + s.oxleft > v.oxleft AND s.oxleft < v.oxright; + +SELECT s.oxid FROM t1 v, t1 s + WHERE s.oxrootid = 'd8c4177d09f8b11f5.52725521' AND + v.oxrootid ='d8c4177d09f8b11f5.52725521' AND + s.oxleft > v.oxleft AND s.oxleft < v.oxright; + +DROP TABLE t1; + --echo End of 4.1 tests # diff --git a/mysql-test/t/select.test b/mysql-test/t/select.test index 0e7361218aa..fbb2b4d8d3d 100644 --- a/mysql-test/t/select.test +++ b/mysql-test/t/select.test @@ -3104,6 +3104,16 @@ SELECT t3.a FROM t1,t2,t3 t3.c IN ('bb','ee'); DROP TABLE t1,t2,t3; + +# +# Bug#25172: Not checked buffer size leads to a server crash +# +CREATE TABLE t1 ( f1 int primary key, f2 int, f3 int, f4 int, f5 int, f6 int, checked_out int); +CREATE TABLE t2 ( f11 int PRIMARY KEY ); +INSERT INTO t1 VALUES (1,1,1,0,0,0,0),(2,1,1,3,8,1,0),(3,1,1,4,12,1,0); +INSERT INTO t2 VALUES (62); +SELECT * FROM t1 LEFT JOIN t2 ON f11 = t1.checked_out GROUP BY f1 ORDER BY f2, f3, f4, f5 LIMIT 0, 1; +DROP TABLE t1, t2; # # Bug#6298: LIMIT #, -1 no longer works to set start with no end limit diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test index eefa2528a17..98471622bc5 100644 --- a/mysql-test/t/subselect.test +++ b/mysql-test/t/subselect.test @@ -2510,6 +2510,40 @@ SELECT SQL_NO_CACHE COUNT(*) DROP TABLE t1,t2; # +# Bug #25219: EXIST subquery with UNION over a mix of +# correlated and uncorrelated selects +# + +CREATE TABLE t1 (id char(4) PRIMARY KEY, c int); +CREATE TABLE t2 (c int); + +INSERT INTO t1 VALUES ('aa', 1); +INSERT INTO t2 VALUES (1); + +SELECT * FROM t1 + WHERE EXISTS (SELECT c FROM t2 WHERE c=1 + UNION + SELECT c from t2 WHERE c=t1.c); + +INSERT INTO t1 VALUES ('bb', 2), ('cc', 3), ('dd',1); + +SELECT * FROM t1 + WHERE EXISTS (SELECT c FROM t2 WHERE c=1 + UNION + SELECT c from t2 WHERE c=t1.c); + +INSERT INTO t2 VALUES (2); +CREATE TABLE t3 (c int); +INSERT INTO t3 VALUES (1); + +SELECT * FROM t1 + WHERE EXISTS (SELECT t2.c FROM t2 JOIN t3 ON t2.c=t3.c WHERE t2.c=1 + UNION + SELECT c from t2 WHERE c=t1.c); + +DROP TABLE t1,t2,t3; + +# # Bug#20835 (literal string with =any values) # CREATE TABLE t1 (s1 char(1)); diff --git a/mysql-test/t/trigger.test b/mysql-test/t/trigger.test index 56d310eec37..b40a8538e04 100644 --- a/mysql-test/t/trigger.test +++ b/mysql-test/t/trigger.test @@ -1554,4 +1554,50 @@ select * from t1; drop table t1; +# +# Bug#25398: crash when a trigger contains a SELECT with +# trigger fields in the select list under DISTINCT +# + +CREATE TABLE t1 ( + id int NOT NULL DEFAULT '0', + a varchar(10) NOT NULL, + b varchar(10), + c varchar(10), + d timestamp NOT NULL, + PRIMARY KEY (id, a) +); + +CREATE TABLE t2 ( + fubar_id int unsigned NOT NULL DEFAULT '0', + last_change_time datetime NOT NULL DEFAULT '0000-00-00 00:00:00', + PRIMARY KEY (fubar_id) +); + +DELIMITER |; + +CREATE TRIGGER fubar_change + AFTER UPDATE ON t1 + FOR EACH ROW + BEGIN + INSERT INTO t2 (fubar_id, last_change_time) + SELECT DISTINCT NEW.id AS fubar_id, NOW() AS last_change_time + FROM t1 WHERE (id = NEW.id) AND (OLD.c != NEW.c) + ON DUPLICATE KEY UPDATE + last_change_time = + IF((fubar_id = NEW.id)AND(OLD.c != NEW.c),NOW(),last_change_time); + END +| + +DELIMITER ;| + +INSERT INTO t1 (id,a, b,c,d) VALUES + (1,'a','b','c',now()),(2,'a','b','c',now()); + +UPDATE t1 SET c='Bang!' WHERE id=1; + +SELECT fubar_id FROM t2; + +DROP TABLE t1,t2; + --echo End of 5.0 tests diff --git a/mysql-test/t/udf.test b/mysql-test/t/udf.test index 010a532f48b..7d870a86bc8 100644 --- a/mysql-test/t/udf.test +++ b/mysql-test/t/udf.test @@ -294,3 +294,50 @@ drop table bug18761; select is_const((1,2,3)); drop function if exists is_const; + +# +# Bug #25382: Passing NULL to an UDF called from stored procedures +# crashes server +# +--replace_result $UDF_EXAMPLE_LIB UDF_EXAMPLE_LIB +eval CREATE FUNCTION metaphon RETURNS STRING SONAME "$UDF_EXAMPLE_LIB"; + +--replace_result $UDF_EXAMPLE_LIB UDF_EXAMPLE_LIB +eval CREATE FUNCTION myfunc_double RETURNS REAL SONAME "$UDF_EXAMPLE_LIB"; + +--replace_result $UDF_EXAMPLE_LIB UDF_EXAMPLE_LIB +eval CREATE FUNCTION myfunc_int RETURNS INTEGER SONAME "$UDF_EXAMPLE_LIB"; + +delimiter //; +create function f1(p1 varchar(255)) +returns varchar(255) +begin + return metaphon(p1); +end// + +create function f2(p1 varchar(255)) +returns double +begin + return myfunc_double(p1); +end// + +create function f3(p1 varchar(255)) +returns double +begin + return myfunc_int(p1); +end// + +delimiter ;// + +select f3(NULL); +select f2(NULL); +select f1(NULL); + +drop function f1; +drop function f2; +drop function f3; +drop function metaphon; +drop function myfunc_double; +drop function myfunc_int; + +--echo End of 5.0 tests. diff --git a/mysql-test/t/view.test b/mysql-test/t/view.test index ea429e2a0f0..2e67fc3d2d2 100644 --- a/mysql-test/t/view.test +++ b/mysql-test/t/view.test @@ -1879,15 +1879,6 @@ DROP VIEW v1; DROP TABLE t1; # -# Bug #11335 View redefines column types -# -create table t1 (f1 tinyint(1), f2 char(1), f3 varchar(1), f4 geometry, f5 datetime); -create view v1 as select * from t1; -desc v1; -drop view v1; -drop table t1; - -# # Bug #11760 Typo in Item_func_add_time::print() results in NULLs returned # subtime() in view create table t1(f1 datetime); @@ -2907,6 +2898,57 @@ DROP VIEW v1; DROP TABLE t1; # +# BUG#22584: last_insert_id not updated after inserting a record +# through a updatable view +# +# We still do not update LAST_INSERT_ID if AUTO_INCREMENT column is +# not accessible through a view. However, we do not reset the value +# of LAST_INSERT_ID, but keep it unchanged. +# +--disable_warnings +DROP VIEW IF EXISTS v1, v2; +DROP TABLE IF EXISTS t1; +--enable_warnings + +CREATE TABLE t1 (i INT AUTO_INCREMENT PRIMARY KEY, j INT); +CREATE VIEW v1 AS SELECT j FROM t1; +CREATE VIEW v2 AS SELECT * FROM t1; + +INSERT INTO t1 (j) VALUES (1); +SELECT LAST_INSERT_ID(); + +INSERT INTO v1 (j) VALUES (2); +--echo # LAST_INSERT_ID() should not change. +SELECT LAST_INSERT_ID(); + +INSERT INTO v2 (j) VALUES (3); +--echo # LAST_INSERT_ID() should be updated. +SELECT LAST_INSERT_ID(); + +INSERT INTO v1 (j) SELECT j FROM t1; +--echo # LAST_INSERT_ID() should not change. +SELECT LAST_INSERT_ID(); + +SELECT * FROM t1; + +DROP VIEW v1, v2; +DROP TABLE t1; + +# +# Bug #25580: !0 as an operand in a select expression of a view +# + +CREATE VIEW v AS SELECT !0 * 5 AS x FROM DUAL; +SHOW CREATE VIEW v; + +SELECT !0 * 5 AS x FROM DUAL; +SELECT * FROM v; + +DROP VIEW v; + + +--echo End of 5.0 tests. + # Bug #16813 (WITH CHECK OPTION doesn't work with UPDATE) # CREATE TABLE t1(id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, val INT UNSIGNED NOT NULL); |