[MDEV-3706] LP:985828 - regression in sql queries using sub queries (with order by) Created: 2012-04-19  Updated: 2012-10-04  Resolved: 2012-10-04

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: None
Fix Version/s: None

Type: Bug Priority: Trivial
Reporter: Sebastien Robin (Inactive) Assignee: Oleksandr Byelkin
Resolution: Won't Fix Votes: 0
Labels: Launchpad

Attachments: XML File LPexportBug985828.xml    

 Description   
  1. Since mariadb version 5.3.3, I do have regression with sql queries using
  2. sub queries (with order by) and user variables. Theses queries just worked
  3. fine since many months/years, and they now return wrong result. May be
  4. it's just me doing something that's not supposed to work. I include here
  5. detailed explanation. Any hint would be highly appreciated
  1. I wish to parse this stock table in a given order and
  2. display in the same time a column showing the quantity
  3. cumulated. So expected result is (with "ORDER BY uid DESC") :
  4. ---------------------------
  5. uid quantity total_quantity
  6. ---------------------------
  7. 3 4 4
  8. 1 2 6
  9. ---------------------------

DROP TABLE IF EXISTS stock_table;
CREATE TABLE stock_table ( `uid` BIGINT UNSIGNED NOT NULL, `quantity` BIGINT UNSIGNED NOT NULL);
INSERT INTO stock_table values (1, 2), (3, 4);

SET @total_quantity := 0 ;
SELECT
q1.*,
@total_quantity := @total_quantity + q1.quantity as total_quantity
FROM (SELECT stock_table.uid,
stock_table.quantity
FROM
stock_table
ORDER BY
stock_table.uid DESC) as q1;

  1. that was giving the expected result for us since a very long time,
  2. on older version of mariadb and mysql. We were relying on the fact,
  3. in our usage, that mariadb was always parsing the subquery in the specified
  4. order. But theses days (5.5.22-MariaDB-log), it does not work, it give :
  5. ---------------------------
  6. uid quantity total_quantity
  7. ---------------------------
  8. 1 2 2
  9. 3 4 6
  10. ---------------------------
  11. we want uid to be 3 then 1
    #
  12. It start failing after we moved from mariadb 5.3.2-beta to 5.3.3-rc
  13. In the changelog http://kb.askmonty.org/en/mariadb-533-changelog
  14. I see that there is optimization on "useless subquery clauses", it
  15. could be related.
  1. Is it expected that the outer query does not read the subquery in
  2. the order specified by the subquery ?
  3. Is there a way to solve this problem with mariadb if this is not considered
  4. as a regression?

 Comments   
Comment by Elena Stepanova [ 2012-04-19 ]

Re: regression in sql queries using sub queries (with order by)
Hi,

If you want to get the old behavior, I suppose you can try either of these, whichever suits you best:

  1. Workaround 1
    #---------------------
  2. Modify default optimizer switch at runtime for the session or globally in the cnf:

SET optimizer_switch = 'derived_merge=off';

  1. The rest of the test case is unchanged

DROP TABLE IF EXISTS stock_table;
CREATE TABLE stock_table ( `uid` BIGINT UNSIGNED NOT NULL, `quantity` BIGINT UNSIGNED NOT NULL);
INSERT INTO stock_table values (1, 2), (3, 4);

SET @total_quantity := 0 ;
SELECT
q1.*,
@total_quantity := @total_quantity + q1.quantity as total_quantity
FROM (SELECT stock_table.uid,
stock_table.quantity
FROM
stock_table
ORDER BY
stock_table.uid DESC) as q1;

  1. End of workaround 1
  1. uid quantity total_quantity
  2. 3 4 4
  3. 1 2 6

#

  1. Workaround 2
  2. ----------------------
  1. Convert FROM subquery into a view:

DROP TABLE IF EXISTS stock_table;
CREATE TABLE stock_table ( `uid` BIGINT UNSIGNED NOT NULL, `quantity` BIGINT UNSIGNED NOT NULL);
INSERT INTO stock_table values (1, 2), (3, 4);

CREATE VIEW q1 AS
SELECT stock_table.uid,
stock_table.quantity
FROM
stock_table
ORDER BY
stock_table.uid DESC;

SET @total_quantity := 0 ;
SELECT
q1.*,
@total_quantity := @total_quantity + q1.quantity as total_quantity
FROM q1;

  1. End of workaround 2
  1. uid quantity total_quantity
  2. 3 4 4
  3. 1 2 6
Comment by Elena Stepanova [ 2012-04-19 ]

Re: regression in sql queries using sub queries (with order by)
Logically, I don't see a reason why the query is obliged to behave the way it did before, but I will forward the bug report to Igor to confirm the current behavior is legitimate.

Comment by Oleksandr Byelkin [ 2012-04-24 ]

Re: regression in sql queries using sub queries (with order by)
Now subquery in the FROM clause could be merged so it behave as merged VIEW. it was always so that VIEWS if merged algorithm applied and it is not part of UNION append upper query ORDER BY with its one. If one put ORDER BY in subquery or view it should know what he/she is doing. IMHO it is not a bug.

Comment by Oleksandr Byelkin [ 2012-04-24 ]

Re: regression in sql queries using sub queries (with order by)
I forgot solution to the problem: do not put ORDER BY in the SUBQUERY/VIEW if you do not want to have ordered results in most cases.

Comment by Rasmus Johansson (Inactive) [ 2012-04-24 ]

Launchpad bug id: 985828

Generated at Thu Feb 08 06:50:37 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.