[MDEV-8913] Derived queries with same column names as final projection causes issues when using Order By - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Affects Version/s: 10.0.21, 10.1.7, 5.3.13, 5.5(EOL), 10.0(EOL), 10.1(EOL)
Fix Version/s: 10.0.23, 10.1.9
Component/s: Data Manipulation - Subquery, Optimizer
Labels:
- derived
- wrong_result

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

- Sort By Name
- Sort By Date
- Ascending
- Descending
- Thumbnails
- List
- Download All

issue_example.jpg
238 kB
2015-10-07 18:53

Activity

Ascending order - Click to sort in descending order

Ryan Griffith created issue - 2015-10-07 18:53

Ryan Griffith made changes - 2015-10-07 18:55

Field	Original Value	New Value
Description	DDL {code:java} CREATE TABLE `fact_web_session_summary` ( `id` INT(11) NOT NULL AUTO_INCREMENT, `date_id` INT(11) NOT NULL, `web_profile_id` INT(11) NOT NULL, `web_visitor_location_id` INT(11) NOT NULL, `web_platform_device_id` INT(11) NOT NULL, `web_source_id` INT(11) NOT NULL, `web_session_flags_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; {code} {noformat} 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 {noformat} 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	h4. DDL {noformat} CREATE TABLE `fact_web_session_summary` ( `id` INT(11) NOT NULL AUTO_INCREMENT, `date_id` INT(11) NOT NULL, `web_profile_id` INT(11) NOT NULL, `web_visitor_location_id` INT(11) NOT NULL, `web_platform_device_id` INT(11) NOT NULL, `web_source_id` INT(11) NOT NULL, `web_session_flags_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; {noformat} h4. SQL Statement to Issue {noformat} 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 {noformat} h4. 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

Ryan Griffith made changes - 2015-10-07 19:50

Affects Version/s

10.0.21 [ 19406 ]

Ryan Griffith made changes - 2015-10-07 19:57

Description

h4. DDL

{noformat}
CREATE TABLE `fact_web_session_summary` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`date_id` INT(11) NOT NULL,
`web_profile_id` INT(11) NOT NULL,
`web_visitor_location_id` INT(11) NOT NULL,
`web_platform_device_id` INT(11) NOT NULL,
`web_source_id` INT(11) NOT NULL,
`web_session_flags_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;

{noformat}

h4. SQL Statement to Issue

{noformat}
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
{noformat}

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

h4. DDL

{noformat}
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;

{noformat}

h4. SQL Statement to Issue

{noformat}
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
{noformat}

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

Elena Stepanova made changes - 2015-10-19 15:41

Component/s		Optimizer [ 10200 ]
Fix Version/s		10.1 [ 16100 ]
Fix Version/s		10.0 [ 16000 ]
Affects Version/s		5.3.13 [ 12602 ]
Affects Version/s		10.1 [ 16100 ]
Affects Version/s		10.0 [ 16000 ]
Affects Version/s		5.5 [ 15800 ]
Assignee		Oleksandr Byelkin [ sanja ]
Labels		derived wrong_result

Elena Stepanova made changes - 2015-10-19 15:41

Status

Open [ 1 ]

Confirmed [ 10101 ]

Oleksandr Byelkin made changes - 2015-10-20 18:06

Status

Confirmed [ 10101 ]

In Progress [ 3 ]

Oleksandr Byelkin made changes - 2015-10-20 18:28

Status

In Progress [ 3 ]

Stalled [ 10000 ]

Rasmus Johansson (Inactive) made changes - 2015-10-21 10:45

Sprint

10.0.22 [ 17 ]

Rasmus Johansson (Inactive) made changes - 2015-10-21 10:45

Rank

Ranked higher

Oleksandr Byelkin made changes - 2015-10-27 10:41

Status

Stalled [ 10000 ]

In Progress [ 3 ]

Oleksandr Byelkin made changes - 2015-10-27 12:20

Status

In Progress [ 3 ]

Stalled [ 10000 ]

Oleksandr Byelkin made changes - 2015-10-27 12:59

Assignee	Oleksandr Byelkin [ sanja ]	Sergey Vojtovich [ svoj ]
Status	Stalled [ 10000 ]	In Review [ 10002 ]

Oleksandr Byelkin made changes - 2015-10-27 13:56

Assignee

Sergey Vojtovich [ svoj ]

Oleksandr Byelkin [ sanja ]

Oleksandr Byelkin made changes - 2015-10-27 13:56

Assignee

Oleksandr Byelkin [ sanja ]

Alexander Barkov [ bar ]

Rasmus Johansson (Inactive) made changes - 2015-10-28 11:26

Sprint

10.0.22 [ 17 ]

10.0.22, 10.1.9 [ 17, 18 ]

Rasmus Johansson (Inactive) made changes - 2015-10-28 11:26

Rank

Ranked higher

Alexander Barkov made changes - 2015-10-29 13:02

Status

In Review [ 10002 ]

Stalled [ 10000 ]

Alexander Barkov made changes - 2015-10-29 13:41

Assignee

Alexander Barkov [ bar ]

Oleksandr Byelkin [ sanja ]

Oleksandr Byelkin made changes - 2015-10-29 15:31

Status

Stalled [ 10000 ]

In Progress [ 3 ]

Oleksandr Byelkin made changes - 2015-10-29 16:59

Assignee	Oleksandr Byelkin [ sanja ]	Alexander Barkov [ bar ]
Status	In Progress [ 3 ]	In Review [ 10002 ]

Alexander Barkov made changes - 2015-10-30 08:19

Assignee	Alexander Barkov [ bar ]	Oleksandr Byelkin [ sanja ]
Status	In Review [ 10002 ]	Stalled [ 10000 ]

Oleksandr Byelkin made changes - 2015-10-30 15:53

Fix Version/s		10.0.23 [ 20401 ]
Fix Version/s		10.1.9 [ 20301 ]
Fix Version/s	10.0 [ 16000 ]
Fix Version/s	10.1 [ 16100 ]
Resolution		Fixed [ 1 ]
Status	Stalled [ 10000 ]	Closed [ 6 ]

Sergei Golubchik made changes - 2021-12-06 21:42

Workflow

MariaDB v3 [ 71905 ]

MariaDB v4 [ 149698 ]

People

Assignee:: Oleksandr Byelkin

Reporter:: Ryan Griffith

Votes:: 0 Vote for this issue

Watchers:: 5 Start watching this issue

Dates

Created:: 2015-10-07 18:53

Updated:: 2015-10-30 15:53

Resolved:: 2015-10-30 15:53

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server

Details

Description

DDL

SQL Statement to Issue

Synopsis

Attachments

Attachments

Activity

People

Dates

Git Integration