CODEWITHSUNDEEP
mysqlmariadb
Ese Mikes
Ese Mikes

Reputation: 57

MariaDB vs Mysql Query Trouble

Recently we change our database system from MySql to MariaDB and we are having some troubles with one query...

SELECT
    (SELECT
        IFNULL(label, '') FROM (
            SELECT
                ( SELECT label FROM llx_categorie WHERE   rowid = @r And type = 0 ) AS label,
                @r As r,
                ( Select  @r := fk_parent FROM llx_categorie WHERE   rowid = r And type = 0 ) As parent,
                @l := @l - 1 As lvl
            FROM( SELECT  @r := 732, @l := 5, @cl := 0 ) vars,
                llx_categorie h
            Where (@l > 0) And type = 0 And @r > 0 And label IS NOT NULL
            ORDER BY parent ASC 
        )  as t1
        LIMIT 0,1 ) as catlevel1,
     (SELECT
        IFNULL(label, '') FROM (
            SELECT
                ( SELECT label FROM llx_categorie WHERE   rowid = @r And type = 0 ) AS label,
                @r As r,
                ( Select  @r := fk_parent FROM llx_categorie WHERE   rowid = r And type = 0 ) As parent,
                @l := @l - 1 As lvl
            FROM( SELECT  @r := 732, @l := 5, @cl := 0 ) vars,
                llx_categorie h
            Where (@l > 0) And type = 0 And @r > 0 And label IS NOT NULL
            ORDER BY parent ASC 
        )  as t1
        LIMIT 1,1 ) as catlevel2

It's just a simple query for getting parent categories from category id @r732.

If we run this on MySql returns as expected, example.

+----------------------+--------------+
| catlevel1            | catlevel2    |
+----------------------+--------------+
| Todas las categorías | Sonido-Audio |
+----------------------+--------------+

But in MariaDB returns this.

+----------------------+--------------+
| catlevel1            | catlevel2    |
+----------------------+--------------+
| Todas las categorías | NULL         |
+----------------------+--------------+

Anyone knows how it can be possible? Or possible solutions? Missing configuration enabled o server? I´m getting crazy.

Thanks in advance.

Upvotes: 1

Views: 424

Answers (2)

Rick James
Rick James

Reputation: 142296

I think the problem stems from something like this:

Given a subquery with an ORDER BY and an outer query without an ORDER BY...

In old MySQL, the inner ordering was still valid in the outer``SELECT`.

In MariaDB, the outer ordering, not being specified, is unpredictable.

Possible solution: Move ORDER BY parent ASC out one level.

Another possible solution (for MariaDB): Set optimizer_switch='derived_merge=off' (or optimizer_switch='derived_merge=off,derived_with_keys=off', depending on the structure of your table). Reference.

I suspect it was just coincidence that you got the 'correct' catlevel1, but not catlevel2.

Also, get rid of llx_categorie h if you are not using it.

Upvotes: 1

Gewure
Gewure

Reputation: 1268

First, this is not true:

It's just a simple query

;)

Secondly, after i did some research into this (it suprised me), i noticed there were quite a lot of different results when querying mySQL or MariaDB, but most of them seem to be connected to ORDER BY and GROUP BY. Somebod elaborately also pointed out that mariaDB is more strict(=more ANSI compliant) with SIGNED and UNSIGNED Types. Maybe this helps you into the right direction.

I don't know the solution to this problem.

Upvotes: 0

Related Questions