Details
-
Bug
-
Status: Closed (View Workflow)
-
Trivial
-
Resolution: Won't Fix
-
None
-
None
-
None
Description
- 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?
Attachments
Activity
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.
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.
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.
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:
#---------------------
SET optimizer_switch = 'derived_merge=off';
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;
#
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;