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
|
-- source include/have_innodb.inc
-- source include/not_embedded.inc
# check that CSV engine was compiled in, as the result of the test depends
# on the presence of the log tables (which are CSV-based).
--source include/have_csv.inc
--echo #
--echo # Bug#18925: subqueries with MIN/MAX functions on INFORMATION_SCHEMA
--echo #
--sorted_result
SELECT t.table_name, c1.column_name
FROM information_schema.tables t
INNER JOIN
information_schema.columns c1
ON t.table_schema = c1.table_schema AND
t.table_name = c1.table_name
WHERE t.table_schema = 'information_schema' AND
c1.ordinal_position =
( SELECT COALESCE(MIN(c2.ordinal_position),1)
FROM information_schema.columns c2
WHERE c2.table_schema = t.table_schema AND
c2.table_name = t.table_name AND
c2.column_name LIKE '%SCHEMA%'
)
AND t.table_name NOT LIKE 'innodb%' and t.table_name NOT LIKE "OPTIMIZER_TRACE%";
--sorted_result
SELECT t.table_name, c1.column_name
FROM information_schema.tables t
INNER JOIN
information_schema.columns c1
ON t.table_schema = c1.table_schema AND
t.table_name = c1.table_name
WHERE t.table_schema = 'information_schema' AND
c1.ordinal_position =
( SELECT COALESCE(MIN(c2.ordinal_position),1)
FROM information_schema.columns c2
WHERE c2.table_schema = 'information_schema' AND
c2.table_name = t.table_name AND
c2.column_name LIKE '%SCHEMA%'
)
AND t.table_name NOT LIKE 'innodb%' and t.table_name NOT LIKE "OPTIMIZER_TRACE%";
|
