4637 Jonas Oreland 2011-11-07 [merge]
ndb - merge 63 to 70
added:
mysql-test/suite/ndb/r/ndb_multi_update_delete.result
mysql-test/suite/ndb/t/ndb_multi_update_delete.test
modified:
sql/sql_select.cc
4636 Martin Skold 2011-11-07
Bug#11765142 58073: CONCAT AND OR GIVE INCORRECT QUERY RESULTS: Fixed typo
modified:
sql/ha_ndbcluster_cond.cc
=== added file 'mysql-test/suite/ndb/r/ndb_multi_update_delete.result'
--- a/mysql-test/suite/ndb/r/ndb_multi_update_delete.result 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/ndb/r/ndb_multi_update_delete.result 2011-11-07 14:23:36 +0000
@@ -0,0 +1,68 @@
+
+# Bug#12728221 - MULTI-TABLE DELETE ON UNIQUE INDEX REMOVES WRONG ROWS IN CLUSTER
+
+CREATE TABLE t1 (
+a int(11) NOT NULL,
+b int(11) DEFAULT NULL,
+c varchar(64) DEFAULT NULL,
+PRIMARY KEY (a),
+UNIQUE KEY c (c),
+KEY b (b)
+) ENGINE=ndbcluster DEFAULT CHARSET=latin1;
+CREATE TABLE t2 (
+a int(11) NOT NULL,
+b text,
+PRIMARY KEY (a)
+) ENGINE=ndbcluster DEFAULT CHARSET=latin1;
+INSERT INTO t1 VALUES (1,3,'test3'),(2,2,'test2'),(3,1,'test1');
+INSERT INTO t2 VALUES (1,''),(2,''),(3,'');
+delete t1,t2 from t1,t2 where t1.b=t2.a and t1.c='test1';
+delete t1,t2 from t1,t2 where t1.b=t2.a and t1.c='test2';
+delete t1,t2 from t1,t2 where t1.b=t2.a and t1.c='test3';
+select * from t1,t2;
+a b c a b
+drop table t1,t2;
+
+# Bug#12718336 - 61705: TRIGGER WORKS IMPROPERLY IN MYSQL CLUSTER.
+
+CREATE TABLE t1 (a int(11) NOT NULL, b int(11) DEFAULT NULL, c
+varchar(64) DEFAULT NULL, d char(10), j int, PRIMARY KEY (a),
+UNIQUE KEY c (c), KEY b (b)) ENGINE=ndbcluster DEFAULT
+CHARSET=latin1;
+CREATE TABLE t2 ( e int(11) NOT NULL, f char(10), PRIMARY KEY
+(e)) ENGINE=ndbcluster DEFAULT CHARSET=latin1;
+CREATE TABLE t3 (t3_id int(11) NOT NULL AUTO_INCREMENT,g int(11)
+DEFAULT NULL, h char(10), PRIMARY KEY (t3_id)) ENGINE=ndbcluster
+DEFAULT CHARSET=latin1;
+CREATE TRIGGER trg1 AFTER UPDATE ON t1 FOR EACH ROW INSERT INTO
+t3(g,h) values (old.b, old.d);
+INSERT INTO t1 VALUES (11,1,'test1', 'hei1',111),(22,2,'test2',
+'hei2',222),(33,3,'test3', 'hei3',333);
+INSERT INTO t2 VALUES (1,'xx'),(2,'yy'),(3,'zz');
+select * from t1;
+a b c d j
+11 1 test1 hei1 111
+22 2 test2 hei2 222
+33 3 test3 hei3 333
+select * from t2;
+e f
+1 xx
+2 yy
+3 zz
+select * from t3;
+t3_id g h
+update t1,t2 set t1.d=t2.f where t1.b=t2.e and t1.c='test1';
+select * from t1;
+a b c d j
+11 1 test1 xx 111
+22 2 test2 hei2 222
+33 3 test3 hei3 333
+select * from t2;
+e f
+1 xx
+2 yy
+3 zz
+select * from t3;
+t3_id g h
+1 1 hei1
+drop table t1,t2,t3;
=== added file 'mysql-test/suite/ndb/t/ndb_multi_update_delete.test'
--- a/mysql-test/suite/ndb/t/ndb_multi_update_delete.test 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/ndb/t/ndb_multi_update_delete.test 2011-11-07 14:23:36 +0000
@@ -0,0 +1,64 @@
+-- source include/have_ndb.inc
+
+--echo
+--echo # Bug#12728221 - MULTI-TABLE DELETE ON UNIQUE INDEX REMOVES WRONG ROWS IN CLUSTER
+--echo
+
+CREATE TABLE t1 (
+ a int(11) NOT NULL,
+ b int(11) DEFAULT NULL,
+ c varchar(64) DEFAULT NULL,
+ PRIMARY KEY (a),
+ UNIQUE KEY c (c),
+ KEY b (b)
+) ENGINE=ndbcluster DEFAULT CHARSET=latin1;
+
+CREATE TABLE t2 (
+ a int(11) NOT NULL,
+ b text,
+ PRIMARY KEY (a)
+) ENGINE=ndbcluster DEFAULT CHARSET=latin1;
+
+INSERT INTO t1 VALUES (1,3,'test3'),(2,2,'test2'),(3,1,'test1');
+INSERT INTO t2 VALUES (1,''),(2,''),(3,'');
+
+delete t1,t2 from t1,t2 where t1.b=t2.a and t1.c='test1';
+delete t1,t2 from t1,t2 where t1.b=t2.a and t1.c='test2';
+delete t1,t2 from t1,t2 where t1.b=t2.a and t1.c='test3';
+select * from t1,t2;
+drop table t1,t2;
+
+
+--echo
+--echo # Bug#12718336 - 61705: TRIGGER WORKS IMPROPERLY IN MYSQL CLUSTER.
+--echo
+
+CREATE TABLE t1 (a int(11) NOT NULL, b int(11) DEFAULT NULL, c
+varchar(64) DEFAULT NULL, d char(10), j int, PRIMARY KEY (a),
+UNIQUE KEY c (c), KEY b (b)) ENGINE=ndbcluster DEFAULT
+CHARSET=latin1;
+CREATE TABLE t2 ( e int(11) NOT NULL, f char(10), PRIMARY KEY
+(e)) ENGINE=ndbcluster DEFAULT CHARSET=latin1;
+CREATE TABLE t3 (t3_id int(11) NOT NULL AUTO_INCREMENT,g int(11)
+DEFAULT NULL, h char(10), PRIMARY KEY (t3_id)) ENGINE=ndbcluster
+DEFAULT CHARSET=latin1;
+CREATE TRIGGER trg1 AFTER UPDATE ON t1 FOR EACH ROW INSERT INTO
+t3(g,h) values (old.b, old.d);
+INSERT INTO t1 VALUES (11,1,'test1', 'hei1',111),(22,2,'test2',
+'hei2',222),(33,3,'test3', 'hei3',333);
+INSERT INTO t2 VALUES (1,'xx'),(2,'yy'),(3,'zz');
+--sorted_result
+select * from t1;
+--sorted_result
+select * from t2;
+--sorted_result
+select * from t3;
+update t1,t2 set t1.d=t2.f where t1.b=t2.e and t1.c='test1';
+--sorted_result
+select * from t1;
+--sorted_result
+select * from t2;
+--sorted_result
+select * from t3;
+
+drop table t1,t2,t3;
=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc 2011-08-16 10:20:19 +0000
+++ b/sql/sql_select.cc 2011-11-07 14:46:47 +0000
@@ -12013,6 +12013,36 @@ join_read_const_table(JOIN_TAB *tab, POS
table->const_table=1;
table->null_row=0;
table->status=STATUS_NO_RECORD;
+
+#ifndef MCP_BUG_12728221
+ MY_BITMAP * const save_read_set= table->read_set;
+ if (table->reginfo.lock_type >= TL_WRITE_ALLOW_WRITE)
+ {
+ const enum_sql_command sql_command= tab->join->thd->lex->sql_command;
+ if (sql_command == SQLCOM_UPDATE_MULTI ||
+ sql_command == SQLCOM_DELETE_MULTI)
+ {
+ /*
+ In a multi-UPDATE, if we represent "depends on" with "->", we have:
+ "what columns to read (read_set)" ->
+ "whether table will be updated on-the-fly or with tmp table" ->
+ "whether to-be-updated columns are used by access path"
+ "access path to table (range, ref, scan...)" ->
+ "query execution plan" ->
+ "what tables are const" ->
+ "reading const tables" ->
+ "what columns to read (read_set)".
+ To break this loop, we always read all columns of a constant table if
+ it is going to be updated.
+ Another case is in multi-UPDATE and multi-DELETE, when the table has a
+ trigger: bits of columns needed by the trigger are turned on in
+ result->initialize_tables(), which has not yet been called when we do
+ the reading now, so we must read all columns.
+ */
+ table->column_bitmaps_set(&table->s->all_set, table->write_set);
+ }
+ }
+#endif
if (tab->type == JT_SYSTEM)
{
@@ -12027,7 +12057,12 @@ join_read_const_table(JOIN_TAB *tab, POS
#else
if (!table->maybe_null || error > 0)
#endif
+ {
+#ifndef MCP_BUG_12728221
+ table->column_bitmaps_set(save_read_set, table->write_set);
+#endif
DBUG_RETURN(error);
+ }
}
}
else
@@ -12052,7 +12087,12 @@ join_read_const_table(JOIN_TAB *tab, POS
#else
if (!table->maybe_null || error > 0)
#endif
+ {
+#ifndef MCP_BUG_12728221
+ table->column_bitmaps_set(save_read_set, table->write_set);
+#endif
DBUG_RETURN(error);
+ }
}
}
if (*tab->on_expr_ref && !table->null_row)
@@ -12083,6 +12123,9 @@ join_read_const_table(JOIN_TAB *tab, POS
embedding->nested_join->join_list.head() == embedded);
}
+#ifndef MCP_BUG_12728221
+ table->column_bitmaps_set(save_read_set, table->write_set);
+#endif
DBUG_RETURN(0);
}
No bundle (reason: useless for push emails).
| Thread |
|---|
| • bzr push into mysql-5.1-telco-7.0 branch (jonas.oreland:4636 to 4637) | Jonas Oreland | 7 Nov |
