1# suite/funcs_1/datadict/is_columns.inc
2#
3# Auxiliary script to be sourced by
4#    is_columns_is
5#    is_columns_mysql
6#    is_columns_<engine>
7#
8# Purpose:
9#    Check the content of information_schema.columns about tables within certain
10#    database/s.
11#
12# Usage:
13#    The variable $my_where has to
14#    - be set before sourcing this script.
15#    - contain the first part of the WHERE qualification
16#    Example:
17#       let $my_where = WHERE table_schema = 'information_schema'
18#       AND table_name <> 'profiling';
19#       --source suite/funcs_1/datadict/is_columns.inc
20#
21# Author:
22# 2008-01-23 mleich WL#4203 Reorganize and fix the data dictionary tests of
23#                           testsuite funcs_1
24#                   Create this script based on older scripts and new code.
25#
26
27--source suite/funcs_1/datadict/datadict_bug_12777.inc
28
29# The following is needed as embedded server can be compiled with and without
30# privlege tables
31
32eval
33SELECT * FROM information_schema.columns
34$my_where
35ORDER BY table_schema, table_name, column_name;
36
37--echo ##########################################################################
38--echo # Show the quotient of CHARACTER_OCTET_LENGTH and CHARACTER_MAXIMUM_LENGTH
39--echo ##########################################################################
40eval
41SELECT DISTINCT
42       CHARACTER_OCTET_LENGTH / CHARACTER_MAXIMUM_LENGTH AS COL_CML,
43       DATA_TYPE,
44       CHARACTER_SET_NAME,
45       COLLATION_NAME
46FROM information_schema.columns
47$my_where
48AND CHARACTER_OCTET_LENGTH / CHARACTER_MAXIMUM_LENGTH = 1
49ORDER BY CHARACTER_SET_NAME, COLLATION_NAME, COL_CML;
50
51#FIXME 3.2.6.2: check the value 2.0079 tinytext ucs2 ucs2_general_ci
52eval
53SELECT DISTINCT
54       CHARACTER_OCTET_LENGTH / CHARACTER_MAXIMUM_LENGTH AS COL_CML,
55       DATA_TYPE,
56       CHARACTER_SET_NAME,
57       COLLATION_NAME
58FROM information_schema.columns
59$my_where
60AND CHARACTER_OCTET_LENGTH / CHARACTER_MAXIMUM_LENGTH <> 1
61ORDER BY CHARACTER_SET_NAME, COLLATION_NAME, COL_CML;
62
63eval
64SELECT DISTINCT
65       CHARACTER_OCTET_LENGTH / CHARACTER_MAXIMUM_LENGTH AS COL_CML,
66       DATA_TYPE,
67       CHARACTER_SET_NAME,
68       COLLATION_NAME
69FROM information_schema.columns
70$my_where
71      AND CHARACTER_OCTET_LENGTH / CHARACTER_MAXIMUM_LENGTH IS NULL
72ORDER BY CHARACTER_SET_NAME, COLLATION_NAME, COL_CML;
73
74echo --> CHAR(0) is allowed (see manual), and here both CHARACHTER_* values;
75echo --> are 0, which is intended behavior, and the result of 0 / 0 IS NULL;
76--source suite/funcs_1/datadict/datadict_bug_12777.inc
77eval
78SELECT CHARACTER_OCTET_LENGTH / CHARACTER_MAXIMUM_LENGTH AS COL_CML,
79       TABLE_SCHEMA,
80       TABLE_NAME,
81       COLUMN_NAME,
82       DATA_TYPE,
83       CHARACTER_MAXIMUM_LENGTH,
84       CHARACTER_OCTET_LENGTH,
85       CHARACTER_SET_NAME,
86       COLLATION_NAME,
87       COLUMN_TYPE
88FROM information_schema.columns
89$my_where
90ORDER BY TABLE_SCHEMA, TABLE_NAME, ORDINAL_POSITION;
91