selectmariadblagmariadb-10.4

MariaDB absurdity using LAG function

Absurdity happening. Can someone help me with this? When I comment out some non-logical part the result changes. Screenshots are provided on Navicat.

SELECT 

(1=1) 
AND 
(
    (LAG(l) OVER (PARTITION BY ticker ORDER BY date) > 1 AND 1=1) 
OR (LAG(l) OVER (PARTITION BY ticker ORDER BY date) > 1 AND 1=1)
)

AS is_fs FROM `aggregates` WHERE YEAR(date) = 2016 AND ticker = 'GFF' ORDER BY is_fs DESC

And the result is NULL. While when commenting "(1=1) AND", the result becomes 1. Or even worse, when I delete 2nd part of the OR (which is BTW identical to the first part) again the result becomes 1. Or even the worst: when I delete "PARTITION BY ticker" from the firts part of "OR" again the result appears.

How this is even possible... What am I missing ?

Absurdity1

Absurdity2

Absurdity3

Data

Solution

  • Still don't know the reason, but changing MariaDB to MySQL-8 solved the issue, after trying to repair, backup reinstall, index drop-set, etc...