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

[PATCH] engine-independent-stats column_stats has limited values for max/min values

    XMLWordPrintable

Details

    • 10.0.20

    Description

      Engine-independent stats can't parse some values into min/max_value in column stats. The result is a NON NULL empty string. I suspect query plans based on this information won't work too well.

      MariaDB [logsleads]> analyze table GeoIPOrg persistent for all;
      +--------------------+---------+----------+---------------------------------------------------------------------------+
      | Table              | Op      | Msg_type | Msg_text                                                                  |
      +--------------------+---------+----------+---------------------------------------------------------------------------+
      | logsleads.GeoIPOrg | analyze | status   | Engine-independent statistics collected                                   |
      | logsleads.GeoIPOrg | analyze | Warning  | Incorrect string value: '\xD8ybj\xF8r...' for column 'max_value' at row 1 |
      | logsleads.GeoIPOrg | analyze | status   | Table is already up to date                                               |
      +--------------------+---------+----------+---------------------------------------------------------------------------+
      3 rows in set (9.52 sec)
       
       
      | GeoIPOrg | CREATE TABLE `GeoIPOrg` (
        `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
        `ipv4n_start` int(10) unsigned NOT NULL,
        `ipv4n_end` int(10) unsigned NOT NULL,
        `org_name` varchar(100) NOT NULL,
        `bucket` smallint(5) unsigned NOT NULL,
        PRIMARY KEY (`id`),
        UNIQUE KEY `ipv4n_start` (`ipv4n_start`,`ipv4n_end`),
        KEY `bucket` (`bucket`)
      ) ENGINE=MyISAM AUTO_INCREMENT=1292213 DEFAULT CHARSET=latin1 |
       
      MariaDB [mysql]> select *,IF(max_value IS NULL,'NULL','NOT NULL') as max_value_null,hex(max_value) as max_value_hex,length(max_value) as max_value_len from column_stats where db_name='logsleads' and table_name='GeoIPOrg';
      +-----------+------------+-------------+--------------------------------------------------+------------+-------------+------------+---------------+-----------+-----------+-----------+----------------+----------------------+---------------+
      | db_name   | table_name | column_name | min_value                                        | max_value  | nulls_ratio | avg_length | avg_frequency | hist_size | hist_type | histogram | max_value_null | max_value_hex        | max_value_len |
      +-----------+------------+-------------+--------------------------------------------------+------------+-------------+------------+---------------+-----------+-----------+-----------+----------------+----------------------+---------------+
      | logsleads | GeoIPOrg   | bucket      | 256                                              | 57343      |      0.0000 |     2.0000 |       49.4683 |         0 | NULL      | NULL      | NOT NULL       | 3537333433           |             5 |
      | logsleads | GeoIPOrg   | id          | 1                                                | 1292212    |      0.0000 |     4.0000 |        1.0000 |         0 | NULL      | NULL      | NOT NULL       | 31323932323132       |             7 |
      | logsleads | GeoIPOrg   | ipv4n_end   | 16777471                                         | 3758096127 |      0.0000 |     4.0000 |        1.0000 |         0 | NULL      | NULL      | NOT NULL       | 33373538303936313237 |            10 |
      | logsleads | GeoIPOrg   | ipv4n_start | 16777216                                         | 3758095872 |      0.0000 |     4.0000 |        1.0000 |         0 | NULL      | NULL      | NOT NULL       | 33373538303935383732 |            10 |
      | logsleads | GeoIPOrg   | org_name    | ! atlngahpla1_g8/7 to atlngahpla2_g8/7 ospf area |            |      0.0000 |    21.6174 |        2.6580 |         0 | NULL      | NULL      | NOT NULL       |                      |             0 |
      +-----------+------------+-------------+--------------------------------------------------+------------+-------------+------------+---------------+-----------+-----------+-----------+----------------+----------------------+---------------+
       
      select org_name,hex(org_name) from GeoIPOrg order by org_name desc limit 1;
      +------------------+--------------------------+
      | org_name         | hex(org_name)            |
      +------------------+--------------------------+
      | Øybjørg_sæbø     | D879626AF872675F73E662F8 |
      +------------------+--------------------------+

      Attachments

        Issue Links

          Activity

            People

              psergei Sergei Petrunia
              danblack Daniel Black
              Votes:
              1 Vote for this issue
              Watchers:
              5 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.