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
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.
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!