regression in sql queries using sub queries (with order by)

[Sebastien Robin]

# Since mariadb version 5.3.3, I do have regression with sql queries using
# sub queries (with order by) and user variables. Theses queries just worked
# fine since many months/years, and they now return wrong result. May be
# it's just me doing something that's not supposed to work. I include here
# detailed explanation. Any hint would be highly appreciated

# I wish to parse this stock table in a given order and
# display in the same time a column showing the quantity
# cumulated. So expected result is (with "ORDER BY uid DESC") :
# +-----+----------+----------------+
# | uid | quantity | total_quantity |
# +-----+----------+----------------+
# | 3 | 4 | 4 |
# | 1 | 2 | 6 |
# +-----+----------+----------------+

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;

# that was giving the expected result for us since a very long time,
# on older version of mariadb and mysql. We were relying on the fact,
# in our usage, that mariadb was always parsing the subquery in the specified
# order. But theses days (5.5.22-MariaDB-log), it does not work, it give :
# +-----+----------+----------------+
# | uid | quantity | total_quantity |
# +-----+----------+----------------+
# | 1 | 2 | 2 |
# | 3 | 4 | 6 |
# +-----+----------+----------------+
# we want uid to be 3 then 1
#
# It start failing after we moved from mariadb 5.3.2-beta to 5.3.3-rc
# In the changelog http://kb.askmonty.org/en/mariadb-533-changelog
# I see that there is optimization on "useless subquery clauses", it
# could be related.

# Is it expected that the outer query does not read the subquery in
# the order specified by the subquery ?
# Is there a way to solve this problem with mariadb if this is not considered
# as a regression?

[Elena Stepanova (elenst)]

Hi,

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

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

SET optimizer_switch = 'derived_merge=off';

# 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;

# End of workaround 1

# uid quantity total_quantity
# 3 4 4
# 1 2 6

#
# Workaround 2
# ----------------------

# 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;

# End of workaround 2

# uid quantity total_quantity
# 3 4 4
# 1 2 6

[Elena Stepanova (elenst)]

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.

[Oleksandr "Sanja" Byelkin (sanja-byelkin)]

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.

[Oleksandr "Sanja" Byelkin (sanja-byelkin)]

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.