CODEWITHSUNDEEP
mysqlsqlsql-serverpostgresql
Lukasz Szozda
Lukasz Szozda

Reputation: 175964

DISTINCT and LAG window function

There is simple table:

CREATE TABLE tab AS 
SELECT 1 AS t,   'G' AS company, 40 AS quote
UNION SELECT 2 , 'G',    60 
UNION SELECT 3 , 'S',    60 
UNION SELECT 4,  'S',    20;

And query:

SELECT DISTINCT company, quote ,LAG(quote) OVER(PARTITION BY company ORDER BY t)
FROM tab;

Output - result is consistent across RDBMSes.

+---------+-------+------+
| company | quote | lag  |
+---------+-------+------+
| G       |    40 | null |
| S       |    60 | null |
| G       |    60 | 40   |
| S       |    20 | 60   |
+---------+-------+------+

DBFiddle Demo - PostgreSQL

DBFiddle Demo - SQL Server

DBFiddle Demo - Oracle 11g

DBFiddle Demo - MariaDB

DBFiddle Demo - MySQL 8.0

But when I try to make some calculations I get different results:

SELECT DISTINCT company, quote - LAG(quote) OVER(PARTITION BY company ORDER BY t)
FROM tab;

PostgreSQL/SQL Server/Oracle (as I expected):

+---------+--------+
| company | result |
+---------+--------+
| G       | null   |
| G       | 20     |
| S       | null   |
| S       | -40    |
+---------+--------+

MariaDB/MySQL:

+----------+--------+
| company  | result |
+----------+--------+
| G        | null   |
| S        | null   |
+----------+--------+

Now as far as I know Logical Query Processing:

  1. FROM
  2. ON
  3. JOIN
  4. WHERE
  5. GROUP BY
  6. WITH CUBE/ROLLUP
  7. HAVING
  8. SELECT
  9. DISTINCT

...

DISTINCT is after SELECT so the correct way should be like PostgreSQL/SQL Server/Oracle.(Yes I know that I could use subquery/remove DISTINCT but it is not the point of the question).

Is this behaviour a bug or is it working correctly(documentation)?

Upvotes: 9

Views: 2792

Answers (1)

Lukas Eder
Lukas Eder

Reputation: 221106

Definitely a bug in both MySQL and MariaDB. I've reported the issue to the two databases, linking back to this Stack Overflow question:

Upvotes: 2

Related Questions