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