[prev in list] [next in list] [prev in thread] [next in thread]
List: mysql-internals
Subject: bk commit into 4.0 tree (1.1445)
From: monty () mysql ! com
Date: 2002-11-30 22:48:39
[Download RAW message or body]
Below is the list of changes that have just been committed into a local
4.0 repository of monty. When monty does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://www.mysql.com/doc/I/n/Installing_source_tree.html
ChangeSet
1.1445 02/12/01 00:48:35 monty@mashka.mysql.fi +8 -0
Fixed some bugs from last multi-table-update push.
More tests for multi-table-update & timestamp handling
sql/sql_update.cc
1.70 02/12/01 00:48:33 monty@mashka.mysql.fi +2 -2
Fixed bug in autocommit in multi-table-update
sql/sql_select.cc
1.211 02/12/01 00:48:33 monty@mashka.mysql.fi +1 -1
Fixed bug in safe mode checking
mysql-test/t/type_timestamp.test
1.6 02/12/01 00:48:33 monty@mashka.mysql.fi +16 -5
New timestamp tests
mysql-test/t/multi_update.test
1.17 02/12/01 00:48:33 monty@mashka.mysql.fi +52 -4
New multi-table-update tests
mysql-test/t/innodb.test
1.27 02/12/01 00:48:33 monty@mashka.mysql.fi +13 -0
New multi-table-update tests
mysql-test/r/type_timestamp.result
1.7 02/12/01 00:48:33 monty@mashka.mysql.fi +26 -6
New timestamp tests
mysql-test/r/multi_update.result
1.15 02/12/01 00:48:33 monty@mashka.mysql.fi +70 -0
New multi-table-update tests
mysql-test/r/innodb.result
1.49 02/12/01 00:48:33 monty@mashka.mysql.fi +15 -0
New multi-table-update tests
# This is a BitKeeper patch. What follows are the unified diffs for the
# set of deltas contained in the patch. The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User: monty
# Host: mashka.mysql.fi
# Root: /home/my/mysql-4.0
--- 1.210/sql/sql_select.cc Fri Nov 29 16:40:15 2002
+++ 1.211/sql/sql_select.cc Sun Dec 1 00:48:33 2002
@@ -2744,7 +2744,7 @@
tab < end;
tab++)
{
- if (tab->type == JT_ALL && !tab->select->quick)
+ if (tab->type == JT_ALL && (!tab->select || !tab->select->quick))
{
my_error(ER_UPDATE_WITHOUT_KEY_IN_SAFE_MODE,MYF(0));
return(1);
--- 1.69/sql/sql_update.cc Fri Nov 29 16:40:16 2002
+++ 1.70/sql/sql_update.cc Sun Dec 1 00:48:33 2002
@@ -832,7 +832,7 @@
Query_log_event qinfo(thd, thd->query, thd->query_length,
log_delayed);
if (mysql_bin_log.write(&qinfo) && trans_safe)
- local_error=1; // Rollback update
+ local_error= 1; // Rollback update
}
if (!log_delayed)
thd->options|=OPTION_STATUS_NO_TRANS_UPDATE;
@@ -840,7 +840,7 @@
if (transactional_tables)
{
- if (ha_autocommit_or_rollback(thd, local_error >= 0))
+ if (ha_autocommit_or_rollback(thd, local_error != 0))
local_error=1;
}
--- 1.48/mysql-test/r/innodb.result Tue Nov 12 13:27:50 2002
+++ 1.49/mysql-test/r/innodb.result Sun Dec 1 00:48:33 2002
@@ -1021,3 +1021,18 @@
7 4 Matt
COMMIT;
DROP TABLE t1;
+drop table if exists t1,t2;
+create table t1 (n int(10), d int(10)) type=innodb;
+create table t2 (n int(10), d int(10)) type=innodb;
+insert into t1 values(1,1),(1,2);
+insert into t2 values(1,10),(2,20);
+UPDATE t1,t2 SET t1.d=t2.d,t2.d=30 WHERE t1.n=t2.n;
+select * from t1;
+n d
+1 10
+1 10
+select * from t2;
+n d
+1 30
+2 20
+drop table t1,t2;
--- 1.6/mysql-test/r/type_timestamp.result Fri Aug 30 21:32:54 2002
+++ 1.7/mysql-test/r/type_timestamp.result Sun Dec 1 00:48:33 2002
@@ -1,11 +1,31 @@
-drop table if exists t1;
-CREATE TABLE t1 ( t timestamp);
+drop table if exists t1,t2;
+CREATE TABLE t1 (a int, t timestamp);
+CREATE TABLE t2 (a int, t datetime);
SET TIMESTAMP=1234;
-insert into t1 values(NULL);
+insert into t1 values(1,NULL);
+insert into t1 values(2,"2002-03-03");
+SET TIMESTAMP=1235;
+insert into t1 values(3,NULL);
+SET TIMESTAMP=1236;
+insert into t1 (a) values(4);
+insert into t2 values(5,"2002-03-04"),(6,NULL),(7,"2002-03-05"),(8,"00-00-00");
+SET TIMESTAMP=1237;
+insert into t1 select * from t2;
+SET TIMESTAMP=1238;
+insert into t1 (a) select a+1 from t2 where a=8;
select * from t1;
-t
-19700101032034
-drop table t1;
+a t
+1 19700101032034
+2 20020303000000
+3 19700101032035
+4 19700101032036
+5 20020304000000
+6 19700101032037
+7 20020305000000
+8 00000000000000
+9 19700101032038
+drop table t1,t2;
+SET TIMESTAMP=1234;
CREATE TABLE t1 (value TEXT NOT NULL, id VARCHAR(32) NOT NULL, stamp timestamp, \
PRIMARY KEY (id)); INSERT INTO t1 VALUES ("my value", "myKey","1999-04-02 \
00:00:00"); SELECT stamp FROM t1 WHERE id="myKey";
--- 1.26/mysql-test/t/innodb.test Wed Aug 21 23:55:05 2002
+++ 1.27/mysql-test/t/innodb.test Sun Dec 1 00:48:33 2002
@@ -660,3 +660,16 @@
select id, code, name from t1 order by id;
COMMIT;
DROP TABLE t1;
+
+#
+# Test of multi-table-update
+#
+drop table if exists t1,t2;
+create table t1 (n int(10), d int(10)) type=innodb;
+create table t2 (n int(10), d int(10)) type=innodb;
+insert into t1 values(1,1),(1,2);
+insert into t2 values(1,10),(2,20);
+UPDATE t1,t2 SET t1.d=t2.d,t2.d=30 WHERE t1.n=t2.n;
+select * from t1;
+select * from t2;
+drop table t1,t2;
--- 1.5/mysql-test/t/type_timestamp.test Fri Aug 30 21:32:55 2002
+++ 1.6/mysql-test/t/type_timestamp.test Sun Dec 1 00:48:33 2002
@@ -2,14 +2,25 @@
# Test timestamp
#
-drop table if exists t1;
-CREATE TABLE t1 ( t timestamp);
+drop table if exists t1,t2;
+CREATE TABLE t1 (a int, t timestamp);
+CREATE TABLE t2 (a int, t datetime);
SET TIMESTAMP=1234;
-insert into t1 values(NULL);
+insert into t1 values(1,NULL);
+insert into t1 values(2,"2002-03-03");
+SET TIMESTAMP=1235;
+insert into t1 values(3,NULL);
+SET TIMESTAMP=1236;
+insert into t1 (a) values(4);
+insert into t2 values(5,"2002-03-04"),(6,NULL),(7,"2002-03-05"),(8,"00-00-00");
+SET TIMESTAMP=1237;
+insert into t1 select * from t2;
+SET TIMESTAMP=1238;
+insert into t1 (a) select a+1 from t2 where a=8;
select * from t1;
-drop table t1;
-
+drop table t1,t2;
+SET TIMESTAMP=1234;
CREATE TABLE t1 (value TEXT NOT NULL, id VARCHAR(32) NOT NULL, stamp timestamp, \
PRIMARY KEY (id)); INSERT INTO t1 VALUES ("my value", "myKey","1999-04-02 \
00:00:00"); SELECT stamp FROM t1 WHERE id="myKey";
--- 1.14/mysql-test/r/multi_update.result Fri Nov 29 16:40:15 2002
+++ 1.15/mysql-test/r/multi_update.result Sun Dec 1 00:48:33 2002
@@ -166,3 +166,73 @@
2 20
unlock tables;
drop table t1,t2;
+set sql_safe_updates=1;
+create table t1 (n int(10), d int(10));
+create table t2 (n int(10), d int(10));
+insert into t1 values(1,1);
+insert into t2 values(1,10),(2,20);
+UPDATE t1,t2 SET t1.d=t2.d WHERE t1.n=t2.n;
+You are using safe update mode and you tried to update a table without a WHERE that \
uses a KEY column +set sql_safe_updates=0;
+drop table t1,t2;
+set timestamp=1038401397;
+create table t1 (n int(10) not null primary key, d int(10), t timestamp);
+create table t2 (n int(10) not null primary key, d int(10), t timestamp);
+insert into t1 values(1,1,NULL);
+insert into t2 values(1,10,NULL),(2,20,NULL);
+set timestamp=1038000000;
+UPDATE t1,t2 SET t1.d=t2.d WHERE t1.n=t2.n;
+select * from t1;
+n d t
+1 10 20021123002000
+select * from t2;
+n d t
+1 10 20021127154957
+2 20 20021127154957
+UPDATE t1,t2 SET 1=2 WHERE t1.n=t2.n;
+You have an error in your SQL syntax. Check the manual that corresponds to your \
MySQL server version for the right syntax to use near '1=2 WHERE t1.n=t2.n' at line 1 \
+drop table t1,t2; +set timestamp=0;
+set sql_safe_updates=0;
+create table t1 (n int(10) not null primary key, d int(10));
+create table t2 (n int(10) not null primary key, d int(10));
+insert into t1 values(1,1), (3,3);
+insert into t2 values(1,10),(2,20);
+UPDATE t2 left outer join t1 on t1.n=t2.n SET t1.d=t2.d;
+select * from t1;
+n d
+1 10
+3 3
+select * from t2;
+n d
+1 10
+2 20
+drop table t1,t2;
+create table t1 (n int(10), d int(10));
+create table t2 (n int(10), d int(10));
+insert into t1 values(1,1),(1,2);
+insert into t2 values(1,10),(2,20);
+UPDATE t1,t2 SET t1.d=t2.d,t2.d=30 WHERE t1.n=t2.n;
+select * from t1;
+n d
+1 10
+1 10
+select * from t2;
+n d
+1 30
+2 20
+drop table t1,t2;
+create table t1 (n int(10), d int(10));
+create table t2 (n int(10), d int(10));
+insert into t1 values(1,1),(3,2);
+insert into t2 values(1,10),(1,20);
+UPDATE t1,t2 SET t1.d=t2.d,t2.d=30 WHERE t1.n=t2.n;
+select * from t1;
+n d
+1 10
+3 2
+select * from t2;
+n d
+1 30
+1 30
+drop table t1,t2;
--- 1.16/mysql-test/t/multi_update.test Fri Nov 29 16:40:15 2002
+++ 1.17/mysql-test/t/multi_update.test Sun Dec 1 00:48:33 2002
@@ -1,9 +1,6 @@
#
-# Only run the test if we are using --big-test, because this test takes a
-# long time
+# Test of update statement that uses many tables.
#
-#-- require r/big_test.require
-#eval select $BIG_TEST as using_big_test;
drop table if exists t1,t2,t3;
create table t1(id1 int not null auto_increment primary key, t char(12));
@@ -165,4 +162,55 @@
select * from t1;
select * from t2;
unlock tables;
+drop table t1,t2;
+
+#
+# Test safe updates and timestamps
+#
+set sql_safe_updates=1;
+create table t1 (n int(10), d int(10));
+create table t2 (n int(10), d int(10));
+insert into t1 values(1,1);
+insert into t2 values(1,10),(2,20);
+--error 1175
+UPDATE t1,t2 SET t1.d=t2.d WHERE t1.n=t2.n;
+set sql_safe_updates=0;
+drop table t1,t2;
+set timestamp=1038401397;
+create table t1 (n int(10) not null primary key, d int(10), t timestamp);
+create table t2 (n int(10) not null primary key, d int(10), t timestamp);
+insert into t1 values(1,1,NULL);
+insert into t2 values(1,10,NULL),(2,20,NULL);
+set timestamp=1038000000;
+UPDATE t1,t2 SET t1.d=t2.d WHERE t1.n=t2.n;
+select * from t1;
+select * from t2;
+--error 1064
+UPDATE t1,t2 SET 1=2 WHERE t1.n=t2.n;
+drop table t1,t2;
+set timestamp=0;
+set sql_safe_updates=0;
+create table t1 (n int(10) not null primary key, d int(10));
+create table t2 (n int(10) not null primary key, d int(10));
+insert into t1 values(1,1), (3,3);
+insert into t2 values(1,10),(2,20);
+UPDATE t2 left outer join t1 on t1.n=t2.n SET t1.d=t2.d;
+select * from t1;
+select * from t2;
+drop table t1,t2;
+create table t1 (n int(10), d int(10));
+create table t2 (n int(10), d int(10));
+insert into t1 values(1,1),(1,2);
+insert into t2 values(1,10),(2,20);
+UPDATE t1,t2 SET t1.d=t2.d,t2.d=30 WHERE t1.n=t2.n;
+select * from t1;
+select * from t2;
+drop table t1,t2;
+create table t1 (n int(10), d int(10));
+create table t2 (n int(10), d int(10));
+insert into t1 values(1,1),(3,2);
+insert into t2 values(1,10),(1,20);
+UPDATE t1,t2 SET t1.d=t2.d,t2.d=30 WHERE t1.n=t2.n;
+select * from t1;
+select * from t2;
drop table t1,t2;
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail internals-thread5860@lists.mysql.com
To unsubscribe, e-mail <internals-unsubscribe@lists.mysql.com>
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic