diff options
28 files changed, 788 insertions, 243 deletions
diff --git a/mysql-test/include/mix1.inc b/mysql-test/include/mix1.inc index 3919e4918c8..2b876d10cf2 100644 --- a/mysql-test/include/mix1.inc +++ b/mysql-test/include/mix1.inc @@ -197,6 +197,31 @@ INSERT INTO t1 (c1) VALUES ('1b') ON DUPLICATE KEY UPDATE cnt=cnt+1; SELECT * FROM t1; DROP TABLE t1; +# +# Bug #28272: EXPLAIN for SELECT from an empty InnoDB table +# + +CREATE TABLE t1 ( + a1 decimal(10,0) DEFAULT NULL, + a2 blob, + a3 time DEFAULT NULL, + a4 blob, + a5 char(175) DEFAULT NULL, + a6 timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', + a7 tinyblob, + INDEX idx (a6,a7(239),a5) +) ENGINE=InnoDB; + +EXPLAIN SELECT a4 FROM t1 WHERE +a6=NULL AND +a4='UNcT5pIde4I6c2SheTo4gt92OV1jgJCVkXmzyf325R1DwLURkbYHwhydANIZMbKTgdcR5xS'; + +EXPLAIN SELECT t1.a4 FROM t1, t1 t WHERE +t.a6=t.a6 AND t1.a6=NULL AND +t1.a4='UNcT5pIde4I6c2SheTo4gt92OV1jgJCVkXmzyf325R1DwLURkbYHwhydANIZMbKTgdcR5xS'; + +DROP TABLE t1; + --echo End of 4.1 tests diff --git a/mysql-test/r/bigint.result b/mysql-test/r/bigint.result index a70c6b3df7d..8d831ba800b 100644 --- a/mysql-test/r/bigint.result +++ b/mysql-test/r/bigint.result @@ -352,6 +352,16 @@ select c1 mod 50 as result from t1; result 6 drop table t1; +select cast(19999999999999999999 as signed); +cast(19999999999999999999 as signed) +9223372036854775807 +Warnings: +Error 1292 Truncated incorrect DECIMAL value: '' +select cast(-19999999999999999999 as signed); +cast(-19999999999999999999 as signed) +-9223372036854775808 +Warnings: +Error 1292 Truncated incorrect DECIMAL value: '' create table t1 select -9223372036854775808 bi; describe t1; Field Type Null Key Default Extra diff --git a/mysql-test/r/compress.result b/mysql-test/r/compress.result index 2eced78b07a..e46b56f4215 100644 --- a/mysql-test/r/compress.result +++ b/mysql-test/r/compress.result @@ -3,7 +3,7 @@ Variable_name Value Compression ON select * from information_schema.session_status where variable_name= 'COMPRESSION'; VARIABLE_NAME VARIABLE_VALUE -COMPRESSION 1.0000000 +COMPRESSION ON drop table if exists t1,t2,t3,t4; CREATE TABLE t1 ( Period smallint(4) unsigned zerofill DEFAULT '0000' NOT NULL, diff --git a/mysql-test/r/func_gconcat.result b/mysql-test/r/func_gconcat.result index 4c7baac051a..bb09217d9d7 100644 --- a/mysql-test/r/func_gconcat.result +++ b/mysql-test/r/func_gconcat.result @@ -743,4 +743,30 @@ SELECT GROUP_CONCAT(DISTINCT UCASE(b)) FROM t1; GROUP_CONCAT(DISTINCT UCASE(b)) ONE.1,TWO.2,ONE.3 DROP TABLE t1; +CREATE TABLE t1( a VARCHAR( 10 ), b INT ); +INSERT INTO t1 VALUES ( repeat( 'a', 10 ), 1), +( repeat( 'b', 10 ), 2); +SET group_concat_max_len = 20; +SELECT GROUP_CONCAT( a ) FROM t1; +GROUP_CONCAT( a ) +aaaaaaaaaa,bbbbbbbbb +Warnings: +Warning 1260 1 line(s) were cut by GROUP_CONCAT() +SELECT GROUP_CONCAT( DISTINCT a ) FROM t1; +GROUP_CONCAT( DISTINCT a ) +aaaaaaaaaa,bbbbbbbbb +Warnings: +Warning 1260 1 line(s) were cut by GROUP_CONCAT() +SELECT GROUP_CONCAT( a ORDER BY b ) FROM t1; +GROUP_CONCAT( a ORDER BY b ) +aaaaaaaaaa,bbbbbbbbb +Warnings: +Warning 1260 1 line(s) were cut by GROUP_CONCAT() +SELECT GROUP_CONCAT( DISTINCT a ORDER BY b ) FROM t1; +GROUP_CONCAT( DISTINCT a ORDER BY b ) +aaaaaaaaaa,bbbbbbbbb +Warnings: +Warning 1260 1 line(s) were cut by GROUP_CONCAT() +SET group_concat_max_len = DEFAULT; +DROP TABLE t1; End of 5.0 tests diff --git a/mysql-test/r/func_group.result b/mysql-test/r/func_group.result index 0e6cae2f103..1f640dec0a4 100644 --- a/mysql-test/r/func_group.result +++ b/mysql-test/r/func_group.result @@ -1321,4 +1321,51 @@ SELECT a,AVG(DISTINCT b) AS average FROM t1 GROUP BY a HAVING average > 50; a average 1 32768.5000 DROP TABLE t1; +CREATE TABLE t1 ( a INT, b INT, KEY(a) ); +INSERT INTO t1 VALUES (NULL, 1), (NULL, 2); +EXPLAIN SELECT MIN(a), MIN(b) FROM t1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 +SELECT MIN(a), MIN(b) FROM t1; +MIN(a) MIN(b) +NULL 1 +CREATE TABLE t2( a INT, b INT, c INT, KEY(a, b) ); +INSERT INTO t2 ( a, b, c ) VALUES ( 1, NULL, 2 ), ( 1, 3, 4 ), ( 1, 4, 4 ); +EXPLAIN SELECT MIN(b), MIN(c) FROM t2 WHERE a = 1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ref a a 5 const 2 Using where +SELECT MIN(b), MIN(c) FROM t2 WHERE a = 1; +MIN(b) MIN(c) +3 2 +CREATE TABLE t3 (a INT, b INT, c int, KEY(a, b)); +INSERT INTO t3 VALUES (1, NULL, 1), (2, NULL, 2), (2, NULL, 2), (3, NULL, 3); +EXPLAIN SELECT MIN(a), MIN(b) FROM t3 where a = 2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +SELECT MIN(a), MIN(b) FROM t3 where a = 2; +MIN(a) MIN(b) +2 NULL +CREATE TABLE t4 (a INT, b INT, c int, KEY(a, b)); +INSERT INTO t4 VALUES (1, 1, 1), (2, NULL, 2), (2, NULL, 2), (3, 1, 3); +EXPLAIN SELECT MIN(a), MIN(b) FROM t4 where a = 2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +SELECT MIN(a), MIN(b) FROM t4 where a = 2; +MIN(a) MIN(b) +2 NULL +SELECT MIN(b), min(c) FROM t4 where a = 2; +MIN(b) min(c) +NULL 2 +CREATE TABLE t5( a INT, b INT, KEY( a, b) ); +INSERT INTO t5 VALUES( 1, 1 ), ( 1, 2 ); +EXPLAIN SELECT MIN(a), MIN(b) FROM t5 WHERE a = 1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +SELECT MIN(a), MIN(b) FROM t5 WHERE a = 1; +MIN(a) MIN(b) +1 1 +SELECT MIN(a), MIN(b) FROM t5 WHERE a = 1 and b > 1; +MIN(a) MIN(b) +1 2 +DROP TABLE t1, t2, t3, t4, t5; End of 5.0 tests diff --git a/mysql-test/r/information_schema.result b/mysql-test/r/information_schema.result index fc7a45445a6..709246edcf1 100644 --- a/mysql-test/r/information_schema.result +++ b/mysql-test/r/information_schema.result @@ -761,7 +761,6 @@ information_schema COLUMNS COLUMN_DEFAULT information_schema COLUMNS COLUMN_TYPE information_schema EVENTS EVENT_DEFINITION information_schema EVENTS SQL_MODE -information_schema GLOBAL_VARIABLES VARIABLE_VALUE information_schema PARTITIONS PARTITION_EXPRESSION information_schema PARTITIONS SUBPARTITION_EXPRESSION information_schema PARTITIONS PARTITION_DESCRIPTION @@ -769,7 +768,6 @@ information_schema PLUGINS PLUGIN_DESCRIPTION information_schema PROCESSLIST INFO information_schema ROUTINES ROUTINE_DEFINITION information_schema ROUTINES SQL_MODE -information_schema SESSION_VARIABLES VARIABLE_VALUE information_schema TRIGGERS ACTION_CONDITION information_schema TRIGGERS ACTION_STATEMENT information_schema TRIGGERS SQL_MODE diff --git a/mysql-test/r/innodb_mysql.result b/mysql-test/r/innodb_mysql.result index dc9564b21a2..fb3bac44c61 100644 --- a/mysql-test/r/innodb_mysql.result +++ b/mysql-test/r/innodb_mysql.result @@ -145,6 +145,27 @@ SELECT * FROM t1; c1 cnt 1a 2 DROP TABLE t1; +CREATE TABLE t1 ( +a1 decimal(10,0) DEFAULT NULL, +a2 blob, +a3 time DEFAULT NULL, +a4 blob, +a5 char(175) DEFAULT NULL, +a6 timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', +a7 tinyblob, +INDEX idx (a6,a7(239),a5) +) ENGINE=InnoDB; +EXPLAIN SELECT a4 FROM t1 WHERE +a6=NULL AND +a4='UNcT5pIde4I6c2SheTo4gt92OV1jgJCVkXmzyf325R1DwLURkbYHwhydANIZMbKTgdcR5xS'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +EXPLAIN SELECT t1.a4 FROM t1, t1 t WHERE +t.a6=t.a6 AND t1.a6=NULL AND +t1.a4='UNcT5pIde4I6c2SheTo4gt92OV1jgJCVkXmzyf325R1DwLURkbYHwhydANIZMbKTgdcR5xS'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +DROP TABLE t1; End of 4.1 tests create table t1m (a int) engine = MEMORY; create table t1i (a int); diff --git a/mysql-test/r/openssl_1.result b/mysql-test/r/openssl_1.result index c01a9643e8a..5e29e30732f 100644 --- a/mysql-test/r/openssl_1.result +++ b/mysql-test/r/openssl_1.result @@ -73,8 +73,8 @@ variable_name LIKE 'SSL_CALLBACK_CACHE_HITS'; END$$ SELECT variable_name, variable_value FROM thread_status; variable_name variable_value -SSL_ACCEPTS 0.0000000 -SSL_CALLBACK_CACHE_HITS 0.0000000 +SSL_ACCEPTS 0 +SSL_CALLBACK_CACHE_HITS 0 DROP TABLE thread_status; SET GLOBAL event_scheduler=0; End of 5.1 tests diff --git a/mysql-test/r/ps.result b/mysql-test/r/ps.result index b811a27203c..10d4015925f 100644 --- a/mysql-test/r/ps.result +++ b/mysql-test/r/ps.result @@ -1190,6 +1190,214 @@ EXECUTE b12651; DROP VIEW b12651_V1; DROP TABLE b12651_T1, b12651_T2; DEALLOCATE PREPARE b12651; +DROP TABLE IF EXISTS t1, t2; +CREATE TABLE t1 (i INT); +PREPARE st_19182 +FROM "CREATE TABLE t2 (i INT, j INT, KEY (i), KEY(j)) SELECT i FROM t1"; +EXECUTE st_19182; +DESC t2; +Field Type Null Key Default Extra +j int(11) YES MUL NULL +i int(11) YES MUL NULL +DROP TABLE t2; +EXECUTE st_19182; +DESC t2; +Field Type Null Key Default Extra +j int(11) YES MUL NULL +i int(11) YES MUL NULL +DEALLOCATE PREPARE st_19182; +DROP TABLE t2, t1; +drop database if exists mysqltest; +drop table if exists t1, t2; +create database mysqltest character set utf8; +prepare stmt1 from "create table mysqltest.t1 (c char(10))"; +prepare stmt2 from "create table mysqltest.t2 select 'test'"; +execute stmt1; +execute stmt2; +show create table mysqltest.t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c` char(10) default NULL +) ENGINE=MyISAM DEFAULT CHARSET=utf8 +show create table mysqltest.t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `test` varchar(4) character set latin1 NOT NULL default '' +) ENGINE=MyISAM DEFAULT CHARSET=utf8 +drop table mysqltest.t1; +drop table mysqltest.t2; +alter database mysqltest character set latin1; +execute stmt1; +execute stmt2; +show create table mysqltest.t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c` char(10) character set utf8 default NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +show create table mysqltest.t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `test` varchar(4) NOT NULL default '' +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop database mysqltest; +deallocate prepare stmt1; +deallocate prepare stmt2; +execute stmt; +show create table t1; +drop table t1; +execute stmt; +show create table t1; +drop table t1; +deallocate prepare stmt; +CREATE TABLE t1(a int); +INSERT INTO t1 VALUES (2), (3), (1); +PREPARE st1 FROM +'(SELECT a FROM t1) UNION (SELECT a+10 FROM t1) ORDER BY RAND()*0+a'; +EXECUTE st1; +a +1 +2 +3 +11 +12 +13 +EXECUTE st1; +a +1 +2 +3 +11 +12 +13 +DEALLOCATE PREPARE st1; +DROP TABLE t1; +End of 4.1 tests. +create table t1 (a varchar(20)); +insert into t1 values ('foo'); +prepare stmt FROM 'SELECT char_length (a) FROM t1'; +ERROR 42000: FUNCTION test.char_length does not exist +drop table t1; +create table t1 (a char(3) not null, b char(3) not null, +c char(3) not null, primary key (a, b, c)); +create table t2 like t1; +prepare stmt from +"select t1.a from (t1 left outer join t2 on t2.a=1 and t1.b=t2.b) + where t1.a=1"; +execute stmt; +a +execute stmt; +a +execute stmt; +a +prepare stmt from +"select t1.a, t1.b, t1.c, t2.a, t2.b, t2.c from +(t1 left outer join t2 on t2.a=? and t1.b=t2.b) +left outer join t2 t3 on t3.a=? where t1.a=?"; +set @a:=1, @b:=1, @c:=1; +execute stmt using @a, @b, @c; +a b c a b c +execute stmt using @a, @b, @c; +a b c a b c +execute stmt using @a, @b, @c; +a b c a b c +deallocate prepare stmt; +drop table t1,t2; +SET @aux= "SELECT COUNT(*) + FROM INFORMATION_SCHEMA.COLUMNS A, + INFORMATION_SCHEMA.COLUMNS B + WHERE A.TABLE_SCHEMA = B.TABLE_SCHEMA + AND A.TABLE_NAME = B.TABLE_NAME + AND A.COLUMN_NAME = B.COLUMN_NAME AND + A.TABLE_NAME = 'user'"; +prepare my_stmt from @aux; +execute my_stmt; +COUNT(*) +37 +execute my_stmt; +COUNT(*) +37 +execute my_stmt; +COUNT(*) +37 +deallocate prepare my_stmt; +drop procedure if exists p1| +drop table if exists t1| +create table t1 (id int)| +insert into t1 values(1)| +create procedure p1(a int, b int) +begin +declare c int; +select max(id)+1 into c from t1; +insert into t1 select a+b; +insert into t1 select a-b; +insert into t1 select a-c; +end| +set @a= 3, @b= 4| +prepare stmt from "call p1(?, ?)"| +execute stmt using @a, @b| +execute stmt using @a, @b| +select * from t1| +id +1 +7 +-1 +1 +7 +-1 +-5 +deallocate prepare stmt| +drop procedure p1| +drop table t1| +create table t1 (a int); +insert into t1 (a) values (1), (2), (3), (4), (5), (6), (7), (8), (9), (10); +prepare stmt from "select * from t1 limit ?, ?"; +set @offset=0, @limit=1; +execute stmt using @offset, @limit; +a +1 +select * from t1 limit 0, 1; +a +1 +set @offset=3, @limit=2; +execute stmt using @offset, @limit; +a +4 +5 +select * from t1 limit 3, 2; +a +4 +5 +prepare stmt from "select * from t1 limit ?"; +execute stmt using @limit; +a +1 +2 +prepare stmt from "select * from t1 where a in (select a from t1 limit ?)"; +ERROR 42000: This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery' +prepare stmt from "select * from t1 union all select * from t1 limit ?, ?"; +set @offset=9; +set @limit=2; +execute stmt using @offset, @limit; +a +10 +1 +prepare stmt from "(select * from t1 limit ?, ?) union all + (select * from t1 limit ?, ?) order by a limit ?"; +execute stmt using @offset, @limit, @offset, @limit, @limit; +a +10 +10 +drop table t1; +deallocate prepare stmt; +CREATE TABLE b12651_T1(a int) ENGINE=MYISAM; +CREATE TABLE b12651_T2(b int) ENGINE=MYISAM; +CREATE VIEW b12651_V1 as SELECT b FROM b12651_T2; +PREPARE b12651 FROM 'SELECT 1 FROM b12651_T1 WHERE a IN (SELECT b FROM b12651_V1)'; +EXECUTE b12651; +1 +DROP VIEW b12651_V1; +DROP TABLE b12651_T1, b12651_T2; +DEALLOCATE PREPARE b12651; create table t1 (id int); prepare ins_call from "insert into t1 (id) values (1)"; execute ins_call; diff --git a/mysql-test/r/rpl_packet.result b/mysql-test/r/rpl_packet.result index 8f4a16341b6..981c234d380 100644 --- a/mysql-test/r/rpl_packet.result +++ b/mysql-test/r/rpl_packet.result @@ -19,7 +19,7 @@ Variable_name Value Slave_running ON select * from information_schema.session_status where variable_name= 'SLAVE_RUNNING'; VARIABLE_NAME VARIABLE_VALUE -SLAVE_RUNNING 1.0000000 +SLAVE_RUNNING ON drop database DB_NAME_OF_MAX_LENGTH_AKA_NAME_LEN_64_BYTES_____________________; SET @@global.max_allowed_packet=4096; SET @@global.net_buffer_length=4096; diff --git a/mysql-test/r/status.result b/mysql-test/r/status.result index e9891e4d495..b6dcbc251d7 100644 --- a/mysql-test/r/status.result +++ b/mysql-test/r/status.result @@ -5,8 +5,8 @@ Table_locks_immediate 0 Table_locks_waited 0 select * from information_schema.session_status where variable_name like 'Table_lock%'; VARIABLE_NAME VARIABLE_VALUE -TABLE_LOCKS_IMMEDIATE 0.0000000 -TABLE_LOCKS_WAITED 0.0000000 +TABLE_LOCKS_IMMEDIATE 0 +TABLE_LOCKS_WAITED 0 SET SQL_LOG_BIN=0; drop table if exists t1; create table t1(n int) engine=myisam; @@ -22,8 +22,8 @@ Table_locks_immediate 3 Table_locks_waited 1 select * from information_schema.session_status where variable_name like 'Table_lock%'; VARIABLE_NAME VARIABLE_VALUE -TABLE_LOCKS_IMMEDIATE 3.0000000 -TABLE_LOCKS_WAITED 1.0000000 +TABLE_LOCKS_IMMEDIATE 3 +TABLE_LOCKS_WAITED 1 drop table t1; select 1; 1 @@ -63,7 +63,7 @@ Variable_name Value Max_used_connections 1 SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME LIKE 'max_used_connections'; VARIABLE_NAME VARIABLE_VALUE -MAX_USED_CONNECTIONS 1.0000000 +MAX_USED_CONNECTIONS 1 SET @save_thread_cache_size=@@thread_cache_size; SET GLOBAL thread_cache_size=3; SHOW STATUS LIKE 'max_used_connections'; @@ -71,26 +71,26 @@ Variable_name Value Max_used_connections 3 SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME LIKE 'max_used_connections'; VARIABLE_NAME VARIABLE_VALUE -MAX_USED_CONNECTIONS 3.0000000 +MAX_USED_CONNECTIONS 3 FLUSH STATUS; SHOW STATUS LIKE 'max_used_connections'; Variable_name Value Max_used_connections 2 SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME LIKE 'max_used_connections'; VARIABLE_NAME VARIABLE_VALUE -MAX_USED_CONNECTIONS 2.0000000 +MAX_USED_CONNECTIONS 2 SHOW STATUS LIKE 'max_used_connections'; Variable_name Value Max_used_connections 3 SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME LIKE 'max_used_connections'; VARIABLE_NAME VARIABLE_VALUE -MAX_USED_CONNECTIONS 3.0000000 +MAX_USED_CONNECTIONS 3 SHOW STATUS LIKE 'max_used_connections'; Variable_name Value Max_used_connections 4 SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME LIKE 'max_used_connections'; VARIABLE_NAME VARIABLE_VALUE -MAX_USED_CONNECTIONS 4.0000000 +MAX_USED_CONNECTIONS 4 SET GLOBAL thread_cache_size=@save_thread_cache_size; show status like 'com_show_status'; Variable_name Value diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index e90f43b9870..3dc1795a65a 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -4041,6 +4041,36 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ref a a 5 const 1 Using where; Using index 2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using temporary; Using filesort DROP TABLE t1; +CREATE TABLE t1 (id int NOT NULL, st CHAR(2), INDEX idx(id)); +INSERT INTO t1 VALUES +(3,'FL'), (2,'GA'), (4,'FL'), (1,'GA'), (5,'NY'), (7,'FL'), (6,'NY'); +CREATE TABLE t2 (id int NOT NULL, INDEX idx(id)); +INSERT INTO t2 VALUES (7), (5), (1), (3); +SELECT id, st FROM t1 +WHERE st IN ('GA','FL') AND EXISTS(SELECT 1 FROM t2 WHERE t2.id=t1.id); +id st +3 FL +1 GA +7 FL +SELECT id, st FROM t1 +WHERE st IN ('GA','FL') AND EXISTS(SELECT 1 FROM t2 WHERE t2.id=t1.id) +GROUP BY id; +id st +1 GA +3 FL +7 FL +SELECT id, st FROM t1 +WHERE st IN ('GA','FL') AND NOT EXISTS(SELECT 1 FROM t2 WHERE t2.id=t1.id); +id st +2 GA +4 FL +SELECT id, st FROM t1 +WHERE st IN ('GA','FL') AND NOT EXISTS(SELECT 1 FROM t2 WHERE t2.id=t1.id) +GROUP BY id; +id st +2 GA +4 FL +DROP TABLE t1,t2; End of 5.0 tests. CREATE TABLE t1 (a int, b int); INSERT INTO t1 VALUES (2,22),(1,11),(2,22); diff --git a/mysql-test/r/type_datetime.result b/mysql-test/r/type_datetime.result index e1be32aa7eb..9a6dd44faa1 100644 --- a/mysql-test/r/type_datetime.result +++ b/mysql-test/r/type_datetime.result @@ -346,6 +346,36 @@ call test27759(); a b a_then_b b_then_a c_then_a 2007-04-10 2007-04-11 2007-04-10 2007-04-10 2004-04-09 00:00:00 drop procedure test27759; +create table t1 (f1 date); +insert into t1 values (curdate()); +select left(f1,10) = curdate() from t1; +left(f1,10) = curdate() +1 +drop table t1; +create table t1(f1 date); +insert into t1 values('01-01-01'),('02-02-02'),('01-01-01'),('02-02-02'); +set @bug28261=''; +select if(@bug28261 = f1, '', @bug28261:= f1) from t1; +if(@bug28261 = f1, '', @bug28261:= f1) +2001-01-01 +2002-02-02 +2001-01-01 +2002-02-02 +Warnings: +Warning 1292 Incorrect date value: '' for column 'f1' at row 1 +select if(@bug28261 = f1, '', @bug28261:= f1) from t1; +if(@bug28261 = f1, '', @bug28261:= f1) +2001-01-01 +2002-02-02 +2001-01-01 +2002-02-02 +select if(@bug28261 = f1, '', @bug28261:= f1) from t1; +if(@bug28261 = f1, '', @bug28261:= f1) +2001-01-01 +2002-02-02 +2001-01-01 +2002-02-02 +drop table t1; set @org_mode=@@sql_mode; create table t1 (da date default '1962-03-03 23:33:34', dt datetime default '1962-03-03'); Warnings: diff --git a/mysql-test/t/bigint.test b/mysql-test/t/bigint.test index c2c6f895545..52f34e58e97 100644 --- a/mysql-test/t/bigint.test +++ b/mysql-test/t/bigint.test @@ -288,6 +288,13 @@ insert into t1 values (10000002383263201056); select c1 mod 50 as result from t1; drop table t1; +# +# Bug #8663 cant use bgint unsigned as input to cast +# + +select cast(19999999999999999999 as signed); +select cast(-19999999999999999999 as signed); + # Bug #28005 Partitions: can't use -9223372036854775808 create table t1 select -9223372036854775808 bi; describe t1; diff --git a/mysql-test/t/func_gconcat.test b/mysql-test/t/func_gconcat.test index 431d582be50..4e965d7e8c9 100644 --- a/mysql-test/t/func_gconcat.test +++ b/mysql-test/t/func_gconcat.test @@ -507,4 +507,18 @@ SELECT GROUP_CONCAT(DISTINCT UCASE(a)) FROM t1; SELECT GROUP_CONCAT(DISTINCT UCASE(b)) FROM t1; DROP TABLE t1; +# +# Bug #28273: GROUP_CONCAT and ORDER BY: No warning when result gets truncated. +# +CREATE TABLE t1( a VARCHAR( 10 ), b INT ); +INSERT INTO t1 VALUES ( repeat( 'a', 10 ), 1), + ( repeat( 'b', 10 ), 2); +SET group_concat_max_len = 20; +SELECT GROUP_CONCAT( a ) FROM t1; +SELECT GROUP_CONCAT( DISTINCT a ) FROM t1; +SELECT GROUP_CONCAT( a ORDER BY b ) FROM t1; +SELECT GROUP_CONCAT( DISTINCT a ORDER BY b ) FROM t1; +SET group_concat_max_len = DEFAULT; +DROP TABLE t1; + --echo End of 5.0 tests diff --git a/mysql-test/t/func_group.test b/mysql-test/t/func_group.test index 778bfc9528f..d1b4919c83e 100644 --- a/mysql-test/t/func_group.test +++ b/mysql-test/t/func_group.test @@ -834,4 +834,38 @@ SELECT a,AVG(DISTINCT b) AS average FROM t1 GROUP BY a HAVING average > 50; DROP TABLE t1; +# +# Bug #27573: MIN() on an indexed column which is always NULL sets _other_ +# results to NULL +# +CREATE TABLE t1 ( a INT, b INT, KEY(a) ); +INSERT INTO t1 VALUES (NULL, 1), (NULL, 2); +EXPLAIN SELECT MIN(a), MIN(b) FROM t1; +SELECT MIN(a), MIN(b) FROM t1; + +CREATE TABLE t2( a INT, b INT, c INT, KEY(a, b) ); +INSERT INTO t2 ( a, b, c ) VALUES ( 1, NULL, 2 ), ( 1, 3, 4 ), ( 1, 4, 4 ); +EXPLAIN SELECT MIN(b), MIN(c) FROM t2 WHERE a = 1; +SELECT MIN(b), MIN(c) FROM t2 WHERE a = 1; + +CREATE TABLE t3 (a INT, b INT, c int, KEY(a, b)); +INSERT INTO t3 VALUES (1, NULL, 1), (2, NULL, 2), (2, NULL, 2), (3, NULL, 3); +EXPLAIN SELECT MIN(a), MIN(b) FROM t3 where a = 2; +SELECT MIN(a), MIN(b) FROM t3 where a = 2; + +CREATE TABLE t4 (a INT, b INT, c int, KEY(a, b)); +INSERT INTO t4 VALUES (1, 1, 1), (2, NULL, 2), (2, NULL, 2), (3, 1, 3); +EXPLAIN SELECT MIN(a), MIN(b) FROM t4 where a = 2; +SELECT MIN(a), MIN(b) FROM t4 where a = 2; +SELECT MIN(b), min(c) FROM t4 where a = 2; + +CREATE TABLE t5( a INT, b INT, KEY( a, b) ); +INSERT INTO t5 VALUES( 1, 1 ), ( 1, 2 ); +EXPLAIN SELECT MIN(a), MIN(b) FROM t5 WHERE a = 1; +SELECT MIN(a), MIN(b) FROM t5 WHERE a = 1; +SELECT MIN(a), MIN(b) FROM t5 WHERE a = 1 and b > 1; + +DROP TABLE t1, t2, t3, t4, t5; + +### --echo End of 5.0 tests diff --git a/mysql-test/t/ps.test b/mysql-test/t/ps.test index e21c3f793ad..22eb51c6327 100644 --- a/mysql-test/t/ps.test +++ b/mysql-test/t/ps.test @@ -1120,6 +1120,114 @@ DROP TABLE t1; --echo End of 4.1 tests. +# +# Bug#19182: CREATE TABLE bar (m INT) SELECT n FROM foo; doesn't work +# from stored procedure. +# +# The cause of a bug was that cached LEX::create_list was modified, +# and then together with LEX::key_list was reset. +# +--disable_warnings +DROP TABLE IF EXISTS t1, t2; +--enable_warnings + +CREATE TABLE t1 (i INT); + +PREPARE st_19182 +FROM "CREATE TABLE t2 (i INT, j INT, KEY (i), KEY(j)) SELECT i FROM t1"; + +EXECUTE st_19182; +DESC t2; + +DROP TABLE t2; + +# Check that on second execution we don't loose 'j' column and the keys +# on 'i' and 'j' columns. +EXECUTE st_19182; +DESC t2; + +DEALLOCATE PREPARE st_19182; +DROP TABLE t2, t1; + +# +# Bug #22060 "ALTER TABLE x AUTO_INCREMENT=y in SP crashes server" +# +# Code which implemented CREATE/ALTER TABLE and CREATE DATABASE +# statement modified HA_CREATE_INFO structure in LEX, making these +# statements PS/SP-unsafe (their re-execution might have resulted +# in incorrect results). +# +--disable_warnings +drop database if exists mysqltest; +drop table if exists t1, t2; +--enable_warnings +# CREATE TABLE and CREATE TABLE ... SELECT +create database mysqltest character set utf8; +prepare stmt1 from "create table mysqltest.t1 (c char(10))"; +prepare stmt2 from "create table mysqltest.t2 select 'test'"; +execute stmt1; +execute stmt2; +show create table mysqltest.t1; +show create table mysqltest.t2; +drop table mysqltest.t1; +drop table mysqltest.t2; +alter database mysqltest character set latin1; +execute stmt1; +execute stmt2; +show create table mysqltest.t1; +show create table mysqltest.t2; +drop database mysqltest; +deallocate prepare stmt1; +deallocate prepare stmt2; +# +# CREATE TABLE with DATA DIRECTORY option +# +# Protect ourselves from data left in tmp/ by a previos possibly failed +# test +--system rm -f $MYSQLTEST_VARDIR/tmp/t1.* +--disable_warnings +--disable_query_log +eval prepare stmt from "create table t1 (c char(10)) data directory='$MYSQLTEST_VARDIR/tmp'"; +--enable_query_log +execute stmt; +# +# DATA DIRECTORY option does not always work: if the operating +# system does not support symlinks, have_symlinks option is automatically +# disabled. +# In this case DATA DIRECTORY is silently ignored when +# creating a table, and is not output by SHOW CREATE TABLE. +# +--disable_result_log +show create table t1; +--enable_result_log +drop table t1; +execute stmt; +--disable_result_log +show create table t1; +--enable_result_log +--enable_warnings +drop table t1; +deallocate prepare stmt; +# + +# +# Bug #27937: crash on the second execution for prepared statement +# from UNION with ORDER BY an expression containing RAND() +# + +CREATE TABLE t1(a int); +INSERT INTO t1 VALUES (2), (3), (1); + +PREPARE st1 FROM + '(SELECT a FROM t1) UNION (SELECT a+10 FROM t1) ORDER BY RAND()*0+a'; + +EXECUTE st1; +EXECUTE st1; + +DEALLOCATE PREPARE st1; +DROP TABLE t1; + +--echo End of 4.1 tests. ############################# 5.0 tests start ################################ # diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test index aaeb998d0c7..12e2e4f1f25 100644 --- a/mysql-test/t/subselect.test +++ b/mysql-test/t/subselect.test @@ -2882,6 +2882,30 @@ INSERT INTO t1 VALUES (1,1),(2,1); EXPLAIN SELECT 1 FROM t1 WHERE a = (SELECT COUNT(*) FROM t1 GROUP BY b); DROP TABLE t1; +# +# Bug #28377: grouping query with a correlated subquery in WHERE condition +# + +CREATE TABLE t1 (id int NOT NULL, st CHAR(2), INDEX idx(id)); +INSERT INTO t1 VALUES + (3,'FL'), (2,'GA'), (4,'FL'), (1,'GA'), (5,'NY'), (7,'FL'), (6,'NY'); +CREATE TABLE t2 (id int NOT NULL, INDEX idx(id)); +INSERT INTO t2 VALUES (7), (5), (1), (3); + +SELECT id, st FROM t1 + WHERE st IN ('GA','FL') AND EXISTS(SELECT 1 FROM t2 WHERE t2.id=t1.id); +SELECT id, st FROM t1 + WHERE st IN ('GA','FL') AND EXISTS(SELECT 1 FROM t2 WHERE t2.id=t1.id) + GROUP BY id; + +SELECT id, st FROM t1 + WHERE st IN ('GA','FL') AND NOT EXISTS(SELECT 1 FROM t2 WHERE t2.id=t1.id); +SELECT id, st FROM t1 + WHERE st IN ('GA','FL') AND NOT EXISTS(SELECT 1 FROM t2 WHERE t2.id=t1.id) + GROUP BY id; + +DROP TABLE t1,t2; + --echo End of 5.0 tests. # diff --git a/mysql-test/t/type_datetime.test b/mysql-test/t/type_datetime.test index 7689f16baa4..23804633666 100644 --- a/mysql-test/t/type_datetime.test +++ b/mysql-test/t/type_datetime.test @@ -227,6 +227,28 @@ call test27759(); drop procedure test27759; # +# Bug#28208: Wrong result of a non-const STRING function with a const +# DATETIME function. +# +create table t1 (f1 date); +insert into t1 values (curdate()); +select left(f1,10) = curdate() from t1; +drop table t1; + +# +# Bug#28261: Wrong DATETIME comparison result when the GET_USER_VAR function +# is involved. +# +create table t1(f1 date); +insert into t1 values('01-01-01'),('02-02-02'),('01-01-01'),('02-02-02'); +set @bug28261=''; +select if(@bug28261 = f1, '', @bug28261:= f1) from t1; +select if(@bug28261 = f1, '', @bug28261:= f1) from t1; +select if(@bug28261 = f1, '', @bug28261:= f1) from t1; +drop table t1; + + +# # Test of storing datetime into date fields # diff --git a/sql/item.cc b/sql/item.cc index 3800ebdcbce..24958f64f2a 100644 --- a/sql/item.cc +++ b/sql/item.cc @@ -3558,7 +3558,8 @@ Item_field::fix_outer_field(THD *thd, Field **from_field, Item **reference) prev_subselect_item->const_item_cache= 0; set_field(*from_field); if (!last_checked_context->select_lex->having_fix_field && - select->group_list.elements) + select->group_list.elements && + (place == SELECT_LIST || place == IN_HAVING)) { Item_outer_ref *rf; /* diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc index 2e6081ba5f8..4a18de8ce4f 100644 --- a/sql/item_cmpfunc.cc +++ b/sql/item_cmpfunc.cc @@ -690,7 +690,13 @@ Arg_comparator::can_compare_as_dates(Item *a, Item *b, ulonglong *const_value) if (cmp_type != CMP_DATE_DFLT) { - if (cmp_type != CMP_DATE_WITH_DATE && str_arg->const_item()) + /* + Do not cache GET_USER_VAR() function as its const_item() may return TRUE + for the current thread but it still may change during the execution. + */ + if (cmp_type != CMP_DATE_WITH_DATE && str_arg->const_item() && + (str_arg->type() != Item::FUNC_ITEM || + ((Item_func*)str_arg)->functype() != Item_func::GUSERVAR_FUNC)) { THD *thd= current_thd; ulonglong value; @@ -718,7 +724,7 @@ int Arg_comparator::set_cmp_func(Item_bool_func2 *owner_arg, Item_result type) { enum enum_date_cmp_type cmp_type; - ulonglong const_value; + ulonglong const_value= (ulonglong)-1; a= a1; b= a2; @@ -731,8 +737,7 @@ int Arg_comparator::set_cmp_func(Item_bool_func2 *owner_arg, a_cache= 0; b_cache= 0; - if (cmp_type != CMP_DATE_WITH_DATE && - ((*b)->const_item() || (*a)->const_item())) + if (const_value != (ulonglong)-1) { Item_cache_int *cache= new Item_cache_int(); /* Mark the cache as non-const to prevent re-caching. */ @@ -838,7 +843,12 @@ get_datetime_value(THD *thd, Item ***item_arg, Item **cache_arg, MYSQL_TYPE_DATE ? MYSQL_TIMESTAMP_DATE : MYSQL_TIMESTAMP_DATETIME; value= get_date_from_str(thd, str, t_type, warn_item->name, &error); } - if (item->const_item() && cache_arg) + /* + Do not cache GET_USER_VAR() function as its const_item() may return TRUE + for the current thread but it still may change during the execution. + */ + if (item->const_item() && cache_arg && (item->type() != Item::FUNC_ITEM || + ((Item_func*)item)->functype() != Item_func::GUSERVAR_FUNC)) { Item_cache_int *cache= new Item_cache_int(); /* Mark the cache as non-const to prevent re-caching. */ diff --git a/sql/item_sum.cc b/sql/item_sum.cc index 6215aacde64..f962d067e17 100644 --- a/sql/item_sum.cc +++ b/sql/item_sum.cc @@ -3360,6 +3360,10 @@ String* Item_func_group_concat::val_str(String* str) DBUG_ASSERT(fixed == 1); if (null_value) return 0; + if (!result.length() && tree) + /* Tree is used for sorting as in ORDER BY */ + tree_walk(tree, (tree_walk_action)&dump_leaf_key, (void*)this, + left_root_right); if (count_cut_values && !warning) { /* @@ -3371,11 +3375,6 @@ String* Item_func_group_concat::val_str(String* str) ER_CUT_VALUE_GROUP_CONCAT, ER(ER_CUT_VALUE_GROUP_CONCAT)); } - if (result.length()) - return &result; - if (tree) - tree_walk(tree, (tree_walk_action)&dump_leaf_key, (void*)this, - left_root_right); return &result; } diff --git a/sql/opt_sum.cc b/sql/opt_sum.cc index f9a06f3fb6e..c020b9ab53e 100644 --- a/sql/opt_sum.cc +++ b/sql/opt_sum.cc @@ -249,12 +249,68 @@ int opt_sum_query(TABLE_LIST *tables, List<Item> &all_fields,COND *conds) if (!ref.key_length) error= table->file->index_first(table->record[0]); - else - error= table->file->index_read(table->record[0],key_buff, - make_prev_keypart_map(ref.key_parts), - range_fl & NEAR_MIN ? - HA_READ_AFTER_KEY : - HA_READ_KEY_OR_NEXT); + else + { + /* + Use index to replace MIN/MAX functions with their values + according to the following rules: + + 1) Insert the minimum non-null values where the WHERE clause still + matches, or + 2) a NULL value if there are only NULL values for key_part_k. + 3) Fail, producing a row of nulls + + Implementation: Read the smallest value using the search key. If + the interval is open, read the next value after the search + key. If read fails, and we're looking for a MIN() value for a + nullable column, test if there is an exact match for the key. + */ + if (!(range_fl & NEAR_MIN)) + /* + Closed interval: Either The MIN argument is non-nullable, or + we have a >= predicate for the MIN argument. + */ + error= table->file->index_read(table->record[0], ref.key_buff, + ref.key_length, + HA_READ_KEY_OR_NEXT); + else + { + /* + Open interval: There are two cases: + 1) We have only MIN() and the argument column is nullable, or + 2) there is a > predicate on it, nullability is irrelevant. + We need to scan the next bigger record first. + */ + error= table->file->index_read(table->record[0], ref.key_buff, + ref.key_length, HA_READ_AFTER_KEY); + /* + If the found record is outside the group formed by the search + prefix, or there is no such record at all, check if all + records in that group have NULL in the MIN argument + column. If that is the case return that NULL. + + Check if case 1 from above holds. If it does, we should read + the skipped tuple. + */ + if (ref.key_buff[prefix_len] == 1 && + /* + Last keypart (i.e. the argument to MIN) is set to NULL by + find_key_for_maxmin only if all other keyparts are bound + to constants in a conjunction of equalities. Hence, we + can detect this by checking only if the last keypart is + NULL. + */ + (error == HA_ERR_KEY_NOT_FOUND || + key_cmp_if_same(table, ref.key_buff, ref.key, prefix_len))) + { + DBUG_ASSERT(item_field->field->real_maybe_null()); + error= table->file->index_read(table->record[0], ref.key_buff, + ref.key_length, + HA_READ_KEY_EXACT); + } + } + } + /* Verify that the read tuple indeed matches the search key */ if (!error && reckey_in_range(0, &ref, item_field->field, conds, range_fl, prefix_len)) error= HA_ERR_KEY_NOT_FOUND; @@ -784,16 +840,26 @@ static bool find_key_for_maxmin(bool max_fl, TABLE_REF *ref, if (!max_fl && key_part_used == key_part_to_use && part->null_bit) { /* - SELECT MIN(key_part2) FROM t1 WHERE key_part1=const - If key_part2 may be NULL, then we want to find the first row - that is not null + The query is on this form: + + SELECT MIN(key_part_k) + FROM t1 + WHERE key_part_1 = const and ... and key_part_k-1 = const + + If key_part_k is nullable, we want to find the first matching row + where key_part_k is not null. The key buffer is now {const, ..., + NULL}. This will be passed to the handler along with a flag + indicating open interval. If a tuple is read that does not match + these search criteria, an attempt will be made to read an exact + match for the key buffer. */ + /* Set the first byte of key_part_k to 1, that means NULL */ ref->key_buff[ref->key_length]= 1; ref->key_length+= part->store_length; ref->key_parts++; DBUG_ASSERT(ref->key_parts == jdx+1); *range_fl&= ~NO_MIN_RANGE; - *range_fl|= NEAR_MIN; // > NULL + *range_fl|= NEAR_MIN; // Open interval } /* The following test is false when the key in the key tree is diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc index 0d2621e9b9c..65e5f8035e4 100644 --- a/sql/sql_lex.cc +++ b/sql/sql_lex.cc @@ -1784,7 +1784,7 @@ void Query_tables_list::destroy_query_tables_list() st_lex::st_lex() :result(0), yacc_yyss(0), yacc_yyvs(0), - sql_command(SQLCOM_END) + sql_command(SQLCOM_END), option_type(OPT_DEFAULT) { /* Check that plugins_static_buffer is declared immediately after plugins */ compile_time_assert((&plugins + 1) == (DYNAMIC_ARRAY*)plugins_static_buffer); diff --git a/sql/sql_select.cc b/sql/sql_select.cc index ea78e126d9c..ab0437dd7b5 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -988,6 +988,12 @@ JOIN::optimize() } } + if (conds &&!outer_join && const_table_map != found_const_table_map && + (select_options & SELECT_DESCRIBE) && + select_lex->master_unit() == &thd->lex->unit) // upper level SELECT + { + conds=new Item_int((longlong) 0,1); // Always false + } if (make_join_select(this, select, conds)) { zero_result_cause= @@ -9133,8 +9139,7 @@ Field *create_tmp_field_for_schema(THD *thd, Item *item, TABLE *table) if (item->field_type() == MYSQL_TYPE_VARCHAR) { Field *field; - if (item->max_length > MAX_FIELD_VARCHARLENGTH / - item->collation.collation->mbmaxlen) + if (item->max_length > MAX_FIELD_VARCHARLENGTH) field= new Field_blob(item->max_length, item->maybe_null, item->name, item->collation.collation); else diff --git a/sql/sql_show.cc b/sql/sql_show.cc index 175588368b3..e8107248c14 100644 --- a/sql/sql_show.cc +++ b/sql/sql_show.cc @@ -4516,9 +4516,19 @@ int fill_variables(THD *thd, TABLE_LIST *tables, COND *cond) int res= 0; LEX *lex= thd->lex; const char *wild= lex->wild ? lex->wild->ptr() : NullS; + enum enum_schema_tables schema_table_idx= + get_schema_table_idx(tables->schema_table); + enum enum_var_type option_type= OPT_SESSION; + bool upper_case_names= (schema_table_idx != SCH_VARIABLES); + bool sorted_vars= (schema_table_idx == SCH_VARIABLES); + + if (lex->option_type == OPT_GLOBAL || + schema_table_idx == SCH_GLOBAL_VARIABLES) + option_type= OPT_GLOBAL; + rw_rdlock(&LOCK_system_variables_hash); - res= show_status_array(thd, wild, enumerate_sys_vars(thd, TRUE), - lex->option_type, 0, "", tables->table, 0); + res= show_status_array(thd, wild, enumerate_sys_vars(thd, sorted_vars), + option_type, NULL, "", tables->table, upper_case_names); rw_unlock(&LOCK_system_variables_hash); DBUG_RETURN(res); } @@ -4530,16 +4540,38 @@ int fill_status(THD *thd, TABLE_LIST *tables, COND *cond) LEX *lex= thd->lex; const char *wild= lex->wild ? lex->wild->ptr() : NullS; int res= 0; - STATUS_VAR tmp; + STATUS_VAR *tmp1, tmp; + enum enum_schema_tables schema_table_idx= + get_schema_table_idx(tables->schema_table); + enum enum_var_type option_type; + bool upper_case_names= (schema_table_idx != SCH_STATUS); + + if (schema_table_idx == SCH_STATUS) + { + option_type= lex->option_type; + if (option_type == OPT_GLOBAL) + tmp1= &tmp; + else + tmp1= thd->initial_status_var; + } + else if (schema_table_idx == SCH_GLOBAL_STATUS) + { + option_type= OPT_GLOBAL; + tmp1= &tmp; + } + else + { + option_type= OPT_SESSION; + tmp1= &thd->status_var; + } + pthread_mutex_lock(&LOCK_status); - if (lex->option_type == OPT_GLOBAL) + if (option_type == OPT_GLOBAL) calc_sum_of_all_status(&tmp); res= show_status_array(thd, wild, (SHOW_VAR *)all_status_vars.buffer, - OPT_GLOBAL, - (lex->option_type == OPT_GLOBAL ? - &tmp: thd->initial_status_var), - "", tables->table, 0); + option_type, tmp1, "", tables->table, + upper_case_names); pthread_mutex_unlock(&LOCK_status); DBUG_RETURN(res); } @@ -4782,12 +4814,10 @@ TABLE *create_schema_table(THD *thd, TABLE_LIST *table_list) /* Don't let unimplemented types pass through. Could be a grave error. */ DBUG_ASSERT(fields_info->field_type == MYSQL_TYPE_STRING); - /* this should be changed when Item_empty_string is fixed(in 4.1) */ - if (!(item= new Item_empty_string("", 0, cs))) + if (!(item= new Item_empty_string("", fields_info->field_length, cs))) { DBUG_RETURN(0); } - item->max_length= fields_info->field_length * cs->mbmaxlen; item->set_name(fields_info->field_name, strlen(fields_info->field_name), cs); break; @@ -5228,172 +5258,6 @@ int fill_schema_files(THD *thd, TABLE_LIST *tables, COND *cond) DBUG_RETURN(0); } -int fill_schema_status(THD *thd, SHOW_VAR *variables, - struct system_status_var *status_var, - const char *prefix, TABLE *table) -{ - SHOW_VAR tmp, *var; - SHOW_TYPE show_type; - LEX_STRING null_lex_str; - char buff[SHOW_VAR_FUNC_BUFF_SIZE]; - char name_buf[64], *name_pos; - int name_len; - DBUG_ENTER("fill_schema_status"); - - null_lex_str.str= 0; - null_lex_str.length= 0; - - name_pos= strnmov(name_buf, prefix, sizeof(name_buf) - 1); - if (*prefix) - *name_pos++= '_'; - name_len= name_buf + sizeof(name_buf) - name_pos; - - for (; variables->name; variables++) - { - strnmov(name_pos, variables->name, name_len); - name_buf[sizeof(name_buf) - 1]= 0; - make_upper(name_buf); - - for (var= variables; var->type == SHOW_FUNC; var= &tmp) - ((mysql_show_var_func)(var->value))(thd, &tmp, buff); - - show_type= var->type; - - if (show_type == SHOW_ARRAY) - { - fill_schema_status(thd, (SHOW_VAR*) var->value, - status_var, name_buf, table); - } - else - { - char *value= var->value; - - restore_record(table, s->default_values); - table->field[0]->store(name_buf, strlen(name_buf), system_charset_info); - - if (show_type == SHOW_SYS) - { - show_type= ((sys_var*) value)->show_type(); - value= (char*) ((sys_var*) value)->value_ptr(thd, OPT_GLOBAL, - &null_lex_str); - } - - switch (show_type) - { - case SHOW_DOUBLE_STATUS: - value= (char*) status_var + (ulong) value; - table->field[1]->store(*(double*) value); - break; - case SHOW_LONG_STATUS: - value= (char*) status_var + (ulong) value; - /* fall through */ - case SHOW_LONG: - case SHOW_LONG_NOFLUSH: /* the difference lies in refresh_status() */ - table->field[1]->store((longlong) *(long*) value, false); - break; - case SHOW_LONGLONG: - table->field[1]->store(*(longlong*) value, false); - break; - case SHOW_HA_ROWS: - table->field[1]->store((longlong) *(ha_rows*) value, false); - break; - case SHOW_BOOL: - table->field[1]->store((longlong) *(bool*) value, false); - break; - case SHOW_MY_BOOL: - table->field[1]->store((longlong) *(my_bool*) value, false); - break; - case SHOW_INT: - table->field[1]->store((longlong) *(uint32*) value, false); - break; - case SHOW_HAVE: /* always displayed as 0 */ - table->field[1]->store((longlong) 0, false); - break; - case SHOW_CHAR_PTR: - value= *(char**) value; - /* fall through */ - case SHOW_CHAR: /* always displayed as 0 */ - table->field[1]->store((longlong) 0, false); - break; - case SHOW_KEY_CACHE_LONG: - value= (char*) dflt_key_cache + (ulong) value; - table->field[1]->store((longlong) *(long*) value, false); - break; - case SHOW_KEY_CACHE_LONGLONG: - value= (char*) dflt_key_cache + (ulong) value; - table->field[1]->store(*(longlong*) value, false); - break; - case SHOW_UNDEF: /* always displayed as 0 */ - table->field[1]->store((longlong) 0, false); - break; - case SHOW_SYS: /* cannot happen */ - default: - DBUG_ASSERT(0); - break; - } - - table->field[1]->set_notnull(); - if (schema_table_store_record(thd, table)) - DBUG_RETURN(1); - } - } - - DBUG_RETURN(0); -} - -int fill_schema_global_status(THD *thd, TABLE_LIST *tables, COND *cond) -{ - STATUS_VAR tmp; - int res= 0; - DBUG_ENTER("fill_schema_global_status"); - - pthread_mutex_lock(&LOCK_status); - calc_sum_of_all_status(&tmp); - res= fill_schema_status(thd, (SHOW_VAR*) all_status_vars.buffer, - &tmp, "", tables->table); - pthread_mutex_unlock(&LOCK_status); - - DBUG_RETURN(res); -} - -int fill_schema_session_status(THD *thd, TABLE_LIST *tables, COND *cond) -{ - int res= 0; - DBUG_ENTER("fill_schema_session_status"); - - pthread_mutex_lock(&LOCK_status); - res= fill_schema_status(thd, (SHOW_VAR*) all_status_vars.buffer, - &thd->status_var, "", tables->table); - pthread_mutex_unlock(&LOCK_status); - - DBUG_RETURN(res); -} - -int fill_schema_global_variables(THD *thd, TABLE_LIST *tables, COND *cond) -{ - int res= 0; - DBUG_ENTER("fill_schema_global_variables"); - - rw_rdlock(&LOCK_system_variables_hash); - res= show_status_array(thd, "", enumerate_sys_vars(thd, FALSE), OPT_GLOBAL, - NULL, "", tables->table, 1); - rw_unlock(&LOCK_system_variables_hash); - - DBUG_RETURN(res); -} - -int fill_schema_session_variables(THD *thd, TABLE_LIST *tables, COND *cond) -{ - int res= 0; - DBUG_ENTER("fill_schema_session_variables"); - - rw_rdlock(&LOCK_system_variables_hash); - res= show_status_array(thd, "", enumerate_sys_vars(thd, FALSE), OPT_SESSION, - NULL, "", tables->table, 1); - rw_unlock(&LOCK_system_variables_hash); - - DBUG_RETURN(res); -} ST_FIELD_INFO schema_fields_info[]= { @@ -5763,24 +5627,8 @@ ST_FIELD_INFO partitions_fields_info[]= ST_FIELD_INFO variables_fields_info[]= { - {"Variable_name", 80, MYSQL_TYPE_STRING, 0, 0, "Variable_name"}, - {"Value", FN_REFLEN, MYSQL_TYPE_STRING, 0, 0, "Value"}, - {0, 0, MYSQL_TYPE_STRING, 0, 0, 0} -}; - - -ST_FIELD_INFO status_fields_info[]= -{ - {"VARIABLE_NAME", 64, MYSQL_TYPE_STRING, 0, 0, "Variable_name"}, - {"VARIABLE_VALUE", 2207, MYSQL_TYPE_DECIMAL, 0, 0, "Value"}, - {0, 0, MYSQL_TYPE_STRING, 0, 0, 0} -}; - - -ST_FIELD_INFO system_variables_fields_info[]= -{ {"VARIABLE_NAME", 64, MYSQL_TYPE_STRING, 0, 0, "Variable_name"}, - {"VARIABLE_VALUE", 65535, MYSQL_TYPE_STRING, 0, 1, "Value"}, + {"VARIABLE_VALUE", 20480, MYSQL_TYPE_STRING, 0, 1, "Value"}, {0, 0, MYSQL_TYPE_STRING, 0, 0, 0} }; @@ -5920,10 +5768,10 @@ ST_SCHEMA_TABLE schema_tables[]= Events::fill_schema_events, make_old_format, 0, -1, -1, 0}, {"FILES", files_fields_info, create_schema_table, fill_schema_files, 0, 0, -1, -1, 0}, - {"GLOBAL_STATUS", status_fields_info, create_schema_table, - fill_schema_global_status, make_old_format, 0, -1, -1, 0}, - {"GLOBAL_VARIABLES", system_variables_fields_info, create_schema_table, - fill_schema_global_variables, make_old_format, 0, -1, -1, 0}, + {"GLOBAL_STATUS", variables_fields_info, create_schema_table, + fill_status, make_old_format, 0, -1, -1, 0}, + {"GLOBAL_VARIABLES", variables_fields_info, create_schema_table, + fill_variables, make_old_format, 0, -1, -1, 0}, {"KEY_COLUMN_USAGE", key_column_usage_fields_info, create_schema_table, get_all_tables, 0, get_schema_key_column_usage_record, 4, 5, 0}, {"OPEN_TABLES", open_tables_fields_info, create_schema_table, @@ -5943,10 +5791,10 @@ ST_SCHEMA_TABLE schema_tables[]= fill_schema_shemata, make_schemata_old_format, 0, 1, -1, 0}, {"SCHEMA_PRIVILEGES", schema_privileges_fields_info, create_schema_table, fill_schema_schema_privileges, 0, 0, -1, -1, 0}, - {"SESSION_STATUS", status_fields_info, create_schema_table, - fill_schema_session_status, make_old_format, 0, -1, -1, 0}, - {"SESSION_VARIABLES", system_variables_fields_info, create_schema_table, - fill_schema_session_variables, make_old_format, 0, -1, -1, 0}, + {"SESSION_STATUS", variables_fields_info, create_schema_table, + fill_status, make_old_format, 0, -1, -1, 0}, + {"SESSION_VARIABLES", variables_fields_info, create_schema_table, + fill_variables, make_old_format, 0, -1, -1, 0}, {"STATISTICS", stat_fields_info, create_schema_table, get_all_tables, make_old_format, get_schema_stat_record, 1, 2, 0}, {"STATUS", variables_fields_info, create_schema_table, fill_status, diff --git a/sql/sql_union.cc b/sql/sql_union.cc index 2a1db422058..dbae7977d62 100644 --- a/sql/sql_union.cc +++ b/sql/sql_union.cc @@ -147,8 +147,16 @@ st_select_lex_unit::init_prepare_fake_select_lex(THD *thd_arg) fake_select_lex->table_list.link_in_list((byte *)&result_table_list, (byte **) &result_table_list.next_local); - fake_select_lex->context.table_list= fake_select_lex->context.first_name_resolution_table= + fake_select_lex->context.table_list= + fake_select_lex->context.first_name_resolution_table= fake_select_lex->get_table_list(); + if (!fake_select_lex->first_execution) + { + for (ORDER *order= (ORDER *) global_parameters->order_list.first; + order; + order= order->next) + order->item= &order->item_ptr; + } for (ORDER *order= (ORDER *)global_parameters->order_list.first; order; order=order->next) diff --git a/strings/decimal.c b/strings/decimal.c index b29017aa6cb..0768c8cd4ca 100644 --- a/strings/decimal.c +++ b/strings/decimal.c @@ -1089,7 +1089,11 @@ int decimal2longlong(decimal_t *from, longlong *to) x=x*DIG_BASE - *buf++; if (unlikely(y < (LONGLONG_MIN/DIG_BASE) || x > y)) { - *to= from->sign ? y : -y; + /* + the decimal is bigger than any possible integer + return border integer depending on the sign + */ + *to= from->sign ? LONGLONG_MIN : LONGLONG_MAX; return E_DEC_OVERFLOW; } } |