MariaDB query problems (ORDER BY ignored)

Question

I have a MySQL query that ran fine but my hosting proivder change my database to MariaDB. The query in itself works fine but I can't get the sorting to work correctly as it did in MySQL.

Is there some syntax that has changed or is it not available anymore?

    SELECT
 id,
  name,
  position,
  player1,
  player1_thru,
  player1_to_par,
  player2,
  player2_thru,
  player2_to_par,
  player3,
  player3_thru,
  player3_to_par,
  team_total,
  IF (counter = 1, @rank:= placeholder, @rank) AS ranker
FROM(
  SELECT
    *,
    @c := @c +1 AS placeholder,
    IF(@a <> team_total, @b := @b := 1, @b :=0) AS counter,
    @a := team_total
  from(
    SELECT
        usopen_2018.id,
        usopen_2018.position
        , usopen_2018.name
       , usopen_2018.player1
       , player1_score.hole as player1_thru
       , player1_score.to_par_s AS player1_to_par
       , usopen_2018.player2
       , player2_score.hole as player2_thru
       , player2_score.to_par_s AS player2_to_par
       , usopen_2018.player3
       , player3_score.hole as player3_thru
       , player3_score.to_par_s AS player3_to_par
       , (player1_score.`to_par` + player2_score.`to_par` + player3_score.`to_par`) AS team_total
    FROM usopen_2018
    INNER JOIN leaderboard_A AS player1_score
      ON player1_score.name = usopen_2018.player1
      AND player1_score.tournament_name = 'U.S. Open'
      AND player1_score.year = 2018
    INNER JOIN leaderboard_A AS player2_score
      ON player2_score.name = usopen_2018.player2
      AND player2_score.tournament_name = 'U.S. Open'
      AND player2_score.year = 2018
    INNER JOIN leaderboard_A AS player3_score
      ON player3_score.name = usopen_2018.player3
      AND player3_score.tournament_name = 'U.S. Open'
      AND player3_score.year = 2018
    WHERE usopen_2018.cut = 0 AND usopen_2018.wd = 0
    ORDER BY team_total, player1, player2, player3, name
  )AS t
)AS FINAL

Rick James · Accepted Answer · 2018-06-18 19:12:54Z

1

The ORDER BY needs to be moved out one level.

Here's why. MariaDB is now honoring the standard, which says that a subselect is an unordered set of rows. That is, the inner ORDER BY can be ignored. MariaDB has done so for the last few versions; MySQL is also doing so in the latest version.

In the past, MySQL and MariaDB blindly did any ORDER BY requested by the user. And then fed the results, still in order, to the next stage of the query.

LIMIT trick

If that does not work, add LIMIT 99999999 after the ORDER BY in the innermost SELECT. This should keep the Optimizer from ignoring the ORDER BY, while not actually doing any 'limit'.

edited Jun 18, 2018 at 19:12

answered Jun 18, 2018 at 3:28

Rick James

144k15 gold badges144 silver badges254 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Alan Over a year ago

Thanks I'll try to rearrange my query and see if I can get it working. I did try it and didn't get the required resultset.

Rick James Over a year ago

@Alan - I added another approach.

Collectives™ on Stack Overflow

MariaDB query problems (ORDER BY ignored)

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related