Below is the list of changes that have just been committed into a local
5.0 repository of marty. When marty does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html
ChangeSet
1.1814 05/03/14 15:26:10 mskold@stripped +3 -0
WL#2353 EXPLAIN support for condition pushdown
mysql-test/r/ndb_condition_pushdown.result
1.9 05/03/14 15:23:13 mskold@stripped +398 -8
WL#2353 EXPLAIN support for condition pushdown
sql/sql_select.cc
1.296 05/03/14 15:23:12 mskold@stripped +15 -1
WL#2353 EXPLAIN support for condition pushdown
mysql-test/t/ndb_condition_pushdown.test
1.9 05/03/14 15:23:12 mskold@stripped +371 -0
WL#2353 EXPLAIN support for condition pushdown
# This is a BitKeeper patch. What follows are the unified diffs for the
# set of deltas contained in the patch. The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User: mskold
# Host: blowfish.ndb.mysql.com
# Root: /usr/local/home/marty/MySQL/mysql-5.0
--- 1.295/sql/sql_select.cc Sun Mar 13 21:53:28 2005
+++ 1.296/sql/sql_select.cc Mon Mar 14 15:23:12 2005
@@ -13008,7 +13008,21 @@
extra.append(')');
}
else if (tab->select->cond)
- extra.append("; Using where");
+ {
+ const COND *pushed_cond= tab->table->file->pushed_cond;
+
+ if (thd->variables.engine_condition_pushdown && pushed_cond)
+ {
+ extra.append("; Using where with pushed condition");
+ if (thd->lex->describe & DESCRIBE_EXTENDED)
+ {
+ extra.append(": ");
+ ((COND *)pushed_cond)->print(&extra);
+ }
+ }
+ else
+ extra.append("; Using where");
+ }
}
if (key_read)
{
--- 1.8/mysql-test/r/ndb_condition_pushdown.result Thu Feb 24 14:20:02 2005
+++ 1.9/mysql-test/r/ndb_condition_pushdown.result Mon Mar 14 15:23:13 2005
@@ -278,6 +278,35 @@
3 3 9223372036854775805 3 d 3 3 9223372036854775805 3 d
4 4 9223372036854775806 4 e 4 4 9223372036854775806 4 e
set engine_condition_pushdown = on;
+explain
+select auto from t1 where
+string = "aaaa" and
+vstring = "aaaa" and
+bin = 0xAAAA and
+vbin = 0xAAAA and
+tiny = -1 and
+short = -1 and
+medium = -1 and
+long_int = -1 and
+longlong = -1 and
+real_float > 1.0 and real_float < 2.0 and
+real_double > 1.0 and real_double < 2.0 and
+real_decimal > 1.0 and real_decimal < 2.0 and
+utiny = 1 and
+ushort = 1 and
+umedium = 1 and
+ulong = 1 and
+ulonglong = 1 and
+/* bits = b'001' and */
+options = 'one' and
+flags = 'one' and
+date_field = '1901-01-01' and
+year_field = '1901' and
+time_field = '01:01:01' and
+date_time = '1901-01-01 01:01:01'
+order by auto;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 4 Using where with pushed condition; Using filesort
select auto from t1 where
string = "aaaa" and
vstring = "aaaa" and
@@ -306,6 +335,35 @@
order by auto;
auto
1
+explain
+select auto from t1 where
+string != "aaaa" and
+vstring != "aaaa" and
+bin != 0xAAAA and
+vbin != 0xAAAA and
+tiny != -1 and
+short != -1 and
+medium != -1 and
+long_int != -1 and
+longlong != -1 and
+(real_float < 1.0 or real_float > 2.0) and
+(real_double < 1.0 or real_double > 2.0) and
+(real_decimal < 1.0 or real_decimal > 2.0) and
+utiny != 1 and
+ushort != 1 and
+umedium != 1 and
+ulong != 1 and
+ulonglong != 1 and
+/* bits != b'001' and */
+options != 'one' and
+flags != 'one' and
+date_field != '1901-01-01' and
+year_field != '1901' and
+time_field != '01:01:01' and
+date_time != '1901-01-01 01:01:01'
+order by auto;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 4 Using where with pushed condition; Using filesort
select auto from t1 where
string != "aaaa" and
vstring != "aaaa" and
@@ -336,6 +394,35 @@
2
3
4
+explain
+select auto from t1 where
+string > "aaaa" and
+vstring > "aaaa" and
+bin > 0xAAAA and
+vbin > 0xAAAA and
+tiny < -1 and
+short < -1 and
+medium < -1 and
+long_int < -1 and
+longlong < -1 and
+real_float > 1.1 and
+real_double > 1.1 and
+real_decimal > 1.1 and
+utiny > 1 and
+ushort > 1 and
+umedium > 1 and
+ulong > 1 and
+ulonglong > 1 and
+/* bits > b'001' and */
+(options = 'two' or options = 'three' or options = 'four') and
+(flags = 'one,two' or flags = 'one,two,three' or flags = 'one,two,three,four') and
+date_field > '1901-01-01' and
+year_field > '1901' and
+time_field > '01:01:01' and
+date_time > '1901-01-01 01:01:01'
+order by auto;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 4 Using where with pushed condition; Using filesort
select auto from t1 where
string > "aaaa" and
vstring > "aaaa" and
@@ -366,6 +453,35 @@
2
3
4
+explain
+select auto from t1 where
+string >= "aaaa" and
+vstring >= "aaaa" and
+bin >= 0xAAAA and
+vbin >= 0xAAAA and
+tiny <= -1 and
+short <= -1 and
+medium <= -1 and
+long_int <= -1 and
+longlong <= -1 and
+real_float >= 1.0 and
+real_double >= 1.0 and
+real_decimal >= 1.0 and
+utiny >= 1 and
+ushort >= 1 and
+umedium >= 1 and
+ulong >= 1 and
+ulonglong >= 1 and
+/* bits >= b'001' and */
+(options = 'one' or options = 'two' or options = 'three' or options = 'four') and
+(flags = 'one' or flags = 'one,two' or flags = 'one,two,three' or flags = 'one,two,three,four') and
+date_field >= '1901-01-01' and
+year_field >= '1901' and
+time_field >= '01:01:01' and
+date_time >= '1901-01-01 01:01:01'
+order by auto;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 4 Using where with pushed condition; Using filesort
select auto from t1 where
string >= "aaaa" and
vstring >= "aaaa" and
@@ -397,6 +513,35 @@
2
3
4
+explain
+select auto from t1 where
+string < "dddd" and
+vstring < "dddd" and
+bin < 0xDDDD and
+vbin < 0xDDDD and
+tiny > -4 and
+short > -4 and
+medium > -4 and
+long_int > -4 and
+longlong > -4 and
+real_float < 4.4 and
+real_double < 4.4 and
+real_decimal < 4.4 and
+utiny < 4 and
+ushort < 4 and
+umedium < 4 and
+ulong < 4 and
+ulonglong < 4 and
+/* bits < b'100' and */
+(options = 'one' or options = 'two' or options = 'three') and
+(flags = 'one' or flags = 'one,two' or flags = 'one,two,three') and
+date_field < '1904-01-01' and
+year_field < '1904' and
+time_field < '04:04:04' and
+date_time < '1904-04-04 04:04:04'
+order by auto;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 4 Using where with pushed condition; Using filesort
select auto from t1 where
string < "dddd" and
vstring < "dddd" and
@@ -427,6 +572,35 @@
1
2
3
+explain
+select auto from t1 where
+string <= "dddd" and
+vstring <= "dddd" and
+bin <= 0xDDDD and
+vbin <= 0xDDDD and
+tiny >= -4 and
+short >= -4 and
+medium >= -4 and
+long_int >= -4 and
+longlong >= -4 and
+real_float <= 4.5 and
+real_double <= 4.5 and
+real_decimal <= 4.5 and
+utiny <= 4 - 1 + 1 and /* Checking function composition */
+ushort <= 4 and
+umedium <= 4 and
+ulong <= 4 and
+ulonglong <= 4 and
+/* bits <= b'100' and */
+(options = 'one' or options = 'two' or options = 'three' or options = 'four') and
+(flags = 'one' or flags = 'one,two' or flags = 'one,two,three' or flags = 'one,two,three,four') and
+date_field <= '1904-04-04' and
+year_field <= '1904' and
+time_field <= '04:04:04' and
+date_time <= '1904-04-04 04:04:04'
+order by auto;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 4 Using where with pushed condition; Using filesort
select auto from t1 where
string <= "dddd" and
vstring <= "dddd" and
@@ -459,6 +633,35 @@
3
4
create index medium_index on t1(medium);
+explain
+select auto from t1 where
+string = "aaaa" and
+vstring = "aaaa" and
+bin = 0xAAAA and
+vbin = 0xAAAA and
+tiny = -1 and
+short = -1 and
+medium = -1 and
+long_int = -1 and
+longlong = -1 and
+real_float > 1.0 and real_float < 2.0 and
+real_double > 1.0 and real_double < 2.0 and
+real_decimal > 1.0 and real_decimal < 2.0 and
+utiny = 1 and
+ushort = 1 and
+umedium = 1 and
+ulong = 1 and
+ulonglong = 1 and
+/* bits = b'001' and */
+options = 'one' and
+flags = 'one' and
+date_field = '1901-01-01' and
+year_field = '1901' and
+time_field = '01:01:01' and
+date_time = '1901-01-01 01:01:01'
+order by auto;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ref medium_index medium_index 3 const 10 Using where with pushed condition; Using filesort
select auto from t1 where
string = "aaaa" and
vstring = "aaaa" and
@@ -487,6 +690,35 @@
order by auto;
auto
1
+explain
+select auto from t1 where
+string != "aaaa" and
+vstring != "aaaa" and
+bin != 0xAAAA and
+vbin != 0xAAAA and
+tiny != -1 and
+short != -1 and
+medium != -1 and
+long_int != -1 and
+longlong != -1 and
+(real_float < 1.0 or real_float > 2.0) and
+(real_double < 1.0 or real_double > 2.0) and
+(real_decimal < 1.0 or real_decimal > 2.0) and
+utiny != 1 and
+ushort != 1 and
+umedium != 1 and
+ulong != 1 and
+ulonglong != 1 and
+/* bits != b'001' and */
+options != 'one' and
+flags != 'one' and
+date_field != '1901-01-01' and
+year_field != '1901' and
+time_field != '01:01:01' and
+date_time != '1901-01-01 01:01:01'
+order by auto;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 range medium_index medium_index 3 NULL 20 Using where with pushed condition; Using filesort
select auto from t1 where
string != "aaaa" and
vstring != "aaaa" and
@@ -517,6 +749,35 @@
2
3
4
+explain
+select auto from t1 where
+string > "aaaa" and
+vstring > "aaaa" and
+bin > 0xAAAA and
+vbin > 0xAAAA and
+tiny < -1 and
+short < -1 and
+medium < -1 and
+long_int < -1 and
+longlong < -1 and
+real_float > 1.1 and
+real_double > 1.1 and
+real_decimal > 1.1 and
+utiny > 1 and
+ushort > 1 and
+umedium > 1 and
+ulong > 1 and
+ulonglong > 1 and
+/* bits > b'001' and */
+(options = 'two' or options = 'three' or options = 'four') and
+(flags = 'one,two' or flags = 'one,two,three' or flags = 'one,two,three,four') and
+date_field > '1901-01-01' and
+year_field > '1901' and
+time_field > '01:01:01' and
+date_time > '1901-01-01 01:01:01'
+order by auto;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 range medium_index medium_index 3 NULL 10 Using where with pushed condition; Using filesort
select auto from t1 where
string > "aaaa" and
vstring > "aaaa" and
@@ -547,6 +808,35 @@
2
3
4
+explain
+select auto from t1 where
+string >= "aaaa" and
+vstring >= "aaaa" and
+bin >= 0xAAAA and
+vbin >= 0xAAAA and
+tiny <= -1 and
+short <= -1 and
+medium <= -1 and
+long_int <= -1 and
+longlong <= -1 and
+real_float >= 1.0 and
+real_double >= 1.0 and
+real_decimal >= 1.0 and
+utiny >= 1 and
+ushort >= 1 and
+umedium >= 1 and
+ulong >= 1 and
+ulonglong >= 1 and
+/* bits >= b'001' and */
+(options = 'one' or options = 'two' or options = 'three' or options = 'four') and
+(flags = 'one' or flags = 'one,two' or flags = 'one,two,three' or flags = 'one,two,three,four') and
+date_field >= '1901-01-01' and
+year_field >= '1901' and
+time_field >= '01:01:01' and
+date_time >= '1901-01-01 01:01:01'
+order by auto;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 range medium_index medium_index 3 NULL 10 Using where with pushed condition; Using filesort
select auto from t1 where
string >= "aaaa" and
vstring >= "aaaa" and
@@ -578,6 +868,35 @@
2
3
4
+explain
+select auto from t1 where
+string < "dddd" and
+vstring < "dddd" and
+bin < 0xDDDD and
+vbin < 0xDDDD and
+tiny > -4 and
+short > -4 and
+medium > -4 and
+long_int > -4 and
+longlong > -4 and
+real_float < 4.4 and
+real_double < 4.4 and
+real_decimal < 4.4 and
+utiny < 4 and
+ushort < 4 and
+umedium < 4 and
+ulong < 4 and
+ulonglong < 4 and
+/* bits < b'100' and */
+(options = 'one' or options = 'two' or options = 'three') and
+(flags = 'one' or flags = 'one,two' or flags = 'one,two,three') and
+date_field < '1904-01-01' and
+year_field < '1904' and
+time_field < '04:04:04' and
+date_time < '1904-04-04 04:04:04'
+order by auto;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 range medium_index medium_index 3 NULL 10 Using where with pushed condition; Using filesort
select auto from t1 where
string < "dddd" and
vstring < "dddd" and
@@ -608,6 +927,35 @@
1
2
3
+explain
+select auto from t1 where
+string <= "dddd" and
+vstring <= "dddd" and
+bin <= 0xDDDD and
+vbin <= 0xDDDD and
+tiny >= -4 and
+short >= -4 and
+medium >= -4 and
+long_int >= -4 and
+longlong >= -4 and
+real_float <= 4.5 and
+real_double <= 4.5 and
+real_decimal <= 4.5 and
+utiny <= 4 - 1 + 1 and /* Checking function composition */
+ushort <= 4 and
+umedium <= 4 and
+ulong <= 4 and
+ulonglong <= 4 and
+/* bits <= b'100' and */
+(options = 'one' or options = 'two' or options = 'three' or options = 'four') and
+(flags = 'one' or flags = 'one,two' or flags = 'one,two,three' or flags = 'one,two,three,four') and
+date_field <= '1904-04-04' and
+year_field <= '1904' and
+time_field <= '04:04:04' and
+date_time <= '1904-04-04 04:04:04'
+order by auto;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 range medium_index medium_index 3 NULL 10 Using where with pushed condition; Using filesort
select auto from t1 where
string <= "dddd" and
vstring <= "dddd" and
@@ -639,6 +987,15 @@
2
3
4
+explain
+select auto from t1 where
+string like "b%" and
+vstring like "b%" and
+bin like concat(0xBB, '%') and
+vbin like concat(0xBB, '%')
+order by auto;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 4 Using where with pushed condition; Using filesort
select auto from t1 where
string like "b%" and
vstring like "b%" and
@@ -647,6 +1004,15 @@
order by auto;
auto
2
+explain
+select auto from t1 where
+string not like "b%" and
+vstring not like "b%" and
+bin not like concat(0xBB, '%') and
+vbin not like concat(0xBB, '%')
+order by auto;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 4 Using where with pushed condition; Using filesort
select auto from t1 where
string not like "b%" and
vstring not like "b%" and
@@ -657,41 +1023,65 @@
1
3
4
+explain
+select * from t2 where attr3 is null or attr1 > 2 and pk1= 3 order by pk1;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t2 ALL PRIMARY NULL NULL NULL 6 Using where with pushed condition; Using filesort
select * from t2 where attr3 is null or attr1 > 2 and pk1= 3 order by pk1;
pk1 attr1 attr2 attr3
2 2 NULL NULL
3 3 3 d
+explain
+select * from t2 where attr3 is not null and attr1 > 2 order by pk1;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where with pushed condition; Using filesort
select * from t2 where attr3 is not null and attr1 > 2 order by pk1;
pk1 attr1 attr2 attr3
3 3 3 d
4 4 4 e
5 5 5 f
+explain
+select * from t3 where attr2 > 9223372036854775803 and attr3 != 3 order by pk1;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t3 ALL NULL NULL NULL NULL 6 Using where with pushed condition; Using filesort
select * from t3 where attr2 > 9223372036854775803 and attr3 != 3 order by pk1;
pk1 attr1 attr2 attr3 attr4
2 2 9223372036854775804 2 c
4 4 9223372036854775806 4 e
5 5 9223372036854775807 5 f
+explain
+select * from t2,t3 where t2.attr1 < 1 and t2.attr2 = t3.attr2 and t3.attr1 < 5 order by t2.pk1;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where with pushed condition; Using temporary; Using filesort
+1 SIMPLE t3 ALL NULL NULL NULL NULL 6 Using where with pushed condition
select * from t2,t3 where t2.attr1 < 1 and t2.attr2 = t3.attr2 and t3.attr1 < 5 order by t2.pk1;
pk1 attr1 attr2 attr3 pk1 attr1 attr2 attr3 attr4
0 0 0 a 0 0 0 0 a
+explain
+select * from t4 where attr1 < 5 and attr2 > 9223372036854775803 and attr3 != 3 order by t4.pk1;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t4 range attr1 attr1 4 NULL 10 Using where with pushed condition; Using filesort
select * from t4 where attr1 < 5 and attr2 > 9223372036854775803 and attr3 != 3 order by t4.pk1;
pk1 attr1 attr2 attr3 attr4
2 2 9223372036854775804 2 c
4 4 9223372036854775806 4 e
+explain
+select * from t3,t4 where t4.attr1 > 1 and t4.attr2 = t3.attr2 and t4.attr3 < 5 order by t4.pk1;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t4 range attr1 attr1 4 NULL 10 Using where with pushed condition; Using temporary; Using filesort
+1 SIMPLE t3 ALL NULL NULL NULL NULL 6 Using where
select * from t3,t4 where t4.attr1 > 1 and t4.attr2 = t3.attr2 and t4.attr3 < 5 order by t4.pk1;
pk1 attr1 attr2 attr3 attr4 pk1 attr1 attr2 attr3 attr4
2 2 9223372036854775804 2 c 2 2 9223372036854775804 2 c
3 3 9223372036854775805 3 d 3 3 9223372036854775805 3 d
4 4 9223372036854775806 4 e 4 4 9223372036854775806 4 e
+explain
select auto from t1 where string = "aaaa" collate latin1_general_ci order by auto;
-auto
-1
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 4 Using where; Using filesort
+explain
select * from t2 where (attr1 < 2) = (attr2 < 2) order by pk1;
-pk1 attr1 attr2 attr3
-0 0 0 a
-1 1 1 b
-3 3 3 d
-4 4 4 e
-5 5 5 f
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using filesort
set engine_condition_pushdown = @old_ecpd;
DROP TABLE t1,t2,t3,t4;
--- 1.8/mysql-test/t/ndb_condition_pushdown.test Thu Feb 24 14:20:02 2005
+++ 1.9/mysql-test/t/ndb_condition_pushdown.test Mon Mar 14 15:23:12 2005
@@ -259,6 +259,7 @@
set engine_condition_pushdown = on;
# Test all types and compare operators
+explain
select auto from t1 where
string = "aaaa" and
vstring = "aaaa" and
@@ -287,6 +288,61 @@
order by auto;
select auto from t1 where
+string = "aaaa" and
+vstring = "aaaa" and
+bin = 0xAAAA and
+vbin = 0xAAAA and
+tiny = -1 and
+short = -1 and
+medium = -1 and
+long_int = -1 and
+longlong = -1 and
+real_float > 1.0 and real_float < 2.0 and
+real_double > 1.0 and real_double < 2.0 and
+real_decimal > 1.0 and real_decimal < 2.0 and
+utiny = 1 and
+ushort = 1 and
+umedium = 1 and
+ulong = 1 and
+ulonglong = 1 and
+/* bits = b'001' and */
+options = 'one' and
+flags = 'one' and
+date_field = '1901-01-01' and
+year_field = '1901' and
+time_field = '01:01:01' and
+date_time = '1901-01-01 01:01:01'
+order by auto;
+
+explain
+select auto from t1 where
+string != "aaaa" and
+vstring != "aaaa" and
+bin != 0xAAAA and
+vbin != 0xAAAA and
+tiny != -1 and
+short != -1 and
+medium != -1 and
+long_int != -1 and
+longlong != -1 and
+(real_float < 1.0 or real_float > 2.0) and
+(real_double < 1.0 or real_double > 2.0) and
+(real_decimal < 1.0 or real_decimal > 2.0) and
+utiny != 1 and
+ushort != 1 and
+umedium != 1 and
+ulong != 1 and
+ulonglong != 1 and
+/* bits != b'001' and */
+options != 'one' and
+flags != 'one' and
+date_field != '1901-01-01' and
+year_field != '1901' and
+time_field != '01:01:01' and
+date_time != '1901-01-01 01:01:01'
+order by auto;
+
+select auto from t1 where
string != "aaaa" and
vstring != "aaaa" and
bin != 0xAAAA and
@@ -313,6 +369,34 @@
date_time != '1901-01-01 01:01:01'
order by auto;
+explain
+select auto from t1 where
+string > "aaaa" and
+vstring > "aaaa" and
+bin > 0xAAAA and
+vbin > 0xAAAA and
+tiny < -1 and
+short < -1 and
+medium < -1 and
+long_int < -1 and
+longlong < -1 and
+real_float > 1.1 and
+real_double > 1.1 and
+real_decimal > 1.1 and
+utiny > 1 and
+ushort > 1 and
+umedium > 1 and
+ulong > 1 and
+ulonglong > 1 and
+/* bits > b'001' and */
+(options = 'two' or options = 'three' or options = 'four') and
+(flags = 'one,two' or flags = 'one,two,three' or flags = 'one,two,three,four') and
+date_field > '1901-01-01' and
+year_field > '1901' and
+time_field > '01:01:01' and
+date_time > '1901-01-01 01:01:01'
+order by auto;
+
select auto from t1 where
string > "aaaa" and
vstring > "aaaa" and
@@ -340,6 +424,7 @@
date_time > '1901-01-01 01:01:01'
order by auto;
+explain
select auto from t1 where
string >= "aaaa" and
vstring >= "aaaa" and
@@ -368,6 +453,34 @@
order by auto;
select auto from t1 where
+string >= "aaaa" and
+vstring >= "aaaa" and
+bin >= 0xAAAA and
+vbin >= 0xAAAA and
+tiny <= -1 and
+short <= -1 and
+medium <= -1 and
+long_int <= -1 and
+longlong <= -1 and
+real_float >= 1.0 and
+real_double >= 1.0 and
+real_decimal >= 1.0 and
+utiny >= 1 and
+ushort >= 1 and
+umedium >= 1 and
+ulong >= 1 and
+ulonglong >= 1 and
+/* bits >= b'001' and */
+(options = 'one' or options = 'two' or options = 'three' or options = 'four') and
+(flags = 'one' or flags = 'one,two' or flags = 'one,two,three' or flags = 'one,two,three,four') and
+date_field >= '1901-01-01' and
+year_field >= '1901' and
+time_field >= '01:01:01' and
+date_time >= '1901-01-01 01:01:01'
+order by auto;
+
+explain
+select auto from t1 where
string < "dddd" and
vstring < "dddd" and
bin < 0xDDDD and
@@ -395,6 +508,61 @@
order by auto;
select auto from t1 where
+string < "dddd" and
+vstring < "dddd" and
+bin < 0xDDDD and
+vbin < 0xDDDD and
+tiny > -4 and
+short > -4 and
+medium > -4 and
+long_int > -4 and
+longlong > -4 and
+real_float < 4.4 and
+real_double < 4.4 and
+real_decimal < 4.4 and
+utiny < 4 and
+ushort < 4 and
+umedium < 4 and
+ulong < 4 and
+ulonglong < 4 and
+/* bits < b'100' and */
+(options = 'one' or options = 'two' or options = 'three') and
+(flags = 'one' or flags = 'one,two' or flags = 'one,two,three') and
+date_field < '1904-01-01' and
+year_field < '1904' and
+time_field < '04:04:04' and
+date_time < '1904-04-04 04:04:04'
+order by auto;
+
+explain
+select auto from t1 where
+string <= "dddd" and
+vstring <= "dddd" and
+bin <= 0xDDDD and
+vbin <= 0xDDDD and
+tiny >= -4 and
+short >= -4 and
+medium >= -4 and
+long_int >= -4 and
+longlong >= -4 and
+real_float <= 4.5 and
+real_double <= 4.5 and
+real_decimal <= 4.5 and
+utiny <= 4 - 1 + 1 and /* Checking function composition */
+ushort <= 4 and
+umedium <= 4 and
+ulong <= 4 and
+ulonglong <= 4 and
+/* bits <= b'100' and */
+(options = 'one' or options = 'two' or options = 'three' or options = 'four') and
+(flags = 'one' or flags = 'one,two' or flags = 'one,two,three' or flags = 'one,two,three,four') and
+date_field <= '1904-04-04' and
+year_field <= '1904' and
+time_field <= '04:04:04' and
+date_time <= '1904-04-04 04:04:04'
+order by auto;
+
+select auto from t1 where
string <= "dddd" and
vstring <= "dddd" and
bin <= 0xDDDD and
@@ -425,6 +593,7 @@
create index medium_index on t1(medium);
# Test all types and compare operators
+explain
select auto from t1 where
string = "aaaa" and
vstring = "aaaa" and
@@ -453,6 +622,61 @@
order by auto;
select auto from t1 where
+string = "aaaa" and
+vstring = "aaaa" and
+bin = 0xAAAA and
+vbin = 0xAAAA and
+tiny = -1 and
+short = -1 and
+medium = -1 and
+long_int = -1 and
+longlong = -1 and
+real_float > 1.0 and real_float < 2.0 and
+real_double > 1.0 and real_double < 2.0 and
+real_decimal > 1.0 and real_decimal < 2.0 and
+utiny = 1 and
+ushort = 1 and
+umedium = 1 and
+ulong = 1 and
+ulonglong = 1 and
+/* bits = b'001' and */
+options = 'one' and
+flags = 'one' and
+date_field = '1901-01-01' and
+year_field = '1901' and
+time_field = '01:01:01' and
+date_time = '1901-01-01 01:01:01'
+order by auto;
+
+explain
+select auto from t1 where
+string != "aaaa" and
+vstring != "aaaa" and
+bin != 0xAAAA and
+vbin != 0xAAAA and
+tiny != -1 and
+short != -1 and
+medium != -1 and
+long_int != -1 and
+longlong != -1 and
+(real_float < 1.0 or real_float > 2.0) and
+(real_double < 1.0 or real_double > 2.0) and
+(real_decimal < 1.0 or real_decimal > 2.0) and
+utiny != 1 and
+ushort != 1 and
+umedium != 1 and
+ulong != 1 and
+ulonglong != 1 and
+/* bits != b'001' and */
+options != 'one' and
+flags != 'one' and
+date_field != '1901-01-01' and
+year_field != '1901' and
+time_field != '01:01:01' and
+date_time != '1901-01-01 01:01:01'
+order by auto;
+
+select auto from t1 where
string != "aaaa" and
vstring != "aaaa" and
bin != 0xAAAA and
@@ -479,6 +703,7 @@
date_time != '1901-01-01 01:01:01'
order by auto;
+explain
select auto from t1 where
string > "aaaa" and
vstring > "aaaa" and
@@ -507,6 +732,61 @@
order by auto;
select auto from t1 where
+string > "aaaa" and
+vstring > "aaaa" and
+bin > 0xAAAA and
+vbin > 0xAAAA and
+tiny < -1 and
+short < -1 and
+medium < -1 and
+long_int < -1 and
+longlong < -1 and
+real_float > 1.1 and
+real_double > 1.1 and
+real_decimal > 1.1 and
+utiny > 1 and
+ushort > 1 and
+umedium > 1 and
+ulong > 1 and
+ulonglong > 1 and
+/* bits > b'001' and */
+(options = 'two' or options = 'three' or options = 'four') and
+(flags = 'one,two' or flags = 'one,two,three' or flags = 'one,two,three,four') and
+date_field > '1901-01-01' and
+year_field > '1901' and
+time_field > '01:01:01' and
+date_time > '1901-01-01 01:01:01'
+order by auto;
+
+explain
+select auto from t1 where
+string >= "aaaa" and
+vstring >= "aaaa" and
+bin >= 0xAAAA and
+vbin >= 0xAAAA and
+tiny <= -1 and
+short <= -1 and
+medium <= -1 and
+long_int <= -1 and
+longlong <= -1 and
+real_float >= 1.0 and
+real_double >= 1.0 and
+real_decimal >= 1.0 and
+utiny >= 1 and
+ushort >= 1 and
+umedium >= 1 and
+ulong >= 1 and
+ulonglong >= 1 and
+/* bits >= b'001' and */
+(options = 'one' or options = 'two' or options = 'three' or options = 'four') and
+(flags = 'one' or flags = 'one,two' or flags = 'one,two,three' or flags = 'one,two,three,four') and
+date_field >= '1901-01-01' and
+year_field >= '1901' and
+time_field >= '01:01:01' and
+date_time >= '1901-01-01 01:01:01'
+order by auto;
+
+select auto from t1 where
string >= "aaaa" and
vstring >= "aaaa" and
bin >= 0xAAAA and
@@ -533,6 +813,34 @@
date_time >= '1901-01-01 01:01:01'
order by auto;
+explain
+select auto from t1 where
+string < "dddd" and
+vstring < "dddd" and
+bin < 0xDDDD and
+vbin < 0xDDDD and
+tiny > -4 and
+short > -4 and
+medium > -4 and
+long_int > -4 and
+longlong > -4 and
+real_float < 4.4 and
+real_double < 4.4 and
+real_decimal < 4.4 and
+utiny < 4 and
+ushort < 4 and
+umedium < 4 and
+ulong < 4 and
+ulonglong < 4 and
+/* bits < b'100' and */
+(options = 'one' or options = 'two' or options = 'three') and
+(flags = 'one' or flags = 'one,two' or flags = 'one,two,three') and
+date_field < '1904-01-01' and
+year_field < '1904' and
+time_field < '04:04:04' and
+date_time < '1904-04-04 04:04:04'
+order by auto;
+
select auto from t1 where
string < "dddd" and
vstring < "dddd" and
@@ -560,6 +868,34 @@
date_time < '1904-04-04 04:04:04'
order by auto;
+explain
+select auto from t1 where
+string <= "dddd" and
+vstring <= "dddd" and
+bin <= 0xDDDD and
+vbin <= 0xDDDD and
+tiny >= -4 and
+short >= -4 and
+medium >= -4 and
+long_int >= -4 and
+longlong >= -4 and
+real_float <= 4.5 and
+real_double <= 4.5 and
+real_decimal <= 4.5 and
+utiny <= 4 - 1 + 1 and /* Checking function composition */
+ushort <= 4 and
+umedium <= 4 and
+ulong <= 4 and
+ulonglong <= 4 and
+/* bits <= b'100' and */
+(options = 'one' or options = 'two' or options = 'three' or options = 'four') and
+(flags = 'one' or flags = 'one,two' or flags = 'one,two,three' or flags = 'one,two,three,four') and
+date_field <= '1904-04-04' and
+year_field <= '1904' and
+time_field <= '04:04:04' and
+date_time <= '1904-04-04 04:04:04'
+order by auto;
+
select auto from t1 where
string <= "dddd" and
vstring <= "dddd" and
@@ -588,6 +924,7 @@
order by auto;
# Test LIKE/NOT LIKE
+explain
select auto from t1 where
string like "b%" and
vstring like "b%" and
@@ -596,6 +933,21 @@
order by auto;
select auto from t1 where
+string like "b%" and
+vstring like "b%" and
+bin like concat(0xBB, '%') and
+vbin like concat(0xBB, '%')
+order by auto;
+
+explain
+select auto from t1 where
+string not like "b%" and
+vstring not like "b%" and
+bin not like concat(0xBB, '%') and
+vbin not like concat(0xBB, '%')
+order by auto;
+
+select auto from t1 where
string not like "b%" and
vstring not like "b%" and
bin not like concat(0xBB, '%') and
@@ -603,15 +955,34 @@
order by auto;
# Various tests
+explain
+select * from t2 where attr3 is null or attr1 > 2 and pk1= 3 order by pk1;
select * from t2 where attr3 is null or attr1 > 2 and pk1= 3 order by pk1;
+
+explain
+select * from t2 where attr3 is not null and attr1 > 2 order by pk1;
select * from t2 where attr3 is not null and attr1 > 2 order by pk1;
+
+explain
select * from t3 where attr2 > 9223372036854775803 and attr3 != 3 order by pk1;
+select * from t3 where attr2 > 9223372036854775803 and attr3 != 3 order by pk1;
+
+explain
select * from t2,t3 where t2.attr1 < 1 and t2.attr2 = t3.attr2 and t3.attr1 < 5 order by t2.pk1;
+select * from t2,t3 where t2.attr1 < 1 and t2.attr2 = t3.attr2 and t3.attr1 < 5 order by t2.pk1;
+
+explain
+select * from t4 where attr1 < 5 and attr2 > 9223372036854775803 and attr3 != 3 order by t4.pk1;
select * from t4 where attr1 < 5 and attr2 > 9223372036854775803 and attr3 != 3 order by t4.pk1;
+
+explain
+select * from t3,t4 where t4.attr1 > 1 and t4.attr2 = t3.attr2 and t4.attr3 < 5 order by t4.pk1;
select * from t3,t4 where t4.attr1 > 1 and t4.attr2 = t3.attr2 and t4.attr3 < 5 order by t4.pk1;
# Some tests that are currently not supported and should not push condition
+explain
select auto from t1 where string = "aaaa" collate latin1_general_ci order by auto;
+explain
select * from t2 where (attr1 < 2) = (attr2 < 2) order by pk1;
set engine_condition_pushdown = @old_ecpd;
| Thread |
|---|
| • bk commit into 5.0 tree (mskold:1.1814) | Martin Skold | 14 Mar |
