simulate row_number with variables ignore order

Question

I was trying to help with this question Selecting rows based on a few rules

The idea is for each user select the row id with the language matching @lenguage otherwise select the first language created.

Because doesn't have row_number(), had to use user variables. Consider I add a field test_id so could put all the cases on the same table.

SQL DEMO

  SELECT t.* , (`language` = @language) as tt,
         @rn := if (@partition = CONCAT(`test_id`, '-', `user`),
                    @rn + 1,
                    if(@partition := CONCAT(`test_id`, '-', `user`), 1, 1)
                   ) as rn,
         @partition           
  FROM Table1 t
  CROSS JOIN ( SELECT @language := 'de', @rn := 0, @partition := '' ) as var
  ORDER BY CONCAT(`test_id`, '-', `user`), 
           (`language` = @language) DESC,
           `created`

OUTPUT

But even when the ORDER BY give the correct sort, partitions 1-4 and 3-4 don't put the language 'de' as first. So something is altering how the variable @rn is being assigned.

| test_id | id | title | language |              created | user | tt | rn | @partition |
|---------|----|-------|----------|----------------------|------|----|----|------------|
|       1 |  3 |     c |       de | 2019-01-03T00:00:00Z |    4 |  1 |  3*|        1-4 |
|       1 |  1 |     a |       en | 2019-01-01T00:00:00Z |    4 |  0 |  1 |        1-4 |
|       1 |  2 |     b |       es | 2019-01-02T00:00:00Z |    4 |  0 |  2 |        1-4 |
|       2 |  1 |     a |       en | 2019-01-01T00:00:00Z |    4 |  0 |  1 |        2-4 |
|       2 |  2 |     b |       es | 2019-01-02T00:00:00Z |    4 |  0 |  2 |        2-4 |
|       3 |  1 |     a |       en | 2019-01-01T00:00:00Z |    3 |  0 |  1 |        3-3 |
|       3 |  3 |     b |       de | 2019-01-03T00:00:00Z |    4 |  1 |  2*|        3-4 |
|       3 |  2 |     b |       es | 2019-01-02T00:00:00Z |    4 |  0 |  1 |        3-4 |
|       3 |  4 |     c |       de | 2019-01-04T00:00:00Z |    5 |  1 |  1 |        3-5 |

MySQL manual says something about *"The order of evaluation for expressions involving user variables is undefined."* pretty sure that happens here .. @Barmar 's suggestion seams to be correct.. — Raymond Nijland, Aug 01 '19 at 22:06
@Barmar I add tt for debug, i had `(language = @language) DESC`in the `order by`. Unless you mean I have to create a subquery first to order before doing the @rn? — Juan Carlos Oropeza, Aug 01 '19 at 22:06
Whats is the complete output which you expected, you should something about expecting the `de` first in a group? — Raymond Nijland, Aug 01 '19 at 22:08
Yes @RaymondNijland that is why I use `if()` to make sure the correct order is applied. The variables only change inside the `if()` — Juan Carlos Oropeza, Aug 01 '19 at 22:08
@RaymondNijland for partition `1-4` I expect 'de' be 1 and then 2,3. The order is correct but the variable should be `1,2,3` not `3,1,2` — Juan Carlos Oropeza, Aug 01 '19 at 22:09
you know how it works, ideally we would love to see a Minimal, Complete, and Verifiable example. ? — Raymond Nijland, Aug 01 '19 at 22:10
*"What part of MVC you think is missing"* Your comment only explains results for the `test_id = 1` based on the `OUTPUT` but not for the `test_id = 2 / 3` cases? You didn't confirm those ordering where correct or incorrect.. Unless iam missing something obvious here as it is late over here.. — Raymond Nijland, Aug 01 '19 at 22:17
The other cases are correct because doesn't have 'de' , only the rows I mark with * in the ouput has error. — Juan Carlos Oropeza, Aug 01 '19 at 22:19
Yes i see, problem is that simulating MySQL 8 syntax `ROW_NUMBER() OVER(PARTITION BY .. ORDER BY ...)` is hard/tricky to simulate with MySQL user variables which i assume you are trying to do here? — Raymond Nijland, Aug 01 '19 at 22:27
Yes, but the thing Is I have done this many times before. This behavior is freaking me out. — Juan Carlos Oropeza, Aug 01 '19 at 22:29
Why you make language null? you need a value to make it first — Juan Carlos Oropeza, Aug 01 '19 at 22:35
I want it to be first. The order is ok. The problem is @rn isnt 1 — Juan Carlos Oropeza, Aug 01 '19 at 22:35
*"Why you make language null? "* Your main problem is that `SELECT @language := 'de'` because of the `(language= @language) DESC` part `de` is always sorted first.. if you check with `SELECT @language := 'es'` `es` is sorted first.. — Raymond Nijland, Aug 01 '19 at 22:39
Yes, I want select the id matching language first if exists, otherwise select the older one, that is why there are 2 order condition at the end — Juan Carlos Oropeza, Aug 01 '19 at 22:52

score 2 · Accepted Answer · answered Aug 01 '19 at 22:10

Ordering is done after all the rows are selected. Your @rn variable is being set during the row selection, so it's using the internal order of rows, not the order specified in your ORDER BY clause.

You need to move the ordering into a subquery, then calculate @rn in the main query.

  SELECT t.*,
         @rn := if (@partition = CONCAT(`test_id`, '-', `user`),
                    @rn + 1,
                    if(@partition := CONCAT(`test_id`, '-', `user`), 1, 1)
                   ) as rn,
         @partition           
  FROM (
    SELECT *, (language = @language) AS tt
    FROM Table
    CROSS JOIN (SELECT @language := 'de') AS var
    ORDER BY CONCAT(test_id, '-', user),
            tt DESC,
            created
  ) AS t
  CROSS JOIN ( SELECT @rn := 0, @partition := '' ) as var

I see that work, but why here http://sqlfiddle.com/#!9/69d52b/10 doesnt need a subquery? — Juan Carlos Oropeza, Aug 01 '19 at 23:02
When you're ordering on a table column directly, MySQL performs an optimization and does the ordering during extraction. But the query in the question, you're ordering by a computed value, so it can't extract from the table in that order. — Barmar, Aug 01 '19 at 23:34

simulate row_number with variables ignore order

1 Answers1