diff options
Diffstat (limited to 'mysql-test/t/ndb_condition_pushdown.test')
| -rw-r--r-- | mysql-test/t/ndb_condition_pushdown.test | 1756 |
1 files changed, 0 insertions, 1756 deletions
diff --git a/mysql-test/t/ndb_condition_pushdown.test b/mysql-test/t/ndb_condition_pushdown.test deleted file mode 100644 index ab56430ac1d..00000000000 --- a/mysql-test/t/ndb_condition_pushdown.test +++ /dev/null @@ -1,1756 +0,0 @@ --- 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; |