diff options
| -rw-r--r-- | .bzrignore | 4 | ||||
| -rw-r--r-- | mysql-test/r/bdb.result | 8 | ||||
| -rw-r--r-- | mysql-test/r/func_group.result | 2 | ||||
| -rw-r--r-- | mysql-test/r/func_test.result | 4 | ||||
| -rw-r--r-- | mysql-test/r/heap_btree.result | 4 | ||||
| -rw-r--r-- | mysql-test/r/index_merge.result | 12 | ||||
| -rw-r--r-- | mysql-test/r/innodb.result | 8 | ||||
| -rw-r--r-- | mysql-test/r/join_outer.result | 2 | ||||
| -rw-r--r-- | mysql-test/r/odbc.result | 2 | ||||
| -rw-r--r-- | mysql-test/r/range.result | 32 | ||||
| -rw-r--r-- | mysql-test/r/select.result | 4 | ||||
| -rw-r--r-- | mysql-test/r/subselect.result | 24 | ||||
| -rw-r--r-- | mysql-test/r/user_var.result | 4 | ||||
| -rw-r--r-- | mysql-test/t/range.test | 4 | ||||
| -rw-r--r-- | sql/item.cc | 47 | ||||
| -rw-r--r-- | sql/item.h | 26 | ||||
| -rw-r--r-- | sql/item_cmpfunc.cc | 233 | ||||
| -rw-r--r-- | sql/item_cmpfunc.h | 63 | ||||
| -rw-r--r-- | sql/item_func.cc | 34 | ||||
| -rw-r--r-- | sql/item_func.h | 8 | ||||
| -rw-r--r-- | sql/item_row.cc | 12 | ||||
| -rw-r--r-- | sql/item_row.h | 1 | ||||
| -rw-r--r-- | sql/item_strfunc.h | 8 | ||||
| -rw-r--r-- | sql/opt_range.cc | 226 | ||||
| -rw-r--r-- | sql/opt_sum.cc | 15 | ||||
| -rw-r--r-- | sql/sql_list.h | 35 | ||||
| -rw-r--r-- | sql/sql_select.cc | 805 | ||||
| -rw-r--r-- | sql/sql_select.h | 2 |
28 files changed, 1467 insertions, 162 deletions
diff --git a/.bzrignore b/.bzrignore index 2e4fafa52fd..b6bd0a25740 100644 --- a/.bzrignore +++ b/.bzrignore @@ -483,6 +483,7 @@ mysql-test/gmon.out mysql-test/install_test_db mysql-test/mysql-test-run mysql-test/r/*.reject +mysql-test/r/index_merge_load.result mysql-test/r/rpl000001.eval mysql-test/r/rpl000002.eval mysql-test/r/rpl000014.eval @@ -493,9 +494,11 @@ mysql-test/r/slave-running.eval mysql-test/r/slave-stopped.eval mysql-test/share/mysql mysql-test/std_data/*.pem +mysql-test/t/index_merge.load mysql-test/var/* mysql.kdevprj mysql.proj +mysql_priv.h mysqld.S mysqld.sym mysys/#mf_iocache.c# @@ -647,4 +650,3 @@ vio/test-ssl vio/test-sslclient vio/test-sslserver vio/viotest-ssl -mysql_priv.h diff --git a/mysql-test/r/bdb.result b/mysql-test/r/bdb.result index b02574cf8a3..e0033603e5a 100644 --- a/mysql-test/r/bdb.result +++ b/mysql-test/r/bdb.result @@ -140,13 +140,13 @@ id parent_id level 1015 102 2 explain select level from t1 where level=1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref level level 1 const 1 Using where; Using index +1 SIMPLE t1 ref level level 1 const 1 Using index explain select level,id from t1 where level=1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref level level 1 const 1 Using where; Using index +1 SIMPLE t1 ref level level 1 const 1 Using index explain select level,id,parent_id from t1 where level=1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref level level 1 const 1 Using where +1 SIMPLE t1 ref level level 1 const 1 select level,id from t1 where level=1; level id 1 1002 @@ -625,7 +625,7 @@ id parent_id level 1016 102 2 explain select level from t1 where level=1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref level level 1 const 1 Using where; Using index +1 SIMPLE t1 ref level level 1 const 1 Using index select level,id from t1 where level=1; level id 1 1004 diff --git a/mysql-test/r/func_group.result b/mysql-test/r/func_group.result index 6a704f2847d..13fa1fbe0ae 100644 --- a/mysql-test/r/func_group.result +++ b/mysql-test/r/func_group.result @@ -412,7 +412,7 @@ CHI Los Angeles explain select max(a3) from t1 where a2 is null and a2 = 2; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE select max(a3) from t1 where a2 is null and a2 = 2; max(a3) NULL diff --git a/mysql-test/r/func_test.result b/mysql-test/r/func_test.result index 4bb2e067f9c..6edc4a5485d 100644 --- a/mysql-test/r/func_test.result +++ b/mysql-test/r/func_test.result @@ -74,9 +74,9 @@ select * from t1 where 1 xor 1; a explain extended select * from t1 where 1 xor 1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE Warnings: -Note 1003 select high_priority test.t1.a AS `a` from test.t1 where (1 xor 1) +Note 1003 select high_priority test.t1.a AS `a` from test.t1 select - a from t1; - a -1 diff --git a/mysql-test/r/heap_btree.result b/mysql-test/r/heap_btree.result index ef63b1d4e00..928026a7f6c 100644 --- a/mysql-test/r/heap_btree.result +++ b/mysql-test/r/heap_btree.result @@ -73,7 +73,7 @@ engine=heap; insert into t1 values (1,1),(2,2),(1,3),(2,4),(2,5),(2,6); explain select * from t1 where x=1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref x x 4 const 1 Using where +1 SIMPLE t1 ref x x 4 const 1 select * from t1 where x=1; x y 1 1 @@ -134,7 +134,7 @@ a b 1 1 explain select * from tx where b=x; id select_type table type possible_keys key key_len ref rows Extra -x SIMPLE tx ref b b x const x Using where +x SIMPLE tx ref b b x const x drop table t1; create table t1 (id int unsigned not null, primary key using BTREE (id)) engine=HEAP; insert into t1 values(1); diff --git a/mysql-test/r/index_merge.result b/mysql-test/r/index_merge.result index a847f2d7025..03202427fc8 100644 --- a/mysql-test/r/index_merge.result +++ b/mysql-test/r/index_merge.result @@ -76,13 +76,13 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t0 ALL i1,i2 NULL NULL NULL 1024 Using where explain select * from t0 where key2 = 45 or key1 is null; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t0 range i1,i2 i2 4 NULL 1 Using where +1 SIMPLE t0 ref i2 i2 4 const 1 explain select * from t0 where key2=10 or key3=3 or key4 <=> null; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t0 index_merge i2,i3,i4 i2,i3 4,4 NULL 2 Using where explain select * from t0 where key2=10 or key3=3 or key4 is null; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t0 index_merge i2,i3,i4 i2,i3 4,4 NULL 2 Using where +1 SIMPLE t0 index_merge i2,i3 i2,i3 4,4 NULL 2 Using where explain select key1 from t0 where (key1 <=> null) or (key2 < 5) or (key3=10) or (key4 <=> null); id select_type table type possible_keys key key_len ref rows Extra @@ -257,18 +257,18 @@ explain select * from t0,t1 where (t0.key1=t1.key1) and (t0.key1=3 or t0.key2=4) and t1.key1<200; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t0 index_merge i1,i2 i1,i2 4,4 NULL 2 Using where -1 SIMPLE t1 ref i1 i1 4 test.t0.key1 1 Using where +1 SIMPLE t0 range i1,i2 i1 4 NULL 179 Using where +1 SIMPLE t1 ref i1 i1 4 test.t0.key1 1 explain select * from t0,t1 where (t0.key1=t1.key1) and (t0.key1=3 or t0.key2<4) and t1.key1=2; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t0 ref i1,i2 i1 4 const 1 Using where -1 SIMPLE t1 ref i1 i1 4 const 1 Using where +1 SIMPLE t1 ref i1 i1 4 const 1 explain select * from t0,t1 where t0.key1 = 5 and (t1.key1 = t0.key1 or t1.key8 = t0.key1); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t0 ref i1 i1 4 const 1 Using where +1 SIMPLE t0 ref i1 i1 4 const 1 1 SIMPLE t1 index_merge i1,i8 i1,i8 4,4 NULL 2 Using where explain select * from t0,t1 where t0.key1 < 3 and (t1.key1 = t0.key1 or t1.key8 = t0.key1); diff --git a/mysql-test/r/innodb.result b/mysql-test/r/innodb.result index eb0b9af8e38..081ca7b7d3d 100644 --- a/mysql-test/r/innodb.result +++ b/mysql-test/r/innodb.result @@ -141,13 +141,13 @@ id parent_id level 1015 102 2 explain select level from t1 where level=1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref level level 1 const # Using where; Using index +1 SIMPLE t1 ref level level 1 const # Using index explain select level,id from t1 where level=1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref level level 1 const # Using where; Using index +1 SIMPLE t1 ref level level 1 const # Using index explain select level,id,parent_id from t1 where level=1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref level level 1 const # Using where +1 SIMPLE t1 ref level level 1 const # select level,id from t1 where level=1; level id 1 1002 @@ -609,7 +609,7 @@ id parent_id level 1016 102 2 explain select level from t1 where level=1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref level level 1 const # Using where; Using index +1 SIMPLE t1 ref level level 1 const # Using index select level,id from t1 where level=1; level id 1 1004 diff --git a/mysql-test/r/join_outer.result b/mysql-test/r/join_outer.result index c40f86827f8..9856245725f 100644 --- a/mysql-test/r/join_outer.result +++ b/mysql-test/r/join_outer.result @@ -634,7 +634,7 @@ insert into t2 values (10,1),(20,2),(30,3); explain select * from t2 left join t1 on t1.fooID = t2.fooID and t1.fooID = 30; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 index NULL PRIMARY 4 NULL 3 Using index -1 SIMPLE t1 eq_ref PRIMARY PRIMARY 2 const 1 Using where; Using index +1 SIMPLE t1 eq_ref PRIMARY PRIMARY 2 const 1 Using index select * from t2 left join t1 on t1.fooID = t2.fooID and t1.fooID = 30; fooID barID fooID 10 1 NULL diff --git a/mysql-test/r/odbc.result b/mysql-test/r/odbc.result index c0b2ada0053..2d9d39393b1 100644 --- a/mysql-test/r/odbc.result +++ b/mysql-test/r/odbc.result @@ -12,5 +12,5 @@ select * from t1 where a is null; a b explain select * from t1 where b is null; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE drop table t1; diff --git a/mysql-test/r/range.result b/mysql-test/r/range.result index 5e61b1c58c3..5f1e8071618 100644 --- a/mysql-test/r/range.result +++ b/mysql-test/r/range.result @@ -220,24 +220,22 @@ insert into t1 (x) values (1),(2),(3),(4),(5),(6),(7),(8),(9); update t1 set y=x; explain select * from t1, t1 t2 where t1.y = 2 and t2.x between 7 and t1.y+0; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref y y 5 const 1 Using where -1 SIMPLE t2 range x x 5 NULL 4 Using where +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables explain select * from t1, t1 t2 where t1.y = 2 and t2.x >= 7 and t2.x <= t1.y+0; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref y y 5 const 1 Using where -1 SIMPLE t2 range x x 5 NULL 4 Using where +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables explain select * from t1, t1 t2 where t1.y = 2 and t2.x between t1.y-1 and t1.y+1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref y y 5 const 1 Using where -1 SIMPLE t2 ALL x NULL NULL NULL 9 Range checked for each record (index map: 0x1) +1 SIMPLE t2 range x x 5 NULL 3 Using where explain select * from t1, t1 t2 where t1.y = 2 and t2.x >= t1.y-1 and t2.x <= t1.y+1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref y y 5 const 1 Using where -1 SIMPLE t2 ALL x NULL NULL NULL 9 Range checked for each record (index map: 0x1) +1 SIMPLE t2 range x x 5 NULL 3 Using where explain select * from t1, t1 t2 where t1.y = 2 and t2.x between 0 and t1.y; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref y y 5 const 1 Using where -1 SIMPLE t2 ALL x NULL NULL NULL 9 Using where +1 SIMPLE t2 range x x 5 NULL 2 Using where explain select * from t1, t1 t2 where t1.y = 2 and t2.x >= 0 and t2.x <= t1.y; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref y y 5 const 1 Using where @@ -255,12 +253,12 @@ CREATE TABLE t2 (keya int(11) NOT NULL default '0', KEY j1 (keya)); INSERT INTO t2 VALUES (0),(0),(1),(1),(2),(2); explain select * from t1, t2 where (t1.key1 <t2.keya + 1) and t2.keya=3; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 ref j1 j1 4 const 1 Using where; Using index -1 SIMPLE t1 ALL i1,i2 NULL NULL NULL 4 Range checked for each record (index map: 0x3) +1 SIMPLE t2 ref j1 j1 4 const 1 Using index +1 SIMPLE t1 range i1,i2 i1 4 NULL 4 Using where; Using index explain select * from t1 force index(i2), t2 where (t1.key1 <t2.keya + 1) and t2.keya=3; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 ref j1 j1 4 const 1 Using where; Using index -1 SIMPLE t1 ALL i2 NULL NULL NULL 4 Range checked for each record (index map: 0x2) +1 SIMPLE t2 ref j1 j1 4 const 1 Using index +1 SIMPLE t1 range i2 i2 4 NULL 4 Using where; Using index DROP TABLE t1,t2; CREATE TABLE t1 ( a int(11) default NULL, @@ -379,14 +377,26 @@ count(*) select count(*) from t2; count(*) 1026 +analyze table t1,t2; +Table Op Msg_type Msg_text +test.t1 analyze status OK +test.t2 analyze status Table is already up to date explain select * from t1, t2 where t1.uid=t2.uid AND t1.uid > 0; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range uid_index uid_index 4 NULL 128 Using where 1 SIMPLE t2 ref uid_index uid_index 4 test.t1.uid 38 +explain select * from t1, t2 where t1.uid=t2.uid AND t2.uid > 0; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range uid_index uid_index 4 NULL 128 Using where +1 SIMPLE t2 ref uid_index uid_index 4 test.t1.uid 38 explain select * from t1, t2 where t1.uid=t2.uid AND t1.uid != 0; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range uid_index uid_index 4 NULL 129 Using where 1 SIMPLE t2 ref uid_index uid_index 4 test.t1.uid 38 +explain select * from t1, t2 where t1.uid=t2.uid AND t2.uid != 0; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range uid_index uid_index 4 NULL 129 Using where +1 SIMPLE t2 ref uid_index uid_index 4 test.t1.uid 38 select * from t1, t2 where t1.uid=t2.uid AND t1.uid > 0; id name uid id name uid 1001 A 1 1001 A 1 diff --git a/mysql-test/r/select.result b/mysql-test/r/select.result index 30b391dd947..d8cd0cf1037 100644 --- a/mysql-test/r/select.result +++ b/mysql-test/r/select.result @@ -1374,7 +1374,7 @@ id select_type table type possible_keys key key_len ref rows Extra explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 and t4.companynr > 0; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where -1 SIMPLE t4 eq_ref PRIMARY PRIMARY 1 test.t2.companynr 1 Using where +1 SIMPLE t4 eq_ref PRIMARY PRIMARY 1 test.t2.companynr 1 explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 or t2.companynr is null; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t4 ALL NULL NULL NULL NULL 12 @@ -1470,7 +1470,7 @@ explain extended select count(*),min(fld4),max(fld4),sum(fld1),avg(fld1),std(fld id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where Warnings: -Note 1003 select high_priority count(0) AS `count(*)`,min(test.t2.fld4) AS `min(fld4)`,max(test.t2.fld4) AS `max(fld4)`,sum(test.t2.fld1) AS `sum(fld1)`,avg(test.t2.fld1) AS `avg(fld1)`,std(test.t2.fld1) AS `std(fld1)`,variance(test.t2.fld1) AS `variance(fld1)` from test.t2 where ((test.t2.companynr = 34) and (test.t2.fld4 <> _latin1'')) +Note 1003 select high_priority count(0) AS `count(*)`,min(test.t2.fld4) AS `min(fld4)`,max(test.t2.fld4) AS `max(fld4)`,sum(test.t2.fld1) AS `sum(fld1)`,avg(test.t2.fld1) AS `avg(fld1)`,std(test.t2.fld1) AS `std(fld1)`,variance(test.t2.fld1) AS `variance(fld1)` from test.t2 where ((test.t2.fld4 <> _latin1'') and (test.t2.companynr = 34)) select companynr,count(*),min(fld4),max(fld4),sum(fld1),avg(fld1),std(fld1),variance(fld1) from t2 group by companynr limit 3; companynr count(*) min(fld4) max(fld4) sum(fld1) avg(fld1) std(fld1) variance(fld1) 00 82 Anthony windmills 10355753 126289.6707 115550.9757 13352027981.7087 diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index 286a8833b05..8607fe6fe2d 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -309,7 +309,7 @@ id select_type table type possible_keys key key_len ref rows Extra Warnings: Note 1275 Field or reference 't2.a' of SELECT #2 was resolved in SELECT #1 Note 1275 Field or reference 't2.a' of SELECT #3 was resolved in SELECT #1 -Note 1003 select high_priority (select test.t1.a AS `a` from test.t1 where (test.t1.a = test.t2.a) union select test.t5.a AS `a` from test.t5 where (test.t5.a = test.t2.a)) AS `(select a from t1 where t1.a=t2.a union select a from t5 where t5.a=t2.a)`,test.t2.a AS `a` from test.t2 +Note 1003 select high_priority (select test.t1.a AS `a` from test.t1 where (test.t2.a = test.t1.a) union select test.t5.a AS `a` from test.t5 where (test.t2.a = test.t5.a)) AS `(select a from t1 where t1.a=t2.a union select a from t5 where t5.a=t2.a)`,test.t2.a AS `a` from test.t2 select (select a from t1 where t1.a=t2.a union all select a from t5 where t5.a=t2.a), a from t2; ERROR 21000: Subquery returns more than 1 row create table t6 (patient_uq int, clinic_uq int, index i1 (clinic_uq)); @@ -327,7 +327,7 @@ id select_type table type possible_keys key key_len ref rows Extra 2 DEPENDENT SUBQUERY t7 eq_ref PRIMARY PRIMARY 4 test.t6.clinic_uq 1 Warnings: Note 1275 Field or reference 'clinic_uq' of SELECT #2 was resolved in SELECT #1 -Note 1003 select high_priority test.t6.patient_uq AS `patient_uq`,test.t6.clinic_uq AS `clinic_uq` from test.t6 where exists(select test.t7.uq AS `uq`,test.t7.name AS `name` from test.t7 where (test.t7.uq = test.t6.clinic_uq) limit 1) +Note 1003 select high_priority test.t6.patient_uq AS `patient_uq`,test.t6.clinic_uq AS `clinic_uq` from test.t6 where exists(select test.t7.uq AS `uq`,test.t7.name AS `name` from test.t7 where (test.t6.clinic_uq = test.t7.uq) limit 1) select * from t1 where a= (select a from t2,t4 where t2.b=t4.b); ERROR 23000: Column: 'a' in field list is ambiguous drop table if exists t1,t2,t3; @@ -540,7 +540,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 const PRIMARY,numreponse PRIMARY 7 const,const 1 2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away Warnings: -Note 1003 select high_priority test.t1.numreponse AS `numreponse` from test.t1 where ((test.t1.numeropost = _latin1'1') and (test.t1.numreponse = 3)) +Note 1003 select high_priority test.t1.numreponse AS `numreponse` from test.t1 where ((test.t1.numeropost = _latin1'1') and (test.t1.numreponse = (select max(test.t1.numreponse) AS `MAX(numreponse)` from test.t1 where (test.t1.numeropost = _latin1'1')))) drop table t1; CREATE TABLE t1 (a int(1)); INSERT INTO t1 VALUES (1); @@ -899,7 +899,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 high_priority 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) limit 1)) AS `t1.a in (select t2.a from t2,t3 where t3.a=t2.a)` from test.t1 +Note 1003 select high_priority test.t1.a AS `a`,<in_optimizer>(test.t1.a,<exists>(select 1 AS `Not_used` from test.t2 join test.t3 where (((<cache>(test.t1.a) = test.t2.a) or isnull(test.t2.a)) and (test.t3.a = test.t2.a)) having <is_not_null_test>(test.t2.a) limit 1)) 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); @@ -1168,9 +1168,9 @@ SELECT 0 IN (SELECT 1 FROM t1 a); EXPLAIN EXTENDED SELECT 0 IN (SELECT 1 FROM t1 a); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used -2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE Warnings: -Note 1003 select high_priority <in_optimizer>(0,<exists>(select 1 AS `Not_used` from test.t1 a where isnull(1) having <is_not_null_test>(1) limit 1)) AS `0 IN (SELECT 1 FROM t1 a)` +Note 1003 select high_priority <in_optimizer>(0,<exists>(select 1 AS `Not_used` from test.t1 a having <is_not_null_test>(1) limit 1)) AS `0 IN (SELECT 1 FROM t1 a)` INSERT INTO t1 (pseudo) VALUES ('test1'); SELECT 0 IN (SELECT 1 FROM t1 a); 0 IN (SELECT 1 FROM t1 a) @@ -1178,9 +1178,9 @@ SELECT 0 IN (SELECT 1 FROM t1 a); EXPLAIN EXTENDED SELECT 0 IN (SELECT 1 FROM t1 a); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used -2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE Warnings: -Note 1003 select high_priority <in_optimizer>(0,<exists>(select 1 AS `Not_used` from test.t1 a where isnull(1) having <is_not_null_test>(1) limit 1)) AS `0 IN (SELECT 1 FROM t1 a)` +Note 1003 select high_priority <in_optimizer>(0,<exists>(select 1 AS `Not_used` from test.t1 a having <is_not_null_test>(1) limit 1)) AS `0 IN (SELECT 1 FROM t1 a)` drop table t1; CREATE TABLE `t1` ( `i` int(11) NOT NULL default '0', @@ -1312,7 +1312,7 @@ id select_type table type possible_keys key key_len ref rows Extra 2 DEPENDENT SUBQUERY t1 eq_ref PRIMARY PRIMARY 4 func 1 Using where 2 DEPENDENT SUBQUERY t3 eq_ref PRIMARY PRIMARY 4 test.t1.b 1 Using where; Using index Warnings: -Note 1003 select high_priority test.t2.a AS `a` from test.t2 where <in_optimizer>(test.t2.a,<exists>(select 1 AS `Not_used` from test.t1 join test.t3 where ((test.t1.b = test.t3.a) and (<cache>(test.t2.a) = test.t1.a)) limit 1)) +Note 1003 select high_priority test.t2.a AS `a` from test.t2 where <in_optimizer>(test.t2.a,<exists>(select 1 AS `Not_used` from test.t1 join test.t3 where ((<cache>(test.t2.a) = test.t1.a) and (test.t3.a = test.t1.b)) limit 1)) drop table t1, t2, t3; create table t1 (a int, b int, index a (a,b)); create table t2 (a int, index a (a)); @@ -1348,10 +1348,10 @@ a explain extended select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 index NULL a 5 NULL 4 Using where; Using index -2 DEPENDENT SUBQUERY t3 index a a 5 NULL 3 Using index -2 DEPENDENT SUBQUERY t1 ref a a 10 func,test.t3.a 1000 Using where; Using index +2 DEPENDENT SUBQUERY t1 ref a a 5 func 1001 Using where; Using index +2 DEPENDENT SUBQUERY t3 index a a 5 NULL 3 Using where; Using index Warnings: -Note 1003 select high_priority test.t2.a AS `a` from test.t2 where <in_optimizer>(test.t2.a,<exists>(select 1 AS `Not_used` from test.t1 join test.t3 where ((test.t1.b = test.t3.a) and (<cache>(test.t2.a) = test.t1.a)) limit 1)) +Note 1003 select high_priority test.t2.a AS `a` from test.t2 where <in_optimizer>(test.t2.a,<exists>(select 1 AS `Not_used` from test.t1 join test.t3 where ((<cache>(test.t2.a) = test.t1.a) and (test.t3.a = test.t1.b)) limit 1)) insert into t1 values (3,31); select * from t2 where t2.a in (select a from t1 where t1.b <> 30); a diff --git a/mysql-test/r/user_var.result b/mysql-test/r/user_var.result index fc3dc4eddcd..fcd06a6d8e7 100644 --- a/mysql-test/r/user_var.result +++ b/mysql-test/r/user_var.result @@ -23,7 +23,7 @@ i @vv1:=if(sv1.i,1,0) @vv2:=if(sv2.i,1,0) @vv3:=if(sv3.i,1,0) @vv1+@vv2+@vv3 2 1 0 0 1 explain select * from t1 where i=@vv1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref i i 4 const 1 Using where +1 SIMPLE t1 ref i i 4 const 1 explain select * from t1 where @vv1:=@vv1+1 and i=@vv1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using where @@ -32,7 +32,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index NULL i 4 NULL 3 Using where; Using index explain select * from t1 where i=@vv1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref i i 4 const 1 Using where +1 SIMPLE t1 ref i i 4 const 1 drop table t1,t2; set @a=0,@b=0; select @a:=10, @b:=1, @a > @b, @a < @b; diff --git a/mysql-test/t/range.test b/mysql-test/t/range.test index f01d8d4eacf..18e4ea20990 100644 --- a/mysql-test/t/range.test +++ b/mysql-test/t/range.test @@ -334,8 +334,12 @@ insert into t2(id, uid, name) select id, uid, name from t1; select count(*) from t1; select count(*) from t2; +analyze table t1,t2; + explain select * from t1, t2 where t1.uid=t2.uid AND t1.uid > 0; +explain select * from t1, t2 where t1.uid=t2.uid AND t2.uid > 0; explain select * from t1, t2 where t1.uid=t2.uid AND t1.uid != 0; +explain select * from t1, t2 where t1.uid=t2.uid AND t2.uid != 0; select * from t1, t2 where t1.uid=t2.uid AND t1.uid > 0; select * from t1, t2 where t1.uid=t2.uid AND t1.uid != 0; diff --git a/sql/item.cc b/sql/item.cc index acb2fdbb40a..f9c1843707c 100644 --- a/sql/item.cc +++ b/sql/item.cc @@ -318,7 +318,8 @@ bool DTCollation::aggregate(DTCollation &dt) return 0; } -Item_field::Item_field(Field *f) :Item_ident(NullS,f->table_name,f->field_name) +Item_field::Item_field(Field *f) + :Item_ident(NullS,f->table_name,f->field_name), item_equal(0) { set_field(f); collation.set(DERIVATION_IMPLICIT); @@ -332,6 +333,7 @@ Item_field::Item_field(THD *thd, Item_field &item) result_field(item.result_field) { collation.set(DERIVATION_IMPLICIT); + item_equal= item.item_equal; } void Item_field::set_field(Field *field_par) @@ -992,6 +994,49 @@ bool Item_field::fix_fields(THD *thd, TABLE_LIST *tables, Item **ref) return 0; } +Item_equal *Item_field::find_item_equal(COND_EQUAL *cond_equal) +{ + Item_equal *item= 0; + while (cond_equal) + { + List_iterator_fast<Item_equal> li(cond_equal->current_level); + while ((item= li++)) + { + if (item->contains(field)) + return item; + } + cond_equal= cond_equal->parent_level; + } + return item; +} + +Item *Item_field::equal_fields_propagator(byte *arg) +{ + COND_EQUAL *cond_equal= (COND_EQUAL *) arg; + item_equal= find_item_equal(cond_equal); + Item *item= 0; + if (item_equal) + item= item_equal->get_const(); + if (item) + item->fixed= 0; + else + item= this; + return item; +} + +bool Item_field::replace_equal_field_processor(byte *arg) +{ + if (item_equal) + { + Item_field *subst= item_equal->get_first(); + if (subst && !field->eq(subst->field)) + { + field= subst->field; + return 0; + } + } + return 0; +} void Item::init_make_field(Send_field *tmp_field, enum enum_field_types field_type) diff --git a/sql/item.h b/sql/item.h index 7de6a5bfef8..1783fc6ef48 100644 --- a/sql/item.h +++ b/sql/item.h @@ -83,6 +83,7 @@ public: }; typedef bool (Item::*Item_processor)(byte *arg); +typedef Item* (Item::*Item_calculator) (byte *arg); class Item { Item(const Item &); /* Prevent use of these */ @@ -211,8 +212,15 @@ public: return (this->*processor)(arg); } + virtual Item* traverse(Item_calculator calculator, byte *arg) + { + return (this->*calculator)(arg); + } + virtual bool remove_dependence_processor(byte * arg) { return 0; } virtual bool remove_fixed(byte * arg) { fixed= 0; return 0; } + virtual Item *equal_fields_propagator(byte * arg) { return this; } + virtual bool replace_equal_field_processor(byte * arg) { return 0; } virtual Item *this_item() { return this; } /* For SPs mostly. */ virtual Item *this_const_item() const { return const_cast<Item*>(this); } /* For SPs mostly. */ @@ -337,17 +345,21 @@ public: bool remove_dependence_processor(byte * arg); }; +class Item_equal; +class COND_EQUAL; class Item_field :public Item_ident { void set_field(Field *field); public: Field *field,*result_field; + Item_equal *item_equal; // Item_field() {} Item_field(const char *db_par,const char *table_name_par, const char *field_name_par) - :Item_ident(db_par,table_name_par,field_name_par),field(0),result_field(0) + :Item_ident(db_par,table_name_par,field_name_par),field(0),result_field(0), + item_equal(0) { collation.set(DERIVATION_IMPLICIT); } // Constructor need to process subselect with temporary tables (see Item) Item_field(THD *thd, Item_field &item); @@ -381,6 +393,9 @@ public: bool get_time(TIME *ltime); bool is_null() { return field->is_null(); } Item *get_tmp_table_item(THD *thd); + Item_equal *find_item_equal(COND_EQUAL *cond_equal); + Item *equal_fields_propagator(byte *arg); + bool replace_equal_field_processor(byte *arg); friend class Item_default_value; friend class Item_insert_value; }; @@ -933,6 +948,15 @@ public: return arg->walk(processor, args) || (this->*processor)(args); } + + Item *traverse(Item_calculator calculator, byte *args) + { + Item *new_item= arg->traverse(calculator, args); + if (!new_item) + return 0; + arg= new_item; + return (this->*calculator)(args); + } }; class Item_insert_value : public Item_field diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc index a79ef21e97a..f79e59b5e8f 100644 --- a/sql/item_cmpfunc.cc +++ b/sql/item_cmpfunc.cc @@ -225,7 +225,7 @@ void Item_bool_func2::fix_length_and_dec() } // Make a special case of compare with fields to get nicer DATE comparisons - if (args[0]->type() == FIELD_ITEM) + if (args[0]->type() == FIELD_ITEM && !args[0]->const_item()) { Field *field=((Item_field*) args[0])->field; if (field->store_for_compare()) @@ -238,7 +238,7 @@ void Item_bool_func2::fix_length_and_dec() } } } - if (args[1]->type() == FIELD_ITEM) + if (args[1]->type() == FIELD_ITEM && !args[1]->const_item()) { Field *field=((Item_field*) args[1])->field; if (field->store_for_compare()) @@ -1739,6 +1739,21 @@ bool Item_cond::walk(Item_processor processor, byte *arg) return Item_func::walk(processor, arg); } +Item *Item_cond::traverse(Item_calculator calculator, byte *arg) +{ + List_iterator<Item> li(list); + Item *item; + while ((item= li++)) + { + Item *new_item= item->traverse(calculator, arg); + if (!new_item) + return 0; + if (new_item != item) + li.replace(new_item); + } + return Item_func::traverse(calculator, arg); +} + void Item_cond::split_sum_func(Item **ref_pointer_array, List<Item> &fields) { List_iterator<Item> li(list); @@ -2526,3 +2541,217 @@ Item *Item_cond_or::neg_transformer() /* NOT(a OR b OR ...) -> */ neg_arguments(); return new Item_cond_and(list); } + +Item_equal::Item_equal(Item_field *f1, Item_field *f2) + : Item_bool_func(), const_item(0), eval_item(0), cond_false(0) +{ + const_item_cache= 0; + fields.push_back(f1); + fields.push_back(f2); +} + +Item_equal::Item_equal(Item *c, Item_field *f) + : Item_bool_func(), eval_item(0), cond_false(0) +{ + const_item_cache= 0; + fields.push_back(f); + const_item= c; +} + +Item_equal::Item_equal(Item_equal *item_equal) + : Item_bool_func(), eval_item(0), cond_false(0) +{ + const_item_cache= 0; + List_iterator_fast<Item_field> li(item_equal->fields); + Item_field *item; + while ((item= li++)) + { + fields.push_back(item); + } + const_item= item_equal->const_item; + cond_false= item_equal->cond_false; +} + +void Item_equal::add(Item *c) +{ + if (cond_false) + return; + if (!const_item) + { + const_item= c; + return; + } + Item_func_eq *func= new Item_func_eq(c, const_item); + func->set_cmp_func(); + cond_false = !(func->val_int()); +} + +void Item_equal::add(Item_field *f) +{ + fields.push_back(f); +} + +bool Item_equal::contains(Field *field) +{ + List_iterator_fast<Item_field> it(fields); + Item_field *item; + while ((item= it++)) + { + if (field->eq(item->field)) + return 1; + } + return 0; +} + +void Item_equal::merge(Item_equal *item) +{ + fields.concat(&item->fields); + Item *c= item->const_item; + if (c) + { + /* + The flag cond_false will be set to 1 after this, if + the multiple equality already contains a constant and its + value is not equal to the value of c. + */ + add(const_item); + } + cond_false|= item->cond_false; +} + +void Item_equal::sort(void *table_join_idx) +{ + bool swap; + void **idx= (void **) table_join_idx; + List_iterator<Item_field> it(fields); + do + { + Item_field *item1= it++; + Item_field **ref1= it.ref(); + Item_field *item2; + Item_field **ref2; + + if (!item1) + break; + swap= FALSE; + while ((item2= it++)) + { + ref2= it.ref(); + if (idx[item1->field->table->tablenr] > + idx[item2->field->table->tablenr]) + { + Item_field *item= *ref1; + *ref1= *ref2; + *ref2= item; + swap= TRUE; + } + else + { + item1= item2; + ref1= ref2; + } + } + it.rewind(); + } while (swap); +} + +bool Item_equal::fix_fields(THD *thd, TABLE_LIST *tables, Item **ref) +{ + List_iterator_fast<Item_field> li(fields); + Item *item; + not_null_tables_cache= used_tables_cache= 0; + const_item_cache= 0; + while ((item=li++)) + { + table_map tmp_table_map; + used_tables_cache|= item->used_tables(); + tmp_table_map= item->not_null_tables(); + not_null_tables_cache|= tmp_table_map; + if (item->maybe_null) + maybe_null=1; + } + fix_length_and_dec(); + fixed= 1; + return 0; +} + +void Item_equal::update_used_tables() +{ + List_iterator_fast<Item_field> li(fields); + Item *item; + not_null_tables_cache= used_tables_cache= 0; + while ((item=li++)) + { + item->update_used_tables(); + used_tables_cache|= item->used_tables(); + const_item_cache&= item->const_item(); + } +} + +longlong Item_equal::val_int() +{ + if (cond_false) + return 0; + List_iterator_fast<Item_field> it(fields); + Item *item= const_item ? const_item : it++; + if ((null_value= item->null_value)) + return 0; + eval_item->store_value(item); + while((item= it++)) + { + if ((null_value= item->null_value) || eval_item->cmp(item)) + return 0; + } + return 1; +} + +void Item_equal::fix_length_and_dec() +{ + Item *item= const_item ? const_item : get_first(); + eval_item= cmp_item::get_comparator(item); + if (item->result_type() == STRING_RESULT) + eval_item->cmp_charset= cmp_collation.collation; +} + +bool Item_equal::walk(Item_processor processor, byte *arg) +{ + List_iterator_fast<Item_field> it(fields); + Item *item; + while ((item= it++)) + if (item->walk(processor, arg)) + return 1; + return Item_func::walk(processor, arg); +} + +Item *Item_equal::traverse(Item_calculator calculator, byte *arg) +{ + List_iterator<Item_field> it(fields); + Item *item; + while ((item= it++)) + { + Item *new_item= item->traverse(calculator, arg); + if (!new_item) + return 0; + if (new_item != item) + it.replace((Item_field *) new_item); + } + return Item_func::traverse(calculator, arg); +} + +void Item_equal::print(String *str) +{ + str->append(func_name()); + str->append('('); + List_iterator_fast<Item_field> it(fields); + Item *item; + if ((item= it++)) + item->print(str); + while ((item= it++)) + { + str->append(','); + str->append(' '); + item->print(str); + } + str->append(')'); +} + diff --git a/sql/item_cmpfunc.h b/sql/item_cmpfunc.h index 541bc47557d..9d567bbc01b 100644 --- a/sql/item_cmpfunc.h +++ b/sql/item_cmpfunc.h @@ -902,13 +902,76 @@ public: void top_level_item() { abort_on_null=1; } void copy_andor_arguments(THD *thd, Item_cond *item); bool walk(Item_processor processor, byte *arg); + Item *traverse(Item_calculator calculator, byte *arg); void neg_arguments(); }; +class Item_equal: public Item_bool_func +{ + List<Item_field> fields; /* list of equal field items */ + Item *const_item; /* optional constant item equal to fields items */ + cmp_item *eval_item; + bool cond_false; + DTCollation cmp_collation; +public: + inline Item_equal() + : Item_bool_func(), const_item(0), eval_item(0), cond_false(0) + { const_item_cache=0 ;} + Item_equal(Item_field *f1, Item_field *f2); + Item_equal(Item *c, Item_field *f); + Item_equal(Item_equal *item_equal); + inline Item* get_const() { return const_item; } + void add(Item *c); + void add(Item_field *f); + bool is_false() { return cond_false; } + bool contains(Field *field); + Item_field* get_first() { return fields.head(); } + void merge(Item_equal *item); + enum Functype functype() const { return MULT_EQUAL_FUNC; } + longlong val_int(); + const char *func_name() const { return "multiple equal"; } + optimize_type select_optimize() const { return OPTIMIZE_EQUAL; } + void sort(void *table_join_idx); + friend class Item_equal_iterator; + void fix_length_and_dec(); + bool fix_fields(THD *thd, TABLE_LIST *tables, Item **ref); + void update_used_tables(); + bool walk(Item_processor processor, byte *arg); + Item *traverse(Item_calculator calculator, byte *arg); + void print(String *str); +}; + +class COND_EQUAL +{ +public: + COND_EQUAL *parent_level; + List<Item_equal> current_level; + COND_EQUAL() { parent_level= 0; } +}; + + +class Item_equal_iterator :List_iterator_fast<Item_field> +{ +public: + inline Item_equal_iterator(Item_equal &item_equal) + :List_iterator_fast<Item_field> (item_equal.fields) + {} + inline Item_field* operator++(int) + { + Item_field *item= (*(List_iterator_fast<Item_field> *) this)++; + return item; + } + inline void rewind(void) + { + List_iterator_fast<Item_field>::rewind(); + } +}; + class Item_cond_and :public Item_cond { public: + COND_EQUAL cond_equal; Item_cond_and() :Item_cond() {} Item_cond_and(Item *i1,Item *i2) :Item_cond(i1,i2) {} Item_cond_and(THD *thd, Item_cond_and &item) :Item_cond(thd, item) {} diff --git a/sql/item_func.cc b/sql/item_func.cc index 41da8e35419..10c22c2b386 100644 --- a/sql/item_func.cc +++ b/sql/item_func.cc @@ -242,6 +242,40 @@ bool Item_func::walk (Item_processor processor, byte *argument) return (this->*processor)(argument); } +Item *Item_func::traverse(Item_calculator calculator, byte *argument) +{ + if (arg_count) + { + Item **arg,**arg_end; + for (arg= args, arg_end= args+arg_count; arg != arg_end; arg++) + { + Item *new_item= (*arg)->traverse(calculator, argument); + if (!new_item) + return 0; + *arg= new_item; + } + } + return (this->*calculator)(argument); +} + +Item *Item_func::equal_fields_propagator(byte *argument) +{ + if (arg_count) + { + Item **arg,**arg_end; + for (arg= args, arg_end= args+arg_count; arg != arg_end; arg++) + { + if (!(*arg)->fixed) + { + fix_fields(current_thd, 0, 0); + break; + } + } + } + return this; +} + + void Item_func::split_sum_func(Item **ref_pointer_array, List<Item> &fields) { Item **arg, **arg_end; diff --git a/sql/item_func.h b/sql/item_func.h index c2aa62ec2d7..5add4b8a739 100644 --- a/sql/item_func.h +++ b/sql/item_func.h @@ -40,7 +40,8 @@ public: enum Functype { UNKNOWN_FUNC,EQ_FUNC,EQUAL_FUNC,NE_FUNC,LT_FUNC,LE_FUNC, GE_FUNC,GT_FUNC,FT_FUNC, LIKE_FUNC,NOTLIKE_FUNC,ISNULL_FUNC,ISNOTNULL_FUNC, - COND_AND_FUNC, COND_OR_FUNC, COND_XOR_FUNC, BETWEEN, IN_FUNC, + COND_AND_FUNC, COND_OR_FUNC, COND_XOR_FUNC, + BETWEEN, IN_FUNC, MULT_EQUAL_FUNC, INTERVAL_FUNC, ISNOTNULLTEST_FUNC, SP_EQUALS_FUNC, SP_DISJOINT_FUNC,SP_INTERSECTS_FUNC, SP_TOUCHES_FUNC,SP_CROSSES_FUNC,SP_WITHIN_FUNC, @@ -49,7 +50,8 @@ public: SP_POINTN,SP_GEOMETRYN,SP_INTERIORRINGN, NOT_FUNC, NOT_ALL_FUNC, GUSERVAR_FUNC}; - enum optimize_type { OPTIMIZE_NONE,OPTIMIZE_KEY,OPTIMIZE_OP, OPTIMIZE_NULL }; + enum optimize_type { OPTIMIZE_NONE,OPTIMIZE_KEY,OPTIMIZE_OP, OPTIMIZE_NULL, + OPTIMIZE_EQUAL }; enum Type type() const { return FUNC_ITEM; } virtual enum Functype functype() const { return UNKNOWN_FUNC; } Item_func(void): @@ -146,6 +148,8 @@ public: bool agg_arg_collations_for_comparison(DTCollation &c, Item **items, uint nitems); bool walk(Item_processor processor, byte *arg); + Item *traverse(Item_calculator calculator, byte *arg); + Item *equal_fields_propagator(byte *arg); }; diff --git a/sql/item_row.cc b/sql/item_row.cc index 89b38c8a753..d7afe9ad5f0 100644 --- a/sql/item_row.cc +++ b/sql/item_row.cc @@ -140,6 +140,18 @@ bool Item_row::walk(Item_processor processor, byte *arg) return (this->*processor)(arg); } +Item *Item_row::traverse(Item_calculator calculator, byte *arg) +{ + for (uint i= 0; i < arg_count; i++) + { + Item *new_item= items[i]->traverse(calculator, arg); + if (!new_item) + return 0; + items[i]= new_item; + } + return (this->*calculator)(arg); +} + void Item_row::bring_value() { for (uint i= 0; i < arg_count; i++) diff --git a/sql/item_row.h b/sql/item_row.h index a09bd1a2c31..de6c18bf0d9 100644 --- a/sql/item_row.h +++ b/sql/item_row.h @@ -71,6 +71,7 @@ public: void print(String *str); bool walk(Item_processor processor, byte *arg); + Item *traverse(Item_calculator calculator, byte *arg); uint cols() { return arg_count; } Item* el(uint i) { return items[i]; } diff --git a/sql/item_strfunc.h b/sql/item_strfunc.h index 40b00cdd488..0bc02ca3dd5 100644 --- a/sql/item_strfunc.h +++ b/sql/item_strfunc.h @@ -427,6 +427,14 @@ public: return item->walk(processor, arg) || Item_str_func::walk(processor, arg); } + Item *traverse(Item_calculator calculator, byte *arg) + { + Item *new_item= item->traverse(calculator, arg); + if (!new_item) + return 0; + item= new_item; + return Item_str_func::traverse(calculator, arg); + } void print(String *str); }; diff --git a/sql/opt_range.cc b/sql/opt_range.cc index fa1b80f007e..80d9bf92b45 100644 --- a/sql/opt_range.cc +++ b/sql/opt_range.cc @@ -1493,11 +1493,75 @@ static int get_quick_select_params(SEL_TREE *tree, PARAM *param, return result; } + +static SEL_TREE *get_func_mm_tree(PARAM *param, Item_func *cond_func, + Field *field, Item *value, + Item_result cmp_type) +{ + SEL_TREE *tree= 0; + DBUG_ENTER("get_func_mm_tree"); + + if (cond_func->functype() == Item_func::NE_FUNC) + { + + tree= get_mm_parts(param, field, Item_func::LT_FUNC, + value, cmp_type); + if (tree) + { + tree= tree_or(param, tree, get_mm_parts(param, field, + Item_func::GT_FUNC, + value, cmp_type)); + } + } + else if (cond_func->functype() == Item_func::BETWEEN) + { + + tree= get_mm_parts(param, field, Item_func::GE_FUNC, + cond_func->arguments()[1],cmp_type); + if (tree) + { + tree= tree_and(param, tree, get_mm_parts(param, field, + Item_func::LE_FUNC, + cond_func->arguments()[2], + cmp_type)); + } + } + else if (cond_func->functype() == Item_func::IN_FUNC) + { + Item_func_in *func=(Item_func_in*) cond_func; + tree= get_mm_parts(param, field, Item_func::EQ_FUNC, + func->arguments()[1], cmp_type); + if (tree) + { + for (uint i =2 ; i < func->argument_count() ; i++) + { + tree= tree_or(param, tree, get_mm_parts(param, field, + Item_func::EQ_FUNC, + func->arguments()[i], + cmp_type)); + } + } + } + else + { + Item_func::Functype func_type= + (value != cond_func->arguments()[0]) ? cond_func->functype() : + ((Item_bool_func2*) cond_func)->rev_functype(); + tree= get_mm_parts(param, field, func_type, value, cmp_type); + } + DBUG_RETURN(tree); + +} + + /* make a select tree of all keys in condition */ static SEL_TREE *get_mm_tree(PARAM *param,COND *cond) { SEL_TREE *tree=0; + SEL_TREE *ftree= 0; + Item_field *field_item= 0; + Item *value; DBUG_ENTER("get_mm_tree"); if (cond->type() == Item::COND_ITEM) @@ -1545,9 +1609,12 @@ static SEL_TREE *get_mm_tree(PARAM *param,COND *cond) DBUG_RETURN(new SEL_TREE(SEL_TREE::IMPOSSIBLE)); } - table_map ref_tables=cond->used_tables(); + table_map ref_tables= 0; + table_map param_comp= ~(param->prev_tables | param->read_tables | + param->current_table); if (cond->type() != Item::FUNC_ITEM) { // Should be a field + ref_tables= cond->used_tables(); if ((ref_tables & param->current_table) || (ref_tables & ~(param->prev_tables | param->read_tables))) DBUG_RETURN(0); @@ -1559,76 +1626,98 @@ static SEL_TREE *get_mm_tree(PARAM *param,COND *cond) DBUG_RETURN(0); // Can't be calculated if (cond_func->functype() == Item_func::BETWEEN) - { + { if (cond_func->arguments()[0]->type() == Item::FIELD_ITEM) { - Field *field=((Item_field*) (cond_func->arguments()[0]))->field; - Item_result cmp_type=field->cmp_type(); - DBUG_RETURN(tree_and(param, - get_mm_parts(param, field, - Item_func::GE_FUNC, - cond_func->arguments()[1], cmp_type), - get_mm_parts(param, field, - Item_func::LE_FUNC, - cond_func->arguments()[2], cmp_type))); + field_item= (Item_field*) (cond_func->arguments()[0]); + value= NULL; } - DBUG_RETURN(0); + else + DBUG_RETURN(0); } - if (cond_func->functype() == Item_func::IN_FUNC) - { // COND OR + else if (cond_func->functype() == Item_func::IN_FUNC) + { Item_func_in *func=(Item_func_in*) cond_func; if (func->key_item()->type() == Item::FIELD_ITEM) { - Field *field=((Item_field*) (func->key_item()))->field; - Item_result cmp_type=field->cmp_type(); - tree= get_mm_parts(param,field,Item_func::EQ_FUNC, - func->arguments()[1],cmp_type); - if (!tree) - DBUG_RETURN(tree); // Not key field - for (uint i=2 ; i < func->argument_count(); i++) + field_item= (Item_field*) (func->key_item()); + value= NULL; + } + else + DBUG_RETURN(0); + } + else if (cond_func->functype() == Item_func::MULT_EQUAL_FUNC) + { + Item_equal *item_equal= (Item_equal *) cond; + Item_equal_iterator it(*item_equal); + if (!(value= item_equal->get_const())) + value= it++; + while (value) + { + ref_tables= value->used_tables(); + Item_equal_iterator li(*item_equal); + while ((field_item= li++)) { - SEL_TREE *new_tree=get_mm_parts(param,field,Item_func::EQ_FUNC, - func->arguments()[i],cmp_type); - tree=tree_or(param,tree,new_tree); + if (field_item != value) + { + Field *field= field_item->field; + Item_result cmp_type= field->cmp_type(); + if (!((ref_tables | field->table->map) & param_comp)) + { + tree= get_mm_parts(param, field, Item_func::EQ_FUNC, + value,cmp_type); + ftree= !ftree ? tree : tree_and(param, ftree, tree); + } + } } - DBUG_RETURN(tree); - } - DBUG_RETURN(0); // Can't optimize this IN - } - - if (ref_tables & ~(param->prev_tables | param->read_tables | - param->current_table)) - DBUG_RETURN(0); // Can't be calculated yet - if (!(ref_tables & param->current_table)) - DBUG_RETURN(new SEL_TREE(SEL_TREE::MAYBE)); // This may be false or true - - /* check field op const */ - /* btw, ft_func's arguments()[0] isn't FIELD_ITEM. SerG*/ - if (cond_func->arguments()[0]->type() == Item::FIELD_ITEM) - { - tree= get_mm_parts(param, - ((Item_field*) (cond_func->arguments()[0]))->field, - cond_func->functype(), - cond_func->arg_count > 1 ? cond_func->arguments()[1] : - 0, - ((Item_field*) (cond_func->arguments()[0]))->field-> - cmp_type()); - } - /* check const op field */ - if (!tree && - cond_func->have_rev_func() && - cond_func->arguments()[1]->type() == Item::FIELD_ITEM) - { - DBUG_RETURN(get_mm_parts(param, - ((Item_field*) - (cond_func->arguments()[1]))->field, - ((Item_bool_func2*) cond_func)->rev_functype(), - cond_func->arguments()[0], - ((Item_field*) - (cond_func->arguments()[1]))->field->cmp_type() - )); + if (item_equal->get_const()) + break; + value= it++; + } + DBUG_RETURN(ftree); } - DBUG_RETURN(tree); + else if (cond_func->arguments()[0]->type() == Item::FIELD_ITEM) + { + field_item= (Item_field*) (cond_func->arguments()[0]); + value= cond_func->arg_count > 1 ? cond_func->arguments()[1] : 0; + } + else if (cond_func->have_rev_func() && + cond_func->arguments()[1]->type() == Item::FIELD_ITEM) + { + field_item= (Item_field*) (cond_func->arguments()[1]); + value= cond_func->arguments()[0]; + } + else + DBUG_RETURN(0); + + for (uint i= 0; i < cond_func->arg_count; i++) + { + Item *arg= cond_func->arguments()[i]; + if (arg != field_item) + ref_tables|= arg->used_tables(); + } + Field *field= field_item->field; + Item_result cmp_type= field->cmp_type(); + if (!((ref_tables | field->table->map) & param_comp)) + ftree= get_func_mm_tree(param, cond_func, field, value, cmp_type); + Item_equal *item_equal= field_item->item_equal; + if (item_equal) + { + Item_equal_iterator it(*item_equal); + Item_field *item; + while ((item= it++)) + { + Field *f= item->field; + if (field->eq(f)) + continue; + if (!((ref_tables | f->table->map) & param_comp)) + { + tree= get_func_mm_tree(param, cond_func, f, value, cmp_type); + ftree= !ftree ? tree : tree_and(param, ftree, tree); + } + } + } + DBUG_RETURN(ftree); } @@ -1636,17 +1725,10 @@ static SEL_TREE * get_mm_parts(PARAM *param, Field *field, Item_func::Functype type, Item *value, Item_result cmp_type) { - bool ne_func= FALSE; DBUG_ENTER("get_mm_parts"); if (field->table != param->table) DBUG_RETURN(0); - if (type == Item_func::NE_FUNC) - { - ne_func= TRUE; - type= Item_func::LT_FUNC; - } - KEY_PART *key_part = param->key_parts; KEY_PART *end = param->key_parts_end; SEL_TREE *tree=0; @@ -1683,14 +1765,6 @@ get_mm_parts(PARAM *param, Field *field, Item_func::Functype type, } } - if (ne_func) - { - SEL_TREE *tree2= get_mm_parts(param, field, Item_func::GT_FUNC, - value, cmp_type); - if (tree2) - tree= tree_or(param,tree,tree2); - } - DBUG_RETURN(tree); } diff --git a/sql/opt_sum.cc b/sql/opt_sum.cc index 4fdcd093132..03be9624cd8 100644 --- a/sql/opt_sum.cc +++ b/sql/opt_sum.cc @@ -351,6 +351,18 @@ static bool simple_pred(Item_func *func_item, Item **args, bool *inv_order) Item *item; *inv_order= 0; switch (func_item->argument_count()) { + case 0: + /* MULT_EQUAL_FUNC */ + { + Item_equal *item_equal= (Item_equal *) func_item; + Item_equal_iterator it(*item_equal); + args[0]= it++; + if (it++) + return 0; + if (!(args[1]= item_equal->get_const())) + return 0; + } + break; case 1: /* field IS NULL */ item= func_item->arguments()[0]; @@ -491,6 +503,9 @@ static bool matching_cond(bool max_fl, TABLE_REF *ref, KEY *keyinfo, case Item_func::BETWEEN: between= 1; break; + case Item_func::MULT_EQUAL_FUNC: + eq_type= 1; + break; default: return 0; // Can't optimize function } diff --git a/sql/sql_list.h b/sql/sql_list.h index 592173c36da..bac4a2a8655 100644 --- a/sql/sql_list.h +++ b/sql/sql_list.h @@ -121,10 +121,12 @@ public: void remove(list_node **prev) { list_node *node=(*prev)->next; - delete *prev; - *prev=node; if (!--elements) last= &first; + else if (last == &(*prev)->next) + last= prev; + delete *prev; + *prev=node; } inline void *pop(void) { @@ -137,9 +139,27 @@ public: } inline void concat(base_list *list) { - *last= list->first; - last= list->last; - elements+= list->elements; + if (!list->is_empty()) + { + *last= list->first; + last= list->last; + elements+= list->elements; + } + } + inline void disjoin(base_list *list) + { + list_node **prev= &first; + list_node *node= first; + list_node *list_first= list->first; + elements=0; + while (node && node != list_first) + { + prev= &node->next; + node= node->next; + elements++; + } + *prev= *last; + last= prev; } inline list_node* last_node() { return *last; } inline list_node* first_node() { return first;} @@ -251,6 +271,8 @@ public: inline T* head() {return (T*) base_list::head(); } inline T** head_ref() {return (T**) base_list::head_ref(); } inline T* pop() {return (T*) base_list::pop(); } + inline void concat(List<T> *list) { base_list::concat(list); } + inline void disjoin(List<T> *list) { base_list::disjoin(list); } void delete_elements(void) { list_node *element,*next; @@ -261,7 +283,6 @@ public: } empty(); } - inline void concat(List<T> *list) { base_list::concat(list); } }; @@ -272,6 +293,8 @@ public: inline T* operator++(int) { return (T*) base_list_iterator::next(); } inline T *replace(T *a) { return (T*) base_list_iterator::replace(a); } inline T *replace(List<T> &a) { return (T*) base_list_iterator::replace(a); } + inline void rewind(void) { base_list_iterator::rewind(); } + inline void remove() { base_list_iterator::remove(); } inline void after(T *a) { base_list_iterator::after(a); } inline T** ref(void) { return (T**) base_list_iterator::ref(); } }; diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 9f26d7458d0..4736cca1d41 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -73,6 +73,12 @@ static int return_zero_rows(JOIN *join, select_result *res,TABLE_LIST *tables, uint select_options, const char *info, Item *having, Procedure *proc, SELECT_LEX_UNIT *unit); +static Item *flatten_condition(COND *cond); +static COND *build_all_equal_items(COND *cond, + COND_EQUAL *inherited); +static COND* substitute_for_best_equal_field(COND *cond, + COND_EQUAL *cond_equal, + void *table_join_idx); static COND *optimize_cond(COND *conds,Item::cond_result *cond_value); static COND *remove_eq_conds(COND *cond,Item::cond_result *cond_value); static bool const_expression_in_where(COND *conds,Item *item, Item **comp_item); @@ -524,6 +530,56 @@ JOIN::optimize() } #endif + /* eliminate NOT operators */ + conds= eliminate_not_funcs(conds); + DBUG_EXECUTE("where", print_where(conds, "after negation elimination");); + + /* Eliminate nested AND/OR in conditions */ + if (conds) + conds= flatten_condition(conds); + { + TABLE_LIST *tables; + for (tables= tables_list; tables; tables= tables->next) + { + if (tables->on_expr) + tables->on_expr= flatten_condition(tables->on_expr); + } + } + + /* + Build all multiple equality predicates and eliminate equality + predicates that can be inferred from these multiple equalities. + For each reference of a field included into a multiple equality + that occurs in a function set a pointer to the multiple equality + predicate. Substitute a constant instead of this field if the + multiple equality contains a constant. + */ + if (conds) + { + conds= build_all_equal_items(conds, NULL); + conds->update_used_tables(); + if (conds->type() == Item::COND_ITEM && + ((Item_cond*) conds)->functype() == Item_func::COND_AND_FUNC) + cond_equal= &((Item_cond_and*) conds)->cond_equal; + else if (conds->type() == Item::FUNC_ITEM && + ((Item_cond*) conds)->functype() == Item_func::MULT_EQUAL_FUNC) + { + cond_equal= new COND_EQUAL; + cond_equal->current_level.push_back((Item_equal *) conds); + } + } + { + TABLE_LIST *tables; + for (tables= tables_list; tables; tables= tables->next) + { + if (tables->on_expr) + { + tables->on_expr= build_all_equal_items(tables->on_expr, cond_equal); + tables->on_expr->update_used_tables(); + } + } + } + conds= optimize_cond(conds,&cond_value); if (thd->net.report_error) { @@ -624,6 +680,31 @@ JOIN::optimize() } mysql_unlock_some_tables(thd, table, const_tables); } + /* + Among the equal fields belonging to the same multiple equality + choose the one that is to be retrieved first and substitute + all references to these in where condition for a reference for + the selected field. + */ + if (conds) + { + conds= substitute_for_best_equal_field(conds, cond_equal, map2table); + conds->update_used_tables(); + } + { + TABLE_LIST *tables; + for (tables= tables_list; tables; tables= tables->next) + { + if (tables->on_expr) + { + tables->on_expr= substitute_for_best_equal_field(tables->on_expr, + cond_equal, + map2table); + tables->on_expr->update_used_tables(); + map2table[tables->table->tablenr]->on_expr= tables->on_expr; + } + } + } if (!conds && outer_join) { /* Handle the case where we have an OUTER JOIN without a WHERE */ @@ -2218,6 +2299,35 @@ add_key_field(KEY_FIELD **key_fields,uint and_level, static void +add_key_equal_fields(KEY_FIELD **key_fields, uint and_level, + Item_field *field_item, + bool eq_func, Item **val, + uint num_values, table_map usable_tables) +{ + Field *field= field_item->field; + add_key_field(key_fields, and_level, field, + eq_func, val, num_values, usable_tables); + Item_equal *item_equal= field_item->item_equal; + if (item_equal) + { + /* + Add to the set of possible key values every substitution of + the field for an equal field included into item_equal + */ + Item_equal_iterator it(*item_equal); + Item_field *item; + while ((item= it++)) + { + if (!field->eq(item->field)) + { + add_key_field(key_fields, and_level, item->field, + eq_func, val, num_values, usable_tables); + } + } + } +} + +static void add_key_fields(JOIN_TAB *stat,KEY_FIELD **key_fields,uint *and_level, COND *cond, table_map usable_tables) { @@ -2262,11 +2372,11 @@ add_key_fields(JOIN_TAB *stat,KEY_FIELD **key_fields,uint *and_level, // BETWEEN or IN if (cond_func->key_item()->real_item()->type() == Item::FIELD_ITEM && !(cond_func->used_tables() & OUTER_REF_TABLE_BIT)) - add_key_field(key_fields,*and_level, - ((Item_field*) (cond_func->key_item()->real_item()))-> - field, 0, - cond_func->arguments()+1, cond_func->argument_count()-1, - usable_tables); + add_key_equal_fields(key_fields,*and_level, + (Item_field*) (cond_func->key_item()->real_item()), + 0, cond_func->arguments()+1, + cond_func->argument_count()-1, + usable_tables); break; case Item_func::OPTIMIZE_OP: { @@ -2276,21 +2386,19 @@ add_key_fields(JOIN_TAB *stat,KEY_FIELD **key_fields,uint *and_level, if (cond_func->arguments()[0]->real_item()->type() == Item::FIELD_ITEM && !(cond_func->arguments()[0]->used_tables() & OUTER_REF_TABLE_BIT)) { - add_key_field(key_fields,*and_level, - ((Item_field*) (cond_func->arguments()[0])->real_item()) - ->field, - equal_func, - cond_func->arguments()+1, 1, usable_tables); + add_key_equal_fields(key_fields, *and_level, + (Item_field*) (cond_func->arguments()[0])->real_item(), + equal_func, + cond_func->arguments()+1, 1, usable_tables); } if (cond_func->arguments()[1]->real_item()->type() == Item::FIELD_ITEM && cond_func->functype() != Item_func::LIKE_FUNC && !(cond_func->arguments()[1]->used_tables() & OUTER_REF_TABLE_BIT)) { - add_key_field(key_fields,*and_level, - ((Item_field*) (cond_func->arguments()[1])->real_item()) - ->field, - equal_func, - cond_func->arguments(),1,usable_tables); + add_key_equal_fields(key_fields,*and_level, + (Item_field*) (cond_func->arguments()[1])->real_item(), + equal_func, + cond_func->arguments(),1,usable_tables); } break; } @@ -2302,15 +2410,55 @@ add_key_fields(JOIN_TAB *stat,KEY_FIELD **key_fields,uint *and_level, Item *tmp=new Item_null; if (!tmp) // Should never be true return; - add_key_field(key_fields,*and_level, - ((Item_field*) (cond_func->arguments()[0])->real_item()) - ->field, + add_key_equal_fields(key_fields,*and_level, + (Item_field*) (cond_func->arguments()[0])->real_item(), cond_func->functype() == Item_func::ISNULL_FUNC, &tmp, 1, usable_tables); } break; + case Item_func::OPTIMIZE_EQUAL: + Item_equal *item_equal= (Item_equal *) cond; + Item *const_item= item_equal->get_const(); + Item_equal_iterator it(*item_equal); + Item_field *item; + if (const_item) + { + /* + For each field field1 from item_equal consider the equality + field1=const_item as a condition allowing an index access of the table + with field1 by the keys value of field1. + */ + while ((item= it++)) + { + add_key_field(key_fields, *and_level, item->field, + TRUE, &const_item, 1, usable_tables); + } + } + else + { + /* + Consider all pairs of different fields included into item_equal. + For each of them (field1, field1) consider the equality + field1=field2 as a condition allowing an index access of the table + with field1 by the keys value of field2. + */ + Item_equal_iterator fi(*item_equal); + while ((item= fi++)) + { + Field *field= item->field; + while ((item= it++)) + { + if (!field->eq(item->field)) + { + add_key_field(key_fields, *and_level, field, + TRUE, (Item **) &item, 1, usable_tables); + } + } + it.rewind(); + } + } + break; } - return; } /* @@ -2691,21 +2839,33 @@ find_best(JOIN *join,table_map rest_tables,uint idx,double record_count, { uint keypart=keyuse->keypart; uint found_part_ref_or_null= KEY_OPTIMIZE_REF_OR_NULL; + bool usable= 0; + table_map best_part_found_ref= 0; + double best_prev_record_reads= DBL_MAX; do { if (!(rest_tables & keyuse->used_tables) && !(found_ref_or_null & keyuse->optimize)) { found_part|=keyuse->keypart_map; - found_ref|= keyuse->used_tables; + double tmp= prev_record_reads(join, + (table_map) (found_ref | keyuse->used_tables)); + if (tmp < best_prev_record_reads) + { + best_part_found_ref= keyuse->used_tables; + best_prev_record_reads= tmp; + } if (rec > keyuse->ref_table_rows) rec= keyuse->ref_table_rows; found_part_ref_or_null&= keyuse->optimize; + usable= 1; } keyuse++; - found_ref_or_null|= found_part_ref_or_null; + found_ref|= best_part_found_ref; } while (keyuse->table == table && keyuse->key == key && keyuse->keypart == keypart); + if (usable) + found_ref_or_null|= found_part_ref_or_null; } while (keyuse->table == table && keyuse->key == key); /* @@ -3417,7 +3577,7 @@ make_join_select(JOIN *join,SQL_SELECT *select,COND *cond) } COND *tmp=make_cond_for_table(cond,used_tables,current_map); - if (!tmp && tab->quick) + if (!tmp && tab->quick && tab->type == JT_ALL) { // Outer join /* Hack to handle the case where we only refer to a table @@ -4114,6 +4274,583 @@ template class List<Item_func_match>; template class List_iterator<Item_func_match>; #endif +/* + Eliminate nesting in AND/OR subexpressions od a condition + + SYNOPSIS + flatten_condition() + cond condition where to eliminate nesting + + DESCRIPTION + The function traverse the condition and recursively eliminates + nesting for AND/OR subexpressions: + ... AND (p AND ... r) AND ... => ... AND p AND ... r AND ... + ... OR (p OR ... r) OR ... => ... OR p OR ... r OR ... + + NOTES + Nesting in AND/OR subexpresions inside of NOT/XOR formulas is not + eliminated. + + RETURN + The transformed condition +*/ +static Item *flatten_condition(COND *cond) +{ + if (cond->type() == Item::COND_ITEM) + { + Item_func::Functype functype= ((Item_cond*) cond)->functype(); + if (functype == Item_func::COND_AND_FUNC || + functype == Item_func::COND_OR_FUNC) + { + + List<Item> *args= ((Item_cond*) cond)->argument_list(); + List_iterator<Item> li(*args); + Item *item; + List<Item> list; + while ((item= li++)) + { + item= flatten_condition(item); + if (item->type() == Item::COND_ITEM && + ((Item_func*) item)->functype() == functype) + { + list.concat(((Item_cond*) item)->argument_list()); + li.remove(); + } + } + args->concat(&list); + } + } + return cond; +} + + +/* + Find the multiple equality predicate containing a field + + SYNOPSIS + find_item_equal() + cond_equal multiple equalities to search in + field field to look for + inherited_fl :out set up to TRUE iff multiple equality is found + on upper levels (not on current level of cond_equal) + + DESCRIPTION + The function retrieves the multiple equalities accessed through + the con_equal structure from current level and up looking for + an equality containing field. It stops retrieval as soon as the equality + is found and set up inherited_fl to TRUE if it's found on upper levels. + + RETURN + Item_equal for the found multiple equality predicate if a success; + NULL - otherwise. +*/ + +Item_equal *find_item_equal(COND_EQUAL *cond_equal, Field *field, + bool *inherited_fl) +{ + Item_equal *item= 0; + bool in_upper_level= FALSE; + while (cond_equal) + { + List_iterator_fast<Item_equal> li(cond_equal->current_level); + while ((item= li++)) + { + if (item->contains(field)) + goto finish; + } + in_upper_level= TRUE; + cond_equal= cond_equal->parent_level; + } + in_upper_level= FALSE; +finish: + if (inherited_fl) + *inherited_fl= in_upper_level; + return item; +} + +/* + Check whether an item is a simple equality predicate and if so + create/find a multiple equality for this predicate + + SYNOPSIS + check_equality() + item item to check + cond_equal multiple equalities that must hold together with the predicate + + DESCRIPTION + This function first checks whether an item is a simple equality i.e. + the one that equates a field with another field or a constant. + If this is the case the function looks a for a multiple equality + in the lists referenced directly or indirectly by cond_equal. If it + doesn't find any it builds a multiple equality that covers + the predicate, i.e. the predicate can be inferred from it. The built + multiple equality could be obtained in such a way: create a binary + multiple equality equivalent to the predicate, than merge it, if + possible, with one of old multiple equalities. This guarantees that + the set of multiple equalities covering equality predicates will + be minimal. + + NOTES + Now only fields that have the same type defintions (verified by + the Field::eq_def method) are placed to the same multiple equalities. + Because of this some equality predicates are not eliminated and + can be used in constant propagation procedure. + We could weeken the equlity test as soon as at least one of the + equal fields is to be equal to a constant. It would require a + more complicated implementation: we would have to store, in + general case, its own constant for each fields from the multiple + equality. But at the same time it would allow us to get rid + of constant propagation completely: it would be done by the call + to build_all_equal_items. + + IMPLEMENTATION + The implementation does not follow exactly the above rules to + build a new multiple equality for the equality predicate. + If it processes the equality of the form field1=field2, it + looks for multiple equalities me1 containig field1 and me2 containing + field2. If only one of them is found the fuction expand it by + the lacking field. If multiple equalities for both fields are + found they are merged. If both searches fail a new multiple equality + containing just field1 and field2 is added to the existing + multiple equalities. + If the function processes the predicate of the form field1=const, + it looks for a multiple equality containing field1. If found, the + function checks the constant of the multiple equality. If the value + is unknown, it is setup to const. Otherwise the value is compared with + const and the evaluation of the equality predicate is performed. + When expanding/merging equality predicates from the upper levels + the function first copies them for the current level. It looks + acceptable, as this happens rarely. The implementation without + copying would be much more complicated. + + RETURN + TRUE - if the predicate is a simple equality predicate + FALSE - otherwise +*/ + +static bool check_equality(Item *item, COND_EQUAL *cond_equal) +{ + if (item->type() == Item::FUNC_ITEM && + ((Item_func*) item)->functype() == Item_func::EQ_FUNC) + { + Item *left_item= ((Item_func*) item)->arguments()[0]; + Item *right_item= ((Item_func*) item)->arguments()[1]; + if (left_item->type() == Item::FIELD_ITEM && + right_item->type() == Item::FIELD_ITEM) + { + /* The predicate the form field1=field2 is processed */ + + Field *left_field= ((Item_field*) left_item)->field; + Field *right_field= ((Item_field*) right_item)->field; + + if (!left_field->eq_def(right_field)) + return FALSE; + + if (left_field->eq(right_field)) + return TRUE; + + /* Search for multiple equalities containing field1 and/or field2 */ + bool left_copyfl, right_copyfl; + Item_equal *left_item_equal= + find_item_equal(cond_equal, left_field, &left_copyfl); + Item_equal *right_item_equal= + find_item_equal(cond_equal, right_field, &right_copyfl); + + if (left_item_equal && left_item_equal == right_item_equal) + { + /* + The equality predicate is inference of one of the existing + multiple equalities + */ + return TRUE; + } + + /* Copy the found multiple equalities at the current level if needed */ + if (left_copyfl) + { + left_item_equal= new Item_equal(left_item_equal); + cond_equal->current_level.push_back(left_item_equal); + } + if (right_copyfl) + { + right_item_equal= new Item_equal(right_item_equal); + cond_equal->current_level.push_back(right_item_equal); + } + + if (left_item_equal) + { + if (! right_item_equal) + left_item_equal->add((Item_field *) right_item); + else + { + /* Merge two multiple equalities forming a new one */ + left_item_equal->merge(right_item_equal); + /* Remove the merged multiple equality from the list */ + List_iterator<Item_equal> li(cond_equal->current_level); + while ((li++) != right_item_equal); + li.remove(); + } + } + else + { + if (right_item_equal) + right_item_equal->add((Item_field *) left_item); + else + { + /* Multiple equalities for neither of the fields has been found */ + Item_equal *item= new Item_equal((Item_field *) left_item, + (Item_field *) right_item); + cond_equal->current_level.push_back(item); + } + } + return TRUE; + } + else + { + /* The predicate of the form field=const/const=field is processed */ + Item *const_item= 0; + Item_field *field_item= 0; + if (left_item->type() == Item::FIELD_ITEM && + right_item->const_item()) + { + field_item= (Item_field*) left_item; + const_item= right_item; + } + else if (right_item->type() == Item::FIELD_ITEM && + left_item->const_item()) + { + field_item= (Item_field*) right_item; + const_item= left_item; + } + if (const_item && + field_item->result_type() == const_item->result_type()) + { + bool copyfl; + Item_equal *item_equal = find_item_equal(cond_equal, + field_item->field, ©fl); + if (copyfl) + { + item_equal= new Item_equal(item_equal); + cond_equal->current_level.push_back(item_equal); + } + if (item_equal) + { + /* + The flag cond_false will be set to 1 after this, if item_equal + already contains a constant and its value is not equal to + the value of const_item. + */ + item_equal->add(const_item); + } + else + { + item_equal= new Item_equal(const_item, field_item); + cond_equal->current_level.push_back(item_equal); + } + return TRUE; + } + } + } + return FALSE; +} + +/* + Replace all equality predicates in a condition by multiple equality items + + SYNOPSIS + build_all_equal_items() + cond condition(expression) where to make replacement + inherited path to all inherited multiple equality items + + DESCRIPTION + At each 'and' level the function detects items for equality predicates + and replaced them by a set of multiple equality items of class Item_equal, + taking into account inherited equalities from upper levels. + If an equality predicate is used not in a conjunction it's just + replaced by a multiple equality predicate. + The functuion also traverse the cond tree and and for each field reference + sets a ponter to the multiple equality item containing the field, if there + is any. If this multiple equality equates fields to a constant the + function replace the field reference by the constant. + + NOTES + Multiple equality predicate =(f1,..fn) is equivalent to the conjuction of + f1=f2, .., fn-1=fn. It substitutes any inference from these + equality predicates that is equivalent to the conjunction. + Thus, =(a1,a2,a3) can substitute for ((a1=a3) AND (a2=a3) AND (a2=a1)) as + it is equivalent to ((a1=a2) AND (a2=a3)). + The function always makes a subsitution of all equality predicates occured + in a conjuction for a minimal set of multiple equality predicates. + This set can be considered as a canonical representation of the + sub-conjunction of the equality predicates. + E.g. (t1.a=t2.b AND t2.b>5 AND t1.a=t3.c) is replaced by + (=(t1.a,t2.b,t3.c) AND t2.b>5), not by + (=(t1.a,t2.b) AND =(t1.a,t3.c) AND t2.b>5); + while (t1.a=t2.b AND t2.b>5 AND t3.c,t4.d) is replaced by + (=(t1.a,t2.b) AND =(t3.c=t4.d) AND t2.b>5), + but if additionally =(t4.d,t2.b) is inherited, it + will be replaced by (=(t1.a,t2.b,t3.c,t4.d) AND t2.b>5) + + IMPLEMENTATION + The function performs the substitution in a recursive descent by + the condtion tree, passing to the next AND level a chain of multiple + equality predicates which have been built at the upper levels. + The Item_equal items built at the level are attached to other + non-equality conjucts as a sublist. The pointer to the inherited + multiple equalities is saved in the and condition object (Item_cond_and). + This chain allows us for any field reference occurence easyly to find a + multiple equality that must be held for this occurence. + + RETURN + pointer to the transformed condition +*/ + +static COND *build_all_equal_items(COND *cond, + COND_EQUAL *inherited) +{ + COND_EQUAL cond_equal; + cond_equal.parent_level= inherited; + + if (cond->type() == Item::COND_ITEM) + { + bool and_level= ((Item_cond*) cond)->functype() == + Item_func::COND_AND_FUNC; + List<Item> *args= ((Item_cond*) cond)->argument_list(); + + List_iterator<Item> li(*args); + Item *item; + + if (and_level) + { + /* + Retrieve all conjucts of this level detecting the equality + that are subject to substitution by multiple equality items and + removing each such predicate from the conjunction after after having + found/created a multiple equality whose inference the predicate is. + */ + while ((item= li++)) + { + if (check_equality(item, &cond_equal)) + li.remove(); + } + + List_iterator_fast<Item_equal> it(cond_equal.current_level); + while ((item= it++)) + { + item->fix_fields(current_thd, 0, 0); + } + ((Item_cond_and*)cond)->cond_equal= cond_equal; + inherited= &(((Item_cond_and*)cond)->cond_equal); + } + /* + Make replacement of equality predicates for lower levels + of the condition expression. + */ + List_iterator<Item> it(*args); + while((item= it++)) + { + Item *new_item; + if ((new_item = build_all_equal_items(item, inherited))!= item) + { + /* This replacement happens only for standalone equalities */ + it.replace(new_item); + } + } + if (and_level) + args->concat((List<Item> *)&cond_equal.current_level); + } + else if (cond->type() == Item::FUNC_ITEM) + { + /* Standalone equalities are handled here */ + Item_equal *item_equal; + if (check_equality(cond, &cond_equal) && + (item_equal= cond_equal.current_level.pop())) + { + item_equal->fix_fields(current_thd, 0, 0); + return item_equal; + } + else + { + cond= cond->traverse(&Item::equal_fields_propagator, + (byte *) inherited); + cond->update_used_tables(); + } + } + return cond; +} + + +/* + Generate minimal set of simple equalities equivalent to a multiple equality + + SYNOPSIS + eliminate_item_equal() + cond condition to add the generated equality to + cond_equal structure to access multiple equality of upper levels + item_equal multiple equality to generate simple equality from + + DESCRIPTION + The function retrieves the fields of the multiple equality item + item_equal and for each field f: + - if item_equal contains const it generates the equality f=const_item; + - otherwise, if f is not the first field, generates the equality + f=item_equal->get_first(). + All generated equality are added to the cond conjunction. + + NOTES + Before generating an equality function checks that it has not + been generated for multiple equalies of the upper levels. + If cond is equal to 0, then not more then one equality is generated + and a pointer to it is returned as the result of the function. + + RETURN + The condition with generated simple equalities or + a pointer to the simple generated equality. +*/ + +static Item *eliminate_item_equal(COND *cond, COND_EQUAL *cond_equal, + Item_equal *item_equal) +{ + Item *item_const= item_equal->get_const(); + Item_equal_iterator it(*item_equal); + Item *head; + if (item_const) + head= item_const; + else + { + head= item_equal->get_first(); + it++; + } + Item_field *item_field; + Item *new_item= 0; + while ((item_field= it++)) + { + Item_equal *upper= item_field->find_item_equal(cond_equal); + Item_field *item= item_field; + if (upper) + { + if (item_const && upper->get_const()) + item= 0; + else + { + Item_equal_iterator li(*item_equal); + while ((item= li++) != item_field) + { + if (item->find_item_equal(cond_equal) == upper) + break; + } + } + } + if (item == item_field) + { + if (!cond && new_item) + { + cond= new Item_cond_and(); + ((Item_cond *) cond)->add(new_item); + } + item_field->item_equal= item_equal; + new_item= new Item_func_eq(item_field, head); + ((Item_func_eq *) new_item)->fix_length_and_dec(); + if (cond) + ((Item_cond *) cond)->add(new_item); + } + } + if (!cond) + cond= (COND *) new_item; + return cond; +} + + +/* + Substitute every field reference in a condition by the best equal field + and eliminate all multiplle equality predicates + + SYNOPSIS + substitute_for_best_equal_field() + cond condition to process + cond_equal multiple equalities to take into consideration + table_join_idx index to tables determining field preference + + DESCRIPTION + The function retrieves the cond condition and for each encountered + multiple equality predicate it sorts the field references in it + according to the order of tables specified by the table_join_idx + parameter. Then it eliminates the multiple equality predicate it + replacing it by the conjunction of simple equality predicates + equating every field from the multiple equality to the first + field in it, or to the constant, if there is any. + After this the function retrieves all other conjuncted + predicates substitute every field reference by the field reference + to the first equal field or equal constant if there are any. + + NOTES + At the first glance full sort of fields in multiple equality + seems to be an overkill. Yet it's not the case due to possible + new fields in multiple equality item of lower levels. We want + the order in them to comply with the order of upper levels. + + RETURN + The transformed condition +*/ + +static COND* substitute_for_best_equal_field(COND *cond, + COND_EQUAL *cond_equal, + void *table_join_idx) +{ + Item_equal *item_equal; + + if (cond->type() == Item::COND_ITEM) + { + List<Item> *cond_list= ((Item_cond*) cond)->argument_list(); + + bool and_level= ((Item_cond*) cond)->functype() == + Item_func::COND_AND_FUNC; + if (and_level) + { + cond_equal= &((Item_cond_and *) cond)->cond_equal; + cond_list->disjoin((List<Item> *) &cond_equal->current_level); + + List_iterator_fast<Item_equal> it(cond_equal->current_level); + while((item_equal= it++)) + { + item_equal->sort(table_join_idx); + } + } + + List_iterator<Item> li(*cond_list); + Item *item; + while ((item= li++)) + { + Item *new_item =substitute_for_best_equal_field(item, cond_equal, + table_join_idx); + if (new_item != item) + li.replace(new_item); + } + + if (and_level) + { + List_iterator_fast<Item_equal> it(cond_equal->current_level); + while((item_equal= it++)) + { + eliminate_item_equal(cond, cond_equal->parent_level, item_equal); + } + } + } + else if (cond->type() == Item::FUNC_ITEM && + ((Item_cond*) cond)->functype() == Item_func::MULT_EQUAL_FUNC) + { + item_equal= (Item_equal *) cond; + item_equal->sort(table_join_idx); + if (cond_equal && cond_equal->current_level.head() == item_equal) + cond_equal= 0; + return eliminate_item_equal(0, cond_equal, item_equal); + } + else + cond->walk(&Item::replace_equal_field_processor, 0); + return cond; +} + + /* change field = field to field = const for each found field = const in the and_level @@ -4344,9 +5081,6 @@ optimize_cond(COND *conds,Item::cond_result *cond_value) DBUG_RETURN(conds); } DBUG_EXECUTE("where",print_where(conds,"original");); - /* eliminate NOT operators */ - conds= eliminate_not_funcs(conds); - DBUG_EXECUTE("where", print_where(conds, "after negation elimination");); /* change field = field to field = const for each found field = const */ propagate_cond_constants((I_List<COND_CMP> *) 0,conds,conds); /* @@ -4488,6 +5222,27 @@ remove_eq_conds(COND *cond,Item::cond_result *cond_value) } } } + if (cond->const_item()) + { + *cond_value= eval_const_cond(cond) ? Item::COND_TRUE : Item::COND_FALSE; + return (COND*) 0; + } + } + else if (cond->type() == Item::FUNC_ITEM && + ((Item_func*) cond)->functype() == Item_func::MULT_EQUAL_FUNC) + { + /* + The is_false method for an multiple equality item returns 1 + when the conjunction with this item originally contained an + equality that was inconsistent with the multiple equality + predicate or has been inherited from other multiple equality + for which is_false returns 1. + */ + if (((Item_equal *) cond)->is_false()) + { + *cond_value= Item::COND_FALSE; + return (COND*) 0; + } } else if (cond->const_item()) { diff --git a/sql/sql_select.h b/sql/sql_select.h index 9bba4e9318e..086e181d523 100644 --- a/sql/sql_select.h +++ b/sql/sql_select.h @@ -196,6 +196,7 @@ class JOIN :public Sql_alloc ORDER *order, *group_list, *proc_param; //hold parameters of mysql_select COND *conds; // ---"--- Item *conds_history; // store WHERE for explain + COND_EQUAL *cond_equal; TABLE_LIST *tables_list; //hold 'tables' parameter of mysql_selec SQL_SELECT *select; //created in optimisation phase Item **ref_pointer_array; //used pointer reference for this select @@ -255,6 +256,7 @@ class JOIN :public Sql_alloc ref_pointer_array_size= 0; zero_result_cause= 0; optimized= 0; + cond_equal= 0; fields_list= fields_arg; bzero((char*) &keyuse,sizeof(keyuse)); |