[MDEV-4621] select returns null for information_schema.statistics.collation field Created: 2013-06-06  Updated: 2022-01-14  Resolved: 2022-01-14

Status: Closed
Project: MariaDB Server
Component/s: Information Schema
Affects Version/s: 10.0.2, 5.5.31, 5.1.67, 5.2.14, 5.3.12, 10.2, 10.3, 10.4, 10.5, 10.6, 10.7
Fix Version/s: 10.2.42, 10.3.33, 10.4.23, 10.5.14, 10.6.6

Type: Bug Priority: Critical
Reporter: Luciano Barcaro Assignee: Sergei Golubchik
Resolution: Fixed Votes: 0
Labels: upstream-fixed
Environment:

Gentoo linux - Mariadb-5.5.31-log
Windows 7 - MariaDB-5.5.28

Issue Links:
Relates
relates to MDEV-13756 Implement descending index: KEY (a DE... Closed

 Description   

Hi,
I'm trying to get index info from information_schema.statistics
but, if I try to get collation field, it always returns null.

Test case

create table test.some_table (field1 int unsigned not null, field2 varchar(64), primary key (field1), key(field2));
 
 
 
/* collation with their correct value */
 
select * from information_schema.STATISTICS where table_name='some_table' and table_schema='test';
 
 
 
/* collation is null here */
 
select table_schema, table_name, index_name, column_name, collation
 
from information_schema.STATISTICS
 
where table_schema='test' and table_name='some_table';
 
 
 
/* collation (quoted) is null here */
 
select table_schema, table_name, index_name, column_name, `collation`
 
from information_schema.STATISTICS
 
where table_schema='test' and table_name='some_table';



 Comments   
Comment by Elena Stepanova [ 2013-06-06 ]

Also reproducible on all of MySQL 5.1 - 5.7 (MySQL bug #65121)

Comment by Elena Stepanova [ 2022-01-10 ]

Now when with the development of MDEV-13756 INFORMATION_SCHEMA.STATISTICS.COLLATION starts meaning something, I think it needs to be fixed.

preview-10.8-MDEV-13756-desc-indexes c13c1079b

 
MariaDB [test]> create or replace table t (a int, b int, key(a), key(b desc));
 
Query OK, 0 rows affected (0.107 sec)
 
 
 
MariaDB [test]> select index_name, column_name, collation from information_schema.statistics where table_name = 't';
 
+------------+-------------+-----------+
 
| index_name | column_name | collation |
 
+------------+-------------+-----------+
 
| a          | a           | NULL      |
 
| b          | b           | NULL      |
 
+------------+-------------+-----------+
 
2 rows in set (0.002 sec)

The upstream bug was fixed in 8.0.

MySQL 8.0

 
MySQL [test]> create  table t (a int, b int, key(a), key(b desc));
 
Query OK, 0 rows affected (0.244 sec)
 
 
 
MySQL [test]> select index_name, column_name, collation from information_schema.statistics where table_name = 't';
 
+------------+-------------+-----------+
 
| INDEX_NAME | COLUMN_NAME | COLLATION |
 
+------------+-------------+-----------+
 
| a          | a           | A         |
 
| b          | b           | D         |
 
+------------+-------------+-----------+
 
2 rows in set (0.007 sec)

Generated at Thu Feb 08 06:57:48 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.