1-- source include/have_ndb.inc 2 3--disable_warnings 4drop table if exists t1, t2, t3, t4; 5--enable_warnings 6 7CREATE TABLE t10( 8 K INT NOT NULL AUTO_INCREMENT, 9 I INT, J INT, 10 PRIMARY KEY(K), 11 KEY(I,J), 12 UNIQUE KEY(J,K) 13) ENGINE=ndbcluster 14 partition by key (K) partitions 1; 15 16INSERT INTO t10(I,J) VALUES (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9),(0,0); 17 18CREATE TABLE t100 LIKE t10; 19INSERT INTO t100(I,J) 20 SELECT X.J, X.J+(10*Y.J) FROM t10 AS X,t10 AS Y; 21 22CREATE TABLE t10000 LIKE t10; 23 24# Insert into t10000 into myisam and alter to ndb 25# not to exhaust MaxNoOfConcurrentOperations 26ALTER TABLE t10000 ENGINE=MYISAM; 27INSERT INTO t10000(I,J) 28 SELECT X.J, X.J+(100*Y.J) FROM t100 AS X,t100 AS Y; 29ALTER TABLE t10000 ENGINE=NDBCLUSTER; 30 31ANALYZE TABLE t10,t100,t10000; 32 33SELECT COUNT(*) FROM t10; 34SELECT COUNT(*) FROM t100; 35SELECT COUNT(*) FROM t10000; 36 37# 38# Bug #59517: Incorrect detection of single row access in 39# ha_ndbcluster::records_in_range() 40 41# Expect a single row (or const) when PK is excact specified 42EXPLAIN 43SELECT * FROM t10000 WHERE k = 42; 44 45# All queries below should *not* return a single row 46EXPLAIN 47SELECT * FROM t10000 WHERE k >= 42 and k < 10000; 48EXPLAIN 49SELECT * FROM t10000 WHERE k BETWEEN 42 AND 10000; 50EXPLAIN 51SELECT * FROM t10000 WHERE k < 42; 52EXPLAIN 53SELECT * FROM t10000 WHERE k > 42; 54 55# 56# Bug #59519 ::set_rec_per_key() assumes ORDER_INDEX to be unique 57# 58 59# 'REF' join of 'Y' should match >1 rows 60EXPLAIN 61SELECT * FROM t10000 AS x JOIN t10000 AS y 62 ON y.i=x.i AND y.j = x.i; 63 64# 65# Bug #11804277: INCORRECT INDEX MAY BE SELECTED DUE TO INSUFFICIENT 66# STATISTICS FROM CLUSTER 67# 68 69# Open bounded range should return 10% of #rows in table 70EXPLAIN 71SELECT * FROM t100 WHERE k < 42; 72EXPLAIN 73SELECT * FROM t100 WHERE k > 42; 74EXPLAIN 75SELECT * FROM t10000 WHERE k < 42; 76EXPLAIN 77SELECT * FROM t10000 WHERE k > 42; 78 79#Closed bounded range should return 5% of #rows in table 80EXPLAIN 81SELECT * FROM t100 WHERE k BETWEEN 42 AND 10000; 82EXPLAIN 83SELECT * FROM t10000 WHERE k BETWEEN 42 AND 10000; 84 85#EQ-range selectivity depends on 86# - key length specified 87# - #rows in table. 88# - unique/non-unique index 89# - min 2% selectivity 90# 91# Possibly combined with open/closed ranges as 92# above which further improves selectivity 93# 94EXPLAIN 95SELECT * FROM t10000 WHERE I = 0; 96EXPLAIN 97SELECT * FROM t10000 WHERE J = 0; 98 99EXPLAIN 100SELECT * FROM t10000 WHERE I = 0 AND J = 0; 101 102EXPLAIN 103SELECT * FROM t10000 WHERE I = 0; 104EXPLAIN 105SELECT * FROM t10000 WHERE I = 0 AND J > 1; 106EXPLAIN 107SELECT * FROM t10000 WHERE I = 0 AND J < 1; 108EXPLAIN 109SELECT * FROM t10000 WHERE I = 0 AND J BETWEEN 1 AND 10; 110EXPLAIN 111SELECT * FROM t10000 WHERE I = 0 AND J = 1; 112 113EXPLAIN 114SELECT * FROM t10000 WHERE J = 0; 115EXPLAIN 116SELECT * FROM t10000 WHERE J = 0 AND K > 1; 117EXPLAIN 118SELECT * FROM t10000 WHERE J = 0 AND K < 1; 119EXPLAIN 120SELECT * FROM t10000 WHERE J = 0 AND K BETWEEN 1 AND 10; 121EXPLAIN 122SELECT * FROM t10000 WHERE J = 0 AND K = 1; 123 124## Verify selection of 'best' index 125## (The one of index I/J being EQ) 126EXPLAIN 127SELECT * FROM t10000 WHERE I = 0 AND J <> 1; 128EXPLAIN 129SELECT * FROM t10000 WHERE I <> 0 AND J = 1; 130EXPLAIN 131SELECT * FROM t10000 WHERE I <> 0 AND J <> 1; 132 133EXPLAIN 134SELECT * FROM t10000 WHERE J <> 1 AND I = 0; 135EXPLAIN 136SELECT * FROM t10000 WHERE J = 1 AND I <> 0; 137EXPLAIN 138SELECT * FROM t10000 WHERE J <> 1 AND I <> 0; 139 140 141DROP TABLE t10,t100,t10000; 142 143--echo End of 5.1 tests 144