drop table if exists t1, t2, t3, t4;
CREATE TABLE t10(
K INT NOT NULL AUTO_INCREMENT,
I INT, J INT,
PRIMARY KEY(K),
KEY(I,J),
UNIQUE KEY(J,K)
) ENGINE=ndbcluster;
INSERT 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);
CREATE TABLE t100 LIKE t10;
INSERT INTO t100(I,J)
SELECT X.J, X.J+(10*Y.J) FROM t10 AS X,t10 AS Y;
CREATE TABLE t10000 LIKE t10;
INSERT INTO t10000(I,J)
SELECT X.J, X.J+(100*Y.J) FROM t100 AS X,t100 AS Y
WHERE X.J<50;
INSERT INTO t10000(I,J)
SELECT X.J, X.J+(100*Y.J) FROM t100 AS X,t100 AS Y
WHERE X.J>=50;
ANALYZE TABLE t10,t100,t10000;
Table	Op	Msg_type	Msg_text
test.t10	analyze	status	OK
test.t100	analyze	status	OK
test.t10000	analyze	status	OK
SELECT COUNT(*) FROM t10;
COUNT(*)
10
SELECT COUNT(*) FROM t100;
COUNT(*)
100
SELECT COUNT(*) FROM t10000;
COUNT(*)
10000
EXPLAIN
SELECT * FROM t10000 WHERE k = 42;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t10000	const	PRIMARY	PRIMARY	4	const	1	
EXPLAIN
SELECT * FROM t10000 WHERE k >= 42 and k < 10000;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t10000	range	PRIMARY	PRIMARY	4	NULL	500	Using where with pushed condition
EXPLAIN
SELECT * FROM t10000 WHERE k BETWEEN 42 AND 10000;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t10000	range	PRIMARY	PRIMARY	4	NULL	500	Using where with pushed condition
EXPLAIN
SELECT * FROM t10000 WHERE k < 42;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t10000	range	PRIMARY	PRIMARY	4	NULL	1000	Using where with pushed condition
EXPLAIN
SELECT * FROM t10000 WHERE k > 42;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t10000	range	PRIMARY	PRIMARY	4	NULL	1000	Using where with pushed condition
EXPLAIN
SELECT * FROM t10000 AS X JOIN t10000 AS Y
ON Y.I=X.I AND Y.J = X.I;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	X	ALL	I	NULL	NULL	NULL	10000	
1	SIMPLE	Y	ref	J,I	I	10	test.X.I,test.X.I	11	Using where
EXPLAIN
SELECT * FROM t100 WHERE k < 42;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t100	range	PRIMARY	PRIMARY	4	NULL	10	Using where with pushed condition
EXPLAIN
SELECT * FROM t100 WHERE k > 42;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t100	range	PRIMARY	PRIMARY	4	NULL	10	Using where with pushed condition
EXPLAIN
SELECT * FROM t10000 WHERE k < 42;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t10000	range	PRIMARY	PRIMARY	4	NULL	1000	Using where with pushed condition
EXPLAIN
SELECT * FROM t10000 WHERE k > 42;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t10000	range	PRIMARY	PRIMARY	4	NULL	1000	Using where with pushed condition
EXPLAIN
SELECT * FROM t100 WHERE k BETWEEN 42 AND 10000;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t100	range	PRIMARY	PRIMARY	4	NULL	5	Using where with pushed condition
EXPLAIN
SELECT * FROM t10000 WHERE k BETWEEN 42 AND 10000;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t10000	range	PRIMARY	PRIMARY	4	NULL	500	Using where with pushed condition
EXPLAIN
SELECT * FROM t10000 WHERE I = 0;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t10000	ref	I	I	5	const	200	Using where with pushed condition
EXPLAIN
SELECT * FROM t10000 WHERE J = 0;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t10000	ref	J	J	5	const	100	Using where with pushed condition
EXPLAIN
SELECT * FROM t10000 WHERE I = 0 AND J = 0;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t10000	ref	J,I	I	10	const,const	4	Using where with pushed condition
EXPLAIN
SELECT * FROM t10000 WHERE I = 0;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t10000	ref	I	I	5	const	200	Using where with pushed condition
EXPLAIN
SELECT * FROM t10000 WHERE I = 0 AND J > 1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t10000	range	J,I	I	10	NULL	100	Using where with pushed condition
EXPLAIN
SELECT * FROM t10000 WHERE I = 0 AND J < 1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t10000	range	J,I	I	10	NULL	50	Using where with pushed condition
EXPLAIN
SELECT * FROM t10000 WHERE I = 0 AND J BETWEEN 1 AND 10;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t10000	range	J,I	I	10	NULL	50	Using where with pushed condition
EXPLAIN
SELECT * FROM t10000 WHERE I = 0 AND J = 1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t10000	ref	J,I	I	10	const,const	4	Using where with pushed condition
EXPLAIN
SELECT * FROM t10000 WHERE J = 0;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t10000	ref	J	J	5	const	100	Using where with pushed condition
EXPLAIN
SELECT * FROM t10000 WHERE J = 0 AND K > 1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t10000	range	PRIMARY,J	J	9	NULL	50	Using where with pushed condition
EXPLAIN
SELECT * FROM t10000 WHERE J = 0 AND K < 1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t10000	range	PRIMARY,J	J	9	NULL	50	Using where with pushed condition
EXPLAIN
SELECT * FROM t10000 WHERE J = 0 AND K BETWEEN 1 AND 10;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t10000	range	PRIMARY,J	J	9	NULL	25	Using where with pushed condition
EXPLAIN
SELECT * FROM t10000 WHERE J = 0 AND K = 1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
EXPLAIN
SELECT * FROM t10000 WHERE I = 0 AND J <> 1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t10000	range	J,I	I	10	NULL	150	Using where with pushed condition
EXPLAIN
SELECT * FROM t10000 WHERE I <> 0 AND J = 1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t10000	ref	J,I	J	5	const	100	Using where with pushed condition
EXPLAIN
SELECT * FROM t10000 WHERE I <> 0 AND J <> 1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t10000	range	J,I	J	5	NULL	1500	Using where with pushed condition
EXPLAIN
SELECT * FROM t10000 WHERE J <> 1 AND I = 0;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t10000	range	J,I	I	10	NULL	150	Using where with pushed condition
EXPLAIN
SELECT * FROM t10000 WHERE J = 1 AND I <> 0;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t10000	ref	J,I	J	5	const	100	Using where with pushed condition
EXPLAIN
SELECT * FROM t10000 WHERE J <> 1 AND I <> 0;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t10000	range	J,I	J	5	NULL	1500	Using where with pushed condition
DROP TABLE t10,t100,t10000;
End of 5.1 tests