mirror of
https://github.com/MariaDB/server.git
synced 2025-11-04 12:56:14 +01:00
52c29f3bdc
Heap tables are allocated blocks to store rows according to my_default_record_cache (mapped to the server global variable read_buffer_size). This causes performance issues when the record length is big (> 1000 bytes) and the my_default_record_cache is small. Changed to instead split the default heap allocation to 1/16 of the allowed space and not use my_default_record_cache anymore when creating the heap. The allocation is also aligned to be just under a power of 2. For some test that I have been running, which was using record length=633, the speed of the query doubled thanks to this change. Other things: - Fixed calculation of max_records passed to hp_create() to take into account padding between records. - Updated calculation of memory needed by heap tables. Before we did not take into account internal structures needed to access rows. - Changed block sized for memory_table from 1 to 16384 to get less fragmentation. This also avoids a problem where we need 1K to manage index and row storage which was not counted for before. - Moved heap memory usage to a separate test for 32 bit. - Allocate all data blocks in heap in powers of 2. Change reported memory usage for heap to reflect this. Reviewed-by: Sergei Golubchik <serg@mariadb.org>
767 lines
22 KiB
Text
767 lines
22 KiB
Text
drop table if exists t1,t2;
|
|
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;
|
|
update t1 set a=a+100 where a=1 and a=2;
|
|
update t1 set a=b+100 where a=1 and a=2;
|
|
ERROR 42S22: Unknown column 'b' in 'SET'
|
|
update t1 set a=b+100 where c=1 and a=2;
|
|
ERROR 42S22: Unknown column 'c' in 'WHERE'
|
|
update t1 set d=a+100 where a=1;
|
|
ERROR 42S22: Unknown column 'd' in 'SET'
|
|
select * from t1;
|
|
a
|
|
101
|
|
102
|
|
103
|
|
104
|
|
105
|
|
106
|
|
107
|
|
108
|
|
109
|
|
110
|
|
111
|
|
112
|
|
113
|
|
114
|
|
115
|
|
116
|
|
117
|
|
118
|
|
119
|
|
120
|
|
121
|
|
122
|
|
123
|
|
124
|
|
125
|
|
126
|
|
127
|
|
128
|
|
129
|
|
130
|
|
131
|
|
132
|
|
133
|
|
134
|
|
145
|
|
146
|
|
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;
|
|
place_id shows
|
|
1 1
|
|
drop table t1;
|
|
CREATE TABLE t1 (
|
|
lfdnr int(10) unsigned NOT NULL default '0',
|
|
ticket int(10) unsigned NOT NULL default '0',
|
|
client varchar(255) NOT NULL default '',
|
|
replyto varchar(255) NOT NULL default '',
|
|
subject varchar(100) NOT NULL default '',
|
|
timestamp int(10) unsigned NOT NULL default '0',
|
|
tstamp timestamp NOT NULL,
|
|
status int(3) NOT NULL default '0',
|
|
type varchar(15) NOT NULL default '',
|
|
assignment int(10) unsigned NOT NULL default '0',
|
|
fupcount int(4) unsigned NOT NULL default '0',
|
|
parent int(10) unsigned NOT NULL default '0',
|
|
activity int(10) unsigned NOT NULL default '0',
|
|
priority tinyint(1) unsigned NOT NULL default '1',
|
|
cc varchar(255) NOT NULL default '',
|
|
bcc varchar(255) NOT NULL default '',
|
|
body text NOT NULL,
|
|
comment text,
|
|
header text,
|
|
PRIMARY KEY (lfdnr),
|
|
KEY k1 (timestamp),
|
|
KEY k2 (type),
|
|
KEY k3 (parent),
|
|
KEY k4 (assignment),
|
|
KEY ticket (ticket)
|
|
) ENGINE=MyISAM;
|
|
INSERT INTO t1 VALUES (773,773,'','','',980257344,20010318180652,0,'Open',10,0,0,0,1,'','','','','');
|
|
alter table t1 change lfdnr lfdnr int(10) unsigned not null auto_increment;
|
|
update t1 set status=1 where type='Open';
|
|
select status from t1;
|
|
status
|
|
1
|
|
drop table t1;
|
|
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;
|
|
a b
|
|
1 2
|
|
1 3
|
|
1 99
|
|
2 4
|
|
2 5
|
|
2 6
|
|
3 7
|
|
3 8
|
|
3 9
|
|
3 10
|
|
3 11
|
|
3 12
|
|
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;
|
|
a b
|
|
2 4
|
|
2 5
|
|
2 6
|
|
3 7
|
|
3 8
|
|
3 9
|
|
3 10
|
|
3 11
|
|
3 12
|
|
13 2
|
|
111 100
|
|
111 100
|
|
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;
|
|
CREATE TABLE t1 (
|
|
`id_param` smallint(3) unsigned NOT NULL default '0',
|
|
`nom_option` char(40) NOT NULL default '',
|
|
`valid` tinyint(1) NOT NULL default '0',
|
|
KEY `id_param` (`id_param`,`nom_option`)
|
|
) ENGINE=MyISAM;
|
|
INSERT INTO t1 (id_param,nom_option,valid) VALUES (185,'600x1200',1);
|
|
UPDATE t1 SET nom_option='test' WHERE id_param=185 AND nom_option='600x1200' AND valid=1 LIMIT 1;
|
|
select * from t1;
|
|
id_param nom_option valid
|
|
185 test 1
|
|
drop table t1;
|
|
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;
|
|
F1 F2 F3 cnt groupid
|
|
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
|
|
2 0 1 2 4
|
|
2 2 0 1 7
|
|
drop table t1;
|
|
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;
|
|
colC colA colD colE colF
|
|
3 4433 10005 495 500
|
|
3 4433 10005 496 500
|
|
3 4433 10009 495 0
|
|
3 4433 10011 495 0
|
|
3 4433 10005 498 0
|
|
3 4433 10013 490 0
|
|
3 4433 10005 494 500
|
|
3 4433 10005 493 500
|
|
3 4433 10005 492 500
|
|
DROP TABLE t1;
|
|
DROP TABLE t2;
|
|
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;
|
|
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;
|
|
id id_str
|
|
1 test1
|
|
drop table t1;
|
|
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;
|
|
a b
|
|
0 2
|
|
drop table t1;
|
|
create table t1 (a int, b varchar(10), key b(b(5))) engine=myisam;
|
|
create table t2 (a int, b varchar(10)) engine=myisam;
|
|
insert into t1 values ( 1, 'abcd1e');
|
|
insert into t1 values ( 2, 'abcd2e');
|
|
insert into t2 values ( 1, 'abcd1e');
|
|
insert into t2 values ( 2, 'abcd2e');
|
|
analyze table t1,t2;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status Engine-independent statistics collected
|
|
test.t1 analyze status OK
|
|
test.t2 analyze status Engine-independent statistics collected
|
|
test.t2 analyze status OK
|
|
update t1, t2 set t1.a = t2.a where t2.b = t1.b;
|
|
show warnings;
|
|
Level Code Message
|
|
drop table t1, t2;
|
|
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;
|
|
f1 f2
|
|
1 1
|
|
2 2
|
|
drop table t1,t2;
|
|
create table t1(f1 int);
|
|
select DATABASE();
|
|
DATABASE()
|
|
test
|
|
update t1 set f1=1 where count(*)=1;
|
|
ERROR HY000: Invalid use of group function
|
|
select DATABASE();
|
|
DATABASE()
|
|
test
|
|
delete from t1 where count(*)=1;
|
|
ERROR HY000: Invalid use of group function
|
|
drop table t1;
|
|
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;
|
|
a
|
|
0
|
|
show status like 'handler_read%';
|
|
Variable_name Value
|
|
Handler_read_first 1
|
|
Handler_read_key 0
|
|
Handler_read_last 0
|
|
Handler_read_next 0
|
|
Handler_read_prev 0
|
|
Handler_read_retry 0
|
|
Handler_read_rnd 0
|
|
Handler_read_rnd_deleted 0
|
|
Handler_read_rnd_next 0
|
|
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%';
|
|
Variable_name Value
|
|
Handler_read_first 1
|
|
Handler_read_key 0
|
|
Handler_read_last 0
|
|
Handler_read_next 0
|
|
Handler_read_prev 0
|
|
Handler_read_retry 0
|
|
Handler_read_rnd 2
|
|
Handler_read_rnd_deleted 0
|
|
Handler_read_rnd_next 9
|
|
flush status;
|
|
delete from t1 order by a limit 1;
|
|
show status like 'handler_read%';
|
|
Variable_name Value
|
|
Handler_read_first 1
|
|
Handler_read_key 0
|
|
Handler_read_last 0
|
|
Handler_read_next 0
|
|
Handler_read_prev 0
|
|
Handler_read_retry 0
|
|
Handler_read_rnd 0
|
|
Handler_read_rnd_deleted 0
|
|
Handler_read_rnd_next 0
|
|
flush status;
|
|
delete from t1 order by a desc limit 1;
|
|
show status like 'handler_read%';
|
|
Variable_name Value
|
|
Handler_read_first 0
|
|
Handler_read_key 0
|
|
Handler_read_last 1
|
|
Handler_read_next 0
|
|
Handler_read_prev 0
|
|
Handler_read_retry 0
|
|
Handler_read_rnd 0
|
|
Handler_read_rnd_deleted 0
|
|
Handler_read_rnd_next 0
|
|
alter table t1 disable keys;
|
|
flush status;
|
|
delete from t1 order by a limit 1;
|
|
show status like 'handler_read%';
|
|
Variable_name Value
|
|
Handler_read_first 0
|
|
Handler_read_key 4
|
|
Handler_read_last 0
|
|
Handler_read_next 0
|
|
Handler_read_prev 0
|
|
Handler_read_retry 0
|
|
Handler_read_rnd 1
|
|
Handler_read_rnd_deleted 2
|
|
Handler_read_rnd_next 7
|
|
select * from t1;
|
|
a b
|
|
0 0
|
|
0 0
|
|
0 0
|
|
0 0
|
|
0 0
|
|
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;
|
|
a b
|
|
11 2
|
|
21 2
|
|
22 3
|
|
22 3
|
|
23 3
|
|
drop table t1;
|
|
create table t1 (f1 date not null);
|
|
insert into t1 values('2000-01-01'),('0000-00-00');
|
|
update t1 set f1='2002-02-02' where f1 is null;
|
|
select * from t1;
|
|
f1
|
|
2000-01-01
|
|
2002-02-02
|
|
drop table t1;
|
|
create table t1 (f1 int);
|
|
create table t2 (f2 int);
|
|
insert into t1 values(1),(2);
|
|
insert into t2 values(1),(1);
|
|
update t1,t2 set f1=3,f2=3 where f1=f2 and f1=1;
|
|
affected rows: 3
|
|
info: Rows matched: 3 Changed: 3 Warnings: 0
|
|
update t2 set f2=1;
|
|
update t1 set f1=1 where f1=3;
|
|
update t2,t1 set f1=3,f2=3 where f1=f2 and f1=1;
|
|
affected rows: 3
|
|
info: Rows matched: 3 Changed: 3 Warnings: 0
|
|
drop table t1,t2;
|
|
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%';
|
|
Variable_name Value
|
|
Handler_read_first 0
|
|
Handler_read_key 1
|
|
Handler_read_last 0
|
|
Handler_read_next 1
|
|
Handler_read_prev 0
|
|
Handler_read_retry 0
|
|
Handler_read_rnd 1
|
|
Handler_read_rnd_deleted 0
|
|
Handler_read_rnd_next 0
|
|
drop table t1, t2;
|
|
create table t1(f1 int, `*f2` int);
|
|
insert into t1 values (1,1);
|
|
update t1 set `*f2`=1;
|
|
drop table t1;
|
|
create table t1(f1 int);
|
|
update t1 set f2=1 order by f2;
|
|
ERROR 42S22: Unknown column 'f2' in 'ORDER BY'
|
|
drop table t1;
|
|
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;
|
|
user_id
|
|
show status like '%Handler_read%';
|
|
Variable_name Value
|
|
Handler_read_first 0
|
|
Handler_read_key 1
|
|
Handler_read_last 0
|
|
Handler_read_next 0
|
|
Handler_read_prev 0
|
|
Handler_read_retry 0
|
|
Handler_read_rnd 0
|
|
Handler_read_rnd_deleted 0
|
|
Handler_read_rnd_next 0
|
|
SELECT user_id FROM t1 WHERE request_id=999999999999999999999999999999;
|
|
user_id
|
|
show status like '%Handler_read%';
|
|
Variable_name Value
|
|
Handler_read_first 0
|
|
Handler_read_key 2
|
|
Handler_read_last 0
|
|
Handler_read_next 0
|
|
Handler_read_prev 0
|
|
Handler_read_retry 0
|
|
Handler_read_rnd 0
|
|
Handler_read_rnd_deleted 0
|
|
Handler_read_rnd_next 0
|
|
UPDATE t1 SET user_id=null WHERE request_id=9999999999999;
|
|
show status like '%Handler_read%';
|
|
Variable_name Value
|
|
Handler_read_first 0
|
|
Handler_read_key 2
|
|
Handler_read_last 0
|
|
Handler_read_next 0
|
|
Handler_read_prev 0
|
|
Handler_read_retry 0
|
|
Handler_read_rnd 0
|
|
Handler_read_rnd_deleted 0
|
|
Handler_read_rnd_next 0
|
|
UPDATE t1 SET user_id=null WHERE request_id=999999999999999999999999999999;
|
|
show status like '%Handler_read%';
|
|
Variable_name Value
|
|
Handler_read_first 0
|
|
Handler_read_key 2
|
|
Handler_read_last 0
|
|
Handler_read_next 0
|
|
Handler_read_prev 0
|
|
Handler_read_retry 0
|
|
Handler_read_rnd 0
|
|
Handler_read_rnd_deleted 0
|
|
Handler_read_rnd_next 0
|
|
DROP TABLE t1;
|
|
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;
|
|
a quux
|
|
1 1.000000000000000000000000000000
|
|
2 0.100000000000000000000000000000
|
|
3 NULL
|
|
DROP TABLE t1;
|
|
connect con1,localhost,root,,test;
|
|
connection con1;
|
|
set tmp_table_size=2048;
|
|
Warnings:
|
|
Warning 1292 Truncated incorrect tmp_table_size value: '2048'
|
|
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;
|
|
select count(*) from t1 join t2 on (t1.a=t2.a);
|
|
count(*)
|
|
64
|
|
update t1 join t2 on (t1.a=t2.a) set t1.id=t2.id;
|
|
affected rows: 0
|
|
info: Rows matched: 64 Changed: 0 Warnings: 0
|
|
insert into t2(a) select a from t2;
|
|
update t2 set a=id;
|
|
truncate t1;
|
|
insert into t1 select * from t2;
|
|
select count(*) from t1 join t2 on (t1.a=t2.a);
|
|
count(*)
|
|
128
|
|
update t1 join t2 on (t1.a=t2.a) set t1.id=t2.id;
|
|
affected rows: 0
|
|
info: Rows matched: 128 Changed: 0 Warnings: 0
|
|
update t1 set a=1;
|
|
update t2 set a=1;
|
|
select count(*) from t1 join t2 on (t1.a=t2.a);
|
|
count(*)
|
|
16384
|
|
update t1 join t2 on (t1.a=t2.a) set t1.id=t2.id;
|
|
affected rows: 127
|
|
info: Rows matched: 128 Changed: 127 Warnings: 0
|
|
drop table t1,t2;
|
|
connection default;
|
|
disconnect con1;
|
|
DROP TABLE IF EXISTS t1;
|
|
DROP FUNCTION IF EXISTS f1;
|
|
CREATE FUNCTION f1() RETURNS INT RETURN f1();
|
|
CREATE TABLE t1 (i INT);
|
|
INSERT INTO t1 VALUES (1);
|
|
UPDATE t1 SET i = 3 WHERE f1();
|
|
ERROR HY000: Recursive stored functions and triggers are not allowed
|
|
UPDATE t1 SET i = f1();
|
|
ERROR HY000: Recursive stored functions and triggers are not allowed
|
|
DROP TABLE t1;
|
|
DROP FUNCTION f1;
|
|
End of 5.0 tests
|
|
#
|
|
# Bug #47919 assert in open_table during ALTER temporary table
|
|
#
|
|
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;
|
|
#
|
|
# Bug#50545: Single table UPDATE IGNORE crashes on join view in
|
|
# sql_safe_updates mode.
|
|
#
|
|
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;
|
|
UPDATE IGNORE v1 SET a = 1;
|
|
ERROR HY000: You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column
|
|
SET SESSION sql_safe_updates = DEFAULT;
|
|
DROP TABLE t1;
|
|
DROP VIEW v1;
|
|
#
|
|
# Bug#54734 assert in Diagnostics_area::set_ok_status
|
|
#
|
|
DROP TABLE IF EXISTS t1, not_exists;
|
|
DROP FUNCTION IF EXISTS f1;
|
|
DROP VIEW IF EXISTS v1;
|
|
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;
|
|
UPDATE v1 SET pk = 7 WHERE pk > 0;
|
|
ERROR HY000: View 'test.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
|
|
DROP VIEW v1;
|
|
DROP FUNCTION f1;
|
|
DROP TABLE t1;
|
|
# Bug #21143080: UPDATE ON VARCHAR AND TEXT COLUMNS PRODUCE INCORRECT
|
|
# 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;
|
|
a b
|
|
inject start trail
|
|
UPDATE t1 SET b = c, c = 'inject';
|
|
SELECT c, b FROM t1;
|
|
c b
|
|
inject even longer string
|
|
DROP TABLE t1;
|
|
#
|
|
# MDEV-3948 Assertion `records_are_comparable(table)' fails in compare_record(const TABLE*) on UPDATE with simple AND condition, index_merge+index_merge_intersection, InnoDB
|
|
#
|
|
# Verify that UPDATE does the same number of handler_update
|
|
# operations, no matter if there is ORDER BY or not.
|
|
#
|
|
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;
|
|
START TRANSACTION;
|
|
UPDATE t2 SET d = 10 WHERE b = 10 LIMIT 5;
|
|
SHOW STATUS LIKE 'HANDLER_UPDATE';
|
|
Variable_name Value
|
|
Handler_update 1
|
|
ROLLBACK;
|
|
FLUSH STATUS;
|
|
START TRANSACTION;
|
|
UPDATE t2 SET d = 10 WHERE b = 10 ORDER BY a, c LIMIT 5;
|
|
SHOW STATUS LIKE 'HANDLER_UPDATE';
|
|
Variable_name Value
|
|
Handler_update 1
|
|
ROLLBACK;
|
|
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;
|
|
SHOW STATUS LIKE 'HANDLER_UPDATE';
|
|
Variable_name Value
|
|
Handler_update 5
|
|
ROLLBACK;
|
|
FLUSH STATUS;
|
|
START TRANSACTION;
|
|
UPDATE t2 SET c = 10 ORDER BY a, b DESC LIMIT 5;
|
|
SHOW STATUS LIKE 'HANDLER_UPDATE';
|
|
Variable_name Value
|
|
Handler_update 5
|
|
ROLLBACK;
|
|
DROP TABLE t1, t2;
|
|
#
|
|
# MDEV-8938: Server Crash on Update with joins
|
|
#
|
|
CREATE TABLE `t1` (
|
|
`name` varchar(255) NOT NULL,
|
|
`value` varchar(4095) DEFAULT NULL,
|
|
PRIMARY KEY (`name`)
|
|
);
|
|
UPDATE `t1` SET value = CONCAT("*.",(SELECT `temptable`.`value` FROM (SELECT * FROM `t1` WHERE `name`="consoleproxy.url.domain") AS `temptable` WHERE `temptable`.`name`="consoleproxy.url.domain")) WHERE `name`="consoleproxy.url.domain";
|
|
drop table t1;
|
|
CREATE TABLE `t1` (
|
|
`name` varchar(255) NOT NULL,
|
|
`value` varchar(4095) DEFAULT NULL,
|
|
PRIMARY KEY (`name`)
|
|
);
|
|
create table t2 (
|
|
`name` varchar(255) NOT NULL,
|
|
`value` varchar(4095) DEFAULT NULL,
|
|
PRIMARY KEY (`name`)
|
|
);
|
|
UPDATE t1
|
|
SET value = (SELECT value FROM t2 WHERE `name`= t1.name)
|
|
WHERE value is null ;
|
|
drop table t1,t2;
|
|
#
|
|
#MDEV-8701: Crash on derived query
|
|
#
|
|
CREATE TABLE t1 (
|
|
data_exit_entry_id int(11) NOT NULL,
|
|
data_entry_id int(11) NOT NULL,
|
|
data_entry_exit_id int(11) NOT NULL,
|
|
data_exit_entry_quantity double NOT NULL
|
|
) DEFAULT CHARSET=utf8;
|
|
CREATE TABLE t2 (
|
|
data_entry_id int(11) NOT NULL,
|
|
data_entry_cost double NOT NULL,
|
|
data_entry_quantity double NOT NULL
|
|
) DEFAULT CHARSET=utf8;
|
|
create algorithm=temptable view v1 as SELECT data_entry_exit_id, data_exit_entry_quantity, data_entry_cost
|
|
FROM t1 INNER JOIN t2 as dt ON dt.data_entry_id = t1.data_entry_id;
|
|
UPDATE t2
|
|
SET data_entry_cost
|
|
= ( ( SELECT SUM(data_exit_entry_quantity * data_entry_cost)
|
|
FROM
|
|
v1 AS query
|
|
WHERE data_entry_exit_id = t2.data_entry_id
|
|
)
|
|
);
|
|
UPDATE t2
|
|
SET data_entry_cost
|
|
= ( ( SELECT SUM(data_exit_entry_quantity * data_entry_cost)
|
|
FROM
|
|
( SELECT data_entry_exit_id, data_exit_entry_quantity, data_entry_cost
|
|
FROM t1 INNER JOIN t2 as dt ON dt.data_entry_id = t1.data_entry_id) AS query
|
|
WHERE data_entry_exit_id = t2.data_entry_id
|
|
)
|
|
);
|
|
drop view v1;
|
|
drop table t1, t2;
|
|
#
|
|
# MDEV-4410: update does not want to use a covering index, but select uses it.
|
|
#
|
|
create table t2(a int);
|
|
insert into t2 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
|
|
create table t1 (key1 int, col1 int, key(key1));
|
|
insert into t1
|
|
select A.a + 10 * B.a + 100 * C.a, 1234 from t2 A, t2 B, t2 C;
|
|
# This must not have "Using filesort":
|
|
explain
|
|
update t1 set key1=key1+1 where key1 between 10 and 110 order by key1 limit 2;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 range key1 key1 5 NULL 2 Using where; Using buffer
|
|
flush status;
|
|
update t1 set key1=key1+1 where key1 between 10 and 110 order by key1 limit 2;
|
|
show status like 'Handler_read%';
|
|
Variable_name Value
|
|
Handler_read_first 0
|
|
Handler_read_key 1
|
|
Handler_read_last 0
|
|
Handler_read_next 1
|
|
Handler_read_prev 0
|
|
Handler_read_retry 0
|
|
Handler_read_rnd 2
|
|
Handler_read_rnd_deleted 0
|
|
Handler_read_rnd_next 0
|
|
drop table t1, t2;
|
|
# End of MariaDB 10.0 tests
|
|
#
|
|
# MDEV-18945: multi-table update with constant table and
|
|
# comparison of date field with integer field
|
|
#
|
|
CREATE TABLE t1 (i1 int, d1 date , i2 int , d2 date) engine=myisam;
|
|
INSERT INTO t1 VALUES (19,'0000-00-00',73,'2008-05-21');
|
|
CREATE TABLE t2 (d1 date , i1 int, i2 int , d2 date) engine=myisam;
|
|
INSERT INTO t2 VALUES
|
|
('2006-01-12',-102,45,'2023-11-25'),('2034-12-19',-102,45,'2001-11-20');
|
|
UPDATE t1,t2 SET t1.i1 = -39 WHERE t2.d1 <> t1.i1 AND t2.d1 = t1.d2;
|
|
ERROR 22007: Incorrect datetime value: '19' for column `test`.`t1`.`i1` at row 1
|
|
DROP TABLE t1,t2;
|
|
# End of MariaDB 10.2 tests
|
|
#
|
|
# MDEV-20773: UPDATE with LIKE predicate over non-indexed column
|
|
# of VARCHAR type
|
|
#
|
|
create table t1 (a1 varchar(30), a2 varchar(30) collate utf8_bin);
|
|
insert into t1 values
|
|
('aa','zzz'), ('b','xxaa'), ('ccc','yyy'), ('ddd','xxb');
|
|
analyze table t1 persistent for all;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status Engine-independent statistics collected
|
|
test.t1 analyze status OK
|
|
explain extended
|
|
update t1 set a1 = 'u'
|
|
where a2 like 'xx%' and exists(select 1 from t1 where t1.a1 < 'c');
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY t1 ALL NULL NULL NULL NULL 4 49.22 Using where
|
|
2 SUBQUERY t1 ALL NULL NULL NULL NULL 4 50.00 Using where
|
|
Warnings:
|
|
Note 1003 /* select#1 */ update `test`.`t1` set `test`.`t1`.`a1` = 'u' where `test`.`t1`.`a2` like 'xx%'
|
|
update t1 set a1 = 'u'
|
|
where a2 like 'xx%' and exists(select 1 from t1 where t1.a1 < 'c');
|
|
select * from t1;
|
|
a1 a2
|
|
aa zzz
|
|
u xxaa
|
|
ccc yyy
|
|
u xxb
|
|
drop table t1;
|
|
# End of MariaDB 10.4 tests
|