Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.1.23, 10.2.5, 5.5(EOL), 10.0(EOL), 10.1(EOL), 10.2(EOL)
-
10.2.7-1
Description
Hi,
MariaDB executes subquery with group by without filter
before the derived table is ready for using.
Executing large subselects with group by can take some time
and oracle can filter this type of selects.
Migrations from oracle to mariadb will be easier, if the optimizer can manage this.
CREATE TABLE `t1` ( |
`id` INT(11) NOT NULL, |
`amt` INT(11) NULL DEFAULT NULL, |
PRIMARY KEY (`id`), |
INDEX `amt` (`amt`) |
);
|
 |
CREATE TABLE `t2` ( |
`amt` INT(11) NOT NULL, |
`somestring` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci', |
INDEX `amt_ind` (`amt`) |
);
|
Inserting some values
INSERT INTO `t2` (`amt`, `somestring`) VALUES (4, 'AtcsBD'); |
INSERT INTO `t2` (`amt`, `somestring`) VALUES (3, 'Wqjovg'); |
INSERT INTO `t2` (`amt`, `somestring`) VALUES (2, 'iWjoMy'); |
INSERT INTO `t2` (`amt`, `somestring`) VALUES (3, 'eoAkem'); |
INSERT INTO `t2` (`amt`, `somestring`) VALUES (4, '23Dj9r'); |
INSERT INTO `t2` (`amt`, `somestring`) VALUES (1, 'Im42UO'); |
INSERT INTO `t2` (`amt`, `somestring`) VALUES (1, 'qbjV4X'); |
INSERT INTO `t2` (`amt`, `somestring`) VALUES (1, 'BuO8oI'); |
INSERT INTO `t2` (`amt`, `somestring`) VALUES (2, '6Lf5Fv'); |
INSERT INTO `t2` (`amt`, `somestring`) VALUES (1, 'udFOm8'); |
 |
 |
INSERT INTO `t1` (`id`, `amt`) VALUES (2, 1); |
INSERT INTO `t1` (`id`, `amt`) VALUES (3, 1); |
INSERT INTO `t1` (`id`, `amt`) VALUES (7, 1); |
INSERT INTO `t1` (`id`, `amt`) VALUES (10, 1); |
INSERT INTO `t1` (`id`, `amt`) VALUES (1, 2); |
INSERT INTO `t1` (`id`, `amt`) VALUES (4, 2); |
INSERT INTO `t1` (`id`, `amt`) VALUES (5, 2); |
INSERT INTO `t1` (`id`, `amt`) VALUES (9, 2); |
INSERT INTO `t1` (`id`, `amt`) VALUES (6, 3); |
INSERT INTO `t1` (`id`, `amt`) VALUES (8, 3); |
The explain
explain
|
select * |
from t1 a |
inner join |
(select * from t2 group by amt) b |
on a.amt = b.amt |
where b.amt = 1; |
Output shows no index is using for creating the derived table.
MariaDB [bughunt]> explain select * from t1 a inner join (select * from t2 group by amt) b on a.amt = b.amt where a.amt = 1 \G
|
*************************** 1. row ***************************
|
id: 1
|
select_type: PRIMARY
|
table: a
|
type: ref
|
possible_keys: amt
|
key: amt
|
key_len: 5
|
ref: const
|
rows: 4
|
Extra: Using index
|
*************************** 2. row ***************************
|
id: 1
|
select_type: PRIMARY
|
table: <derived2>
|
type: ALL
|
possible_keys: NULL
|
key: NULL
|
key_len: NULL
|
ref: NULL
|
rows: 4
|
Extra: Using where; Using join buffer (flat, BNL join)
|
*************************** 3. row ***************************
|
id: 2
|
select_type: DERIVED
|
table: t2
|
type: ALL
|
possible_keys: amt_ind
|
key: NULL
|
key_len: NULL
|
ref: NULL
|
rows: 10
|
Extra: Using where
|
3 rows in set (0.00 sec)
|
 |
}
|
I don't know, if it is a bug or feature request, if initially create as a bug,
you can change it, if you want to classify it as a feature request.
Attachments
Issue Links
- causes
-
MDEV-13166 Server crashes in Item_ref::used_tables on subquery from a view
- Closed
- duplicates
-
MDEV-13091 "Reference not supported" with nested GROUP BY's on the same table and column
- Closed
- relates to
-
MDEV-13162 Condition pushdown into derived tables on the inner sides of outer joins
- Open