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
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:
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
added a comment - 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:
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
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.
Elena Stepanova
added a comment - 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.
Oleksandr Byelkin
added a comment - 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.
Oleksandr Byelkin
added a comment - 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.
Bug
Trivial
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;