1

As discussed elsewhere in this forum (e.g. here), one can use variables in MySQL to emulate row_number() over (partition by ... order by ...) functionality in MS SQL Server. For example, a code like this:

SELECT 
    col1, 
    col2, 
    ROW_NUMBER() OVER (PARTITION BY col1, col2 ORDER BY col3 DESC) AS rowNum
FROM Table1

can be implemented in MySQL like this:

SELECT 
    @rowNum := 
        CASE 
            WHEN col1<>@col1 OR col2<>@col2 THEN 1 
            ELSE @rowNum+1 
        END rowNum
    , @col1 := col1 col1
    , @col2 := col2 col2
    FROM
    (SELECT @col1 := 'xxx', @col2 := 'yyy', @rowNum := 0) a,
    (SELECT col1, col2, col3 FROM Table1 ORDER BY col1, col2, col3 DESC) b

While this is quite fast (since it avoids doing a self-join), yet I am hesitant to put this into production since it makes detailed assumptions about query execution plan which may or may not be portable. In particular, it assumes that:

1) Rows of table 'b' are processed in their sorted order by the outer query,

2) Columns of the outer query are calculated from left to right (i.e. rowNum -> col1 -> col2.

These assumptions seem to break the usual abstraction of SQL syntax that I am accustomed to. Also, this query requires the programmer to ensure that the initial values that he/she supplies for col1 and col2 don't exist in Table1. Again, this is bad design in my opinion. This brings me to a related question, is this latter form (using variables) ANSI SQL compliant (92 or 99), or is it a feature of MySQL? What is a definite source to decide if a given syntax is ANSI SQL complaint or not? Would you feel comfortable enough to put the second version into production?

Community
  • 1
  • 1
asmahani
  • 13
  • 4

1 Answers1

0

No, the MySQL solution is not standard SQL. MySQL user variables are an extension to ANSI/ISO SQL.

SQL-99 Complete, Really is a free, online resource describing the SQL specification in a human-readable manner. It's an online version, hopefully posted with permission of its authors, of the book by the same name.

However, I don't know of a similar resource for SQL:2003 or later. You'd have to purchase the specification documents from ISO. They are not inexpensive, and they are dry reading.

IMHO, trying to limit yourself to SQL that is 100% portable and compliant with the abstraction of the language is too constraining. For example, try to find a single SQL data type that is implemented exactly the same in all brands of RDBMS, and exactly matching the language specification. I haven't found one.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • If we focus on MySQL, what do you make of this, quoted from MySQL website: "As a general rule, you should never assign a value to a user variable and read the value within the same statement. You might get the results you expect, but this is not guaranteed. The order of evaluation for expressions involving user variables is undefined and may change based on the elements contained within a given statement..." Isn't this exactly what we are doing in the second query, i.e. assigning and reading col1 and col2 in the outer query? – asmahani Jun 28 '13 at 14:03
  • The statement is more of an indemnification for the MySQL product. If they change their implementation in ways that the SQL standard allows them to, you are required to not get upset with them. :-) – Bill Karwin Jun 28 '13 at 15:26