Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.11
-
None
-
None
-
Unexpected results
Description
The following scenario incorrectly produces a row when it should instead produce an empty set.
create table t1 (a int primary key); |
insert into t1 values (1),(2),(3); |
create table t2 (b int primary key); |
insert into t2 values (1),(2),(3),(4); |
create table t3 (c int, d int); |
insert into t3 values (1,1),(2,2),(3,3),(4,4),(5,5), |
(6,6),(7,7),(8,8),(9,9),(10,10);
|
insert into t3 select c+10, d+10 from t3; |
insert into t3 select c+20, d+20 from t3; |
insert into t3 select c+40, d+40 from t3; |
 |
select t1.a x, t2.b y from t1 left join t2 on t1.a=t2.b |
where t1.a=1 and t2.b=1 and t1.a in (select c from t3) and rand() < 0 |
union
|
select t1.a x, t2.b y from t1 right join t2 on t1.a=t2.b |
where t1.a=1 and t2.b=1 and t1.a in (select c from t3) and rand() < 0; |
The 80 rows in t3 are needed to force materialization.
Result:
+---+---+
|
| x | y |
|
+---+---+
|
| 1 | 1 |
|
+---+---+
|
Expected result: the empty set. rand() returns a value in [0, ..., 1), so rand() < 0 is never true.
Running with set optimizer_switch='materialization=off' returns the empty set, so something is wrong with materialization.
EXPLAIN FORMAT=JSON...
MariaDB [test]> explain format=json select t1.a x, t2.b y from t1 left join t2 on t1.a=t2.b where t1.a=1 and t2.b=1 and t1.a in (select c from t3) and rand() < 0 union select t1.a x, t2.b y from t1 right join t2 on t1.a=t2.b where t1.a=1 and t2.b=1 and t1.a in (select c from t3) and rand() < 0\G
|
*************************** 1. row ***************************
|
EXPLAIN: {
|
"query_block": {
|
"union_result": {
|
"table_name": "<union1,3>",
|
"access_type": "ALL",
|
"query_specifications": [
|
{
|
"query_block": {
|
"select_id": 1,
|
"nested_loop": [
|
{
|
"table": {
|
"table_name": "t1",
|
"access_type": "const",
|
"possible_keys": ["PRIMARY"],
|
"key": "PRIMARY",
|
"key_length": "4",
|
"used_key_parts": ["a"],
|
"ref": ["const"],
|
"rows": 1,
|
"filtered": 100,
|
"using_index": true
|
}
|
},
|
{
|
"table": {
|
"table_name": "t2",
|
"access_type": "const",
|
"possible_keys": ["PRIMARY"],
|
"key": "PRIMARY",
|
"key_length": "4",
|
"used_key_parts": ["b"],
|
"ref": ["const"],
|
"rows": 1,
|
"filtered": 100,
|
"using_index": true
|
}
|
},
|
{
|
"table": {
|
"table_name": "<subquery2>",
|
"access_type": "eq_ref",
|
"possible_keys": ["distinct_key"],
|
"key": "distinct_key",
|
"key_length": "4",
|
"used_key_parts": ["c"],
|
"ref": ["func"],
|
"rows": 1,
|
"filtered": 100,
|
"materialized": {
|
"unique": 1,
|
"query_block": {
|
"select_id": 2,
|
"nested_loop": [
|
{
|
"table": {
|
"table_name": "t3",
|
"access_type": "ALL",
|
"rows": 80,
|
"filtered": 100,
|
"attached_condition": "t3.c = 1"
|
}
|
}
|
]
|
}
|
}
|
}
|
}
|
]
|
}
|
},
|
{
|
"query_block": {
|
"select_id": 3,
|
"operation": "UNION",
|
"nested_loop": [
|
{
|
"table": {
|
"table_name": "t1",
|
"access_type": "const",
|
"possible_keys": ["PRIMARY"],
|
"key": "PRIMARY",
|
"key_length": "4",
|
"used_key_parts": ["a"],
|
"ref": ["const"],
|
"rows": 1,
|
"filtered": 100,
|
"using_index": true
|
}
|
},
|
{
|
"table": {
|
"table_name": "t2",
|
"access_type": "const",
|
"possible_keys": ["PRIMARY"],
|
"key": "PRIMARY",
|
"key_length": "4",
|
"used_key_parts": ["b"],
|
"ref": ["const"],
|
"rows": 1,
|
"filtered": 100,
|
"using_index": true
|
}
|
},
|
{
|
"table": {
|
"table_name": "<subquery4>",
|
"access_type": "eq_ref",
|
"possible_keys": ["distinct_key"],
|
"key": "distinct_key",
|
"key_length": "4",
|
"used_key_parts": ["c"],
|
"ref": ["func"],
|
"rows": 1,
|
"filtered": 100,
|
"materialized": {
|
"unique": 1,
|
"query_block": {
|
"select_id": 4,
|
"nested_loop": [
|
{
|
"table": {
|
"table_name": "t3",
|
"access_type": "ALL",
|
"rows": 80,
|
"filtered": 100,
|
"attached_condition": "t3.c = 1"
|
}
|
}
|
]
|
}
|
}
|
}
|
}
|
]
|
}
|
}
|
]
|
}
|
}
|
}
|
1 row in set (0.002 sec)
|
PostgreSQL comparison
PostgreSQL has random(), not rand(), but otherwise the query's identical. Postgres returns the correct result:
select t1.a x, t2.b y from t1 left join t2 on t1.a=t2.b |
where t1.a=1 and t2.b=1 and t1.a in (select c from t3) and random() < 0 |
union
|
select t1.a x, t2.b y from t1 right join t2 on t1.a=t2.b |
where t1.a=1 and t2.b=1 and t1.a in (select c from t3) and random() < 0; |
PostgreSQL returns the empty set.