Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-8913

Derived queries with same column names as final projection causes issues when using Order By

    XMLWordPrintable

    Details

    • Sprint:
      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

            People

            Assignee:
            sanja Oleksandr Byelkin
            Reporter:
            rgriffith Ryan Griffith
            Votes:
            0 Vote for this issue
            Watchers:
            5 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved: