maria-discuss team mailing list archive

[Sergei Golubchik <serg@xxxxxxxxxxx>]

Hi, Mats!

On Jan 09, Ian Gilfillan wrote:
> On 09/01/2018 01:14, Mats Eklund wrote:
> > Need some help here. I have the following expression:
> >
> > SELECT IF(1=1, 0x2, 0x4) | 0x1
> >
> > which in MySQL evaluates to: 3
> >
> > but in MariaDB it evaluates to: 1
> >
> > MySQL's result seems correct to me. Would appreciate any advice on this.
> >
> This is related to the change made in 10.0.3. See:
> https://mariadb.com/kb/en/library/hexadecimal-literals/
> Also see the explanation in:
> https://jira.mariadb.org/browse/MDEV-6092

Not exactly. MDEV-6092 is about x'01' syntax.

This particular behavior is due to the bug
https://jira.mariadb.org/browse/MDEV-4863
which was fixed in 5.5.33, 10.0.5, etc.

In short, IF() is a so called "hybrid" function, the type of the result
depends on the type of the arguments (unlike CONCAT(), for example, that
always returns a string).

But hexadecimal literals are "magic", their type is determined run-time,
depending on what kind of the context they are used in.

IF() removes this magic when it tries to derive the type of the result,
and ends up treating hexadecimal literals as strings.

In MySQL, MDEV-4863 is not fixed, IF() does not tries to derive the type
of the result, and that produces incorrect results in cases like

  SELECT CAST(IF(1,DATE('2001-01-01'),DATE('2001-01-02')) AS SIGNED);

In MariaDB the result is 20010101, in MySQL the result is 2001.

The workaround in your case would be to remove magic behavior of
hexadecimal literals, by explicitly casting them into the correct type,
as Ian explained below:

> If you specifically CAST it as a number it will work as you expect, for 
> example
> 
> SELECT IF(1=1, CAST(0x2 AS UNSIGNED), CAST(0x4 AS UNSIGNED)) | 0x1;
> +-------------------------------------------------------------+
> | IF(1=1, CAST(0x2 AS UNSIGNED), CAST(0x4 AS UNSIGNED)) | 0x1 |
> +-------------------------------------------------------------+
> |                                                           3 |
> +-------------------------------------------------------------+

Regards,
Sergei
Chief Architect MariaDB
and security@xxxxxxxxxxx