Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
11.4.3, 11.0(EOL), 11.2(EOL), 11.4, 11.5(EOL)
-
Production
Description
The issue can be reproduced by creating the following table:
CREATE TABLE `__testIssue` (
|
`ID` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
|
`name` VARCHAR(255) NOT NULL COLLATE 'utf8mb3_unicode_ci',
|
`notes` LONGTEXT NOT NULL COLLATE 'utf8mb3_unicode_ci',
|
PRIMARY KEY (`ID`) USING BTREE
|
)
|
COLLATE='utf8mb3_unicode_ci'
|
ENGINE=InnoDB
|
And then insert one test record:
INSERT INTO __testIssue VALUES (NULL, 'test', 'test')
|
And then this query gives 0 results in 11.4.3 while it did return results in 10.11.9:
select test.ID, test.notes from (select ID, notes from __testIssue
|
union
|
select ID, notes from __testIssue) test
|
WHERE ID = 1
|
Some findings:
- changing "WHERE ID = 1" into "WHERE ID > 0" does give results
- changing the colum notes from LONGTEXT into varchar does also give results
- removing the column notes from the select's does give results
Attachments
Issue Links
- causes
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
Activity
| Field | Original Value | New Value |
|---|---|---|
| Description |
The issue can be reproduced by creating the following table:
{code:SQL} CREATE TABLE `__testIssue` ( `ID` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, `name` VARCHAR(255) NOT NULL COLLATE 'utf8mb3_unicode_ci', `notes` LONGTEXT NOT NULL COLLATE 'utf8mb3_unicode_ci', PRIMARY KEY (`ID`) USING BTREE ) COLLATE='utf8mb3_unicode_ci' ENGINE=InnoDB {code} And then insert one test record: {code:SQL} INSERT INTO __testIssue VALUES (NULL, 'test', 'test') {code} And then this query gives 0 results in 11.4.3 while it did return results in 10.11.9: {code:SQL} select test.ID, test.notes from (select ID, notes from __testIssue union select ID, notes from __testIssue) test WHERE ID = 0 {code} Some findings: - changing "WHERE ID = 0" into "WHERE ID > 0" does give results - changing the colum notes from LONGTEXT into varchar does also give results |
The issue can be reproduced by creating the following table:
{code:SQL} CREATE TABLE `__testIssue` ( `ID` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, `name` VARCHAR(255) NOT NULL COLLATE 'utf8mb3_unicode_ci', `notes` LONGTEXT NOT NULL COLLATE 'utf8mb3_unicode_ci', PRIMARY KEY (`ID`) USING BTREE ) COLLATE='utf8mb3_unicode_ci' ENGINE=InnoDB {code} And then insert one test record: {code:SQL} INSERT INTO __testIssue VALUES (NULL, 'test', 'test') {code} And then this query gives 0 results in 11.4.3 while it did return results in 10.11.9: {code:SQL} select test.ID, test.notes from (select ID, notes from __testIssue union select ID, notes from __testIssue) test WHERE ID = 1 {code} Some findings: - changing "WHERE ID = 0" into "WHERE ID > 0" does give results - changing the colum notes from LONGTEXT into varchar does also give results |
| Description |
The issue can be reproduced by creating the following table:
{code:SQL} CREATE TABLE `__testIssue` ( `ID` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, `name` VARCHAR(255) NOT NULL COLLATE 'utf8mb3_unicode_ci', `notes` LONGTEXT NOT NULL COLLATE 'utf8mb3_unicode_ci', PRIMARY KEY (`ID`) USING BTREE ) COLLATE='utf8mb3_unicode_ci' ENGINE=InnoDB {code} And then insert one test record: {code:SQL} INSERT INTO __testIssue VALUES (NULL, 'test', 'test') {code} And then this query gives 0 results in 11.4.3 while it did return results in 10.11.9: {code:SQL} select test.ID, test.notes from (select ID, notes from __testIssue union select ID, notes from __testIssue) test WHERE ID = 1 {code} Some findings: - changing "WHERE ID = 0" into "WHERE ID > 0" does give results - changing the colum notes from LONGTEXT into varchar does also give results |
The issue can be reproduced by creating the following table:
{code:SQL} CREATE TABLE `__testIssue` ( `ID` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, `name` VARCHAR(255) NOT NULL COLLATE 'utf8mb3_unicode_ci', `notes` LONGTEXT NOT NULL COLLATE 'utf8mb3_unicode_ci', PRIMARY KEY (`ID`) USING BTREE ) COLLATE='utf8mb3_unicode_ci' ENGINE=InnoDB {code} And then insert one test record: {code:SQL} INSERT INTO __testIssue VALUES (NULL, 'test', 'test') {code} And then this query gives 0 results in 11.4.3 while it did return results in 10.11.9: {code:SQL} select test.ID, test.notes from (select ID, notes from __testIssue union select ID, notes from __testIssue) test WHERE ID = 1 {code} Some findings: - changing "WHERE ID = 1" into "WHERE ID > 0" does give results - changing the colum notes from LONGTEXT into varchar does also give results |
| Description |
The issue can be reproduced by creating the following table:
{code:SQL} CREATE TABLE `__testIssue` ( `ID` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, `name` VARCHAR(255) NOT NULL COLLATE 'utf8mb3_unicode_ci', `notes` LONGTEXT NOT NULL COLLATE 'utf8mb3_unicode_ci', PRIMARY KEY (`ID`) USING BTREE ) COLLATE='utf8mb3_unicode_ci' ENGINE=InnoDB {code} And then insert one test record: {code:SQL} INSERT INTO __testIssue VALUES (NULL, 'test', 'test') {code} And then this query gives 0 results in 11.4.3 while it did return results in 10.11.9: {code:SQL} select test.ID, test.notes from (select ID, notes from __testIssue union select ID, notes from __testIssue) test WHERE ID = 1 {code} Some findings: - changing "WHERE ID = 1" into "WHERE ID > 0" does give results - changing the colum notes from LONGTEXT into varchar does also give results |
The issue can be reproduced by creating the following table:
{code:SQL} CREATE TABLE `__testIssue` ( `ID` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, `name` VARCHAR(255) NOT NULL COLLATE 'utf8mb3_unicode_ci', `notes` LONGTEXT NOT NULL COLLATE 'utf8mb3_unicode_ci', PRIMARY KEY (`ID`) USING BTREE ) COLLATE='utf8mb3_unicode_ci' ENGINE=InnoDB {code} And then insert one test record: {code:SQL} INSERT INTO __testIssue VALUES (NULL, 'test', 'test') {code} And then this query gives 0 results in 11.4.3 while it did return results in 10.11.9: {code:SQL} select test.ID, test.notes from (select ID, notes from __testIssue union select ID, notes from __testIssue) test WHERE ID = 1 {code} Some findings: - changing "WHERE ID = 1" into "WHERE ID > 0" does give results - changing the colum notes from LONGTEXT into varchar does also give results - removing the column notes from the select's does give results |
| Fix Version/s | 11.0 [ 28320 ] | |
| Fix Version/s | 11.2 [ 28603 ] | |
| Fix Version/s | 11.4 [ 29301 ] | |
| Fix Version/s | 11.5 [ 29506 ] |
| Affects Version/s | 11.0 [ 28320 ] | |
| Affects Version/s | 11.2 [ 28603 ] | |
| Affects Version/s | 11.4 [ 29301 ] | |
| Affects Version/s | 11.5 [ 29506 ] |
| Assignee | Sergei Petrunia [ psergey ] |
| Status | Open [ 1 ] | Confirmed [ 10101 ] |
| Component/s | Optimizer [ 10200 ] | |
| Component/s | Storage Engine - InnoDB [ 10129 ] |
| Fix Version/s | 11.0 [ 28320 ] | |
| Fix Version/s | 11.5 [ 29506 ] |
Thanks for the quick response and quick analysis!
Do you have any idea about when we can expect a fix? Is it days, or weeks or more?
| Assignee | Sergei Petrunia [ psergey ] | Oleg Smirnov [ JIRAUSER50405 ] |
| Priority | Major [ 3 ] | Critical [ 2 ] |
| Labels | regression |
| Status | Confirmed [ 10101 ] | In Progress [ 3 ] |
| Summary | Some queries in 11.4.3 give different results than in 10.11.9 | Incorrect result for query with derived table having TEXT field |
The derived table `dt` is materialized, and a unique key is created over all columns to ensure only distinct values are placed to the result (because there is UNION command but not UNION ALL). This key is created in a special mode HA_UNIQUE_HASH to support BLOBs (TEXT in the case above).
Later in `best_access_path` the optimizer chooses this index to retrieve values from the derived table, although such type of index cannot be used for data retrieval. This is why no data is fetched from `dt`.
| Assignee | Oleg Smirnov [ JIRAUSER50405 ] | Sergei Petrunia [ psergey ] |
| Status | In Progress [ 3 ] | In Review [ 10002 ] |
I'm wondering if there are any other parts of the query that could try to use this index...
test_if_skip_sort_order() won't use it because it the blob field has empty Field::part_of_sortkey...
Ok, there are also other questions, see the PR.
| Assignee | Sergei Petrunia [ psergey ] | Oleg Smirnov [ JIRAUSER50405 ] |
| Status | In Review [ 10002 ] | Stalled [ 10000 ] |
| Status | Stalled [ 10000 ] | In Progress [ 3 ] |
| Assignee | Oleg Smirnov [ JIRAUSER50405 ] | Sergei Petrunia [ psergey ] |
| Status | In Progress [ 3 ] | In Review [ 10002 ] |
| Assignee | Sergei Petrunia [ psergey ] | Oleg Smirnov [ JIRAUSER50405 ] |
| Status | In Review [ 10002 ] | Stalled [ 10000 ] |
| Fix Version/s | 11.2.6 [ 29906 ] | |
| Fix Version/s | 11.2 [ 28603 ] | |
| Fix Version/s | 11.4 [ 29301 ] | |
| Resolution | Fixed [ 1 ] | |
| Status | Stalled [ 10000 ] | Closed [ 6 ] |
| Fix Version/s | 11.4.4 [ 29907 ] |
| Link |
This issue causes |
| Link |
This issue causes |
| Link |
This issue causes |
| Link |
This issue causes |
Thanks! I repeated as described on 11.0-11.5:
MariaDB [test]> CREATE TABLE t1 ( id int NOT NULL auto_increment PRIMARY KEY, notes TEXT NOT NULL);Query OK, 0 rows affected (0,041 sec)MariaDB [test]> INSERT INTO t1 VALUES (1, 'test');Query OK, 1 row affected (0,005 sec)MariaDB [test]> select dt.* from (select * from t1 union select * from t1) dt WHERE id = 1;Empty set (0,007 sec)MariaDB [test]> select dt.* from (select * from t1 union select * from t1) dt WHERE id >= 1;+----+-------+| id | notes |+----+-------+| 1 | test |+----+-------+1 row in set (0,006 sec)MariaDB [test]> explain extended select dt.* from (select * from t1 union select * from t1) dt WHERE id = 1;+------+--------------+------------+-------+---------------+--------------+---------+-------+------+----------+-------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+------+--------------+------------+-------+---------------+--------------+---------+-------+------+----------+-------+| 1 | PRIMARY | <derived2> | ref | distinct_key | distinct_key | 4 | const | 1 | 100.00 | || 2 | DERIVED | t1 | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | || 3 | UNION | t1 | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | || NULL | UNION RESULT | <union2,3> | ALL | NULL | NULL | NULL | NULL | NULL | NULL | |+------+--------------+------------+-------+---------------+--------------+---------+-------+------+----------+-------+4 rows in set, 1 warning (0,005 sec)Note (Code 1003): /* select#1 */ select `dt`.`id` AS `id`,`dt`.`notes` AS `notes` from (/* select#2 */ select 1 AS `id`,'test' AS `notes` from `test`.`t1` union /* select#3 */ select 1 AS `id`,'test' AS `notes` from `test`.`t1`) `dt` where `dt`.`id` = 1MariaDB [test]>MariaDB [test]> analyze format=json select dt.* from (select * from t1 union select * from t1) dt WHERE id = 1;+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| ANALYZE |+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| {"query_optimization": {"r_total_time_ms": 2.874248112},"query_block": {"select_id": 1,"cost": 0.001229029,"r_loops": 1,"r_total_time_ms": 1.868339105,"nested_loop": [{"table": {"table_name": "<derived2>","access_type": "ref","possible_keys": ["distinct_key"],"key": "distinct_key","key_length": "4","used_key_parts": ["id"],"ref": ["const"],"loops": 1,"r_loops": 1,"rows": 1,"r_rows": 0,"cost": 0.001229029,"r_table_time_ms": 0.182791515,"r_other_time_ms": 0.041447163,"filtered": 100,"r_filtered": 100,"materialized": {"query_block": {"union_result": {"table_name": "<union2,3>","access_type": "ALL","r_loops": 1,"r_rows": 1,"query_specifications": [{"query_block": {"select_id": 2,"r_loops": 1,"r_total_time_ms": 0.288899226,"nested_loop": [{"table": {"table_name": "t1","access_type": "const","possible_keys": ["PRIMARY"],"key": "PRIMARY","key_length": "4","used_key_parts": ["id"],"ref": ["const"],"r_loops": 0,"rows": 1,"r_rows": null,"r_engine_stats": {"pages_accessed": 1},"filtered": 100,"r_filtered": null}}]}},{"query_block": {"select_id": 3,"operation": "UNION","r_loops": 1,"r_total_time_ms": 0.154588559,"nested_loop": [{"table": {"table_name": "t1","access_type": "const","possible_keys": ["PRIMARY"],"key": "PRIMARY","key_length": "4","used_key_parts": ["id"],"ref": ["const"],"r_loops": 0,"rows": 1,"r_rows": null,"r_engine_stats": {"pages_accessed": 1},"filtered": 100,"r_filtered": null}}]}}]}}}}}]}} |+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0,007 sec)on 10.5-10.11 : note ALL vs ref,distinct_key
MariaDB [test]> select dt.*from (select * from t1 union select * from t1) dt WHERE id = 1;+----+-------+| id | notes |+----+-------+| 1 | test |+----+-------+1 row in set (0,007 sec)MariaDB [test]> explain extended select dt.*from (select * from t1 union select * from t1) dt WHERE id = 1;+------+--------------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+------+--------------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | Using where || 2 | DERIVED | t1 | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | || 3 | UNION | t1 | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | || NULL | UNION RESULT | <union2,3> | ALL | NULL | NULL | NULL | NULL | NULL | NULL | |+------+--------------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+4 rows in set, 1 warning (0,006 sec)Note (Code 1003): /* select#1 */ select `dt`.`id` AS `id`,`dt`.`notes` AS `notes` from (/* select#2 */ select 1 AS `id`,'test' AS `notes` from `test`.`t1` union /* select#3 */ select 1 AS `id`,'test' AS `notes` from `test`.`t1`) `dt` where `dt`.`id` = 1