You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
673 lines
19 KiB
673 lines
19 KiB
# Skipping it for binlog_format=STATEMENT due to Unsafe statements:
|
|
# unsafe auto-increment column, LIMIT clause.
|
|
--source include/not_binlog_format_statement.inc
|
|
|
|
#
|
|
# test of updating of keys
|
|
#
|
|
|
|
--disable_warnings
|
|
drop table if exists t1,t2;
|
|
--enable_warnings
|
|
|
|
create table t1 (a int auto_increment , primary key (a));
|
|
insert into t1 values (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL);
|
|
update t1 set a=a+10 where a > 34;
|
|
update t1 set a=a+100 where a > 0;
|
|
|
|
# Some strange updates to test some otherwise unused code
|
|
update t1 set a=a+100 where a=1 and a=2;
|
|
--error 1054
|
|
update t1 set a=b+100 where a=1 and a=2;
|
|
--error 1054
|
|
update t1 set a=b+100 where c=1 and a=2;
|
|
--error 1054
|
|
update t1 set d=a+100 where a=1;
|
|
select * from t1;
|
|
drop table t1;
|
|
|
|
CREATE TABLE t1
|
|
(
|
|
place_id int (10) unsigned NOT NULL,
|
|
shows int(10) unsigned DEFAULT '0' NOT NULL,
|
|
ishows int(10) unsigned DEFAULT '0' NOT NULL,
|
|
ushows int(10) unsigned DEFAULT '0' NOT NULL,
|
|
clicks int(10) unsigned DEFAULT '0' NOT NULL,
|
|
iclicks int(10) unsigned DEFAULT '0' NOT NULL,
|
|
uclicks int(10) unsigned DEFAULT '0' NOT NULL,
|
|
ts timestamp,
|
|
PRIMARY KEY (place_id,ts)
|
|
);
|
|
|
|
INSERT INTO t1 (place_id,shows,ishows,ushows,clicks,iclicks,uclicks,ts)
|
|
VALUES (1,0,0,0,0,0,0,20000928174434);
|
|
UPDATE t1 SET shows=shows+1,ishows=ishows+1,ushows=ushows+1,clicks=clicks+1,iclicks=iclicks+1,uclicks=uclicks+1 WHERE place_id=1 AND ts>="2000-09-28 00:00:00";
|
|
select place_id,shows from t1;
|
|
drop table t1;
|
|
|
|
#
|
|
# Test of ORDER BY
|
|
#
|
|
|
|
create table t1 (a int not null, b int not null, key (a));
|
|
insert into t1 values (1,1),(1,2),(1,3),(3,1),(3,2),(3,3),(3,1),(3,2),(3,3),(2,1),(2,2),(2,3);
|
|
SET @tmp=0;
|
|
update t1 set b=(@tmp:=@tmp+1) order by a;
|
|
update t1 set b=99 where a=1 order by b asc limit 1;
|
|
select * from t1 order by a,b;
|
|
update t1 set b=100 where a=1 order by b desc limit 2;
|
|
update t1 set a=a+10+b where a=1 order by b;
|
|
select * from t1 order by a,b;
|
|
create table t2 (a int not null, b int not null);
|
|
insert into t2 values (1,1),(1,2),(1,3);
|
|
update t1 set b=(select distinct 1 from (select * from t2) a);
|
|
drop table t1,t2;
|
|
|
|
#
|
|
# Multi table update test from bugs
|
|
#
|
|
|
|
create table t1 (F1 VARCHAR(30), F2 VARCHAR(30), F3 VARCHAR(30), cnt int, groupid int, KEY groupid_index (groupid));
|
|
|
|
insert into t1 (F1,F2,F3,cnt,groupid) values ('0','0','0',1,6),
|
|
('0','1','2',1,5), ('0','2','0',1,3), ('1','0','1',1,2),
|
|
('1','2','1',1,1), ('1','2','2',1,1), ('2','0','1',2,4),
|
|
('2','2','0',1,7);
|
|
delete from m1 using t1 m1,t1 m2 where m1.groupid=m2.groupid and (m1.cnt < m2.cnt or m1.cnt=m2.cnt and m1.F3>m2.F3);
|
|
select * from t1;
|
|
drop table t1;
|
|
|
|
#
|
|
# Bug#5553 - Multi table UPDATE IGNORE fails on duplicate keys
|
|
#
|
|
|
|
CREATE TABLE t1 (
|
|
`colA` int(10) unsigned NOT NULL auto_increment,
|
|
`colB` int(11) NOT NULL default '0',
|
|
PRIMARY KEY (`colA`)
|
|
);
|
|
INSERT INTO t1 VALUES (4433,5424);
|
|
CREATE TABLE t2 (
|
|
`colC` int(10) unsigned NOT NULL default '0',
|
|
`colA` int(10) unsigned NOT NULL default '0',
|
|
`colD` int(10) unsigned NOT NULL default '0',
|
|
`colE` int(10) unsigned NOT NULL default '0',
|
|
`colF` int(10) unsigned NOT NULL default '0',
|
|
PRIMARY KEY (`colC`,`colA`,`colD`,`colE`)
|
|
);
|
|
INSERT INTO t2 VALUES (3,4433,10005,495,500);
|
|
INSERT INTO t2 VALUES (3,4433,10005,496,500);
|
|
INSERT INTO t2 VALUES (3,4433,10009,494,500);
|
|
INSERT INTO t2 VALUES (3,4433,10011,494,500);
|
|
INSERT INTO t2 VALUES (3,4433,10005,497,500);
|
|
INSERT INTO t2 VALUES (3,4433,10013,489,500);
|
|
INSERT INTO t2 VALUES (3,4433,10005,494,500);
|
|
INSERT INTO t2 VALUES (3,4433,10005,493,500);
|
|
INSERT INTO t2 VALUES (3,4433,10005,492,500);
|
|
UPDATE IGNORE t2,t1 set t2.colE = t2.colE + 1,colF=0 WHERE t1.colA = t2.colA AND (t1.colB & 4096) > 0 AND (colE + 1) < colF;
|
|
SELECT * FROM t2;
|
|
DROP TABLE t1;
|
|
DROP TABLE t2;
|
|
|
|
#
|
|
# Bug #6054
|
|
#
|
|
create table t1 (c1 int, c2 char(6), c3 int);
|
|
create table t2 (c1 int, c2 char(6));
|
|
insert into t1 values (1, "t1c2-1", 10), (2, "t1c2-2", 20);
|
|
update t1 left join t2 on t1.c1 = t2.c1 set t2.c2 = "t2c2-1";
|
|
update t1 left join t2 on t1.c1 = t2.c1 set t2.c2 = "t2c2-1" where t1.c3 = 10;
|
|
drop table t1, t2;
|
|
|
|
#
|
|
# Bug #8057
|
|
#
|
|
create table t1 (id int not null auto_increment primary key, id_str varchar(32));
|
|
insert into t1 (id_str) values ("test");
|
|
update t1 set id_str = concat(id_str, id) where id = last_insert_id();
|
|
select * from t1;
|
|
drop table t1;
|
|
|
|
#
|
|
# Bug #8942: a problem with update and partial key part
|
|
#
|
|
|
|
create table t1 (a int, b char(255), key(a, b(20)));
|
|
insert into t1 values (0, '1');
|
|
update t1 set b = b + 1 where a = 0;
|
|
select * from t1;
|
|
drop table t1;
|
|
|
|
#
|
|
# Bug #11868 Update with subquery with ref built with a key from the updated
|
|
# table crashes server
|
|
#
|
|
create table t1(f1 int, f2 int);
|
|
create table t2(f3 int, f4 int);
|
|
create index idx on t2(f3);
|
|
insert into t1 values(1,0),(2,0);
|
|
insert into t2 values(1,1),(2,2);
|
|
UPDATE t1 SET t1.f2=(SELECT MAX(t2.f4) FROM t2 WHERE t2.f3=t1.f1);
|
|
select * from t1;
|
|
drop table t1,t2;
|
|
|
|
#
|
|
# Bug #13180 sometimes server accepts sum func in update/delete where condition
|
|
#
|
|
create table t1(f1 int);
|
|
select DATABASE();
|
|
--error 1111
|
|
update t1 set f1=1 where count(*)=1;
|
|
select DATABASE();
|
|
--error 1111
|
|
delete from t1 where count(*)=1;
|
|
drop table t1;
|
|
|
|
# BUG#12915: Optimize "DELETE|UPDATE ... ORDER BY ... LIMIT n" to use an index
|
|
create table t1 ( a int, b int default 0, index (a) );
|
|
insert into t1 (a) values (0),(0),(0),(0),(0),(0),(0),(0);
|
|
|
|
flush status;
|
|
select a from t1 order by a limit 1;
|
|
show status like 'handler_read%';
|
|
|
|
flush status;
|
|
update t1 set a=9999 order by a limit 1;
|
|
update t1 set b=9999 order by a limit 1;
|
|
show status like 'handler_read%';
|
|
|
|
flush status;
|
|
delete from t1 order by a limit 1;
|
|
show status like 'handler_read%';
|
|
|
|
flush status;
|
|
delete from t1 order by a desc limit 1;
|
|
show status like 'handler_read%';
|
|
|
|
alter table t1 disable keys;
|
|
|
|
flush status;
|
|
delete from t1 order by a limit 1;
|
|
show status like 'handler_read%';
|
|
|
|
select * from t1;
|
|
update t1 set a=a+10,b=1 order by a limit 3;
|
|
update t1 set a=a+11,b=2 order by a limit 3;
|
|
update t1 set a=a+12,b=3 order by a limit 3;
|
|
select * from t1 order by a;
|
|
|
|
drop table t1;
|
|
|
|
#
|
|
# Bug#15028 Multitable update returns different numbers of matched rows
|
|
# depending on table order
|
|
create table t1 (f1 int);
|
|
create table t2 (f2 int);
|
|
insert into t1 values(1),(2);
|
|
insert into t2 values(1),(1);
|
|
--enable_info
|
|
update t1,t2 set f1=3,f2=3 where f1=f2 and f1=1;
|
|
--disable_info
|
|
update t2 set f2=1;
|
|
update t1 set f1=1 where f1=3;
|
|
--enable_info
|
|
update t2,t1 set f1=3,f2=3 where f1=f2 and f1=1;
|
|
--disable_info
|
|
drop table t1,t2;
|
|
|
|
|
|
# BUG#15935
|
|
create table t1 (a int);
|
|
insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
|
|
create table t2 (a int, filler1 char(200), filler2 char(200), key(a));
|
|
insert into t2 select A.a + 10*B.a, 'filler','filler' from t1 A, t1 B;
|
|
flush status;
|
|
update t2 set a=3 where a=2;
|
|
show status like 'handler_read%';
|
|
drop table t1, t2;
|
|
|
|
#
|
|
# Bug #16510 Updating field named like '*name' caused server crash
|
|
#
|
|
create table t1(f1 int, `*f2` int);
|
|
insert into t1 values (1,1);
|
|
update t1 set `*f2`=1;
|
|
drop table t1;
|
|
|
|
#
|
|
# Bug#25126: Wrongly resolved field leads to a crash
|
|
#
|
|
create table t1(f1 int);
|
|
--error 1054
|
|
update t1 set f2=1 order by f2;
|
|
drop table t1;
|
|
# End of 4.1 tests
|
|
|
|
#
|
|
# Bug #24035: performance degradation with condition int_field=big_decimal
|
|
#
|
|
SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
|
|
CREATE TABLE t1 (
|
|
request_id int unsigned NOT NULL auto_increment,
|
|
user_id varchar(12) default NULL,
|
|
time_stamp datetime NOT NULL default '0000-00-00 00:00:00',
|
|
ip_address varchar(15) default NULL,
|
|
PRIMARY KEY (request_id),
|
|
KEY user_id_2 (user_id,time_stamp)
|
|
);
|
|
|
|
INSERT INTO t1 (user_id) VALUES ('user1');
|
|
INSERT INTO t1(user_id) SELECT user_id FROM t1;
|
|
INSERT INTO t1(user_id) SELECT user_id FROM t1;
|
|
INSERT INTO t1(user_id) SELECT user_id FROM t1;
|
|
INSERT INTO t1(user_id) SELECT user_id FROM t1;
|
|
INSERT INTO t1(user_id) SELECT user_id FROM t1;
|
|
INSERT INTO t1(user_id) SELECT user_id FROM t1;
|
|
INSERT INTO t1(user_id) SELECT user_id FROM t1;
|
|
INSERT INTO t1(user_id) SELECT user_id FROM t1;
|
|
|
|
flush status;
|
|
SELECT user_id FROM t1 WHERE request_id=9999999999999;
|
|
show status like '%Handler_read%';
|
|
SELECT user_id FROM t1 WHERE request_id=999999999999999999999999999999;
|
|
show status like '%Handler_read%';
|
|
UPDATE t1 SET user_id=null WHERE request_id=9999999999999;
|
|
show status like '%Handler_read%';
|
|
UPDATE t1 SET user_id=null WHERE request_id=999999999999999999999999999999;
|
|
show status like '%Handler_read%';
|
|
|
|
DROP TABLE t1;
|
|
SET sql_mode = default;
|
|
#
|
|
# Bug #24010: INSERT INTO ... SELECT fails on unique constraint with data it
|
|
# doesn't select
|
|
#
|
|
CREATE TABLE t1 (
|
|
|
|
a INT(11),
|
|
quux decimal( 31, 30 ),
|
|
|
|
UNIQUE KEY bar (a),
|
|
KEY quux (quux)
|
|
);
|
|
|
|
INSERT INTO
|
|
t1 ( a, quux )
|
|
VALUES
|
|
( 1, 1 ),
|
|
( 2, 0.1 );
|
|
|
|
INSERT INTO t1( a )
|
|
SELECT @newA := 1 + a FROM t1 WHERE quux <= 0.1;
|
|
|
|
SELECT * FROM t1;
|
|
|
|
DROP TABLE t1;
|
|
|
|
#
|
|
# Bug #22364: Inconsistent "matched rows" when executing UPDATE
|
|
#
|
|
|
|
connect (con1,localhost,root,,test);
|
|
connection con1;
|
|
|
|
set tmp_table_size=1024;
|
|
|
|
# Create the test tables
|
|
create table t1 (id int, a int, key idx(a));
|
|
create table t2 (id int unsigned not null auto_increment primary key, a int);
|
|
insert into t2(a) values(1),(2),(3),(4),(5),(6),(7),(8);
|
|
insert into t2(a) select a from t2;
|
|
insert into t2(a) select a from t2;
|
|
insert into t2(a) select a from t2;
|
|
update t2 set a=id;
|
|
insert into t1 select * from t2;
|
|
|
|
# Check that the number of matched rows is correct when the temporary
|
|
# table is small enough to not be converted to MyISAM
|
|
select count(*) from t1 join t2 on (t1.a=t2.a);
|
|
--enable_info
|
|
update t1 join t2 on (t1.a=t2.a) set t1.id=t2.id;
|
|
--disable_info
|
|
|
|
# Increase table sizes
|
|
insert into t2(a) select a from t2;
|
|
update t2 set a=id;
|
|
truncate t1;
|
|
insert into t1 select * from t2;
|
|
|
|
# Check that the number of matched rows is correct when the temporary
|
|
# table has to be converted to MyISAM
|
|
select count(*) from t1 join t2 on (t1.a=t2.a);
|
|
--enable_info
|
|
update t1 join t2 on (t1.a=t2.a) set t1.id=t2.id;
|
|
--disable_info
|
|
|
|
# Check that the number of matched rows is correct when there are duplicate
|
|
# key errors
|
|
update t1 set a=1;
|
|
update t2 set a=1;
|
|
select count(*) from t1 join t2 on (t1.a=t2.a);
|
|
--enable_info
|
|
update t1 join t2 on (t1.a=t2.a) set t1.id=t2.id;
|
|
--disable_info
|
|
|
|
drop table t1,t2;
|
|
|
|
connection default;
|
|
disconnect con1;
|
|
|
|
#
|
|
# Bug #40745: Error during WHERE clause calculation in UPDATE
|
|
# leads to an assertion failure
|
|
#
|
|
--disable_warnings
|
|
DROP TABLE IF EXISTS t1;
|
|
DROP FUNCTION IF EXISTS f1;
|
|
--enable_warnings
|
|
|
|
CREATE FUNCTION f1() RETURNS INT RETURN f1();
|
|
CREATE TABLE t1 (i INT);
|
|
INSERT INTO t1 VALUES (1);
|
|
|
|
--error ER_SP_NO_RECURSION
|
|
UPDATE t1 SET i = 3 WHERE f1();
|
|
--error ER_SP_NO_RECURSION
|
|
UPDATE t1 SET i = f1();
|
|
|
|
DROP TABLE t1;
|
|
DROP FUNCTION f1;
|
|
|
|
--echo End of 5.0 tests
|
|
|
|
--echo #
|
|
--echo # Bug #47919 assert in open_table during ALTER temporary table
|
|
--echo #
|
|
|
|
CREATE TABLE t1 (f1 INTEGER AUTO_INCREMENT, PRIMARY KEY (f1));
|
|
CREATE TEMPORARY TABLE t2 LIKE t1;
|
|
INSERT INTO t1 VALUES (1);
|
|
INSERT INTO t2 VALUES (1);
|
|
|
|
ALTER TABLE t2 COMMENT = 'ABC';
|
|
UPDATE t2, t1 SET t2.f1 = 2, t1.f1 = 9;
|
|
ALTER TABLE t2 COMMENT = 'DEF';
|
|
|
|
DROP TABLE t1, t2;
|
|
|
|
--echo #
|
|
--echo # Bug#50545: Single table UPDATE IGNORE crashes on join view in
|
|
--echo # sql_safe_updates mode.
|
|
--echo #
|
|
CREATE TABLE t1 ( a INT, KEY( a ) );
|
|
INSERT INTO t1 VALUES (0), (1);
|
|
CREATE VIEW v1 AS SELECT t11.a, t12.a AS b FROM t1 t11, t1 t12;
|
|
SET SESSION sql_safe_updates = 1;
|
|
|
|
--error ER_UPDATE_WITHOUT_KEY_IN_SAFE_MODE
|
|
UPDATE IGNORE v1 SET a = 1;
|
|
|
|
SET SESSION sql_safe_updates = DEFAULT;
|
|
DROP TABLE t1;
|
|
DROP VIEW v1;
|
|
|
|
--echo #
|
|
--echo # Bug#54734 assert in Diagnostics_area::set_ok_status
|
|
--echo #
|
|
SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
|
|
--disable_warnings
|
|
DROP TABLE IF EXISTS t1, not_exists;
|
|
DROP FUNCTION IF EXISTS f1;
|
|
DROP VIEW IF EXISTS v1;
|
|
--enable_warnings
|
|
|
|
CREATE TABLE t1 (PRIMARY KEY(pk)) AS SELECT 1 AS pk;
|
|
CREATE FUNCTION f1() RETURNS INTEGER RETURN (SELECT 1 FROM not_exists);
|
|
CREATE VIEW v1 AS SELECT pk FROM t1 WHERE f1() = 13;
|
|
--error ER_VIEW_INVALID
|
|
UPDATE v1 SET pk = 7 WHERE pk > 0;
|
|
|
|
DROP VIEW v1;
|
|
DROP FUNCTION f1;
|
|
DROP TABLE t1;
|
|
SET sql_mode = default;
|
|
--echo #
|
|
--echo # Verify that UPDATE does the same number of handler_update
|
|
--echo # operations, no matter if there is ORDER BY or not.
|
|
--echo #
|
|
|
|
CREATE TABLE t1 (i INT) ENGINE=INNODB;
|
|
INSERT INTO t1 VALUES (10),(11),(12),(13),(14),(15),(16),(17),(18),(19),
|
|
(20),(21),(22),(23),(24),(25),(26),(27),(28),(29),
|
|
(30),(31),(32),(33),(34),(35);
|
|
CREATE TABLE t2 (a CHAR(2), b CHAR(2), c CHAR(2), d CHAR(2),
|
|
INDEX idx (a,b(1),c)) ENGINE=INNODB;
|
|
INSERT INTO t2 SELECT i, i, i, i FROM t1;
|
|
FLUSH STATUS; # FLUSH is autocommit, so we put it outside of transaction
|
|
START TRANSACTION;
|
|
UPDATE t2 SET d = 10 WHERE b = 10 LIMIT 5;
|
|
# There is one handler_update less when the binary log is enabled.
|
|
SET @binlog_handler_update= IF(@@global.log_bin AND @@global.binlog_format != 'STATEMENT', 1, 0);
|
|
--let $handler_update_statement= SELECT VARIABLE_VALUE + @binlog_handler_update FROM performance_schema.session_status WHERE VARIABLE_NAME = 'HANDLER_UPDATE'
|
|
--eval $handler_update_statement
|
|
ROLLBACK;
|
|
FLUSH STATUS;
|
|
START TRANSACTION;
|
|
UPDATE t2 SET d = 10 WHERE b = 10 ORDER BY a, c LIMIT 5;
|
|
--eval $handler_update_statement
|
|
ROLLBACK;
|
|
|
|
--echo Same test with a different UPDATE.
|
|
|
|
ALTER TABLE t2 DROP INDEX idx, ADD INDEX idx2 (a, b);
|
|
FLUSH STATUS;
|
|
START TRANSACTION;
|
|
UPDATE t2 SET c = 10 LIMIT 5;
|
|
--eval $handler_update_statement
|
|
ROLLBACK;
|
|
FLUSH STATUS;
|
|
START TRANSACTION;
|
|
UPDATE t2 SET c = 10 ORDER BY a, b DESC LIMIT 5;
|
|
--eval $handler_update_statement
|
|
ROLLBACK;
|
|
DROP TABLE t1, t2;
|
|
|
|
--echo # Bug#20454533: Assertion failed: sargables == 0 || *keyfields ...
|
|
|
|
CREATE TABLE t1(
|
|
a int,
|
|
c int,
|
|
e int,
|
|
f int,
|
|
g blob,
|
|
h int,
|
|
i int,
|
|
j blob,
|
|
unique key (g(221),c),
|
|
unique key (c,a,j(148)),
|
|
key (i)
|
|
) engine=innodb;
|
|
|
|
UPDATE (SELECT 1 AS a FROM t1 NATURAL JOIN t1 AS t2) AS x, t1
|
|
SET t1.e= x.a;
|
|
|
|
DROP TABLE t1;
|
|
|
|
--echo # Bug #21143080: UPDATE ON VARCHAR AND TEXT COLUMNS PRODUCE INCORRECT
|
|
--echo # RESULTS
|
|
|
|
CREATE TABLE t1 (a VARCHAR(50), b TEXT, c CHAR(50)) ENGINE=INNODB;
|
|
|
|
INSERT INTO t1 (a, b, c) VALUES ('start trail', '', 'even longer string');
|
|
UPDATE t1 SET b = a, a = 'inject';
|
|
SELECT a, b FROM t1;
|
|
UPDATE t1 SET b = c, c = 'inject';
|
|
SELECT c, b FROM t1;
|
|
|
|
DROP TABLE t1;
|
|
|
|
--echo #
|
|
--echo # Bug#18698556: UPDATE ORDER BY DOES A FILESORT IF UPDATING
|
|
--echo # A COLUMN IN THE INDEX
|
|
--echo #
|
|
|
|
CREATE TABLE t1 (
|
|
a INTEGER,
|
|
b INTEGER,
|
|
c INTEGER,
|
|
d INTEGER,
|
|
KEY key1 (a,b,c)
|
|
);
|
|
|
|
INSERT INTO t1 (a,b,c,d) VALUES (1, 1, 1, 4), (2, 2, 2, 5), (2, 3, 4, 7),
|
|
(3, 3, 3, 9), (4, 4, 4, 0), (5, 5, 5, 1), (5, 6, 7, 3), (5, 7, 9, 9);
|
|
analyze table t1;
|
|
|
|
# Updating part of index, should still use the index for the order by (no
|
|
# filesort)
|
|
analyze table t1;
|
|
let $query=UPDATE t1 SET c = 72 WHERE a = 2 ORDER BY b ASC LIMIT 1;
|
|
--replace_column 10 #
|
|
eval explain $query;
|
|
eval $query;
|
|
SELECT * FROM t1;
|
|
|
|
# Updating field not included in index, should use index for order by (no file
|
|
# sort)
|
|
let $query=UPDATE t1 SET d = 72 WHERE a = 2 ORDER BY b ASC LIMIT 1;
|
|
--replace_column 10 #
|
|
eval explain $query;
|
|
eval $query;
|
|
SELECT * FROM t1;
|
|
|
|
# Checking if we are updating three different rows when limit is increased.
|
|
# Updating field included in index, should use index for order by (no file
|
|
#sort).
|
|
INSERT INTO t1 (a,b,c,d) VALUES (5, 5, 1, 1), (5, 5, 2, 1), (5, 5, 4, 1);
|
|
analyze table t1;
|
|
let $query=UPDATE t1 SET c = 3 WHERE a = 5 ORDER BY b ASC LIMIT 3;
|
|
--replace_column 10 #
|
|
eval explain $query;
|
|
eval $query;
|
|
SELECT * FROM t1;
|
|
|
|
# Must use file sort since the index can not be used for order by as range on
|
|
# "a" is not constant
|
|
analyze table t1;
|
|
let $query=UPDATE t1 SET c = 62 WHERE a > 4 ORDER BY b ASC LIMIT 3;
|
|
--replace_column 10 #
|
|
eval explain $query;
|
|
eval $query;
|
|
SELECT * FROM t1;
|
|
|
|
# To make the cost model for order by limit to pick index for ordering, we need
|
|
# to insert some more data
|
|
INSERT INTO t1 (a,b,c,d) VALUES (1, 1, 1, 4), (2, 2, 2, 5), (2, 3, 4, 7),
|
|
(3, 3, 3, 9), (4, 4, 4, 0), (5, 5, 5, 1), (5, 6, 7, 3), (5, 7, 9, 9);
|
|
|
|
ANALYZE TABLE t1;
|
|
|
|
# Updating part of index, should still use the index for the order by (no file
|
|
# sort)
|
|
let $query=UPDATE t1 SET c = 82 ORDER BY a ASC LIMIT 1;
|
|
--replace_column 10 #
|
|
eval explain $query;
|
|
eval $query;
|
|
SELECT * FROM t1;
|
|
|
|
# Must use file sort since the index can not be used for order by
|
|
let $query=UPDATE t1 SET a = 82 ORDER BY b ASC LIMIT 1;
|
|
--replace_column 10 #
|
|
eval explain $query;
|
|
eval $query;
|
|
SELECT * FROM t1;
|
|
|
|
# Must use file sort since the index can not be used for order by
|
|
let $query=UPDATE t1 SET b = 82 ORDER BY d ASC LIMIT 1;
|
|
--replace_column 10 #
|
|
eval explain $query;
|
|
eval $query;
|
|
SELECT * FROM t1;
|
|
|
|
# Checking if we are updating two different rows when limit is increased. Cost
|
|
# model will choose index for ordering for increased limit only when some more
|
|
# dats is inserted
|
|
INSERT INTO t1 (a,b,c,d) VALUES (1, 1, 1, 4), (2, 2, 2, 5), (2, 3, 4, 7),
|
|
(3, 3, 3, 9), (4, 4, 4, 0), (5, 5, 5, 1), (5, 6, 7, 3), (5, 7, 9, 9);
|
|
INSERT INTO t1 (a,b,c,d) VALUES (1, 1, 1, 4), (2, 2, 2, 5), (2, 3, 4, 7),
|
|
(3, 3, 3, 9), (4, 4, 4, 0), (5, 5, 5, 1), (5, 6, 7, 3), (5, 7, 9, 9);
|
|
|
|
ANALYZE TABLE t1;
|
|
|
|
# Updating part of index, should still use the index for the order by (no file
|
|
# sort)
|
|
let $query=UPDATE t1 SET c = 82 ORDER BY a ASC LIMIT 2;
|
|
--replace_column 10 #
|
|
eval explain $query;
|
|
eval $query;
|
|
SELECT * FROM t1;
|
|
|
|
DROP TABLE t1;
|
|
|
|
--echo # End of test for Bug#18698556
|
|
|
|
--echo #
|
|
--echo # Bug#21944866: REFACTORING: REMOVE KILL_BAD_DATA
|
|
--echo #
|
|
|
|
CREATE TABLE t1 (a INT PRIMARY KEY, b DATE);
|
|
--error ER_TRUNCATED_WRONG_VALUE
|
|
UPDATE t1 SET b= NULL WHERE a < '00:38:47.008761';
|
|
DROP TABLE t1;
|
|
|
|
--echo #
|
|
--echo # Bug#21032418: PERFORMANCE REGRESSION IN 5.6: UPDATE DOES NOT USE INDEX
|
|
--echo #
|
|
|
|
CREATE TABLE t1(
|
|
id INTEGER NOT NULL AUTO_INCREMENT,
|
|
token VARCHAR(255) DEFAULT NULL,
|
|
PRIMARY KEY (id),
|
|
KEY token (token)
|
|
)DEFAULT CHARSET=utf8;
|
|
|
|
INSERT INTO t1 VALUES (1, "abc"), (2, "def");
|
|
|
|
SET sql_mode='';
|
|
UPDATE t1 SET token = X'ad';
|
|
SELECT * FROM t1;
|
|
|
|
let query=UPDATE t1 SET token = NULL WHERE token = X'ad';
|
|
eval EXPLAIN $query;
|
|
eval $query;
|
|
|
|
SET sql_mode=default;
|
|
eval EXPLAIN $query;
|
|
eval $query;
|
|
SELECT * FROM t1;
|
|
|
|
DROP TABLE t1;
|
|
|
|
--echo # End of test for Bug#21032418
|
|
|
|
--echo #
|
|
--echo # Bug #29047894: ASSERTION FAILURE: ROW0MYSQL.CC:2387:TRX->ALLOW_SEMI_CONSISTENT()
|
|
--echo #
|
|
|
|
CREATE TABLE t1 (a INTEGER);
|
|
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
|
|
INSERT INTO t1 VALUES (0);
|
|
|
|
# Cause the UPDATE to fail halfway, verifying that we're resetting
|
|
# allow_semi_consistent() even in error paths.
|
|
--error ER_TRUNCATED_WRONG_VALUE
|
|
UPDATE t1 SET a=1 ORDER BY CAST('invalid' AS DATETIME);
|
|
|
|
# If the transaction is still marked as allowing semi-consistent reads
|
|
# when in SERIALIZABLE, this will crash when trying to unlock the
|
|
# (non-matching) record.
|
|
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
|
|
DELETE FROM t1 WHERE a=2;
|
|
|
|
DROP TABLE t1;
|
|
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; # Return to default.
|
|
|