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

Query results are incorrect when indexes are added.

    XMLWordPrintable

    Details

      Description

      I am using MariaDB for several products.

      When I inserted data in a table with indexes pre-added, I sometimes did not get the expected search results.

      I tried this with Mariadb 10.5.10 on Docker container.

      First, start the MariaDB container on Linux (CentOS 7.9).

      docker run -p 3306:3306 --name test-mariadb -e MARIADB_ROOT_PASSWORD=12345678 -d mariadb:10.5.10
      

      Next, I ran the following on my MacBook.

      lobin@Nagisa relief-map % mysql -h192.168.59.109 -uroot -p12345678 < setup.sql
      mysql: [Warning] Using a password on the command line interface can be insecure.
      lobin@Nagisa relief-map % mysql -h192.168.59.109 -uroot -p12345678 test1 < insert_data.sql
      mysql: [Warning] Using a password on the command line interface can be insecure.
      lobin@Nagisa relief-map % mysql -h192.168.59.109 -uroot -p12345678 --table test1 < select.sql
      mysql: [Warning] Using a password on the command line interface can be insecure.
      +----+----------+---------------+------+-------------+-------------+---------------------+---------------------+---------------------+
      | ID | Facility | prefecture_id | ID   | Facility_ID | damage_type | comment             | regist_date         | update_date         |
      +----+----------+---------------+------+-------------+-------------+---------------------+---------------------+---------------------+
      |  1 | having0  |            27 | NULL |        NULL |        NULL | NULL                | NULL                | NULL                |
      |  2 | having1  |            27 |    1 |           2 |           4 | this is latest.     | 2019-03-05 00:00:00 | 2019-03-06 00:00:00 |
      |  3 | having2  |            27 |    2 |           3 |           4 | this is not latest. | 2019-03-05 00:00:00 | 2019-03-06 00:00:00 |
      +----+----------+---------------+------+-------------+-------------+---------------------+---------------------+---------------------+
      lobin@Nagisa relief-map %
      

      This is not the result I was expecting.

      Next, I removed the index created at the end of `setup.sql` and ran `select.sql` again, and got the expected results.

      lobin@Nagisa relief-map % mysql -h192.168.59.109 -uroot -p12345678 --table test1
      mysql: [Warning] Using a password on the command line interface can be insecure.
      Reading table information for completion of table and column names
      You can turn off this feature to get a quicker startup with -A
       
      Welcome to the MySQL monitor.  Commands end with ; or \g.
      Your MySQL connection id is 42
      Server version: 5.5.5-10.5.10-MariaDB-1:10.5.10+maria~focal mariadb.org binary distribution
       
      Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
       
      Oracle is a registered trademark of Oracle Corporation and/or its
      affiliates. Other names may be trademarks of their respective
      owners.
       
      Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
       
      mysql> DROP INDEX `for_latest_sort` ON `facility2_damages`
          -> ;
      Query OK, 0 rows affected (0.01 sec)
      Records: 0  Duplicates: 0  Warnings: 0
       
      mysql> ^DBye
      lobin@Nagisa relief-map % mysql -h192.168.59.109 -uroot -p12345678 --table test1 < select.sql
      mysql: [Warning] Using a password on the command line interface can be insecure.
      +----+----------+---------------+------+-------------+-------------+------------------+---------------------+---------------------+
      | ID | Facility | prefecture_id | ID   | Facility_ID | damage_type | comment          | regist_date         | update_date         |
      +----+----------+---------------+------+-------------+-------------+------------------+---------------------+---------------------+
      |  1 | having0  |            27 | NULL |        NULL |        NULL | NULL             | NULL                | NULL                |
      |  2 | having1  |            27 |    1 |           2 |           4 | this is latest.  | 2019-03-05 00:00:00 | 2019-03-06 00:00:00 |
      |  3 | having2  |            27 |    3 |           3 |           4 | this is latest2. | 2019-03-06 00:00:00 | 2019-03-05 00:00:00 |
      +----+----------+---------------+------+-------------+-------------+------------------+---------------------+---------------------+
      lobin@Nagisa relief-map %
      

      After that, I re-added the same index, and the correct search results remained.
      It seems that adding the index first and then inserting the data will result in incorrect search results.

      I also tried with MariaDB 10.2.38 and the results were the same.

      I think It might be related to the fact that I'm trying to reference the index in the correlation subquery.

      I tried the same with MySQL 5.7.34 and got the correct results without reindexing.

      # start a MySQL container On CentOS
      docker run -p 3307:3306 --name test-mysql -e MYSQL_ROOT_PASSWORD=12345678 -d mysql:5.7.34
      

      lobin@Nagisa relief-map % mysql -h192.168.59.109 -P3307 -uroot -p12345678 < setup.sql
      mysql: [Warning] Using a password on the command line interface can be insecure.
      lobin@Nagisa relief-map % mysql -h192.168.59.109 -P3307 -uroot -p12345678 test1 < insert_data.sql
      mysql: [Warning] Using a password on the command line interface can be insecure.
      lobin@Nagisa relief-map % mysql -h192.168.59.109 -P3307 -uroot -p12345678 --table  test1 < select.sql
      mysql: [Warning] Using a password on the command line interface can be insecure.
      +----+----------+---------------+------+-------------+-------------+------------------+---------------------+---------------------+
      | ID | Facility | prefecture_id | ID   | Facility_ID | damage_type | comment          | regist_date         | update_date         |
      +----+----------+---------------+------+-------------+-------------+------------------+---------------------+---------------------+
      |  2 | having1  |            27 |    1 |           2 |           4 | this is latest.  | 2019-03-05 00:00:00 | 2019-03-06 00:00:00 |
      |  3 | having2  |            27 |    3 |           3 |           4 | this is latest2. | 2019-03-06 00:00:00 | 2019-03-05 00:00:00 |
      |  1 | having0  |            27 | NULL |        NULL |        NULL | NULL             | NULL                | NULL                |
      +----+----------+---------------+------+-------------+-------------+------------------+---------------------+---------------------+
      lobin@Nagisa relief-map %
      

      thanx.

        Attachments

        1. insert_data.sql
          1 kB
        2. select.sql
          1 kB
        3. setup.sql
          1 kB

          Activity

            People

            Assignee:
            psergei Sergei Petrunia
            Reporter:
            lobin.z0x50 Wataru Maeda
            Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved:

                Git Integration