[MDEV-25858] Query results are incorrect when indexes are added. - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Affects Version/s: 10.5.10, 10.2(EOL), 10.3(EOL), 10.4(EOL), 10.5
Fix Version/s: 10.2.40, 10.3.31, 10.4.21, 10.5.12, 10.6.4
Component/s: Data Manipulation - Subquery
Labels:
None
Environment:
CentOS 7.9, Docker

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

- Sort By Name
- Sort By Date
- Ascending
- Descending
- Thumbnails
- List
- Download All

insert_data.sql
1 kB
2021-06-04 13:37
select.sql
1 kB
2021-06-04 13:37
setup.sql
1 kB
2021-06-04 13:37

Issue Links

relates to

MDEV-27270 Wrong query plan with Range Checked for Each Record and ORDER BY ... LIMIT

Closed

Activity

Ascending order - Click to sort in descending order

Wataru Maeda created issue - 2021-06-04 14:04

Wataru Maeda made changes - 2021-06-04 14:07

Field	Original Value	New Value
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 indexes 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 from the beginning. ``` # 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.	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). {code:bash} docker run -p 3306:3306 --name test-mariadb -e MARIADB_ROOT_PASSWORD=12345678 -d mariadb:10.5.10 {code} Next, I ran the following on my MacBook. {code:bash} 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 % {code} This is not the result I was expecting. Next, I removed the indexes created at the end of `setup.sql` and ran `select.sql` again, and got the expected results. {code:bash} 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 % {code} 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 from the beginning. {code:bash} # start a MySQL container On CentOS docker run -p 3307:3306 --name test-mysql -e MYSQL_ROOT_PASSWORD=12345678 -d mysql:5.7.34 {code} {code:bash} 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 % {code} thanx.

Wataru Maeda made changes - 2021-06-04 14:10

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).

{code:bash}
docker run -p 3306:3306 --name test-mariadb -e MARIADB_ROOT_PASSWORD=12345678 -d mariadb:10.5.10
{code}

Next, I ran the following on my MacBook.
{code:bash}
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 %
{code}

This is not the result I was expecting.

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

{code:bash}
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 %
{code}

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 from the beginning.

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

{code:bash}
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 %
{code}

thanx.

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).

{code:bash}
docker run -p 3306:3306 --name test-mariadb -e MARIADB_ROOT_PASSWORD=12345678 -d mariadb:10.5.10
{code}

Next, I ran the following on my MacBook.
{code:bash}
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 %
{code}

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.

{code:bash}
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 %
{code}

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.

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

{code:bash}
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 %
{code}

thanx.

Alice Sherepa made changes - 2021-06-04 16:31

Affects Version/s		10.2 [ 14601 ]
Affects Version/s		10.3 [ 22126 ]
Affects Version/s		10.4 [ 22408 ]
Affects Version/s		10.5 [ 23123 ]

Alice Sherepa made changes - 2021-06-04 16:31

Fix Version/s		10.2 [ 14601 ]
Fix Version/s		10.3 [ 22126 ]
Fix Version/s		10.4 [ 22408 ]
Fix Version/s		10.5 [ 23123 ]

Alice Sherepa made changes - 2021-06-04 16:31

Status

Open [ 1 ]

Confirmed [ 10101 ]

Alice Sherepa made changes - 2021-06-04 16:32

Assignee

Sergei Petrunia [ psergey ]

Sergei Petrunia made changes - 2021-06-09 14:55

Status

Confirmed [ 10101 ]

In Progress [ 3 ]

Sergei Petrunia made changes - 2021-06-29 15:25

Assignee	Sergei Petrunia [ psergey ]	Oleksandr Byelkin [ sanja ]
Status	In Progress [ 3 ]	In Review [ 10002 ]

Oleksandr Byelkin made changes - 2021-07-14 16:01

Assignee	Oleksandr Byelkin [ sanja ]	Sergei Petrunia [ psergey ]
Status	In Review [ 10002 ]	Stalled [ 10000 ]

Sergei Petrunia made changes - 2021-07-15 16:12

Fix Version/s		10.2.40 [ 26027 ]
Fix Version/s		10.3.31 [ 26028 ]
Fix Version/s		10.4.21 [ 26030 ]
Fix Version/s		10.5.12 [ 26025 ]
Fix Version/s		10.6.4 [ 26033 ]
Fix Version/s	10.2 [ 14601 ]
Fix Version/s	10.3 [ 22126 ]
Fix Version/s	10.4 [ 22408 ]
Fix Version/s	10.5 [ 23123 ]
Resolution		Fixed [ 1 ]
Status	Stalled [ 10000 ]	Closed [ 6 ]

Sergei Golubchik made changes - 2021-12-06 21:53

Workflow

MariaDB v3 [ 122465 ]

MariaDB v4 [ 159362 ]

Sergei Petrunia made changes - 2021-12-15 11:50

Link

This issue relates to ~~MDEV-27270~~ [ ~~MDEV-27270~~ ]

People

Assignee:: Sergei Petrunia

Reporter:: Wataru Maeda

Votes:: 0 Vote for this issue

Watchers:: 4 Start watching this issue

Dates

Created:: 2021-06-04 14:04

Updated:: 2022-01-26 17:43

Resolved:: 2021-07-15 16:12

Git Integration

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

MariaDB Server

Details

Description

Attachments

Attachments

Issue Links

Activity

People

Dates

Git Integration