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
Bug
Major
