diff options
57 files changed, 1852 insertions, 287 deletions
diff --git a/Makefile.am b/Makefile.am index e45ae5222c0..1a0afdf25f4 100644 --- a/Makefile.am +++ b/Makefile.am @@ -135,6 +135,7 @@ test-force-pl: ./mysql-test-run.pl --force && \ ./mysql-test-run.pl --ps-protocol --force +#used by autopush.pl to run memory based tests test-force-pl-mem: cd mysql-test; \ ./mysql-test-run.pl --force --mem && \ diff --git a/client/mysqltest.c b/client/mysqltest.c index 433810934d6..2063d31251d 100644 --- a/client/mysqltest.c +++ b/client/mysqltest.c @@ -475,7 +475,6 @@ void handle_error(struct st_command*, const char *err_sqlstate, DYNAMIC_STRING *ds); void handle_no_error(struct st_command*); - #ifdef EMBEDDED_LIBRARY /* send_one_query executes query in separate thread what is @@ -484,7 +483,7 @@ void handle_no_error(struct st_command*); by mysql_send_query. It's technically possible, though i don't see where it is needed. */ -pthread_handler_decl(send_one_query, arg) +pthread_handler_t send_one_query(void *arg) { struct st_connection *cn= (struct st_connection*)arg; @@ -4574,8 +4573,8 @@ void run_query_normal(struct st_connection *cn, struct st_command *command, int flags, char *query, int query_len, DYNAMIC_STRING *ds, DYNAMIC_STRING *ds_warnings) { - MYSQL *mysql= &cn->mysql; MYSQL_RES *res= 0; + MYSQL *mysql= &cn->mysql; int err= 0, counter= 0; DBUG_ENTER("run_query_normal"); DBUG_PRINT("enter",("flags: %d", flags)); diff --git a/include/my_time.h b/include/my_time.h index 574f1a49c5a..5361c47d57f 100644 --- a/include/my_time.h +++ b/include/my_time.h @@ -67,6 +67,8 @@ typedef long my_time_t; #define TIME_MAX_VALUE (TIME_MAX_HOUR*10000 + TIME_MAX_MINUTE*100 + \ TIME_MAX_SECOND) +my_bool check_date(const MYSQL_TIME *ltime, my_bool not_zero_date, + ulong flags, int *was_cut); enum enum_mysql_timestamp_type str_to_datetime(const char *str, uint length, MYSQL_TIME *l_time, uint flags, int *was_cut); diff --git a/libmysql/libmysql.c b/libmysql/libmysql.c index 21fb84fb19a..8786655d9c3 100644 --- a/libmysql/libmysql.c +++ b/libmysql/libmysql.c @@ -4769,14 +4769,6 @@ int STDCALL mysql_stmt_store_result(MYSQL_STMT *stmt) DBUG_RETURN(1); } - if (result->data) - { - free_root(&result->alloc, MYF(MY_KEEP_PREALLOC)); - result->data= NULL; - result->rows= 0; - stmt->data_cursor= NULL; - } - if (stmt->update_max_length && !stmt->bind_result_done) { /* diff --git a/mysql-test/r/func_group.result b/mysql-test/r/func_group.result index c6117053a60..23517f7b603 100644 --- a/mysql-test/r/func_group.result +++ b/mysql-test/r/func_group.result @@ -1029,3 +1029,29 @@ t1 CREATE TABLE `t1` ( `stddev(0)` double(8,4) default NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; +CREATE TABLE t1 (a INT, b INT); +INSERT INTO t1 VALUES (1,1),(1,2),(1,3),(1,4),(1,5),(1,6),(1,7),(1,8); +INSERT INTO t1 SELECT a, b+8 FROM t1; +INSERT INTO t1 SELECT a, b+16 FROM t1; +INSERT INTO t1 SELECT a, b+32 FROM t1; +INSERT INTO t1 SELECT a, b+64 FROM t1; +INSERT INTO t1 SELECT a, b+128 FROM t1; +INSERT INTO t1 SELECT a, b+256 FROM t1; +INSERT INTO t1 SELECT a, b+512 FROM t1; +INSERT INTO t1 SELECT a, b+1024 FROM t1; +INSERT INTO t1 SELECT a, b+2048 FROM t1; +INSERT INTO t1 SELECT a, b+4096 FROM t1; +INSERT INTO t1 SELECT a, b+8192 FROM t1; +INSERT INTO t1 SELECT a, b+16384 FROM t1; +INSERT INTO t1 SELECT a, b+32768 FROM t1; +SELECT a,COUNT(DISTINCT b) AS cnt FROM t1 GROUP BY a HAVING cnt > 50; +a cnt +1 65536 +SELECT a,SUM(DISTINCT b) AS sumation FROM t1 GROUP BY a HAVING sumation > 50; +a sumation +1 2147516416 +SELECT a,AVG(DISTINCT b) AS average FROM t1 GROUP BY a HAVING average > 50; +a average +1 32768.5000 +DROP TABLE t1; +End of 5.0 tests diff --git a/mysql-test/r/gis-rtree.result b/mysql-test/r/gis-rtree.result index e017a31f24b..e8134a50496 100644 --- a/mysql-test/r/gis-rtree.result +++ b/mysql-test/r/gis-rtree.result @@ -873,3 +873,11 @@ SELECT 1 FROM t1 WHERE foo != PointFromWKB(POINT(0,0)); 1 1 DROP TABLE t1; +CREATE TABLE t1(foo GEOMETRY NOT NULL, SPATIAL INDEX(foo) ); +INSERT INTO t1(foo) VALUES (NULL); +ERROR 23000: Column 'foo' cannot be null +INSERT INTO t1() VALUES (); +ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field +INSERT INTO t1(foo) VALUES (''); +ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field +DROP TABLE t1; diff --git a/mysql-test/r/metadata.result b/mysql-test/r/metadata.result index 50b0b6ae294..34e961395c4 100644 --- a/mysql-test/r/metadata.result +++ b/mysql-test/r/metadata.result @@ -96,3 +96,37 @@ i 2 affected rows: 1 affected rows: 0 +create table t1 (id int(10)); +insert into t1 values (1); +CREATE VIEW v1 AS select t1.id as id from t1; +CREATE VIEW v2 AS select t1.id as renamed from t1; +CREATE VIEW v3 AS select t1.id + 12 as renamed from t1; +select * from v1 group by id limit 1; +Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr +def test t1 v1 id id 3 10 1 Y 32768 0 63 +id +1 +select * from v1 group by id limit 0; +Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr +def test t1 v1 id id 3 10 0 Y 32768 0 63 +id +select * from v1 where id=1000 group by id; +Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr +def test t1 v1 id id 3 10 0 Y 32768 0 63 +id +select * from v1 where id=1 group by id; +Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr +def test t1 v1 id id 3 10 1 Y 32768 0 63 +id +1 +select * from v2 where renamed=1 group by renamed; +Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr +def test t1 v2 id renamed 3 10 1 Y 32768 0 63 +renamed +1 +select * from v3 where renamed=1 group by renamed; +Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr +def v3 renamed 8 12 0 Y 32896 0 63 +renamed +drop table t1; +drop view v1,v2,v3; diff --git a/mysql-test/r/query_cache.result b/mysql-test/r/query_cache.result index 846c687967d..d2b022a93fb 100644 --- a/mysql-test/r/query_cache.result +++ b/mysql-test/r/query_cache.result @@ -1273,3 +1273,51 @@ Variable_name Value Last_query_cost 0.000000 drop table t1; SET GLOBAL query_cache_size=0; +set global query_cache_size=1024*1024; +flush status; +create table t1 (a int); +insert into t1 (a) values (1), (2), (3); +select * from t1; +a +1 +2 +3 +show status like 'Qcache_hits'; +Variable_name Value +Qcache_hits 0 +select * from t1; +a +1 +2 +3 +show status like 'Qcache_hits'; +Variable_name Value +Qcache_hits 1 +create table t2 like t1; +select * from t1; +a +1 +2 +3 +show status like 'Qcache_hits'; +Variable_name Value +Qcache_hits 2 +insert into t2 select * from t1; +select * from t1; +a +1 +2 +3 +show status like 'Qcache_hits'; +Variable_name Value +Qcache_hits 3 +drop table t1, t2; +create table t1(c1 int); +create table t2(c1 int); +create table t3(c1 int); +create view v1 as select t3.c1 as c1 from t3,t2 where t3.c1 = t2.c1; +start transaction; +insert into t1(c1) select c1 from v1; +drop table t1, t2, t3; +drop view v1; +set global query_cache_size=0; diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index 37f871a4c7c..43247b56096 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -744,7 +744,7 @@ id select_type table type possible_keys key key_len ref rows Extra 3 DEPENDENT UNION NULL NULL NULL NULL NULL NULL NULL No tables used NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL Warnings: -Note 1003 select `test`.`t2`.`id` AS `id` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`id`,<exists>(select 1 AS `1` having (<cache>(`test`.`t2`.`id`) = <ref_null_helper>(1)) union select 3 AS `3` having (<cache>(`test`.`t2`.`id`) = <ref_null_helper>(3)))) +Note 1003 select `test`.`t2`.`id` AS `id` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`id`,<exists>(select 1 AS `1` having trigcond((<cache>(`test`.`t2`.`id`) = <ref_null_helper>(1))) union select 3 AS `3` having trigcond((<cache>(`test`.`t2`.`id`) = <ref_null_helper>(3))))) SELECT * FROM t2 WHERE id IN (SELECT 5 UNION SELECT 3); id SELECT * FROM t2 WHERE id IN (SELECT 5 UNION SELECT 2); @@ -907,7 +907,7 @@ id select_type table type possible_keys key key_len ref rows Extra 2 DEPENDENT SUBQUERY t2 ref_or_null a a 5 func 2 Using where; Using index 2 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 3 Using where Warnings: -Note 1003 select `test`.`t1`.`a` AS `a`,<in_optimizer>(`test`.`t1`.`a`,<exists>(select 1 AS `Not_used` from `test`.`t2` join `test`.`t3` where ((`test`.`t3`.`a` = `test`.`t2`.`a`) and ((<cache>(`test`.`t1`.`a`) = `test`.`t2`.`a`) or isnull(`test`.`t2`.`a`))) having <is_not_null_test>(`test`.`t2`.`a`))) AS `t1.a in (select t2.a from t2,t3 where t3.a=t2.a)` from `test`.`t1` +Note 1003 select `test`.`t1`.`a` AS `a`,<in_optimizer>(`test`.`t1`.`a`,<exists>(select 1 AS `Not_used` from `test`.`t2` join `test`.`t3` where ((`test`.`t3`.`a` = `test`.`t2`.`a`) and trigcond(((<cache>(`test`.`t1`.`a`) = `test`.`t2`.`a`) or isnull(`test`.`t2`.`a`)))) having trigcond(<is_not_null_test>(`test`.`t2`.`a`)))) AS `t1.a in (select t2.a from t2,t3 where t3.a=t2.a)` from `test`.`t1` drop table t1,t2,t3; create table t1 (a float); select 10.5 IN (SELECT * from t1 LIMIT 1); @@ -2816,19 +2816,19 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 8 2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 9 Using where Warnings: -Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two`,<in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(select `test`.`t2`.`one` AS `one`,`test`.`t2`.`two` AS `two` from `test`.`t2` where ((`test`.`t2`.`flag` = _latin1'0') and ((<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) or isnull(`test`.`t2`.`one`)) and ((<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`) or isnull(`test`.`t2`.`two`))) having (<is_not_null_test>(`test`.`t2`.`one`) and <is_not_null_test>(`test`.`t2`.`two`)))) AS `test` from `test`.`t1` +Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two`,<in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(select `test`.`t2`.`one` AS `one`,`test`.`t2`.`two` AS `two` from `test`.`t2` where ((`test`.`t2`.`flag` = _latin1'0') and trigcond((((<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) or isnull(`test`.`t2`.`one`)) and ((<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`) or isnull(`test`.`t2`.`two`))))) having trigcond((<is_not_null_test>(`test`.`t2`.`one`) and <is_not_null_test>(`test`.`t2`.`two`))))) AS `test` from `test`.`t1` explain extended SELECT one,two from t1 where ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = 'N'); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 8 Using where 2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 9 Using where Warnings: -Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two` from `test`.`t1` where <in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(select `test`.`t2`.`one` AS `one`,`test`.`t2`.`two` AS `two` from `test`.`t2` where ((`test`.`t2`.`flag` = _latin1'N') and (<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) and (<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`)))) +Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two` from `test`.`t1` where <in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(select `test`.`t2`.`one` AS `one`,`test`.`t2`.`two` AS `two` from `test`.`t2` where ((`test`.`t2`.`flag` = _latin1'N') and trigcond(((<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) and (<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`)))))) explain extended SELECT one,two,ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = '0' group by one,two) as 'test' from t1; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 8 2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 9 Using where; Using temporary; Using filesort Warnings: -Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two`,<in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(select `test`.`t2`.`one` AS `one`,`test`.`t2`.`two` AS `two` from `test`.`t2` where (`test`.`t2`.`flag` = _latin1'0') group by `test`.`t2`.`one`,`test`.`t2`.`two` having (((<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) or isnull(`test`.`t2`.`one`)) and ((<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`) or isnull(`test`.`t2`.`two`)) and <is_not_null_test>(`test`.`t2`.`one`) and <is_not_null_test>(`test`.`t2`.`two`)))) AS `test` from `test`.`t1` +Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two`,<in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(select `test`.`t2`.`one` AS `one`,`test`.`t2`.`two` AS `two` from `test`.`t2` where (`test`.`t2`.`flag` = _latin1'0') group by `test`.`t2`.`one`,`test`.`t2`.`two` having trigcond((((<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) or isnull(`test`.`t2`.`one`)) and ((<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`) or isnull(`test`.`t2`.`two`)) and <is_not_null_test>(`test`.`t2`.`one`) and <is_not_null_test>(`test`.`t2`.`two`))))) AS `test` from `test`.`t1` DROP TABLE t1,t2; CREATE TABLE t1 (a char(5), b char(5)); INSERT INTO t1 VALUES (NULL,'aaa'), ('aaa','aaa'); @@ -3576,3 +3576,19 @@ FROM t1 GROUP BY t1.a LIMIT 1) 2 2 DROP TABLE t1,t2; +CREATE TABLE t1 (a int, b int auto_increment, PRIMARY KEY (b)); +CREATE TABLE t2 (x int auto_increment, y int, z int, +PRIMARY KEY (x), FOREIGN KEY (y) REFERENCES t1 (b)); +SET SESSION sort_buffer_size = 32 * 1024; +SELECT SQL_NO_CACHE COUNT(*) +FROM (SELECT a, b, (SELECT x FROM t2 WHERE y=b ORDER BY z DESC LIMIT 1) c +FROM t1) t; +COUNT(*) +3000 +SET SESSION sort_buffer_size = 8 * 1024 * 1024; +SELECT SQL_NO_CACHE COUNT(*) +FROM (SELECT a, b, (SELECT x FROM t2 WHERE y=b ORDER BY z DESC LIMIT 1) c +FROM t1) t; +COUNT(*) +3000 +DROP TABLE t1,t2; diff --git a/mysql-test/r/subselect3.result b/mysql-test/r/subselect3.result new file mode 100644 index 00000000000..5ab8e448b39 --- /dev/null +++ b/mysql-test/r/subselect3.result @@ -0,0 +1,153 @@ +drop table if exists t0, t1, t2, t3, t4; +create table t1 (oref int, grp int, ie int) ; +insert into t1 (oref, grp, ie) values +(1, 1, 1), +(1, 1, 1), +(1, 2, NULL), +(2, 1, 3), +(3, 1, 4), +(3, 2, NULL); +create table t2 (oref int, a int); +insert into t2 values +(1, 1), +(2, 2), +(3, 3), +(4, NULL), +(2, NULL); +select a, oref, a in (select max(ie) +from t1 where oref=t2.oref group by grp) from t2; +a oref a in (select max(ie) +from t1 where oref=t2.oref group by grp) +1 1 1 +2 2 0 +3 3 NULL +NULL 4 0 +NULL 2 NULL +explain extended +select a, oref, a in (select max(ie) +from t1 where oref=t2.oref group by grp) from t2; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 5 +2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 6 Using where; Using temporary; Using filesort +Warnings: +Note 1276 Field or reference 't2.oref' of SELECT #2 was resolved in SELECT #1 +Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`oref` AS `oref`,<in_optimizer>(`test`.`t2`.`a`,<exists>(select max(`test`.`t1`.`ie`) AS `max(ie)` from `test`.`t1` where (`test`.`t1`.`oref` = `test`.`t2`.`oref`) group by `test`.`t1`.`grp` having trigcond((<cache>(`test`.`t2`.`a`) = <ref_null_helper>(max(`test`.`t1`.`ie`)))))) AS `a in (select max(ie) +from t1 where oref=t2.oref group by grp)` from `test`.`t2` +explain extended +select a, oref from t2 +where a in (select max(ie) from t1 where oref=t2.oref group by grp); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 5 Using where +2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 6 Using where; Using temporary; Using filesort +Warnings: +Note 1276 Field or reference 't2.oref' of SELECT #2 was resolved in SELECT #1 +Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`oref` AS `oref` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(select max(`test`.`t1`.`ie`) AS `max(ie)` from `test`.`t1` where (`test`.`t1`.`oref` = `test`.`t2`.`oref`) group by `test`.`t1`.`grp` having (<cache>(`test`.`t2`.`a`) = <ref_null_helper>(max(`test`.`t1`.`ie`))))) +create table t3 (a int); +insert into t3 values (NULL), (NULL); +flush status; +select a in (select max(ie) from t1 where oref=4 group by grp) from t3; +a in (select max(ie) from t1 where oref=4 group by grp) +0 +0 +show status like 'Handler_read_rnd_next'; +Variable_name Value +Handler_read_rnd_next 11 +select ' ^ This must show 11' Z; +Z + ^ This must show 11 +explain extended select a in (select max(ie) from t1 where oref=4 group by grp) from t3; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t3 ALL NULL NULL NULL NULL 2 +2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 6 Using where; Using temporary; Using filesort +Warnings: +Note 1003 select <in_optimizer>(`test`.`t3`.`a`,<exists>(select max(`test`.`t1`.`ie`) AS `max(ie)` from `test`.`t1` where (`test`.`t1`.`oref` = 4) group by `test`.`t1`.`grp` having trigcond((<cache>(`test`.`t3`.`a`) = <ref_null_helper>(max(`test`.`t1`.`ie`)))))) AS `a in (select max(ie) from t1 where oref=4 group by grp)` from `test`.`t3` +drop table t1, t2, t3; +create table t1 (a int, oref int, key(a)); +insert into t1 values +(1, 1), +(1, NULL), +(2, 3), +(2, NULL), +(3, NULL); +create table t2 (a int, oref int); +insert into t2 values (1, 1), (2,2), (NULL, 3), (NULL, 4); +select oref, a, a in (select a from t1 where oref=t2.oref) Z from t2; +oref a Z +1 1 1 +2 2 0 +3 NULL NULL +4 NULL 0 +explain extended +select oref, a, a in (select a from t1 where oref=t2.oref) Z from t2; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 4 +2 DEPENDENT SUBQUERY t1 index_subquery a a 5 func 2 Using index; Using where +Warnings: +Note 1276 Field or reference 't2.oref' of SELECT #2 was resolved in SELECT #1 +Note 1003 select `test`.`t2`.`oref` AS `oref`,`test`.`t2`.`a` AS `a`,<in_optimizer>(`test`.`t2`.`a`,<exists>(<index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on a checking NULL where (`test`.`t1`.`oref` = `test`.`t2`.`oref`)))) AS `Z` from `test`.`t2` +flush status; +select oref, a from t2 where a in (select a from t1 where oref=t2.oref); +oref a +1 1 +show status like '%Handler_read_rnd_next'; +Variable_name Value +Handler_read_rnd_next 5 +delete from t2; +insert into t2 values (NULL, 0),(NULL, 0), (NULL, 0), (NULL, 0); +flush status; +select oref, a, a in (select a from t1 where oref=t2.oref) Z from t2; +oref a Z +0 NULL 0 +0 NULL 0 +0 NULL 0 +0 NULL 0 +show status like '%Handler_read%'; +Variable_name Value +Handler_read_first 0 +Handler_read_key 0 +Handler_read_next 0 +Handler_read_prev 0 +Handler_read_rnd 0 +Handler_read_rnd_next 29 +select 'No key lookups, seq reads: 29= 5 reads from t2 + 4 * 6 reads from t1.' Z; +Z +No key lookups, seq reads: 29= 5 reads from t2 + 4 * 6 reads from t1. +drop table t1, t2; +create table t1 (a int, b int, primary key (a)); +insert into t1 values (1,1), (3,1),(100,1); +create table t2 (a int, b int); +insert into t2 values (1,1),(2,1),(NULL,1),(NULL,0); +select a,b, a in (select a from t1 where t1.b = t2.b) Z from t2 ; +a b Z +1 1 1 +2 1 0 +NULL 1 NULL +NULL 0 0 +drop table t1, t2; +create table t1 (a int, b int, key(a)); +insert into t1 values +(0,0),(1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9); +create table t2 like t1; +insert into t2 select * from t1; +update t2 set b=1; +create table t3 (a int, oref int); +insert into t3 values (1, 1), (NULL,1), (NULL,0); +select a, oref, +t3.a in (select t1.a from t1, t2 where t1.b=t2.a and t2.b=t3.oref) Z +from t3; +a oref Z +1 1 1 +NULL 1 NULL +NULL 0 0 +explain extended +select a, oref, +t3.a in (select t1.a from t1, t2 where t1.b=t2.a and t2.b=t3.oref) Z +from t3; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t3 ALL NULL NULL NULL NULL 3 +2 DEPENDENT SUBQUERY t1 ref_or_null a a 5 func 4 Using where +2 DEPENDENT SUBQUERY t2 ref a a 5 test.t1.b 1 Using where +Warnings: +Note 1276 Field or reference 't3.oref' of SELECT #2 was resolved in SELECT #1 +Note 1003 select `test`.`t3`.`a` AS `a`,`test`.`t3`.`oref` AS `oref`,<in_optimizer>(`test`.`t3`.`a`,<exists>(select 1 AS `Not_used` from `test`.`t1` join `test`.`t2` where ((`test`.`t2`.`a` = `test`.`t1`.`b`) and (`test`.`t2`.`b` = `test`.`t3`.`oref`) and trigcond(((<cache>(`test`.`t3`.`a`) = `test`.`t1`.`a`) or isnull(`test`.`t1`.`a`)))) having trigcond(<is_not_null_test>(`test`.`t1`.`a`)))) AS `Z` from `test`.`t3` +drop table t1, t2, t3; diff --git a/mysql-test/r/type_datetime.result b/mysql-test/r/type_datetime.result index 49e4827cb97..7fc1c4f398d 100644 --- a/mysql-test/r/type_datetime.result +++ b/mysql-test/r/type_datetime.result @@ -179,3 +179,15 @@ a 2006-06-06 15:55:55 DROP PREPARE s; DROP TABLE t1; +SELECT CAST(CAST('2006-08-10' AS DATE) AS DECIMAL(20,6)); +CAST(CAST('2006-08-10' AS DATE) AS DECIMAL(20,6)) +20060810.000000 +SELECT CAST(CAST('2006-08-10 10:11:12' AS DATETIME) AS DECIMAL(20,6)); +CAST(CAST('2006-08-10 10:11:12' AS DATETIME) AS DECIMAL(20,6)) +20060810101112.000000 +SELECT CAST(CAST('2006-08-10 10:11:12' AS DATETIME) + INTERVAL 14 MICROSECOND AS DECIMAL(20,6)); +CAST(CAST('2006-08-10 10:11:12' AS DATETIME) + INTERVAL 14 MICROSECOND AS DECIMAL(20,6)) +20060810101112.000014 +SELECT CAST(CAST('10:11:12.098700' AS TIME) AS DECIMAL(20,6)); +CAST(CAST('10:11:12.098700' AS TIME) AS DECIMAL(20,6)) +101112.098700 diff --git a/mysql-test/r/type_newdecimal.result b/mysql-test/r/type_newdecimal.result index 84d2ea47298..e65e76ded3f 100644 --- a/mysql-test/r/type_newdecimal.result +++ b/mysql-test/r/type_newdecimal.result @@ -1410,3 +1410,16 @@ i2 count(distinct j) 1.0 2 2.0 2 drop table t1; +create table t1(f1 decimal(20,6)); +insert into t1 values (CAST('10:11:12' AS date) + interval 14 microsecond); +insert into t1 values (CAST('10:11:12' AS time)); +select * from t1; +f1 +20101112000000.000014 +101112.000000 +drop table t1; +select cast(19999999999999999999 as unsigned); +cast(19999999999999999999 as unsigned) +18446744073709551615 +Warnings: +Error 1292 Truncated incorrect DECIMAL value: '' diff --git a/mysql-test/r/udf.result b/mysql-test/r/udf.result index 8e37cca6aa9..396f1efa1b7 100644 --- a/mysql-test/r/udf.result +++ b/mysql-test/r/udf.result @@ -105,6 +105,85 @@ explain select myfunc_int(f1) from t1 order by 1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using temporary; Using filesort drop table t1; +CREATE TABLE t1(a INT, b INT); +INSERT INTO t1 values (1,1),(2,2); +CREATE FUNCTION fn(a int) RETURNS int DETERMINISTIC +BEGIN +RETURN a; +END +|| +CREATE VIEW v1 AS SELECT a, fn(MIN(b)) as c FROM t1 GROUP BY a; +SELECT myfunc_int(a AS attr_name) FROM t1; +myfunc_int(a AS attr_name) +1 +2 +EXPLAIN EXTENDED SELECT myfunc_int(a AS attr_name) FROM t1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 +Warnings: +Note 1003 select myfunc_int(`test`.`t1`.`a` AS `attr_name`) AS `myfunc_int(a AS attr_name)` from `test`.`t1` +EXPLAIN EXTENDED SELECT myfunc_int(a) FROM t1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 +Warnings: +Note 1003 select myfunc_int(`test`.`t1`.`a` AS `a`) AS `myfunc_int(a)` from `test`.`t1` +SELECT a,c FROM v1; +a c +1 1 +2 2 +SELECT a, fn(MIN(b) xx) as c FROM t1 GROUP BY a; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'xx) as c FROM t1 GROUP BY a' at line 1 +SELECT myfunc_int(fn(MIN(b) xx)) as c FROM t1 GROUP BY a; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'xx)) as c FROM t1 GROUP BY a' at line 1 +SELECT myfunc_int(test.fn(MIN(b) xx)) as c FROM t1 GROUP BY a; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'xx)) as c FROM t1 GROUP BY a' at line 1 +SELECT myfunc_int(fn(MIN(b)) xx) as c FROM t1 GROUP BY a; +c +1 +2 +SELECT myfunc_int(test.fn(MIN(b)) xx) as c FROM t1 GROUP BY a; +c +1 +2 +EXPLAIN EXTENDED SELECT myfunc_int(MIN(b) xx) as c FROM t1 GROUP BY a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using temporary; Using filesort +Warnings: +Note 1003 select myfunc_int(min(`test`.`t1`.`b`) AS `xx`) AS `c` from `test`.`t1` group by `test`.`t1`.`a` +EXPLAIN EXTENDED SELECT test.fn(MIN(b)) as c FROM t1 GROUP BY a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using temporary; Using filesort +Warnings: +Note 1003 select `test`.`fn`(min(`test`.`t1`.`b`)) AS `c` from `test`.`t1` group by `test`.`t1`.`a` +EXPLAIN EXTENDED SELECT myfunc_int(fn(MIN(b))) as c FROM t1 GROUP BY a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using temporary; Using filesort +Warnings: +Note 1003 select myfunc_int(`test`.`fn`(min(`test`.`t1`.`b`)) AS `fn(MIN(b))`) AS `c` from `test`.`t1` group by `test`.`t1`.`a` +EXPLAIN EXTENDED SELECT myfunc_int(test.fn(MIN(b))) as c FROM t1 GROUP BY a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using temporary; Using filesort +Warnings: +Note 1003 select myfunc_int(`test`.`fn`(min(`test`.`t1`.`b`)) AS `test.fn(MIN(b))`) AS `c` from `test`.`t1` group by `test`.`t1`.`a` +SELECT myfunc_int(MIN(b) xx) as c FROM t1 GROUP BY a; +c +1 +2 +SELECT test.fn(MIN(b)) as c FROM t1 GROUP BY a; +c +1 +2 +SELECT myfunc_int(fn(MIN(b))) as c FROM t1 GROUP BY a; +c +1 +2 +SELECT myfunc_int(test.fn(MIN(b))) as c FROM t1 GROUP BY a; +c +1 +2 +DROP VIEW v1; +DROP TABLE t1; +DROP FUNCTION fn; End of 5.0 tests. DROP FUNCTION metaphon; DROP FUNCTION myfunc_double; diff --git a/mysql-test/t/func_group.test b/mysql-test/t/func_group.test index 079d107fad8..089f5ed9911 100644 --- a/mysql-test/t/func_group.test +++ b/mysql-test/t/func_group.test @@ -700,3 +700,28 @@ create table t1 select stddev(0); show create table t1; drop table t1; +# +# Bug #23184: SELECT causes server crash +# +CREATE TABLE t1 (a INT, b INT); +INSERT INTO t1 VALUES (1,1),(1,2),(1,3),(1,4),(1,5),(1,6),(1,7),(1,8); +INSERT INTO t1 SELECT a, b+8 FROM t1; +INSERT INTO t1 SELECT a, b+16 FROM t1; +INSERT INTO t1 SELECT a, b+32 FROM t1; +INSERT INTO t1 SELECT a, b+64 FROM t1; +INSERT INTO t1 SELECT a, b+128 FROM t1; +INSERT INTO t1 SELECT a, b+256 FROM t1; +INSERT INTO t1 SELECT a, b+512 FROM t1; +INSERT INTO t1 SELECT a, b+1024 FROM t1; +INSERT INTO t1 SELECT a, b+2048 FROM t1; +INSERT INTO t1 SELECT a, b+4096 FROM t1; +INSERT INTO t1 SELECT a, b+8192 FROM t1; +INSERT INTO t1 SELECT a, b+16384 FROM t1; +INSERT INTO t1 SELECT a, b+32768 FROM t1; +SELECT a,COUNT(DISTINCT b) AS cnt FROM t1 GROUP BY a HAVING cnt > 50; +SELECT a,SUM(DISTINCT b) AS sumation FROM t1 GROUP BY a HAVING sumation > 50; +SELECT a,AVG(DISTINCT b) AS average FROM t1 GROUP BY a HAVING average > 50; + +DROP TABLE t1; + +--echo End of 5.0 tests diff --git a/mysql-test/t/gis-rtree.test b/mysql-test/t/gis-rtree.test index 09cdcb2435e..e34dd14dbfc 100644 --- a/mysql-test/t/gis-rtree.test +++ b/mysql-test/t/gis-rtree.test @@ -242,3 +242,15 @@ INSERT INTO t1 (foo) VALUES (PointFromWKB(POINT(0,0))); SELECT 1 FROM t1 WHERE foo != PointFromWKB(POINT(0,0)); DROP TABLE t1; # End of 4.1 tests + +# +# bug #21790 (UNKNOWN ERROR on NULLs in RTree) +# +CREATE TABLE t1(foo GEOMETRY NOT NULL, SPATIAL INDEX(foo) ); +--error 1048 +INSERT INTO t1(foo) VALUES (NULL); +--error 1416 +INSERT INTO t1() VALUES (); +--error 1416 +INSERT INTO t1(foo) VALUES (''); +DROP TABLE t1; diff --git a/mysql-test/t/metadata.test b/mysql-test/t/metadata.test index 65338448555..a6ebfdc14c1 100644 --- a/mysql-test/t/metadata.test +++ b/mysql-test/t/metadata.test @@ -61,4 +61,23 @@ drop table t1;// delimiter ;// --disable_info +# +# Bug #20191: getTableName gives wrong or inconsistent result when using VIEWs +# +--enable_metadata +create table t1 (id int(10)); +insert into t1 values (1); +CREATE VIEW v1 AS select t1.id as id from t1; +CREATE VIEW v2 AS select t1.id as renamed from t1; +CREATE VIEW v3 AS select t1.id + 12 as renamed from t1; +select * from v1 group by id limit 1; +select * from v1 group by id limit 0; +select * from v1 where id=1000 group by id; +select * from v1 where id=1 group by id; +select * from v2 where renamed=1 group by renamed; +select * from v3 where renamed=1 group by renamed; +drop table t1; +drop view v1,v2,v3; +--disable_metadata + # End of 4.1 tests diff --git a/mysql-test/t/mysql.test b/mysql-test/t/mysql.test index c06e52e2d78..cb4a5593de7 100644 --- a/mysql-test/t/mysql.test +++ b/mysql-test/t/mysql.test @@ -143,6 +143,24 @@ drop table t1; --exec diff $MYSQLTEST_VARDIR/tmp/bug20328_1.result $MYSQLTEST_VARDIR/tmp/bug20328_2.result # +# Bug #19216: Client crashes on long SELECT +# +--exec echo "select" > $MYSQLTEST_VARDIR/tmp/b19216.tmp +# 3400 * 20 makes 68000 columns that is more than the max number that can fit +# in a 16 bit number. +let $i= 3400; +while ($i) +{ + --exec echo "'a','a','a','a','a','a','a','a','a','a','a','a','a','a','a','a','a','a','a','a'," >> $MYSQLTEST_VARDIR/tmp/b19216.tmp + dec $i; +} + +--exec echo "'b';" >> $MYSQLTEST_VARDIR/tmp/b19216.tmp +--disable_query_log +--exec $MYSQL < $MYSQLTEST_VARDIR/tmp/b19216.tmp >/dev/null +--enable_query_log + +# # Bug #20103: Escaping with backslash does not work # --exec echo "SET SQL_MODE = 'NO_BACKSLASH_ESCAPES';" > $MYSQLTEST_VARDIR/tmp/bug20103.sql diff --git a/mysql-test/t/order_by.test b/mysql-test/t/order_by.test index ff75186f64e..d7cf0e2a375 100644 --- a/mysql-test/t/order_by.test +++ b/mysql-test/t/order_by.test @@ -595,66 +595,6 @@ select a, b from t1 group by a, b order by sum(c); drop table t1; # -# Bug #7331 -# - -create table t1 ( - `sid` decimal(8,0) default null, - `wnid` varchar(11) not null default '', - key `wnid14` (`wnid`(4)), - key `wnid` (`wnid`) -) engine=myisam default charset=latin1; - -insert into t1 (`sid`, `wnid`) values -('10100','01019000000'),('37986','01019000000'),('37987','01019010000'), -('39560','01019090000'),('37989','01019000000'),('37990','01019011000'), -('37991','01019011000'),('37992','01019019000'),('37993','01019030000'), -('37994','01019090000'),('475','02070000000'),('25253','02071100000'), -('25255','02071100000'),('25256','02071110000'),('25258','02071130000'), -('25259','02071190000'),('25260','02071200000'),('25261','02071210000'), -('25262','02071290000'),('25263','02071300000'),('25264','02071310000'), -('25265','02071310000'),('25266','02071320000'),('25267','02071320000'), -('25269','02071330000'),('25270','02071340000'),('25271','02071350000'), -('25272','02071360000'),('25273','02071370000'),('25281','02071391000'), -('25282','02071391000'),('25283','02071399000'),('25284','02071400000'), -('25285','02071410000'),('25286','02071410000'),('25287','02071420000'), -('25288','02071420000'),('25291','02071430000'),('25290','02071440000'), -('25292','02071450000'),('25293','02071460000'),('25294','02071470000'), -('25295','02071491000'),('25296','02071491000'),('25297','02071499000'); - -explain select * from t1 where wnid like '0101%' order by wnid; - -select * from t1 where wnid like '0101%' order by wnid; - -drop table t1; - -# -# Bug #7672 - a wrong result for a select query in braces followed by order by -# - -CREATE TABLE t1 (a int); -INSERT INTO t1 VALUES (2), (1), (1), (2), (1); -SELECT a FROM t1 ORDER BY a; -(SELECT a FROM t1) ORDER BY a; -DROP TABLE t1; - -# -# Bug #18767: global ORDER BY applied to a SELECT with ORDER BY either was -# ignored or 'concatened' to the latter. - -CREATE TABLE t1 (a int, b int); -INSERT INTO t1 VALUES (1,30), (2,20), (1,10), (2,30), (1,20), (2,10); - -(SELECT b,a FROM t1 ORDER BY a,b) ORDER BY b,a; -(SELECT b FROM t1 ORDER BY b DESC) ORDER BY b ASC; -(SELECT b,a FROM t1 ORDER BY b,a) ORDER BY a,b; -(SELECT b,a FROM t1 ORDER by b,a LIMIT 3) ORDER by a,b; - -DROP TABLE t1; - -# End of 4.1 tests - -# # Bug#21302: Result not properly sorted when using an ORDER BY on a second # table in a join # diff --git a/mysql-test/t/query_cache.test b/mysql-test/t/query_cache.test index d86f1a464b1..f6e480282ff 100644 --- a/mysql-test/t/query_cache.test +++ b/mysql-test/t/query_cache.test @@ -870,3 +870,32 @@ select * from t1 where a > 3; show status like 'last_query_cost'; drop table t1; SET GLOBAL query_cache_size=0; + +# +# Bug #20045: Server crash on INSERT ... SELECT ... FROM non-mergeable view +# +set global query_cache_size=1024*1024; +flush status; +create table t1 (a int); +insert into t1 (a) values (1), (2), (3); +select * from t1; +show status like 'Qcache_hits'; +select * from t1; +show status like 'Qcache_hits'; +create table t2 like t1; +select * from t1; +show status like 'Qcache_hits'; +insert into t2 select * from t1; +select * from t1; +show status like 'Qcache_hits'; +drop table t1, t2; + +create table t1(c1 int); +create table t2(c1 int); +create table t3(c1 int); +create view v1 as select t3.c1 as c1 from t3,t2 where t3.c1 = t2.c1; +start transaction; +insert into t1(c1) select c1 from v1; +drop table t1, t2, t3; +drop view v1; +set global query_cache_size=0; diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test index d62b0e77952..1cfbc85128a 100644 --- a/mysql-test/t/subselect.test +++ b/mysql-test/t/subselect.test @@ -2459,3 +2459,40 @@ SELECT ( FROM t1 t2 GROUP BY t2.a; DROP TABLE t1,t2; + +# +# Bug #21727: Correlated subquery that requires filesort: +# slow with big sort_buffer_size +# + +CREATE TABLE t1 (a int, b int auto_increment, PRIMARY KEY (b)); +CREATE TABLE t2 (x int auto_increment, y int, z int, + PRIMARY KEY (x), FOREIGN KEY (y) REFERENCES t1 (b)); + +disable_query_log; +let $1=3000; +while ($1) +{ + eval INSERT INTO t1(a) VALUES(RAND()*1000); + eval SELECT MAX(b) FROM t1 INTO @id; + let $2=10; + while ($2) + { + eval INSERT INTO t2(y,z) VALUES(@id,RAND()*1000); + dec $2; + } + dec $1; +} +enable_query_log; + +SET SESSION sort_buffer_size = 32 * 1024; +SELECT SQL_NO_CACHE COUNT(*) + FROM (SELECT a, b, (SELECT x FROM t2 WHERE y=b ORDER BY z DESC LIMIT 1) c + FROM t1) t; + +SET SESSION sort_buffer_size = 8 * 1024 * 1024; +SELECT SQL_NO_CACHE COUNT(*) + FROM (SELECT a, b, (SELECT x FROM t2 WHERE y=b ORDER BY z DESC LIMIT 1) c + FROM t1) t; + +DROP TABLE t1,t2; diff --git a/mysql-test/t/subselect3.test b/mysql-test/t/subselect3.test new file mode 100644 index 00000000000..f7fbafdd17f --- /dev/null +++ b/mysql-test/t/subselect3.test @@ -0,0 +1,137 @@ +--disable_warnings +drop table if exists t0, t1, t2, t3, t4; +--enable_warnings + +# +# 1. Subquery with GROUP/HAVING +# +create table t1 (oref int, grp int, ie int) ; +insert into t1 (oref, grp, ie) values + (1, 1, 1), + (1, 1, 1), + (1, 2, NULL), + + (2, 1, 3), + + (3, 1, 4), + (3, 2, NULL); + +# Ok, for +# select max(ie) from t1 where oref=PARAM group by grp +# we'll have: +# 1 -> (1, NULL) matching + NULL +# 2 -> (3) non-matching +# 3 -> (3, NULL) non-matching + NULL +# 4 -> () nothing. + +create table t2 (oref int, a int); +insert into t2 values + (1, 1), + (2, 2), + (3, 3), + (4, NULL), + (2, NULL); + +# true, false, null, false, null +select a, oref, a in (select max(ie) + from t1 where oref=t2.oref group by grp) from t2; + +# This must have a trigcond +explain extended +select a, oref, a in (select max(ie) + from t1 where oref=t2.oref group by grp) from t2; + +# This must not have a trigcond: +explain extended +select a, oref from t2 +where a in (select max(ie) from t1 where oref=t2.oref group by grp); + + +# Non-correlated subquery, 2 NULL evaluations +create table t3 (a int); +insert into t3 values (NULL), (NULL); +flush status; +select a in (select max(ie) from t1 where oref=4 group by grp) from t3; +show status like 'Handler_read_rnd_next'; +select ' ^ This must show 11' Z; + +# This must show trigcond: +explain extended select a in (select max(ie) from t1 where oref=4 group by grp) from t3; + +drop table t1, t2, t3; + +# +# 2. Subquery handled with 'index_subquery': +# +create table t1 (a int, oref int, key(a)); +insert into t1 values + (1, 1), + (1, NULL), + (2, 3), + (2, NULL), + (3, NULL); + +create table t2 (a int, oref int); +insert into t2 values (1, 1), (2,2), (NULL, 3), (NULL, 4); + +select oref, a, a in (select a from t1 where oref=t2.oref) Z from t2; + +# The next explain shows "using index" but that is just incorrect display +# (there is a bug filed about this). +explain extended +select oref, a, a in (select a from t1 where oref=t2.oref) Z from t2; + +flush status; +select oref, a from t2 where a in (select a from t1 where oref=t2.oref); +# This will only show access to t2: +show status like '%Handler_read_rnd_next'; + +# Check that repeated NULL-scans are not cached (subq. is not correlated): +delete from t2; +insert into t2 values (NULL, 0),(NULL, 0), (NULL, 0), (NULL, 0); + +flush status; +select oref, a, a in (select a from t1 where oref=t2.oref) Z from t2; +show status like '%Handler_read%'; +select 'No key lookups, seq reads: 29= 5 reads from t2 + 4 * 6 reads from t1.' Z; + +drop table t1, t2; + +# +# 3. Subquery handled with 'unique_index_subquery': +# +create table t1 (a int, b int, primary key (a)); +insert into t1 values (1,1), (3,1),(100,1); + +create table t2 (a int, b int); +insert into t2 values (1,1),(2,1),(NULL,1),(NULL,0); + +select a,b, a in (select a from t1 where t1.b = t2.b) Z from t2 ; + +drop table t1, t2; + +# +# 4. Subquery that is a join, with ref access +# +create table t1 (a int, b int, key(a)); +insert into t1 values + (0,0),(1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9); + +create table t2 like t1; +insert into t2 select * from t1; +update t2 set b=1; + +create table t3 (a int, oref int); +insert into t3 values (1, 1), (NULL,1), (NULL,0); +select a, oref, + t3.a in (select t1.a from t1, t2 where t1.b=t2.a and t2.b=t3.oref) Z +from t3; + +# This must have trigcond in WHERE and HAVING: +explain extended +select a, oref, + t3.a in (select t1.a from t1, t2 where t1.b=t2.a and t2.b=t3.oref) Z +from t3; + +drop table t1, t2, t3; + diff --git a/mysql-test/t/type_datetime.test b/mysql-test/t/type_datetime.test index cdf73bf6c89..3ad6bdc53e4 100644 --- a/mysql-test/t/type_datetime.test +++ b/mysql-test/t/type_datetime.test @@ -125,3 +125,13 @@ PREPARE s FROM 'SELECT a FROM t1 WHERE a=(SELECT MAX(a) FROM t1) AND (a="2006060 EXECUTE s; DROP PREPARE s; DROP TABLE t1; + + +# +# Bug 19491 (CAST DATE AS DECIMAL returns incorrect result +# +SELECT CAST(CAST('2006-08-10' AS DATE) AS DECIMAL(20,6)); +SELECT CAST(CAST('2006-08-10 10:11:12' AS DATETIME) AS DECIMAL(20,6)); +SELECT CAST(CAST('2006-08-10 10:11:12' AS DATETIME) + INTERVAL 14 MICROSECOND AS DECIMAL(20,6)); +SELECT CAST(CAST('10:11:12.098700' AS TIME) AS DECIMAL(20,6)); + diff --git a/mysql-test/t/type_newdecimal.test b/mysql-test/t/type_newdecimal.test index e4843c3b83e..f2031498687 100644 --- a/mysql-test/t/type_newdecimal.test +++ b/mysql-test/t/type_newdecimal.test @@ -1108,3 +1108,15 @@ insert into t1 values (1,1), (1,2), (2,3), (2,4); select i, count(distinct j) from t1 group by i; select i+0.0 as i2, count(distinct j) from t1 group by i2; drop table t1; + +create table t1(f1 decimal(20,6)); +insert into t1 values (CAST('10:11:12' AS date) + interval 14 microsecond); +insert into t1 values (CAST('10:11:12' AS time)); +select * from t1; +drop table t1; + +# +# Bug #8663 (cant use bigint as input to CAST) +# +select cast(19999999999999999999 as unsigned); + diff --git a/mysql-test/t/udf.test b/mysql-test/t/udf.test index 96e559f5c05..37358a292be 100644 --- a/mysql-test/t/udf.test +++ b/mysql-test/t/udf.test @@ -127,6 +127,50 @@ create table t1(f1 int); insert into t1 values(1),(2); explain select myfunc_int(f1) from t1 order by 1; drop table t1; + +# +# Bug #21809: Error 1356 while selecting from view with grouping though +# underlying select OK. +# +CREATE TABLE t1(a INT, b INT); INSERT INTO t1 values (1,1),(2,2); + +DELIMITER ||; +CREATE FUNCTION fn(a int) RETURNS int DETERMINISTIC +BEGIN + RETURN a; +END +|| +DELIMITER ;|| + +CREATE VIEW v1 AS SELECT a, fn(MIN(b)) as c FROM t1 GROUP BY a; + +SELECT myfunc_int(a AS attr_name) FROM t1; +EXPLAIN EXTENDED SELECT myfunc_int(a AS attr_name) FROM t1; +EXPLAIN EXTENDED SELECT myfunc_int(a) FROM t1; +SELECT a,c FROM v1; + +--error ER_PARSE_ERROR +SELECT a, fn(MIN(b) xx) as c FROM t1 GROUP BY a; +--error ER_PARSE_ERROR +SELECT myfunc_int(fn(MIN(b) xx)) as c FROM t1 GROUP BY a; +--error ER_PARSE_ERROR +SELECT myfunc_int(test.fn(MIN(b) xx)) as c FROM t1 GROUP BY a; + +SELECT myfunc_int(fn(MIN(b)) xx) as c FROM t1 GROUP BY a; +SELECT myfunc_int(test.fn(MIN(b)) xx) as c FROM t1 GROUP BY a; + +EXPLAIN EXTENDED SELECT myfunc_int(MIN(b) xx) as c FROM t1 GROUP BY a; +EXPLAIN EXTENDED SELECT test.fn(MIN(b)) as c FROM t1 GROUP BY a; +EXPLAIN EXTENDED SELECT myfunc_int(fn(MIN(b))) as c FROM t1 GROUP BY a; +EXPLAIN EXTENDED SELECT myfunc_int(test.fn(MIN(b))) as c FROM t1 GROUP BY a; +SELECT myfunc_int(MIN(b) xx) as c FROM t1 GROUP BY a; +SELECT test.fn(MIN(b)) as c FROM t1 GROUP BY a; +SELECT myfunc_int(fn(MIN(b))) as c FROM t1 GROUP BY a; +SELECT myfunc_int(test.fn(MIN(b))) as c FROM t1 GROUP BY a; +DROP VIEW v1; +DROP TABLE t1; +DROP FUNCTION fn; + --echo End of 5.0 tests. # diff --git a/sql-common/my_time.c b/sql-common/my_time.c index 2dd40c112de..7d12a49e40f 100644 --- a/sql-common/my_time.c +++ b/sql-common/my_time.c @@ -76,8 +76,8 @@ uint calc_days_in_year(uint year) 1 error */ -static my_bool check_date(const MYSQL_TIME *ltime, my_bool not_zero_date, - ulong flags, int *was_cut) +my_bool check_date(const MYSQL_TIME *ltime, my_bool not_zero_date, + ulong flags, int *was_cut) { if (not_zero_date) { diff --git a/sql/field.cc b/sql/field.cc index f04efc66dfd..1cfd0843179 100644 --- a/sql/field.cc +++ b/sql/field.cc @@ -2426,6 +2426,13 @@ int Field_new_decimal::store_decimal(const my_decimal *decimal_value) } +int Field_new_decimal::store_time(TIME *ltime, timestamp_type t_type) +{ + my_decimal decimal_value; + return store_value(date2my_decimal(ltime, &decimal_value)); +} + + double Field_new_decimal::val_real(void) { double dbl; @@ -5400,7 +5407,21 @@ int Field_newdate::store_time(TIME *ltime,timestamp_type type) long tmp; int error= 0; if (type == MYSQL_TIMESTAMP_DATE || type == MYSQL_TIMESTAMP_DATETIME) + { tmp=ltime->year*16*32+ltime->month*32+ltime->day; + if ((my_bool)check_date(ltime, tmp, + (TIME_FUZZY_DATE | + (current_thd->variables.sql_mode & + (MODE_NO_ZERO_IN_DATE | MODE_NO_ZERO_DATE | + MODE_INVALID_DATES))), &error)) + { + char buff[12]; + String str(buff, sizeof(buff), &my_charset_latin1); + make_date((DATE_TIME_FORMAT *) 0, ltime, &str); + set_datetime_warning(MYSQL_ERROR::WARN_LEVEL_WARN, WARN_DATA_TRUNCATED, + str.ptr(), str.length(), MYSQL_TIMESTAMP_DATE, 1); + } + } else { tmp=0; @@ -5609,8 +5630,22 @@ int Field_datetime::store_time(TIME *ltime,timestamp_type type) structure always fit into DATETIME range. */ if (type == MYSQL_TIMESTAMP_DATE || type == MYSQL_TIMESTAMP_DATETIME) + { tmp=((ltime->year*10000L+ltime->month*100+ltime->day)*LL(1000000)+ (ltime->hour*10000L+ltime->minute*100+ltime->second)); + if ((my_bool)check_date(ltime, tmp, + (TIME_FUZZY_DATE | + (current_thd->variables.sql_mode & + (MODE_NO_ZERO_IN_DATE | MODE_NO_ZERO_DATE | + MODE_INVALID_DATES))), &error)) + { + char buff[19]; + String str(buff, sizeof(buff), &my_charset_latin1); + make_datetime((DATE_TIME_FORMAT *) 0, ltime, &str); + set_datetime_warning(MYSQL_ERROR::WARN_LEVEL_WARN, WARN_DATA_TRUNCATED, + str.ptr(), str.length(), MYSQL_TIMESTAMP_DATETIME,1); + } + } else { tmp=0; diff --git a/sql/field.h b/sql/field.h index 0f845f6c2f9..709630218b2 100644 --- a/sql/field.h +++ b/sql/field.h @@ -496,6 +496,7 @@ public: int store(const char *to, uint length, CHARSET_INFO *charset); int store(double nr); int store(longlong nr, bool unsigned_val); + int store_time(TIME *ltime, timestamp_type t_type); int store_decimal(const my_decimal *); double val_real(void); longlong val_int(void); diff --git a/sql/filesort.cc b/sql/filesort.cc index b063b416191..12b17846fe4 100644 --- a/sql/filesort.cc +++ b/sql/filesort.cc @@ -109,6 +109,8 @@ ha_rows filesort(THD *thd, TABLE *table, SORT_FIELD *sortorder, uint s_length, DBUG_PUSH(""); /* No DBUG here */ #endif FILESORT_INFO table_sort; + TABLE_LIST *tab= table->pos_in_table_list; + Item_subselect *subselect= tab ? tab->containing_subselect() : 0; /* Don't use table->sort in filesort as it is also used by QUICK_INDEX_MERGE_SELECT. Work with a copy and put it back at the end @@ -121,7 +123,6 @@ ha_rows filesort(THD *thd, TABLE *table, SORT_FIELD *sortorder, uint s_length, my_b_clear(&tempfile); my_b_clear(&buffpek_pointers); buffpek=0; - sort_keys= (uchar **) NULL; error= 1; bzero((char*) ¶m,sizeof(param)); param.sort_length= sortlength(thd, sortorder, s_length, &multi_byte_charset); @@ -202,13 +203,15 @@ ha_rows filesort(THD *thd, TABLE *table, SORT_FIELD *sortorder, uint s_length, ulong old_memavl; ulong keys= memavl/(param.rec_length+sizeof(char*)); param.keys=(uint) min(records+1, keys); - if ((sort_keys= (uchar **) make_char_array(param.keys, param.rec_length, - MYF(0)))) + if (table_sort.sort_keys || + (table_sort.sort_keys= (uchar **) make_char_array(param.keys, param.rec_length, + MYF(0)))) break; old_memavl=memavl; if ((memavl=memavl/4*3) < min_sort_memory && old_memavl > min_sort_memory) memavl= min_sort_memory; } + sort_keys= table_sort.sort_keys; if (memavl < min_sort_memory) { my_error(ER_OUTOFMEMORY,MYF(ME_ERROR+ME_WAITTANG), @@ -235,8 +238,12 @@ ha_rows filesort(THD *thd, TABLE *table, SORT_FIELD *sortorder, uint s_length, } else { - if (!(buffpek=read_buffpek_from_file(&buffpek_pointers, maxbuffer))) + if (!table_sort.buffpek && table_sort.buffpek_len < maxbuffer && + !(table_sort.buffpek= + (byte *) read_buffpek_from_file(&buffpek_pointers, maxbuffer))) goto err; + buffpek= (BUFFPEK *) table_sort.buffpek; + table_sort.buffpek_len= maxbuffer; close_cached_file(&buffpek_pointers); /* Open cached file if it isn't open */ if (! my_b_inited(outfile) && @@ -269,8 +276,14 @@ ha_rows filesort(THD *thd, TABLE *table, SORT_FIELD *sortorder, uint s_length, err: if (param.tmp_buffer) x_free(param.tmp_buffer); - x_free((gptr) sort_keys); - x_free((gptr) buffpek); + if (!subselect || !subselect->is_uncacheable()) + { + x_free((gptr) sort_keys); + table_sort.sort_keys= 0; + x_free((gptr) buffpek); + table_sort.buffpek= 0; + table_sort.buffpek_len= 0; + } close_cached_file(&tempfile); close_cached_file(&buffpek_pointers); if (my_b_inited(outfile)) @@ -301,13 +314,27 @@ ha_rows filesort(THD *thd, TABLE *table, SORT_FIELD *sortorder, uint s_length, } /* filesort */ -void filesort_free_buffers(TABLE *table) +void filesort_free_buffers(TABLE *table, bool full) { if (table->sort.record_pointers) { my_free((gptr) table->sort.record_pointers,MYF(0)); table->sort.record_pointers=0; } + if (full) + { + if (table->sort.sort_keys ) + { + x_free((gptr) table->sort.sort_keys); + table->sort.sort_keys= 0; + } + if (table->sort.buffpek) + { + x_free((gptr) table->sort.buffpek); + table->sort.buffpek= 0; + table->sort.buffpek_len= 0; + } + } if (table->sort.addon_buf) { my_free((char *) table->sort.addon_buf, MYF(0)); diff --git a/sql/handler.cc b/sql/handler.cc index cff8213edec..040d1b0c279 100644 --- a/sql/handler.cc +++ b/sql/handler.cc @@ -1798,8 +1798,8 @@ void handler::print_error(int error, myf errflag) break; } case HA_ERR_NULL_IN_SPATIAL: - textno= ER_UNKNOWN_ERROR; - break; + my_error(ER_CANT_CREATE_GEOMETRY_OBJECT, MYF(0)); + DBUG_VOID_RETURN; case HA_ERR_FOUND_DUPP_UNIQUE: textno=ER_DUP_UNIQUE; break; diff --git a/sql/item.cc b/sql/item.cc index 3864ee966c4..59d28c00416 100644 --- a/sql/item.cc +++ b/sql/item.cc @@ -272,6 +272,34 @@ my_decimal *Item::val_decimal_from_string(my_decimal *decimal_value) } +my_decimal *Item::val_decimal_from_date(my_decimal *decimal_value) +{ + DBUG_ASSERT(fixed == 1); + TIME ltime; + longlong date; + if (get_date(<ime, TIME_FUZZY_DATE)) + { + my_decimal_set_zero(decimal_value); + return 0; + } + return date2my_decimal(<ime, decimal_value); +} + + +my_decimal *Item::val_decimal_from_time(my_decimal *decimal_value) +{ + DBUG_ASSERT(fixed == 1); + TIME ltime; + longlong date; + if (get_time(<ime)) + { + my_decimal_set_zero(decimal_value); + return 0; + } + return date2my_decimal(<ime, decimal_value); +} + + double Item::val_real_from_decimal() { /* Note that fix_fields may not be called for Item_avg_field items */ @@ -295,6 +323,25 @@ longlong Item::val_int_from_decimal() return result; } +int Item::save_time_in_field(Field *field) +{ + TIME ltime; + if (get_time(<ime)) + return set_field_to_null(field); + field->set_notnull(); + return field->store_time(<ime, MYSQL_TIMESTAMP_TIME); +} + + +int Item::save_date_in_field(Field *field) +{ + TIME ltime; + if (get_date(<ime, TIME_FUZZY_DATE)) + return set_field_to_null(field); + field->set_notnull(); + return field->store_time(<ime, MYSQL_TIMESTAMP_DATETIME); +} + Item::Item(): rsize(0), name(0), orig_name(0), name_length(0), fixed(0), @@ -1148,6 +1195,28 @@ void Item_name_const::print(String *str) /* + need a special class to adjust printing : references to aggregate functions + must not be printed as refs because the aggregate functions that are added to + the front of select list are not printed as well. +*/ +class Item_aggregate_ref : public Item_ref +{ +public: + Item_aggregate_ref(Name_resolution_context *context_arg, Item **item, + const char *table_name_arg, const char *field_name_arg) + :Item_ref(context_arg, item, table_name_arg, field_name_arg) {} + + void print (String *str) + { + if (ref) + (*ref)->print(str); + else + Item_ident::print(str); + } +}; + + +/* Move SUM items out from item tree and replace with reference SYNOPSIS @@ -1200,8 +1269,8 @@ void Item::split_sum_func2(THD *thd, Item **ref_pointer_array, Item *new_item, *real_itm= real_item(); ref_pointer_array[el]= real_itm; - if (!(new_item= new Item_ref(&thd->lex->current_select->context, - ref_pointer_array + el, 0, name))) + if (!(new_item= new Item_aggregate_ref(&thd->lex->current_select->context, + ref_pointer_array + el, 0, name))) return; // fatal_error is set fields.push_front(real_itm); thd->change_item_tree(ref, new_item); @@ -3658,16 +3727,16 @@ bool Item_field::fix_fields(THD *thd, Item **reference) Item_ref to point to the Item in the select list and replace the Item_field created by the parser with the new Item_ref. */ - Item_ref *rf= new Item_ref(db_name,table_name,field_name); + Item_ref *rf= new Item_ref(context, db_name,table_name,field_name); if (!rf) return 1; - thd->change_item_tree(ref, rf); + thd->change_item_tree(reference, rf); /* Because Item_ref never substitutes itself with other items in Item_ref::fix_fields(), we can safely use the original pointer to it even after fix_fields() */ - return rf->fix_fields(thd, tables, ref) || rf->check_cols(1); + return rf->fix_fields(thd, reference) || rf->check_cols(1); } } } @@ -4133,6 +4202,10 @@ void Item_field::make_field(Send_field *tmp_field) DBUG_ASSERT(tmp_field->table_name != 0); if (name) tmp_field->col_name=name; // Use user supplied name + if (table_name) + tmp_field->table_name= table_name; + if (db_name) + tmp_field->db_name= db_name; } diff --git a/sql/item.h b/sql/item.h index b5490f9e814..63d89113ec1 100644 --- a/sql/item.h +++ b/sql/item.h @@ -605,9 +605,14 @@ public: my_decimal *val_decimal_from_real(my_decimal *decimal_value); my_decimal *val_decimal_from_int(my_decimal *decimal_value); my_decimal *val_decimal_from_string(my_decimal *decimal_value); + my_decimal *val_decimal_from_date(my_decimal *decimal_value); + my_decimal *val_decimal_from_time(my_decimal *decimal_value); longlong val_int_from_decimal(); double val_real_from_decimal(); + int save_time_in_field(Field *field); + int save_date_in_field(Field *field); + virtual Field *get_tmp_table_field() { return 0; } /* This is also used to create fields in CREATE ... SELECT: */ virtual Field *tmp_table_field(TABLE *t_arg) { return 0; } @@ -1947,6 +1952,16 @@ public: class Item_in_subselect; + +/* + An object of this class: + - Converts val_XXX() calls to ref->val_XXX_result() calls, like Item_ref. + - Sets owner->was_null=TRUE if it has returned a NULL value from any + val_XXX() function. This allows to inject an Item_ref_null_helper + object into subquery and then check if the subquery has produced a row + with NULL value. +*/ + class Item_ref_null_helper: public Item_ref { protected: diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc index 9a400d60ae6..540f67ba0ee 100644 --- a/sql/item_cmpfunc.cc +++ b/sql/item_cmpfunc.cc @@ -786,9 +786,41 @@ longlong Item_in_optimizer::val_int() { DBUG_ASSERT(fixed == 1); cache->store(args[0]); + if (cache->null_value) { - null_value= 1; + if (((Item_in_subselect*)args[1])->is_top_level_item()) + { + /* + We're evaluating "NULL IN (SELECT ...)". The result can be NULL or + FALSE, and we can return one instead of another. Just return NULL. + */ + null_value= 1; + } + else + { + if (!((Item_in_subselect*)args[1])->is_correlated && + result_for_null_param != UNKNOWN) + { + /* Use cached value from previous execution */ + null_value= result_for_null_param; + } + else + { + /* + We're evaluating "NULL IN (SELECT ...)". The result is: + FALSE if SELECT produces an empty set, or + NULL otherwise. + We disable the predicates we've pushed down into subselect, run the + subselect and see if it has produced any rows. + */ + ((Item_in_subselect*)args[1])->enable_pushed_conds= FALSE; + longlong tmp= args[1]->val_bool_result(); + result_for_null_param= null_value= + !((Item_in_subselect*)args[1])->engine->no_rows(); + ((Item_in_subselect*)args[1])->enable_pushed_conds= TRUE; + } + } return 0; } bool tmp= args[1]->val_bool_result(); diff --git a/sql/item_cmpfunc.h b/sql/item_cmpfunc.h index c8439cba303..acad1e51bc9 100644 --- a/sql/item_cmpfunc.h +++ b/sql/item_cmpfunc.h @@ -100,25 +100,44 @@ public: }; class Item_cache; +#define UNKNOWN ((my_bool)-1) + + +/* + Item_in_optimizer(left_expr, Item_in_subselect(...)) + + Item_in_optimizer is used to wrap an instance of Item_in_subselect. This + class does the following: + - Evaluate the left expression and store it in Item_cache_* object (to + avoid re-evaluating it many times during subquery execution) + - Shortcut the evaluation of "NULL IN (...)" to NULL in the cases where we + don't care if the result is NULL or FALSE. + + NOTE + It is not quite clear why the above listed functionality should be + placed into a separate class called 'Item_in_optimizer'. +*/ + class Item_in_optimizer: public Item_bool_func { protected: Item_cache *cache; bool save_cache; + /* + Stores the value of "NULL IN (SELECT ...)" for uncorrelated subqueries: + UNKNOWN - "NULL in (SELECT ...)" has not yet been evaluated + FALSE - result is FALSE + TRUE - result is NULL + */ + my_bool result_for_null_param; public: Item_in_optimizer(Item *a, Item_in_subselect *b): - Item_bool_func(a, my_reinterpret_cast(Item *)(b)), cache(0), save_cache(0) + Item_bool_func(a, my_reinterpret_cast(Item *)(b)), cache(0), + save_cache(0), result_for_null_param(UNKNOWN) {} bool fix_fields(THD *, Item **); bool fix_left(THD *thd, Item **ref); bool is_null(); - /* - Item_in_optimizer item is special boolean function. On value request - (one of val, val_int or val_str methods) it evaluate left expression - of IN by storing it value in cache item (one of Item_cache* items), - then it test cache is it NULL. If left expression (cache) is NULL then - Item_in_optimizer return NULL, else it evaluate Item_in_subselect. - */ longlong val_int(); void cleanup(); const char *func_name() const { return "<in_optimizer>"; } @@ -256,9 +275,11 @@ public: class Item_maxmin_subselect; /* + trigcond<param>(arg) ::= param? arg : TRUE + The class Item_func_trig_cond is used for guarded predicates which are employed only for internal purposes. - A guarded predicates is an object consisting of an a regular or + A guarded predicate is an object consisting of an a regular or a guarded predicate P and a pointer to a boolean guard variable g. A guarded predicate P/g is evaluated to true if the value of the guard g is false, otherwise it is evaluated to the same value that @@ -276,6 +297,10 @@ class Item_maxmin_subselect; Objects of this class are built only for query execution after the execution plan has been already selected. That's why this class needs only val_int out of generic methods. + + Current uses of Item_func_trig_cond objects: + - To wrap selection conditions when executing outer joins + - To wrap condition that is pushed down into subquery */ class Item_func_trig_cond: public Item_bool_func @@ -1019,6 +1044,11 @@ public: /* Functions used by HAVING for rewriting IN subquery */ class Item_in_subselect; + +/* + This is like IS NOT NULL but it also remembers if it ever has + encountered a NULL. +*/ class Item_is_not_null_test :public Item_func_isnull { Item_in_subselect* owner; diff --git a/sql/item_func.cc b/sql/item_func.cc index fa953889619..0fc6e5f6c61 100644 --- a/sql/item_func.cc +++ b/sql/item_func.cc @@ -964,7 +964,14 @@ longlong Item_func_unsigned::val_int() longlong value; int error; - if (args[0]->cast_to_int_type() != STRING_RESULT) + if (args[0]->cast_to_int_type() == DECIMAL_RESULT) + { + my_decimal tmp, *dec= args[0]->val_decimal(&tmp); + if (!(null_value= args[0]->null_value)) + my_decimal2int(E_DEC_FATAL_ERROR, dec, 1, &value); + return value; + } + else if (args[0]->cast_to_int_type() != STRING_RESULT) { value= args[0]->val_int(); null_value= args[0]->null_value; @@ -2882,6 +2889,20 @@ void Item_udf_func::cleanup() } +void Item_udf_func::print(String *str) +{ + str->append(func_name()); + str->append('('); + for (uint i=0 ; i < arg_count ; i++) + { + if (i != 0) + str->append(','); + args[i]->print_item_w_name(str); + } + str->append(')'); +} + + double Item_func_udf_float::val_real() { DBUG_ASSERT(fixed == 1); diff --git a/sql/item_func.h b/sql/item_func.h index f887ba6a7cd..9d94bc85d2c 100644 --- a/sql/item_func.h +++ b/sql/item_func.h @@ -953,6 +953,7 @@ public: Item_result result_type () const { return udf.result_type(); } table_map not_null_tables() const { return 0; } bool is_expensive() { return 1; } + void print(String *str); }; diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc index 5251bb91a70..d2f8e092d00 100644 --- a/sql/item_subselect.cc +++ b/sql/item_subselect.cc @@ -37,7 +37,7 @@ inline Item * and_items(Item* cond, Item *item) Item_subselect::Item_subselect(): Item_result_field(), value_assigned(0), thd(0), substitution(0), engine(0), old_engine(0), used_tables_cache(0), have_to_be_excluded(0), - const_item_cache(1), engine_changed(0), changed(0) + const_item_cache(1), engine_changed(0), changed(0), is_correlated(FALSE) { with_subselect= 1; reset(); @@ -192,16 +192,16 @@ bool Item_subselect::fix_fields(THD *thd_param, Item **ref) return res; } -bool Item_subselect::exec() +bool Item_subselect::exec(bool full_scan) { int res; - res= engine->exec(); + res= engine->exec(full_scan); if (engine_changed) { engine_changed= 0; - return exec(); + return exec(full_scan); } return (res); } @@ -450,13 +450,13 @@ bool Item_singlerow_subselect::null_inside() void Item_singlerow_subselect::bring_value() { - exec(); + exec(FALSE); } double Item_singlerow_subselect::val_real() { DBUG_ASSERT(fixed == 1); - if (!exec() && !value->null_value) + if (!exec(FALSE) && !value->null_value) { null_value= 0; return value->val_real(); @@ -471,7 +471,7 @@ double Item_singlerow_subselect::val_real() longlong Item_singlerow_subselect::val_int() { DBUG_ASSERT(fixed == 1); - if (!exec() && !value->null_value) + if (!exec(FALSE) && !value->null_value) { null_value= 0; return value->val_int(); @@ -485,7 +485,7 @@ longlong Item_singlerow_subselect::val_int() String *Item_singlerow_subselect::val_str(String *str) { - if (!exec() && !value->null_value) + if (!exec(FALSE) && !value->null_value) { null_value= 0; return value->val_str(str); @@ -500,7 +500,7 @@ String *Item_singlerow_subselect::val_str(String *str) my_decimal *Item_singlerow_subselect::val_decimal(my_decimal *decimal_value) { - if (!exec() && !value->null_value) + if (!exec(FALSE) && !value->null_value) { null_value= 0; return value->val_decimal(decimal_value); @@ -515,7 +515,7 @@ my_decimal *Item_singlerow_subselect::val_decimal(my_decimal *decimal_value) bool Item_singlerow_subselect::val_bool() { - if (!exec() && !value->null_value) + if (!exec(FALSE) && !value->null_value) { null_value= 0; return value->val_bool(); @@ -566,7 +566,8 @@ bool Item_in_subselect::test_limit(SELECT_LEX_UNIT *unit) Item_in_subselect::Item_in_subselect(Item * left_exp, st_select_lex *select_lex): - Item_exists_subselect(), optimizer(0), transformed(0), upper_item(0) + Item_exists_subselect(), optimizer(0), transformed(0), + enable_pushed_conds(TRUE), upper_item(0) { DBUG_ENTER("Item_in_subselect::Item_in_subselect"); left_expr= left_exp; @@ -611,7 +612,7 @@ void Item_exists_subselect::fix_length_and_dec() double Item_exists_subselect::val_real() { DBUG_ASSERT(fixed == 1); - if (exec()) + if (exec(FALSE)) { reset(); return 0; @@ -622,7 +623,7 @@ double Item_exists_subselect::val_real() longlong Item_exists_subselect::val_int() { DBUG_ASSERT(fixed == 1); - if (exec()) + if (exec(FALSE)) { reset(); return 0; @@ -633,7 +634,7 @@ longlong Item_exists_subselect::val_int() String *Item_exists_subselect::val_str(String *str) { DBUG_ASSERT(fixed == 1); - if (exec()) + if (exec(FALSE)) { reset(); return 0; @@ -646,7 +647,7 @@ String *Item_exists_subselect::val_str(String *str) my_decimal *Item_exists_subselect::val_decimal(my_decimal *decimal_value) { DBUG_ASSERT(fixed == 1); - if (exec()) + if (exec(FALSE)) { reset(); return 0; @@ -659,7 +660,7 @@ my_decimal *Item_exists_subselect::val_decimal(my_decimal *decimal_value) bool Item_exists_subselect::val_bool() { DBUG_ASSERT(fixed == 1); - if (exec()) + if (exec(FALSE)) { reset(); return 0; @@ -677,7 +678,7 @@ double Item_in_subselect::val_real() DBUG_ASSERT(0); DBUG_ASSERT(fixed == 1); null_value= 0; - if (exec()) + if (exec(!enable_pushed_conds)) { reset(); null_value= 1; @@ -698,7 +699,7 @@ longlong Item_in_subselect::val_int() DBUG_ASSERT(0); DBUG_ASSERT(fixed == 1); null_value= 0; - if (exec()) + if (exec(!enable_pushed_conds)) { reset(); null_value= 1; @@ -719,7 +720,7 @@ String *Item_in_subselect::val_str(String *str) DBUG_ASSERT(0); DBUG_ASSERT(fixed == 1); null_value= 0; - if (exec()) + if (exec(!enable_pushed_conds)) { reset(); null_value= 1; @@ -738,7 +739,8 @@ String *Item_in_subselect::val_str(String *str) bool Item_in_subselect::val_bool() { DBUG_ASSERT(fixed == 1); - if (exec()) + null_value= 0; + if (exec(!enable_pushed_conds)) { reset(); null_value= 1; @@ -756,8 +758,9 @@ my_decimal *Item_in_subselect::val_decimal(my_decimal *decimal_value) method should not be used */ DBUG_ASSERT(0); + null_value= 0; DBUG_ASSERT(fixed == 1); - if (exec()) + if (exec(!enable_pushed_conds)) { reset(); null_value= 1; @@ -770,7 +773,55 @@ my_decimal *Item_in_subselect::val_decimal(my_decimal *decimal_value) } -/* Rewrite a single-column IN/ALL/ANY subselect. */ +/* + Rewrite a single-column IN/ALL/ANY subselect + + SYNOPSIS + Item_in_subselect::single_value_transformer() + join Join object of the subquery (i.e. 'child' join). + func Subquery comparison creator + + DESCRIPTION + Rewrite a single-column subquery using rule-based approach. The subquery + + oe $cmp$ (SELECT ie FROM ... WHERE subq_where ... HAVING subq_having) + + First, try to convert the subquery to scalar-result subquery in one of + the forms: + + - oe $cmp$ (SELECT MAX(...) ) // handled by Item_singlerow_subselect + - oe $cmp$ <max>(SELECT ...) // handled by Item_maxmin_subselect + + If that fails, the subquery will be handled with class Item_in_optimizer, + Inject the predicates into subquery, i.e. convert it to: + + - If the subquery has aggregates, GROUP BY, or HAVING, convert to + + SELECT ie FROM ... HAVING subq_having AND + trigcond(oe $cmp$ ref_or_null_helper<ie>) + + the addition is wrapped into trigger only when we want to distinguish + between NULL and FALSE results. + + - Otherwise (no aggregates/GROUP BY/HAVING) convert it to one of the + following: + + = If we don't need to distinguish between NULL and FALSE subquery: + + SELECT 1 FROM ... WHERE (oe $cmp$ ie) AND subq_where + + = If we need to distinguish between those: + + SELECT 1 FROM ... + WHERE subq_where AND trigcond((oe $cmp$ ie) OR (ie IS NULL)) + HAVING trigcond(<is_not_null_test>(ie)) + + RETURN + RES_OK - OK, either subquery was transformed, or appopriate + predicates where injected into it. + RES_REDUCE - The subquery was reduced to non-subquery + RES_ERROR - Error +*/ Item_subselect::trans_res Item_in_subselect::single_value_transformer(JOIN *join, @@ -903,8 +954,12 @@ Item_in_subselect::single_value_transformer(JOIN *join, select_lex->uncacheable|= UNCACHEABLE_DEPENDENT; /* Add the left part of a subselect to a WHERE or HAVING clause of - the right part, e.g. SELECT 1 IN (SELECT a FROM t1) => - SELECT Item_in_optimizer(1, SELECT a FROM t1 WHERE a=1) + the right part, e.g. + + SELECT 1 IN (SELECT a FROM t1) => + + SELECT Item_in_optimizer(1, SELECT a FROM t1 WHERE a=1) + HAVING is used only if the right part contains a SUM function, a GROUP BY or a HAVING clause. */ @@ -919,10 +974,15 @@ Item_in_subselect::single_value_transformer(JOIN *join, ref_pointer_array, (char *)"<ref>", this->full_name())); -#ifdef CORRECT_BUT_TOO_SLOW_TO_BE_USABLE - if (!abort_on_null && left_expr->maybe_null) - item= new Item_cond_or(new Item_func_isnull(left_expr), item); -#endif + if (!abort_on_null && ((Item*)select_lex->item_list.head())->maybe_null) + { + /* + We can encounter "NULL IN (SELECT ...)". Wrap the added condition + within a trigger. + */ + item= new Item_func_trig_cond(item, &enable_pushed_conds); + } + /* AND and comparison functions can't be changed during fix_fields() we can assign select_lex->having here, and pass 0 as last @@ -951,19 +1011,19 @@ Item_in_subselect::single_value_transformer(JOIN *join, select_lex->item_list.push_back(new Item_int("Not_used", (longlong) 1, 21)); select_lex->ref_pointer_array[0]= select_lex->item_list.head(); + item= func->create(expr, item); if (!abort_on_null && orig_item->maybe_null) { - having= new Item_is_not_null_test(this, having); + having= + new Item_func_trig_cond(new Item_is_not_null_test(this, having), + &enable_pushed_conds); /* Item_is_not_null_test can't be changed during fix_fields() we can assign select_lex->having here, and pass 0 as last argument (reference) to fix_fields() */ - select_lex->having= - join->having= (join->having ? - new Item_cond_and(having, join->having) : - having); + select_lex->having= join->having= having; select_lex->having_fix_field= 1; /* we do not check join->having->fixed, because Item_and (from @@ -974,12 +1034,15 @@ Item_in_subselect::single_value_transformer(JOIN *join, select_lex->having_fix_field= 0; if (tmp) DBUG_RETURN(RES_ERROR); + /* + NOTE: It is important that we add this "IS NULL" here, even when + orig_item can't be NULL. This is needed so that this predicate is + only used by ref[_or_null] analyzer (and, e.g. is not used by const + propagation). + */ item= new Item_cond_or(item, new Item_func_isnull(orig_item)); -#ifdef CORRECT_BUT_TOO_SLOW_TO_BE_USABLE - if (left_expr->maybe_null) - item= new Item_cond_or(new Item_func_isnull(left_expr), item); -#endif + item= new Item_func_trig_cond(item, &enable_pushed_conds); } item->name= (char *)in_additional_cond; /* @@ -1006,13 +1069,14 @@ Item_in_subselect::single_value_transformer(JOIN *join, we can assign select_lex->having here, and pass 0 as last argument (reference) to fix_fields() */ - select_lex->having= - join->having= - func->create(expr, + Item *new_having= + func->create(expr, new Item_ref_null_helper(&select_lex->context, this, select_lex->ref_pointer_array, (char *)"<no matter>", (char *)"<result>")); + new_having= new Item_func_trig_cond(new_having, &enable_pushed_conds); + select_lex->having= join->having= new_having; select_lex->having_fix_field= 1; /* @@ -1217,6 +1281,8 @@ Item_in_subselect::row_value_transformer(JOIN *join) where_item= and_items(where_item, item); } + if (where_item) + where_item= new Item_func_trig_cond(where_item, &enable_pushed_conds); /* AND can't be changed during fix_fields() we can assign select_lex->where here, and pass 0 as last @@ -1230,6 +1296,8 @@ Item_in_subselect::row_value_transformer(JOIN *join) if (having_item) { bool res; + having_item= new Item_func_trig_cond(having_item, &enable_pushed_conds); + select_lex->having= join->having= and_items(join->having, having_item); select_lex->having->top_level_item(); /* @@ -1446,6 +1514,27 @@ bool subselect_union_engine::is_executed() const } +/* + Check if last execution of the subquery engine produced any rows + + SYNOPSIS + subselect_union_engine::no_rows() + + DESCRIPTION + Check if last execution of the subquery engine produced any rows. The + return value is undefined if last execution ended in an error. + + RETURN + TRUE - Last subselect execution has produced no rows + FALSE - Otherwise +*/ + +bool subselect_union_engine::no_rows() +{ + /* Check if we got any rows when reading UNION result from temp. table: */ + return test(!unit->fake_select_lex->join->send_records); +} + void subselect_uniquesubquery_engine::cleanup() { DBUG_ENTER("subselect_uniquesubquery_engine::cleanup"); @@ -1511,6 +1600,28 @@ int subselect_uniquesubquery_engine::prepare() return 1; } + +/* + Check if last execution of the subquery engine produced any rows + + SYNOPSIS + subselect_single_select_engine::no_rows() + + DESCRIPTION + Check if last execution of the subquery engine produced any rows. The + return value is undefined if last execution ended in an error. + + RETURN + TRUE - Last subselect execution has produced no rows + FALSE - Otherwise +*/ + +bool subselect_single_select_engine::no_rows() +{ + return !item->assigned(); +} + + /* makes storage for the output values for the subquery and calcuates their data and column types and their nullability. @@ -1569,7 +1680,11 @@ void subselect_uniquesubquery_engine::fix_length_and_dec(Item_cache **row) DBUG_ASSERT(0); } -int subselect_single_select_engine::exec() +int init_read_record_seq(JOIN_TAB *tab); +int join_read_always_key_or_null(JOIN_TAB *tab); +int join_read_next_same_or_null(READ_RECORD *info); + +int subselect_single_select_engine::exec(bool full_scan) { DBUG_ENTER("subselect_single_select_engine::exec"); char const *save_where= thd->where; @@ -1607,7 +1722,43 @@ int subselect_single_select_engine::exec() if (!executed) { item->reset_value_registration(); + if (full_scan) + { + /* + We should not apply optimizations based on the condition that was + pushed down into the subquery. Those optimizations are ref[_or_null] + acceses. Change them to be full table scans. + */ + for (uint i=join->const_tables ; i < join->tables ; i++) + { + JOIN_TAB *tab=join->join_tab+i; + if (tab->keyuse && tab->keyuse->outer_ref) + { + tab->read_first_record= init_read_record_seq; + tab->read_record.record= tab->table->record[0]; + tab->read_record.thd= join->thd; + tab->read_record.ref_length= tab->table->file->ref_length; + } + } + } + join->exec(); + + if (full_scan) + { + /* Enable the optimizations back */ + for (uint i=join->const_tables ; i < join->tables ; i++) + { + JOIN_TAB *tab=join->join_tab+i; + if (tab->keyuse && tab->keyuse->outer_ref) + { + tab->read_record.record= 0; + tab->read_record.ref_length= 0; + tab->read_first_record= join_read_always_key_or_null; + tab->read_record.read_record= join_read_next_same_or_null; + } + } + } executed= 1; thd->where= save_where; thd->lex->current_select= save_select; @@ -1618,29 +1769,159 @@ int subselect_single_select_engine::exec() DBUG_RETURN(0); } -int subselect_union_engine::exec() +int subselect_union_engine::exec(bool full_scan) { char const *save_where= thd->where; + /* + Ignore the full_scan parameter: the pushed down predicates are only used + for filtering, and the caller has disabled them if necessary. + */ int res= unit->exec(); thd->where= save_where; return res; } -int subselect_uniquesubquery_engine::exec() +/* + Search for at least on row satisfying select condition + + SYNOPSIS + subselect_uniquesubquery_engine::scan_table() + + DESCRIPTION + Scan the table using sequential access until we find at least one row + satisfying select condition. + + The result of this function (info about whether a row was found) is + stored in this->empty_result_set. + + RETURN + FALSE - OK + TRUE - Error +*/ + +int subselect_uniquesubquery_engine::scan_table() { - DBUG_ENTER("subselect_uniquesubquery_engine::exec"); int error; TABLE *table= tab->table; - for (store_key **copy=tab->ref.key_copy ; *copy ; copy++) + DBUG_ENTER("subselect_uniquesubquery_engine::scan_table"); + empty_result_set= TRUE; + + if (table->file->inited) + table->file->ha_index_end(); + + table->file->ha_rnd_init(1); + table->file->extra_opt(HA_EXTRA_CACHE, + current_thd->variables.read_buff_size); + table->null_row= 0; + for (;;) + { + error=table->file->rnd_next(table->record[0]); + if (error && error != HA_ERR_END_OF_FILE) + { + error= report_error(table, error); + break; + } + /* No more rows */ + if (table->status) + break; + + if (!cond || cond->val_int()) + { + empty_result_set= FALSE; + break; + } + } + + table->file->ha_rnd_end(); + DBUG_RETURN(error != 0); +} + + +/* + Copy ref key and check for null parts in it + + SYNOPSIS + subselect_uniquesubquery_engine::copy_ref_key() + + DESCRIPTION + Copy ref key and check for null parts in it. + + RETURN + FALSE - ok, index lookup key without keys copied. + TRUE - an error occured while copying the key +*/ + +bool subselect_uniquesubquery_engine::copy_ref_key() +{ + DBUG_ENTER("subselect_uniquesubquery_engine::copy_ref_key"); + + for (store_key **copy= tab->ref.key_copy ; *copy ; copy++) { - if ((tab->ref.key_err= (*copy)->copy()) & 1) + tab->ref.key_err= (*copy)->copy(); + + /* + When there is a NULL part in the key we don't need to make index + lookup for such key thus we don't need to copy whole key. + If we later should do a sequential scan return OK. Fail otherwise. + + See also the comment for the subselect_uniquesubquery_engine::exec() + function. + */ + null_keypart= (*copy)->null_key; + bool top_level= ((Item_in_subselect *) item)->is_top_level_item(); + if (null_keypart && !top_level) + break; + if ((tab->ref.key_err) & 1 || (null_keypart && top_level)) { - table->status= STATUS_NOT_FOUND; + tab->table->status= STATUS_NOT_FOUND; DBUG_RETURN(1); } } + DBUG_RETURN(0); +} + + +/* + Execute subselect + + SYNOPSIS + subselect_uniquesubquery_engine::exec() + DESCRIPTION + Find rows corresponding to the ref key using index access. + If some part of the lookup key is NULL, then we're evaluating + NULL IN (SELECT ... ) + This is a special case, we don't need to search for NULL in the table, + instead, the result value is + - NULL if select produces empty row set + - FALSE otherwise. + + In some cases (IN subselect is a top level item, i.e. abort_on_null==TRUE) + the caller doesn't distinguish between NULL and FALSE result and we just + return FALSE. + Otherwise we make a full table scan to see if there is at least one matching row. + + NOTE + + RETURN + FALSE - ok + TRUE - an error occured while scanning +*/ + +int subselect_uniquesubquery_engine::exec(bool full_scan) +{ + DBUG_ENTER("subselect_uniquesubquery_engine::exec"); + int error; + TABLE *table= tab->table; + + /* TODO: change to use of 'full_scan' here? */ + if (copy_ref_key()) + DBUG_RETURN(1); + + if (null_keypart) + DBUG_RETURN(scan_table()); + if (!table->file->inited) table->file->ha_index_init(tab->ref.key); error= table->file->index_read(table->record[0], @@ -1669,14 +1950,68 @@ subselect_uniquesubquery_engine::~subselect_uniquesubquery_engine() } -int subselect_indexsubquery_engine::exec() +/* + Index-lookup subselect 'engine' - run the subquery + + SYNOPSIS + subselect_uniquesubquery_engine:exec() + full_scan + + DESCRIPTION + The engine is used to resolve subqueries in form + + oe IN (SELECT key FROM tbl WHERE subq_where) + + The value of the predicate is calculated as follows: + 1. If oe IS NULL, this is a special case, do a full table scan on + table tbl and search for row that satisfies subq_where. If such + row is found, return NULL, otherwise return FALSE. + 2. Make an index lookup via key=oe, search for a row that satisfies + subq_where. If found, return TRUE. + 3. If check_null==TRUE, make another lookup via key=NULL, search for a + row that satisfies subq_where. If found, return NULL, otherwise + return FALSE. + + TODO + The step #1 can be optimized further when the index has several key + parts. Consider a subquery: + + (oe1, oe2) IN (SELECT keypart1, keypart2 FROM tbl WHERE subq_where) + + and suppose we need to evaluate it for {oe1, oe2}=={const1, NULL}. + Current code will do a full table scan and obtain correct result. There + is a better option: instead of evaluating + + SELECT keypart1, keypart2 FROM tbl WHERE subq_where (1) + + and checking if it has produced any matching rows, evaluate + + SELECT keypart2 FROM tbl WHERE subq_where AND keypart1=const1 (2) + + If this query produces a row, the result is NULL (as we're evaluating + "(const1, NULL) IN { (const1, X), ... }", which has a value of UNKNOWN, + i.e. NULL). If the query produces no rows, the result is FALSE. + + We currently evaluate (1) by doing a full table scan. (2) can be + evaluated by doing a "ref" scan on "keypart1=const1", which can be much + cheaper. We can use index statistics to quickly check whether "ref" scan + will be cheaper than full table scan. + + RETURN + 0 + 1 +*/ + +int subselect_indexsubquery_engine::exec(bool full_scan) { - DBUG_ENTER("subselect_indexsubselect_engine::exec"); + DBUG_ENTER("subselect_indexsubquery_engine::exec"); int error; bool null_finding= 0; TABLE *table= tab->table; ((Item_in_subselect *) item)->value= 0; + empty_result_set= TRUE; + null_keypart= 0; if (check_null) { @@ -1685,14 +2020,12 @@ int subselect_indexsubquery_engine::exec() ((Item_in_subselect *) item)->was_null= 0; } - for (store_key **copy=tab->ref.key_copy ; *copy ; copy++) - { - if ((tab->ref.key_err= (*copy)->copy()) & 1) - { - table->status= STATUS_NOT_FOUND; - DBUG_RETURN(1); - } - } + /* Copy the ref key and check for nulls... */ + if (copy_ref_key()) + DBUG_RETURN(1); + + if (null_keypart) + DBUG_RETURN(scan_table()); if (!table->file->inited) table->file->ha_index_init(tab->ref.key); diff --git a/sql/item_subselect.h b/sql/item_subselect.h index a48ff3e2429..f1be99353cc 100644 --- a/sql/item_subselect.h +++ b/sql/item_subselect.h @@ -60,6 +60,9 @@ public: /* subquery is transformed */ bool changed; + /* TRUE <=> The underlying SELECT is correlated w.r.t some ancestor select */ + bool is_correlated; + enum trans_res {RES_OK, RES_REDUCE, RES_ERROR}; enum subs_type {UNKNOWN_SUBS, SINGLEROW_SUBS, EXISTS_SUBS, IN_SUBS, ALL_SUBS, ANY_SUBS}; @@ -92,7 +95,7 @@ public: return null_value; } bool fix_fields(THD *thd, Item **ref); - virtual bool exec(); + virtual bool exec(bool full_scan); virtual void fix_length_and_dec(); table_map used_tables() const; table_map not_null_tables() const { return 0; } @@ -114,6 +117,7 @@ public: single select and union subqueries only. */ bool is_evaluated() const; + bool is_uncacheable() const; /* Used by max/min subquery to initialize value presence registration @@ -216,7 +220,20 @@ public: friend class subselect_indexsubquery_engine; }; -/* IN subselect */ + +/* + IN subselect: this represents "left_exr IN (SELECT ...)" + + This class has: + - (as a descendant of Item_subselect) a "subquery execution engine" which + allows it to evaluate subqueries. (and this class participates in + execution by having was_null variable where part of execution result + is stored. + - Transformation methods (todo: more on this). + + This class is not used directly, it is "wrapped" into Item_in_optimizer + which provides some small bits of subquery evaluation. +*/ class Item_in_subselect :public Item_exists_subselect { @@ -232,12 +249,14 @@ protected: bool abort_on_null; bool transformed; public: + /* Used to trigger on/off conditions that were pushed down to subselect */ + bool enable_pushed_conds; Item_func_not_all *upper_item; // point on NOT/NOP before ALL/SOME subquery Item_in_subselect(Item * left_expr, st_select_lex *select_lex); Item_in_subselect() :Item_exists_subselect(), optimizer(0), abort_on_null(0), transformed(0), - upper_item(0) + enable_pushed_conds(TRUE), upper_item(0) {} subs_type substype() { return IN_SUBS; } @@ -257,6 +276,7 @@ public: my_decimal *val_decimal(my_decimal *); bool val_bool(); void top_level_item() { abort_on_null=1; } + inline bool is_top_level_item() { return abort_on_null; } bool test_limit(st_select_lex_unit *unit); void print(String *str); bool fix_fields(THD *thd, Item **ref); @@ -316,7 +336,28 @@ public: THD * get_thd() { return thd; } virtual int prepare()= 0; virtual void fix_length_and_dec(Item_cache** row)= 0; - virtual int exec()= 0; + /* + Execute the engine + + SYNOPSIS + exec() + full_scan TRUE - Pushed-down predicates are disabled, the engine + must disable made based on those predicates. + FALSE - Pushed-down predicates are in effect. + DESCRIPTION + Execute the engine. The result of execution is subquery value that is + either captured by previously set up select_result-based 'sink' or + stored somewhere by the exec() method itself. + + A required side effect: if full_scan==TRUE, subselect_engine->no_rows() + should return correct result. + + RETURN + 0 - OK + 1 - Either an execution error, or the engine was be "changed", and + caller should call exec() again for the new engine. + */ + virtual int exec(bool full_scan)= 0; virtual uint cols()= 0; /* return number of columns in select */ virtual uint8 uncacheable()= 0; /* query is uncacheable */ enum Item_result type() { return res_type; } @@ -329,6 +370,11 @@ public: virtual bool change_result(Item_subselect *si, select_subselect *result)= 0; virtual bool no_tables()= 0; virtual bool is_executed() const { return FALSE; } + /* Check if subquery produced any rows during last query execution */ + virtual bool no_rows() = 0; + +protected: + void set_row(List<Item> &item_list, Item_cache **row); }; @@ -346,7 +392,7 @@ public: void cleanup(); int prepare(); void fix_length_and_dec(Item_cache** row); - int exec(); + int exec(bool full_scan); uint cols(); uint8 uncacheable(); void exclude(); @@ -355,6 +401,7 @@ public: bool change_result(Item_subselect *si, select_subselect *result); bool no_tables(); bool is_executed() const { return executed; } + bool no_rows(); }; @@ -368,7 +415,7 @@ public: void cleanup(); int prepare(); void fix_length_and_dec(Item_cache** row); - int exec(); + int exec(bool full_scan); uint cols(); uint8 uncacheable(); void exclude(); @@ -377,6 +424,7 @@ public: bool change_result(Item_subselect *si, select_subselect *result); bool no_tables(); bool is_executed() const; + bool no_rows(); }; @@ -386,6 +434,12 @@ class subselect_uniquesubquery_engine: public subselect_engine protected: st_join_table *tab; Item *cond; + /* + TRUE<=> last execution produced empty set. Valid only when left + expression is NULL. + */ + bool empty_result_set; + bool null_keypart; /* TRUE <=> constructed search tuple has a NULL */ public: // constructor can assign THD because it will be called after JOIN::prepare @@ -399,7 +453,7 @@ public: void cleanup(); int prepare(); void fix_length_and_dec(Item_cache** row); - int exec(); + int exec(bool full_scan); uint cols() { return 1; } uint8 uncacheable() { return UNCACHEABLE_DEPENDENT; } void exclude(); @@ -407,11 +461,15 @@ public: void print (String *str); bool change_result(Item_subselect *si, select_subselect *result); bool no_tables(); + int scan_table(); + bool copy_ref_key(); + bool no_rows() { return empty_result_set; } }; class subselect_indexsubquery_engine: public subselect_uniquesubquery_engine { + /* FALSE for 'ref', TRUE for 'ref-or-null'. */ bool check_null; public: @@ -422,7 +480,7 @@ public: :subselect_uniquesubquery_engine(thd, tab_arg, subs, where), check_null(chk_null) {} - int exec(); + int exec(bool full_scan); void print (String *str); }; @@ -432,3 +490,9 @@ inline bool Item_subselect::is_evaluated() const return engine->is_executed(); } +inline bool Item_subselect::is_uncacheable() const +{ + return engine->uncacheable(); +} + + diff --git a/sql/item_sum.cc b/sql/item_sum.cc index f1e0001c994..c2219aafd03 100644 --- a/sql/item_sum.cc +++ b/sql/item_sum.cc @@ -893,6 +893,7 @@ bool Item_sum_distinct::setup(THD *thd) tree= new Unique(simple_raw_key_cmp, &tree_key_length, tree_key_length, thd->variables.max_heap_table_size); + is_evaluated= FALSE; DBUG_RETURN(tree == 0); } @@ -900,6 +901,7 @@ bool Item_sum_distinct::setup(THD *thd) bool Item_sum_distinct::add() { args[0]->save_in_field(table->field[0], FALSE); + is_evaluated= FALSE; if (!table->field[0]->is_null()) { DBUG_ASSERT(tree); @@ -929,6 +931,7 @@ void Item_sum_distinct::clear() DBUG_ASSERT(tree != 0); /* we always have a tree */ null_value= 1; tree->reset(); + is_evaluated= FALSE; DBUG_VOID_RETURN; } @@ -938,6 +941,7 @@ void Item_sum_distinct::cleanup() delete tree; tree= 0; table= 0; + is_evaluated= FALSE; } Item_sum_distinct::~Item_sum_distinct() @@ -949,16 +953,20 @@ Item_sum_distinct::~Item_sum_distinct() void Item_sum_distinct::calculate_val_and_count() { - count= 0; - val.traits->set_zero(&val); - /* - We don't have a tree only if 'setup()' hasn't been called; - this is the case of sql_select.cc:return_zero_rows. - */ - if (tree) + if (!is_evaluated) { - table->field[0]->set_notnull(); - tree->walk(item_sum_distinct_walk, (void*) this); + count= 0; + val.traits->set_zero(&val); + /* + We don't have a tree only if 'setup()' hasn't been called; + this is the case of sql_select.cc:return_zero_rows. + */ + if (tree) + { + table->field[0]->set_notnull(); + tree->walk(item_sum_distinct_walk, (void*) this); + } + is_evaluated= TRUE; } } @@ -1014,9 +1022,13 @@ Item_sum_avg_distinct::fix_length_and_dec() void Item_sum_avg_distinct::calculate_val_and_count() { - Item_sum_distinct::calculate_val_and_count(); - if (count) - val.traits->div(&val, count); + if (!is_evaluated) + { + Item_sum_distinct::calculate_val_and_count(); + if (count) + val.traits->div(&val, count); + is_evaluated= TRUE; + } } @@ -2478,6 +2490,7 @@ void Item_sum_count_distinct::cleanup() */ delete tree; tree= 0; + is_evaluated= FALSE; if (table) { free_tmp_table(table->in_use, table); @@ -2499,6 +2512,7 @@ void Item_sum_count_distinct::make_unique() original= 0; force_copy_fields= 1; tree= 0; + is_evaluated= FALSE; tmp_table_param= 0; always_null= FALSE; } @@ -2618,6 +2632,7 @@ bool Item_sum_count_distinct::setup(THD *thd) but this has to be handled - otherwise someone can crash the server with a DoS attack */ + is_evaluated= FALSE; if (! tree) return TRUE; } @@ -2634,8 +2649,11 @@ Item *Item_sum_count_distinct::copy_or_same(THD* thd) void Item_sum_count_distinct::clear() { /* tree and table can be both null only if always_null */ + is_evaluated= FALSE; if (tree) + { tree->reset(); + } else if (table) { table->file->extra(HA_EXTRA_NO_CACHE); @@ -2656,6 +2674,7 @@ bool Item_sum_count_distinct::add() if ((*field)->is_real_null(0)) return 0; // Don't count NULL + is_evaluated= FALSE; if (tree) { /* @@ -2682,12 +2701,14 @@ longlong Item_sum_count_distinct::val_int() return LL(0); if (tree) { - ulonglong count; + if (is_evaluated) + return count; if (tree->elements == 0) return (longlong) tree->elements_in_tree(); // everything fits in memory count= 0; tree->walk(count_distinct_walk, (void*) &count); + is_evaluated= TRUE; return (longlong) count; } diff --git a/sql/item_sum.h b/sql/item_sum.h index fe7edd76ecf..c11ef7e548a 100644 --- a/sql/item_sum.h +++ b/sql/item_sum.h @@ -352,12 +352,23 @@ public: class Item_sum_num :public Item_sum { +protected: + /* + val_xxx() functions may be called several times during the execution of a + query. Derived classes that require extensive calculation in val_xxx() + maintain cache of aggregate value. This variable governs the validity of + that cache. + */ + bool is_evaluated; public: - Item_sum_num() :Item_sum() {} - Item_sum_num(Item *item_par) :Item_sum(item_par) {} - Item_sum_num(Item *a, Item* b) :Item_sum(a,b) {} - Item_sum_num(List<Item> &list) :Item_sum(list) {} - Item_sum_num(THD *thd, Item_sum_num *item) :Item_sum(thd, item) {} + Item_sum_num() :Item_sum(),is_evaluated(FALSE) {} + Item_sum_num(Item *item_par) + :Item_sum(item_par), is_evaluated(FALSE) {} + Item_sum_num(Item *a, Item* b) :Item_sum(a,b),is_evaluated(FALSE) {} + Item_sum_num(List<Item> &list) + :Item_sum(list), is_evaluated(FALSE) {} + Item_sum_num(THD *thd, Item_sum_num *item) + :Item_sum(thd, item),is_evaluated(item->is_evaluated) {} bool fix_fields(THD *, Item **); longlong val_int() { @@ -540,6 +551,12 @@ class Item_sum_count_distinct :public Item_sum_int */ Unique *tree; /* + Storage for the value of count between calls to val_int() so val_int() + will not recalculate on each call. Validitiy of the value is stored in + is_evaluated. + */ + longlong count; + /* Following is 0 normal object and pointer to original one for copy (to correctly free resources) */ @@ -556,14 +573,15 @@ class Item_sum_count_distinct :public Item_sum_int public: Item_sum_count_distinct(List<Item> &list) :Item_sum_int(list), table(0), field_lengths(0), tmp_table_param(0), - force_copy_fields(0), tree(0), original(0), always_null(FALSE) + force_copy_fields(0), tree(0), count(0), + original(0), always_null(FALSE) { quick_group= 0; } Item_sum_count_distinct(THD *thd, Item_sum_count_distinct *item) :Item_sum_int(thd, item), table(item->table), field_lengths(item->field_lengths), tmp_table_param(item->tmp_table_param), - force_copy_fields(0), tree(item->tree), original(item), - tree_key_length(item->tree_key_length), + force_copy_fields(0), tree(item->tree), count(item->count), + original(item), tree_key_length(item->tree_key_length), always_null(item->always_null) {} ~Item_sum_count_distinct(); diff --git a/sql/item_timefunc.cc b/sql/item_timefunc.cc index a776d8a5ff7..232ad4b9213 100644 --- a/sql/item_timefunc.cc +++ b/sql/item_timefunc.cc @@ -1413,17 +1413,6 @@ String *Item_date::val_str(String *str) } -int Item_date::save_in_field(Field *field, bool no_conversions) -{ - TIME ltime; - if (get_date(<ime, TIME_FUZZY_DATE)) - return set_field_to_null(field); - field->set_notnull(); - field->store_time(<ime, MYSQL_TIMESTAMP_DATE); - return 0; -} - - longlong Item_date::val_int() { DBUG_ASSERT(fixed == 1); diff --git a/sql/item_timefunc.h b/sql/item_timefunc.h index e79c62e6ffb..68e8700d1b0 100644 --- a/sql/item_timefunc.h +++ b/sql/item_timefunc.h @@ -339,12 +339,20 @@ public: decimals=0; max_length=MAX_DATE_WIDTH*MY_CHARSET_BIN_MB_MAXLEN; } - int save_in_field(Field *to, bool no_conversions); Field *tmp_table_field(TABLE *t_arg) { return (new Field_date(maybe_null, name, t_arg, &my_charset_bin)); } bool result_as_longlong() { return TRUE; } + my_decimal *val_decimal(my_decimal *decimal_value) + { + DBUG_ASSERT(fixed == 1); + return val_decimal_from_date(decimal_value); + } + int save_in_field(Field *field, bool no_conversions) + { + return save_date_in_field(field); + } }; @@ -361,21 +369,57 @@ public: return (new Field_datetime(maybe_null, name, t_arg, &my_charset_bin)); } bool result_as_longlong() { return TRUE; } + my_decimal *val_decimal(my_decimal *decimal_value) + { + DBUG_ASSERT(fixed == 1); + return val_decimal_from_date(decimal_value); + } + int save_in_field(Field *field, bool no_conversions) + { + return save_date_in_field(field); + } +}; + + +class Item_str_timefunc :public Item_str_func +{ +public: + Item_str_timefunc() :Item_str_func() {} + Item_str_timefunc(Item *a) :Item_str_func(a) {} + Item_str_timefunc(Item *a,Item *b) :Item_str_func(a,b) {} + Item_str_timefunc(Item *a, Item *b, Item *c) :Item_str_func(a, b ,c) {} + enum_field_types field_type() const { return MYSQL_TYPE_TIME; } + void fix_length_and_dec() + { + decimals=0; + max_length=MAX_TIME_WIDTH*MY_CHARSET_BIN_MB_MAXLEN; + } + Field *tmp_table_field(TABLE *t_arg) + { + return (new Field_time(maybe_null, name, t_arg, &my_charset_bin)); + } + my_decimal *val_decimal(my_decimal *decimal_value) + { + DBUG_ASSERT(fixed == 1); + return val_decimal_from_time(decimal_value); + } + int save_in_field(Field *field, bool no_conversions) + { + return save_time_in_field(field); + } }; /* Abstract CURTIME function. Children should define what time zone is used */ -class Item_func_curtime :public Item_func +class Item_func_curtime :public Item_str_timefunc { longlong value; char buff[9*2+32]; uint buff_length; public: - Item_func_curtime() :Item_func() {} - Item_func_curtime(Item *a) :Item_func(a) {} - enum Item_result result_type () const { return STRING_RESULT; } - enum_field_types field_type() const { return MYSQL_TYPE_TIME; } + Item_func_curtime() :Item_str_timefunc() {} + Item_func_curtime(Item *a) :Item_str_timefunc(a) {} double val_real() { DBUG_ASSERT(fixed == 1); return (double) value; } longlong val_int() { DBUG_ASSERT(fixed == 1); return value; } String *val_str(String *str); @@ -602,10 +646,10 @@ class Item_func_convert_tz :public Item_date_func }; -class Item_func_sec_to_time :public Item_str_func +class Item_func_sec_to_time :public Item_str_timefunc { public: - Item_func_sec_to_time(Item *item) :Item_str_func(item) {} + Item_func_sec_to_time(Item *item) :Item_str_timefunc(item) {} double val_real() { DBUG_ASSERT(fixed == 1); @@ -615,17 +659,12 @@ public: String *val_str(String *); void fix_length_and_dec() { + Item_str_timefunc::fix_length_and_dec(); collation.set(&my_charset_bin); maybe_null=1; decimals= DATETIME_DEC; - max_length=MAX_TIME_WIDTH*MY_CHARSET_BIN_MB_MAXLEN; } - enum_field_types field_type() const { return MYSQL_TYPE_TIME; } const char *func_name() const { return "sec_to_time"; } - Field *tmp_table_field(TABLE *t_arg) - { - return (new Field_time(maybe_null, name, t_arg, &my_charset_bin)); - } bool result_as_longlong() { return TRUE; } }; @@ -762,6 +801,15 @@ public: } bool result_as_longlong() { return TRUE; } longlong val_int(); + my_decimal *val_decimal(my_decimal *decimal_value) + { + DBUG_ASSERT(fixed == 1); + return val_decimal_from_date(decimal_value); + } + int save_in_field(Field *field, bool no_conversions) + { + return save_date_in_field(field); + } }; @@ -780,6 +828,15 @@ public: } bool result_as_longlong() { return TRUE; } longlong val_int(); + my_decimal *val_decimal(my_decimal *decimal_value) + { + DBUG_ASSERT(fixed == 1); + return val_decimal_from_time(decimal_value); + } + int save_in_field(Field *field, bool no_conversions) + { + return save_time_in_field(field); + } }; @@ -797,12 +854,21 @@ public: } bool result_as_longlong() { return TRUE; } longlong val_int(); + my_decimal *val_decimal(my_decimal *decimal_value) + { + DBUG_ASSERT(fixed == 1); + return val_decimal_from_date(decimal_value); + } + int save_in_field(Field *field, bool no_conversions) + { + return save_date_in_field(field); + } }; -class Item_func_makedate :public Item_str_func +class Item_func_makedate :public Item_date_func { public: - Item_func_makedate(Item *a,Item *b) :Item_str_func(a,b) {} + Item_func_makedate(Item *a,Item *b) :Item_date_func(a,b) {} String *val_str(String *str); const char *func_name() const { return "makedate"; } enum_field_types field_type() const { return MYSQL_TYPE_DATE; } @@ -815,8 +881,16 @@ public: { return (new Field_date(maybe_null, name, t_arg, &my_charset_bin)); } - bool result_as_longlong() { return TRUE; } longlong val_int(); + my_decimal *val_decimal(my_decimal *decimal_value) + { + DBUG_ASSERT(fixed == 1); + return val_decimal_from_date(decimal_value); + } + int save_in_field(Field *field, bool no_conversions) + { + return save_date_in_field(field); + } }; @@ -848,45 +922,46 @@ public: } void print(String *str); const char *func_name() const { return "add_time"; } + my_decimal *val_decimal(my_decimal *decimal_value) + { + DBUG_ASSERT(fixed == 1); + if (cached_field_type == MYSQL_TYPE_TIME) + return val_decimal_from_time(decimal_value); + if (cached_field_type == MYSQL_TYPE_DATETIME) + return val_decimal_from_date(decimal_value); + return Item_str_func::val_decimal(decimal_value); + } + int save_in_field(Field *field, bool no_conversions) + { + if (cached_field_type == MYSQL_TYPE_TIME) + return save_time_in_field(field); + if (cached_field_type == MYSQL_TYPE_DATETIME) + return save_date_in_field(field); + return Item_str_func::save_in_field(field, no_conversions); + } }; -class Item_func_timediff :public Item_str_func +class Item_func_timediff :public Item_str_timefunc { public: Item_func_timediff(Item *a, Item *b) - :Item_str_func(a, b) {} + :Item_str_timefunc(a, b) {} String *val_str(String *str); const char *func_name() const { return "timediff"; } - enum_field_types field_type() const { return MYSQL_TYPE_TIME; } void fix_length_and_dec() { - decimals=0; - max_length=MAX_TIME_WIDTH*MY_CHARSET_BIN_MB_MAXLEN; + Item_str_timefunc::fix_length_and_dec(); maybe_null= 1; } - Field *tmp_table_field(TABLE *t_arg) - { - return (new Field_time(maybe_null, name, t_arg, &my_charset_bin)); - } }; -class Item_func_maketime :public Item_str_func +class Item_func_maketime :public Item_str_timefunc { public: Item_func_maketime(Item *a, Item *b, Item *c) - :Item_str_func(a, b ,c) {} + :Item_str_timefunc(a, b ,c) {} String *val_str(String *str); const char *func_name() const { return "maketime"; } - enum_field_types field_type() const { return MYSQL_TYPE_TIME; } - void fix_length_and_dec() - { - decimals=0; - max_length=MAX_TIME_WIDTH*MY_CHARSET_BIN_MB_MAXLEN; - } - Field *tmp_table_field(TABLE *t_arg) - { - return (new Field_time(maybe_null, name, t_arg, &my_charset_bin)); - } }; class Item_func_microsecond :public Item_int_func diff --git a/sql/my_decimal.cc b/sql/my_decimal.cc index 1bd16940b47..f33609e0168 100644 --- a/sql/my_decimal.cc +++ b/sql/my_decimal.cc @@ -15,6 +15,8 @@ Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA */ #include "mysql_priv.h" +#include <time.h> + #ifndef MYSQL_CLIENT /* @@ -190,6 +192,23 @@ int str2my_decimal(uint mask, const char *from, uint length, } +my_decimal *date2my_decimal(TIME *ltime, my_decimal *dec) +{ + longlong date; + date = (ltime->year*100L + ltime->month)*100L + ltime->day; + if (ltime->time_type > MYSQL_TIMESTAMP_DATE) + date= ((date*100L + ltime->hour)*100L+ ltime->minute)*100L + ltime->second; + if (int2my_decimal(E_DEC_FATAL_ERROR, date, FALSE, dec)) + return dec; + if (ltime->second_part) + { + dec->buf[(dec->intg-1) / 9 + 1]= ltime->second_part * 1000; + dec->frac= 6; + } + return dec; +} + + #ifndef DBUG_OFF /* routines for debugging print */ diff --git a/sql/my_decimal.h b/sql/my_decimal.h index b02abacf0a3..af3edade8d6 100644 --- a/sql/my_decimal.h +++ b/sql/my_decimal.h @@ -295,7 +295,12 @@ int string2my_decimal(uint mask, const String *str, my_decimal *d) { return str2my_decimal(mask, str->ptr(), str->length(), str->charset(), d); } -#endif + + +my_decimal *date2my_decimal(TIME *ltime, my_decimal *dec); + + +#endif /*defined(MYSQL_SERVER) || defined(EMBEDDED_LIBRARY) */ inline int double2my_decimal(uint mask, double val, my_decimal *d) diff --git a/sql/mysql_priv.h b/sql/mysql_priv.h index ac0b9f56567..a63b714e0b0 100644 --- a/sql/mysql_priv.h +++ b/sql/mysql_priv.h @@ -1482,7 +1482,7 @@ void end_read_record(READ_RECORD *info); ha_rows filesort(THD *thd, TABLE *form,struct st_sort_field *sortorder, uint s_length, SQL_SELECT *select, ha_rows max_rows, ha_rows *examined_rows); -void filesort_free_buffers(TABLE *table); +void filesort_free_buffers(TABLE *table, bool full); void change_double_for_sort(double nr,byte *to); double my_double_round(double value, int dec, bool truncate); int get_quick_record(SQL_SELECT *select); diff --git a/sql/records.cc b/sql/records.cc index b352f9f395a..78473659b22 100644 --- a/sql/records.cc +++ b/sql/records.cc @@ -20,7 +20,7 @@ #include "mysql_priv.h" static int rr_quick(READ_RECORD *info); -static int rr_sequential(READ_RECORD *info); +int rr_sequential(READ_RECORD *info); static int rr_from_tempfile(READ_RECORD *info); static int rr_unpack_from_tempfile(READ_RECORD *info); static int rr_unpack_from_buffer(READ_RECORD *info); @@ -184,6 +184,7 @@ void init_read_record(READ_RECORD *info,THD *thd, TABLE *table, } /* init_read_record */ + void end_read_record(READ_RECORD *info) { /* free cache if used */ if (info->cache) @@ -193,7 +194,7 @@ void end_read_record(READ_RECORD *info) } if (info->table) { - filesort_free_buffers(info->table); + filesort_free_buffers(info->table,0); (void) info->file->extra(HA_EXTRA_NO_CACHE); if (info->read_record != rr_quick) // otherwise quick_range does it (void) info->file->ha_index_or_rnd_end(); @@ -289,7 +290,7 @@ static int rr_index(READ_RECORD *info) } -static int rr_sequential(READ_RECORD *info) +int rr_sequential(READ_RECORD *info) { int tmp; while ((tmp=info->file->rnd_next(info->record))) diff --git a/sql/sql_base.cc b/sql/sql_base.cc index 0f6715fc078..7c652fdcd4f 100644 --- a/sql/sql_base.cc +++ b/sql/sql_base.cc @@ -1483,6 +1483,7 @@ TABLE *open_table(THD *thd, TABLE_LIST *table_list, MEM_ROOT *mem_root, table->file->ft_handler= 0; if (table->timestamp_field) table->timestamp_field_type= table->timestamp_field->get_auto_set_type(); + table->pos_in_table_list= table_list; table_list->updatable= 1; // It is not derived table nor non-updatable VIEW DBUG_ASSERT(table->key_read == 0); DBUG_RETURN(table); @@ -2767,6 +2768,7 @@ TABLE *open_temporary_table(THD *thd, const char *path, const char *db, if (thd->slave_thread) slave_open_temp_tables++; } + tmp_table->pos_in_table_list= 0; DBUG_RETURN(tmp_table); } diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc index f39cf39f08e..3de842c8551 100644 --- a/sql/sql_lex.cc +++ b/sql/sql_lex.cc @@ -162,6 +162,7 @@ void lex_start(THD *thd, uchar *buf,uint length) lex->select_lex.ftfunc_list= &lex->select_lex.ftfunc_list_alloc; lex->select_lex.group_list.empty(); lex->select_lex.order_list.empty(); + lex->select_lex.udf_list.empty(); lex->current_select= &lex->select_lex; lex->yacc_yyss=lex->yacc_yyvs=0; lex->ignore_space=test(thd->variables.sql_mode & MODE_IGNORE_SPACE); @@ -1165,6 +1166,7 @@ void st_select_lex::init_select() braces= 0; when_list.empty(); expr_list.empty(); + udf_list.empty(); interval_list.empty(); use_index.empty(); ftfunc_list_alloc.empty(); @@ -1178,7 +1180,7 @@ void st_select_lex::init_select() select_limit= 0; /* denotes the default limit = HA_POS_ERROR */ offset_limit= 0; /* denotes the default offset = 0 */ with_sum_func= 0; - + is_correlated= 0; } /* @@ -1372,6 +1374,8 @@ void st_select_lex::mark_as_dependent(SELECT_LEX *last) SELECT_LEX_UNIT *munit= s->master_unit(); munit->uncacheable|= UNCACHEABLE_DEPENDENT; } + is_correlated= TRUE; + this->master_unit()->item->is_correlated= TRUE; } bool st_select_lex_node::set_braces(bool value) { return 1; } diff --git a/sql/sql_lex.h b/sql/sql_lex.h index 398e5666c7f..5f968252cc3 100644 --- a/sql/sql_lex.h +++ b/sql/sql_lex.h @@ -470,7 +470,7 @@ public: void set_thd(THD *thd_arg) { thd= thd_arg; } friend void lex_start(THD *thd, uchar *buf, uint length); - friend int subselect_union_engine::exec(); + friend int subselect_union_engine::exec(bool); List<Item> *get_unit_column_types(); }; @@ -562,6 +562,8 @@ public: query processing end even if we use temporary table */ bool subquery_in_having; + /* TRUE <=> this SELECT is correlated w.r.t. some ancestor select */ + bool is_correlated; /* This variable is required to ensure proper work of subqueries and stored procedures. Generally, one should use the states of @@ -581,6 +583,8 @@ public: /* exclude this select from check of unique_table() */ bool exclude_from_table_unique_test; + List<udf_func> udf_list; /* udf function calls stack */ + void init_query(); void init_select(); st_select_lex_unit* master_unit(); diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc index e3ee9f340cf..9ed29149831 100644 --- a/sql/sql_parse.cc +++ b/sql/sql_parse.cc @@ -3451,8 +3451,12 @@ end_with_restore_list: if (first_table->lock_type == TL_WRITE_CONCURRENT_INSERT && thd->lock) { + /* INSERT ... SELECT should invalidate only the very first table */ + TABLE_LIST *save_table= first_table->next_local; + first_table->next_local= 0; mysql_unlock_tables(thd, thd->lock); query_cache_invalidate3(thd, first_table, 1); + first_table->next_local= save_table; thd->lock=0; } delete result; diff --git a/sql/sql_select.cc b/sql/sql_select.cc index abadd0697ac..adb57e3e9f4 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -158,8 +158,8 @@ static int join_read_prev_same(READ_RECORD *info); static int join_read_prev(READ_RECORD *info); static int join_ft_read_first(JOIN_TAB *tab); static int join_ft_read_next(READ_RECORD *info); -static int join_read_always_key_or_null(JOIN_TAB *tab); -static int join_read_next_same_or_null(READ_RECORD *info); +int join_read_always_key_or_null(JOIN_TAB *tab); +int join_read_next_same_or_null(READ_RECORD *info); static COND *make_cond_for_table(COND *cond,table_map table, table_map used_table); static Item* part_of_refkey(TABLE *form,Field *field); @@ -512,11 +512,12 @@ err: DBUG_RETURN(-1); /* purecov: inspected */ } + /* test if it is known for optimisation IN subquery - SYNOPSYS - JOIN::test_in_subselect + SYNOPSIS + JOIN::test_in_subselect() where - pointer for variable in which conditions should be stored if subquery is known @@ -551,6 +552,35 @@ bool JOIN::test_in_subselect(Item **where) /* + Check if the passed HAVING clause is a clause added by subquery optimizer + + SYNOPSIS + is_having_subq_predicates() + having Having clause + + RETURN + TRUE The passed HAVING clause was added by the subquery optimizer + FALSE Otherwise +*/ + +bool is_having_subq_predicates(Item *having) +{ + if (having->type() == Item::FUNC_ITEM) + { + if (((Item_func *) having)->functype() == Item_func::ISNOTNULLTEST_FUNC) + return TRUE; + if (((Item_func *) having)->functype() == Item_func::TRIG_COND_FUNC) + { + having= ((Item_func*)having)->arguments()[0]; + if (((Item_func *) having)->functype() == Item_func::ISNOTNULLTEST_FUNC) + return TRUE; + } + return TRUE; + } + return FALSE; +} + +/* global select optimisation. return 0 - success 1 - error @@ -1018,9 +1048,7 @@ JOIN::optimize() } } else if (join_tab[0].type == JT_REF_OR_NULL && join_tab[0].ref.items[0]->name == in_left_expr_name && - having->type() == Item::FUNC_ITEM && - ((Item_func *) having)->functype() == - Item_func::ISNOTNULLTEST_FUNC) + is_having_subq_predicates(having)) { join_tab[0].type= JT_INDEX_SUBQUERY; error= 0; @@ -1273,14 +1301,14 @@ JOIN::reinit() exec_tmp_table1->file->extra(HA_EXTRA_RESET_STATE); exec_tmp_table1->file->delete_all_rows(); free_io_cache(exec_tmp_table1); - filesort_free_buffers(exec_tmp_table1); + filesort_free_buffers(exec_tmp_table1,0); } if (exec_tmp_table2) { exec_tmp_table2->file->extra(HA_EXTRA_RESET_STATE); exec_tmp_table2->file->delete_all_rows(); free_io_cache(exec_tmp_table2); - filesort_free_buffers(exec_tmp_table2); + filesort_free_buffers(exec_tmp_table2,0); } if (items0) set_items_ref_array(items0); @@ -2519,6 +2547,9 @@ typedef struct key_field_t { // Used when finding key fields when val IS NULL. */ bool null_rejecting; + + /* TRUE<=> This ref access is an outer subquery reference access */ + bool outer_ref; } KEY_FIELD; /* Values in optimize */ @@ -2817,6 +2848,7 @@ add_key_field(KEY_FIELD **key_fields,uint and_level, Item_func *cond, cond->functype() == Item_func::MULT_EQUAL_FUNC) && ((*value)->type() == Item::FIELD_ITEM) && ((Item_field*)*value)->field->maybe_null()); + (*key_fields)->outer_ref= FALSE; (*key_fields)++; } @@ -2875,7 +2907,7 @@ add_key_equal_fields(KEY_FIELD **key_fields, uint and_level, } static void -add_key_fields(KEY_FIELD **key_fields,uint *and_level, +add_key_fields(JOIN *join, KEY_FIELD **key_fields, uint *and_level, COND *cond, table_map usable_tables, SARGABLE_PARAM **sargables) { @@ -2888,28 +2920,56 @@ add_key_fields(KEY_FIELD **key_fields,uint *and_level, { Item *item; while ((item=li++)) - add_key_fields(key_fields,and_level,item,usable_tables,sargables); + add_key_fields(join, key_fields, and_level, item, usable_tables, + sargables); for (; org_key_fields != *key_fields ; org_key_fields++) org_key_fields->level= *and_level; } else { (*and_level)++; - add_key_fields(key_fields,and_level,li++,usable_tables,sargables); + add_key_fields(join, key_fields, and_level, li++, usable_tables, + sargables); Item *item; while ((item=li++)) { KEY_FIELD *start_key_fields= *key_fields; (*and_level)++; - add_key_fields(key_fields,and_level,item,usable_tables,sargables); + add_key_fields(join, key_fields, and_level, item, usable_tables, + sargables); *key_fields=merge_key_fields(org_key_fields,start_key_fields, *key_fields,++(*and_level)); } } return; } - /* If item is of type 'field op field/constant' add it to key_fields */ + /* + Subquery optimization: check if the encountered condition is one + added by condition push down into subquery. + */ + { + if (cond->type() == Item::FUNC_ITEM && + ((Item_func*)cond)->functype() == Item_func::TRIG_COND_FUNC) + { + cond= ((Item_func*)cond)->arguments()[0]; + if (!join->group_list && !join->order && + join->unit->item && + join->unit->item->substype() == Item_subselect::IN_SUBS && + !join->unit->first_select()->next_select()) + { + KEY_FIELD *save= *key_fields; + add_key_fields(join, key_fields, and_level, cond, usable_tables, + sargables); + // Indicate that this ref access candidate is for subquery lookup: + for (; save != *key_fields; save++) + save->outer_ref= TRUE; + } + return; + } + } + + /* If item is of type 'field op field/constant' add it to key_fields */ if (cond->type() != Item::FUNC_ITEM) return; Item_func *cond_func= (Item_func*) cond; @@ -3083,6 +3143,7 @@ add_key_part(DYNAMIC_ARRAY *keyuse_array,KEY_FIELD *key_field) keyuse.used_tables=key_field->val->used_tables(); keyuse.optimize= key_field->optimize & KEY_OPTIMIZE_REF_OR_NULL; keyuse.null_rejecting= key_field->null_rejecting; + keyuse.outer_ref= key_field->outer_ref; VOID(insert_dynamic(keyuse_array,(gptr) &keyuse)); } } @@ -3205,7 +3266,7 @@ sort_keyuse(KEYUSE *a,KEYUSE *b) Here we can add 'ref' access candidates for t1 and t2, but not for t3. */ -static void add_key_fields_for_nj(TABLE_LIST *nested_join_table, +static void add_key_fields_for_nj(JOIN *join, TABLE_LIST *nested_join_table, KEY_FIELD **end, uint *and_level, SARGABLE_PARAM **sargables) { @@ -3217,12 +3278,13 @@ static void add_key_fields_for_nj(TABLE_LIST *nested_join_table, while ((table= li++)) { if (table->nested_join) - add_key_fields_for_nj(table, end, and_level, sargables); + add_key_fields_for_nj(join, table, end, and_level, sargables); else if (!table->on_expr) tables |= table->table->map; } - add_key_fields(end, and_level, nested_join_table->on_expr, tables, sargables); + add_key_fields(join, end, and_level, nested_join_table->on_expr, tables, + sargables); } @@ -3297,7 +3359,8 @@ update_ref_and_keys(THD *thd, DYNAMIC_ARRAY *keyuse,JOIN_TAB *join_tab, return TRUE; if (cond) { - add_key_fields(&end,&and_level,cond,normal_tables,sargables); + add_key_fields(join_tab->join, &end, &and_level, cond, normal_tables, + sargables); for (; field != end ; field++) { add_key_part(keyuse,field); @@ -3319,8 +3382,9 @@ update_ref_and_keys(THD *thd, DYNAMIC_ARRAY *keyuse,JOIN_TAB *join_tab, into account as well. */ if (*join_tab[i].on_expr_ref) - add_key_fields(&end,&and_level,*join_tab[i].on_expr_ref, - join_tab[i].table->map,sargables); + add_key_fields(join_tab->join, &end, &and_level, + *join_tab[i].on_expr_ref, + join_tab[i].table->map, sargables); } /* Process ON conditions for the nested joins */ @@ -3330,7 +3394,8 @@ update_ref_and_keys(THD *thd, DYNAMIC_ARRAY *keyuse,JOIN_TAB *join_tab, while ((table= li++)) { if (table->nested_join) - add_key_fields_for_nj(table, &end, &and_level, sargables); + add_key_fields_for_nj(join_tab->join, table, &end, &and_level, + sargables); } } @@ -6094,7 +6159,7 @@ void JOIN::cleanup(bool full) if (tables > const_tables) // Test for not-const tables { free_io_cache(table[const_tables]); - filesort_free_buffers(table[const_tables]); + filesort_free_buffers(table[const_tables],full); } if (full) @@ -10794,6 +10859,13 @@ join_init_quick_read_record(JOIN_TAB *tab) } +int rr_sequential(READ_RECORD *info); +int init_read_record_seq(JOIN_TAB *tab) +{ + tab->read_record.read_record= rr_sequential; + return tab->read_record.file->ha_rnd_init(1); +} + static int test_if_quick_select(JOIN_TAB *tab) { @@ -10922,7 +10994,7 @@ join_ft_read_next(READ_RECORD *info) Reading of key with key reference and one part that may be NULL */ -static int +int join_read_always_key_or_null(JOIN_TAB *tab) { int res; @@ -10938,7 +11010,7 @@ join_read_always_key_or_null(JOIN_TAB *tab) } -static int +int join_read_next_same_or_null(READ_RECORD *info) { int error; @@ -13534,9 +13606,16 @@ setup_copy_fields(THD *thd, TMP_TABLE_PARAM *param, if (real_pos->type() == Item::FIELD_ITEM) { Item_field *item; - pos= real_pos; - if (!(item= new Item_field(thd, ((Item_field*) pos)))) + if (!(item= new Item_field(thd, ((Item_field*) real_pos)))) goto err; + if (pos->type() == Item::REF_ITEM) + { + /* preserve the names of the ref when dereferncing */ + Item_ref *ref= (Item_ref *) pos; + item->db_name= ref->db_name; + item->table_name= ref->table_name; + item->name= ref->name; + } pos= item; if (item->field->flags & BLOB_FLAG) { diff --git a/sql/sql_select.h b/sql/sql_select.h index 30b8f834ddf..629b44538d8 100644 --- a/sql/sql_select.h +++ b/sql/sql_select.h @@ -36,6 +36,8 @@ typedef struct keyuse_t { satisfied if val has NULL 'value'. */ bool null_rejecting; + /* TRUE<=> This ref access is an outer subquery reference access */ + bool outer_ref; } KEYUSE; class store_key; @@ -455,10 +457,11 @@ class store_key :public Sql_alloc Field *to_field; // Store data here char *null_ptr; char err; - public: +public: + bool null_key; /* TRUE <=> the value of the key has a null part */ enum store_key_result { STORE_KEY_OK, STORE_KEY_FATAL, STORE_KEY_CONV }; store_key(THD *thd, Field *field_arg, char *ptr, char *null, uint length) - :null_ptr(null),err(0) + :null_ptr(null), err(0), null_key(0) { if (field_arg->type() == FIELD_TYPE_BLOB) { @@ -496,6 +499,7 @@ class store_key_field: public store_key enum store_key_result copy() { copy_field.do_copy(©_field); + null_key= to_field->is_null(); return err != 0 ? STORE_KEY_FATAL : STORE_KEY_OK; } const char *name() const { return field_name; } @@ -516,8 +520,8 @@ public: enum store_key_result copy() { int res= item->save_in_field(to_field, 1); + null_key= to_field->is_null() || item->null_value; return (err != 0 || res > 2 ? STORE_KEY_FATAL : (store_key_result) res); - } const char *name() const { return "func"; } }; @@ -547,6 +551,7 @@ public: err= res; } } + null_key= to_field->is_null() || item->null_value; return (err > 2 ? STORE_KEY_FATAL : (store_key_result) err); } const char *name() const { return "const"; } diff --git a/sql/sql_show.cc b/sql/sql_show.cc index f107a57fe2b..1d524418480 100644 --- a/sql/sql_show.cc +++ b/sql/sql_show.cc @@ -3979,7 +3979,7 @@ bool get_schema_tables_result(JOIN *join) table_list->table->file->extra(HA_EXTRA_RESET_STATE); table_list->table->file->delete_all_rows(); free_io_cache(table_list->table); - filesort_free_buffers(table_list->table); + filesort_free_buffers(table_list->table,1); table_list->table->null_row= 0; } else diff --git a/sql/sql_table.cc b/sql/sql_table.cc index f1de8e9c80b..c14529f6eb1 100644 --- a/sql/sql_table.cc +++ b/sql/sql_table.cc @@ -2267,7 +2267,6 @@ static bool mysql_admin_table(THD* thd, TABLE_LIST* tables, goto send_result; } - table->table->pos_in_table_list= table; if ((table->table->db_stat & HA_READ_ONLY) && open_for_modify) { char buff[FN_REFLEN + MYSQL_ERRMSG_SIZE]; @@ -4056,8 +4055,6 @@ bool mysql_checksum_table(THD *thd, TABLE_LIST *tables, HA_CHECK_OPT *check_opt) } else { - t->pos_in_table_list= table; - if (t->file->table_flags() & HA_HAS_CHECKSUM && !(check_opt->flags & T_EXTEND)) protocol->store((ulonglong)t->file->checksum()); diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy index 6f24a42c07c..676f3f0e6ab 100644 --- a/sql/sql_yacc.yy +++ b/sql/sql_yacc.yy @@ -761,7 +761,7 @@ bool my_yyoverflow(short **a, YYSTYPE **b, ulong *yystacksize); %type <item_list> expr_list udf_expr_list udf_expr_list2 when_list - ident_list ident_list_arg + ident_list ident_list_arg opt_expr_list %type <var_type> option_type opt_var_type opt_var_ident_type @@ -4701,7 +4701,7 @@ simple_expr: { $$= new Item_func_trim($5,$3); } | TRUNCATE_SYM '(' expr ',' expr ')' { $$= new Item_func_round($3,$5,1); } - | ident '.' ident '(' udf_expr_list ')' + | ident '.' ident '(' opt_expr_list ')' { LEX *lex= Lex; sp_name *name= new sp_name($1, $3); @@ -4718,27 +4718,27 @@ simple_expr: { #ifdef HAVE_DLOPEN udf_func *udf= 0; + LEX *lex= Lex; if (using_udf_functions && (udf= find_udf($1.str, $1.length)) && udf->type == UDFTYPE_AGGREGATE) { - LEX *lex= Lex; if (lex->current_select->inc_in_sum_expr()) { yyerror(ER(ER_SYNTAX_ERROR)); YYABORT; } } - $<udf>$= udf; + lex->current_select->udf_list.push_front(udf); #endif } udf_expr_list ')' { #ifdef HAVE_DLOPEN - udf_func *udf= $<udf>3; - SELECT_LEX *sel= Select; + udf_func *udf; + LEX *lex= Lex; - if (udf) + if (NULL != (udf= lex->current_select->udf_list.pop())) { if (udf->type == UDFTYPE_AGGREGATE) Select->in_sum_expr--; @@ -4965,12 +4965,29 @@ udf_expr_list3: udf_expr: remember_name expr remember_end select_alias { + udf_func *udf= Select->udf_list.head(); + /* + Use Item::name as a storage for the attribute value of user + defined function argument. It is safe to use Item::name + because the syntax will not allow having an explicit name here. + See WL#1017 re. udf attributes. + */ if ($4.str) { + if (!udf) + { + /* + Disallow using AS to specify explicit names for the arguments + of stored routine calls + */ + yyerror(ER(ER_SYNTAX_ERROR)); + YYABORT; + } + $2->is_autogenerated_name= FALSE; $2->set_name($4.str, $4.length, system_charset_info); } - else + else if (udf) $2->set_name($1, (uint) ($3 - $1), YYTHD->charset()); $$= $2; } @@ -5131,6 +5148,11 @@ cast_type: | DECIMAL_SYM float_options { $$=ITEM_CAST_DECIMAL; Lex->charset= NULL; } ; +opt_expr_list: + /* empty */ { $$= NULL; } + | expr_list { $$= $1;} + ; + expr_list: { Select->expr_list.push_front(new List<Item>); } expr_list2 diff --git a/sql/table.cc b/sql/table.cc index d72379efb32..5d5d5095e7c 100644 --- a/sql/table.cc +++ b/sql/table.cc @@ -3032,6 +3032,23 @@ void st_table_list::reinit_before_use(THD *thd) embedding->nested_join->join_list.head() == embedded); } +/* + Return subselect that contains the FROM list this table is taken from + + SYNOPSIS + st_table_list::containing_subselect() + + RETURN + Subselect item for the subquery that contains the FROM list + this table is taken from if there is any + 0 - otherwise + +*/ + +Item_subselect *st_table_list::containing_subselect() +{ + return (select_lex ? select_lex->master_unit()->item : 0); +} /***************************************************************************** ** Instansiate templates diff --git a/sql/table.h b/sql/table.h index 5136ac2c4db..f0190353328 100644 --- a/sql/table.h +++ b/sql/table.h @@ -18,6 +18,7 @@ /* Structs that defines the TABLE */ class Item; /* Needed by ORDER */ +class Item_subselect; class GRANT_TABLE; class st_select_lex_unit; class st_select_lex; @@ -68,6 +69,9 @@ enum frm_type_enum typedef struct st_filesort_info { IO_CACHE *io_cache; /* If sorted through filebyte */ + uchar **sort_keys; /* Buffer for sorting keys */ + byte *buffpek; /* Buffer for buffpek structures */ + uint buffpek_len; /* Max number of buffpeks in the buffer */ byte *addon_buf; /* Pointer to a buffer if sorted with fields */ uint addon_length; /* Length of the buffer */ struct st_sort_addon_field *addon_field; /* Pointer to the fields info */ @@ -678,6 +682,7 @@ typedef struct st_table_list procedure. */ void reinit_before_use(THD *thd); + Item_subselect *containing_subselect(); private: bool prep_check_option(THD *thd, uint8 check_opt_type); diff --git a/strings/decimal.c b/strings/decimal.c index 5a0bc0968b6..e0b06685521 100644 --- a/strings/decimal.c +++ b/strings/decimal.c @@ -1036,7 +1036,7 @@ int decimal2ulonglong(decimal_t *from, ulonglong *to) x=x*DIG_BASE + *buf++; if (unlikely(y > ((ulonglong) ULONGLONG_MAX/DIG_BASE) || x < y)) { - *to=y; + *to=ULONGLONG_MAX; return E_DEC_OVERFLOW; } } |