CODEWITHSUNDEEP
mysqlmariadb
user538565
user538565

Reputation: 513

MySQL vs MariaDB: add row number

I have a SQL like this:

select a.*, (@r := @r+1) as seq
from (
    ....
    order by xx asc
) as a
join (select @r:=0) as y

I used to use MySQL, and this SQL ran just fine.
Unfortunately, when I migrate to MariaDB,
this SQL produces wierd results.
The results aren't in the correct order.
The "order by" clause seems to be ignored...

Anybody knows why?

Upvotes: 0

Views: 600

Answers (1)

Rick James
Rick James

Reputation: 142366

The SQL standard says it is OK. More Info.

One solution (as mentioned above) is to set optimizer_switch='derived_merge=off'.

Upvotes: 1

Related Questions