diff options
Diffstat (limited to 'mysql-test')
| -rw-r--r-- | mysql-test/r/ndb_index_unique.result | 35 | ||||
| -rw-r--r-- | mysql-test/t/ndb_index_unique.test | 15 |
2 files changed, 48 insertions, 2 deletions
diff --git a/mysql-test/r/ndb_index_unique.result b/mysql-test/r/ndb_index_unique.result index ce4759ad553..12ffc8437db 100644 --- a/mysql-test/r/ndb_index_unique.result +++ b/mysql-test/r/ndb_index_unique.result @@ -140,7 +140,40 @@ b int unsigned not null, c int unsigned, UNIQUE USING HASH (b, c) ) engine=ndbcluster; -ERROR 42000: Column 'c' is used with UNIQUE or INDEX but is not defined as NOT NULL +Warnings: +Warning 1121 Ndb does not support unique index on NULL valued attributes, index access with NULL value will become full table scan +insert t2 values(1,1,NULL),(2,2,2),(3,3,NULL),(4,4,4),(5,5,NULL),(6,6,6),(7,7,NULL),(8,3,NULL),(9,3,NULL); +select * from t2 where c IS NULL order by a; +a b c +1 1 NULL +3 3 NULL +5 5 NULL +7 7 NULL +8 3 NULL +9 3 NULL +select * from t2 where b = 3 AND c IS NULL order by a; +a b c +3 3 NULL +8 3 NULL +9 3 NULL +select * from t2 where (b = 3 OR b = 5) AND c IS NULL order by a; +a b c +3 3 NULL +5 5 NULL +8 3 NULL +9 3 NULL +set @old_ecpd = @@session.engine_condition_pushdown; +set engine_condition_pushdown = true; +explain select * from t2 where (b = 3 OR b = 5) AND c IS NULL AND a < 9 order by a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 range PRIMARY,b PRIMARY 4 NULL 1 Using where with pushed condition +select * from t2 where (b = 3 OR b = 5) AND c IS NULL AND a < 9 order by a; +a b c +3 3 NULL +5 5 NULL +8 3 NULL +set engine_condition_pushdown = @old_ecpd; +drop table t2; CREATE TABLE t3 ( a int unsigned NOT NULL, b int unsigned not null, diff --git a/mysql-test/t/ndb_index_unique.test b/mysql-test/t/ndb_index_unique.test index 8561b3794c4..e210491c9d0 100644 --- a/mysql-test/t/ndb_index_unique.test +++ b/mysql-test/t/ndb_index_unique.test @@ -85,7 +85,6 @@ select * from t2 order by a; drop table t2; --- error 1121 CREATE TABLE t2 ( a int unsigned NOT NULL PRIMARY KEY, b int unsigned not null, @@ -93,6 +92,20 @@ CREATE TABLE t2 ( UNIQUE USING HASH (b, c) ) engine=ndbcluster; + +insert t2 values(1,1,NULL),(2,2,2),(3,3,NULL),(4,4,4),(5,5,NULL),(6,6,6),(7,7,NULL),(8,3,NULL),(9,3,NULL); + +select * from t2 where c IS NULL order by a; +select * from t2 where b = 3 AND c IS NULL order by a; +select * from t2 where (b = 3 OR b = 5) AND c IS NULL order by a; +set @old_ecpd = @@session.engine_condition_pushdown; +set engine_condition_pushdown = true; +explain select * from t2 where (b = 3 OR b = 5) AND c IS NULL AND a < 9 order by a; +select * from t2 where (b = 3 OR b = 5) AND c IS NULL AND a < 9 order by a; +set engine_condition_pushdown = @old_ecpd; + +drop table t2; + # # Show use of PRIMARY KEY USING HASH indexes # |