[debian-mysql] help with incorrect result with a mariadb server (different versions and currently with 1:10.3.25-0+deb10u1 or 1:10.3.25-0ubuntu1)

[Patrice Duroux]

Hi,

More on that, I switched today one system (Ubuntu) to use mysql-server-8.0
(8.0.21-1) and this does not happen.
I will try also on my Debian Sid laptop.
My production system is running Debian Buster 10.6 with a MariaDB server
having this trouble.
It seems that there is a discrepancy between MySQL and MariaDB server on
such queries.
How to address this?

Thanks,
Patrice

Le lun. 2 nov. 2020 à 13:48, Patrice Duroux <patrice.duroux at gmail.com> a
écrit :

> Hi,
>
> I don't know where to address this.
> I have tried to submit a post at maria-discuss
> <https://launchpad.net/~maria-discuss> following what is indicated here:
> https://mariadb.org/contribute/ but my post never got in without any
> notification back and I checked:
> https://lists.launchpad.net/maria-discuss/date.html
>
> The structure of the SQL query is:
>
> SELECT id,a,b,c
> FROM main
> LEFT JOIN (SELECT id,... AS a, ... AS b, ... AS c FROM ...) AS USING (id)
>
> First the result is fine without any WHERE clause.
> Then adding a WHERE clause of the form 'WHERE id IN (SELECT ...)',
> the columns a,b,c are correct or become all null when it should not be the
> case.
> It is like suddenly the subquery result in the LEFT JOIN becomes empty.
>
> Suspecting some optimizer troubles, I have also tried to play with the
> optimizer_switch variable (even switching almost everything to off) but
> without any success.
>
> Same query was working well with MySQL server before Debian switched to
> MariaDB.
>
> Thanks,
> Patrice
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://alioth-lists.debian.net/pipermail/pkg-mysql-maint/attachments/20201109/85e6d70f/attachment.html>