There is a 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 request:
SELECT DISTINCT company, quote ,LAG(quote) OVER(PARTITION BY company ORDER BY t) FROM tab;
Result - The result is consistent with RDBMS.
+---------+-------+------+ | 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
DB-Fiddle.com Demo - MySQL 8.0
But when I try to do 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 :
- WITH
- ON
- Join
- WHERE
- GROUP BY
- With CUBE / ROLLUP
- HAVING
- SELECT
- Distinct
...
DISTINCT after SELECT , so the correct path should be similar to PostgreSQL / SQL Server / Oracle. (Yes, I know that I could use the DISTINCT subquery / delete, but that is not a question).
Is this behavior an error or is it working correctly (documentation)?