The opposite WHERE clause intersects and is always an empty set.

Dear MariaDB Developer:

I found a performance bug that seems different from the usual 'Impossible WHERE' cases. The query plan does not show the typical 'Impossible WHERE' hint, but the query still behaves inefficiently when it logically shouldn’t execute at all.

q1: SELECT * FROM TABLES CROSS JOIN VIEWS WHERE TABLES.TABLE_NAME != 'accounts'
q2: SELECT * FROM TABLES CROSS JOIN VIEWS WHERE TABLES.TABLE_NAME = 'accounts';

Since q1 and q2 have opposite WHERE clause, q1 INTERSECT q2 should always produce empty set and without physical read.

MariaDB [information_schema]> FLUSH STATUS;
Query OK, 0 rows affected (0.000 sec)

MariaDB [information_schema]> SELECT * FROM TABLES CROSS JOIN VIEWS WHERE TABLES.TABLE_NAME != 'accounts' INTERSECT SELECT * FROM TABLES CROSS JOIN VIEWS WHERE TABLES.TABLE_NAME = 'accounts';
Empty set (4.121 sec)

MariaDB [information_schema]> SHOW SESSION STATUS LIKE 'Handler_read%';
--------------------------------+

--------------------------------+

--------------------------------+
9 rows in set (0.001 sec)

MariaDB [information_schema]> EXPLAIN format=json SELECT * FROM TABLES CROSS JOIN VIEWS WHERE TABLES.TABLE_NAME != 'accounts' INTERSECT SELECT * FROM TABLES CROSS
JOIN VIEWS WHERE TABLES.TABLE_NAME = 'accounts';
{
"query_block": {
"union_result": {
"table_name": "<intersect1,2>",
"access_type": "ALL",
"query_specifications": [
{
"query_block": {
"select_id": 1,
"cost": 1.09287406,
"nested_loop": [
{
"table":

{ "table_name": "TABLES", "access_type": "ALL", "loops": 1, "cost": 0.01423506, "attached_condition": "`TABLES`.`TABLE_NAME` <> 'accounts'", "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,
"nested_loop": [
{
"table":

{ "table_name": "TABLES", "access_type": "ALL", "key": "TABLE_NAME", "loops": 1, "cost": 0.01423506, "attached_condition": "`TABLES`.`TABLE_NAME` = 'accounts'", "open_full_table": true, "scanned_databases": 1 }

},
{
"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,