bzr commit into mysql-6.0-falcon branch (vvaintroub:2761) Bug#35939

[Vladislav Vaintroub]

#At file:///C:/bzr/mysql-6.0-falcon-team/

 2761 Vladislav Vaintroub	2008-07-31
      Bug#35939 - Drift in Falcon row count reported by SHOW TABLE STATUS
      The problem is that during recalculation of cardinality during commit. 
      If a record was inserted and deleted in the same transaction, it was 
      counted as new and cardinality would be incremented.
            
      This patch addresses this issue. Now table cardinality does not 
      change when such a record is encountered.
added:
  mysql-test/suite/falcon/r/falcon_bug_35939.result
  mysql-test/suite/falcon/t/falcon_bug_35939.test
modified:
  storage/falcon/Transaction.cpp

per-file messages:
  mysql-test/suite/falcon/r/falcon_bug_35939.result
    different test cases for table cardinality calculation
  mysql-test/suite/falcon/t/falcon_bug_35939.test
    different test cases for table cardinality calculation
  storage/falcon/Transaction.cpp
    Ensure that cardinality is not changed if record is inserted 
    and deleted in the same transaction.
=== added file 'mysql-test/suite/falcon/r/falcon_bug_35939.result'
--- a/mysql-test/suite/falcon/r/falcon_bug_35939.result	1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/falcon/r/falcon_bug_35939.result	2008-07-31 10:04:30 +0000
@@ -0,0 +1,61 @@
+*** Bug #35939 ***
+SET @@storage_engine = 'Falcon';
+DROP TABLE IF EXISTS t1;
+CREATE TABLE t1(i int);
+INSERT INTO t1(i) VALUES (1),(2),(3),(4);
+SELECT TABLE_ROWS  FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 't1';
+TABLE_ROWS
+4
+UPDATE t1 set i=i+1;
+SELECT TABLE_ROWS  FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 't1';
+TABLE_ROWS
+4
+DELETE FROM t1 WHERE i=2;
+SELECT TABLE_ROWS  FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 't1';
+TABLE_ROWS
+3
+SET AUTOCOMMIT=OFF;
+BEGIN;
+INSERT INTO t1(i) VALUES(42);
+DELETE FROM t1 WHERE i=42;
+COMMIT;
+SELECT TABLE_ROWS  FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 't1';
+TABLE_ROWS
+3
+BEGIN;
+DELETE FROM t1 WHERE i=4;
+INSERT INTO t1(i) VALUES(4);
+COMMIT;
+SELECT TABLE_ROWS  FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 't1';
+TABLE_ROWS
+3
+BEGIN;
+INSERT INTO t1(i) VALUES(42);
+UPDATE t1 SET i=43 WHERE i=42;
+DELETE FROM t1 WHERE i=43;
+COMMIT;
+SELECT TABLE_ROWS  FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 't1';
+TABLE_ROWS
+3
+BEGIN;
+DELETE FROM t1 WHERE i=4;
+INSERT INTO t1(i) VALUES(4);
+UPDATE t1 SET i=42 WHERE i=4;
+UPDATE t1 SET i=4 WHERE i=42;
+COMMIT;
+SELECT TABLE_ROWS  FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 't1';
+TABLE_ROWS
+3
+BEGIN;
+INSERT INTO t1(i) VALUES(42);
+UPDATE t1 SET i=43 WHERE i=42;
+DELETE FROM t1 WHERE i=43;
+ROLLBACK;
+SELECT TABLE_ROWS  FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 't1';
+TABLE_ROWS
+3
+SET AUTOCOMMIT=ON;
+SELECT count(*) FROM t1;
+count(*)
+3
+DROP TABLE t1;

