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

Erroneous Aria Index / Optimizer behaviour

    XMLWordPrintable

Details

    Description

      We stumbled upon a strange problem which seems to be related to either an index or an optimizer error which is repeatable but not straightforward to reproduce. For purpose of this bugreport we were able to bring the case down to a simple table:

      CREATE TABLE `rt_sum_fm23` (
      `route` varchar(20) NOT NULL DEFAULT '',
      `timestamp` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
      PRIMARY KEY (`route`,`timestamp`)
      ) ENGINE=Aria DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1 TRANSACTIONAL=1

      which contains a little short of 1.900.000 records. This is relevant as the experienced behaviour seems related to having a certain number of records. This table contains records with 364 distinct entries for 'route' and per route a number of timestamped records. Now see this:

      > select route, min(timestamp), max(timestamp) from rt_sum_fm23 where route = 'N201__N205_ZAV';

      route min(timestamp) max(timestamp)
      N201__N205_ZAV 2020-06-07 17:59:00 2020-06-12 23:59:00

      So there is (at least) one record with a timestamp on June 7th, which is supported by the following:

      > select * from rt_sum_fm23 where route = 'N201__N205_ZAV' order by timestamp asc limit 5;

      route timestamp
      N201__N205_ZAV 2020-06-07 17:59:00
      N201__N205_ZAV 2020-06-07 18:00:00
      N201__N205_ZAV 2020-06-07 18:01:00
      N201__N205_ZAV 2020-06-07 18:02:00
      N201__N205_ZAV 2020-06-07 18:03:00

      > explain select * from rt_sum_fm23 where route = 'N201__N205_ZAV' order by timestamp asc limit 5;

      id select_type table type possible_keys key key_len ref rows Extra
      1 SIMPLE rt_sum_fm23 ref PRIMARY PRIMARY 22 const 7290 Using where; Using index

      However - and this is the strange behaviour - this is what happens if we add a condition to that where:

      > select * from rt_sum_fm23 where route = 'N201__N205_ZAV' and timestamp < 20200608000000 order by timestamp asc limit 5;
      Empty set (0.00 sec)

      > explain select * from rt_sum_fm23 where route = 'N201__N205_ZAV' and timestamp < 20200608000000 order by timestamp asc limit 5;

      id select_type table type possible_keys key key_len ref rows Extra
      1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables

      Repairing the table results in either the problem remaining the same,, or it shifts (other 'routes' experience the same issue). We have made an export (dump to SQL) and imported that on two different hosts what resulted in the same experience (even the same route). The SQL dump is attached for your benefit.

      We have done numerous tests and the behavior is repeatable.

      edit: removed some incorrect markup from the text, only editorial changes

      Attachments

        Activity

          People

            monty Michael Widenius
            Frank_VID Frank Maas
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

              Created:
              Updated:

              Git Integration

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