#At file:///G:/bzr/mysql-6.0-falcon-team/ based on revid:vvaintroub@stripped
3097 Vladislav Vaintroub 2009-04-06
Bug#42208 Falcon's ORDER BY ..LIMIT gives wrong/inconsistent results on NULL values
Bug#42405 Missing/Wrong rows on Falcon's ORDER BY ..LIMIT with range access on pk/index
This patch introduces new Falcon index version.
It changes handling of multisegment keys, so binary keys that
differ in number of trailing zeros bytes are now considered different.
Also, NULL in index is now different from 0, empty string
(0x00) and zero length string and sorts according to SQL
standard rules, less than any other value.
(incorporated changes from Lars-Erik for Bug#42208)
INDEX_CURRENT_VERSION is now 2. Minor ODS version is increased to 5.
@ mysql-test/suite/falcon/r/falcon_bug_42208.result
test for bug 42208
@ mysql-test/suite/falcon/r/falcon_index_v2.result
test for correct sorting order of multisegment keys,
NULLs and zero length keys
@ mysql-test/suite/falcon/t/falcon_bug_42208.test
test for bug 42208
@ mysql-test/suite/falcon/t/falcon_index_v2.test
test for correct sorting order of multisegment keys,
NULLs and zero length keys
@ storage/falcon/Database.h
Increase ODS version (new index format)
@ storage/falcon/Index.cpp
Increase ODS version (new index format)
@ storage/falcon/Index.h
Increase index version
@ storage/falcon/StorageDatabase.cpp
Use all keys provided by optimizer, do not break at NULLs
(but not for index version 1, where NULL is broken)
added:
mysql-test/suite/falcon/r/falcon_bug_42208.result
mysql-test/suite/falcon/r/falcon_bug_42405.result
mysql-test/suite/falcon/r/falcon_index_v2.result
mysql-test/suite/falcon/t/falcon_bug_42208.test
mysql-test/suite/falcon/t/falcon_bug_42405.test
mysql-test/suite/falcon/t/falcon_index_v2.test
modified:
storage/falcon/Database.h
storage/falcon/Index.cpp
storage/falcon/Index.h
storage/falcon/StorageDatabase.cpp
=== added file 'mysql-test/suite/falcon/r/falcon_bug_42208.result'
--- a/mysql-test/suite/falcon/r/falcon_bug_42208.result 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/falcon/r/falcon_bug_42208.result 2009-04-05 22:45:06 +0000
@@ -0,0 +1,514 @@
+*** Bug #42208 ***
+SET @@storage_engine = 'Falcon';
+DROP TABLE IF EXISTS t1;
+CREATE TABLE t1(c1 tinyint unsigned null, c2 tinyint, c3 bigint, index idx(c2,c3));
+INSERT INTO t1 (c1,c2,c3) values (0,NULL,26),(0,NULL,106),(0,-128,11);
+INSERT INTO t1 (c1,c2,c3) values (0,0,26),(0,1,2),(0,126,26);
+SELECT * FROM t1 ORDER BY c2,c3;
+c1 c2 c3
+0 NULL 26
+0 NULL 106
+0 -128 11
+0 0 26
+0 1 2
+0 126 26
+SELECT * FROM t1 ORDER BY c2,c3 LIMIT 1;
+c1 c2 c3
+0 NULL 26
+SELECT * FROM t1 ORDER BY c2,c3 LIMIT 2;
+c1 c2 c3
+0 NULL 26
+0 NULL 106
+SELECT * FROM t1 ORDER BY c2,c3 LIMIT 3;
+c1 c2 c3
+0 NULL 26
+0 NULL 106
+0 -128 11
+SELECT * FROM t1 ORDER BY c2,c3 LIMIT 4;
+c1 c2 c3
+0 NULL 26
+0 NULL 106
+0 -128 11
+0 0 26
+SELECT * FROM t1 ORDER BY c2,c3 LIMIT 5;
+c1 c2 c3
+0 NULL 26
+0 NULL 106
+0 -128 11
+0 0 26
+0 1 2
+SELECT * FROM t1 ORDER BY c2,c3 LIMIT 6;
+c1 c2 c3
+0 NULL 26
+0 NULL 106
+0 -128 11
+0 0 26
+0 1 2
+0 126 26
+DROP TABLE t1;
+CREATE TABLE t1 (s1 varchar(10), key(s1));
+INSERT INTO t1 values ("This"), (""), ("is"), (NULL), ("a"), (NULL), (""), (""), ("test"), (""), ("of"), (NULL), ("and"), ("");
+SELECT * FROM t1 ORDER BY s1;
+s1
+NULL
+NULL
+NULL
+
+
+
+
+
+a
+and
+is
+of
+test
+This
+SELECT * FROM t1 ORDER BY s1 LIMIT 1;
+s1
+NULL
+SELECT * FROM t1 ORDER BY s1 LIMIT 2;
+s1
+NULL
+NULL
+SELECT * FROM t1 ORDER BY s1 LIMIT 3;
+s1
+NULL
+NULL
+NULL
+SELECT * FROM t1 ORDER BY s1 LIMIT 4;
+s1
+NULL
+NULL
+NULL
+
+SELECT * FROM t1 ORDER BY s1 LIMIT 5;
+s1
+NULL
+NULL
+NULL
+
+
+SELECT * FROM t1 ORDER BY s1 LIMIT 6;
+s1
+NULL
+NULL
+NULL
+
+
+
+SELECT * FROM t1 ORDER BY s1 LIMIT 7;
+s1
+NULL
+NULL
+NULL
+
+
+
+
+SELECT * FROM t1 ORDER BY s1 LIMIT 8;
+s1
+NULL
+NULL
+NULL
+
+
+
+
+
+SELECT * FROM t1 ORDER BY s1 LIMIT 9;
+s1
+NULL
+NULL
+NULL
+
+
+
+
+
+a
+SELECT * FROM t1 ORDER BY s1 LIMIT 10;
+s1
+NULL
+NULL
+NULL
+
+
+
+
+
+a
+and
+SELECT * FROM t1 ORDER BY s1 LIMIT 11;
+s1
+NULL
+NULL
+NULL
+
+
+
+
+
+a
+and
+is
+SELECT * FROM t1 ORDER BY s1 LIMIT 12;
+s1
+NULL
+NULL
+NULL
+
+
+
+
+
+a
+and
+is
+of
+SELECT * FROM t1 ORDER BY s1 LIMIT 13;
+s1
+NULL
+NULL
+NULL
+
+
+
+
+
+a
+and
+is
+of
+test
+SELECT * FROM t1 ORDER BY s1 LIMIT 14;
+s1
+NULL
+NULL
+NULL
+
+
+
+
+
+a
+and
+is
+of
+test
+This
+DROP TABLE t1;
+CREATE TABLE t1 (s1 varchar(10), key(s1));
+INSERT INTO t1 values (""),(NULL),(""),(""),(NULL), (NULL);
+SELECT * FROM t1 ORDER BY s1;
+s1
+NULL
+NULL
+NULL
+
+
+
+SELECT * FROM t1 ORDER BY s1 LIMIT 1;
+s1
+NULL
+SELECT * FROM t1 ORDER BY s1 LIMIT 2;
+s1
+NULL
+NULL
+SELECT * FROM t1 ORDER BY s1 LIMIT 3;
+s1
+NULL
+NULL
+NULL
+SELECT * FROM t1 ORDER BY s1 LIMIT 4;
+s1
+NULL
+NULL
+NULL
+
+SELECT * FROM t1 ORDER BY s1 LIMIT 5;
+s1
+NULL
+NULL
+NULL
+
+
+SELECT * FROM t1 ORDER BY s1 LIMIT 6;
+s1
+NULL
+NULL
+NULL
+
+
+
+DROP TABLE t1;
+CREATE TABLE t1 (s1 varchar(10), key(s1));
+INSERT INTO t1 values ("");
+SELECT * FROM t1 ORDER BY s1;
+s1
+
+SELECT * FROM t1 ORDER BY s1 LIMIT 1;
+s1
+
+DROP TABLE t1;
+CREATE TABLE t1 (s1 varchar(10), key(s1));
+INSERT INTO t1 values (NULL);
+SELECT * FROM t1 ORDER BY s1;
+s1
+NULL
+SELECT * FROM t1 ORDER BY s1 LIMIT 1;
+s1
+NULL
+DROP TABLE t1;
+CREATE TABLE t1 (s1 varchar(10), key(s1));
+INSERT INTO t1 values (""), (NULL);
+SELECT * FROM t1 ORDER BY s1;
+s1
+NULL
+
+SELECT * FROM t1 ORDER BY s1 LIMIT 1;
+s1
+NULL
+SELECT * FROM t1 ORDER BY s1 LIMIT 2;
+s1
+NULL
+
+DROP TABLE t1;
+SET @@autocommit = 0;
+CREATE TABLE t1 (s1 varchar(10), key(s1));
+INSERT INTO t1 values ("This"), (""), ("is"), (NULL), ("a"), (NULL), (""), (""), ("test"), (""), ("of"), (NULL), ("and"), ("");
+SELECT * FROM t1 ORDER BY s1;
+s1
+NULL
+NULL
+NULL
+
+
+
+
+
+a
+and
+is
+of
+test
+This
+SELECT * FROM t1 ORDER BY s1 LIMIT 1;
+s1
+NULL
+SELECT * FROM t1 ORDER BY s1 LIMIT 2;
+s1
+NULL
+NULL
+SELECT * FROM t1 ORDER BY s1 LIMIT 3;
+s1
+NULL
+NULL
+NULL
+SELECT * FROM t1 ORDER BY s1 LIMIT 4;
+s1
+NULL
+NULL
+NULL
+
+SELECT * FROM t1 ORDER BY s1 LIMIT 5;
+s1
+NULL
+NULL
+NULL
+
+
+SELECT * FROM t1 ORDER BY s1 LIMIT 6;
+s1
+NULL
+NULL
+NULL
+
+
+
+SELECT * FROM t1 ORDER BY s1 LIMIT 7;
+s1
+NULL
+NULL
+NULL
+
+
+
+
+SELECT * FROM t1 ORDER BY s1 LIMIT 8;
+s1
+NULL
+NULL
+NULL
+
+
+
+
+
+SELECT * FROM t1 ORDER BY s1 LIMIT 9;
+s1
+NULL
+NULL
+NULL
+
+
+
+
+
+a
+SELECT * FROM t1 ORDER BY s1 LIMIT 10;
+s1
+NULL
+NULL
+NULL
+
+
+
+
+
+a
+and
+SELECT * FROM t1 ORDER BY s1 LIMIT 11;
+s1
+NULL
+NULL
+NULL
+
+
+
+
+
+a
+and
+is
+SELECT * FROM t1 ORDER BY s1 LIMIT 12;
+s1
+NULL
+NULL
+NULL
+
+
+
+
+
+a
+and
+is
+of
+SELECT * FROM t1 ORDER BY s1 LIMIT 13;
+s1
+NULL
+NULL
+NULL
+
+
+
+
+
+a
+and
+is
+of
+test
+SELECT * FROM t1 ORDER BY s1 LIMIT 14;
+s1
+NULL
+NULL
+NULL
+
+
+
+
+
+a
+and
+is
+of
+test
+This
+DROP TABLE t1;
+COMMIT;
+CREATE TABLE t1 (s1 varchar(10), key(s1));
+INSERT INTO t1 values (""),(NULL),(""),(""),(NULL), (NULL);
+SELECT * FROM t1 ORDER BY s1;
+s1
+NULL
+NULL
+NULL
+
+
+
+SELECT * FROM t1 ORDER BY s1 LIMIT 1;
+s1
+NULL
+SELECT * FROM t1 ORDER BY s1 LIMIT 2;
+s1
+NULL
+NULL
+SELECT * FROM t1 ORDER BY s1 LIMIT 3;
+s1
+NULL
+NULL
+NULL
+SELECT * FROM t1 ORDER BY s1 LIMIT 4;
+s1
+NULL
+NULL
+NULL
+
+SELECT * FROM t1 ORDER BY s1 LIMIT 5;
+s1
+NULL
+NULL
+NULL
+
+
+SELECT * FROM t1 ORDER BY s1 LIMIT 6;
+s1
+NULL
+NULL
+NULL
+
+
+
+DROP TABLE t1;
+COMMIT;
+CREATE TABLE t1 (s1 varchar(10), key(s1));
+INSERT INTO t1 values ("");
+SELECT * FROM t1 ORDER BY s1;
+s1
+
+SELECT * FROM t1 ORDER BY s1 LIMIT 1;
+s1
+
+DROP TABLE t1;
+COMMIT;
+CREATE TABLE t1 (s1 varchar(10), key(s1));
+INSERT INTO t1 values (NULL);
+SELECT * FROM t1 ORDER BY s1;
+s1
+NULL
+SELECT * FROM t1 ORDER BY s1 LIMIT 1;
+s1
+NULL
+DROP TABLE t1;
+COMMIT;
+CREATE TABLE t1 (s1 varchar(10), key(s1));
+INSERT INTO t1 values (""), (NULL);
+SELECT * FROM t1 ORDER BY s1;
+s1
+NULL
+
+SELECT * FROM t1 ORDER BY s1 LIMIT 1;
+s1
+NULL
+SELECT * FROM t1 ORDER BY s1 LIMIT 2;
+s1
+NULL
+
+COMMIT;
+SET @@autocommit = 1;
+SELECT count(*) FROM t1;
+count(*)
+2
+DROP TABLE t1;
=== added file 'mysql-test/suite/falcon/r/falcon_bug_42405.result'
--- a/mysql-test/suite/falcon/r/falcon_bug_42405.result 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/falcon/r/falcon_bug_42405.result 2009-04-05 22:45:06 +0000
@@ -0,0 +1,34 @@
+*** Bug #42405 ***
+SET @@storage_engine = 'Falcon';
+DROP TABLE IF EXISTS t2;
+CREATE TABLE t2(c1 TINYINT UNSIGNED NOT NULL,
+c2 TINYINT NULL,
+c3 SMALLINT,
+c4 MEDIUMINT,
+c5 INT,
+c6 INTEGER,
+c7 BIGINT,
+PRIMARY KEY(c1,c6));
+INSERT INTO t2 VALUES
+(101,102,103,104,105,106,107),
+(108,109,110,111,112,113,114),
+(115,116,117,118,119,120,121),
+(122,123,124,125,126,127,128);
+SELECT * FROM t2 WHERE c1 <> 108 ORDER BY c1,c6 LIMIT 2;
+c1 c2 c3 c4 c5 c6 c7
+101 102 103 104 105 106 107
+115 116 117 118 119 120 121
+SELECT * FROM t2 WHERE c1 <> 115 ORDER BY c1,c6 LIMIT 2;
+c1 c2 c3 c4 c5 c6 c7
+101 102 103 104 105 106 107
+108 109 110 111 112 113 114
+SELECT * FROM t2 WHERE c1 IN (101,115) ORDER BY c1,c6 LIMIT 2;
+c1 c2 c3 c4 c5 c6 c7
+101 102 103 104 105 106 107
+115 116 117 118 119 120 121
+SELECT * FROM t2 WHERE c1 IN (101, 115, 122) ORDER BY c1,c6 LIMIT 5;
+c1 c2 c3 c4 c5 c6 c7
+101 102 103 104 105 106 107
+115 116 117 118 119 120 121
+122 123 124 125 126 127 128
+DROP TABLE t2;
=== added file 'mysql-test/suite/falcon/r/falcon_index_v2.result'
--- a/mysql-test/suite/falcon/r/falcon_index_v2.result 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/falcon/r/falcon_index_v2.result 2009-04-05 22:45:06 +0000
@@ -0,0 +1,45 @@
+*** This is the test for known bugs in index version1 ***
+SET @@storage_engine = 'Falcon';
+DROP TABLE IF EXISTS t1;
+CREATE TABLE t1 (v varbinary(10), comment varchar(30), key(v));
+INSERT INTO t1 values ('A','A'),(0x00,'single byte zero'),('','zero length value'),(0x01, 'single byte one'),(NULL, 'SQL null');
+SELECT hex(v), comment FROM t1 FORCE INDEX FOR ORDER BY(v) ORDER BY v LIMIT 5 ;
+hex(v) comment
+NULL SQL null
+ zero length value
+00 single byte zero
+01 single byte one
+41 A
+SELECT hex(v), comment FROM t1 IGNORE INDEX FOR ORDER BY(v) ORDER BY v LIMIT 5 ;
+hex(v) comment
+NULL SQL null
+ zero length value
+00 single byte zero
+01 single byte one
+41 A
+CREATE TABLE t2(v1 varbinary(5), v2 varbinary(5), comment varchar(30));
+CREATE INDEX i2 on t2(v1,v2);
+INSERT INTO t2 values (0x00000000,0x00000000, "4 zero bytes");
+INSERT INTO t2 values (0x000000,0x000000, "3 zero bytes");
+INSERT INTO t2 values (0x0000,0x00000, "2 zero bytes");
+INSERT INTO t2 values (0x00,0x00, "1 zero byte");
+INSERT INTO t2 values ('','', "zero length value");
+INSERT INTO t2 values (NULL,NULL, "SQL NULL");
+SELECT hex(v1), comment FROM t2 FORCE INDEX(i2) ORDER BY v1,v2 LIMIT 6;
+hex(v1) comment
+NULL SQL NULL
+ zero length value
+00 1 zero byte
+0000 2 zero bytes
+000000 3 zero bytes
+00000000 4 zero bytes
+SELECT hex(v1), comment FROM t2 IGNORE INDEX(i2) ORDER BY v1,v2 LIMIT 6;
+hex(v1) comment
+NULL SQL NULL
+ zero length value
+00 1 zero byte
+0000 2 zero bytes
+000000 3 zero bytes
+00000000 4 zero bytes
+DROP TABLE t1;
+DROP TABLE t2;
=== added file 'mysql-test/suite/falcon/t/falcon_bug_42208.test'
--- a/mysql-test/suite/falcon/t/falcon_bug_42208.test 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/falcon/t/falcon_bug_42208.test 2009-04-05 22:45:06 +0000
@@ -0,0 +1,162 @@
+--source include/have_falcon.inc
+
+#
+# Bug #42208: Falcon's ORDER BY ..LIMIT gives wrong/inconsistent results on NULL values
+#
+--echo *** Bug #42208 ***
+
+# ----------------------------------------------------- #
+# --- Initialisation --- #
+# ----------------------------------------------------- #
+let $engine = 'Falcon';
+eval SET @@storage_engine = $engine;
+
+--disable_warnings
+DROP TABLE IF EXISTS t1;
+--enable_warnings
+
+# ----------------------------------------------------- #
+# --- Test --- #
+# ----------------------------------------------------- #
+
+# Testing the case from the bug report
+
+CREATE TABLE t1(c1 tinyint unsigned null, c2 tinyint, c3 bigint, index idx(c2,c3));
+INSERT INTO t1 (c1,c2,c3) values (0,NULL,26),(0,NULL,106),(0,-128,11);
+INSERT INTO t1 (c1,c2,c3) values (0,0,26),(0,1,2),(0,126,26);
+SELECT * FROM t1 ORDER BY c2,c3;
+SELECT * FROM t1 ORDER BY c2,c3 LIMIT 1;
+SELECT * FROM t1 ORDER BY c2,c3 LIMIT 2;
+SELECT * FROM t1 ORDER BY c2,c3 LIMIT 3;
+SELECT * FROM t1 ORDER BY c2,c3 LIMIT 4;
+SELECT * FROM t1 ORDER BY c2,c3 LIMIT 5;
+SELECT * FROM t1 ORDER BY c2,c3 LIMIT 6;
+DROP TABLE t1;
+
+# Testing some other interesting cases as too, that will
+# be affected by the patch
+
+# Testing single field indexes to see that empty string and NULL sort correctly
+
+CREATE TABLE t1 (s1 varchar(10), key(s1));
+INSERT INTO t1 values ("This"), (""), ("is"), (NULL), ("a"), (NULL), (""), (""), ("test"), (""), ("of"), (NULL), ("and"), ("");
+SELECT * FROM t1 ORDER BY s1;
+SELECT * FROM t1 ORDER BY s1 LIMIT 1;
+SELECT * FROM t1 ORDER BY s1 LIMIT 2;
+SELECT * FROM t1 ORDER BY s1 LIMIT 3;
+SELECT * FROM t1 ORDER BY s1 LIMIT 4;
+SELECT * FROM t1 ORDER BY s1 LIMIT 5;
+SELECT * FROM t1 ORDER BY s1 LIMIT 6;
+SELECT * FROM t1 ORDER BY s1 LIMIT 7;
+SELECT * FROM t1 ORDER BY s1 LIMIT 8;
+SELECT * FROM t1 ORDER BY s1 LIMIT 9;
+SELECT * FROM t1 ORDER BY s1 LIMIT 10;
+SELECT * FROM t1 ORDER BY s1 LIMIT 11;
+SELECT * FROM t1 ORDER BY s1 LIMIT 12;
+SELECT * FROM t1 ORDER BY s1 LIMIT 13;
+SELECT * FROM t1 ORDER BY s1 LIMIT 14;
+DROP TABLE t1;
+
+# Testing special cases with only NULLs and empty strings
+
+CREATE TABLE t1 (s1 varchar(10), key(s1));
+INSERT INTO t1 values (""),(NULL),(""),(""),(NULL), (NULL);
+SELECT * FROM t1 ORDER BY s1;
+SELECT * FROM t1 ORDER BY s1 LIMIT 1;
+SELECT * FROM t1 ORDER BY s1 LIMIT 2;
+SELECT * FROM t1 ORDER BY s1 LIMIT 3;
+SELECT * FROM t1 ORDER BY s1 LIMIT 4;
+SELECT * FROM t1 ORDER BY s1 LIMIT 5;
+SELECT * FROM t1 ORDER BY s1 LIMIT 6;
+DROP TABLE t1;
+
+CREATE TABLE t1 (s1 varchar(10), key(s1));
+INSERT INTO t1 values ("");
+SELECT * FROM t1 ORDER BY s1;
+SELECT * FROM t1 ORDER BY s1 LIMIT 1;
+DROP TABLE t1;
+
+CREATE TABLE t1 (s1 varchar(10), key(s1));
+INSERT INTO t1 values (NULL);
+SELECT * FROM t1 ORDER BY s1;
+SELECT * FROM t1 ORDER BY s1 LIMIT 1;
+DROP TABLE t1;
+
+CREATE TABLE t1 (s1 varchar(10), key(s1));
+INSERT INTO t1 values (""), (NULL);
+SELECT * FROM t1 ORDER BY s1;
+SELECT * FROM t1 ORDER BY s1 LIMIT 1;
+SELECT * FROM t1 ORDER BY s1 LIMIT 2;
+DROP TABLE t1;
+
+# Then we are testing the for single field index with
+# autocommit of, to test the access through the deferred
+# indexes
+
+SET @@autocommit = 0;
+CREATE TABLE t1 (s1 varchar(10), key(s1));
+INSERT INTO t1 values ("This"), (""), ("is"), (NULL), ("a"), (NULL), (""), (""), ("test"), (""), ("of"), (NULL), ("and"), ("");
+SELECT * FROM t1 ORDER BY s1;
+SELECT * FROM t1 ORDER BY s1 LIMIT 1;
+SELECT * FROM t1 ORDER BY s1 LIMIT 2;
+SELECT * FROM t1 ORDER BY s1 LIMIT 3;
+SELECT * FROM t1 ORDER BY s1 LIMIT 4;
+SELECT * FROM t1 ORDER BY s1 LIMIT 5;
+SELECT * FROM t1 ORDER BY s1 LIMIT 6;
+SELECT * FROM t1 ORDER BY s1 LIMIT 7;
+SELECT * FROM t1 ORDER BY s1 LIMIT 8;
+SELECT * FROM t1 ORDER BY s1 LIMIT 9;
+SELECT * FROM t1 ORDER BY s1 LIMIT 10;
+SELECT * FROM t1 ORDER BY s1 LIMIT 11;
+SELECT * FROM t1 ORDER BY s1 LIMIT 12;
+SELECT * FROM t1 ORDER BY s1 LIMIT 13;
+SELECT * FROM t1 ORDER BY s1 LIMIT 14;
+DROP TABLE t1;
+COMMIT;
+
+# Testing special cases with only NULLs and empty strings
+
+CREATE TABLE t1 (s1 varchar(10), key(s1));
+INSERT INTO t1 values (""),(NULL),(""),(""),(NULL), (NULL);
+SELECT * FROM t1 ORDER BY s1;
+SELECT * FROM t1 ORDER BY s1 LIMIT 1;
+SELECT * FROM t1 ORDER BY s1 LIMIT 2;
+SELECT * FROM t1 ORDER BY s1 LIMIT 3;
+SELECT * FROM t1 ORDER BY s1 LIMIT 4;
+SELECT * FROM t1 ORDER BY s1 LIMIT 5;
+SELECT * FROM t1 ORDER BY s1 LIMIT 6;
+DROP TABLE t1;
+COMMIT;
+
+CREATE TABLE t1 (s1 varchar(10), key(s1));
+INSERT INTO t1 values ("");
+SELECT * FROM t1 ORDER BY s1;
+SELECT * FROM t1 ORDER BY s1 LIMIT 1;
+DROP TABLE t1;
+COMMIT;
+
+CREATE TABLE t1 (s1 varchar(10), key(s1));
+INSERT INTO t1 values (NULL);
+SELECT * FROM t1 ORDER BY s1;
+SELECT * FROM t1 ORDER BY s1 LIMIT 1;
+DROP TABLE t1;
+COMMIT;
+
+CREATE TABLE t1 (s1 varchar(10), key(s1));
+INSERT INTO t1 values (""), (NULL);
+SELECT * FROM t1 ORDER BY s1;
+SELECT * FROM t1 ORDER BY s1 LIMIT 1;
+SELECT * FROM t1 ORDER BY s1 LIMIT 2;
+COMMIT;
+
+SET @@autocommit = 1;
+
+# ----------------------------------------------------- #
+# --- Check --- #
+# ----------------------------------------------------- #
+SELECT count(*) FROM t1;
+
+# ----------------------------------------------------- #
+# --- Final cleanup --- #
+# ----------------------------------------------------- #
+DROP TABLE t1;
=== added file 'mysql-test/suite/falcon/t/falcon_bug_42405.test'
--- a/mysql-test/suite/falcon/t/falcon_bug_42405.test 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/falcon/t/falcon_bug_42405.test 2009-04-05 22:45:06 +0000
@@ -0,0 +1,46 @@
+--source include/have_falcon.inc
+
+#
+# Bug #42405: Missing/Wrong rows on Falcon's ORDER BY ..LIMIT with range access on pk/index
+#
+--echo *** Bug #42405 ***
+
+# ----------------------------------------------------- #
+# --- Initialisation --- #
+# ----------------------------------------------------- #
+let $engine = 'Falcon';
+eval SET @@storage_engine = $engine;
+
+--disable_warnings
+DROP TABLE IF EXISTS t2;
+--enable_warnings
+
+CREATE TABLE t2(c1 TINYINT UNSIGNED NOT NULL,
+c2 TINYINT NULL,
+c3 SMALLINT,
+c4 MEDIUMINT,
+c5 INT,
+c6 INTEGER,
+c7 BIGINT,
+PRIMARY KEY(c1,c6));
+
+INSERT INTO t2 VALUES
+(101,102,103,104,105,106,107),
+(108,109,110,111,112,113,114),
+(115,116,117,118,119,120,121),
+(122,123,124,125,126,127,128);
+
+
+SELECT * FROM t2 WHERE c1 <> 108 ORDER BY c1,c6 LIMIT 2;
+
+SELECT * FROM t2 WHERE c1 <> 115 ORDER BY c1,c6 LIMIT 2;
+
+SELECT * FROM t2 WHERE c1 IN (101,115) ORDER BY c1,c6 LIMIT 2;
+
+SELECT * FROM t2 WHERE c1 IN (101, 115, 122) ORDER BY c1,c6 LIMIT 5;
+
+
+# ----------------------------------------------------- #
+# --- Final cleanup --- #
+# ----------------------------------------------------- #
+DROP TABLE t2;
=== added file 'mysql-test/suite/falcon/t/falcon_index_v2.test'
--- a/mysql-test/suite/falcon/t/falcon_index_v2.test 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/falcon/t/falcon_index_v2.test 2009-04-05 22:45:06 +0000
@@ -0,0 +1,51 @@
+--source include/have_falcon.inc
+
+--echo *** This is the test for known bugs in index version1 ***
+
+# Known bugs are
+# 1) In multisegment index, trailing zero bytes are sorted the same
+# 2) No differentiation between NULL and zero length values
+# 3) NULL is not the smallest value maybe > zero length value
+#
+# We use queries with "limit", as at the moment it is the only
+# way to check if values are stored in the correct order.
+
+# ----------------------------------------------------- #
+# --- Initialisation --- #
+# ----------------------------------------------------- #
+let $engine = 'Falcon';
+eval SET @@storage_engine = $engine;
+
+--disable_warnings
+DROP TABLE IF EXISTS t1;
+--enable_warnings
+
+# ----------------------------------------------------- #
+# --- Test --- #
+# ----------------------------------------------------- #
+
+# Test order of NULL vs empty key vs 0x00
+CREATE TABLE t1 (v varbinary(10), comment varchar(30), key(v));
+INSERT INTO t1 values ('A','A'),(0x00,'single byte zero'),('','zero length value'),(0x01, 'single byte one'),(NULL, 'SQL null');
+
+SELECT hex(v), comment FROM t1 FORCE INDEX FOR ORDER BY(v) ORDER BY v LIMIT 5 ;
+SELECT hex(v), comment FROM t1 IGNORE INDEX FOR ORDER BY(v) ORDER BY v LIMIT 5 ;
+
+# Test multisegment indexes with trailing zero bytes
+CREATE TABLE t2(v1 varbinary(5), v2 varbinary(5), comment varchar(30));
+CREATE INDEX i2 on t2(v1,v2);
+INSERT INTO t2 values (0x00000000,0x00000000, "4 zero bytes");
+INSERT INTO t2 values (0x000000,0x000000, "3 zero bytes");
+INSERT INTO t2 values (0x0000,0x00000, "2 zero bytes");
+INSERT INTO t2 values (0x00,0x00, "1 zero byte");
+INSERT INTO t2 values ('','', "zero length value");
+INSERT INTO t2 values (NULL,NULL, "SQL NULL");
+
+SELECT hex(v1), comment FROM t2 FORCE INDEX(i2) ORDER BY v1,v2 LIMIT 6;
+SELECT hex(v1), comment FROM t2 IGNORE INDEX(i2) ORDER BY v1,v2 LIMIT 6;
+
+# ----------------------------------------------------- #
+# --- Final cleanup --- #
+# ----------------------------------------------------- #
+DROP TABLE t1;
+DROP TABLE t2;
=== modified file 'storage/falcon/Database.h'
--- a/storage/falcon/Database.h 2009-04-02 06:26:04 +0000
+++ b/storage/falcon/Database.h 2009-04-05 22:45:06 +0000
@@ -35,6 +35,7 @@
#define ODS_MINOR_VERSION2 2 // Has SequencePages external to the section tree
#define ODS_MINOR_VERSION3 3 // Switch to variable length record numbers in index
#define ODS_MINOR_VERSION4 4 // Accidentially fixed multisegment keys wrt padding
+#define ODS_MINOR_VERSION5 5 // New multisegment index format
#define ODS_MINOR_VERSION ODS_MINOR_VERSION4
#define COMBINED_VERSION(major,minor) (major * 100 + minor)
=== modified file 'storage/falcon/Index.cpp'
--- a/storage/falcon/Index.cpp 2009-04-02 22:36:19 +0000
+++ b/storage/falcon/Index.cpp 2009-04-05 22:45:06 +0000
@@ -296,7 +296,7 @@ void Index::makeKey(Field *field, Value
indexKey->keyLength = 0;
- if (!value)
+ if (!value || (value->getType() == Null && indexVersion >= INDEX_VERSION_2))
return;
switch (field->type)
@@ -340,6 +340,7 @@ void Index::makeKey(Field *field, Value
indexKey->key[klen++] = 0x20;
}
+ indexKey->keyLength = (int) (q - key);
}
}
break;
@@ -356,6 +357,25 @@ void Index::makeKey(Field *field, Value
default:
indexKey->appendNumber(value->getDouble());
}
+
+ if (indexVersion < INDEX_VERSION_2)
+ return;
+
+ // Prepend 0x00 to empty keys, and keys starting with 0x00
+ // This is done to distinguish Null from empty keys, and to
+ // preserve the original sorting sequence
+ if (indexKey->keyLength == 0)
+ {
+ indexKey->key[0] = 0;
+ indexKey->keyLength++;
+ }
+ else if (indexKey->key[0] == 0)
+ {
+ size_t moveLen = MIN(indexKey->keyLength, MAX_PHYSICAL_KEY_LENGTH - 1);
+ memmove (indexKey->key + 1, indexKey->key, moveLen);
+ indexKey->key[0] = 0;
+ indexKey->keyLength = moveLen + 1;
+ }
}
void Index::makeKey(int count, Value **values, IndexKey *indexKey, bool highKey)
@@ -363,26 +383,80 @@ void Index::makeKey(int count, Value **v
if (damaged)
damageCheck();
-// This causes a different keylength than other makeKey()s
-// when the key is null. This section is not needed.
-// if (!count)
-// {
-// indexKey->keyLength = 0;
-//
-// return;
-// }
+ if (indexVersion >= INDEX_VERSION_2)
+ makeMultiSegmentKey(count, values, indexKey, highKey);
+ else
+ makeMultiSegmentKeyV1(count,values, indexKey, highKey);
+}
+
+
+
+void Index::makeMultiSegmentKey(int count, Value **values, IndexKey *indexKey, bool highKey)
+{
+ uint p = 0;
+ int n;
+ UCHAR *key = indexKey->key;
if (numberFields == 1)
{
makeKey(fields[0], values[0], 0, indexKey, highKey);
-
return;
}
+ for (n = 0; (n < count) && values[n]; ++n)
+ {
+ Field *field = fields[n];
+
+ IndexKey tempKey(this);
+ makeKey(field, values[n], n, &tempKey, false);
+ int length = tempKey.keyLength;
+ UCHAR *t = tempKey.key;
+
+ for (int i=0; i< length; i++)
+ {
+ UCHAR c = t[i];
+
+ // Convert 0 to 0x0100, 1 to 0x0101
+ // other bytes remain unchanged.
+
+ if (c <= 1)
+ {
+ key[p++] = 1;
+ checkIndexKeyOverflow(p);
+ }
+
+ key[p++] = c;
+ checkIndexKeyOverflow(p);
+ }
+
+ // Add field separator
+ key[p++] = 0;
+ checkIndexKeyOverflow(p);
+ }
+
+ // Remove trailing nulls
+ while (p > 0 && key[p-1] == 0)
+ p--;
+
+ checkIndexKeyOverflow(p, maxIndexKeyRunLength(maxKeyLength));
+
+ indexKey->keyLength = p;
+}
+
+
+
+void Index::makeMultiSegmentKeyV1(int count, Value **values, IndexKey *indexKey, bool highKey)
+{
uint p = 0;
int n;
UCHAR *key = indexKey->key;
+ if (numberFields == 1)
+ {
+ makeKey(fields[0], values[0], 0, indexKey, highKey );
+ return;
+ }
+
bool ODSVersion23OrOlder =
(COMBINED_VERSION(database->dbb->odsVersion, database->dbb->odsMinorVersion) <=
COMBINED_VERSION(2,3));
@@ -405,9 +479,8 @@ void Index::makeKey(int count, Value **v
if(n < numberFields - 1)
segmentLength = ROUNDUP(segmentLength , RUN);
- if (p + segmentLength > maxIndexKeyRunLength(maxKeyLength))
- throw SQLError (INDEX_OVERFLOW, "maximum index key length exceeded");
-
+ checkIndexKeyOverflow(p + segmentLength, maxIndexKeyRunLength(maxKeyLength));
+
for (int i = 0; i < length; ++i)
{
if (p % RUN == 0)
@@ -432,17 +505,6 @@ void Index::makeKey(int count, Value **v
}
}
- if (n && n < numberFields)
- {
- // We're constructing partial search key, with only some
- // first fields given. Append segment byte for the next
- // segment. This will make key larger and will hopefully
- // reduce the number of false positives in search (saves
- // work in postprocessing).
- if (p < (uint)maxKeyLength)
- key[p++] = SEGMENT_BYTE(n, numberFields);
- }
-
indexKey->keyLength = p;
}
=== modified file 'storage/falcon/Index.h'
--- a/storage/falcon/Index.h 2009-03-18 10:59:45 +0000
+++ b/storage/falcon/Index.h 2009-04-05 22:45:06 +0000
@@ -26,10 +26,13 @@
#include "Types.h"
#include "Queue.h"
+#include "SQLError.h"
+#include "IndexKey.h"
static const int INDEX_VERSION_0 = 0;
static const int INDEX_VERSION_1 = 1;
-static const int INDEX_CURRENT_VERSION = INDEX_VERSION_1;
+static const int INDEX_VERSION_2 = 2;
+static const int INDEX_CURRENT_VERSION = INDEX_VERSION_2;
//#define CHECK_DEFERRED_INDEXES
@@ -109,6 +112,9 @@ public:
void makeKey (Record *record, IndexKey *key);
void makeKey (int count, Value **values, IndexKey *key, bool highKey);
void makeKey (Field *field, Value *value, int segment, IndexKey *key, bool highKey);
+ void makeMultiSegmentKey(int count, Value **values, IndexKey *indexKey, bool highKey);
+ void makeMultiSegmentKeyV1(int count, Value **values, IndexKey *indexKey, bool highKey);
+
void detachDeferredIndex(DeferredIndex *deferredIndex);
UCHAR getPadByte(void);
@@ -156,6 +162,13 @@ public:
SyncObject syncDIHash;
SyncObject syncUnique;
IndexWalker* positionIndex(IndexKey* lowKey, IndexKey* highKey, int searchFlags, Transaction* transaction);
+
+private:
+ static inline void checkIndexKeyOverflow(int len, int maxLen = MAX_PHYSICAL_KEY_LENGTH)
+ {
+ if(len > maxLen)
+ throw SQLError (INDEX_OVERFLOW, "maximum index key length exceeded");
+ }
};
#endif // !defined(AFX_INDEX_H__02AD6A44_A433_11D2_AB5B_0000C01D2301__INCLUDED_)
=== modified file 'storage/falcon/StorageDatabase.cpp'
--- a/storage/falcon/StorageDatabase.cpp 2009-03-28 06:05:52 +0000
+++ b/storage/falcon/StorageDatabase.cpp 2009-04-05 22:45:06 +0000
@@ -878,7 +878,12 @@ int StorageDatabase::makeKey(StorageInde
if (nullFlag)
{
values[segmentNumber]->setNull();
- break;
+ if (index->indexVersion < INDEX_VERSION_2)
+ {
+ // Older index version do not handle NULLs correctly -it is not the smallest value.
+ // Thus, we cannot use values past NULL and need to break here.
+ break;
+ }
}
p += len;
Attachment: [text/bzr-bundle] bzr/vvaintroub@mysql.com-20090405224506-pl57wpxiirxykiw4.bundle
| Thread |
|---|
| • bzr commit into mysql-6.0-falcon-team branch (vvaintroub:3097)Bug#42208 Bug#42405 | Vladislav Vaintroub | 6 Apr |
