1# This file contains tests moved from information_schema.test and
2# information_schema_db.test whose results depends on which engines are
3# available (since these engines inject tables into INFORMATION_SCHEMA).
4
5--source include/not_embedded.inc
6--source include/have_innodb.inc
7--source include/have_perfschema.inc
8--source include/not_staging.inc
9
10use INFORMATION_SCHEMA;
11--replace_result Tables_in_INFORMATION_SCHEMA Tables_in_information_schema
12--sorted_result
13show tables;
14
15#
16# Bug#18925: subqueries with MIN/MAX functions on INFORMATION_SCHEMA
17#
18
19--sorted_result
20SELECT t.table_name, c1.column_name
21  FROM information_schema.tables t
22       INNER JOIN
23       information_schema.columns c1
24       ON t.table_schema = c1.table_schema AND
25          t.table_name = c1.table_name
26  WHERE t.table_schema = 'information_schema' AND
27        c1.ordinal_position =
28        ( SELECT COALESCE(MIN(c2.ordinal_position),1)
29            FROM information_schema.columns c2
30            WHERE c2.table_schema = t.table_schema AND
31                  c2.table_name = t.table_name AND
32                  c2.column_name LIKE '%SCHEMA%'
33        ) order by t.table_name;
34--sorted_result
35SELECT t.table_name, c1.column_name
36  FROM information_schema.tables t
37       INNER JOIN
38       information_schema.columns c1
39       ON t.table_schema = c1.table_schema AND
40          t.table_name = c1.table_name
41  WHERE t.table_schema = 'information_schema' AND
42        c1.ordinal_position =
43        ( SELECT COALESCE(MIN(c2.ordinal_position),1)
44            FROM information_schema.columns c2
45            WHERE c2.table_schema = 'information_schema' AND
46                  c2.table_name = t.table_name AND
47                  c2.column_name LIKE '%SCHEMA%'
48        ) order by t.table_name;
49
50#
51# Bug#24630  Subselect query crashes mysqld
52#
53select 1 as "must be 1" from information_schema.tables  where "ACCOUNTS"=
54(select cast(table_name as char)  from information_schema.tables
55 order by table_name limit 1) limit 1;
56
57select t.table_name, group_concat(t.table_schema, '.', t.table_name),
58       count(*) as num1
59from information_schema.tables t
60inner join information_schema.columns c1
61on t.table_schema = c1.table_schema AND t.table_name = c1.table_name
62where t.table_schema = 'information_schema' and
63        c1.ordinal_position =
64        (select isnull(c2.column_type) -
65         isnull(group_concat(c2.table_schema, '.', c2.table_name)) +
66         count(*) as num
67         from information_schema.columns c2 where
68         c2.table_schema='information_schema' and
69         (c2.column_type = 'varchar(7)' or c2.column_type = 'varchar(20)')
70          group by c2.column_type order by num limit 1)
71group by t.table_name order by num1, t.table_name;
72
73#
74# Bug #19147: mysqlshow INFORMATION_SCHEMA does not work
75#
76--sorted_result
77--exec $MYSQL_SHOW information_schema
78--sorted_result
79--exec $MYSQL_SHOW INFORMATION_SCHEMA
80--sorted_result
81--exec $MYSQL_SHOW inf_rmation_schema
82
83#
84# Bug #9404  information_schema: Weird error messages
85# with SELECT SUM() ... GROUP BY queries
86#
87SELECT table_schema, count(*) FROM information_schema.TABLES WHERE table_schema IN ('mysql', 'INFORMATION_SCHEMA', 'test', 'mysqltest') GROUP BY TABLE_SCHEMA;
88