Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.0.21, 10.1.7, 5.3.13, 5.5(EOL), 10.0(EOL), 10.1(EOL)
-
10.0.22, 10.1.9-1
Description
DDL
CREATE TABLE `fact_web_session_summary` (
|
`id` INT(11) NOT NULL AUTO_INCREMENT,
|
`date_id` INT(11) NOT NULL,
|
`web_source_id` INT(11) NOT NULL,
|
`page_views` INT(11) NOT NULL,
|
`hits` INT(11) NOT NULL,
|
`users` INT(11) NOT NULL,
|
`new_users` INT(11) NOT NULL,
|
`sessions` INT(11) NOT NULL,
|
`bounces` INT(11) NOT NULL,
|
`duration` INT(11) NOT NULL
|
PRIMARY KEY (`id`)
|
)
|
COLLATE='utf8_general_ci'
|
ENGINE=InnoDB;
|
|
|
CREATE TABLE `dim_web_source` (
|
`id` INT(11) NOT NULL AUTO_INCREMENT,
|
`source` VARCHAR(200) NOT NULL,
|
`medium` VARCHAR(200) NOT NULL,
|
PRIMARY KEY (`id`)
|
)
|
COLLATE='utf8_general_ci'
|
ENGINE=InnoDB
|
;
|
|
|
CREATE TABLE `dim_date` (
|
`date_id` int(11) NOT NULL,
|
`date` date DEFAULT NULL,
|
PRIMARY KEY (`date_id`),
|
UNIQUE KEY `date` (`date`)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
|
|
|
SQL Statement to Issue
SELECT
|
`Project1`.`C7` AS `C1`,
|
`Project1`.`source`,
|
`Project1`.`C1` AS `C2`,
|
`Project1`.`C2` AS `C3`,
|
`Project1`.`C3` AS `C4`,
|
`Project1`.`C4` AS `C5`,
|
`Project1`.`C5` AS `C6`,
|
`Project1`.`C6` AS `C7`
|
FROM (
|
SELECT
|
`GroupBy1`.`A1` AS `C1`,
|
`GroupBy1`.`A2` AS `C2`,
|
`GroupBy1`.`A3` AS `C3`,
|
`GroupBy1`.`A4` AS `C4`,
|
`GroupBy1`.`A5` AS `C5`,
|
`GroupBy1`.`A6` AS `C6`,
|
`GroupBy1`.`K1` AS `source`,
|
1 AS `C7`
|
FROM (
|
SELECT
|
`Extent3`.`source` AS `K1`,
|
SUM(`Extent1`.`sessions`) AS `A1`,
|
SUM(`Extent1`.`new_users`) AS `A2`,
|
SUM(`Extent1`.`page_views`) AS `A3`,
|
SUM(`Extent1`.`bounces`) AS `A4`,
|
SUM(`Extent1`.`duration`) AS `A5`,
|
SUM(`Extent1`.`hits`) AS `A6`
|
FROM `fact_web_session_summary` AS `Extent1`
|
INNER JOIN `dim_date` AS `Extent2` ON `Extent1`.`date_id` = `Extent2`.`date_id`
|
INNER JOIN `dim_web_source` AS `Extent3` ON `Extent1`.`web_source_id` = `Extent3`.`id`
|
WHERE
|
`Extent2`.`date` >= '2015-01-01' AND
|
`Extent2`.`date` <= '2015-10-01'
|
GROUP BY `Extent3`.`source`
|
) AS `GroupBy1`
|
|
) AS `Project1`
|
ORDER BY `Project1`.`C1` DESC
|
LIMIT 10
|
Synopsis
The final result ends up Ordering by the ALIASED `C1`, _NOT _`Project1`.`C1` due to both the derived table and the final alias having the exact same name despite the ORDER BY being fully qualified.
If you change the final alias in the outermost query from `C1` to anything else, such as `ABC`, there is no longer a shared column name between the derived table and the outer queries alias, and so the Order By then takes affect as it should
Attachments
Activity
Hi Elena,
Thank you for your comments. Unfortunately, I'm using an ORM (.Net - Entity Framework / MySQL Connector) which generates these statements. I do not have any control over the output. Additionally, the sole reason I moved to MariaDB is for better performance due of derived tables due to the significant use of them in the generated SQL from the Entity Framework / MySQL connector. Turning off derived_merge would invalidate the very reason I moved to MariaDB. I'm stuck at this point unless MariaDB is updated or the MySQL connector is updated to use different column names.
I see. Hopefully it will be fixed soon. I assume you are on 10.0 now?
I am setup as a Oracle contributor. Given that this is exhibited in MySQL 5.7, should I report the bug there as well?
Sure, please do report it, it's our normal practice to inform upstream about bugs that affect MySQL as well, even though in case of optimizer bugs we rarely use upstream fixes.
The problem is that in the SELECT list we overwrite Item name according to AS clause but tablename is left as it was, so ORDER BY really thinks it resolve correctly (and it does according to new names) but of course it is wrong...
It is perfectly repeatable even with tables (and views):
create table t1 (field int);
|
insert into t1 values (10),(5),(3),(8),(20);
|
|
|
SELECT
|
sq.f2 AS f1,
|
sq.f1 AS f2
|
FROM (
|
SELECT
|
field AS f1,
|
1 AS f2
|
FROM t1
|
) AS sq
|
ORDER BY sq.f1;
|
|
|
|
|
create view v1 as SELECT field AS f1, 1 AS f2 FROM t1;
|
|
|
SELECT
|
sq.f2 AS f1,
|
sq.f1 AS f2
|
FROM v1 AS sq
|
ORDER BY sq.f1;
|
|
|
drop view v1;
|
|
|
create table t2 SELECT field AS f1, 1 AS f2 FROM t1;
|
|
|
SELECT
|
sq.f2 AS f1,
|
sq.f1 AS f2
|
FROM t2 AS sq
|
ORDER BY sq.f1;
|
|
|
drop table t1, t2;
|
revision-id: 772f7914db4fc6b293e438ad1c09dac59a239c5a (mariadb-10.0.21-41-g772f791)
parent(s): 9a3ff0789fbf6c0ea627415c90ae5487449f433b
committer: Oleksandr Byelkin
timestamp: 2015-10-27 11:17:52 +0100
message:
MDEV-8913 Derived queries with same column names as final projection causes issues when using Order By
find_item_in_list() now recognize view fields like a fields even if they rever to an expression.
—
Thank you very much for your work on this. Where can I see the commit? I checked Github but did not see a branch that seemed like it would be the one I should look at. I'm curious as to the fix.
Thanks!
For now you can see it only in e-mail list ( http://lists.askmonty.org/pipermail/commits/2015-October/008536.html ). it will be pushed on github after code review.
As discussed on IRC Sanja will additionally check why this query does not return errors:
SELECT 1 FROM (SELECT 1 as a) AS b HAVING (SELECT `SOME_GARBAGE`.b.a)=1;
|
revision-id: 34f126ee9b7da4f277da4ddd39d5d927c4bce9db (mariadb-10.0.21-41-g34f126e)
parent(s): 9a3ff0789fbf6c0ea627415c90ae5487449f433b
committer: Oleksandr Byelkin
timestamp: 2015-10-29 15:56:51 +0100
message:
MDEV-8913 Derived queries with same column names as final projection causes issues when using Order By
find_item_in_list() now recognize view fields like a fields even if they rever to an expression.
The problem of schema name do not taken into account for field with it and
derived table fixed.
Duplicating code removed
—
This patch looks Ok to push:
http://lists.askmonty.org/pipermail/commits/2015-October/008556.html
Thanks.
rgriffith, thanks for the report.
As a workaround, you can set 'optimizer_switch=derived_merge=off' (or just use unique column aliases, as you said – it will make the query much more readable anyway).
Smaller test case:
create table t1 (field int);insert into t1 values (10),(5),(3),(8),(20);SELECTsq.f2 AS f1,sq.f1 AS f2FROM (SELECTfield AS f1,1 AS f2FROM t1) AS sqORDER BY sq.f1;drop table t1;Actual result
f1 f21 101 51 31 81 20Expected result
f1 f21 31 51 81 101 20Reproducible on MariaDB 5.3 and higher, and also on MySQL 5.7.
Not reproducible on MariaDB 5.2 and MySQL 5.5/5.6.