mirror of
https://github.com/MariaDB/server.git
synced 2025-10-31 19:06:14 +01:00
229 lines
6.4 KiB
Text
229 lines
6.4 KiB
Text
#
|
|
# Test of group functions that depend on innodb
|
|
#
|
|
|
|
--source include/have_innodb.inc
|
|
|
|
set @innodb_stats_persistent_save= @@innodb_stats_persistent;
|
|
set @innodb_stats_persistent_sample_pages_save=
|
|
@@innodb_stats_persistent_sample_pages;
|
|
|
|
set global innodb_stats_persistent= 1;
|
|
set global innodb_stats_persistent_sample_pages=100;
|
|
|
|
--disable_warnings
|
|
create table t1 (USR_ID integer not null, MAX_REQ integer not null, constraint PK_SEA_USER primary key (USR_ID)) engine=InnoDB;
|
|
--enable_warnings
|
|
insert into t1 values (1, 3);
|
|
select count(*) + MAX_REQ - MAX_REQ + MAX_REQ - MAX_REQ + MAX_REQ - MAX_REQ + MAX_REQ - MAX_REQ + MAX_REQ - MAX_REQ as exp from t1 group by MAX_REQ;
|
|
select Case When Count(*) < MAX_REQ Then 1 Else 0 End from t1 where t1.USR_ID = 1 group by MAX_REQ;
|
|
drop table t1;
|
|
|
|
|
|
#
|
|
# Bug #12882 min/max inconsistent on empty table
|
|
#
|
|
|
|
--disable_warnings
|
|
create table t1m (a int) engine=myisam;
|
|
create table t1i (a int) engine=innodb;
|
|
create table t2m (a int) engine=myisam;
|
|
create table t2i (a int) engine=innodb;
|
|
--enable_warnings
|
|
insert into t2m values (5);
|
|
insert into t2i values (5);
|
|
|
|
# test with MyISAM
|
|
select min(a) from t1m;
|
|
select min(7) from t1m;
|
|
select min(7) from DUAL;
|
|
explain select min(7) from t2m join t1m;
|
|
select min(7) from t2m join t1m;
|
|
|
|
select max(a) from t1m;
|
|
select max(7) from t1m;
|
|
select max(7) from DUAL;
|
|
explain select max(7) from t2m join t1m;
|
|
select max(7) from t2m join t1m;
|
|
|
|
select 1, min(a) from t1m where a=99;
|
|
select 1, min(a) from t1m where 1=99;
|
|
select 1, min(1) from t1m where a=99;
|
|
select 1, min(1) from t1m where 1=99;
|
|
|
|
select 1, max(a) from t1m where a=99;
|
|
select 1, max(a) from t1m where 1=99;
|
|
select 1, max(1) from t1m where a=99;
|
|
select 1, max(1) from t1m where 1=99;
|
|
|
|
# test with InnoDB
|
|
select min(a) from t1i;
|
|
select min(7) from t1i;
|
|
select min(7) from DUAL;
|
|
explain select min(7) from t2i join t1i;
|
|
select min(7) from t2i join t1i;
|
|
|
|
select max(a) from t1i;
|
|
select max(7) from t1i;
|
|
select max(7) from DUAL;
|
|
explain select max(7) from t2i join t1i;
|
|
select max(7) from t2i join t1i;
|
|
|
|
select 1, min(a) from t1i where a=99;
|
|
select 1, min(a) from t1i where 1=99;
|
|
select 1, min(1) from t1i where a=99;
|
|
select 1, min(1) from t1i where 1=99;
|
|
|
|
select 1, max(a) from t1i where a=99;
|
|
select 1, max(a) from t1i where 1=99;
|
|
select 1, max(1) from t1i where a=99;
|
|
select 1, max(1) from t1i where 1=99;
|
|
|
|
# mixed MyISAM/InnoDB test
|
|
explain select count(*), min(7), max(7) from t1m, t1i;
|
|
select count(*), min(7), max(7) from t1m, t1i;
|
|
|
|
explain select count(*), min(7), max(7) from t1m, t2i;
|
|
select count(*), min(7), max(7) from t1m, t2i;
|
|
|
|
explain select count(*), min(7), max(7) from t2m, t1i;
|
|
select count(*), min(7), max(7) from t2m, t1i;
|
|
|
|
drop table t1m, t1i, t2m, t2i;
|
|
|
|
--echo #
|
|
--echo # Bug #57954: BIT_AND function returns incorrect results when
|
|
--echo # semijoin=on
|
|
|
|
CREATE TABLE c (
|
|
pk INT,
|
|
col_varchar_key VARCHAR(1),
|
|
PRIMARY KEY (pk),
|
|
KEY col_varchar_key (col_varchar_key)
|
|
) ENGINE=InnoDB;
|
|
INSERT INTO c VALUES (11,NULL);
|
|
INSERT INTO c VALUES (16,'c');
|
|
CREATE TABLE bb (
|
|
pk INT,
|
|
col_varchar_key VARCHAR(1),
|
|
PRIMARY KEY (pk),
|
|
KEY col_varchar_key (col_varchar_key)
|
|
) ENGINE=InnoDB;
|
|
INSERT INTO bb VALUES (10,NULL);
|
|
|
|
SELECT straight_join BIT_AND(c.pk)
|
|
FROM
|
|
bb, c
|
|
WHERE c.col_varchar_key='ABC'
|
|
ORDER BY c.pk;
|
|
|
|
DROP TABLE c,bb;
|
|
|
|
--echo #
|
|
--echo # Bug #58050: BIT_OR and BIT_XOR return incorrect results when
|
|
--echo # semijoin=on
|
|
--echo #
|
|
|
|
CREATE TABLE t1 (pk INT PRIMARY KEY, b INT, c INT) ENGINE=InnoDB;
|
|
INSERT INTO t1 VALUES(1, 1, 1);
|
|
|
|
CREATE TABLE t2 (pk INT PRIMARY KEY, b INT, c INT) ENGINE=InnoDB;
|
|
INSERT INTO t2 VALUES (1, 1, NULL);
|
|
|
|
SELECT t1.* FROM t1 JOIN t2 ON t1.c=t2.c WHERE t1.pk=1;
|
|
SELECT BIT_OR(t1.b) FROM t1 JOIN t2 ON t1.c=t2.c WHERE t1.pk=1;
|
|
SELECT BIT_AND(t1.b) FROM t1 JOIN t2 ON t1.c=t2.c WHERE t1.pk=1;
|
|
SELECT BIT_XOR(t1.b) FROM t1 JOIN t2 ON t1.c=t2.c WHERE t1.pk=1;
|
|
|
|
DROP TABLE t1, t2;
|
|
|
|
--echo #
|
|
--echo # Bug#12713907: STRANGE OPTIMIZE & WRONG RESULT UNDER ORDER BY
|
|
--echo # COUNT(*) LIMIT.
|
|
--echo #
|
|
|
|
CREATE TABLE t1 (
|
|
id BIGINT(20) ,
|
|
member_id_to INT(11) ,
|
|
r_date DATE ,
|
|
PRIMARY KEY (id,r_date),
|
|
KEY r_date_idx (r_date),
|
|
KEY t1_idx01 (member_id_to)
|
|
) ENGINE=InnoDB;
|
|
|
|
INSERT INTO t1 VALUES
|
|
(107924526,518491,'2011-05-01'),
|
|
(107924527,518491,'2011-05-01'),
|
|
(107924534,518491,'2011-06-21'),
|
|
(107924535,518491,'2011-06-21'),
|
|
(107924542,1601319,'2011-06-21'),
|
|
(107924543,1601319,'2011-06-21'),
|
|
(107924544,1601319,'2011-06-21'),
|
|
(107924545,1601319,'2011-06-21');
|
|
|
|
SELECT member_id_to, COUNT(*) FROM t1 WHERE r_date =
|
|
'2011-06-21' GROUP BY member_id_to ORDER BY 2 LIMIT 1;
|
|
|
|
DROP TABLE t1;
|
|
|
|
--echo #
|
|
--echo # MDEV-4269: crash when grouping by values()
|
|
--echo #
|
|
|
|
--disable_cursor_protocol
|
|
SELECT @@default_storage_engine INTO @old_engine;
|
|
set default_storage_engine=innodb;
|
|
--enable_cursor_protocol
|
|
|
|
create table y select 1 b;
|
|
select 1 from y group by b;
|
|
select 1 from y group by value(b);
|
|
drop table y;
|
|
SET default_storage_engine=@old_engine;
|
|
|
|
### End of 5.1 tests
|
|
|
|
--echo #
|
|
--echo # Bug#13723054 CRASH WITH MIN/MAX AFTER QUICK_GROUP_MIN_MAX_SELECT::NEXT_MIN
|
|
--echo #
|
|
|
|
CREATE TABLE t1(a BLOB, b VARCHAR(255) CHARSET LATIN1, c INT,
|
|
KEY(b, c, a(765))) ENGINE=INNODB;
|
|
INSERT INTO t1(a, b, c) VALUES
|
|
('', 'a', 0), ('', 'a', null), ('', 'a', 0), ('', 'a', null), ('', 'a', 0),
|
|
('', 'a', 1), ('', 'a', 1), ('', 'a', 2), ('', 'a', 2), ('', 'a', 3),
|
|
('', 'a', 3), ('', 'a', 4), ('', 'a', 4), ('', 'a', 5), ('', 'a', 5);
|
|
|
|
-- disable_result_log
|
|
ANALYZE TABLE t1;
|
|
-- enable_result_log
|
|
|
|
EXPLAIN SELECT MIN(c) FROM t1 GROUP BY b;
|
|
SELECT MIN(c) FROM t1 GROUP BY b;
|
|
|
|
DROP TABLE t1;
|
|
|
|
--echo #
|
|
--echo # MDEV-17589: Stack-buffer-overflow with indexed varchar (utf8) field
|
|
--echo #
|
|
|
|
CREATE TABLE t1 (v1 varchar(1020), v2 varchar(2), v3 varchar(2),
|
|
KEY k1 (v3,v2,v1)) ENGINE=InnoDB CHARACTER SET=utf8 ROW_FORMAT=DYNAMIC;
|
|
INSERT INTO t1 VALUES ('king', 'qu','qu'), ('bad','go','go');
|
|
explain
|
|
SELECT MIN(t1.v1) FROM t1 where t1.v2='qu' and t1.v3='qu';
|
|
SELECT MIN(t1.v1) FROM t1 where t1.v2='qu' and t1.v3='qu';
|
|
drop table t1;
|
|
|
|
CREATE TABLE t1 (v1 varchar(1024) CHARACTER SET utf8, KEY v1 (v1)) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
|
|
INSERT INTO t1 VALUES ('king'), ('bad');
|
|
explain
|
|
SELECT MIN(x.v1) FROM (SELECT t1.* FROM t1 WHERE t1.v1 >= 'p') x;
|
|
SELECT MIN(x.v1) FROM (SELECT t1.* FROM t1 WHERE t1.v1 >= 'p') x;
|
|
drop table t1;
|
|
|
|
--echo End of 5.5 tests
|
|
|
|
set global innodb_stats_persistent= @innodb_stats_persistent_save;
|
|
set global innodb_stats_persistent_sample_pages=
|
|
@innodb_stats_persistent_sample_pages_save;
|