diff options
| author | unknown <evgen@moonbone.local> | 2006-06-17 00:58:36 +0400 |
|---|---|---|
| committer | unknown <evgen@moonbone.local> | 2006-06-17 00:58:36 +0400 |
| commit | de292d67996bb238b43f4eaca3257e0a47544271 (patch) | |
| tree | 851e2d11dfc8cbe8ccb9e04adeaac5d576cb440a | |
| parent | 689205ae947176220ef7ba7332ffa2b765c4796a (diff) | |
| parent | b47705abf385274f3a5f4844c3496c1b22b15db6 (diff) | |
| download | mariadb-git-de292d67996bb238b43f4eaca3257e0a47544271.tar.gz | |
Manually merged
configure.in:
Auto merged
sql/item_timefunc.cc:
Auto merged
sql/item_timefunc.h:
Auto merged
sql/sql_base.cc:
Auto merged
sql/sql_lex.h:
Auto merged
sql/sql_parse.cc:
Auto merged
39 files changed, 934 insertions, 284 deletions
diff --git a/mysql-test/r/ctype_utf8.result b/mysql-test/r/ctype_utf8.result index d745de43f3e..991d1711a9e 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 1542794798a..f693c6190d5 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_time.result b/mysql-test/r/func_time.result index bc1bd3b6757..f1da903ae98 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 ea02a703c65..b4a7aa5cb76 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 a7d59fcfa62..5a5e4571ba9 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/select.result b/mysql-test/r/select.result index 86e1a8ada19..dceb0efe199 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,71 +2706,20 @@ 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, INDEX idx(a)); -INSERT INTO t1 VALUES (2), (3), (1); -EXPLAIN SELECT * FROM t1 IGNORE INDEX (idx); +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 ALL NULL NULL NULL NULL 3 -EXPLAIN SELECT * FROM t1 IGNORE INDEX (a); -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 ( city char(30) ); -INSERT INTO t1 VALUES ('London'); -INSERT INTO t1 VALUES ('Paris'); -SELECT * FROM t1 WHERE city='London'; -city -London -SELECT * FROM t1 WHERE city='london'; -city -London -EXPLAIN SELECT * FROM t1 WHERE city='London' AND city='london'; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using where -SELECT * FROM t1 WHERE city='London' AND city='london'; -city -London -EXPLAIN SELECT * FROM t1 WHERE city LIKE '%london%' AND city='London'; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using where -SELECT * FROM t1 WHERE city LIKE '%london%' AND city='London'; -city -London -DROP TABLE t1; -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; -a-b -0 -1 -18446744073709551615 -select a-b , (a-b < 0) from t1 order by 1; -a-b (a-b < 0) -0 0 -1 0 -18446744073709551615 0 -select a-b as d, (a-b >= 0), b from t1 group by b having d >= 0; -d (a-b >= 0) b -1 1 0 -0 1 1 -18446744073709551615 1 2 -select cast((a - b) as unsigned) from t1 order by 1; -cast((a - b) as unsigned) -0 -1 -18446744073709551615 -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; +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 ( K2C4 varchar(4) character set latin1 collate latin1_bin NOT NULL default '', K4N4 varchar(4) character set latin1 collate latin1_bin NOT NULL default '0000', @@ -2814,19 +2753,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 +2783,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 +3359,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 eb9e10aa58d..426387e04f5 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/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_time.test b/mysql-test/t/func_time.test index dc2e74c9365..4032d5a0a5c 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 5e3be6d45eb..da19a18c73a 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 a920d6d91b1..33fc5ab3128 100644 --- a/sql/field.cc +++ b/sql/field.cc @@ -4530,11 +4530,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); @@ -4599,7 +4599,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 & @@ -4653,7 +4653,7 @@ longlong Field_timestamp::val_int(void) { 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) @@ -4678,7 +4678,7 @@ String *Field_timestamp::val_str(String *val_buffer, String *val_ptr) { 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); @@ -4749,7 +4749,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); @@ -4832,7 +4832,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) @@ -5024,12 +5025,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); @@ -5217,9 +5219,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) @@ -5272,9 +5275,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); @@ -5420,9 +5424,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) @@ -5460,9 +5465,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)) @@ -5605,10 +5611,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); @@ -5655,9 +5662,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); @@ -6044,17 +6052,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; @@ -6063,8 +6060,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); } @@ -9037,7 +9040,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; @@ -9072,9 +9079,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); } @@ -9101,13 +9109,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); } } @@ -9133,13 +9141,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 f4d27e46877..e7b7aa45c27 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); @@ -1358,6 +1358,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); + } }; diff --git a/sql/item.h b/sql/item.h index 2311133b86b..2cadfda6895 100644 --- a/sql/item.h +++ b/sql/item.h @@ -794,6 +794,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; } }; @@ -1219,6 +1227,10 @@ public: 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); @@ -1827,6 +1839,10 @@ public: bool walk(Item_processor processor, byte *arg) { return (*ref)->walk(processor, 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 725be547a8a..db1af9de0a2 100644 --- a/sql/item_cmpfunc.cc +++ b/sql/item_cmpfunc.cc @@ -63,24 +63,153 @@ 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 orginal + 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 equvalent to: + 1. Check the list for presense 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 presense of a STRING field/function. + The second loop works only if a DATE/TIME field/function is found. + It checks presense 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; + char *buff= NULL; + 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) + buff= alloc_root(thd->mem_root, field->max_length()); + if (!buff || !field) + { + if (field) + delete field; + if (buff) + my_free(buff, MYF(MY_WME)); + field= 0; + } + else + field->move_field(buff, &null_byte, 0); + break; + } + } + if (field) + { + /* Check the rest of the list for presense 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()) + { + 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(); + + for (i= 0; i < nitems ; i++) + { + 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]); + } - type[0]= items[0]->result_type(); - for (i= 1; i < nitems; i++) + if (res == Item::FUNC_ITEM && field) { - type[0]= item_cmp_type(type[0], items[i]->result_type()); - if (field && convert_constant_item(thd, field, &items[i])) - type[0]= INT_RESULT; + delete field; + my_free(buff, MYF(MY_WME)); } } @@ -237,18 +366,18 @@ 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 */ - ulong orig_sql_mode= field->table->in_use->variables.sql_mode; - field->table->in_use->variables.sql_mode|= MODE_INVALID_DATES; + ulong orig_sql_mode= thd->variables.sql_mode; + thd->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, test(field->flags & UNSIGNED_FLAG)); - field->table->in_use->variables.sql_mode= orig_sql_mode; + thd->variables.sql_mode= orig_sql_mode; if (tmp) thd->change_item_tree(item, tmp); return 1; // Item was replaced } - field->table->in_use->variables.sql_mode= orig_sql_mode; + thd->variables.sql_mode= orig_sql_mode; } return 0; } @@ -1098,9 +1227,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 1cfdcef02d0..a2b10eacc79 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_strfunc.cc b/sql/item_strfunc.cc index ce9897afeed..c7fa049a0d5 100644 --- a/sql/item_strfunc.cc +++ b/sql/item_strfunc.cc @@ -288,11 +288,13 @@ 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; + is_const= args[0]->const_item(); for (i=1 ; i < arg_count ; i++) { if (res->length() == 0) @@ -315,7 +317,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 +372,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 +392,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 4195db4a6ee..d8b309978d5 100644 --- a/sql/item_timefunc.cc +++ b/sql/item_timefunc.cc @@ -2464,6 +2464,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); @@ -2478,6 +2492,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); @@ -2517,6 +2542,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 @@ -2553,6 +2586,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 9a14b961130..d5d3efeeab4 100644 --- a/sql/item_timefunc.h +++ b/sql/item_timefunc.h @@ -344,6 +344,7 @@ public: { return (new Field_date(maybe_null, name, t_arg, &my_charset_bin)); } + bool result_as_longlong() { return TRUE; } }; @@ -359,6 +360,7 @@ public: { return (new Field_datetime(maybe_null, name, t_arg, &my_charset_bin)); } + bool result_as_longlong() { return TRUE; } }; @@ -388,6 +390,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; } }; @@ -623,6 +626,7 @@ public: { return (new Field_time(maybe_null, name, t_arg, &my_charset_bin)); } + bool result_as_longlong() { return TRUE; } }; /* @@ -753,6 +757,8 @@ public: max_length= 10; maybe_null= 1; } + bool result_as_longlong() { return TRUE; } + longlong val_int(); }; @@ -769,6 +775,8 @@ public: { return (new Field_time(maybe_null, name, t_arg, &my_charset_bin)); } + bool result_as_longlong() { return TRUE; } + longlong val_int(); }; @@ -784,6 +792,8 @@ public: { return (new Field_datetime(maybe_null, name, t_arg, &my_charset_bin)); } + bool result_as_longlong() { return TRUE; } + longlong val_int(); }; class Item_func_makedate :public Item_str_func @@ -802,6 +812,8 @@ public: { return (new Field_date(maybe_null, name, t_arg, &my_charset_bin)); } + bool result_as_longlong() { return TRUE; } + longlong val_int(); }; diff --git a/sql/opt_range.cc b/sql/opt_range.cc index e36932cbc0c..cb0f35a425e 100644 --- a/sql/opt_range.cc +++ b/sql/opt_range.cc @@ -81,7 +81,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 373753a7b80..22d84d7fb5a 100644 --- a/sql/opt_sum.cc +++ b/sql/opt_sum.cc @@ -608,7 +608,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); @@ -663,6 +664,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, @@ -672,8 +675,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) @@ -707,6 +710,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 1e715bff249..ba9fa6f6c80 100644 --- a/sql/sql_base.cc +++ b/sql/sql_base.cc @@ -4184,10 +4184,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. */ @@ -4200,14 +4196,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_insert.cc b/sql/sql_insert.cc index 26f3b6f5faa..5630702fe52 100644 --- a/sql/sql_insert.cc +++ b/sql/sql_insert.cc @@ -1938,6 +1938,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 6b5c6ddca60..c75aa8f31b9 100644 --- a/sql/sql_lex.h +++ b/sql/sql_lex.h @@ -1051,6 +1051,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 505beedac3e..96b196e34b0 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -4788,7 +4788,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; THD *thd= field->table->in_use; @@ -4799,7 +4799,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; return error || cuted_fields != thd->cuted_fields; @@ -10928,7 +10928,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 01ed8048e4a..d5a1bf82bc8 100644 --- a/sql/sql_select.h +++ b/sql/sql_select.h @@ -404,7 +404,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 b2dbc517fa4..4f3cf4d8554 100644 --- a/sql/sql_yacc.yy +++ b/sql/sql_yacc.yy @@ -8752,6 +8752,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 9421ebdc2af..41b5f3b39c5 100644 --- a/sql/structs.h +++ b/sql/structs.h @@ -70,7 +70,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 */ |