Details
-
Bug
-
Status: Stalled (View Workflow)
-
Major
-
Resolution: Unresolved
-
5.5(EOL), 10.0(EOL), 10.1(EOL), 10.2(EOL), 10.3(EOL)
-
None
-
10.3.6-1
Description
MTR test
CREATE TABLE t1 (a INT PRIMARY KEY, b INT);
|
INSERT INTO t1 VALUES (1,1), (2,2);
|
|
|
CREATE TABLE t2 (a INT PRIMARY KEY, b INT);
|
INSERT INTO t2 VALUES (1,1), (3,3);
|
|
|
set join_cache_level=4;
|
SELECT SQL_NO_CACHE
|
(SELECT SUM(c.a) FROM t1 ttt, t2 ccc
|
WHERE ttt.a = ccc.b AND ttt.a = t.a GROUP BY ttt.a) AS minid
|
FROM t1 t, t2 c WHERE t.a = c.b;
|
minid
|
NULL
|
|
|
set join_cache_level= 2;
|
SELECT SQL_NO_CACHE
|
(SELECT SUM(c.a) FROM t1 ttt, t2 ccc
|
WHERE ttt.a = ccc.b AND ttt.a = t.a GROUP BY ttt.a) AS minid
|
FROM t1 t, t2 c WHERE t.a = c.b;
|
minid
|
1
|
DROP TABLE t1,t2;
|
Attachments
Activity
Out of explain format=json
explain format=json
|
SELECT SQL_NO_CACHE
|
(SELECT SUM(c.a) FROM t1 ttt, t2 ccc
|
WHERE ttt.a = ccc.b AND ttt.a = t.a GROUP BY ttt.a) AS minid
|
FROM t1 t, t2 c WHERE t.a = c.b;
|
EXPLAIN
|
{
|
"query_block": {
|
"select_id": 1,
|
"table": {
|
"table_name": "c",
|
"access_type": "ALL",
|
"rows": 2,
|
"filtered": 100,
|
"attached_condition": "c.b is not null"
|
},
|
"block-nl-join": {
|
"table": {
|
"table_name": "t",
|
"access_type": "hash_index",
|
"possible_keys": ["PRIMARY"],
|
"key": "#hash#PRIMARY:PRIMARY",
|
"key_length": "4:4",
|
"used_key_parts": ["a"],
|
"ref": ["test.c.b"],
|
"rows": 2,
|
"filtered": 50,
|
"using_index": true
|
},
|
"buffer_type": "flat",
|
"buffer_size": "256Kb",
|
"join_type": "BNLH"
|
},
|
"subqueries": [
|
{
|
"query_block": {
|
"select_id": 2,
|
"table": {
|
"table_name": "ttt",
|
"access_type": "eq_ref",
|
"possible_keys": ["PRIMARY"],
|
"key": "PRIMARY",
|
"key_length": "4",
|
"used_key_parts": ["a"],
|
"ref": ["test.t.a"],
|
"rows": 1,
|
"filtered": 100,
|
"using_index": true
|
},
|
"block-nl-join": {
|
"table": {
|
"table_name": "ccc",
|
"access_type": "hash_ALL",
|
"key": "#hash#$hj",
|
"key_length": "5",
|
"used_key_parts": ["b"],
|
"ref": ["test.t.a"],
|
"rows": 2,
|
"filtered": 100
|
},
|
"buffer_type": "flat",
|
"buffer_size": "256Kb",
|
"join_type": "BNLH",
|
"attached_condition": "ccc.b = ttt.a"
|
}
|
}
|
}
|
]
|
}
|
}
|
|
Simplified test-case:
SET optimizer_switch='subquery_cache=off'; |
CREATE TABLE t1 (a INT , b INT); |
INSERT INTO t1 VALUES (1,1) ,(2,2); |
CREATE TABLE t2 (a INT , b INT); |
INSERT INTO t2 VALUES (1,1), (3,3); |
set join_cache_level=2; |
SELECT |
(SELECT sum(c.a) FROM t1 ttt, t2 ccc |
WHERE ttt.a = ccc.b AND ttt.a = t.a ) AS minid |
FROM t1 t, t2 c WHERE t.a = c.b; |
notes from the phone call:
- this is not a stopper for changing the server defaults
- need to figure out what the semantics of the query should be (check other databases)
- * should aggregation be done in the upper query or in the subquery
- is it fixed in MySQL?
Running query on different databases
1) SQL Server
Error(s), warning(s):
|
Column 't1.a' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
|
2) Oracle
SELECT |
(SELECT sum(c.a) FROM t1 ttt, t2 ccc |
WHERE ttt.a = ccc.b AND ttt.a = t.a ) AS minid |
FROM t1 t, t2 c WHERE t.a = c.b; |
Output:
MINID
1
Explain for the query with join_cache_level=4
explainSELECT SQL_NO_CACHE(SELECT SUM(c.a) FROM t1 ttt, t2 cccWHERE ttt.a = ccc.b AND ttt.a = t.a GROUP BY ttt.a) AS minidFROM t1 t, t2 c WHERE t.a = c.b;id select_type table type possible_keys key key_len ref rows Extra1 PRIMARY c ALL NULL NULL NULL NULL 2 Using where1 PRIMARY t hash_index PRIMARY #hash#PRIMARY:PRIMARY 4:4 test.c.b 2 Using index; Using join buffer (flat, BNLH join)2 DEPENDENT SUBQUERY ttt eq_ref PRIMARY PRIMARY 4 test.t.a 1 Using index2 DEPENDENT SUBQUERY ccc hash_ALL NULL #hash#$hj 5 test.t.a 2 Using where; Using join buffer (flat, BNLH join)