diff options
| author | unknown <evgen@moonbone.local> | 2006-06-18 14:56:35 +0400 |
|---|---|---|
| committer | unknown <evgen@moonbone.local> | 2006-06-18 14:56:35 +0400 |
| commit | 610b1a4ebda3d2f7dec8db7a5e8226ac0f07338c (patch) | |
| tree | bc8abc7cba71ffb27a7c5d27a1b756f5b4700da3 | |
| parent | 60a9c875e86fabbaf460d9b05fc74941c5169204 (diff) | |
| parent | 9ec3f63f7eb9c4b9bcfbcddbc25937475b4edb9f (diff) | |
| download | mariadb-git-610b1a4ebda3d2f7dec8db7a5e8226ac0f07338c.tar.gz | |
Manually merged
configure.in:
Auto merged
client/mysqlbinlog.cc:
Auto merged
mysql-test/mysql-test-run.pl:
Auto merged
mysql-test/r/archive.result:
Auto merged
mysql-test/r/auto_increment.result:
Auto merged
mysql-test/r/cast.result:
Auto merged
mysql-test/r/ctype_utf8.result:
Auto merged
mysql-test/r/func_group.result:
Auto merged
mysql-test/r/func_str.result:
Auto merged
mysql-test/r/func_time.result:
Auto merged
mysql-test/r/multi_update.result:
Auto merged
mysql-test/r/ndb_lock.result:
Auto merged
mysql-test/r/replace.result:
Auto merged
mysql-test/r/union.result:
Auto merged
mysql-test/t/archive.test:
Auto merged
mysql-test/t/auto_increment.test:
Auto merged
mysql-test/t/delayed.test:
Auto merged
mysql-test/t/func_time.test:
Auto merged
mysql-test/t/multi_update.test:
Auto merged
mysql-test/t/ndb_lock.test:
Auto merged
mysql-test/t/select.test:
Auto merged
sql/field.cc:
Auto merged
sql/ha_ndbcluster.cc:
Auto merged
sql/item.h:
Auto merged
sql/item_cmpfunc.h:
Auto merged
sql/item_func.cc:
Auto merged
sql/item_strfunc.cc:
Auto merged
sql/item_timefunc.cc:
Auto merged
sql/opt_range.cc:
Auto merged
sql/opt_sum.cc:
Auto merged
sql/sql_base.cc:
Auto merged
sql/sql_class.cc:
Auto merged
sql/sql_class.h:
Auto merged
sql/sql_insert.cc:
Auto merged
sql/sql_lex.cc:
Auto merged
sql/sql_lex.h:
Auto merged
sql/sql_parse.cc:
Auto merged
sql/sql_select.cc:
Auto merged
sql/sql_select.h:
Auto merged
sql/sql_yacc.yy:
Auto merged
sql/structs.h:
Auto merged
storage/archive/ha_archive.cc:
Auto merged
tests/mysql_client_test.c:
Auto merged
51 files changed, 1060 insertions, 230 deletions
diff --git a/mysql-test/extra/rpl_tests/rpl_log.test b/mysql-test/extra/rpl_tests/rpl_log.test index 20ee7cd0d8f..d87030ba531 100644 --- a/mysql-test/extra/rpl_tests/rpl_log.test +++ b/mysql-test/extra/rpl_tests/rpl_log.test @@ -125,6 +125,21 @@ show slave status; --error 1220 show binlog events in 'slave-bin.000005' from 4; +# +# Bug #6880: LAST_INSERT_ID() within a statement +# + +create table t1(a int auto_increment primary key, b int); +insert into t1 values (NULL, 1); +reset master; +set insert_id=5; +insert into t1 values (NULL, last_insert_id()), (NULL, last_insert_id()); +--replace_result $VERSION VERSION +show binlog events; +select * from t1; +drop table t1; + +# End of 4.1 tests # The table drops caused Cluster Replication wrapper to fail as event ID would never be the same.# Moving drops here. DROP TABLE t1; diff --git a/mysql-test/r/archive.result b/mysql-test/r/archive.result index 19cb0c9768f..cacf4aaf304 100644 --- a/mysql-test/r/archive.result +++ b/mysql-test/r/archive.result @@ -6254,6 +6254,7 @@ auto fld1 companynr fld3 fld4 fld5 fld6 3 011402 37 Romans scholastics jarring 4 011403 37 intercepted audiology tinily DELETE FROM t2; +ERROR HY000: Table storage engine for 't2' doesn't have this option SELECT * FROM t2; auto fld1 companynr fld3 fld4 fld5 fld6 1 000001 00 Omaha teethe neat @@ -8685,6 +8686,7 @@ auto fld1 companynr fld3 fld4 fld5 fld6 3 011402 37 Romans scholastics jarring 4 011403 37 intercepted audiology tinily TRUNCATE TABLE t2; +ERROR HY000: Table storage engine for 't2' doesn't have this option SELECT * FROM t2; auto fld1 companynr fld3 fld4 fld5 fld6 1 000001 00 Omaha teethe neat diff --git a/mysql-test/r/auto_increment.result b/mysql-test/r/auto_increment.result index d9e9392f618..10f26c40553 100644 --- a/mysql-test/r/auto_increment.result +++ b/mysql-test/r/auto_increment.result @@ -378,6 +378,28 @@ t1 CREATE TABLE `t1` ( KEY `t1_name` (`t1_name`) ) ENGINE=MyISAM AUTO_INCREMENT=1003 DEFAULT CHARSET=latin1 DROP TABLE `t1`; +create table t1(a int not null auto_increment primary key); +create table t2(a int not null auto_increment primary key, t1a int); +insert into t1 values(NULL); +insert into t2 values (NULL, LAST_INSERT_ID()), (NULL, LAST_INSERT_ID()); +insert into t1 values (NULL); +insert into t2 values (NULL, LAST_INSERT_ID()), (NULL, LAST_INSERT_ID()), +(NULL, LAST_INSERT_ID()); +insert into t1 values (NULL); +insert into t2 values (NULL, LAST_INSERT_ID()), (NULL, LAST_INSERT_ID()), +(NULL, LAST_INSERT_ID()), (NULL, LAST_INSERT_ID()); +select * from t2; +a t1a +1 1 +2 1 +3 2 +4 2 +5 2 +6 3 +7 3 +8 3 +9 3 +drop table t1, t2; End of 4.1 tests CREATE TABLE t1 ( `a` int(11) NOT NULL auto_increment, `b` int(11) default NULL,PRIMARY KEY (`a`),UNIQUE KEY `b` (`b`)); insert into t1 (b) values (1); diff --git a/mysql-test/r/ctype_utf8.result b/mysql-test/r/ctype_utf8.result index 1246c6f3d5d..b092a21787f 100644 --- a/mysql-test/r/ctype_utf8.result +++ b/mysql-test/r/ctype_utf8.result @@ -1112,6 +1112,46 @@ check table t1; Table Op Msg_type Msg_text test.t1 check status OK drop table t1; +SET NAMES utf8; +CREATE TABLE t1 (id int PRIMARY KEY, +a varchar(16) collate utf8_unicode_ci NOT NULL default '', +b int, +f varchar(128) default 'XXX', +INDEX (a(4)) +) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; +INSERT INTO t1(id, a, b) VALUES +(1, 'cccc', 50), (2, 'cccc', 70), (3, 'cccc', 30), +(4, 'cccc', 30), (5, 'cccc', 20), (6, 'bbbbbb', 40), +(7, 'dddd', 30), (8, 'aaaa', 10), (9, 'aaaa', 50), +(10, 'eeeee', 40), (11, 'bbbbbb', 60); +SELECT id, a, b FROM t1; +id a b +1 cccc 50 +2 cccc 70 +3 cccc 30 +4 cccc 30 +5 cccc 20 +6 bbbbbb 40 +7 dddd 30 +8 aaaa 10 +9 aaaa 50 +10 eeeee 40 +11 bbbbbb 60 +SELECT id, a, b FROM t1 WHERE a BETWEEN 'aaaa' AND 'bbbbbb'; +id a b +8 aaaa 10 +9 aaaa 50 +6 bbbbbb 40 +11 bbbbbb 60 +SELECT id, a FROM t1 WHERE a='bbbbbb'; +id a +6 bbbbbb +11 bbbbbb +SELECT id, a FROM t1 WHERE a='bbbbbb' ORDER BY b; +id a +6 bbbbbb +11 bbbbbb +DROP TABLE t1; CREATE TABLE t1(id varchar(20) NOT NULL) DEFAULT CHARSET=utf8; INSERT INTO t1 VALUES ('xxx'), ('aa'), ('yyy'), ('aa'); SELECT id FROM t1; diff --git a/mysql-test/r/delayed.result b/mysql-test/r/delayed.result index f8ae61b03fb..a336f3b4108 100644 --- a/mysql-test/r/delayed.result +++ b/mysql-test/r/delayed.result @@ -39,3 +39,33 @@ select * from t1; a 1 drop table t1; +CREATE TABLE t1 ( a int(10) NOT NULL auto_increment, PRIMARY KEY (a)); +insert delayed into t1 values(null); +insert into t1 values(null); +insert into t1 values(null); +insert delayed into t1 values(null); +insert delayed into t1 values(null); +insert delayed into t1 values(null); +insert into t1 values(null); +insert into t1 values(null); +insert into t1 values(null); +delete from t1 where a=6; +insert delayed into t1 values(null); +insert delayed into t1 values(null); +insert delayed into t1 values(null); +insert delayed into t1 values(null); +select * from t1 order by a; +a +1 +2 +3 +4 +5 +7 +8 +9 +10 +11 +12 +13 +DROP TABLE t1; diff --git a/mysql-test/r/func_concat.result b/mysql-test/r/func_concat.result index 0bd53b32dd7..66808afd4e9 100644 --- a/mysql-test/r/func_concat.result +++ b/mysql-test/r/func_concat.result @@ -68,3 +68,17 @@ select 'a' union select concat('a', -0.0000); a a a0.0000 +select concat((select x from (select 'a' as x) as t1 ), +(select y from (select 'b' as y) as t2 )) from (select 1 union select 2 ) +as t3; +concat((select x from (select 'a' as x) as t1 ), +(select y from (select 'b' as y) as t2 )) +ab +ab +create table t1(f1 varchar(6)) charset=utf8; +insert into t1 values ("123456"); +select concat(f1, 2) a from t1 union select 'x' a from t1; +a +1234562 +x +drop table t1; diff --git a/mysql-test/r/func_group.result b/mysql-test/r/func_group.result index 824edbbe3a6..d9695c5f3df 100644 --- a/mysql-test/r/func_group.result +++ b/mysql-test/r/func_group.result @@ -821,6 +821,41 @@ SELECT MAX(id) FROM t1 WHERE id < 3 AND a=2 AND b=6; MAX(id) NULL DROP TABLE t1; +CREATE TABLE t1 (id int PRIMARY KEY, b char(3), INDEX(b)); +INSERT INTO t1 VALUES (1,'xx'), (2,'aa'); +SELECT * FROM t1; +id b +1 xx +2 aa +SELECT MAX(b) FROM t1 WHERE b < 'ppppp'; +MAX(b) +aa +SHOW WARNINGS; +Level Code Message +SELECT MAX(b) FROM t1 WHERE b < 'pp'; +MAX(b) +aa +DROP TABLE t1; +CREATE TABLE t1 (id int PRIMARY KEY, b char(16), INDEX(b(4))); +INSERT INTO t1 VALUES (1, 'xxxxbbbb'), (2, 'xxxxaaaa'); +SELECT MAX(b) FROM t1; +MAX(b) +xxxxbbbb +EXPLAIN SELECT MAX(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 +DROP TABLE t1; +CREATE TABLE t1 (id int , b varchar(512), INDEX(b(250))) COLLATE latin1_bin; +INSERT INTO t1 VALUES +(1,CONCAT(REPEAT('_', 250), "qq")), (1,CONCAT(REPEAT('_', 250), "zz")), +(1,CONCAT(REPEAT('_', 250), "aa")), (1,CONCAT(REPEAT('_', 250), "ff")); +SELECT MAX(b) FROM t1; +MAX(b) +__________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________zz +EXPLAIN SELECT MAX(b) FROM t1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 4 +DROP TABLE t1; create table t2 (ff double); insert into t2 values (2.2); select cast(sum(distinct ff) as decimal(5,2)) from t2; diff --git a/mysql-test/r/func_in.result b/mysql-test/r/func_in.result index e38e2624e19..f758e2adfd9 100644 --- a/mysql-test/r/func_in.result +++ b/mysql-test/r/func_in.result @@ -202,6 +202,35 @@ select count(*) from t1 where id not in (1,2); count(*) 1 drop table t1; +create table t1 (f1 char(1), f2 int); +insert into t1 values (1,0),('a',1),('z',2); +select f1 from t1 where f1 in (1,'z'); +f1 +1 +z +select f2 from t1 where f2 in (1,'z'); +f2 +0 +1 +Warnings: +Warning 1292 Truncated incorrect INTEGER value: 'z' +select f1 from t1 where 'z' in (1,f1); +f1 +z +select * from t1 where 'z' in (f2,f1); +f1 f2 +1 0 +a 1 +z 2 +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: 'z' +Warning 1292 Truncated incorrect DOUBLE value: 'z' +Warning 1292 Truncated incorrect DOUBLE value: 'z' +select * from t1 where 1 in (f2,f1); +f1 f2 +1 0 +a 1 +drop table t1; CREATE TABLE t1 (a int PRIMARY KEY); INSERT INTO t1 VALUES (44), (45), (46); SELECT * FROM t1 WHERE a IN (45); diff --git a/mysql-test/r/func_str.result b/mysql-test/r/func_str.result index 354c886b19b..54960765c56 100644 --- a/mysql-test/r/func_str.result +++ b/mysql-test/r/func_str.result @@ -1023,6 +1023,21 @@ NULL select ifnull(load_file("lkjlkj"),"it's null"); ifnull(load_file("lkjlkj"),"it's null") it's null +create table t1 (f1 varchar(4), f2 varchar(64), unique key k1 (f1,f2)); +insert into t1 values ( 'test',md5('test')), ('test', sha('test')); +select * from t1 where f1='test' and (f2= md5("test") or f2= md5("TEST")); +f1 f2 +test 098f6bcd4621d373cade4e832627b4f6 +select * from t1 where f1='test' and (f2= md5("TEST") or f2= md5("test")); +f1 f2 +test 098f6bcd4621d373cade4e832627b4f6 +select * from t1 where f1='test' and (f2= sha("test") or f2= sha("TEST")); +f1 f2 +test a94a8fe5ccb19ba61c4c0873d391e987982fbbd3 +select * from t1 where f1='test' and (f2= sha("TEST") or f2= sha("test")); +f1 f2 +test a94a8fe5ccb19ba61c4c0873d391e987982fbbd3 +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/func_time.result b/mysql-test/r/func_time.result index d54989e27bc..43c9748bace 100644 --- a/mysql-test/r/func_time.result +++ b/mysql-test/r/func_time.result @@ -751,6 +751,49 @@ select monthname(str_to_date(null, '%m')), monthname(str_to_date(null, '%m')), monthname(str_to_date(1, '%m')), monthname(str_to_date(0, '%m')); monthname(str_to_date(null, '%m')) monthname(str_to_date(null, '%m')) monthname(str_to_date(1, '%m')) monthname(str_to_date(0, '%m')) NULL NULL January NULL +create table t1(f1 date, f2 time, f3 datetime); +insert into t1 values ("2006-01-01", "12:01:01", "2006-01-01 12:01:01"); +insert into t1 values ("2006-01-02", "12:01:02", "2006-01-02 12:01:02"); +select f1 from t1 where f1 between "2006-1-1" and 20060101; +f1 +2006-01-01 +select f1 from t1 where f1 between "2006-1-1" and "2006.1.1"; +f1 +2006-01-01 +select f1 from t1 where date(f1) between "2006-1-1" and "2006.1.1"; +f1 +2006-01-01 +select f2 from t1 where f2 between "12:1:2" and "12:2:2"; +f2 +12:01:02 +select f2 from t1 where time(f2) between "12:1:2" and "12:2:2"; +f2 +12:01:02 +select f3 from t1 where f3 between "2006-1-1 12:1:1" and "2006-1-1 12:1:2"; +f3 +2006-01-01 12:01:01 +select f3 from t1 where timestamp(f3) between "2006-1-1 12:1:1" and "2006-1-1 12:1:2"; +f3 +2006-01-01 12:01:01 +select f1 from t1 where "2006-1-1" between f1 and f3; +f1 +2006-01-01 +select f1 from t1 where "2006-1-1" between date(f1) and date(f3); +f1 +2006-01-01 +select f1 from t1 where "2006-1-1" between f1 and 'zzz'; +f1 +Warnings: +Warning 1292 Incorrect date value: 'zzz' for column 'f1' at row 1 +Warning 1292 Truncated incorrect INTEGER value: 'zzz' +Warning 1292 Truncated incorrect INTEGER value: 'zzz' +select f1 from t1 where makedate(2006,1) between date(f1) and date(f3); +f1 +2006-01-01 +select f1 from t1 where makedate(2006,2) between date(f1) and date(f3); +f1 +2006-01-02 +drop table t1; select now() - now() + 0, curtime() - curtime() + 0, sec_to_time(1) + 0, from_unixtime(1) + 0; now() - now() + 0 curtime() - curtime() + 0 sec_to_time(1) + 0 from_unixtime(1) + 0 diff --git a/mysql-test/r/join.result b/mysql-test/r/join.result index 86288caf398..48b7730481f 100644 --- a/mysql-test/r/join.result +++ b/mysql-test/r/join.result @@ -744,3 +744,23 @@ select a2 from ((t1 natural join t2) join t3 on b=c1) natural join t4; a2 1 drop table t1,t2,t3,t4; +create table t1 (c int, b int); +create table t2 (a int, b int); +create table t3 (b int, c int); +create table t4 (y int, c int); +create table t5 (y int, z int); +insert into t1 values (3,2); +insert into t2 values (1,2); +insert into t3 values (2,3); +insert into t4 values (1,3); +insert into t5 values (1,4); +prepare stmt1 from "select * from ((t3 natural join (t1 natural join t2)) +natural join t4) natural join t5"; +execute stmt1; +y c b a z +1 3 2 1 4 +select * from ((t3 natural join (t1 natural join t2)) natural join t4) +natural join t5; +y c b a z +1 3 2 1 4 +drop table t1, t2, t3, t4, t5; diff --git a/mysql-test/r/multi_update.result b/mysql-test/r/multi_update.result index 6fdd105fd6c..8791b8cc080 100644 --- a/mysql-test/r/multi_update.result +++ b/mysql-test/r/multi_update.result @@ -476,6 +476,11 @@ aclid bigint, index idx_acl(aclid) insert into t2 values(1,null); delete t2, t1 from t2 left join t1 on (t2.aclid=t1.aclid) where t2.refid='1'; drop table t1, t2; +create table t1(a int); +create table t2(a int); +delete from t1,t2 using t1,t2 where t1.a=(select a from t1); +ERROR HY000: You can't specify target table 't1' for update in FROM clause +drop table t1, t2; create table t1 ( c char(8) not null ) engine=innodb; insert into t1 values ('0'),('1'),('2'),('3'),('4'),('5'),('6'),('7'),('8'),('9'); insert into t1 values ('A'),('B'),('C'),('D'),('E'),('F'); diff --git a/mysql-test/r/replace.result b/mysql-test/r/replace.result index ca32b3d45bf..842302c89ac 100644 --- a/mysql-test/r/replace.result +++ b/mysql-test/r/replace.result @@ -24,3 +24,9 @@ a b 63 default_value 127 last drop table t1; +CREATE TABLE t1 (f1 INT); +CREATE VIEW v1 AS SELECT f1 FROM t1 WHERE f1 = 0 WITH CHECK OPTION; +REPLACE INTO v1 (f1) VALUES (1); +ERROR HY000: CHECK OPTION failed 'test.v1' +DROP TABLE t1; +DROP VIEW v1; diff --git a/mysql-test/r/rpl_stm_log.result b/mysql-test/r/rpl_stm_log.result index 02a861ceb53..23639cb7a99 100644 --- a/mysql-test/r/rpl_stm_log.result +++ b/mysql-test/r/rpl_stm_log.result @@ -95,6 +95,23 @@ Slave_IO_State Master_Host Master_User Master_Port Connect_Retry Master_Log_File # 127.0.0.1 root MASTER_PORT 1 master-bin.000002 388 # # master-bin.000002 Yes Yes # 0 0 388 # None 0 No # show binlog events in 'slave-bin.000005' from 4; ERROR HY000: Error when executing command SHOW BINLOG EVENTS: Could not find target log +create table t1(a int auto_increment primary key, b int); +insert into t1 values (NULL, 1); +reset master; +set insert_id=5; +insert into t1 values (NULL, last_insert_id()), (NULL, last_insert_id()); +show binlog events; +Log_name Pos Event_type Server_id End_log_pos Info +slave-bin.000001 4 Format_desc 2 98 Server ver: VERSION, Binlog ver: 4 +slave-bin.000001 98 Intvar 2 126 LAST_INSERT_ID=1 +slave-bin.000001 126 Intvar 2 154 INSERT_ID=5 +slave-bin.000001 154 Query 2 289 use `test`; insert into t1 values (NULL, last_insert_id()), (NULL, last_insert_id()) +select * from t1; +a b +1 1 +5 1 +6 1 +drop table t1; DROP TABLE t1; DROP TABLE t2; DROP TABLE t3; diff --git a/mysql-test/r/select.result b/mysql-test/r/select.result index d47d49b5298..1e72d12ba06 100644 --- a/mysql-test/r/select.result +++ b/mysql-test/r/select.result @@ -2656,16 +2656,6 @@ t11 MyISAM 10 Dynamic 0 0 X X X X X X X X latin1_swedish_ci NULL select 123 as a from t1 where f1 is null; a drop table t1,t11; -CREATE TABLE t1 (a INT, b INT); -(SELECT a, b AS c FROM t1) ORDER BY c+1; -a c -(SELECT a, b AS c FROM t1) ORDER BY b+1; -a c -SELECT a, b AS c FROM t1 ORDER BY c+1; -a c -SELECT a, b AS c FROM t1 ORDER BY b+1; -a c -drop table t1; CREATE TABLE t1 ( a INT NOT NULL, b INT NOT NULL, UNIQUE idx (a,b) ); INSERT INTO t1 VALUES (1,1),(1,2),(1,3),(1,4); CREATE TABLE t2 ( a INT NOT NULL, b INT NOT NULL, e INT ); @@ -2716,14 +2706,37 @@ select * from t1 where f1 in (select f3 from t2 where (f3,f4)= (select f3,f4 fro f1 f2 1 1 drop table t1,t2; +CREATE TABLE t1(a int, b int, c int, KEY b(b), KEY c(c)); +insert into t1 values (1,0,0),(2,0,0); +CREATE TABLE t2 (a int, b varchar(2), c varchar(2), PRIMARY KEY(a)); +insert into t2 values (1,'',''), (2,'',''); +CREATE TABLE t3 (a int, b int, PRIMARY KEY (a,b), KEY a (a), KEY b (b)); +insert into t3 values (1,1),(1,2); +explain select straight_join DISTINCT t2.a,t2.b, t1.c from t1, t3, t2 +where (t1.c=t2.a or (t1.c=t3.a and t2.a=t3.b)) and t1.b=556476786 and +t2.b like '%%' order by t2.b limit 0,1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref b,c b 5 const 1 Using where; Using temporary; Using filesort +1 SIMPLE t3 index PRIMARY,a,b PRIMARY 8 NULL 2 Using index +1 SIMPLE t2 ALL PRIMARY NULL NULL NULL 2 Range checked for each record (index map: 0x1) +DROP TABLE t1,t2,t3; CREATE TABLE t1 (a int, INDEX idx(a)); INSERT INTO t1 VALUES (2), (3), (1); EXPLAIN SELECT * FROM t1 IGNORE INDEX (idx); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 3 EXPLAIN SELECT * FROM t1 IGNORE INDEX (a); -ERROR 42000: Key 'a' doesn't exist in table 't1' +ERROR HY000: Key 'a' doesn't exist in table 't1' EXPLAIN SELECT * FROM t1 FORCE INDEX (a); +ERROR HY000: Key 'a' doesn't exist in table 't1' +DROP TABLE t1; +CREATE TABLE t1 (a int, INDEX idx(a)); +INSERT INTO t1 VALUES (2), (3), (1); +EXPLAIN SELECT * FROM t1 IGNORE INDEX (idx); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 +EXPLAIN SELECT * FROM t1 IGNORE INDEX (a); +ERROR 42000: Key 'a' doesn't exist in table 't1' ERROR 42000: Key 'a' doesn't exist in table 't1' DROP TABLE t1; CREATE TABLE t1 ( city char(30) ); @@ -2814,19 +2827,6 @@ WART 0100 1 WART 0200 1 WART 0300 3 DROP TABLE t1; -CREATE TABLE t1 ( a BLOB, INDEX (a(20)) ); -CREATE TABLE t2 ( a BLOB, INDEX (a(20)) ); -INSERT INTO t1 VALUES ('one'),('two'),('three'),('four'),('five'); -INSERT INTO t2 VALUES ('one'),('two'),('three'),('four'),('five'); -EXPLAIN SELECT * FROM t1 LEFT JOIN t2 USE INDEX (a) ON t1.a=t2.a; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 5 -1 SIMPLE t2 ref a a 23 test.t1.a 2 -EXPLAIN SELECT * FROM t1 LEFT JOIN t2 FORCE INDEX (a) ON t1.a=t2.a; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 5 -1 SIMPLE t2 ref a a 23 test.t1.a 2 -DROP TABLE t1, t2; create table t1 (a int, b int); create table t2 like t1; select t1.a from (t1 inner join t2 on t1.a=t2.a) where t2.a=1; @@ -2857,29 +2857,6 @@ x NULL 1.0000 drop table t1; -create table t1 (a int(11)); -select all all * from t1; -a -select distinct distinct * from t1; -a -select all distinct * from t1; -ERROR HY000: Incorrect usage of ALL and DISTINCT -select distinct all * from t1; -ERROR HY000: Incorrect usage of ALL and DISTINCT -drop table t1; -CREATE TABLE t1 ( a BLOB, INDEX (a(20)) ); -CREATE TABLE t2 ( a BLOB, INDEX (a(20)) ); -INSERT INTO t1 VALUES ('one'),('two'),('three'),('four'),('five'); -INSERT INTO t2 VALUES ('one'),('two'),('three'),('four'),('five'); -EXPLAIN SELECT * FROM t1 LEFT JOIN t2 USE INDEX (a) ON t1.a=t2.a; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 5 -1 SIMPLE t2 ref a a 23 test.t1.a 2 -EXPLAIN SELECT * FROM t1 LEFT JOIN t2 FORCE INDEX (a) ON t1.a=t2.a; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 5 -1 SIMPLE t2 ref a a 23 test.t1.a 2 -DROP TABLE t1, t2; CREATE TABLE t1 (a int); CREATE TABLE t2 (a int); INSERT INTO t1 VALUES (1), (2), (3), (4), (5); @@ -3456,3 +3433,23 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range PRIMARY,b b 5 NULL 3 Using where 1 SIMPLE t2 ref c c 5 test.t1.a 2 Using where DROP TABLE t1, t2; +create table t1 ( +a int unsigned not null auto_increment primary key, +b bit not null, +c bit not null +); +create table t2 ( +a int unsigned not null auto_increment primary key, +b bit not null, +c int unsigned not null, +d varchar(50) +); +insert into t1 (b,c) values (0,1), (0,1); +insert into t2 (b,c) values (0,1); +select t1.a, t1.b + 0, t1.c + 0, t2.a, t2.b + 0, t2.c, t2.d +from t1 left outer join t2 on t1.a = t2.c and t2.b <> 1 +where t1.b <> 1 order by t1.a; +a t1.b + 0 t1.c + 0 a t2.b + 0 c d +1 0 1 1 0 1 NULL +2 0 1 NULL NULL NULL NULL +drop table t1,t2; diff --git a/mysql-test/r/union.result b/mysql-test/r/union.result index 57beb6e4197..4280ae28e3c 100644 --- a/mysql-test/r/union.result +++ b/mysql-test/r/union.result @@ -1306,3 +1306,48 @@ id 5 99 drop table t1; +(select avg(1)) union (select avg(1)) union (select avg(1)) union +(select avg(1)) union (select avg(1)) union (select avg(1)) union +(select avg(1)) union (select avg(1)) union (select avg(1)) union +(select avg(1)) union (select avg(1)) union (select avg(1)) union +(select avg(1)) union (select avg(1)) union (select avg(1)) union +(select avg(1)) union (select avg(1)) union (select avg(1)) union +(select avg(1)) union (select avg(1)) union (select avg(1)) union +(select avg(1)) union (select avg(1)) union (select avg(1)) union +(select avg(1)) union (select avg(1)) union (select avg(1)) union +(select avg(1)) union (select avg(1)) union (select avg(1)) union +(select avg(1)) union (select avg(1)) union (select avg(1)) union +(select avg(1)) union (select avg(1)) union (select avg(1)) union +(select avg(1)) union (select avg(1)) union (select avg(1)) union +(select avg(1)) union (select avg(1)) union (select avg(1)) union +(select avg(1)) union (select avg(1)) union (select avg(1)) union +(select avg(1)) union (select avg(1)) union (select avg(1)) union +(select avg(1)) union (select avg(1)) union (select avg(1)) union +(select avg(1)) union (select avg(1)) union (select avg(1)) union +(select avg(1)) union (select avg(1)) union (select avg(1)) union +(select avg(1)) union (select avg(1)) union (select avg(1)) union +(select avg(1)) union (select avg(1)) union (select avg(1)) union +(select avg(1)) union (select avg(1)) union (select avg(1)) union +(select avg(1)) union (select avg(1)) union (select avg(1)) union +(select avg(1)) union (select avg(1)) union (select avg(1)) union +(select avg(1)) union (select avg(1)) union (select avg(1)) union +(select avg(1)) union (select avg(1)) union (select avg(1)) union +(select avg(1)) union (select avg(1)) union (select avg(1)) union +(select avg(1)) union (select avg(1)) union (select avg(1)) union +(select avg(1)) union (select avg(1)) union (select avg(1)) union +(select avg(1)) union (select avg(1)) union (select avg(1)) union +(select avg(1)) union (select avg(1)) union (select avg(1)) union +(select avg(1)) union (select avg(1)) union (select avg(1)) union +(select avg(1)) union (select avg(1)) union (select avg(1)) union +(select avg(1)) union (select avg(1)) union (select avg(1)) union +(select avg(1)) union (select avg(1)) union (select avg(1)) union +(select avg(1)) union (select avg(1)) union (select avg(1)) union +(select avg(1)) union (select avg(1)) union (select avg(1)) union +(select avg(1)) union (select avg(1)) union (select avg(1)) union +(select avg(1)) union (select avg(1)) union (select avg(1)) union +(select avg(1)) union (select avg(1)) union (select avg(1)) union +(select avg(1)) union (select avg(1)) union (select avg(1)) union +(select avg(1)) union (select avg(1)) union (select avg(1)) union +(select avg(1)) union (select avg(1)) union (select avg(1)); +avg(1) +NULL diff --git a/mysql-test/t/archive.test b/mysql-test/t/archive.test index c89d31c69b9..ae8aa8d4f85 100644 --- a/mysql-test/t/archive.test +++ b/mysql-test/t/archive.test @@ -1330,12 +1330,14 @@ SELECT * FROM t2; # # For bug #12836 # Delete was allowing all rows to be removed +--error 1031 DELETE FROM t2; SELECT * FROM t2; INSERT INTO t2 VALUES (2,011401,37,'breaking','dreaded','Steinberg','W'); INSERT INTO t2 VALUES (3,011402,37,'Romans','scholastics','jarring',''); INSERT INTO t2 VALUES (4,011403,37,'intercepted','audiology','tinily',''); SELECT * FROM t2; +--error 1031 TRUNCATE TABLE t2; SELECT * FROM t2; diff --git a/mysql-test/t/auto_increment.test b/mysql-test/t/auto_increment.test index e0b024d021b..2674639d0ac 100644 --- a/mysql-test/t/auto_increment.test +++ b/mysql-test/t/auto_increment.test @@ -238,6 +238,23 @@ SHOW CREATE TABLE `t1`; DROP TABLE `t1`; +# +# Bug #6880: LAST_INSERT_ID() within a statement +# + +create table t1(a int not null auto_increment primary key); +create table t2(a int not null auto_increment primary key, t1a int); +insert into t1 values(NULL); +insert into t2 values (NULL, LAST_INSERT_ID()), (NULL, LAST_INSERT_ID()); +insert into t1 values (NULL); +insert into t2 values (NULL, LAST_INSERT_ID()), (NULL, LAST_INSERT_ID()), +(NULL, LAST_INSERT_ID()); +insert into t1 values (NULL); +insert into t2 values (NULL, LAST_INSERT_ID()), (NULL, LAST_INSERT_ID()), +(NULL, LAST_INSERT_ID()), (NULL, LAST_INSERT_ID()); +select * from t2; +drop table t1, t2; + --echo End of 4.1 tests # diff --git a/mysql-test/t/ctype_utf8.test b/mysql-test/t/ctype_utf8.test index 5ca1f58d233..b58a2cf97d4 100644 --- a/mysql-test/t/ctype_utf8.test +++ b/mysql-test/t/ctype_utf8.test @@ -912,6 +912,32 @@ INSERT INTO t1 VALUES('uUABCDEFGHIGKLMNOPRSTUVWXYZ̈bbbbbbbbbbbbbbbbbbbbbbbbbbbb check table t1; drop table t1; +# +# Bug#14896: Comparison with a key in a partial index over mb chararacter field +# + +SET NAMES utf8; +CREATE TABLE t1 (id int PRIMARY KEY, + a varchar(16) collate utf8_unicode_ci NOT NULL default '', + b int, + f varchar(128) default 'XXX', + INDEX (a(4)) +) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; +INSERT INTO t1(id, a, b) VALUES + (1, 'cccc', 50), (2, 'cccc', 70), (3, 'cccc', 30), + (4, 'cccc', 30), (5, 'cccc', 20), (6, 'bbbbbb', 40), + (7, 'dddd', 30), (8, 'aaaa', 10), (9, 'aaaa', 50), + (10, 'eeeee', 40), (11, 'bbbbbb', 60); + +SELECT id, a, b FROM t1; + +SELECT id, a, b FROM t1 WHERE a BETWEEN 'aaaa' AND 'bbbbbb'; + +SELECT id, a FROM t1 WHERE a='bbbbbb'; +SELECT id, a FROM t1 WHERE a='bbbbbb' ORDER BY b; + +DROP TABLE t1; + # End of 4.1 tests # diff --git a/mysql-test/t/delayed.test b/mysql-test/t/delayed.test index 5ae757b1fde..55e8f81f763 100644 --- a/mysql-test/t/delayed.test +++ b/mysql-test/t/delayed.test @@ -50,3 +50,52 @@ insert into t1 values (1); insert delayed into t1 values (1); select * from t1; drop table t1; + +# +# Bug #20195: INSERT DELAYED with auto_increment is assigned wrong values +# +CREATE TABLE t1 ( a int(10) NOT NULL auto_increment, PRIMARY KEY (a)); + +# Make one delayed insert to start the separate thread +insert delayed into t1 values(null); + +# Do some normal inserts +insert into t1 values(null); +insert into t1 values(null); + +# Discarded, since the delayed-counter is 2, which is already used +insert delayed into t1 values(null); + +# Discarded, since the delayed-counter is 3, which is already used +insert delayed into t1 values(null); + +# Works, since the delayed-counter is 4, which is unused +insert delayed into t1 values(null); + +# Do some more inserts +insert into t1 values(null); +insert into t1 values(null); +insert into t1 values(null); + +# Delete one of the above to make a hole +delete from t1 where a=6; + +# Discarded, since the delayed-counter is 5, which is already used +insert delayed into t1 values(null); + +# Works, since the delayed-counter is 6, which is unused (the row we deleted) +insert delayed into t1 values(null); + +# Discarded, since the delayed-counter is 7, which is already used +insert delayed into t1 values(null); + +# Works, since the delayed-counter is 8, which is unused +insert delayed into t1 values(null); + +# Check what we have now +# must wait so that the delayed thread finishes +# Note: this must be increased if the test fails +--sleep 1 +select * from t1 order by a; + +DROP TABLE t1; diff --git a/mysql-test/t/func_concat.test b/mysql-test/t/func_concat.test index 37fc0e105b8..5487ad9c56b 100644 --- a/mysql-test/t/func_concat.test +++ b/mysql-test/t/func_concat.test @@ -52,4 +52,19 @@ select 'a' union select concat('a', -0.0); --replace_result a-0.0000 a0.0000 select 'a' union select concat('a', -0.0000); +# +# Bug#16716: subselect in concat() may lead to a wrong result +# +select concat((select x from (select 'a' as x) as t1 ), + (select y from (select 'b' as y) as t2 )) from (select 1 union select 2 ) + as t3; + # End of 4.1 tests + +# +# Bug#15962: CONCAT() in UNION may lead to a data trucation. +# +create table t1(f1 varchar(6)) charset=utf8; +insert into t1 values ("123456"); +select concat(f1, 2) a from t1 union select 'x' a from t1; +drop table t1; diff --git a/mysql-test/t/func_group.test b/mysql-test/t/func_group.test index fb9470c16dd..e8c5fa18a25 100644 --- a/mysql-test/t/func_group.test +++ b/mysql-test/t/func_group.test @@ -539,6 +539,34 @@ INSERT INTO t1 VALUES SELECT MAX(id) FROM t1 WHERE id < 3 AND a=2 AND b=6; DROP TABLE t1; +# +# Bug #18206: min/max optimization cannot be applied to partial index +# + +CREATE TABLE t1 (id int PRIMARY KEY, b char(3), INDEX(b)); +INSERT INTO t1 VALUES (1,'xx'), (2,'aa'); +SELECT * FROM t1; + +SELECT MAX(b) FROM t1 WHERE b < 'ppppp'; +SHOW WARNINGS; +SELECT MAX(b) FROM t1 WHERE b < 'pp'; +DROP TABLE t1; + +CREATE TABLE t1 (id int PRIMARY KEY, b char(16), INDEX(b(4))); +INSERT INTO t1 VALUES (1, 'xxxxbbbb'), (2, 'xxxxaaaa'); +SELECT MAX(b) FROM t1; +EXPLAIN SELECT MAX(b) FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 (id int , b varchar(512), INDEX(b(250))) COLLATE latin1_bin; +INSERT INTO t1 VALUES + (1,CONCAT(REPEAT('_', 250), "qq")), (1,CONCAT(REPEAT('_', 250), "zz")), + (1,CONCAT(REPEAT('_', 250), "aa")), (1,CONCAT(REPEAT('_', 250), "ff")); + +SELECT MAX(b) FROM t1; +EXPLAIN SELECT MAX(b) FROM t1; +DROP TABLE t1; + # End of 4.1 tests # diff --git a/mysql-test/t/func_in.test b/mysql-test/t/func_in.test index 8ddf1fbe314..5a5e3ec798d 100644 --- a/mysql-test/t/func_in.test +++ b/mysql-test/t/func_in.test @@ -109,6 +109,18 @@ select count(*) from t1 where id not in (1); select count(*) from t1 where id not in (1,2); drop table t1; +# +# Bug#18360 Incorrect type coercion in IN() results in false comparison +# +create table t1 (f1 char(1), f2 int); +insert into t1 values (1,0),('a',1),('z',2); +select f1 from t1 where f1 in (1,'z'); +select f2 from t1 where f2 in (1,'z'); +select f1 from t1 where 'z' in (1,f1); +select * from t1 where 'z' in (f2,f1); +select * from t1 where 1 in (f2,f1); +drop table t1; + # End of 4.1 tests # diff --git a/mysql-test/t/func_str.test b/mysql-test/t/func_str.test index 7f809dbc4a1..b13fe039261 100644 --- a/mysql-test/t/func_str.test +++ b/mysql-test/t/func_str.test @@ -673,6 +673,18 @@ drop table t1; select load_file("lkjlkj"); select ifnull(load_file("lkjlkj"),"it's null"); +# +# Bug#15351: Wrong collation used for comparison of md5() and sha() +# parameter can lead to a wrong result. +# +create table t1 (f1 varchar(4), f2 varchar(64), unique key k1 (f1,f2)); +insert into t1 values ( 'test',md5('test')), ('test', sha('test')); +select * from t1 where f1='test' and (f2= md5("test") or f2= md5("TEST")); +select * from t1 where f1='test' and (f2= md5("TEST") or f2= md5("test")); +select * from t1 where f1='test' and (f2= sha("test") or f2= sha("TEST")); +select * from t1 where f1='test' and (f2= sha("TEST") or f2= sha("test")); +drop table t1; + --echo End of 4.1 tests # diff --git a/mysql-test/t/func_time.test b/mysql-test/t/func_time.test index 6366d100bbc..976f7191a5f 100644 --- a/mysql-test/t/func_time.test +++ b/mysql-test/t/func_time.test @@ -368,6 +368,27 @@ select monthname(str_to_date(null, '%m')), monthname(str_to_date(null, '%m')), monthname(str_to_date(1, '%m')), monthname(str_to_date(0, '%m')); # +# Bug#16377 result of DATE/TIME functions were compared as strings which +# can lead to a wrong result. +# +create table t1(f1 date, f2 time, f3 datetime); +insert into t1 values ("2006-01-01", "12:01:01", "2006-01-01 12:01:01"); +insert into t1 values ("2006-01-02", "12:01:02", "2006-01-02 12:01:02"); +select f1 from t1 where f1 between "2006-1-1" and 20060101; +select f1 from t1 where f1 between "2006-1-1" and "2006.1.1"; +select f1 from t1 where date(f1) between "2006-1-1" and "2006.1.1"; +select f2 from t1 where f2 between "12:1:2" and "12:2:2"; +select f2 from t1 where time(f2) between "12:1:2" and "12:2:2"; +select f3 from t1 where f3 between "2006-1-1 12:1:1" and "2006-1-1 12:1:2"; +select f3 from t1 where timestamp(f3) between "2006-1-1 12:1:1" and "2006-1-1 12:1:2"; +select f1 from t1 where "2006-1-1" between f1 and f3; +select f1 from t1 where "2006-1-1" between date(f1) and date(f3); +select f1 from t1 where "2006-1-1" between f1 and 'zzz'; +select f1 from t1 where makedate(2006,1) between date(f1) and date(f3); +select f1 from t1 where makedate(2006,2) between date(f1) and date(f3); +drop table t1; + +# # Bug #16546 # diff --git a/mysql-test/t/join.test b/mysql-test/t/join.test index f6a57d5e230..27558a31d68 100644 --- a/mysql-test/t/join.test +++ b/mysql-test/t/join.test @@ -563,4 +563,29 @@ select a2 from ((t1 natural join t2) join t3 on b=c1) natural join t4; drop table t1,t2,t3,t4; +# +# BUG#15355: Common natural join column not resolved in prepared statement nested query +# +create table t1 (c int, b int); +create table t2 (a int, b int); +create table t3 (b int, c int); +create table t4 (y int, c int); +create table t5 (y int, z int); + +insert into t1 values (3,2); +insert into t2 values (1,2); +insert into t3 values (2,3); +insert into t4 values (1,3); +insert into t5 values (1,4); + +-- this fails +prepare stmt1 from "select * from ((t3 natural join (t1 natural join t2)) +natural join t4) natural join t5"; +execute stmt1; + +-- this works +select * from ((t3 natural join (t1 natural join t2)) natural join t4) + natural join t5; +drop table t1, t2, t3, t4, t5; + # End of tests for WL#2486 - natural/using join diff --git a/mysql-test/t/multi_update.test b/mysql-test/t/multi_update.test index 04c33e9d709..21271517564 100644 --- a/mysql-test/t/multi_update.test +++ b/mysql-test/t/multi_update.test @@ -452,6 +452,14 @@ insert into t2 values(1,null); delete t2, t1 from t2 left join t1 on (t2.aclid=t1.aclid) where t2.refid='1'; drop table t1, t2; +# +# Bug#19225: unchecked error leads to server crash +# +create table t1(a int); +create table t2(a int); +--error 1093 +delete from t1,t2 using t1,t2 where t1.a=(select a from t1); +drop table t1, t2; # End of 4.1 tests # diff --git a/mysql-test/t/replace.test b/mysql-test/t/replace.test index 10703eaafb8..269854fb180 100644 --- a/mysql-test/t/replace.test +++ b/mysql-test/t/replace.test @@ -35,3 +35,13 @@ select * from t1; drop table t1; # End of 4.1 tests + +# +# Bug#19789: REPLACE was allowed for a VIEW with CHECK OPTION enabled. +# +CREATE TABLE t1 (f1 INT); +CREATE VIEW v1 AS SELECT f1 FROM t1 WHERE f1 = 0 WITH CHECK OPTION; +--error 1369 +REPLACE INTO v1 (f1) VALUES (1); +DROP TABLE t1; +DROP VIEW v1; diff --git a/mysql-test/t/select.test b/mysql-test/t/select.test index 8cd15463c62..ae3981ce47b 100644 --- a/mysql-test/t/select.test +++ b/mysql-test/t/select.test @@ -2217,15 +2217,6 @@ show table status like 't1%'; select 123 as a from t1 where f1 is null; drop table t1,t11; -# Bug 7672 Unknown column error in order clause -# -CREATE TABLE t1 (a INT, b INT); -(SELECT a, b AS c FROM t1) ORDER BY c+1; -(SELECT a, b AS c FROM t1) ORDER BY b+1; -SELECT a, b AS c FROM t1 ORDER BY c+1; -SELECT a, b AS c FROM t1 ORDER BY b+1; -drop table t1; - # # Bug #3874 (function in GROUP and LEFT JOIN) # @@ -2265,6 +2256,21 @@ select * from t1 where f1 in (select f3 from t2 where (f3,f4)= (select f3,f4 fro drop table t1,t2; # +# Bug #4981: 4.x and 5.x produce non-optimal execution path, 3.23 regression test failure +# +CREATE TABLE t1(a int, b int, c int, KEY b(b), KEY c(c)); +insert into t1 values (1,0,0),(2,0,0); +CREATE TABLE t2 (a int, b varchar(2), c varchar(2), PRIMARY KEY(a)); +insert into t2 values (1,'',''), (2,'',''); +CREATE TABLE t3 (a int, b int, PRIMARY KEY (a,b), KEY a (a), KEY b (b)); +insert into t3 values (1,1),(1,2); +# must have "range checked" for t2 +explain select straight_join DISTINCT t2.a,t2.b, t1.c from t1, t3, t2 + where (t1.c=t2.a or (t1.c=t3.a and t2.a=t3.b)) and t1.b=556476786 and + t2.b like '%%' order by t2.b limit 0,1; +DROP TABLE t1,t2,t3; + +# # Bug #17873: confusing error message when IGNORE INDEX refers a column name # @@ -2282,48 +2288,6 @@ DROP TABLE t1; # End of 4.1 tests # -# Test case for bug 7098: substitution of a constant for a string field -# - -CREATE TABLE t1 ( city char(30) ); -INSERT INTO t1 VALUES ('London'); -INSERT INTO t1 VALUES ('Paris'); - -SELECT * FROM t1 WHERE city='London'; -SELECT * FROM t1 WHERE city='london'; -EXPLAIN SELECT * FROM t1 WHERE city='London' AND city='london'; -SELECT * FROM t1 WHERE city='London' AND city='london'; -EXPLAIN SELECT * FROM t1 WHERE city LIKE '%london%' AND city='London'; -SELECT * FROM t1 WHERE city LIKE '%london%' AND city='London'; - -DROP TABLE t1; - -# -# Bug#7425 inconsistent sort order on unsigned columns result of substraction -# - -create table t1 (a int(11) unsigned, b int(11) unsigned); -insert into t1 values (1,0), (1,1), (1,2); -select a-b from t1 order by 1; -select a-b , (a-b < 0) from t1 order by 1; -select a-b as d, (a-b >= 0), b from t1 group by b having d >= 0; -select cast((a - b) as unsigned) from t1 order by 1; -drop table t1; - - -# -# Bug#8733 server accepts malformed query (multiply mentioned distinct) -# -create table t1 (a int(11)); -select all all * from t1; -select distinct distinct * from t1; ---error 1221 -select all distinct * from t1; ---error 1221 -select distinct all * from t1; -drop table t1; - -# # Test for bug #6474 # @@ -2358,21 +2322,6 @@ SELECT K2C4, K4N4, F2I4 FROM t1 DROP TABLE t1; # -# Test case for bug 7520: a wrong cost of the index for a BLOB field -# - -CREATE TABLE t1 ( a BLOB, INDEX (a(20)) ); -CREATE TABLE t2 ( a BLOB, INDEX (a(20)) ); - -INSERT INTO t1 VALUES ('one'),('two'),('three'),('four'),('five'); -INSERT INTO t2 VALUES ('one'),('two'),('three'),('four'),('five'); - -EXPLAIN SELECT * FROM t1 LEFT JOIN t2 USE INDEX (a) ON t1.a=t2.a; -EXPLAIN SELECT * FROM t1 LEFT JOIN t2 FORCE INDEX (a) ON t1.a=t2.a; - -DROP TABLE t1, t2; - -# # Bug#8670 # create table t1 (a int, b int); @@ -2411,34 +2360,6 @@ drop table t1; # -# Bug#8733 server accepts malformed query (multiply mentioned distinct) -# -create table t1 (a int(11)); -select all all * from t1; -select distinct distinct * from t1; ---error 1221 -select all distinct * from t1; ---error 1221 -select distinct all * from t1; -drop table t1; - - -# -# Test case for bug 7520: a wrong cost of the index for a BLOB field -# - -CREATE TABLE t1 ( a BLOB, INDEX (a(20)) ); -CREATE TABLE t2 ( a BLOB, INDEX (a(20)) ); - -INSERT INTO t1 VALUES ('one'),('two'),('three'),('four'),('five'); -INSERT INTO t2 VALUES ('one'),('two'),('three'),('four'),('five'); - -EXPLAIN SELECT * FROM t1 LEFT JOIN t2 USE INDEX (a) ON t1.a=t2.a; -EXPLAIN SELECT * FROM t1 LEFT JOIN t2 FORCE INDEX (a) ON t1.a=t2.a; - -DROP TABLE t1, t2; - -# # Test for bug #10084: STRAIGHT_JOIN with ON expression # @@ -2935,3 +2856,29 @@ EXPLAIN SELECT a, c, d, f FROM t1,t2 WHERE a=c AND b BETWEEN 4 AND 6 AND a > 0; DROP TABLE t1, t2; + +# +# Bug #18895: BIT values cause joins to fail +# +create table t1 ( + a int unsigned not null auto_increment primary key, + b bit not null, + c bit not null +); + +create table t2 ( + a int unsigned not null auto_increment primary key, + b bit not null, + c int unsigned not null, + d varchar(50) +); + +insert into t1 (b,c) values (0,1), (0,1); +insert into t2 (b,c) values (0,1); + +-- Row 1 should succeed. Row 2 should fail. Both fail. +select t1.a, t1.b + 0, t1.c + 0, t2.a, t2.b + 0, t2.c, t2.d +from t1 left outer join t2 on t1.a = t2.c and t2.b <> 1 +where t1.b <> 1 order by t1.a; + +drop table t1,t2; diff --git a/mysql-test/t/union.test b/mysql-test/t/union.test index 692f1f509fa..7dfe4ac482f 100644 --- a/mysql-test/t/union.test +++ b/mysql-test/t/union.test @@ -793,3 +793,51 @@ select id from t1 union all select 99 order by 1; drop table t1; # End of 4.1 tests + +# +# Bug#18175: Union select over 129 tables with a sum function fails. +# +(select avg(1)) union (select avg(1)) union (select avg(1)) union +(select avg(1)) union (select avg(1)) union (select avg(1)) union +(select avg(1)) union (select avg(1)) union (select avg(1)) union +(select avg(1)) union (select avg(1)) union (select avg(1)) union +(select avg(1)) union (select avg(1)) union (select avg(1)) union +(select avg(1)) union (select avg(1)) union (select avg(1)) union +(select avg(1)) union (select avg(1)) union (select avg(1)) union +(select avg(1)) union (select avg(1)) union (select avg(1)) union +(select avg(1)) union (select avg(1)) union (select avg(1)) union +(select avg(1)) union (select avg(1)) union (select avg(1)) union +(select avg(1)) union (select avg(1)) union (select avg(1)) union +(select avg(1)) union (select avg(1)) union (select avg(1)) union +(select avg(1)) union (select avg(1)) union (select avg(1)) union +(select avg(1)) union (select avg(1)) union (select avg(1)) union +(select avg(1)) union (select avg(1)) union (select avg(1)) union +(select avg(1)) union (select avg(1)) union (select avg(1)) union +(select avg(1)) union (select avg(1)) union (select avg(1)) union +(select avg(1)) union (select avg(1)) union (select avg(1)) union +(select avg(1)) union (select avg(1)) union (select avg(1)) union +(select avg(1)) union (select avg(1)) union (select avg(1)) union +(select avg(1)) union (select avg(1)) union (select avg(1)) union +(select avg(1)) union (select avg(1)) union (select avg(1)) union +(select avg(1)) union (select avg(1)) union (select avg(1)) union +(select avg(1)) union (select avg(1)) union (select avg(1)) union +(select avg(1)) union (select avg(1)) union (select avg(1)) union +(select avg(1)) union (select avg(1)) union (select avg(1)) union +(select avg(1)) union (select avg(1)) union (select avg(1)) union +(select avg(1)) union (select avg(1)) union (select avg(1)) union +(select avg(1)) union (select avg(1)) union (select avg(1)) union +(select avg(1)) union (select avg(1)) union (select avg(1)) union +(select avg(1)) union (select avg(1)) union (select avg(1)) union +(select avg(1)) union (select avg(1)) union (select avg(1)) union +(select avg(1)) union (select avg(1)) union (select avg(1)) union +(select avg(1)) union (select avg(1)) union (select avg(1)) union +(select avg(1)) union (select avg(1)) union (select avg(1)) union +(select avg(1)) union (select avg(1)) union (select avg(1)) union +(select avg(1)) union (select avg(1)) union (select avg(1)) union +(select avg(1)) union (select avg(1)) union (select avg(1)) union +(select avg(1)) union (select avg(1)) union (select avg(1)) union +(select avg(1)) union (select avg(1)) union (select avg(1)) union +(select avg(1)) union (select avg(1)) union (select avg(1)) union +(select avg(1)) union (select avg(1)) union (select avg(1)) union +(select avg(1)) union (select avg(1)) union (select avg(1)); + diff --git a/sql/field.cc b/sql/field.cc index fa018b3eaba..df735c59e08 100644 --- a/sql/field.cc +++ b/sql/field.cc @@ -4604,11 +4604,11 @@ int Field_timestamp::store(const char *from,uint len,CHARSET_INFO *cs) int error; bool have_smth_to_conv; my_bool in_dst_time_gap; - THD *thd= table->in_use; + THD *thd= table ? table->in_use : current_thd; /* We don't want to store invalid or fuzzy datetime values in TIMESTAMP */ have_smth_to_conv= (str_to_datetime(from, len, &l_time, - (table->in_use->variables.sql_mode & + (thd->variables.sql_mode & MODE_NO_ZERO_DATE) | MODE_NO_ZERO_IN_DATE, &error) > MYSQL_TIMESTAMP_ERROR); @@ -4674,7 +4674,7 @@ int Field_timestamp::store(longlong nr, bool unsigned_val) my_time_t timestamp= 0; int error; my_bool in_dst_time_gap; - THD *thd= table->in_use; + THD *thd= table ? table->in_use : current_thd; /* We don't want to store invalid or fuzzy datetime values in TIMESTAMP */ longlong tmp= number_to_datetime(nr, &l_time, (thd->variables.sql_mode & @@ -4730,7 +4730,7 @@ longlong Field_timestamp::val_int(void) ASSERT_COLUMN_MARKED_FOR_READ; uint32 temp; TIME time_tmp; - THD *thd= table->in_use; + THD *thd= table ? table->in_use : current_thd; #ifdef WORDS_BIGENDIAN if (table->s->db_low_byte_first) @@ -4756,7 +4756,7 @@ String *Field_timestamp::val_str(String *val_buffer, String *val_ptr) ASSERT_COLUMN_MARKED_FOR_READ; uint32 temp, temp2; TIME time_tmp; - THD *thd= table->in_use; + THD *thd= table ? table->in_use : current_thd; char *to; val_buffer->alloc(field_length+1); @@ -4827,7 +4827,7 @@ String *Field_timestamp::val_str(String *val_buffer, String *val_ptr) bool Field_timestamp::get_date(TIME *ltime, uint fuzzydate) { long temp; - THD *thd= table->in_use; + THD *thd= table ? table->in_use : current_thd; #ifdef WORDS_BIGENDIAN if (table->s->db_low_byte_first) temp=uint4korr(ptr); @@ -4910,7 +4910,8 @@ void Field_timestamp::sql_type(String &res) const void Field_timestamp::set_time() { - long tmp= (long) table->in_use->query_start(); + THD *thd= table ? table->in_use : current_thd; + long tmp= (long) thd->query_start(); set_notnull(); #ifdef WORDS_BIGENDIAN if (table->s->db_low_byte_first) @@ -5107,12 +5108,13 @@ String *Field_time::val_str(String *val_buffer, bool Field_time::get_date(TIME *ltime, uint fuzzydate) { long tmp; + THD *thd= table ? table->in_use : current_thd; if (!(fuzzydate & TIME_FUZZY_DATE)) { - push_warning_printf(table->in_use, MYSQL_ERROR::WARN_LEVEL_WARN, + push_warning_printf(thd, MYSQL_ERROR::WARN_LEVEL_WARN, ER_WARN_DATA_OUT_OF_RANGE, ER(ER_WARN_DATA_OUT_OF_RANGE), field_name, - table->in_use->row_count); + thd->row_count); return 1; } tmp=(long) sint3korr(ptr); @@ -5305,9 +5307,10 @@ int Field_date::store(const char *from, uint len,CHARSET_INFO *cs) TIME l_time; uint32 tmp; int error; + THD *thd= table ? table->in_use : current_thd; if (str_to_datetime(from, len, &l_time, TIME_FUZZY_DATE | - (table->in_use->variables.sql_mode & + (thd->variables.sql_mode & (MODE_NO_ZERO_IN_DATE | MODE_NO_ZERO_DATE | MODE_INVALID_DATES)), &error) <= MYSQL_TIMESTAMP_ERROR) @@ -5361,9 +5364,10 @@ int Field_date::store(longlong nr, bool unsigned_val) TIME not_used; int error; longlong initial_nr= nr; + THD *thd= table ? table->in_use : current_thd; nr= number_to_datetime(nr, ¬_used, (TIME_FUZZY_DATE | - (table->in_use->variables.sql_mode & + (thd->variables.sql_mode & (MODE_NO_ZERO_IN_DATE | MODE_NO_ZERO_DATE | MODE_INVALID_DATES))), &error); @@ -5513,9 +5517,10 @@ int Field_newdate::store(const char *from,uint len,CHARSET_INFO *cs) TIME l_time; long tmp; int error; + THD *thd= table ? table->in_use : current_thd; if (str_to_datetime(from, len, &l_time, (TIME_FUZZY_DATE | - (table->in_use->variables.sql_mode & + (thd->variables.sql_mode & (MODE_NO_ZERO_IN_DATE | MODE_NO_ZERO_DATE | MODE_INVALID_DATES))), &error) <= MYSQL_TIMESTAMP_ERROR) @@ -5554,9 +5559,10 @@ int Field_newdate::store(longlong nr, bool unsigned_val) TIME l_time; longlong tmp; int error; + THD *thd= table ? table->in_use : current_thd; if (number_to_datetime(nr, &l_time, (TIME_FUZZY_DATE | - (table->in_use->variables.sql_mode & + (thd->variables.sql_mode & (MODE_NO_ZERO_IN_DATE | MODE_NO_ZERO_DATE | MODE_INVALID_DATES))), &error) == LL(-1)) @@ -5704,10 +5710,11 @@ int Field_datetime::store(const char *from,uint len,CHARSET_INFO *cs) int error; ulonglong tmp= 0; enum enum_mysql_timestamp_type func_res; + THD *thd= table ? table->in_use : current_thd; func_res= str_to_datetime(from, len, &time_tmp, (TIME_FUZZY_DATE | - (table->in_use->variables.sql_mode & + (thd->variables.sql_mode & (MODE_NO_ZERO_IN_DATE | MODE_NO_ZERO_DATE | MODE_INVALID_DATES))), &error); @@ -5755,9 +5762,10 @@ int Field_datetime::store(longlong nr, bool unsigned_val) TIME not_used; int error; longlong initial_nr= nr; + THD *thd= table ? table->in_use : current_thd; nr= number_to_datetime(nr, ¬_used, (TIME_FUZZY_DATE | - (table->in_use->variables.sql_mode & + (thd->variables.sql_mode & (MODE_NO_ZERO_IN_DATE | MODE_NO_ZERO_DATE | MODE_INVALID_DATES))), &error); @@ -6173,17 +6181,6 @@ int Field_string::cmp(const char *a_ptr, const char *b_ptr) { uint a_len, b_len; - if (field_charset->strxfrm_multiply > 1) - { - /* - We have to remove end space to be able to compare multi-byte-characters - like in latin_de 'ae' and 0xe4 - */ - return field_charset->coll->strnncollsp(field_charset, - (const uchar*) a_ptr, field_length, - (const uchar*) b_ptr, - field_length, 0); - } if (field_charset->mbmaxlen != 1) { uint char_len= field_length/field_charset->mbmaxlen; @@ -6192,8 +6189,14 @@ int Field_string::cmp(const char *a_ptr, const char *b_ptr) } else a_len= b_len= field_length; - return my_strnncoll(field_charset,(const uchar*) a_ptr, a_len, - (const uchar*) b_ptr, b_len); + /* + We have to remove end space to be able to compare multi-byte-characters + like in latin_de 'ae' and 0xe4 + */ + return field_charset->coll->strnncollsp(field_charset, + (const uchar*) a_ptr, a_len, + (const uchar*) b_ptr, b_len, + 0); } @@ -9247,7 +9250,11 @@ bool Field::set_warning(MYSQL_ERROR::enum_warning_level level, uint code, int cuted_increment) { - THD *thd= table->in_use; + /* + If this field was created only for type conversion purposes it + will have table == NULL. + */ + THD *thd= table ? table->in_use : current_thd; if (thd->count_cuted_fields) { thd->cuted_fields+= cuted_increment; @@ -9282,9 +9289,10 @@ Field::set_datetime_warning(MYSQL_ERROR::enum_warning_level level, uint code, const char *str, uint str_length, timestamp_type ts_type, int cuted_increment) { - if (table->in_use->really_abort_on_warning() || + THD *thd= table ? table->in_use : current_thd; + if (thd->really_abort_on_warning() || set_warning(level, code, cuted_increment)) - make_truncated_value_warning(table->in_use, str, str_length, ts_type, + make_truncated_value_warning(thd, str, str_length, ts_type, field_name); } @@ -9311,13 +9319,13 @@ Field::set_datetime_warning(MYSQL_ERROR::enum_warning_level level, uint code, longlong nr, timestamp_type ts_type, int cuted_increment) { - if (table->in_use->really_abort_on_warning() || + THD *thd= table ? table->in_use : current_thd; + if (thd->really_abort_on_warning() || set_warning(level, code, cuted_increment)) { char str_nr[22]; char *str_end= longlong10_to_str(nr, str_nr, -10); - make_truncated_value_warning(table->in_use, str_nr, - (uint) (str_end - str_nr), + make_truncated_value_warning(thd, str_nr, (uint) (str_end - str_nr), ts_type, field_name); } } @@ -9343,13 +9351,15 @@ void Field::set_datetime_warning(MYSQL_ERROR::enum_warning_level level, uint code, double nr, timestamp_type ts_type) { - if (table->in_use->really_abort_on_warning() || + THD *thd= table ? table->in_use : current_thd; + if (thd->really_abort_on_warning() || set_warning(level, code, 1)) { /* DBL_DIG is enough to print '-[digits].E+###' */ char str_nr[DBL_DIG + 8]; uint str_len= my_sprintf(str_nr, (str_nr, "%g", nr)); - make_truncated_value_warning(table->in_use, str_nr, str_len, ts_type, + make_truncated_value_warning(thd, str_nr, str_len, ts_type, field_name); } } + diff --git a/sql/field.h b/sql/field.h index 2ac7ec2c69d..69cb641f158 100644 --- a/sql/field.h +++ b/sql/field.h @@ -124,7 +124,7 @@ public: static bool type_can_have_key_part(enum_field_types); static enum_field_types field_type_merge(enum_field_types, enum_field_types); static Item_result result_merge_type(enum_field_types); - bool eq(Field *field) + virtual bool eq(Field *field) { return (ptr == field->ptr && null_ptr == field->null_ptr && null_bit == field->null_bit); @@ -1387,6 +1387,13 @@ public: bit_ptr= bit_ptr_arg; bit_ofs= bit_ofs_arg; } + bool eq(Field *field) + { + return (Field::eq(field) && + field->type() == type() && + bit_ptr == ((Field_bit *)field)->bit_ptr && + bit_ofs == ((Field_bit *)field)->bit_ofs); + } void move_field_offset(my_ptrdiff_t ptr_diff) { Field::move_field_offset(ptr_diff); diff --git a/sql/item.h b/sql/item.h index 54cab34eb78..a6132aba8b0 100644 --- a/sql/item.h +++ b/sql/item.h @@ -857,6 +857,14 @@ public: { return 0; } + /* + result_as_longlong() must return TRUE for Items representing DATE/TIME + functions and DATE/TIME table fields. + Those Items have result_type()==STRING_RESULT (and not INT_RESULT), but + their values should be compared as integers (because the integer + representation is more precise than the string one). + */ + virtual bool result_as_longlong() { return FALSE; } }; @@ -1283,6 +1291,10 @@ public: bool register_field_in_read_map(byte *arg); bool check_partition_func_processor(byte *bool_arg) { return 0; } void cleanup(); + bool result_as_longlong() + { + return field->can_be_compared_as_longlong(); + } Item_equal *find_item_equal(COND_EQUAL *cond_equal); Item *equal_fields_propagator(byte *arg); Item *set_no_const_sub(byte *arg); @@ -1904,6 +1916,10 @@ public: bool walk(Item_processor processor, bool walk_subquery, byte *arg) { return (*ref)->walk(processor, walk_subquery, arg); } void print(String *str); + bool result_as_longlong() + { + return (*ref)->result_as_longlong(); + } void cleanup(); Item_field *filed_for_view_update() { return (*ref)->filed_for_view_update(); } diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc index 3a1f4b50458..a3464c3bc25 100644 --- a/sql/item_cmpfunc.cc +++ b/sql/item_cmpfunc.cc @@ -63,25 +63,144 @@ static void agg_result_type(Item_result *type, Item **items, uint nitems) } +/* + Aggregates result types from the array of items. + + SYNOPSIS: + agg_cmp_type() + thd thread handle + type [out] the aggregated type + items array of items to aggregate the type from + nitems number of items in the array + + DESCRIPTION + This function aggregates result types from the array of items. Found type + supposed to be used later for comparison of values of these items. + Aggregation itself is performed by the item_cmp_type() function. + + NOTES + Aggregation rules: + If all items are constants the type will be aggregated from all items. + If there are some non-constant items then only types of non-constant + items will be used for aggregation. + If there are DATE/TIME fields/functions in the list and no string + fields/functions in the list then: + The INT_RESULT type will be used for aggregation instead of original + result type of any DATE/TIME field/function in the list + All constant items in the list will be converted to a DATE/TIME using + found field or result field of found function. + + Implementation notes: + The code is equivalent to: + 1. Check the list for presence of a STRING field/function. + Collect the is_const flag. + 2. Get a Field* object to use for type coercion + 3. Perform type conversion. + 1 and 2 are implemented in 2 loops. The first searches for a DATE/TIME + field/function and checks presence of a STRING field/function. + The second loop works only if a DATE/TIME field/function is found. + It checks presence of a STRING field/function in the rest of the list. + + TODO + 1) The current implementation can produce false comparison results for + expressions like: + date_time_field BETWEEN string_field_with_dates AND string_constant + if the string_constant will omit some of leading zeroes. + In order to fully implement correct comparison of DATE/TIME the new + DATETIME_RESULT result type should be introduced and agg_cmp_type() + should return the DATE/TIME field used for the conversion. Later + this field can be used by comparison functions like Item_func_between to + convert string values to ints on the fly and thus return correct results. + This modification will affect functions BETWEEN, IN and CASE. + + 2) If in the list a DATE field/function and a DATETIME field/function + are present in the list then the first found field/function will be + used for conversion. This may lead to wrong results and probably should + be fixed. +*/ + static void agg_cmp_type(THD *thd, Item_result *type, Item **items, uint nitems) { uint i; + Item::Type res= (Item::Type)0; + /* Used only for date/time fields, max_length = 19 */ + char buff[20]; + uchar null_byte; Field *field= NULL; - /* If the first argument is a FIELD_ITEM, pull out the field. */ - if (items[0]->real_item()->type() == Item::FIELD_ITEM) - field=((Item_field *)(items[0]->real_item()))->field; - /* But if it can't be compared as a longlong, we don't really care. */ - if (field && !field->can_be_compared_as_longlong()) - field= NULL; + /* Search for date/time fields/functions */ + for (i= 0; i < nitems; i++) + { + if (!items[i]->result_as_longlong()) + { + /* Do not convert anything if a string field/function is present */ + if (!items[i]->const_item() && items[i]->result_type() == STRING_RESULT) + { + i= nitems; + break; + } + continue; + } + if ((res= items[i]->real_item()->type()) == Item::FIELD_ITEM) + { + field= ((Item_field *)items[i]->real_item())->field; + break; + } + else if (res == Item::FUNC_ITEM) + { + field= items[i]->tmp_table_field_from_field_type(0); + if (field) + field->move_field(buff, &null_byte, 0); + break; + } + } + if (field) + { + /* Check the rest of the list for presence of a string field/function. */ + for (i++ ; i < nitems; i++) + { + if (!items[i]->const_item() && items[i]->result_type() == STRING_RESULT && + !items[i]->result_as_longlong()) + { + if (res == Item::FUNC_ITEM) + delete field; + field= 0; + break; + } + } + } + /* Reset to 0 on first occurence of non-const item. 1 otherwise */ + bool is_const= items[0]->const_item(); + /* + If the first item is a date/time function then its result should be + compared as int + */ + if (field) + { + /* Suppose we are comparing dates and some non-constant items are present. */ + type[0]= INT_RESULT; + is_const= 0; + } + else + type[0]= items[0]->result_type(); - type[0]= items[0]->result_type(); - for (i= 1; i < nitems; i++) + for (i= 0; i < nitems ; i++) { - type[0]= item_cmp_type(type[0], items[i]->result_type()); - if (field && convert_constant_item(thd, field, &items[i])) - type[0]= INT_RESULT; + if (!items[i]->const_item()) + { + Item_result result= field && items[i]->result_as_longlong() ? + INT_RESULT : items[i]->result_type(); + type[0]= is_const ? result : item_cmp_type(type[0], result); + is_const= 0; + } + else if (is_const) + type[0]= item_cmp_type(type[0], items[i]->result_type()); + else if (field) + convert_constant_item(thd, field, &items[i]); } + + if (res == Item::FUNC_ITEM && field) + delete field; } @@ -238,14 +357,6 @@ static bool convert_constant_item(THD *thd, Field *field, Item **item) if (!(*item)->with_subselect && (*item)->const_item()) { /* For comparison purposes allow invalid dates like 2000-01-32 */ - TABLE *table= field->table; - ulong orig_sql_mode= table->in_use->variables.sql_mode; - my_bitmap_map *old_write_map= - dbug_tmp_use_all_columns(table, table->write_set); - my_bitmap_map *old_read_map= - dbug_tmp_use_all_columns(table, table->read_set); - - table->in_use->variables.sql_mode|= MODE_INVALID_DATES; if (!(*item)->save_in_field(field, 1) && !((*item)->null_value)) { Item *tmp= new Item_int_with_ref(field->val_int(), *item, @@ -1106,9 +1217,8 @@ void Item_func_between::fix_length_and_dec() return; agg_cmp_type(thd, &cmp_type, args, 3); - if (cmp_type == STRING_RESULT && - agg_arg_charsets(cmp_collation, args, 3, MY_COLL_CMP_CONV)) - return; + if (cmp_type == STRING_RESULT) + agg_arg_charsets(cmp_collation, args, 3, MY_COLL_CMP_CONV); } diff --git a/sql/item_cmpfunc.h b/sql/item_cmpfunc.h index 8bd1b53e226..82cb5febe7d 100644 --- a/sql/item_cmpfunc.h +++ b/sql/item_cmpfunc.h @@ -46,7 +46,7 @@ public: inline int set_compare_func(Item_bool_func2 *owner_arg) { return set_compare_func(owner_arg, item_cmp_type((*a)->result_type(), - (*b)->result_type())); + (*b)->result_type())); } inline int set_cmp_func(Item_bool_func2 *owner_arg, Item **a1, Item **a2, @@ -59,8 +59,9 @@ public: inline int set_cmp_func(Item_bool_func2 *owner_arg, Item **a1, Item **a2) { - return set_cmp_func(owner_arg, a1, a2, item_cmp_type((*a1)->result_type(), - (*a2)->result_type())); + return set_cmp_func(owner_arg, a1, a2, + item_cmp_type((*a1)->result_type(), + (*a2)->result_type())); } inline int compare() { return (this->*func)(); } diff --git a/sql/item_func.cc b/sql/item_func.cc index 91bb38c8f8a..8139ba81777 100644 --- a/sql/item_func.cc +++ b/sql/item_func.cc @@ -3288,7 +3288,7 @@ longlong Item_func_last_insert_id::val_int() return value; // Avoid side effect of insert_id() } thd->lex->uncacheable(UNCACHEABLE_SIDEEFFECT); - return thd->insert_id(); + return thd->last_insert_id_used ? thd->current_insert_id : thd->insert_id(); } /* This function is just used to test speed of different functions */ diff --git a/sql/item_strfunc.cc b/sql/item_strfunc.cc index 3dead7fe1e7..bffaa163a05 100644 --- a/sql/item_strfunc.cc +++ b/sql/item_strfunc.cc @@ -154,7 +154,15 @@ String *Item_func_md5::val_str(String *str) void Item_func_md5::fix_length_and_dec() { - max_length=32; + max_length=32; + /* + The MD5() function treats its parameter as being a case sensitive. Thus + we set binary collation on it so different instances of MD5() will be + compared properly. + */ + args[0]->collation.set( + get_charset_by_csname(args[0]->collation.collation->csname, + MY_CS_BINSORT,MYF(0)), DERIVATION_COERCIBLE); } @@ -195,7 +203,15 @@ String *Item_func_sha::val_str(String *str) void Item_func_sha::fix_length_and_dec() { - max_length=SHA1_HASH_SIZE*2; // size of hex representation of hash + max_length=SHA1_HASH_SIZE*2; // size of hex representation of hash + /* + The SHA() function treats its parameter as being a case sensitive. Thus + we set binary collation on it so different instances of MD5() will be + compared properly. + */ + args[0]->collation.set( + get_charset_by_csname(args[0]->collation.collation->csname, + MY_CS_BINSORT,MYF(0)), DERIVATION_COERCIBLE); } @@ -288,11 +304,14 @@ String *Item_func_concat::val_str(String *str) DBUG_ASSERT(fixed == 1); String *res,*res2,*use_as_buff; uint i; + bool is_const= 0; null_value=0; if (!(res=args[0]->val_str(str))) goto null; use_as_buff= &tmp_value; + /* Item_subselect in --ps-protocol mode will state it as a non-const */ + is_const= args[0]->const_item() || !args[0]->used_tables(); for (i=1 ; i < arg_count ; i++) { if (res->length() == 0) @@ -315,7 +334,7 @@ String *Item_func_concat::val_str(String *str) current_thd->variables.max_allowed_packet); goto null; } - if (res->alloced_length() >= res->length()+res2->length()) + if (!is_const && res->alloced_length() >= res->length()+res2->length()) { // Use old buffer res->append(*res2); } @@ -370,6 +389,7 @@ String *Item_func_concat::val_str(String *str) res= &tmp_value; use_as_buff=str; } + is_const= 0; } } res->set_charset(collation.collation); @@ -389,7 +409,14 @@ void Item_func_concat::fix_length_and_dec() return; for (uint i=0 ; i < arg_count ; i++) - max_result_length+= args[i]->max_length; + { + if (args[i]->collation.collation->mbmaxlen != collation.collation->mbmaxlen) + max_result_length+= (args[i]->max_length / + args[i]->collation.collation->mbmaxlen) * + collation.collation->mbmaxlen; + else + max_result_length+= args[i]->max_length; + } if (max_result_length >= MAX_BLOB_WIDTH) { diff --git a/sql/item_timefunc.cc b/sql/item_timefunc.cc index 4ffb3324a5b..d38069e54da 100644 --- a/sql/item_timefunc.cc +++ b/sql/item_timefunc.cc @@ -2344,6 +2344,20 @@ String *Item_datetime_typecast::val_str(String *str) } +longlong Item_datetime_typecast::val_int() +{ + DBUG_ASSERT(fixed == 1); + TIME ltime; + if (get_arg0_date(<ime,1)) + { + null_value= 1; + return 0; + } + + return TIME_to_ulonglong_datetime(<ime); +} + + bool Item_time_typecast::get_time(TIME *ltime) { bool res= get_arg0_time(ltime); @@ -2358,6 +2372,17 @@ bool Item_time_typecast::get_time(TIME *ltime) } +longlong Item_time_typecast::val_int() +{ + TIME ltime; + if (get_time(<ime)) + { + null_value= 1; + return 0; + } + return ltime.hour * 10000L + ltime.minute * 100 + ltime.second; +} + String *Item_time_typecast::val_str(String *str) { DBUG_ASSERT(fixed == 1); @@ -2397,6 +2422,14 @@ String *Item_date_typecast::val_str(String *str) return 0; } +longlong Item_date_typecast::val_int() +{ + DBUG_ASSERT(fixed == 1); + TIME ltime; + if (args[0]->get_date(<ime, TIME_FUZZY_DATE)) + return 0; + return (longlong) (ltime.year * 10000L + ltime.month * 100 + ltime.day); +} /* MAKEDATE(a,b) is a date function that creates a date value @@ -2433,6 +2466,33 @@ err: } +longlong Item_func_makedate::val_int() +{ + DBUG_ASSERT(fixed == 1); + TIME l_time; + long daynr= (long) args[1]->val_int(); + long yearnr= (long) args[0]->val_int(); + long days; + + if (args[0]->null_value || args[1]->null_value || + yearnr < 0 || daynr <= 0) + goto err; + + days= calc_daynr(yearnr,1,1) + daynr - 1; + /* Day number from year 0 to 9999-12-31 */ + if (days >= 0 && days < MAX_DAY_NUMBER) + { + null_value=0; + get_date_from_daynr(days,&l_time.year,&l_time.month,&l_time.day); + return (longlong) (l_time.year * 10000L + l_time.month * 100 + l_time.day); + } + +err: + null_value= 1; + return 0; +} + + void Item_func_add_time::fix_length_and_dec() { enum_field_types arg0_field_type; diff --git a/sql/item_timefunc.h b/sql/item_timefunc.h index 513ef692ed7..e3f10c9716f 100644 --- a/sql/item_timefunc.h +++ b/sql/item_timefunc.h @@ -365,6 +365,7 @@ public: { return tmp_table_field_from_field_type(table, 0); } + bool result_as_longlong() { return TRUE; } }; @@ -380,6 +381,7 @@ public: { return tmp_table_field_from_field_type(table, 0); } + bool result_as_longlong() { return TRUE; } }; @@ -409,6 +411,7 @@ public: TIME representation using UTC-SYSTEM or per-thread time zone. */ virtual void store_now_in_TIME(TIME *now_time)=0; + bool result_as_longlong() { return TRUE; } }; @@ -647,6 +650,7 @@ public: { return tmp_table_field_from_field_type(table, 0); } + bool result_as_longlong() { return TRUE; } bool check_partition_func_processor(byte *bool_arg) { return 0;} }; @@ -768,6 +772,8 @@ public: max_length= 10; maybe_null= 1; } + bool result_as_longlong() { return TRUE; } + longlong val_int(); }; @@ -784,6 +790,8 @@ public: { return tmp_table_field_from_field_type(table, 0); } + bool result_as_longlong() { return TRUE; } + longlong val_int(); }; @@ -799,6 +807,8 @@ public: { return tmp_table_field_from_field_type(table, 0); } + bool result_as_longlong() { return TRUE; } + longlong val_int(); }; class Item_func_makedate :public Item_str_func @@ -817,6 +827,8 @@ public: { return tmp_table_field_from_field_type(table, 0); } + bool result_as_longlong() { return TRUE; } + longlong val_int(); bool check_partition_func_processor(byte *bool_arg) { return 0;} }; diff --git a/sql/opt_range.cc b/sql/opt_range.cc index 7fa47b0b005..4547b9e8100 100644 --- a/sql/opt_range.cc +++ b/sql/opt_range.cc @@ -107,7 +107,7 @@ public: uint8 min_flag, uint8 max_flag, uint8 maybe_flag); SEL_ARG(enum Type type_arg) :elements(1),use_count(1),left(0),next_key_part(0),color(BLACK), - type(type_arg) + type(type_arg),min_flag(0) {} inline bool is_same(SEL_ARG *arg) { diff --git a/sql/opt_sum.cc b/sql/opt_sum.cc index ce3f5c5f108..e87a478b8d6 100644 --- a/sql/opt_sum.cc +++ b/sql/opt_sum.cc @@ -651,7 +651,8 @@ static bool matching_cond(bool max_fl, TABLE_REF *ref, KEY *keyinfo, } else { - store_val_in_field(part->field, args[between && max_fl ? 2 : 1]); + store_val_in_field(part->field, args[between && max_fl ? 2 : 1], + CHECK_FIELD_IGNORE); if (part->null_bit) *key_ptr++= (byte) test(part->field->is_null()); part->field->get_key_image((char*) key_ptr, part->length, Field::itRAW); @@ -706,6 +707,8 @@ static bool matching_cond(bool max_fl, TABLE_REF *ref, KEY *keyinfo, field BETWEEN const1 AND const2 3. all references to the columns from the same table as column field occur only in conjucts mentioned above. + 4. each of k first components the index is not partial, i.e. is not + defined on a fixed length proper prefix of the field. If such an index exists the function through the ref parameter returns the key value to find max/min for the field using the index, @@ -715,8 +718,8 @@ static bool matching_cond(bool max_fl, TABLE_REF *ref, KEY *keyinfo, of the whole search key) NOTE - This function may set table->key_read to 1, which must be reset after - index is used! (This can only happen when function returns 1) + This function may set table->key_read to 1, which must be reset after + index is used! (This can only happen when function returns 1) RETURN 0 Index can not be used to optimize MIN(field)/MAX(field) @@ -750,6 +753,12 @@ static bool find_key_for_maxmin(bool max_fl, TABLE_REF *ref, if (!(table->file->index_flags(idx, jdx, 0) & HA_READ_ORDER)) return 0; + /* Check whether the index component is partial */ + Field *part_field= table->field[part->fieldnr-1]; + if ((part_field->flags & BLOB_FLAG) || + part->length < part_field->key_length()) + break; + if (field->eq(part->field)) { ref->key= idx; diff --git a/sql/sql_base.cc b/sql/sql_base.cc index 9b7d46119b7..1b448a3ba18 100644 --- a/sql/sql_base.cc +++ b/sql/sql_base.cc @@ -5084,10 +5084,6 @@ static bool setup_natural_join_row_types(THD *thd, if (from_clause->elements == 0) return FALSE; /* We come here in the case of UNIONs. */ - /* For stored procedures do not redo work if already done. */ - if (!context->select_lex->first_execution) - return FALSE; - List_iterator_fast<TABLE_LIST> table_ref_it(*from_clause); TABLE_LIST *table_ref; /* Current table reference. */ /* Table reference to the left of the current. */ @@ -5100,14 +5096,18 @@ static bool setup_natural_join_row_types(THD *thd, { table_ref= left_neighbor; left_neighbor= table_ref_it++; - if (store_top_level_join_columns(thd, table_ref, - left_neighbor, right_neighbor)) - return TRUE; - if (left_neighbor) + /* For stored procedures do not redo work if already done. */ + if (context->select_lex->first_execution) { - TABLE_LIST *first_leaf_on_the_right; - first_leaf_on_the_right= table_ref->first_leaf_for_name_resolution(); - left_neighbor->next_name_resolution_table= first_leaf_on_the_right; + if (store_top_level_join_columns(thd, table_ref, + left_neighbor, right_neighbor)) + return TRUE; + if (left_neighbor) + { + TABLE_LIST *first_leaf_on_the_right; + first_leaf_on_the_right= table_ref->first_leaf_for_name_resolution(); + left_neighbor->next_name_resolution_table= first_leaf_on_the_right; + } } right_neighbor= table_ref; } diff --git a/sql/sql_class.cc b/sql/sql_class.cc index 0ede042da17..0a9e6472d9f 100644 --- a/sql/sql_class.cc +++ b/sql/sql_class.cc @@ -2121,7 +2121,9 @@ void THD::reset_sub_statement_state(Sub_statement_state *backup, backup->enable_slow_log= enable_slow_log; backup->last_insert_id= last_insert_id; backup->next_insert_id= next_insert_id; + backup->current_insert_id= current_insert_id; backup->insert_id_used= insert_id_used; + backup->last_insert_id_used= last_insert_id_used; backup->clear_next_insert_id= clear_next_insert_id; backup->limit_found_rows= limit_found_rows; backup->examined_row_count= examined_row_count; @@ -2171,7 +2173,9 @@ void THD::restore_sub_statement_state(Sub_statement_state *backup) enable_slow_log= backup->enable_slow_log; last_insert_id= backup->last_insert_id; next_insert_id= backup->next_insert_id; + current_insert_id= backup->current_insert_id; insert_id_used= backup->insert_id_used; + last_insert_id_used= backup->last_insert_id_used; clear_next_insert_id= backup->clear_next_insert_id; limit_found_rows= backup->limit_found_rows; sent_row_count= backup->sent_row_count; diff --git a/sql/sql_class.h b/sql/sql_class.h index 450a8d041c9..723dad715bd 100644 --- a/sql/sql_class.h +++ b/sql/sql_class.h @@ -763,12 +763,13 @@ class Sub_statement_state { public: ulonglong options; - ulonglong last_insert_id, next_insert_id; + ulonglong last_insert_id, next_insert_id, current_insert_id; ulonglong limit_found_rows; ha_rows cuted_fields, sent_row_count, examined_row_count; ulong client_capabilities; uint in_sub_stmt; bool enable_slow_log, insert_id_used, clear_next_insert_id; + bool last_insert_id_used; my_bool no_send_ok; SAVEPOINT *savepoints; }; diff --git a/sql/sql_insert.cc b/sql/sql_insert.cc index 0dae2b8f37b..f1f97400283 100644 --- a/sql/sql_insert.cc +++ b/sql/sql_insert.cc @@ -2009,6 +2009,14 @@ bool delayed_insert::handle_inserts(void) if (!using_bin_log) table->file->extra(HA_EXTRA_WRITE_CACHE); pthread_mutex_lock(&mutex); + + /* Reset auto-increment cacheing */ + if (thd.clear_next_insert_id) + { + thd.next_insert_id= 0; + thd.clear_next_insert_id= 0; + } + while ((row=rows.get())) { stacked_inserts--; diff --git a/sql/sql_lex.h b/sql/sql_lex.h index 77f79ad61f4..15a94041d24 100644 --- a/sql/sql_lex.h +++ b/sql/sql_lex.h @@ -1120,6 +1120,8 @@ typedef struct st_lex : public Query_tables_list case SQLCOM_UPDATE_MULTI: case SQLCOM_INSERT: case SQLCOM_INSERT_SELECT: + case SQLCOM_REPLACE: + case SQLCOM_REPLACE_SELECT: case SQLCOM_LOAD: return TRUE; default: diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 2cd14c3f5e6..e9e2a4ed1e0 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -4823,7 +4823,7 @@ get_store_key(THD *thd, KEYUSE *keyuse, table_map used_tables, */ bool -store_val_in_field(Field *field,Item *item) +store_val_in_field(Field *field, Item *item, enum_check_fields check_flag) { bool error; TABLE *table= field->table; @@ -4838,7 +4838,7 @@ store_val_in_field(Field *field,Item *item) with select_insert, which make count_cuted_fields= 1 */ enum_check_fields old_count_cuted_fields= thd->count_cuted_fields; - thd->count_cuted_fields= CHECK_FIELD_WARN; + thd->count_cuted_fields= check_flag; error= item->save_in_field(field, 1); thd->count_cuted_fields= old_count_cuted_fields; dbug_tmp_restore_column_map(table->write_set, old_map); @@ -11031,7 +11031,7 @@ static bool test_if_ref(Item_field *left_item,Item *right_item) field->real_type() != MYSQL_TYPE_VARCHAR && (field->type() != FIELD_TYPE_FLOAT || field->decimals() == 0)) { - return !store_val_in_field(field,right_item); + return !store_val_in_field(field, right_item, CHECK_FIELD_WARN); } } } diff --git a/sql/sql_select.h b/sql/sql_select.h index 6292977c209..2f0722db635 100644 --- a/sql/sql_select.h +++ b/sql/sql_select.h @@ -406,7 +406,7 @@ extern const char *join_type_str[]; void TEST_join(JOIN *join); /* Extern functions in sql_select.cc */ -bool store_val_in_field(Field *field,Item *val); +bool store_val_in_field(Field *field, Item *val, enum_check_fields check_flag); TABLE *create_tmp_table(THD *thd,TMP_TABLE_PARAM *param,List<Item> &fields, ORDER *group, bool distinct, bool save_sum_fields, ulonglong select_options, ha_rows rows_limit, diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy index afcfed5be3f..32fdcfe45bd 100644 --- a/sql/sql_yacc.yy +++ b/sql/sql_yacc.yy @@ -10635,6 +10635,8 @@ union_list: yyerror(ER(ER_SYNTAX_ERROR)); YYABORT; } + /* This counter shouldn't be incremented for UNION parts */ + Lex->nest_level--; if (mysql_new_select(lex, 0)) YYABORT; mysql_init_select(lex); diff --git a/sql/structs.h b/sql/structs.h index 78f00f72df1..506b37eb31a 100644 --- a/sql/structs.h +++ b/sql/structs.h @@ -54,7 +54,13 @@ typedef struct st_key_part_info { /* Info about a key part */ Field *field; uint offset; /* offset in record (from 0) */ uint null_offset; /* Offset to null_bit in record */ - uint16 length; /* Length of key_part */ + uint16 length; /* Length of keypart value in bytes */ + /* + Number of bytes required to store the keypart value. This may be + different from the "length" field as it also counts + - possible NULL-flag byte (see HA_KEY_NULL_LENGTH) + - possible HA_KEY_BLOB_LENGTH bytes needed to store actual value length. + */ uint16 store_length; uint16 key_type; uint16 fieldnr; /* Fieldnum in UNIREG */ diff --git a/storage/archive/ha_archive.cc b/storage/archive/ha_archive.cc index e6c9c5dccd7..6c0796e3bdc 100644 --- a/storage/archive/ha_archive.cc +++ b/storage/archive/ha_archive.cc @@ -1473,7 +1473,7 @@ int ha_archive::end_bulk_insert() int ha_archive::delete_all_rows() { DBUG_ENTER("ha_archive::delete_all_rows"); - DBUG_RETURN(0); + DBUG_RETURN(HA_ERR_WRONG_COMMAND); } /* |