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.
273 lines
6.6 KiB
273 lines
6.6 KiB
#
|
|
# Some basic tests for the TempTable storage engine.
|
|
#
|
|
# Use "-- sorted_result" instead of "ORDER BY" because the latter may cause
|
|
# different execution plans.
|
|
#
|
|
|
|
# Windows does not have gunzip(1)
|
|
-- source include/not_windows.inc
|
|
|
|
-- let $is_debug = `SELECT VERSION() LIKE '%debug%'`
|
|
|
|
CREATE DATABASE temptable_test;
|
|
USE temptable_test;
|
|
|
|
-- exec gunzip < $MYSQL_TEST_DIR/t/temptable_dump.sql.gz | $MYSQL temptable_test
|
|
|
|
-- disable_query_log
|
|
-- disable_result_log
|
|
ANALYZE TABLE A;
|
|
ANALYZE TABLE AA;
|
|
ANALYZE TABLE B;
|
|
ANALYZE TABLE BB;
|
|
ANALYZE TABLE C;
|
|
ANALYZE TABLE CC;
|
|
ANALYZE TABLE D;
|
|
ANALYZE TABLE DD;
|
|
ANALYZE TABLE DUMMY;
|
|
ANALYZE TABLE E;
|
|
ANALYZE TABLE HH;
|
|
ANALYZE TABLE K;
|
|
ANALYZE TABLE MM;
|
|
ANALYZE TABLE PP;
|
|
ANALYZE TABLE table10000_innodb_int_autoinc;
|
|
|
|
SET SESSION internal_tmp_mem_storage_engine = 'TempTable';
|
|
|
|
# Several of the DBUG_PRINT calls give 'conditional jump'
|
|
if (!$VALGRIND_TEST) {
|
|
if ($is_debug) {
|
|
SET GLOBAL debug = "+d,temptable_api";
|
|
}
|
|
}
|
|
|
|
-- enable_result_log
|
|
-- enable_query_log
|
|
|
|
#
|
|
|
|
-- echo # Test 01
|
|
-- sorted_result
|
|
SELECT * FROM information_schema.table_constraints
|
|
WHERE table_schema = 'mysql' AND table_name != 'ndb_binlog_index'
|
|
ORDER BY table_schema,table_name,constraint_name COLLATE utf8_general_ci;
|
|
|
|
-- echo # Test 03
|
|
SET optimizer_switch = 'derived_merge=off';
|
|
-- sorted_result
|
|
SELECT DISTINCT
|
|
alias1.`col_int` AS field1,
|
|
alias1.`pk` AS field2,
|
|
alias1.`col_int` AS field3,
|
|
alias1.`col_int_key` AS field4,
|
|
alias1.`col_int_key` AS field5
|
|
FROM
|
|
view_K AS alias1
|
|
LEFT JOIN
|
|
view_HH AS alias2
|
|
ON
|
|
alias1.`col_varchar_255_latin1` = alias2.`col_varchar_255_utf8_key`
|
|
WHERE alias1.`col_int` IS NULL
|
|
ORDER BY field1;
|
|
SET optimizer_switch = default;
|
|
|
|
-- echo # Test 04
|
|
-- sorted_result
|
|
SELECT
|
|
GRANDPARENT1.`pk` AS g1,
|
|
GRANDPARENT1.`col_datetime_key`
|
|
FROM
|
|
CC AS GRANDPARENT1
|
|
LEFT JOIN
|
|
CC AS GRANDPARENT2
|
|
USING (`col_int_key`)
|
|
WHERE
|
|
GRANDPARENT1.`col_int_key` IN (
|
|
SELECT PARENT1.`col_int_key` AS p1 FROM CC AS PARENT1
|
|
) AND GRANDPARENT1.`pk` <> 2
|
|
HAVING g1 <> 'p'
|
|
ORDER BY GRANDPARENT1.`col_datetime_key`;
|
|
|
|
-- echo # Test 05
|
|
-- sorted_result
|
|
SELECT
|
|
GRANDPARENT1.`col_int_key` AS g1,
|
|
GRANDPARENT1.`col_datetime_key` AS dt
|
|
FROM
|
|
C AS GRANDPARENT1
|
|
LEFT JOIN
|
|
C AS GRANDPARENT2
|
|
ON (GRANDPARENT2.`pk` <> GRANDPARENT1.`pk`)
|
|
WHERE
|
|
(GRANDPARENT1.`pk`, GRANDPARENT1.`pk`) IN (
|
|
SELECT DISTINCT
|
|
PARENT1.`col_int_key` AS p1,
|
|
PARENT1.`col_int_key` AS p2
|
|
FROM
|
|
C AS PARENT1
|
|
LEFT JOIN
|
|
C AS PARENT2
|
|
USING (`col_varchar_key`)
|
|
WHERE
|
|
((PARENT1.`pk` > GRANDPARENT1.`col_int_key`)
|
|
OR ((PARENT1.`col_time_key` <= GRANDPARENT1.`col_time_key`)
|
|
AND (PARENT1.`col_datetime_key` > '2005-02-01')
|
|
)
|
|
)
|
|
ORDER BY PARENT1.`col_int_key`
|
|
)
|
|
AND GRANDPARENT1.`col_varchar_key` <> 'r'
|
|
HAVING g1 <> '13:16:53.053569'
|
|
ORDER BY GRANDPARENT1.`col_datetime_key`;
|
|
|
|
-- echo # Test 06
|
|
-- sorted_result
|
|
(SELECT DISTINCT
|
|
*
|
|
FROM
|
|
`view_table10000_innodb_int_autoinc`
|
|
WHERE
|
|
(`col_varchar_10_key` LIKE CONCAT('Michigan', '%')
|
|
OR `col_varchar_64_key` LIKE CONCAT('why', '%'))
|
|
AND (`col_varchar_64_key` IS NOT NULL
|
|
OR NOT (`col_varchar_64_key` = 'can\'t'))
|
|
OR (`col_smallint_key` IN (1 , 244, 1, 1)
|
|
OR `col_bigint_key` IS NOT NULL)
|
|
AND (`col_bigint_key` IN (1 , - 89)
|
|
OR (`col_bigint_key` != 1))
|
|
AND (`col_varchar_10_key` IS NOT NULL
|
|
AND `col_varchar_10_key` NOT IN ('Maine' , 'x'))
|
|
AND (NOT (`col_bigint_key` = 1)
|
|
AND `col_smallint_key` BETWEEN 1 AND 1 + 125)) UNION DISTINCT (SELECT
|
|
DISTINCT
|
|
*
|
|
FROM
|
|
`view_table10000_innodb_int_autoinc`
|
|
WHERE
|
|
(`col_varchar_10_key` LIKE CONCAT('Michigan', '%')
|
|
OR `col_varchar_64_key` LIKE CONCAT('why', '%'))
|
|
AND (`col_varchar_64_key` IS NOT NULL
|
|
OR NOT (`col_varchar_64_key` = 'can\'t'))
|
|
OR (`col_smallint_key` IN (1 , 244, 1, 1)
|
|
OR `col_bigint_key` IS NOT NULL)
|
|
AND (`col_bigint_key` IN (1 , - 89)
|
|
OR (`col_bigint_key` != 1))
|
|
AND (`col_varchar_10_key` IS NOT NULL
|
|
AND `col_varchar_10_key` NOT IN ('Maine' , 'x'))
|
|
AND (NOT (`col_bigint_key` = 1)
|
|
AND `col_smallint_key` BETWEEN 1 AND 1 + 125));
|
|
|
|
-- echo # Test 07
|
|
SET optimizer_switch = 'derived_merge=off';
|
|
-- sorted_result
|
|
SELECT
|
|
alias2.`col_int_key`, alias2.pk, alias2.`col_varchar_10_latin1_key`
|
|
FROM
|
|
MM AS alias1
|
|
LEFT OUTER JOIN
|
|
view_PP AS alias2
|
|
ON alias1.`col_varchar_10_latin1` = alias2.`col_varchar_10_latin1_key`
|
|
WHERE alias2.`col_int` NOT IN (1);
|
|
SET optimizer_switch = default;
|
|
|
|
-- echo # Test 08
|
|
SET optimizer_switch = 'derived_merge=on';
|
|
-- sorted_result
|
|
SELECT
|
|
alias2.`col_int_key`
|
|
FROM
|
|
MM AS alias1
|
|
LEFT OUTER JOIN
|
|
view_PP AS alias2
|
|
ON alias1.`col_varchar_10_latin1` = alias2.`col_varchar_10_latin1_key`
|
|
WHERE alias2.`col_int` NOT IN (1);
|
|
SET optimizer_switch = default;
|
|
|
|
-- echo # Test 09
|
|
SET optimizer_switch = 'derived_merge=off';
|
|
-- sorted_result
|
|
SELECT table1.pk
|
|
FROM view_D AS table1
|
|
LEFT JOIN D AS table2 ON table1.col_int_key = table2.col_int_key
|
|
WHERE table1.col_int_key IS NULL;
|
|
|
|
-- echo # Test 10
|
|
SET optimizer_switch = 'derived_merge=on';
|
|
-- sorted_result
|
|
SELECT table1.pk
|
|
FROM view_D AS table1
|
|
LEFT JOIN D AS table2 ON table1.col_int_key = table2.col_int_key
|
|
WHERE table1.col_int_key IS NULL;
|
|
|
|
-- echo # Test 11
|
|
CREATE TABLE t1 (
|
|
pk int(11) NOT NULL DEFAULT '0',
|
|
col_int_key int(11) DEFAULT NULL,
|
|
col_varchar_key varchar(1) DEFAULT NULL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
|
|
INSERT INTO t1 VALUES
|
|
(22,0,NULL),
|
|
(17,9,NULL),
|
|
(29,8,'c'),
|
|
(23,4,'d'),
|
|
(11,7,'d'),
|
|
(26,NULL,'f'),
|
|
(13,7,'f'),
|
|
(24,8,'g'),
|
|
(28,NULL,'j'),
|
|
(16,1,'m'),
|
|
(20,2,'m'),
|
|
(18,2,'o'),
|
|
(27,0,'p'),
|
|
(21,4,'q'),
|
|
(12,1,'r'),
|
|
(15,NULL,'u'),
|
|
(19,9,'w'),
|
|
(25,NULL,'x'),
|
|
(10,8,'x'),
|
|
(14,9,'y');
|
|
ANALYZE TABLE t1;
|
|
-- sorted_result
|
|
SELECT *
|
|
FROM (
|
|
SELECT DISTINCT SUBQUERY1_t1.*
|
|
FROM (
|
|
t1 AS SUBQUERY1_t1
|
|
LEFT OUTER JOIN
|
|
t1 AS SUBQUERY1_t2
|
|
ON (SUBQUERY1_t2.`pk` = SUBQUERY1_t1.`col_int_key`)
|
|
)
|
|
) AS table1
|
|
WHERE table1.`col_varchar_key` IS NULL;
|
|
DROP TABLE t1;
|
|
|
|
-- echo # Test 12
|
|
CREATE TABLE t1 (
|
|
id INT NOT NULL AUTO_INCREMENT,
|
|
c1 CHAR(60) NOT NULL,
|
|
c2 CHAR(60),
|
|
PRIMARY KEY (id)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
|
|
INSERT INTO t1 (c1, c2) VALUES
|
|
('abcdefghij', 'ABCDEFGHIJ'),
|
|
('mnopqrstuv', 'MNOPQRSTUV');
|
|
ANALYZE TABLE t1;
|
|
-- sorted_result
|
|
SELECT DISTINCT c1, c2 FROM t1 WHERE id BETWEEN 1 And 2 ORDER BY 1;
|
|
DROP TABLE t1;
|
|
|
|
#
|
|
|
|
-- disable_query_log
|
|
-- disable_result_log
|
|
if ($is_debug) {
|
|
SET GLOBAL debug = default;
|
|
}
|
|
-- enable_result_log
|
|
-- enable_query_log
|
|
|
|
SET SESSION internal_tmp_mem_storage_engine = default;
|
|
|
|
USE test;
|
|
DROP DATABASE temptable_test;
|
|
|