=== added file 'mysql-test/suite/falcon/t/falcon_bug_35939.test'
--- a/mysql-test/suite/falcon/t/falcon_bug_35939.test	1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/falcon/t/falcon_bug_35939.test	2008-07-31 10:04:30 +0000
@@ -0,0 +1,86 @@
+--source include/have_falcon.inc
+
+#
+# Bug #35939: Drift in Falcon row count reported by SHOW TABLE STATUS
+#
+--echo *** Bug #35939 ***
+
+# ----------------------------------------------------- #
+# --- Initialisation                                --- #
+# ----------------------------------------------------- #
+let $engine = 'Falcon';
+eval SET @@storage_engine = $engine;
+
+--disable_warnings
+DROP TABLE IF EXISTS t1;
+--enable_warnings
+
+# ----------------------------------------------------- #
+# --- Test                                          --- #
+# ----------------------------------------------------- #
+CREATE TABLE t1(i int);
+INSERT INTO t1(i) VALUES (1),(2),(3),(4);
+SELECT TABLE_ROWS  FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 't1';
+# Expect 4 rows
+UPDATE t1 set i=i+1;
+SELECT TABLE_ROWS  FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 't1';
+# Expect 4 rows, nothing added or deleted
+DELETE FROM t1 WHERE i=2;
+# One row deleted, expect count=3
+SELECT TABLE_ROWS  FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 't1';
+
+# insert/delete on the same row, count does not change
+SET AUTOCOMMIT=OFF;
+# Next tests are transactional
+BEGIN;
+INSERT INTO t1(i) VALUES(42);
+DELETE FROM t1 WHERE i=42;
+COMMIT;
+SELECT TABLE_ROWS  FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 't1';
+# Count should not be changed,count=3
+
+#delete/insert, count does not change
+BEGIN;
+DELETE FROM t1 WHERE i=4;
+INSERT INTO t1(i) VALUES(4);
+COMMIT;
+SELECT TABLE_ROWS  FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 't1';
+# Count should not be changed,count=3
+
+# insert/update/delete on the same row, count does not change
+BEGIN;
+INSERT INTO t1(i) VALUES(42);
+UPDATE t1 SET i=43 WHERE i=42;
+DELETE FROM t1 WHERE i=43;
+COMMIT;
+SELECT TABLE_ROWS  FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 't1';
+# Count should not be changed,count=3
+
+#delete/insert/update/update, count does not change
+BEGIN;
+DELETE FROM t1 WHERE i=4;
+INSERT INTO t1(i) VALUES(4);
+UPDATE t1 SET i=42 WHERE i=4;
+UPDATE t1 SET i=4 WHERE i=42;
+COMMIT;
+SELECT TABLE_ROWS  FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 't1';
+# Count should not be changed,count=3
+
+# same as before, but with rollback
+BEGIN;
+INSERT INTO t1(i) VALUES(42);
+UPDATE t1 SET i=43 WHERE i=42;
+DELETE FROM t1 WHERE i=43;
+ROLLBACK;
+SELECT TABLE_ROWS  FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 't1';
+SET AUTOCOMMIT=ON;
+
+# ----------------------------------------------------- #
+# --- Check                                         --- #
+# ----------------------------------------------------- #
+SELECT count(*) FROM t1;
+
+# ----------------------------------------------------- #
+# --- Final cleanup                                 --- #
+# ----------------------------------------------------- #
+DROP TABLE t1;

=== modified file 'storage/falcon/Transaction.cpp'
--- a/storage/falcon/Transaction.cpp	2008-07-24 08:45:03 +0000
+++ b/storage/falcon/Transaction.cpp	2008-07-31 10:04:30 +0000
@@ -299,10 +299,12 @@ void Transaction::commit()
 	syncActiveTransactions.unlock();
 	
 	for (RecordVersion *record = firstRecord; record; record = record->nextInTrans)
+		{
 		if (!record->getPriorVersion())
 			++record->format->table->cardinality;
-		else if (record->state == recDeleted &&
record->format->table->cardinality > 0)
+		if (record->state == recDeleted &&
record->format->table->cardinality > 0)
 			--record->format->table->cardinality;
+		}
 	transactionManager->committedTransactions.append(this);
 	syncCommitted.unlock();
 	database->commit(this);