mirror of
https://github.com/MariaDB/server.git
synced 2025-02-12 00:15:35 +01:00
9e1fb104a3
-----BEGIN PGP SIGNATURE----- iQIzBAABCgAdFiEEF39AEP5WyjM2MAMF8WVvJMdM0dgFAmck77AACgkQ8WVvJMdM 0dgccQ/+Lls8fWt4D+gMPP7x+drJSO/IE/gZFt3ugbWF+/p3B2xXAs5AAE83wxEh QSbp4DCkb/9PnuakhLmzg0lFbxMUlh4rsJ1YyiuLB2J+YgKbAc36eQQf+rtYSipd DT5uRk36c9wOcOXo/mMv4APEvpPXBIBdIL4VvpKFbIOE7xT24Sp767zWXdXqrB1f JgOQdM2ct+bvSPC55oZ5p1kqyxwvd6K6+3RB3CIpwW9zrVSLg7enT3maLjj/761s jvlRae+Cv+r+Hit9XpmEH6n2FYVgIJ3o3WhdAHwN0kxKabXYTg7OCB7QxDZiUHI9 C/5goKmKaPB1PCQyuTQyLSyyK9a8nPfgn6tqw/p/ZKDQhKT9sWJv/5bSWecrVndx LLYifSTrFC/eXLzgPvCnNv/U8SjsZaAdMIKS681+qDJ0P5abghUIlGnMYTjYXuX1 1B6Vrr0bdrQ3V1CLB3tpkRjpUvicrsabtuAUAP65QnEG2G9UJXklOer+DE291Gsl f1I0o6C1zVGAOkUUD3QEYaHD8w7hlvyfKme5oXKUm3DOjaAar5UUKLdr6prxRZL4 ebhmGEy42Mf8fBYoeohIxmxgvv6h2Xd9xCukgPp8hFpqJGw8abg7JNZTTKH4h2IY J51RpD10h4eoi6WRn3opEcjexTGvZ+xNR7yYO5WxWw6VIre9IUA= =s+WW -----END PGP SIGNATURE----- Merge tag '11.4' into 11.6 MariaDB 11.4.4 release
257 lines
5.5 KiB
Text
257 lines
5.5 KiB
Text
create temporary table t1 (i int) engine = innodb;
|
|
insert into t1 values (1), (2), (3), (4);
|
|
select * from t1;
|
|
i
|
|
1
|
|
2
|
|
3
|
|
4
|
|
select * from t1 where i = 4;
|
|
i
|
|
4
|
|
drop table t1;
|
|
create temporary table t1 (i int) engine = innodb;
|
|
insert into t1 values (1), (2), (3), (4);
|
|
select * from t1;
|
|
i
|
|
1
|
|
2
|
|
3
|
|
4
|
|
select * from t1 where i = 4;
|
|
i
|
|
4
|
|
drop table t1;
|
|
create temporary table t2 (i int) engine = innodb;
|
|
insert into t2 values (1), (2), (3), (4);
|
|
select * from t2;
|
|
i
|
|
1
|
|
2
|
|
3
|
|
4
|
|
select * from t2 where i = 4;
|
|
i
|
|
4
|
|
drop table t2;
|
|
create temporary table t1
|
|
(keyc int, c1 char(100), c2 char(100),
|
|
primary key(keyc)) engine = innodb;
|
|
create procedure populate_t1()
|
|
begin
|
|
declare i int default 1;
|
|
while (i <= 200) DO
|
|
insert into t1 values (i, 'a', 'b');
|
|
set i = i + 1;
|
|
end while;
|
|
end|
|
|
set autocommit=0;
|
|
select count(*) from t1;
|
|
count(*)
|
|
0
|
|
call populate_t1();
|
|
select count(*) from t1;
|
|
count(*)
|
|
200
|
|
select * from t1 limit 10;
|
|
keyc c1 c2
|
|
1 a b
|
|
2 a b
|
|
3 a b
|
|
4 a b
|
|
5 a b
|
|
6 a b
|
|
7 a b
|
|
8 a b
|
|
9 a b
|
|
10 a b
|
|
set autocommit=1;
|
|
truncate table t1;
|
|
select count(*) from t1;
|
|
count(*)
|
|
0
|
|
drop table t1;
|
|
create temporary table t1 (i int) engine = innodb;
|
|
insert into t1 values (1), (2), (3), (4);
|
|
select * from t1;
|
|
i
|
|
1
|
|
2
|
|
3
|
|
4
|
|
select * from t1 where i = 4;
|
|
i
|
|
4
|
|
drop table t1;
|
|
create temporary table t1
|
|
(keyc int, c1 char(100), c2 char(100),
|
|
primary key(keyc))
|
|
engine = innodb;
|
|
begin;
|
|
select count(*) from t1;
|
|
count(*)
|
|
0
|
|
call populate_t1();
|
|
select count(*) from t1;
|
|
count(*)
|
|
200
|
|
rollback;
|
|
select count(*) from t1;
|
|
count(*)
|
|
0
|
|
begin;
|
|
call populate_t1();
|
|
commit;
|
|
select count(*) from t1;
|
|
count(*)
|
|
200
|
|
truncate table t1;
|
|
select count(*) from t1;
|
|
count(*)
|
|
0
|
|
drop table t1;
|
|
drop procedure populate_t1;
|
|
create temporary table t1 (t1_i int, t1_f float) engine = innodb;
|
|
insert into t1 values (1, 1.1), (2, 2.5), (3, 2.5), (4, 4.4);
|
|
explain select * from t1 where t1_i = 1;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 ALL NULL NULL NULL NULL 4 Using where
|
|
alter table t1 add unique index pri_index(t1_i);
|
|
explain select * from t1 where t1_i = 1;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 const pri_index pri_index 5 const 1
|
|
select * from t1 where t1_i = 1;
|
|
t1_i t1_f
|
|
1 1.1
|
|
alter table t1 add unique index sec_index(t1_f);
|
|
ERROR 23000: Duplicate entry '2.5' for key 'sec_index'
|
|
alter table t1 add index sec_index(t1_f);
|
|
explain select * from t1 where t1_f >= 2.5;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 range sec_index sec_index 5 NULL 3 Using index condition
|
|
select * from t1 where t1_f >= 2.5;
|
|
t1_i t1_f
|
|
2 2.5
|
|
3 2.5
|
|
4 4.4
|
|
alter table t1 add column (t1_c char(10));
|
|
select * from t1;
|
|
t1_i t1_f t1_c
|
|
1 1.1 NULL
|
|
2 2.5 NULL
|
|
3 2.5 NULL
|
|
4 4.4 NULL
|
|
insert into t1 values (5, 5.5, 'krunal');
|
|
alter table t1 drop column t1_f;
|
|
show create table t1;
|
|
Table Create Table
|
|
t1 CREATE TEMPORARY TABLE `t1` (
|
|
`t1_i` int(11) DEFAULT NULL,
|
|
`t1_c` char(10) DEFAULT NULL,
|
|
UNIQUE KEY `pri_index` (`t1_i`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
|
|
select * from t1 where t1_f >= 2.5;
|
|
ERROR 42S22: Unknown column 't1_f' in 'WHERE'
|
|
alter table t1 add index sec_index2(t1_c), algorithm=inplace;
|
|
ERROR 0A000: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY
|
|
drop table t1;
|
|
create temporary table t1 (i int, f float) engine = innodb;
|
|
insert into t1 values (10, 1.1), (20, 2.2);
|
|
select * from t1;
|
|
i f
|
|
10 1.1
|
|
20 2.2
|
|
alter table t1 discard tablespace;
|
|
ERROR HY000: Cannot DISCARD/IMPORT tablespace associated with temporary table
|
|
alter table t1 import tablespace;
|
|
ERROR HY000: Cannot DISCARD/IMPORT tablespace associated with temporary table
|
|
drop table t1;
|
|
create temporary table t1 (i int) engine=innodb;
|
|
insert into t1 values (1), (2), (3);
|
|
select * from t1;
|
|
i
|
|
1
|
|
2
|
|
3
|
|
alter table t1 rename t2;
|
|
select * from t1;
|
|
ERROR 42S02: Table 'test.t1' doesn't exist
|
|
select * from t2;
|
|
i
|
|
1
|
|
2
|
|
3
|
|
insert into t2 values (1), (2), (6), (7);
|
|
select * from t2;
|
|
i
|
|
1
|
|
2
|
|
3
|
|
1
|
|
2
|
|
6
|
|
7
|
|
drop table t2;
|
|
SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
|
|
create temporary table t (
|
|
a int not null,
|
|
b blob not null,
|
|
index sk (b(3021))
|
|
) row_format = dynamic engine=innodb;
|
|
drop table t;
|
|
create temporary table t (
|
|
a int not null,
|
|
b blob not null,
|
|
index sk (b(3021))
|
|
) row_format = dynamic engine=innodb;
|
|
drop table t;
|
|
create temporary table t (
|
|
a int not null,
|
|
b blob not null,
|
|
index sk (b(3021))
|
|
) row_format = dynamic engine=innodb;
|
|
drop table t;
|
|
SET innodb_strict_mode=OFF;
|
|
create temporary table t (
|
|
a int not null,
|
|
b blob not null,
|
|
index sk (b(3021))
|
|
) row_format = compact engine=innodb;
|
|
ERROR HY000: Index column size too large. The maximum column size is 767 bytes
|
|
create temporary table t (
|
|
a int not null,
|
|
b blob not null,
|
|
index sk (b(3021))
|
|
) row_format = dynamic engine=innodb;
|
|
drop table t;
|
|
create temporary table t (
|
|
a int not null,
|
|
b blob not null,
|
|
index sk (b(3021))
|
|
) row_format = compressed engine=innodb;
|
|
drop table t;
|
|
create temporary table t (
|
|
a int not null,
|
|
b blob not null,
|
|
index sk (b(3021))
|
|
) row_format = compact engine=innodb;
|
|
ERROR HY000: Index column size too large. The maximum column size is 767 bytes
|
|
create temporary table t (
|
|
a int not null,
|
|
b blob not null,
|
|
index sk (b(3021))
|
|
) row_format = dynamic engine=innodb;
|
|
drop table t;
|
|
CREATE TABLE t1 ( i INT ) ENGINE = Innodb;
|
|
CREATE TEMPORARY TABLE t2 ( i INT ) ENGINE = Innodb;
|
|
SELECT COUNT(*) FROM information_schema.INNODB_SYS_TABLES WHERE NAME LIKE '%test%t_';
|
|
COUNT(*)
|
|
1
|
|
CREATE TEMPORARY table t3 ( i INT ) ENGINE = Innodb;
|
|
SELECT COUNT(*) FROM information_schema.INNODB_SYS_TABLES WHERE NAME LIKE '%test%t_';
|
|
COUNT(*)
|
|
1
|
|
DROP TABLE t1,t2,t3;
|
|
SELECT COUNT(*) FROM information_schema.INNODB_SYS_TABLES WHERE NAME LIKE '%test%t_';
|
|
COUNT(*)
|
|
0
|