List:Commits« Previous MessageNext Message »
From:Vladislav Vaintroub Date:April 5 2009 10:45pm
Subject:bzr commit into mysql-6.0-falcon-team branch (vvaintroub:3097)
Bug#42208 Bug#42405
View as plain text  
#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#42405Vladislav Vaintroub6 Apr