最近我执行一个SQL的时候,获取information_schema.columns 中对应的表字段,发现tidb直接断开链接,但同样的SQL在mysql数据库中执行就没问题。
后来我逐级验证,发现在order by 的时候,去掉其中一个字段,就没有任何问题,这个字段是自定义的字段给的值是null, 请问一下,tidb 对空值的排序不支持吗?
附上对应的SQL语句:
SELECT
TABLE_SCHEMA TABLE_CAT,
null TABLE_SCHEM,
TABLE_NAME,
COLUMN_NAME,
CASE data_type WHEN ‘bit’ THEN - 7 WHEN ‘tinyblob’ THEN -3
WHEN ‘mediumblob’ THEN -4
WHEN ‘longblob’ THEN -4
WHEN ‘blob’ THEN -4
WHEN ‘tinytext’ THEN 12
WHEN ‘mediumtext’ THEN -1
WHEN ‘longtext’ THEN -1
WHEN ‘text’ THEN -1
WHEN ‘date’ THEN 91
WHEN ‘datetime’ THEN 93
WHEN ‘decimal’ THEN 3
WHEN ‘double’ THEN 8
WHEN ‘enum’ THEN 12
WHEN ‘float’ THEN 7
WHEN ‘int’ THEN IF ( COLUMN_TYPE LIKE ‘%unsigned%’, 4, 4 )
WHEN ‘bigint’ THEN -5
WHEN ‘mediumint’ THEN 4
WHEN ‘null’ THEN 0
WHEN ‘set’ THEN 12
WHEN ‘smallint’ THEN IF( COLUMN_TYPE LIKE ‘%unsigned%’, 5, 5 )
WHEN ‘varchar’ THEN 12
WHEN ‘varbinary’ THEN -3
WHEN ‘char’ THEN 1
WHEN ‘binary’ THEN - 2
WHEN ‘time’ THEN 92
WHEN ‘timestamp’ THEN 93
WHEN ‘tinyint’ THEN IF ( COLUMN_TYPE LIKE ‘tinyint(1)%’,- 7,- 6 )
WHEN ‘year’ THEN 91 ELSE 1111
END DATA_TYPE,
IF (COLUMN_TYPE LIKE ‘tinyint(1)%’,‘BIT’,UCASE(IF(COLUMN_TYPE LIKE ‘%(%)%’,CONCAT(SUBSTRING( COLUMN_TYPE, 1, LOCATE( ‘(’, COLUMN_TYPE ) - 1 ),SUBSTRING( COLUMN_TYPE, 1+ locate( ‘)’, COLUMN_TYPE ) ) ),COLUMN_TYPE ) ) ) TYPE_NAME,
CASE DATA_TYPE WHEN ‘time’ THEN IF( DATETIME_PRECISION = 0, 10, CAST( 11 + DATETIME_PRECISION AS signed INTeger ) )
WHEN ‘date’ THEN 10
WHEN ‘datetime’ THEN IF( DATETIME_PRECISION = 0, 19, CAST( 20 + DATETIME_PRECISION AS signed INTEGER ) )
WHEN ‘timestamp’ THEN IF( DATETIME_PRECISION = 0, 19, CAST( 20 + DATETIME_PRECISION AS signed INTEGER ) ) ELSE IF( NUMERIC_PRECISION IS NULL, LEAST( CHARACTER_MAXIMUM_LENGTH, 2147483647 ), NUMERIC_PRECISION ) END COLUMN_SIZE,
65535 BUFFER_LENGTH,
CONVERT ( CASE DATA_TYPE WHEN ‘year’ THEN NUMERIC_SCALE WHEN ‘tinyint’ THEN 0 ELSE NUMERIC_SCALE END, UNSIGNED INTEGER ) DECIMAL_DIGITS,
10 NUM_PREC_RADIX,
IF( IS_NULLABLE = ‘yes’, 1, 0 ) NULLABLE,
COLUMN_COMMENT REMARKS,
COLUMN_DEFAULT COLUMN_DEF,
0 SQL_DATA_TYPE,
0 SQL_DATETIME_SUB,
LEAST( CHARACTER_OCTET_LENGTH, 2147483647 ) CHAR_OCTET_LENGTH,
ORDINAL_POSITION,
IS_NULLABLE,
NULL SCOPE_CATALOG,
NULL SCOPE_SCHEMA,
NULL SCOPE_TABLE,
NULL SOURCE_DATA_TYPE,
IF( EXTRA = ‘auto_increment’, ‘YES’, ‘NO’ ) IS_AUTOINCREMENT,
IF( EXTRA IN ( ‘VIRTUAL’, ‘PERSISTENT’, ‘VIRTUAL GENERATED’, ‘STORED GENERATED’ ), ‘YES’, ‘NO’ ) IS_GENERATEDCOLUMN
FROM
INFORMATION_SCHEMA.COLUMNS
WHERE
( ISNULL( DATABASE ( ) ) OR ( TABLE_SCHEMA = DATABASE ( ) ) )
and ( TABLE_NAME LIKE ‘m_dim_day’ )
and (1=1)
ORDER BY
TABLE_CAT,
TABLE_SCHEM,
TABLE_NAME,
ORDINAL_POSITION