1 files changed, 1756 insertions, 0 deletions
diff --git a/mysql-test/suite/ndb/t/ndb_condition_pushdown.test b/mysql-test/suite/ndb/t/ndb_condition_pushdown.test
new file mode 100644
index 00000000000..ab56430ac1d
--- /dev/null
+++ b/mysql-test/suite/ndb/t/ndb_condition_pushdown.test
@@ -0,0 +1,1756 @@
+-- source include/have_ndb.inc
+-- source include/not_embedded.inc
+
+--disable_warnings
+DROP TABLE IF EXISTS t1,t2,t3,t4;
+--enable_warnings
+
+#
+# Test of condition pushdown to storage engine
+#
+CREATE TABLE t1 (
+  auto int(5) unsigned NOT NULL auto_increment,
+  string char(10),
+  vstring varchar(10),
+  bin binary(2),
+  vbin varbinary(7),	
+  tiny tinyint(4) DEFAULT '0' NOT NULL ,
+  short smallint(6) DEFAULT '1' NOT NULL ,
+  medium mediumint(8) DEFAULT '0' NOT NULL,
+  long_int int(11) DEFAULT '0' NOT NULL,
+  longlong bigint(13) DEFAULT '0' NOT NULL,
+  real_float float(13,1) DEFAULT 0.0 NOT NULL,
+  real_double double(16,4),
+  real_decimal decimal(16,4),
+  utiny tinyint(3) unsigned DEFAULT '0' NOT NULL,
+  ushort smallint(5) unsigned zerofill DEFAULT '00000' NOT NULL,
+  umedium mediumint(8) unsigned DEFAULT '0' NOT NULL,
+  ulong int(11) unsigned DEFAULT '0' NOT NULL,
+  ulonglong bigint(13) unsigned DEFAULT '0' NOT NULL,
+  bits bit(3),
+  options enum('zero','one','two','three','four') not null,
+  flags set('zero','one','two','three','four') not null,
+  date_field date,
+  year_field year,
+  time_field time,      
+  date_time datetime,
+  time_stamp timestamp,
+  PRIMARY KEY (auto)
+) engine=ndb;
+
+insert into t1 values 
+(NULL,"aaaa","aaaa",0xAAAA,0xAAAA,-1,-1,-1,-1,-1,1.1,1.1,1.1,1,1,1,1,1, 
+ b'001','one','one',
+ '1901-01-01','1901', 
+'01:01:01','1901-01-01 01:01:01',NULL),
+(NULL,"bbbb","bbbb",0xBBBB,0xBBBB,-2,-2,-2,-2,-2,2.2,2.2,2.2,2,2,2,2,2,
+ b'010','two','one,two',
+ '1902-02-02','1902', 
+'02:02:02','1902-02-02 02:02:02',NULL),
+(NULL,"cccc","cccc",0xCCCC,0xCCCC,-3,-3,-3,-3,-3,3.3,3.3,3.3,3,3,3,3,3,
+ b'011','three','one,two,three',
+ '1903-03-03','1903', 
+'03:03:03','1903-03-03 03:03:03',NULL),
+(NULL,"dddd","dddd",0xDDDD,0xDDDD,-4,-4,-4,-4,-4,4.4,4.4,4.4,4,4,4,4,4,
+ b'100','four','one,two,three,four',
+ '1904-04-04','1904', 
+'04:04:04','1904-04-04 04:04:04',NULL);
+
+CREATE TABLE t2 (pk1 int unsigned NOT NULL PRIMARY KEY,   attr1 int unsigned NOT NULL,   attr2 int unsigned,   attr3 VARCHAR(10) ) ENGINE=ndbcluster;
+
+insert into t2 values (0,0,0, "a"),(1,1,1,"b"),(2,2,NULL,NULL),(3,3,3,"d"),(4,4,4,"e"),(5,5,5,"f");
+
+CREATE TABLE  t3 (pk1 int unsigned NOT NULL PRIMARY KEY,   attr1 int unsigned NOT NULL,   attr2 bigint unsigned, attr3 tinyint unsigned,  attr4 VARCHAR(10) ) ENGINE=ndbcluster;
+
+insert into t3 values (0,0,0,0,"a"),(1,1,9223372036854775803,1,"b"),(2,2,9223372036854775804,2,"c"),(3,3,9223372036854775805,3,"d"),(4,4,9223372036854775806,4,"e"),(5,5,9223372036854775807,5,"f");
+
+CREATE TABLE  t4 (pk1 int unsigned NOT NULL PRIMARY KEY,   attr1 int unsigned NOT NULL,   attr2 bigint unsigned, attr3 tinyint unsigned,  attr4 VARCHAR(10) , KEY (attr1)) ENGINE=ndbcluster;
+
+insert into t4 values (0,0,0,0,"a"),(1,1,9223372036854775803,1,"b"),(2,2,9223372036854775804,2,"c"),(3,3,9223372036854775805,3,"d"),(4,4,9223372036854775806,4,"e"),(5,5,9223372036854775807,5,"f");
+
+set @old_ecpd = @@session.engine_condition_pushdown;
+set engine_condition_pushdown = off;
+
+# Test all types and compare operators
+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;
+
+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 
+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 
+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 < "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 
+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 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' 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;
+
+# Test LIKE/NOT LIKE
+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;
+
+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;
+
+# BETWEEN
+select auto from t1 where
+(string between "aaaa" and "cccc") and 
+(vstring between "aaaa" and "cccc") and 
+(bin between 0xAAAA and 0xCCCC) and 
+(vbin between 0xAAAA and 0xCCCC) and 
+(tiny between -3 and -1) and 
+(short between -3 and -1) and 
+(medium between -3 and -1) and 
+(long_int between -3 and -1) and 
+(longlong between -3 and -1) and 
+(utiny between 1 and 3) and 
+(ushort between 1 and 3) and 
+(umedium between 1 and 3) and 
+(ulong between 1 and 3) and 
+(ulonglong between 1 and 3) and 
+(bits between b'001' and b'011') and
+(options between 'one' and 'three') and 
+(flags between 'one' and 'one,two,three') and 
+(date_field between '1901-01-01' and '1903-03-03') and
+(year_field between '1901' and '1903') and
+(time_field between '01:01:01' and '03:03:03') and 
+(date_time between '1901-01-01 01:01:01' and '1903-03-03 03:03:03') 
+order by auto;
+
+select auto from t1 where
+("aaaa" between string and string) and 
+("aaaa" between vstring and vstring) and 
+(0xAAAA between bin and bin) and 
+(0xAAAA between vbin and vbin) and 
+(-1 between tiny and tiny) and 
+(-1 between short and short) and 
+(-1 between medium and medium) and 
+(-1 between long_int and long_int) and 
+(-1 between longlong and longlong) and 
+(1 between utiny and utiny) and 
+(1 between ushort and ushort) and 
+(1 between umedium and umedium) and 
+(1 between ulong and ulong) and 
+(1 between ulonglong and ulonglong) and 
+(b'001' between bits and bits) and
+('one' between options and options) and 
+('one' between flags and flags) and 
+('1901-01-01' between date_field and date_field) and
+('1901' between year_field and year_field) and
+('01:01:01' between time_field and time_field) and 
+('1901-01-01 01:01:01' between date_time and date_time) 
+order by auto;
+
+# NOT BETWEEN
+select auto from t1 where
+(string not between "aaaa" and "cccc") and 
+(vstring not between "aaaa" and "cccc") and 
+(bin not between 0xAAAA and 0xCCCC) and 
+(vbin not between 0xAAAA and 0xCCCC) and 
+(tiny not between -3 and -1) and 
+(short not between -3 and -1) and 
+(medium not between -3 and -1) and 
+(long_int not between -3 and -1) and 
+(longlong not between -3 and -1) and 
+(utiny not between 1 and 3) and 
+(ushort not between 1 and 3) and 
+(umedium not between 1 and 3) and 
+(ulong not between 1 and 3) and 
+(ulonglong not between 1 and 3) and 
+(bits not between b'001' and b'011') and
+(options not between 'one' and 'three') and 
+(flags not between 'one' and 'one,two,three') and 
+(date_field not between '1901-01-01' and '1903-03-03') and
+(year_field not between '1901' and '1903') and
+(time_field not between '01:01:01' and '03:03:03') and 
+(date_time not between '1901-01-01 01:01:01' and '1903-03-03 03:03:03') 
+order by auto;
+
+select auto from t1 where
+("aaaa" not between string and string) and 
+("aaaa" not between vstring and vstring) and 
+(0xAAAA not between bin and bin) and 
+(0xAAAA not between vbin and vbin) and 
+(-1 not between tiny and tiny) and 
+(-1 not between short and short) and 
+(-1 not between medium and medium) and 
+(-1 not between long_int and long_int) and 
+(-1 not between longlong and longlong) and 
+(1 not between utiny and utiny) and 
+(1 not between ushort and ushort) and 
+(1 not between umedium and umedium) and 
+(1 not between ulong and ulong) and 
+(1 not between ulonglong and ulonglong) and 
+(b'001' not between bits and bits) and
+('one' not between options and options) and 
+('one' not between flags and flags) and 
+('1901-01-01' not between date_field and date_field) and
+('1901' not between year_field and year_field) and
+('01:01:01' not between time_field and time_field) and 
+('1901-01-01 01:01:01' not between date_time and date_time) 
+order by auto;
+
+# IN
+select auto from t1 where
+string in("aaaa","cccc") and 
+vstring in("aaaa","cccc") and 
+bin in(0xAAAA,0xCCCC) and 
+vbin in(0xAAAA,0xCCCC) and 
+tiny in(-1,-3) and 
+short in(-1,-3) and 
+medium in(-1,-3) and 
+long_int in(-1,-3) and 
+longlong in(-1,-3) and 
+utiny in(1,3) and 
+ushort in(1,3) and 
+umedium in(1,3) and 
+ulong in(1,3) and 
+ulonglong in(1,3) and 
+bits in(b'001',b'011') and
+options in('one','three') and 
+flags in('one','one,two,three') and 
+date_field in('1901-01-01','1903-03-03') and
+year_field in('1901','1903') and
+time_field in('01:01:01','03:03:03') and 
+date_time in('1901-01-01 01:01:01','1903-03-03 03:03:03') 
+order by auto;
+
+select auto from t1 where
+"aaaa" in(string) and
+"aaaa" in(vstring) and
+0xAAAA in(bin) and 
+0xAAAA in(vbin) and 
+(-1 in(tiny)) and
+(-1 in(short)) and
+(-1 in(medium)) and
+(-1 in(long_int)) and
+(-1 in(longlong)) and
+1 in(utiny) and 
+1 in(ushort) and 
+1 in(umedium) and 
+1 in(ulong) and 
+1 in(ulonglong) and 
+b'001' in(bits) and
+'one' in(options) and 
+'one' in(flags) and 
+'1901-01-01' in(date_field) and
+'1901' in(year_field) and
+'01:01:01' in(time_field) and 
+'1901-01-01 01:01:01' in(date_time) 
+order by auto;
+
+# NOT IN
+select auto from t1 where
+string not in("aaaa","cccc") and 
+vstring not in("aaaa","cccc") and 
+bin not in(0xAAAA,0xCCCC) and 
+vbin not in(0xAAAA,0xCCCC) and 
+tiny not in(-1,-3) and 
+short not in(-1,-3) and 
+medium not in(-1,-3) and 
+long_int not in(-1,-3) and 
+longlong not in(-1,-3) and 
+utiny not in(1,3) and 
+ushort not in(1,3) and 
+umedium not in(1,3) and 
+ulong not in(1,3) and 
+ulonglong not in(1,3) and 
+bits not in(b'001',b'011') and
+options not in('one','three') and 
+flags not in('one','one,two,three') and 
+date_field not in('1901-01-01','1903-03-03') and
+year_field not in('1901','1903') and
+time_field not in('01:01:01','03:03:03') and 
+date_time not in('1901-01-01 01:01:01','1903-03-03 03:03:03') 
+order by auto;
+
+select auto from t1 where
+"aaaa" not in(string) and
+"aaaa" not in(vstring) and
+0xAAAA not in(bin) and 
+0xAAAA not in(vbin) and 
+(-1 not in(tiny)) and
+(-1 not in(short)) and
+(-1 not in(medium)) and
+(-1 not in(long_int)) and
+(-1 not in(longlong)) and
+1 not in(utiny) and 
+1 not in(ushort) and 
+1 not in(umedium) and 
+1 not in(ulong) and 
+1 not in(ulonglong) and 
+b'001' not in(bits) and
+'one' not in(options) and 
+'one' not in(flags) and 
+'1901-01-01' not in(date_field) and
+'1901' not in(year_field) and
+'01:01:01' not in(time_field) and 
+'1901-01-01 01:01:01' not in(date_time) 
+order by auto;
+
+# Various tests 
+select * from t2 where attr3 is null or attr1 > 2 and pk1= 3 order by pk1;
+select * from t2 where attr3 is not null and attr1 > 2 order by pk1;
+select * from t3 where attr2 >  9223372036854775803 and attr3 != 3 order by pk1;
+select * from t2,t3 where t2.attr1 < 1 and t2.attr2 = t3.attr2 and t3.attr1 < 5 order by t2.pk1;
+select * from t4 where attr1 < 5 and attr2 > 9223372036854775803 and attr3 != 3 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;
+
+set engine_condition_pushdown = on;
+
+# Test all types and compare operators
+--replace_column 9 #
+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;
+
+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;
+
+--replace_column 9 #
+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 
+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;
+
+--replace_column 9 #
+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 
+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;
+
+--replace_column 9 #
+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 
+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;
+
+--replace_column 9 #
+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 
+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;
+
+--replace_column 9 #
+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 
+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;
+
+# Test index scan with filter
+create index medium_index on t1(medium);
+
+# Test all types and compare operators
+--replace_column 9 #
+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;
+
+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;
+
+--replace_column 9 #
+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 
+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;
+
+--replace_column 9 #
+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 
+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;
+
+--replace_column 9 #
+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 
+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;
+
+--replace_column 9 #
+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 
+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;
+
+--replace_column 9 #
+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 
+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;
+
+# Test LIKE/NOT LIKE
+--replace_column 9 #
+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;
+
+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;
+
+--replace_column 9 #
+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
+vbin not like concat(0xBB, '%')
+order by auto;
+
+# BETWEEN
+--replace_column 9 #
+explain 
+select auto from t1 where
+(string between "aaaa" and "cccc") and 
+(vstring between "aaaa" and "cccc") and 
+(bin between 0xAAAA and 0xCCCC) and 
+(vbin between 0xAAAA and 0xCCCC) and 
+(tiny between -3 and -1) and 
+(short between -3 and -1) and 
+(medium between -3 and -1) and 
+(long_int between -3 and -1) and 
+(longlong between -3 and -1) and 
+(utiny between 1 and 3) and 
+(ushort between 1 and 3) and 
+(umedium between 1 and 3) and 
+(ulong between 1 and 3) and 
+(ulonglong between 1 and 3) and 
+/* (bits between b'001' and b'011') and */
+(options between 'one' and 'three') and 
+(flags between 'one' and 'one,two,three') and 
+(date_field between '1901-01-01' and '1903-03-03') and
+(year_field between '1901' and '1903') and
+(time_field between '01:01:01' and '03:03:03') and 
+(date_time between '1901-01-01 01:01:01' and '1903-03-03 03:03:03') 
+order by auto;
+
+select auto from t1 where
+(string between "aaaa" and "cccc") and 
+(vstring between "aaaa" and "cccc") and 
+(bin between 0xAAAA and 0xCCCC) and 
+(vbin between 0xAAAA and 0xCCCC) and 
+(tiny between -3 and -1) and 
+(short between -3 and -1) and 
+(medium between -3 and -1) and 
+(long_int between -3 and -1) and 
+(longlong between -3 and -1) and 
+(utiny between 1 and 3) and 
+(ushort between 1 and 3) and 
+(umedium between 1 and 3) and 
+(ulong between 1 and 3) and 
+(ulonglong between 1 and 3) and 
+/* (bits between b'001' and b'011') and */
+(options between 'one' and 'three') and 
+(flags between 'one' and 'one,two,three') and 
+(date_field between '1901-01-01' and '1903-03-03') and
+(year_field between '1901' and '1903') and
+(time_field between '01:01:01' and '03:03:03') and 
+(date_time between '1901-01-01 01:01:01' and '1903-03-03 03:03:03') 
+order by auto;
+
+--replace_column 9 #
+explain
+select auto from t1 where
+("aaaa" between string and string) and 
+("aaaa" between vstring and vstring) and 
+(0xAAAA between bin and bin) and 
+(0xAAAA between vbin and vbin) and 
+(-1 between tiny and tiny) and 
+(-1 between short and short) and 
+(-1 between medium and medium) and 
+(-1 between long_int and long_int) and 
+(-1 between longlong and longlong) and 
+(1 between utiny and utiny) and 
+(1 between ushort and ushort) and 
+(1 between umedium and umedium) and 
+(1 between ulong and ulong) and 
+(1 between ulonglong and ulonglong) and 
+/* (b'001' between bits and bits) and */
+('one' between options and options) and 
+('one' between flags and flags) and 
+('1901-01-01' between date_field and date_field) and
+('1901' between year_field and year_field) and
+('01:01:01' between time_field and time_field) and 
+('1901-01-01 01:01:01' between date_time and date_time) 
+order by auto;
+
+select auto from t1 where
+("aaaa" between string and string) and 
+("aaaa" between vstring and vstring) and 
+(0xAAAA between bin and bin) and 
+(0xAAAA between vbin and vbin) and 
+(-1 between tiny and tiny) and 
+(-1 between short and short) and 
+(-1 between medium and medium) and 
+(-1 between long_int and long_int) and 
+(-1 between longlong and longlong) and 
+(1 between utiny and utiny) and 
+(1 between ushort and ushort) and 
+(1 between umedium and umedium) and 
+(1 between ulong and ulong) and 
+(1 between ulonglong and ulonglong) and 
+/* (b'001' between bits and bits) and */
+('one' between options and options) and 
+('one' between flags and flags) and 
+('1901-01-01' between date_field and date_field) and
+('1901' between year_field and year_field) and
+('01:01:01' between time_field and time_field) and 
+('1901-01-01 01:01:01' between date_time and date_time) 
+order by auto;
+
+# NOT BETWEEN
+--replace_column 9 #
+explain 
+select auto from t1 where
+(string not between "aaaa" and "cccc") and 
+(vstring not between "aaaa" and "cccc") and 
+(bin not between 0xAAAA and 0xCCCC) and 
+(vbin not between 0xAAAA and 0xCCCC) and 
+(tiny not between -3 and -1) and 
+(short not between -3 and -1) and 
+(medium not between -3 and -1) and 
+(long_int not between -3 and -1) and 
+(longlong not between -3 and -1) and 
+(utiny not between 1 and 3) and 
+(ushort not between 1 and 3) and 
+(umedium not between 1 and 3) and 
+(ulong not between 1 and 3) and 
+(ulonglong not between 1 and 3) and 
+/* (bits not between b'001' and b'011') and */
+(options not between 'one' and 'three') and 
+(flags not between 'one' and 'one,two,three') and 
+(date_field not between '1901-01-01' and '1903-03-03') and
+(year_field not between '1901' and '1903') and
+(time_field not between '01:01:01' and '03:03:03') and 
+(date_time not between '1901-01-01 01:01:01' and '1903-03-03 03:03:03') 
+order by auto;
+
+select auto from t1 where
+(string not between "aaaa" and "cccc") and 
+(vstring not between "aaaa" and "cccc") and 
+(bin not between 0xAAAA and 0xCCCC) and 
+(vbin not between 0xAAAA and 0xCCCC) and 
+(tiny not between -3 and -1) and 
+(short not between -3 and -1) and 
+(medium not between -3 and -1) and 
+(long_int not between -3 and -1) and 
+(longlong not between -3 and -1) and 
+(utiny not between 1 and 3) and 
+(ushort not between 1 and 3) and 
+(umedium not between 1 and 3) and 
+(ulong not between 1 and 3) and 
+(ulonglong not between 1 and 3) and 
+/* (bits not between b'001' and b'011') and */
+(options not between 'one' and 'three') and 
+(flags not between 'one' and 'one,two,three') and 
+(date_field not between '1901-01-01' and '1903-03-03') and
+(year_field not between '1901' and '1903') and
+(time_field not between '01:01:01' and '03:03:03') and 
+(date_time not between '1901-01-01 01:01:01' and '1903-03-03 03:03:03') 
+order by auto;
+
+--replace_column 9 #
+explain
+select auto from t1 where
+("aaaa" not between string and string) and 
+("aaaa" not between vstring and vstring) and 
+(0xAAAA not between bin and bin) and 
+(0xAAAA not between vbin and vbin) and 
+(-1 not between tiny and tiny) and 
+(-1 not between short and short) and 
+(-1 not between medium and medium) and 
+(-1 not between long_int and long_int) and 
+(-1 not between longlong and longlong) and 
+(1 not between utiny and utiny) and 
+(1 not between ushort and ushort) and 
+(1 not between umedium and umedium) and 
+(1 not between ulong and ulong) and 
+(1 not between ulonglong and ulonglong) and 
+/* (b'001' not between bits and bits) and */
+('one' not between options and options) and 
+('one' not between flags and flags) and 
+('1901-01-01' not between date_field and date_field) and
+('1901' not between year_field and year_field) and
+('01:01:01' not between time_field and time_field) and 
+('1901-01-01 01:01:01' not between date_time and date_time) 
+order by auto;
+
+select auto from t1 where
+("aaaa" not between string and string) and 
+("aaaa" not between vstring and vstring) and 
+(0xAAAA not between bin and bin) and 
+(0xAAAA not between vbin and vbin) and 
+(-1 not between tiny and tiny) and 
+(-1 not between short and short) and 
+(-1 not between medium and medium) and 
+(-1 not between long_int and long_int) and 
+(-1 not between longlong and longlong) and 
+(1 not between utiny and utiny) and 
+(1 not between ushort and ushort) and 
+(1 not between umedium and umedium) and 
+(1 not between ulong and ulong) and 
+(1 not between ulonglong and ulonglong) and 
+/* (b'001' not between bits and bits) and */
+('one' not between options and options) and 
+('one' not between flags and flags) and 
+('1901-01-01' not between date_field and date_field) and
+('1901' not between year_field and year_field) and
+('01:01:01' not between time_field and time_field) and 
+('1901-01-01 01:01:01' not between date_time and date_time) 
+order by auto;
+
+# IN
+--replace_column 9 #
+explain
+select auto from t1 where
+string in("aaaa","cccc") and 
+vstring in("aaaa","cccc") and 
+bin in(0xAAAA,0xCCCC) and 
+vbin in(0xAAAA,0xCCCC) and 
+tiny in(-1,-3) and 
+short in(-1,-3) and 
+medium in(-1,-3) and 
+long_int in(-1,-3) and 
+longlong in(-1,-3) and 
+utiny in(1,3) and 
+ushort in(1,3) and 
+umedium in(1,3) and 
+ulong in(1,3) and 
+ulonglong in(1,3) and 
+/* bits in(b'001',b'011') and */
+options in('one','three') and 
+flags in('one','one,two,three') and 
+date_field in('1901-01-01','1903-03-03') and
+year_field in('1901','1903') and
+time_field in('01:01:01','03:03:03') and 
+date_time in('1901-01-01 01:01:01','1903-03-03 03:03:03') 
+order by auto;
+
+select auto from t1 where
+string in("aaaa","cccc") and 
+vstring in("aaaa","cccc") and 
+bin in(0xAAAA,0xCCCC) and 
+vbin in(0xAAAA,0xCCCC) and 
+tiny in(-1,-3) and 
+short in(-1,-3) and 
+medium in(-1,-3) and 
+long_int in(-1,-3) and 
+longlong in(-1,-3) and 
+utiny in(1,3) and 
+ushort in(1,3) and 
+umedium in(1,3) and 
+ulong in(1,3) and 
+ulonglong in(1,3) and 
+/* bits in(b'001',b'011') and */
+options in('one','three') and 
+flags in('one','one,two,three') and 
+date_field in('1901-01-01','1903-03-03') and
+year_field in('1901','1903') and
+time_field in('01:01:01','03:03:03') and 
+date_time in('1901-01-01 01:01:01','1903-03-03 03:03:03') 
+order by auto;
+
+--replace_column 9 #
+explain
+select auto from t1 where
+"aaaa" in(string) and 
+"aaaa" in(vstring) and 
+0xAAAA in(bin) and 
+0xAAAA in(vbin) and 
+(-1 in(tiny)) and
+(-1 in (short)) and
+(-1 in(medium)) and
+(-1 in(long_int)) and
+(-1 in(longlong)) and
+1 in(utiny) and 
+1 in(ushort) and 
+1 in(umedium) and 
+1 in(ulong) and 
+1 in(ulonglong) and 
+/* b'001' in(bits) and */
+'one' in(options) and 
+'one' in(flags) and 
+'1901-01-01' in(date_field) and
+'1901' in(year_field) and
+'01:01:01' in(time_field) and 
+'1901-01-01 01:01:01' in(date_time) 
+order by auto;
+
+select auto from t1 where
+"aaaa" in(string) and 
+"aaaa" in(vstring) and 
+0xAAAA in(bin) and 
+0xAAAA in(vbin) and 
+(-1 in(tiny)) and
+(-1 in (short)) and
+(-1 in(medium)) and
+(-1 in(long_int)) and
+(-1 in(longlong)) and
+1 in(utiny) and 
+1 in(ushort) and 
+1 in(umedium) and 
+1 in(ulong) and 
+1 in(ulonglong) and 
+/* b'001' in(bits) and */
+'one' in(options) and 
+'one' in(flags) and 
+'1901-01-01' in(date_field) and
+'1901' in(year_field) and
+'01:01:01' in(time_field) and 
+'1901-01-01 01:01:01' in(date_time) 
+order by auto;
+
+# NOT IN
+--replace_column 9 #
+explain
+select auto from t1 where
+string not in("aaaa","cccc") and 
+vstring not in("aaaa","cccc") and 
+bin not in(0xAAAA,0xCCCC) and 
+vbin not in(0xAAAA,0xCCCC) and 
+tiny not in(-1,-3) and 
+short not in(-1,-3) and 
+medium not in(-1,-3) and 
+long_int not in(-1,-3) and 
+longlong not in(-1,-3) and 
+utiny not in(1,3) and 
+ushort not in(1,3) and 
+umedium not in(1,3) and 
+ulong not in(1,3) and 
+ulonglong not in(1,3) and 
+/* bits not in(b'001',b'011') and */
+options not in('one','three') and 
+flags not in('one','one,two,three') and 
+date_field not in('1901-01-01','1903-03-03') and
+year_field not in('1901','1903') and
+time_field not in('01:01:01','03:03:03') and 
+date_time not in('1901-01-01 01:01:01','1903-03-03 03:03:03') 
+order by auto;
+
+select auto from t1 where
+string not in("aaaa","cccc") and 
+vstring not in("aaaa","cccc") and 
+bin not in(0xAAAA,0xCCCC) and 
+vbin not in(0xAAAA,0xCCCC) and 
+tiny not in(-1,-3) and 
+short not in(-1,-3) and 
+medium not in(-1,-3) and 
+long_int not in(-1,-3) and 
+longlong not in(-1,-3) and 
+utiny not in(1,3) and 
+ushort not in(1,3) and 
+umedium not in(1,3) and 
+ulong not in(1,3) and 
+ulonglong not in(1,3) and 
+/* bits not in(b'001',b'011') and */
+options not in('one','three') and 
+flags not in('one','one,two,three') and 
+date_field not in('1901-01-01','1903-03-03') and
+year_field not in('1901','1903') and
+time_field not in('01:01:01','03:03:03') and 
+date_time not in('1901-01-01 01:01:01','1903-03-03 03:03:03') 
+order by auto;
+
+--replace_column 9 #
+explain
+select auto from t1 where
+"aaaa" not in(string) and 
+"aaaa" not in(vstring) and 
+0xAAAA not in(bin) and 
+0xAAAA not in(vbin) and 
+(-1 not in(tiny)) and
+(-1 not in(short)) and
+(-1 not in(medium)) and
+(-1 not in(long_int)) and
+(-1 not in(longlong)) and
+1 not in(utiny) and 
+1 not in(ushort) and 
+1 not in(umedium) and 
+1 not in(ulong) and 
+1 not in(ulonglong) and 
+/* b'001' not in(bits) and */
+'one' not in(options) and 
+'one' not in(flags) and 
+'1901-01-01' not in(date_field) and
+'1901' not in(year_field) and
+'01:01:01' not in(time_field) and 
+'1901-01-01 01:01:01' not in(date_time) 
+order by auto;
+
+select auto from t1 where
+"aaaa" not in(string) and 
+"aaaa" not in(vstring) and 
+0xAAAA not in(bin) and 
+0xAAAA not in(vbin) and 
+(-1 not in(tiny)) and
+(-1 not in(short)) and
+(-1 not in(medium)) and
+(-1 not in(long_int)) and
+(-1 not in(longlong)) and
+1 not in(utiny) and 
+1 not in(ushort) and 
+1 not in(umedium) and 
+1 not in(ulong) and 
+1 not in(ulonglong) and 
+/* b'001' not in(bits) and */
+'one' not in(options) and 
+'one' not in(flags) and 
+'1901-01-01' not in(date_field) and
+'1901' not in(year_field) and
+'01:01:01' not in(time_field) and 
+'1901-01-01 01:01:01' not in(date_time) 
+order by auto;
+
+# Update test
+update t1
+set medium = 17
+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';
+
+# Delete test
+delete from t1 
+where
+string = "aaaa" and 
+vstring = "aaaa" and 
+bin = 0xAAAA and 
+vbin = 0xAAAA and
+tiny = -1 and 
+short = -1 and 
+medium = 17 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';
+
+select count(*) from t1;
+
+# Various tests 
+--replace_column 9 #
+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;
+
+--replace_column 9 #
+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;
+
+--replace_column 9 #
+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;
+
+--replace_column 9 #
+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;
+
+--replace_column 9 #
+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;
+
+--replace_column 9 #
+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
+--replace_column 9 #
+explain
+select auto from t1 where string = "aaaa" collate latin1_general_ci order by auto;
+--replace_column 9 #
+explain
+select * from t2 where (attr1 < 2) = (attr2 < 2) order by pk1;
+--replace_column 9 #
+explain
+select * from t3 left join t4 on t4.attr2 = t3.attr2 where t4.attr1 > 1 and t4.attr3 < 5 or t4.attr1 is null order by t4.pk1;
+
+# bug#15722
+create table t5 (a int primary key auto_increment, b tinytext not null) 
+engine = ndb;
+insert into t5 (b) values ('jonas'), ('jensing'), ('johan');
+set engine_condition_pushdown = off;
+select * from t5 where b like '%jo%' order by a;
+set engine_condition_pushdown = on;
+--replace_column 9 #
+explain select * from t5 where b like '%jo%';
+select * from t5 where b like '%jo%' order by a;
+
+# bug#21056  	ndb pushdown equal/setValue error on datetime
+set engine_condition_pushdown = off;
+select auto from t1 where date_time like '1902-02-02 %' order by auto;
+select auto from t1 where date_time not like '1902-02-02 %' order by auto;
+set engine_condition_pushdown = on;
+--replace_column 9 #
+explain select auto from t1 where date_time like '1902-02-02 %';
+select auto from t1 where date_time like '1902-02-02 %' order by auto;
+--replace_column 9 #
+explain select auto from t1 where date_time not like '1902-02-02 %';
+select auto from t1 where date_time not like '1902-02-02 %' order by auto;
+
+# bug#17421 -1
+drop table t1;
+create table t1 (a int, b varchar(3), primary key using hash(a))
+engine=ndb;
+insert into t1 values (1,'a'), (2,'ab'), (3,'abc');
+# in TUP the constants 'ab' 'abc' were expected in varchar format
+# "like" returned error which became "false"
+# scan filter negates "or" which exposes the bug
+set engine_condition_pushdown = off;
+select * from t1 where b like 'ab';
+select * from t1 where b like 'ab' or b like 'ab';
+select * from t1 where b like 'abc';
+select * from t1 where b like 'abc' or b like 'abc';
+set engine_condition_pushdown = on;
+select * from t1 where b like 'ab';
+select * from t1 where b like 'ab' or b like 'ab';
+select * from t1 where b like 'abc';
+select * from t1 where b like 'abc' or b like 'abc';
+
+# bug#17421 -2
+drop table t1;
+create table t1 (a int, b char(3), primary key using hash(a))
+engine=ndb;
+insert into t1 values (1,'a'), (2,'ab'), (3,'abc');
+# test that incorrect MySQL behaviour is preserved
+# 'ab ' LIKE 'ab' is true in MySQL
+set engine_condition_pushdown = off;
+select * from t1 where b like 'ab';
+select * from t1 where b like 'ab' or b like 'ab';
+select * from t1 where b like 'abc';
+select * from t1 where b like 'abc' or b like 'abc';
+set engine_condition_pushdown = on;
+select * from t1 where b like 'ab';
+select * from t1 where b like 'ab' or b like 'ab';
+select * from t1 where b like 'abc';
+select * from t1 where b like 'abc' or b like 'abc';
+
+# bug#20406 (maybe same as bug#17421 -1, not seen on 32-bit x86)
+drop table t1;
+create table  t1 ( fname varchar(255), lname varchar(255) )
+engine=ndbcluster;
+insert into t1 values ("Young","Foo");
+
+set engine_condition_pushdown = 0;
+SELECT fname, lname FROM t1 WHERE (fname like 'Y%') or (lname like 'F%');
+set engine_condition_pushdown = 1;
+SELECT fname, lname FROM t1 WHERE (fname like 'Y%') or (lname like 'F%');
+
+# make sure optimizer does not do some crazy shortcut
+insert into t1 values ("aaa", "aaa");
+insert into t1 values ("bbb", "bbb");
+insert into t1 values ("ccc", "ccc");
+insert into t1 values ("ddd", "ddd");
+
+set engine_condition_pushdown = 0;
+SELECT fname, lname FROM t1 WHERE (fname like 'Y%') or (lname like 'F%');
+set engine_condition_pushdown = 1;
+SELECT fname, lname FROM t1 WHERE (fname like 'Y%') or (lname like 'F%');
+
+set engine_condition_pushdown = @old_ecpd;
+DROP TABLE t1,t2,t3,t4,t5;