1
2
3
4
5
6
7
8
9
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
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
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
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
|
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 replication_asynchronous_connection_failover PRIMARY KEY YES
def mysql PRIMARY mysql replication_asynchronous_connection_failover_managed PRIMARY KEY YES
def mysql PRIMARY mysql replication_group_configuration_version PRIMARY KEY YES
def mysql PRIMARY mysql replication_group_member_actions 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;
CREATE TABLE t1 (c1 VARCHAR(10) COLLATE utf8mb4_bin) ENGINE = InnoDB;
INSERT INTO t1 VALUES (''), (' ');
SELECT DISTINCT(c1) FROM t1;
c1
DROP TABLE t1;
SET SESSION internal_tmp_mem_storage_engine = default;
USE test;
DROP DATABASE temptable_test;
|
