From e5cc604eabf843f25fad2e8e91c4360416b72471 Mon Sep 17 00:00:00 2001 From: unknown Date: Fri, 28 Nov 2003 15:45:34 +0200 Subject: Fixed range optimzier bug (Bug #1828) mysql-test/r/range.result: test for range optimzier bug mysql-test/t/range.test: test for range optimzier bug --- mysql-test/r/range.result | 10 ++++++++++ mysql-test/t/range.test | 13 ++++++++++++- 2 files changed, 22 insertions(+), 1 deletion(-) (limited to 'mysql-test') diff --git a/mysql-test/r/range.result b/mysql-test/r/range.result index 68987009598..e87df9a6c24 100644 --- a/mysql-test/r/range.result +++ b/mysql-test/r/range.result @@ -290,3 +290,13 @@ t1 range a,b a 5 NULL 2 Using where SELECT * FROM t1 WHERE a IN(1,2) AND b=5; a b DROP TABLE t1; +CREATE TABLE t1 (a int, b int, c int, INDEX (c,a,b)); +INSERT INTO t1 VALUES (1,0,0),(1,0,0),(1,0,0); +INSERT INTO t1 VALUES (0,1,0),(0,1,0),(0,1,0); +SELECT COUNT(*) FROM t1 WHERE (c=0 and a=1) or (c=0 and b=1); +COUNT(*) +6 +SELECT COUNT(*) FROM t1 WHERE (c=0 and b=1) or (c=0 and a=1); +COUNT(*) +6 +DROP TABLE t1; diff --git a/mysql-test/t/range.test b/mysql-test/t/range.test index e09fa73256b..364ea2d4195 100644 --- a/mysql-test/t/range.test +++ b/mysql-test/t/range.test @@ -231,9 +231,20 @@ INSERT INTO t1 VALUES (21,4),(22,5),(23,5),(24,5),(25,5),(26,5),(30,5),(31,5),(32,5),(33,5), (33,5),(33,5),(33,5),(33,5),(34,5),(35,5); +# we expect that optimizer will choose index on A EXPLAIN SELECT * FROM t1 WHERE a IN(1,2) AND b=5; SELECT * FROM t1 WHERE a IN(1,2) AND b=5; DROP TABLE t1; -# we expect that optimizer will choose index on A +# +# Test error with +# + +CREATE TABLE t1 (a int, b int, c int, INDEX (c,a,b)); +INSERT INTO t1 VALUES (1,0,0),(1,0,0),(1,0,0); +INSERT INTO t1 VALUES (0,1,0),(0,1,0),(0,1,0); +# -- First reports 3; second reports 6 +SELECT COUNT(*) FROM t1 WHERE (c=0 and a=1) or (c=0 and b=1); +SELECT COUNT(*) FROM t1 WHERE (c=0 and b=1) or (c=0 and a=1); +DROP TABLE t1; -- cgit v1.2.1