diff options
| author | unknown <tnurnberg@white.intern.koehntopp.de> | 2008-03-27 03:18:46 +0100 |
|---|---|---|
| committer | unknown <tnurnberg@white.intern.koehntopp.de> | 2008-03-27 03:18:46 +0100 |
| commit | 548b389f3ac68323583c0819fd640d12bf1bc46c (patch) | |
| tree | 9acb8b8357369ca61d4d9a70a27a4fbf71a09dd1 /mysql-test | |
| parent | 269ebe54211cf96c4e64fa7268a1414588ca1de8 (diff) | |
| parent | fd1616311d7fa2b64a3911ae8e79cd05fa2c8782 (diff) | |
| download | mariadb-git-548b389f3ac68323583c0819fd640d12bf1bc46c.tar.gz | |
Merge mysql.com:/misc/mysql/34731/50-34731
into mysql.com:/misc/mysql/34731/51-34731
mysql-test/t/range.test:
Auto merged
sql/opt_range.cc:
Auto merged
mysql-test/r/range.result:
manual merge
Diffstat (limited to 'mysql-test')
| -rw-r--r-- | mysql-test/r/range.result | 39 | ||||
| -rw-r--r-- | mysql-test/t/range.test | 46 |
2 files changed, 85 insertions, 0 deletions
diff --git a/mysql-test/r/range.result b/mysql-test/r/range.result index f67a8f4cae6..cc5e8d2be96 100644 --- a/mysql-test/r/range.result +++ b/mysql-test/r/range.result @@ -1166,6 +1166,45 @@ EXPLAIN SELECT a FROM t1 WHERE a='b' OR a='B'; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range a a 35 NULL 3 Using where; Using index DROP TABLE t1; +CREATE TABLE t1 (f1 TINYINT(11) UNSIGNED NOT NULL, PRIMARY KEY (f1)); +INSERT INTO t1 VALUES (127),(254),(0),(1),(255); +SELECT SQL_NO_CACHE COUNT(*) FROM t1 WHERE f1 < 256; +COUNT(*) +5 +SELECT SQL_NO_CACHE COUNT(*) FROM t1 WHERE f1 < 256.0; +COUNT(*) +5 +SELECT SQL_NO_CACHE COUNT(*) FROM t1 WHERE f1 < 255; +COUNT(*) +4 +SELECT SQL_NO_CACHE COUNT(*) FROM t1 WHERE f1 < -1; +COUNT(*) +0 +SELECT SQL_NO_CACHE COUNT(*) FROM t1 WHERE f1 > -1; +COUNT(*) +5 +DROP TABLE t1; +CREATE TABLE t1 ( f1 TINYINT(11) NOT NULL, PRIMARY KEY (f1)); +INSERT INTO t1 VALUES (127),(126),(0),(-128),(-127); +SELECT SQL_NO_CACHE COUNT(*) FROM t1 WHERE f1 < 128; +COUNT(*) +5 +SELECT SQL_NO_CACHE COUNT(*) FROM t1 WHERE f1 < 128.0; +COUNT(*) +5 +SELECT SQL_NO_CACHE COUNT(*) FROM t1 WHERE f1 < 127; +COUNT(*) +4 +SELECT SQL_NO_CACHE COUNT(*) FROM t1 WHERE f1 > -129; +COUNT(*) +5 +SELECT SQL_NO_CACHE COUNT(*) FROM t1 WHERE f1 > -129.0; +COUNT(*) +5 +SELECT SQL_NO_CACHE COUNT(*) FROM t1 WHERE f1 > -128; +COUNT(*) +4 +DROP TABLE t1; create table t1 (a int); insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); create table t2 (a int, b int, filler char(100)); diff --git a/mysql-test/t/range.test b/mysql-test/t/range.test index 514e3074292..e1411e7fd46 100644 --- a/mysql-test/t/range.test +++ b/mysql-test/t/range.test @@ -972,6 +972,52 @@ EXPLAIN SELECT a FROM t1 WHERE a='b' OR a='B'; DROP TABLE t1; +# +# Bug #34731: highest possible value for INT erroneously filtered by WHERE +# + +# test UNSIGNED. only occurs when indexed. +CREATE TABLE t1 (f1 TINYINT(11) UNSIGNED NOT NULL, PRIMARY KEY (f1)); + +INSERT INTO t1 VALUES (127),(254),(0),(1),(255); + +# test upper bound +# count 5 +SELECT SQL_NO_CACHE COUNT(*) FROM t1 WHERE f1 < 256; +SELECT SQL_NO_CACHE COUNT(*) FROM t1 WHERE f1 < 256.0; +# count 4 +SELECT SQL_NO_CACHE COUNT(*) FROM t1 WHERE f1 < 255; + +# show we don't fiddle with lower bound on UNSIGNED +# count 0 +SELECT SQL_NO_CACHE COUNT(*) FROM t1 WHERE f1 < -1; +# count 5 +SELECT SQL_NO_CACHE COUNT(*) FROM t1 WHERE f1 > -1; + +DROP TABLE t1; + + +# test signed. only occurs when index. +CREATE TABLE t1 ( f1 TINYINT(11) NOT NULL, PRIMARY KEY (f1)); + +INSERT INTO t1 VALUES (127),(126),(0),(-128),(-127); + +# test upper bound +# count 5 +SELECT SQL_NO_CACHE COUNT(*) FROM t1 WHERE f1 < 128; +SELECT SQL_NO_CACHE COUNT(*) FROM t1 WHERE f1 < 128.0; +# count 4 +SELECT SQL_NO_CACHE COUNT(*) FROM t1 WHERE f1 < 127; + +# test lower bound +# count 5 +SELECT SQL_NO_CACHE COUNT(*) FROM t1 WHERE f1 > -129; +SELECT SQL_NO_CACHE COUNT(*) FROM t1 WHERE f1 > -129.0; +# count 4 +SELECT SQL_NO_CACHE COUNT(*) FROM t1 WHERE f1 > -128; + +DROP TABLE t1; + # End of 5.0 tests # BUG#22393 fix: Adjust 'ref' estimate if we have 'range' estimate for |