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.
462 lines
11 KiB
462 lines
11 KiB
CREATE DATABASE temptable_test;
|
|
USE temptable_test;
|
|
# Test 01
|
|
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;
|
|
CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_SCHEMA TABLE_NAME CONSTRAINT_TYPE ENFORCED
|
|
def mysql PRIMARY mysql columns_priv PRIMARY KEY YES
|
|
def mysql PRIMARY mysql component PRIMARY KEY YES
|
|
def mysql PRIMARY mysql db PRIMARY KEY YES
|
|
def mysql PRIMARY mysql default_roles PRIMARY KEY YES
|
|
def mysql PRIMARY mysql engine_cost PRIMARY KEY YES
|
|
def mysql PRIMARY mysql func PRIMARY KEY YES
|
|
def mysql PRIMARY mysql global_grants PRIMARY KEY YES
|
|
def mysql PRIMARY mysql gtid_executed PRIMARY KEY YES
|
|
def mysql PRIMARY mysql help_category PRIMARY KEY YES
|
|
def mysql PRIMARY mysql help_keyword PRIMARY KEY YES
|
|
def mysql PRIMARY mysql help_relation PRIMARY KEY YES
|
|
def mysql PRIMARY mysql help_topic PRIMARY KEY YES
|
|
def mysql PRIMARY mysql innodb_index_stats PRIMARY KEY YES
|
|
def mysql PRIMARY mysql innodb_table_stats PRIMARY KEY YES
|
|
def mysql PRIMARY mysql password_history PRIMARY KEY YES
|
|
def mysql PRIMARY mysql plugin PRIMARY KEY YES
|
|
def mysql PRIMARY mysql procs_priv PRIMARY KEY YES
|
|
def mysql PRIMARY mysql proxies_priv PRIMARY KEY YES
|
|
def mysql PRIMARY mysql role_edges PRIMARY KEY YES
|
|
def mysql PRIMARY mysql server_cost PRIMARY KEY YES
|
|
def mysql PRIMARY mysql servers PRIMARY KEY YES
|
|
def mysql PRIMARY mysql slave_master_info PRIMARY KEY YES
|
|
def mysql PRIMARY mysql slave_relay_log_info PRIMARY KEY YES
|
|
def mysql PRIMARY mysql slave_worker_info PRIMARY KEY YES
|
|
def mysql PRIMARY mysql tables_priv PRIMARY KEY YES
|
|
def mysql PRIMARY mysql time_zone PRIMARY KEY YES
|
|
def mysql PRIMARY mysql time_zone_leap_second PRIMARY KEY YES
|
|
def mysql PRIMARY mysql time_zone_name PRIMARY KEY YES
|
|
def mysql PRIMARY mysql time_zone_transition PRIMARY KEY YES
|
|
def mysql PRIMARY mysql time_zone_transition_type PRIMARY KEY YES
|
|
def mysql PRIMARY mysql user PRIMARY KEY YES
|
|
def mysql name mysql help_category UNIQUE YES
|
|
def mysql name mysql help_keyword UNIQUE YES
|
|
def mysql name mysql help_topic UNIQUE YES
|
|
# Test 03
|
|
SET optimizer_switch = 'derived_merge=off';
|
|
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;
|
|
field1 field2 field3 field4 field5
|
|
NULL 1 NULL 5 5
|
|
NULL 13 NULL NULL NULL
|
|
NULL 15 NULL NULL NULL
|
|
NULL 16 NULL 536608768 536608768
|
|
NULL 2 NULL 315490304 315490304
|
|
NULL 7 NULL NULL NULL
|
|
SET optimizer_switch = default;
|
|
# Test 04
|
|
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`;
|
|
g1 col_datetime_key
|
|
10 2005-07-20 00:00:00
|
|
10 2005-07-20 00:00:00
|
|
10 2005-07-20 00:00:00
|
|
11 2007-10-06 17:56:40
|
|
11 2007-10-06 17:56:40
|
|
12 NULL
|
|
12 NULL
|
|
13 2007-12-23 05:17:49
|
|
13 2007-12-23 05:17:49
|
|
14 NULL
|
|
14 NULL
|
|
14 NULL
|
|
16 NULL
|
|
16 NULL
|
|
17 2003-02-07 09:50:39
|
|
17 2003-02-07 09:50:39
|
|
17 2003-02-07 09:50:39
|
|
18 2003-08-04 23:00:25
|
|
18 2003-08-04 23:00:25
|
|
19 2009-08-07 12:57:18
|
|
19 2009-08-07 12:57:18
|
|
19 2009-08-07 12:57:18
|
|
20 1900-01-01 00:00:00
|
|
20 1900-01-01 00:00:00
|
|
21 2001-06-17 01:34:03
|
|
21 2001-06-17 01:34:03
|
|
22 NULL
|
|
22 NULL
|
|
23 1900-01-01 00:00:00
|
|
23 1900-01-01 00:00:00
|
|
24 NULL
|
|
24 NULL
|
|
24 NULL
|
|
27 2008-07-18 00:00:00
|
|
27 2008-07-18 00:00:00
|
|
29 2003-06-13 23:19:49
|
|
29 2003-06-13 23:19:49
|
|
29 2003-06-13 23:19:49
|
|
Warning 1292 Truncated incorrect DOUBLE value: 'p'
|
|
Warnings:
|
|
# Test 05
|
|
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`;
|
|
g1 dt
|
|
0 2001-11-08 21:02:12
|
|
0 2001-11-08 21:02:12
|
|
0 2001-11-08 21:02:12
|
|
0 2001-11-08 21:02:12
|
|
0 2001-11-08 21:02:12
|
|
0 2001-11-08 21:02:12
|
|
0 2001-11-08 21:02:12
|
|
0 2001-11-08 21:02:12
|
|
0 2001-11-08 21:02:12
|
|
0 2001-11-08 21:02:12
|
|
0 2001-11-08 21:02:12
|
|
0 2001-11-08 21:02:12
|
|
0 2001-11-08 21:02:12
|
|
0 2001-11-08 21:02:12
|
|
0 2001-11-08 21:02:12
|
|
0 2001-11-08 21:02:12
|
|
0 2001-11-08 21:02:12
|
|
0 2001-11-08 21:02:12
|
|
0 2001-11-08 21:02:12
|
|
1 2007-07-12 00:00:00
|
|
1 2007-07-12 00:00:00
|
|
1 2007-07-12 00:00:00
|
|
1 2007-07-12 00:00:00
|
|
1 2007-07-12 00:00:00
|
|
1 2007-07-12 00:00:00
|
|
1 2007-07-12 00:00:00
|
|
1 2007-07-12 00:00:00
|
|
1 2007-07-12 00:00:00
|
|
1 2007-07-12 00:00:00
|
|
1 2007-07-12 00:00:00
|
|
1 2007-07-12 00:00:00
|
|
1 2007-07-12 00:00:00
|
|
1 2007-07-12 00:00:00
|
|
1 2007-07-12 00:00:00
|
|
1 2007-07-12 00:00:00
|
|
1 2007-07-12 00:00:00
|
|
1 2007-07-12 00:00:00
|
|
1 2007-07-12 00:00:00
|
|
4 1900-01-01 00:00:00
|
|
4 1900-01-01 00:00:00
|
|
4 1900-01-01 00:00:00
|
|
4 1900-01-01 00:00:00
|
|
4 1900-01-01 00:00:00
|
|
4 1900-01-01 00:00:00
|
|
4 1900-01-01 00:00:00
|
|
4 1900-01-01 00:00:00
|
|
4 1900-01-01 00:00:00
|
|
4 1900-01-01 00:00:00
|
|
4 1900-01-01 00:00:00
|
|
4 1900-01-01 00:00:00
|
|
4 1900-01-01 00:00:00
|
|
4 1900-01-01 00:00:00
|
|
4 1900-01-01 00:00:00
|
|
4 1900-01-01 00:00:00
|
|
4 1900-01-01 00:00:00
|
|
4 1900-01-01 00:00:00
|
|
4 1900-01-01 00:00:00
|
|
62 2008-01-03 10:33:32
|
|
62 2008-01-03 10:33:32
|
|
62 2008-01-03 10:33:32
|
|
62 2008-01-03 10:33:32
|
|
62 2008-01-03 10:33:32
|
|
62 2008-01-03 10:33:32
|
|
62 2008-01-03 10:33:32
|
|
62 2008-01-03 10:33:32
|
|
62 2008-01-03 10:33:32
|
|
62 2008-01-03 10:33:32
|
|
62 2008-01-03 10:33:32
|
|
62 2008-01-03 10:33:32
|
|
62 2008-01-03 10:33:32
|
|
62 2008-01-03 10:33:32
|
|
62 2008-01-03 10:33:32
|
|
62 2008-01-03 10:33:32
|
|
62 2008-01-03 10:33:32
|
|
62 2008-01-03 10:33:32
|
|
62 2008-01-03 10:33:32
|
|
7 1900-01-01 00:00:00
|
|
7 1900-01-01 00:00:00
|
|
7 1900-01-01 00:00:00
|
|
7 1900-01-01 00:00:00
|
|
7 1900-01-01 00:00:00
|
|
7 1900-01-01 00:00:00
|
|
7 1900-01-01 00:00:00
|
|
7 1900-01-01 00:00:00
|
|
7 1900-01-01 00:00:00
|
|
7 1900-01-01 00:00:00
|
|
7 1900-01-01 00:00:00
|
|
7 1900-01-01 00:00:00
|
|
7 1900-01-01 00:00:00
|
|
7 1900-01-01 00:00:00
|
|
7 1900-01-01 00:00:00
|
|
7 1900-01-01 00:00:00
|
|
7 1900-01-01 00:00:00
|
|
7 1900-01-01 00:00:00
|
|
7 1900-01-01 00:00:00
|
|
7 2005-04-04 01:21:01
|
|
7 2005-04-04 01:21:01
|
|
7 2005-04-04 01:21:01
|
|
7 2005-04-04 01:21:01
|
|
7 2005-04-04 01:21:01
|
|
7 2005-04-04 01:21:01
|
|
7 2005-04-04 01:21:01
|
|
7 2005-04-04 01:21:01
|
|
7 2005-04-04 01:21:01
|
|
7 2005-04-04 01:21:01
|
|
7 2005-04-04 01:21:01
|
|
7 2005-04-04 01:21:01
|
|
7 2005-04-04 01:21:01
|
|
7 2005-04-04 01:21:01
|
|
7 2005-04-04 01:21:01
|
|
7 2005-04-04 01:21:01
|
|
7 2005-04-04 01:21:01
|
|
7 2005-04-04 01:21:01
|
|
7 2005-04-04 01:21:01
|
|
Warning 1292 Truncated incorrect DOUBLE value: '13:16:53.053569'
|
|
Warnings:
|
|
# Test 06
|
|
(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));
|
|
col_smallint col_varchar_10 col_bigint pk col_smallint_key col_varchar_64_key col_varchar_10_key col_varchar_64 col_bigint_key
|
|
0 Wyoming -5797540095309381632 6 32 u a New York 30584
|
|
10485 New Hampsh NULL 18 4 m y j 103
|
|
22872 toqiyowask 0 2 54 you oqiyowaskl really 2739595948324814848
|
|
32767 g 6 8 111 hsfysdlfwvzywlrokuydyjybesvqsaxfbmnaiejcfhlazotdcasrmxrulycvtifo l s 0
|
|
80 qi -6770880564774830080 3 6 like Alaska iyowasklhsfysdlfwvzywlrokuydyjybesvqsaxfbmnaiejcfhl 40655
|
|
# Test 07
|
|
SET optimizer_switch = 'derived_merge=off';
|
|
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);
|
|
col_int_key pk col_varchar_10_latin1_key
|
|
1052704768 28 i
|
|
5 29 c
|
|
727187456 7 i
|
|
8 36 I
|
|
NULL 10 i
|
|
NULL 23 i
|
|
NULL 57 i
|
|
NULL 76 had
|
|
NULL 94 z
|
|
NULL 94 z
|
|
SET optimizer_switch = default;
|
|
# Test 08
|
|
SET optimizer_switch = 'derived_merge=on';
|
|
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);
|
|
col_int_key
|
|
1052704768
|
|
5
|
|
727187456
|
|
8
|
|
NULL
|
|
NULL
|
|
NULL
|
|
NULL
|
|
NULL
|
|
NULL
|
|
SET optimizer_switch = default;
|
|
# Test 09
|
|
SET optimizer_switch = 'derived_merge=off';
|
|
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;
|
|
pk
|
|
10
|
|
19
|
|
27
|
|
37
|
|
43
|
|
52
|
|
53
|
|
56
|
|
82
|
|
84
|
|
85
|
|
86
|
|
9
|
|
# Test 10
|
|
SET optimizer_switch = 'derived_merge=on';
|
|
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;
|
|
pk
|
|
10
|
|
19
|
|
27
|
|
37
|
|
43
|
|
52
|
|
53
|
|
56
|
|
82
|
|
84
|
|
85
|
|
86
|
|
9
|
|
# 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;
|
|
Warnings:
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
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;
|
|
Table Op Msg_type Msg_text
|
|
temptable_test.t1 analyze status OK
|
|
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;
|
|
pk col_int_key col_varchar_key
|
|
17 9 NULL
|
|
22 0 NULL
|
|
DROP TABLE t1;
|
|
# 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;
|
|
Table Op Msg_type Msg_text
|
|
temptable_test.t1 analyze status OK
|
|
SELECT DISTINCT c1, c2 FROM t1 WHERE id BETWEEN 1 And 2 ORDER BY 1;
|
|
c1 c2
|
|
abcdefghij ABCDEFGHIJ
|
|
mnopqrstuv MNOPQRSTUV
|
|
DROP TABLE t1;
|
|
SET SESSION internal_tmp_mem_storage_engine = default;
|
|
USE test;
|
|
DROP DATABASE temptable_test;
|
|
|