Mariadb 10.3 -10.5 join on 2 conditions fails on occasion / if is evaluated differently then on or where

Question

The query was reduced to the least number of parts that still produce the error. Just for explanation purposes.

SELECT
        s.Saison,
        t.Saison,
        IF(s.Saison = t.Saison,'gleich','unterschiedlich')
FROM tbl_spielplan s
LEFT JOIN
          (SELECT
                        Kino_Id,
                        Saison,
                        (SUM(Besucher_Zahlend+Besucher_Frei)/COUNT(*)) AS Summe_T
                FROM
                        tbl_spielplan
                WHERE
                        Film_gezeigt='1'
                GROUP BY
                        Kino_Id,
                        Saison) AS t
ON s.Kino_Id = t.Kino_Id
WHERE
        Film_Id   = '3109'
AND     s.Kino_Id < 300
AND     s.Saison = t.Saison; 

Gives no Matches found. If I omit the s.Saison = t.Saison i get

s.Saison	t.Saison	if clause match
2019 WiSe	2019 WiSe	gleich
2019 WiSe	2019 SoSe	unterschiedlich
2019 WiSe	2018 WiSe	unterschiedlich

if i match only on s.Saison = t.Saison the join works but it will fail if i add s.Kino_Id = t.Kino_Id in the where clause. Now for the Really weird part, if I add AND BINARY s.Saison = t.Saison; in the on or where clause the problem stops. Also not all datasets produce this error. I Tested it in multiple instances of mariadb 10.3 and 10.5. This behavior is new, half a year ago the query worked every time. in Mariadb 10.1 the query works without fail.

I Already found a workaround, I am more interested in the why, as in my Understanding, this should not be happening.

Answer 1

you are using left outer join from in wrong way. try this

SELECT
        s.Saison,
        t.Saison,
        IF(s.Saison = t.Saison,'gleich','unterschiedlich')
FROM tbl_spielplan s
LEFT JOIN
          (SELECT
                        Kino_Id,
                        Saison,
                        (SUM(Besucher_Zahlend+Besucher_Frei)/COUNT(*)) AS Summe_T
                FROM
                        tbl_spielplan
                WHERE
                        Film_gezeigt='1'
                GROUP BY
                        Kino_Id,
                        Saison) AS t
ON s.Kino_Id = t.Kino_Id and s.Saison = t.Saison
WHERE
        Film_Id   = '3109'
AND     s.Kino_Id < 300 ;

The table with outer join should not be used in where condition.