List:Commits« Previous MessageNext Message »
From:Vladislav Vaintroub Date:April 6 2009 11:44am
Subject:bzr commit into mysql-6.0-falcon-team branch (vvaintroub:3097)
Bug#42208
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 
      
      
      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#42208Vladislav Vaintroub6 Apr