diff options
author | istruewing@chilla.local <> | 2007-02-14 13:39:59 +0100 |
---|---|---|
committer | istruewing@chilla.local <> | 2007-02-14 13:39:59 +0100 |
commit | 3e8c0c4eb90322deba33e90c0797f9561d2f1a15 (patch) | |
tree | fd48d794aa535a68b1642df9583c88b178fc804a /mysql-test | |
parent | 5f235d9dc214d65928d26d77d342fb4b8a602195 (diff) | |
parent | d36e264cc56561b3e427326f81c501f16cdc6424 (diff) | |
download | mariadb-git-3e8c0c4eb90322deba33e90c0797f9561d2f1a15.tar.gz |
Merge bk-internal.mysql.com:/home/bk/mysql-5.1
into chilla.local:/home/mydev/mysql-5.1-axmrg
Diffstat (limited to 'mysql-test')
-rw-r--r-- | mysql-test/include/mix1.inc | 29 | ||||
-rw-r--r-- | mysql-test/r/default.result | 12 | ||||
-rw-r--r-- | mysql-test/r/gis.result | 4 | ||||
-rw-r--r-- | mysql-test/r/information_schema.result | 51 | ||||
-rw-r--r-- | mysql-test/r/innodb_mysql.result | 64 | ||||
-rw-r--r-- | mysql-test/r/insert_select.result | 12 | ||||
-rw-r--r-- | mysql-test/r/join_outer.result | 20 | ||||
-rw-r--r-- | mysql-test/r/select.result | 28 | ||||
-rw-r--r-- | mysql-test/r/update.result | 57 | ||||
-rw-r--r-- | mysql-test/r/view.result | 143 | ||||
-rw-r--r-- | mysql-test/t/default.test | 9 | ||||
-rw-r--r-- | mysql-test/t/gis.test | 8 | ||||
-rw-r--r-- | mysql-test/t/information_schema.test | 24 | ||||
-rw-r--r-- | mysql-test/t/insert_select.test | 13 | ||||
-rw-r--r-- | mysql-test/t/join_outer.test | 23 | ||||
-rw-r--r-- | mysql-test/t/select.test | 23 | ||||
-rw-r--r-- | mysql-test/t/update.test | 35 | ||||
-rw-r--r-- | mysql-test/t/view.test | 112 |
18 files changed, 587 insertions, 80 deletions
diff --git a/mysql-test/include/mix1.inc b/mysql-test/include/mix1.inc index 4bd93220c1e..f0d143c4570 100644 --- a/mysql-test/include/mix1.inc +++ b/mysql-test/include/mix1.inc @@ -388,6 +388,35 @@ EXPLAIN SELECT SQL_BIG_RESULT b, SUM(c) FROM t1 GROUP BY b; DROP TABLE t1; # +# Bug#26159: crash for a loose scan of a table that has been emptied +# + +CREATE TABLE t1 ( + id int NOT NULL, + name varchar(20) NOT NULL, + dept varchar(20) NOT NULL, + age tinyint(3) unsigned NOT NULL, + PRIMARY KEY (id), + INDEX (name,dept) +) ENGINE=InnoDB; +INSERT INTO t1(id, dept, age, name) VALUES + (3987, 'cs1', 10, 'rs1'), (3988, 'cs2', 20, 'rs1'), (3995, 'cs3', 10, 'rs2'), + (3996, 'cs4', 20, 'rs2'), (4003, 'cs5', 10, 'rs3'), (4004, 'cs6', 20, 'rs3'), + (4011, 'cs7', 10, 'rs4'), (4012, 'cs8', 20, 'rs4'), (4019, 'cs9', 10, 'rs5'), + (4020, 'cs10', 20, 'rs5'),(4027, 'cs11', 10, 'rs6'),(4028, 'cs12', 20, 'rs6'); + +EXPLAIN SELECT DISTINCT t1.name, t1.dept FROM t1 WHERE t1.name='rs5'; +SELECT DISTINCT t1.name, t1.dept FROM t1 WHERE t1.name='rs5'; +DELETE FROM t1; +EXPLAIN SELECT DISTINCT t1.name, t1.dept FROM t1 WHERE t1.name='rs5'; +SELECT DISTINCT t1.name, t1.dept FROM t1 WHERE t1.name='rs5'; + +DROP TABLE t1; + +--source include/innodb_rollback_on_timeout.inc + +--echo End of 5.0 tests +# # Test of behaviour with CREATE ... SELECT # diff --git a/mysql-test/r/default.result b/mysql-test/r/default.result index 9bef2e2fdbf..cf9ef4f8f8b 100644 --- a/mysql-test/r/default.result +++ b/mysql-test/r/default.result @@ -193,6 +193,16 @@ a b c d e f g h i x two large 00:00:05 0007-01-01 11 13 17 0019-01-01 00:00:00 23 1 small 00:00:00 0000-00-00 0 0000-00-00 00:00:00 0 2 two large 00:00:05 0007-01-01 11 13 17 0019-01-01 00:00:00 23 3 - small 00:00:00 0000-00-00 0 0000-00-00 00:00:00 0 4 + 00:00:00 0000-00-00 0 0000-00-00 00:00:00 0 4 drop table bug20691; +create table t1 (id int not null); +insert into t1 values(default); +Warnings: +Warning 1364 Field 'id' doesn't have a default value +create view v1 (c) as select id from t1; +insert into t1 values(default); +Warnings: +Warning 1364 Field 'id' doesn't have a default value +drop view v1; +drop table t1; End of 5.0 tests. diff --git a/mysql-test/r/gis.result b/mysql-test/r/gis.result index 8cbc344f062..6483b12acb0 100644 --- a/mysql-test/r/gis.result +++ b/mysql-test/r/gis.result @@ -718,6 +718,10 @@ desc t1; Field Type Null Key Default Extra GeomFromText('point(1 1)') geometry NO drop table t1; +create table t1 (g geometry not null); +insert into t1 values(default); +ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field +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; diff --git a/mysql-test/r/information_schema.result b/mysql-test/r/information_schema.result index 58dc82b6c2a..e5d6078e863 100644 --- a/mysql-test/r/information_schema.result +++ b/mysql-test/r/information_schema.result @@ -1335,8 +1335,7 @@ from information_schema.tables order by object_schema; explain select * from v1; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <derived2> system NULL NULL NULL NULL 0 const row not found -2 DERIVED tables ALL NULL NULL NULL NULL 0 Using filesort +1 SIMPLE tables ALL NULL NULL NULL NULL 2 Using filesort explain select * from (select table_name from information_schema.tables) as a; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY <derived2> system NULL NULL NULL NULL 0 const row not found @@ -1352,6 +1351,54 @@ table_name t1 t2 drop table t1,t2; +select 1 as f1 from information_schema.tables where "CHARACTER_SETS"= +(select cast(table_name as char) from information_schema.tables +order by table_name limit 1) limit 1; +f1 +1 +select t.table_name, group_concat(t.table_schema, '.', t.table_name), +count(*) as num1 +from information_schema.tables t +inner join information_schema.columns c1 +on t.table_schema = c1.table_schema AND t.table_name = c1.table_name +where t.table_schema = 'information_schema' and +c1.ordinal_position = +(select isnull(c2.column_type) - +isnull(group_concat(c2.table_schema, '.', c2.table_name)) + +count(*) as num +from information_schema.columns c2 where +c2.table_schema='information_schema' and +(c2.column_type = 'varchar(7)' or c2.column_type = 'varchar(20)') +group by c2.column_type order by num limit 1) +group by t.table_name order by num1, t.table_name; +table_name group_concat(t.table_schema, '.', t.table_name) num1 +CHARACTER_SETS information_schema.CHARACTER_SETS 1 +COLLATIONS information_schema.COLLATIONS 1 +COLLATION_CHARACTER_SET_APPLICABILITY information_schema.COLLATION_CHARACTER_SET_APPLICABILITY 1 +COLUMNS information_schema.COLUMNS 1 +COLUMN_PRIVILEGES information_schema.COLUMN_PRIVILEGES 1 +ENGINES information_schema.ENGINES 1 +EVENTS information_schema.EVENTS 1 +FILES information_schema.FILES 1 +GLOBAL_STATUS information_schema.GLOBAL_STATUS 1 +GLOBAL_VARIABLES information_schema.GLOBAL_VARIABLES 1 +KEY_COLUMN_USAGE information_schema.KEY_COLUMN_USAGE 1 +PARTITIONS information_schema.PARTITIONS 1 +PLUGINS information_schema.PLUGINS 1 +PROCESSLIST information_schema.PROCESSLIST 1 +REFERENTIAL_CONSTRAINTS information_schema.REFERENTIAL_CONSTRAINTS 1 +ROUTINES information_schema.ROUTINES 1 +SCHEMATA information_schema.SCHEMATA 1 +SCHEMA_PRIVILEGES information_schema.SCHEMA_PRIVILEGES 1 +SESSION_STATUS information_schema.SESSION_STATUS 1 +SESSION_VARIABLES information_schema.SESSION_VARIABLES 1 +STATISTICS information_schema.STATISTICS 1 +TABLES information_schema.TABLES 1 +TABLE_CONSTRAINTS information_schema.TABLE_CONSTRAINTS 1 +TABLE_PRIVILEGES information_schema.TABLE_PRIVILEGES 1 +TRIGGERS information_schema.TRIGGERS 1 +USER_PRIVILEGES information_schema.USER_PRIVILEGES 1 +VIEWS information_schema.VIEWS 1 End of 5.0 tests. select * from information_schema.engines WHERE ENGINE="MyISAM"; ENGINE SUPPORT COMMENT TRANSACTIONS XA SAVEPOINTS diff --git a/mysql-test/r/innodb_mysql.result b/mysql-test/r/innodb_mysql.result index 2c5b7184786..e720c38ec8d 100644 --- a/mysql-test/r/innodb_mysql.result +++ b/mysql-test/r/innodb_mysql.result @@ -324,6 +324,70 @@ EXPLAIN SELECT SQL_BIG_RESULT b, SUM(c) FROM t1 GROUP BY b; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 128 Using filesort DROP TABLE t1; +CREATE TABLE t1 ( +id int NOT NULL, +name varchar(20) NOT NULL, +dept varchar(20) NOT NULL, +age tinyint(3) unsigned NOT NULL, +PRIMARY KEY (id), +INDEX (name,dept) +) ENGINE=InnoDB; +INSERT INTO t1(id, dept, age, name) VALUES +(3987, 'cs1', 10, 'rs1'), (3988, 'cs2', 20, 'rs1'), (3995, 'cs3', 10, 'rs2'), +(3996, 'cs4', 20, 'rs2'), (4003, 'cs5', 10, 'rs3'), (4004, 'cs6', 20, 'rs3'), +(4011, 'cs7', 10, 'rs4'), (4012, 'cs8', 20, 'rs4'), (4019, 'cs9', 10, 'rs5'), +(4020, 'cs10', 20, 'rs5'),(4027, 'cs11', 10, 'rs6'),(4028, 'cs12', 20, 'rs6'); +EXPLAIN SELECT DISTINCT t1.name, t1.dept FROM t1 WHERE t1.name='rs5'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range name name 44 NULL 2 Using where; Using index for group-by +SELECT DISTINCT t1.name, t1.dept FROM t1 WHERE t1.name='rs5'; +name dept +rs5 cs10 +rs5 cs9 +DELETE FROM t1; +EXPLAIN SELECT DISTINCT t1.name, t1.dept FROM t1 WHERE t1.name='rs5'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range name name 44 NULL 2 Using where; Using index for group-by +SELECT DISTINCT t1.name, t1.dept FROM t1 WHERE t1.name='rs5'; +name dept +DROP TABLE t1; +show variables like 'innodb_rollback_on_timeout'; +Variable_name Value +innodb_rollback_on_timeout OFF +create table t1 (a int unsigned not null primary key) engine = innodb; +insert into t1 values (1); +commit; +begin work; +insert into t1 values (2); +select * from t1; +a +1 +2 +begin work; +insert into t1 values (5); +select * from t1; +a +1 +5 +insert into t1 values (2); +ERROR HY000: Lock wait timeout exceeded; try restarting transaction +select * from t1; +a +1 +5 +commit; +select * from t1; +a +1 +2 +commit; +select * from t1; +a +1 +2 +5 +drop table t1; +End of 5.0 tests CREATE TABLE t1 (a int, b int); insert into t1 values (1,1),(1,2); CREATE TABLE t2 (primary key (a)) select * from t1; diff --git a/mysql-test/r/insert_select.result b/mysql-test/r/insert_select.result index b6c02869914..fdb59c02b9d 100644 --- a/mysql-test/r/insert_select.result +++ b/mysql-test/r/insert_select.result @@ -705,3 +705,15 @@ use bug21774_1; INSERT INTO bug21774_2.t1 SELECT t1.* FROM t1; DROP DATABASE bug21774_1; DROP DATABASE bug21774_2; +USE test; +create table t1(f1 int primary key, f2 int); +insert into t1 values (1,1); +affected rows: 1 +insert into t1 values (1,1) on duplicate key update f2=1; +affected rows: 0 +insert into t1 values (1,1) on duplicate key update f2=2; +affected rows: 2 +select * from t1; +f1 f2 +1 2 +drop table t1; diff --git a/mysql-test/r/join_outer.result b/mysql-test/r/join_outer.result index 4d58d035f21..e667d11195d 100644 --- a/mysql-test/r/join_outer.result +++ b/mysql-test/r/join_outer.result @@ -1194,3 +1194,23 @@ a b 3 3 4 NULL DROP TABLE t1,t2; +CREATE TABLE t1 ( +f1 varchar(16) collate latin1_swedish_ci PRIMARY KEY, +f2 varchar(16) collate latin1_swedish_ci +); +CREATE TABLE t2 ( +f1 varchar(16) collate latin1_swedish_ci PRIMARY KEY, +f3 varchar(16) collate latin1_swedish_ci +); +INSERT INTO t1 VALUES ('bla','blah'); +INSERT INTO t2 VALUES ('bla','sheep'); +SELECT * FROM t1 JOIN t2 USING(f1) WHERE f1='Bla'; +f1 f2 f3 +bla blah sheep +SELECT * FROM t1 LEFT JOIN t2 USING(f1) WHERE f1='bla'; +f1 f2 f3 +bla blah sheep +SELECT * FROM t1 LEFT JOIN t2 USING(f1) WHERE f1='Bla'; +f1 f2 f3 +bla blah sheep +DROP TABLE t1,t2; diff --git a/mysql-test/r/select.result b/mysql-test/r/select.result index a324a9a2f80..ec4ac0d6079 100644 --- a/mysql-test/r/select.result +++ b/mysql-test/r/select.result @@ -3728,6 +3728,34 @@ WHERE ID_better=1 AND ID1_with_null IS NULL AND id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref idx1,idx2 idx2 4 const 1 Using where DROP TABLE t1; +CREATE TABLE t1 (a INT, ts TIMESTAMP, KEY ts(ts)); +INSERT INTO t1 VALUES (30,"2006-01-03 23:00:00"), (31,"2006-01-03 23:00:00"); +ANALYZE TABLE t1; +Table Op Msg_type Msg_text +test.t1 analyze status OK +CREATE TABLE t2 (a INT, dt1 DATETIME, dt2 DATETIME, PRIMARY KEY (a)); +INSERT INTO t2 VALUES (30, "2006-01-01 00:00:00", "2999-12-31 00:00:00"); +INSERT INTO t2 SELECT a+1,dt1,dt2 FROM t2; +ANALYZE TABLE t2; +Table Op Msg_type Msg_text +test.t2 analyze status OK +EXPLAIN +SELECT * FROM t1 LEFT JOIN t2 ON (t1.a=t2.a) WHERE t1.a=30 +AND t1.ts BETWEEN t2.dt1 AND t2.dt2 +AND t1.ts BETWEEN "2006-01-01" AND "2006-12-31"; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 const PRIMARY PRIMARY 4 const 1 +1 SIMPLE t1 range ts ts 4 NULL 1 Using where +Warnings: +Warning 1292 Incorrect datetime value: '2999-12-31 00:00:00' for column 'ts' at row 1 +SELECT * FROM t1 LEFT JOIN t2 ON (t1.a=t2.a) WHERE t1.a=30 +AND t1.ts BETWEEN t2.dt1 AND t2.dt2 +AND t1.ts BETWEEN "2006-01-01" AND "2006-12-31"; +a ts a dt1 dt2 +30 2006-01-03 23:00:00 30 2006-01-01 00:00:00 2999-12-31 00:00:00 +Warnings: +Warning 1292 Incorrect datetime value: '2999-12-31 00:00:00' for column 'ts' at row 1 +DROP TABLE t1,t2; create table t1 (a bigint unsigned); insert into t1 values (if(1, 9223372036854775808, 1)), diff --git a/mysql-test/r/update.result b/mysql-test/r/update.result index 4b7dbb3dbe9..5b73cb9501a 100644 --- a/mysql-test/r/update.result +++ b/mysql-test/r/update.result @@ -377,3 +377,60 @@ create table t1(f1 int, `*f2` int); insert into t1 values (1,1); update t1 set `*f2`=1; drop table t1; +CREATE TABLE t1 ( +request_id int unsigned NOT NULL auto_increment, +user_id varchar(12) default NULL, +time_stamp datetime NOT NULL default '0000-00-00 00:00:00', +ip_address varchar(15) default NULL, +PRIMARY KEY (request_id), +KEY user_id_2 (user_id,time_stamp) +); +INSERT INTO t1 (user_id) VALUES ('user1'); +INSERT INTO t1(user_id) SELECT user_id FROM t1; +INSERT INTO t1(user_id) SELECT user_id FROM t1; +INSERT INTO t1(user_id) SELECT user_id FROM t1; +INSERT INTO t1(user_id) SELECT user_id FROM t1; +INSERT INTO t1(user_id) SELECT user_id FROM t1; +INSERT INTO t1(user_id) SELECT user_id FROM t1; +INSERT INTO t1(user_id) SELECT user_id FROM t1; +INSERT INTO t1(user_id) SELECT user_id FROM t1; +flush status; +SELECT user_id FROM t1 WHERE request_id=9999999999999; +user_id +show status like '%Handler_read%'; +Variable_name Value +Handler_read_first 0 +Handler_read_key 1 +Handler_read_next 0 +Handler_read_prev 0 +Handler_read_rnd 0 +Handler_read_rnd_next 0 +SELECT user_id FROM t1 WHERE request_id=999999999999999999999999999999; +user_id +show status like '%Handler_read%'; +Variable_name Value +Handler_read_first 0 +Handler_read_key 2 +Handler_read_next 0 +Handler_read_prev 0 +Handler_read_rnd 0 +Handler_read_rnd_next 0 +UPDATE t1 SET user_id=null WHERE request_id=9999999999999; +show status like '%Handler_read%'; +Variable_name Value +Handler_read_first 0 +Handler_read_key 3 +Handler_read_next 0 +Handler_read_prev 0 +Handler_read_rnd 0 +Handler_read_rnd_next 0 +UPDATE t1 SET user_id=null WHERE request_id=999999999999999999999999999999; +show status like '%Handler_read%'; +Variable_name Value +Handler_read_first 0 +Handler_read_key 3 +Handler_read_next 0 +Handler_read_prev 0 +Handler_read_rnd 0 +Handler_read_rnd_next 0 +DROP TABLE t1; diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result index c0d8e3e249b..8f83e1acb09 100644 --- a/mysql-test/r/view.result +++ b/mysql-test/r/view.result @@ -2532,7 +2532,7 @@ create table t1(f1 int, f2 int); create view v1 as select ta.f1 as a, tb.f1 as b from t1 ta, t1 tb where ta.f1=tb .f1 and ta.f2=tb.f2; insert into t1 values(1,1),(2,2); -create view v2 as select * from v1 where a > 1 with check option; +create view v2 as select * from v1 where a > 1 with local check option; select * from v2; a b 2 2 @@ -2961,6 +2961,16 @@ 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); @@ -3015,51 +3025,108 @@ SHOW CREATE VIEW v1; 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' +CREATE TABLE t1 (mydate DATETIME); +INSERT INTO t1 VALUES +('2007-01-01'), ('2007-01-02'), ('2007-01-30'), ('2007-01-31'); +CREATE VIEW v1 AS SELECT mydate from t1; +SELECT * FROM t1 WHERE mydate BETWEEN '2007-01-01' AND '2007-01-31'; +mydate +2007-01-01 00:00:00 +2007-01-02 00:00:00 +2007-01-30 00:00:00 +2007-01-31 00:00:00 +SELECT * FROM v1 WHERE mydate BETWEEN '2007-01-01' AND '2007-01-31'; +mydate +2007-01-01 00:00:00 +2007-01-02 00:00:00 +2007-01-30 00:00:00 +2007-01-31 00:00:00 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() +CREATE TABLE t1 (a int); +CREATE TABLE t2 (b int); +INSERT INTO t1 VALUES (1), (2); +INSERT INTO t2 VALUES (1), (2); +CREATE VIEW v1 AS +SELECT t2.b FROM t1,t2 WHERE t1.a = t2.b WITH CHECK OPTION; +SELECT * FROM v1; +b 1 -INSERT INTO v1 (j) VALUES (2); -# LAST_INSERT_ID() should not change. -SELECT LAST_INSERT_ID(); -LAST_INSERT_ID() +2 +UPDATE v1 SET b=3; +ERROR HY000: CHECK OPTION failed 'test.v1' +SELECT * FROM v1; +b 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 +2 SELECT * FROM t1; -i j +a +1 +2 +SELECT * FROM t2; +b +1 +2 +DROP VIEW v1; +DROP TABLE t1,t2; +create table t1(f1 int, f2 int); +insert into t1 values(1,2),(1,3),(1,1),(2,3),(2,1),(2,2); +select * from t1; +f1 f2 +1 2 +1 3 1 1 +2 3 +2 1 2 2 -3 3 -4 1 -5 2 -6 3 -DROP VIEW v1, v2; +create view v1 as select * from t1 order by f2; +select * from v1; +f1 f2 +1 1 +2 1 +1 2 +2 2 +1 3 +2 3 +explain extended select * from v1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 6 100.00 Using filesort +Warnings: +Note 1003 select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f2` AS `f2` from `test`.`t1` order by `test`.`t1`.`f2` +select * from v1 order by f1; +f1 f2 +1 1 +1 2 +1 3 +2 1 +2 2 +2 3 +explain extended select * from v1 order by f1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 6 100.00 Using filesort +Warnings: +Note 1003 select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f2` AS `f2` from `test`.`t1` order by `test`.`t1`.`f1`,`test`.`t1`.`f2` +drop view v1; +drop table t1; +CREATE TABLE t1 ( +id int(11) NOT NULL PRIMARY KEY, +country varchar(32), +code int(11) default NULL +); +INSERT INTO t1 VALUES +(1,'ITALY',100),(2,'ITALY',200),(3,'FRANCE',100), (4,'ITALY',100); +CREATE VIEW v1 AS SELECT * FROM t1; +SELECT code, COUNT(DISTINCT country) FROM t1 GROUP BY code ORDER BY MAX(id); +code COUNT(DISTINCT country) +200 1 +100 2 +SELECT code, COUNT(DISTINCT country) FROM v1 GROUP BY code ORDER BY MAX(id); +code COUNT(DISTINCT country) +200 1 +100 2 +DROP VIEW v1; DROP TABLE t1; +End of 5.0 tests. DROP DATABASE IF EXISTS `d-1`; CREATE DATABASE `d-1`; USE `d-1`; diff --git a/mysql-test/t/default.test b/mysql-test/t/default.test index 225ddbc3ee2..14aa4b02cfe 100644 --- a/mysql-test/t/default.test +++ b/mysql-test/t/default.test @@ -137,6 +137,13 @@ insert into bug20691 values (DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAUL select * from bug20691 order by x asc; drop table bug20691; -### +create table t1 (id int not null); +insert into t1 values(default); + +create view v1 (c) as select id from t1; +insert into t1 values(default); +drop view v1; +drop table t1; + --echo End of 5.0 tests. diff --git a/mysql-test/t/gis.test b/mysql-test/t/gis.test index d1ea8dbe18f..51dec7d2b81 100644 --- a/mysql-test/t/gis.test +++ b/mysql-test/t/gis.test @@ -430,6 +430,14 @@ desc t1; drop table t1; # +# Bug #20691 (DEFAULT over NOT NULL field) +# +create table t1 (g geometry not null); +--error ER_CANT_CREATE_GEOMETRY_OBJECT +insert into t1 values(default); +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); diff --git a/mysql-test/t/information_schema.test b/mysql-test/t/information_schema.test index de329ce7b0b..09a588c9195 100644 --- a/mysql-test/t/information_schema.test +++ b/mysql-test/t/information_schema.test @@ -1000,6 +1000,30 @@ where table_schema = 'test' and table_name not in where table_schema = 'test' and column_name = 'f3'); drop table t1,t2; + +# +# Bug#24630 Subselect query crashes mysqld +# +select 1 as f1 from information_schema.tables where "CHARACTER_SETS"= +(select cast(table_name as char) from information_schema.tables + order by table_name limit 1) limit 1; + +select t.table_name, group_concat(t.table_schema, '.', t.table_name), + count(*) as num1 +from information_schema.tables t +inner join information_schema.columns c1 +on t.table_schema = c1.table_schema AND t.table_name = c1.table_name +where t.table_schema = 'information_schema' and + c1.ordinal_position = + (select isnull(c2.column_type) - + isnull(group_concat(c2.table_schema, '.', c2.table_name)) + + count(*) as num + from information_schema.columns c2 where + c2.table_schema='information_schema' and + (c2.column_type = 'varchar(7)' or c2.column_type = 'varchar(20)') + group by c2.column_type order by num limit 1) +group by t.table_name order by num1, t.table_name; + --echo End of 5.0 tests. # # Show engines diff --git a/mysql-test/t/insert_select.test b/mysql-test/t/insert_select.test index 1994d23afd7..8f9e58de1e2 100644 --- a/mysql-test/t/insert_select.test +++ b/mysql-test/t/insert_select.test @@ -265,4 +265,17 @@ INSERT INTO bug21774_2.t1 SELECT t1.* FROM t1; DROP DATABASE bug21774_1; DROP DATABASE bug21774_2; +USE test; +# +# Bug#19978: INSERT .. ON DUPLICATE erroneously reports some records were +# updated. +# +create table t1(f1 int primary key, f2 int); +--enable_info +insert into t1 values (1,1); +insert into t1 values (1,1) on duplicate key update f2=1; +insert into t1 values (1,1) on duplicate key update f2=2; +--disable_info +select * from t1; +drop table t1; diff --git a/mysql-test/t/join_outer.test b/mysql-test/t/join_outer.test index 9bc88a95ffe..10856142701 100644 --- a/mysql-test/t/join_outer.test +++ b/mysql-test/t/join_outer.test @@ -808,3 +808,26 @@ SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.b WHERE (1=1 OR 2=2); SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.b WHERE (1=1 OR 1=0); DROP TABLE t1,t2; + +# +# Bug 26017: LEFT OUTER JOIN over two constant tables and +# a case-insensitive comparison predicate field=const +# + +CREATE TABLE t1 ( + f1 varchar(16) collate latin1_swedish_ci PRIMARY KEY, + f2 varchar(16) collate latin1_swedish_ci +); +CREATE TABLE t2 ( + f1 varchar(16) collate latin1_swedish_ci PRIMARY KEY, + f3 varchar(16) collate latin1_swedish_ci +); + +INSERT INTO t1 VALUES ('bla','blah'); +INSERT INTO t2 VALUES ('bla','sheep'); + +SELECT * FROM t1 JOIN t2 USING(f1) WHERE f1='Bla'; +SELECT * FROM t1 LEFT JOIN t2 USING(f1) WHERE f1='bla'; +SELECT * FROM t1 LEFT JOIN t2 USING(f1) WHERE f1='Bla'; + +DROP TABLE t1,t2; diff --git a/mysql-test/t/select.test b/mysql-test/t/select.test index ac8798c331f..c3770614378 100644 --- a/mysql-test/t/select.test +++ b/mysql-test/t/select.test @@ -3208,6 +3208,29 @@ EXPLAIN SELECT * FROM t1 (ID2_with_null=1 OR ID2_with_null=2); DROP TABLE t1; + +# +# Bug #22344: InnoDB keys act strange on datetime vs timestamp comparison +# +CREATE TABLE t1 (a INT, ts TIMESTAMP, KEY ts(ts)); +INSERT INTO t1 VALUES (30,"2006-01-03 23:00:00"), (31,"2006-01-03 23:00:00"); +ANALYZE TABLE t1; + +CREATE TABLE t2 (a INT, dt1 DATETIME, dt2 DATETIME, PRIMARY KEY (a)); +INSERT INTO t2 VALUES (30, "2006-01-01 00:00:00", "2999-12-31 00:00:00"); +INSERT INTO t2 SELECT a+1,dt1,dt2 FROM t2; +ANALYZE TABLE t2; + +EXPLAIN +SELECT * FROM t1 LEFT JOIN t2 ON (t1.a=t2.a) WHERE t1.a=30 + AND t1.ts BETWEEN t2.dt1 AND t2.dt2 + AND t1.ts BETWEEN "2006-01-01" AND "2006-12-31"; + +SELECT * FROM t1 LEFT JOIN t2 ON (t1.a=t2.a) WHERE t1.a=30 + AND t1.ts BETWEEN t2.dt1 AND t2.dt2 + AND t1.ts BETWEEN "2006-01-01" AND "2006-12-31"; + +DROP TABLE t1,t2; # Bug #22026: Warning when using IF statement and large unsigned bigint # diff --git a/mysql-test/t/update.test b/mysql-test/t/update.test index 5a49de248b1..3ce7ef72670 100644 --- a/mysql-test/t/update.test +++ b/mysql-test/t/update.test @@ -307,3 +307,38 @@ insert into t1 values (1,1); update t1 set `*f2`=1; drop table t1; # End of 4.1 tests + +# +# Bug #24035: performance degradation with condition int_field=big_decimal +# + +CREATE TABLE t1 ( + request_id int unsigned NOT NULL auto_increment, + user_id varchar(12) default NULL, + time_stamp datetime NOT NULL default '0000-00-00 00:00:00', + ip_address varchar(15) default NULL, + PRIMARY KEY (request_id), + KEY user_id_2 (user_id,time_stamp) +); + +INSERT INTO t1 (user_id) VALUES ('user1'); +INSERT INTO t1(user_id) SELECT user_id FROM t1; +INSERT INTO t1(user_id) SELECT user_id FROM t1; +INSERT INTO t1(user_id) SELECT user_id FROM t1; +INSERT INTO t1(user_id) SELECT user_id FROM t1; +INSERT INTO t1(user_id) SELECT user_id FROM t1; +INSERT INTO t1(user_id) SELECT user_id FROM t1; +INSERT INTO t1(user_id) SELECT user_id FROM t1; +INSERT INTO t1(user_id) SELECT user_id FROM t1; + +flush status; +SELECT user_id FROM t1 WHERE request_id=9999999999999; +show status like '%Handler_read%'; +SELECT user_id FROM t1 WHERE request_id=999999999999999999999999999999; +show status like '%Handler_read%'; +UPDATE t1 SET user_id=null WHERE request_id=9999999999999; +show status like '%Handler_read%'; +UPDATE t1 SET user_id=null WHERE request_id=999999999999999999999999999999; +show status like '%Handler_read%'; + +DROP TABLE t1; diff --git a/mysql-test/t/view.test b/mysql-test/t/view.test index 960fc9d39a8..67415499a61 100644 --- a/mysql-test/t/view.test +++ b/mysql-test/t/view.test @@ -2377,7 +2377,7 @@ create table t1(f1 int, f2 int); create view v1 as select ta.f1 as a, tb.f1 as b from t1 ta, t1 tb where ta.f1=tb .f1 and ta.f2=tb.f2; insert into t1 values(1,1),(2,2); -create view v2 as select * from v1 where a > 1 with check option; +create view v2 as select * from v1 where a > 1 with local check option; select * from v2; update v2 set b=3 where a=2; select * from v2; @@ -2900,6 +2900,20 @@ DROP VIEW v1; DROP TABLE t1; # +# 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); +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); +-- error 1369 +INSERT INTO v1 (val) VALUES (6); +-- error 1369 +UPDATE v1 SET val=6 WHERE id=2; +DROP VIEW v1; +DROP TABLE t1; + +# # BUG#22584: last_insert_id not updated after inserting a record # through a updatable view # @@ -2963,58 +2977,80 @@ SHOW CREATE VIEW v1; DROP VIEW v1; ---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); -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); --- error 1369 -INSERT INTO v1 (val) VALUES (6); --- error 1369 -UPDATE v1 SET val=6 WHERE id=2; +# Bug #26124: BETWEEN over a view column of the DATETIME type +# + +CREATE TABLE t1 (mydate DATETIME); +INSERT INTO t1 VALUES + ('2007-01-01'), ('2007-01-02'), ('2007-01-30'), ('2007-01-31'); + +CREATE VIEW v1 AS SELECT mydate from t1; + +SELECT * FROM t1 WHERE mydate BETWEEN '2007-01-01' AND '2007-01-31'; +SELECT * FROM v1 WHERE mydate BETWEEN '2007-01-01' AND '2007-01-31'; + 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. +# Bug #25931: update of a multi-table view with check option # ---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; +CREATE TABLE t1 (a int); +CREATE TABLE t2 (b int); +INSERT INTO t1 VALUES (1), (2); +INSERT INTO t2 VALUES (1), (2); -INSERT INTO t1 (j) VALUES (1); -SELECT LAST_INSERT_ID(); +CREATE VIEW v1 AS + SELECT t2.b FROM t1,t2 WHERE t1.a = t2.b WITH CHECK OPTION; -INSERT INTO v1 (j) VALUES (2); ---echo # LAST_INSERT_ID() should not change. -SELECT LAST_INSERT_ID(); +SELECT * FROM v1; +--error 1369 +UPDATE v1 SET b=3; +SELECT * FROM v1; +SELECT * FROM t1; +SELECT * FROM t2; -INSERT INTO v2 (j) VALUES (3); ---echo # LAST_INSERT_ID() should be updated. -SELECT LAST_INSERT_ID(); +DROP VIEW v1; +DROP TABLE t1,t2; -INSERT INTO v1 (j) SELECT j FROM t1; ---echo # LAST_INSERT_ID() should not change. -SELECT LAST_INSERT_ID(); +# +# Bug#12122: Views with ORDER BY can't be resolved using MERGE algorithm. +# +create table t1(f1 int, f2 int); +insert into t1 values(1,2),(1,3),(1,1),(2,3),(2,1),(2,2); +select * from t1; +create view v1 as select * from t1 order by f2; +select * from v1; +explain extended select * from v1; +select * from v1 order by f1; +explain extended select * from v1 order by f1; +drop view v1; +drop table t1; -SELECT * FROM t1; +# +# Bug#26209: queries with GROUP BY and ORDER BY using views +# -DROP VIEW v1, v2; +CREATE TABLE t1 ( + id int(11) NOT NULL PRIMARY KEY, + country varchar(32), + code int(11) default NULL +); +INSERT INTO t1 VALUES + (1,'ITALY',100),(2,'ITALY',200),(3,'FRANCE',100), (4,'ITALY',100); + +CREATE VIEW v1 AS SELECT * FROM t1; + +SELECT code, COUNT(DISTINCT country) FROM t1 GROUP BY code ORDER BY MAX(id); +SELECT code, COUNT(DISTINCT country) FROM v1 GROUP BY code ORDER BY MAX(id); + +DROP VIEW v1; DROP TABLE t1; +--echo End of 5.0 tests. + # # Bug#21370 View renaming lacks tablename_to_filename encoding # |