#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
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_index_v2.result
mysql-test/suite/falcon/t/falcon_bug_42208.test
mysql-test/suite/falcon/t/falcon_index_v2.test
modified:
mysql-test/suite/falcon/t/falcon_bug_22173a.test
storage/falcon/Database.h
storage/falcon/Index.cpp
storage/falcon/Index.h
storage/falcon/IndexRootPage.cpp
storage/falcon/StorageDatabase.cpp
storage/falcon/Transaction.cpp
storage/falcon/Transaction.h
storage/falcon/TransactionManager.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-06 11:44:49 +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_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-06 11:44:49 +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;
=== modified file 'mysql-test/suite/falcon/t/falcon_bug_22173a.test'
--- a/mysql-test/suite/falcon/t/falcon_bug_22173a.test 2008-12-13 19:55:44 +0000
+++ b/mysql-test/suite/falcon/t/falcon_bug_22173a.test 2009-04-06 11:44:49 +0000
@@ -32,16 +32,13 @@ declare v2 int;
declare continue handler for 1020 begin end;
declare continue handler for 1213 begin end;
declare continue handler for 1015 begin end;
-while v1 < 2500 do
- /* SELECT 'insert', v1; */
+while v1 < 25000 do
INSERT INTO t1 VALUES (v1);
SET v2 = rand() * 10000;
UPDATE t1 SET a = v2 WHERE a = v1;
/* SELECT ' update', v1; */
SET v1 = v1 + 1;
- if v1 mod 50 = 0 then
- /* SELECT ' delete'; */
- /* DELETE FROM t1; */
+ if v1 mod 30 = 0 then
TRUNCATE t1;
end if;
end while;
=== 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-06 11:44:49 +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_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-06 11:44:49 +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-06 11:44:49 +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-06 11:44:49 +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-06 11:44:49 +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/IndexRootPage.cpp'
--- a/storage/falcon/IndexRootPage.cpp 2009-04-02 22:36:19 +0000
+++ b/storage/falcon/IndexRootPage.cpp 2009-04-06 11:44:49 +0000
@@ -332,26 +332,21 @@ Bdb* IndexRootPage::findRoot(Dbb *dbb, i
if (rootPage)
return dbb->fetchPage(rootPage, PAGE_btree, lockType);
- if (indexId < 0)
- return NULL;
+ ASSERT(indexId >= 0);
Bdb *bdb = Section::getSectionPage (dbb, INDEX_ROOT, indexId / dbb->pagesPerSection, Shared, transId);
BDB_HISTORY(bdb);
- if (!bdb)
- return NULL;
+ ASSERT (bdb);
SectionPage *sections = (SectionPage*) bdb->buffer;
int32 pageNumber = sections->pages [indexId % dbb->pagesPerSection];
- if (pageNumber == 0)
- {
- bdb->release(REL_HISTORY);
- return NULL;
- }
+ ASSERT (pageNumber != 0);
bdb = dbb->handoffPage (bdb, pageNumber, PAGE_btree, lockType);
BDB_HISTORY(bdb);
+ ASSERT(bdb);
return bdb;
}
=== 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-06 11:44:49 +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;
=== modified file 'storage/falcon/Transaction.cpp'
--- a/storage/falcon/Transaction.cpp 2009-04-01 19:38:45 +0000
+++ b/storage/falcon/Transaction.cpp 2009-04-06 11:44:49 +0000
@@ -158,6 +158,7 @@ void Transaction::initialize(Connection*
thread = Thread::getThread("Transaction::initialize");
transactionState->syncIsActive.lock(NULL, Exclusive);
transactionState->state = Active;
+ seenGopher=false;
}
Transaction::~Transaction()
@@ -285,7 +286,7 @@ void Transaction::commit()
// Set the commit transition id for this transaction
transactionState->commitId = INTERLOCKED_INCREMENT(transactionManager->transactionSequence);
-
+ ASSERT(writePending);
transactionManager->activeTransactions.remove(this);
transactionManager->committedTransactions.append(this);
transactionState->state = Committed;
@@ -1482,6 +1483,7 @@ void Transaction::fullyCommitted(void)
Log::debug("Transaction::fullyCommitted: Unusual use count=%d\n", useCount);
writeComplete();
+ seenGopher =true;
releaseCommittedTransaction();
}
=== modified file 'storage/falcon/Transaction.h'
--- a/storage/falcon/Transaction.h 2009-04-01 19:38:45 +0000
+++ b/storage/falcon/Transaction.h 2009-04-06 11:44:49 +0000
@@ -140,6 +140,7 @@ public:
bool systemTransaction;
bool hasUpdates;
bool writePending;
+ bool seenGopher;
//bool pendingPageWrites;
bool hasLocks;
SyncObject syncObject;
=== modified file 'storage/falcon/TransactionManager.cpp'
--- a/storage/falcon/TransactionManager.cpp 2009-03-31 09:25:28 +0000
+++ b/storage/falcon/TransactionManager.cpp 2009-04-06 11:44:49 +0000
@@ -189,7 +189,7 @@ void TransactionManager::waitForWriteCom
for (Transaction *trans = committedTransactions.first; trans;
trans = trans->next)
{
- if (trans->hasRecords(table)&& trans->writePending)
+ if (trans->hasRecords(table))
{
again = true;
break;
Attachment: [text/bzr-bundle] bzr/vvaintroub@mysql.com-20090406114449-g655t39h8511iakq.bundle
| Thread |
|---|
| • bzr commit into mysql-6.0-falcon-team branch (vvaintroub:3097)Bug#42208 | Vladislav Vaintroub | 6 Apr |
