Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-4621

select returns null for information_schema.statistics.collation field

Details

    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';

      Attachments

        Issue Links

          relates to

          Task - A task that needs to be done. MDEV-13756 Implement descending index: KEY (a DESC, b ASC)

          • Blocker - Blocks development and/or testing work, production could not run.
          • Closed
          links to

          Web Link MySQL Bug #65121 Inconsistent results for select on INFORMATION_SCHEMA.STATISTICS

          Activity

            Ascending order - Click to sort in descending order
            elenst Elena Stepanova added a comment -

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

            elenst Elena Stepanova added a comment - Also reproducible on all of MySQL 5.1 - 5.7 (MySQL bug #65121)
            elenst Elena Stepanova added a comment - - edited

            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)

            elenst Elena Stepanova added a comment - - edited 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)

            People

              serg Sergei Golubchik
              lucianobarcaro Luciano Barcaro
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.