Details
Description
Dear MariaDB developers,
This is similar case which relates to MDEV-36673, but it is different. I think this situation also needs to be optimized.
MariaDB [(none)]> use information_schema; |
MariaDB [information_schema]> FLUSH STATUS;
|
Query OK, 0 rows affected (0.000 sec) |
|
|
MariaDB [information_schema]> SELECT * FROM TABLES CROSS JOIN VIEWS HAVING TABLES.TABLE_NAME != 'accounts' INTERSECT SELECT * FROM TABLES CROSS JOIN VIEWS HAVING TABLES.TABLE_NAME = 'accounts'; |
Empty set, 8 warnings (4.724 sec) |
|
|
MariaDB [information_schema]> SHOW SESSION STATUS LIKE 'Handler_read%'; |
+--------------------------+--------+ |
| Variable_name | Value |
|
+--------------------------+--------+ |
| Handler_read_first | 0 |
|
| Handler_read_key | 304 |
|
| Handler_read_last | 0 |
|
| Handler_read_next | 0 |
|
| Handler_read_prev | 0 |
|
| Handler_read_retry | 0 |
|
| Handler_read_rnd | 0 |
|
| Handler_read_rnd_deleted | 0 |
|
| Handler_read_rnd_next | 133687 |
|
+--------------------------+--------+ |
9 rows in set (0.001 sec) |
|
|
| {
|
"query_block": { |
"union_result": { |
"table_name": "<intersect1,2>", |
"access_type": "ALL", |
"query_specifications": [ |
{
|
"query_block": { |
"select_id": 1, |
"cost": 1.09287406, |
"having_condition": "`TABLES`.`TABLE_NAME` <> 'accounts'", |
"nested_loop": [ |
{
|
"table": { |
"table_name": "TABLES", |
"access_type": "ALL", |
"loops": 1, |
"cost": 0.01423506, |
"open_full_table": true, |
"scanned_databases": "all" |
}
|
},
|
{
|
"block-nl-join": { |
"table": { |
"table_name": "VIEWS", |
"access_type": "ALL", |
"loops": 100, |
"cost": 1.078639, |
"open_full_table": true, |
"scanned_databases": "all" |
},
|
"buffer_type": "flat", |
"buffer_size": "256Kb", |
"join_type": "BNL" |
}
|
}
|
]
|
}
|
},
|
{
|
"query_block": { |
"select_id": 2, |
"operation": "INTERSECT", |
"cost": 1.09287406, |
"having_condition": "`TABLES`.`TABLE_NAME` = 'accounts'", |
"nested_loop": [ |
{
|
"table": { |
"table_name": "TABLES", |
"access_type": "ALL", |
"loops": 1, |
"cost": 0.01423506, |
"open_full_table": true, |
"scanned_databases": "all" |
}
|
},
|
{
|
"block-nl-join": { |
"table": { |
"table_name": "VIEWS", |
"access_type": "ALL", |
"loops": 100, |
"cost": 1.078639, |
"open_full_table": true, |
"scanned_databases": "all" |
},
|
"buffer_type": "flat", |
"buffer_size": "256Kb", |
"join_type": "BNL" |
}
|
}
|
]
|
}
|
}
|
]
|
}
|
}
|
} |
|
Thank you for your time and attention to this issue. I greatly appreciate your assistance and look forward to hearing your thoughts.
Best regards,
Attachments
Issue Links
- relates to
-
MDEV-36673 The opposite WHERE clause intersects and is always an empty set.
-
- Confirmed
-