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
            lucianobarcaro Luciano Barcaro created issue -
            lucianobarcaro Luciano Barcaro made changes -
            Field Original Value New Value
            Environment Gentoo linux, Mariadb-5.5.31-log Gentoo linux - Mariadb-5.5.31-log
            Windows 7 - MariaDB-5.5.28
            elenst Elena Stepanova made changes -
            Assignee Elena Stepanova [ elenst ]
            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 made changes -
            Fix Version/s 10.0.6 [ 13202 ]
            Fix Version/s 5.5.32 [ 13000 ]
            Affects Version/s 5.1.67 [ 12100 ]
            Affects Version/s 5.2.14 [ 12101 ]
            Affects Version/s 5.3.12 [ 12000 ]
            Assignee Elena Stepanova [ elenst ]
            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';
            		 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
            {code:sql}
            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';
            {code}
            Labels MariaDB_5.5 upstream
            elenst Elena Stepanova made changes -
            elenst Elena Stepanova made changes -
            Affects Version/s 10.0.2 [ 11900 ]
            serg Sergei Golubchik made changes -
            Fix Version/s 5.5.33 [ 13300 ]
            Fix Version/s 5.5.32 [ 13000 ]
            serg Sergei Golubchik made changes -
            Fix Version/s 5.5.34 [ 13500 ]
            Fix Version/s 5.5.33 [ 13300 ]
            serg Sergei Golubchik made changes -
            Fix Version/s 5.5.34 [ 13700 ]
            Fix Version/s 5.5.33a [ 13500 ]
            serg Sergei Golubchik made changes -
            Fix Version/s 10.0.7 [ 14100 ]
            Fix Version/s 10.0.6 [ 13202 ]
            serg Sergei Golubchik made changes -
            Fix Version/s 5.5.35 [ 14000 ]
            Fix Version/s 5.5.34 [ 13700 ]
            serg Sergei Golubchik made changes -
            Fix Version/s 10.0.8 [ 14200 ]
            Fix Version/s 10.0.7 [ 14100 ]
            serg Sergei Golubchik made changes -
            Fix Version/s 5.5.36 [ 14600 ]
            Fix Version/s 5.5.35 [ 14000 ]
            serg Sergei Golubchik made changes -
            Fix Version/s 10.0.9 [ 14400 ]
            Fix Version/s 10.0.8 [ 14200 ]
            serg Sergei Golubchik made changes -
            Fix Version/s 5.5.37 [ 15000 ]
            Fix Version/s 5.5.36 [ 14600 ]
            serg Sergei Golubchik made changes -
            Fix Version/s 10.0.10 [ 14500 ]
            Fix Version/s 10.0.9 [ 14400 ]
            serg Sergei Golubchik made changes -
            Fix Version/s 10.0.11 [ 15200 ]
            Fix Version/s 10.0.10 [ 14500 ]
            serg Sergei Golubchik made changes -
            Fix Version/s 5.5.38 [ 15400 ]
            Fix Version/s 5.5.37 [ 15000 ]
            serg Sergei Golubchik made changes -
            Fix Version/s 10.0.12 [ 15201 ]
            Fix Version/s 10.0.11 [ 15200 ]
            serg Sergei Golubchik made changes -
            Fix Version/s 5.5.39 [ 15800 ]
            Fix Version/s 5.5.38 [ 15400 ]
            serg Sergei Golubchik made changes -
            Workflow defaullt [ 27643 ] MariaDB v2 [ 44078 ]
            serg Sergei Golubchik made changes -
            Fix Version/s 10.0.13 [ 16000 ]
            Fix Version/s 10.0.12 [ 15201 ]
            ratzpo Rasmus Johansson (Inactive) made changes -
            Workflow MariaDB v2 [ 44078 ] MariaDB v3 [ 63737 ]
            serg Sergei Golubchik made changes -
            Workflow MariaDB v3 [ 63737 ] MariaDB v4 [ 139513 ]
            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)
            elenst Elena Stepanova made changes -
            elenst Elena Stepanova made changes -
            Component/s Information Schema [ 14413 ]
            Fix Version/s 10.8 [ 26121 ]
            Affects Version/s 10.2 [ 14601 ]
            Affects Version/s 10.3 [ 22126 ]
            Affects Version/s 10.4 [ 22408 ]
            Affects Version/s 10.5 [ 23123 ]
            Affects Version/s 10.6 [ 24028 ]
            Affects Version/s 10.7 [ 24805 ]
            Assignee Sergei Golubchik [ serg ]
            Labels upstream upstream-fixed
            Priority Minor [ 4 ] Critical [ 2 ]
            marko Marko Mäkelä made changes -
            marko Marko Mäkelä made changes -
            serg Sergei Golubchik made changes -
            Fix Version/s 5.5 [ 15800 ]
            Fix Version/s 10.0 [ 16000 ]
            serg Sergei Golubchik made changes -
            Summary select returns null for information_schema.statistics.collation field. select returns null for information_schema.statistics.collation field
            serg Sergei Golubchik made changes -
            Status Open [ 1 ] In Progress [ 3 ]
            serg Sergei Golubchik made changes -
            Status In Progress [ 3 ] In Testing [ 10301 ]
            serg Sergei Golubchik made changes -
            Fix Version/s 10.2 [ 14601 ]
            Fix Version/s 10.3 [ 22126 ]
            Fix Version/s 10.4 [ 22408 ]
            Fix Version/s 10.5 [ 23123 ]
            Fix Version/s 10.6 [ 24028 ]
            Fix Version/s 10.8 [ 26121 ]
            serg Sergei Golubchik made changes -
            Fix Version/s 10.2.42 [ 26803 ]
            Fix Version/s 10.3.33 [ 26805 ]
            Fix Version/s 10.4.23 [ 26807 ]
            Fix Version/s 10.5.14 [ 26809 ]
            Fix Version/s 10.6.6 [ 26811 ]
            Fix Version/s 10.2 [ 14601 ]
            Fix Version/s 10.3 [ 22126 ]
            Fix Version/s 10.4 [ 22408 ]
            Fix Version/s 10.5 [ 23123 ]
            Fix Version/s 10.6 [ 24028 ]
            Resolution Fixed [ 1 ]
            Status In Testing [ 10301 ] Closed [ 6 ]

            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.