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@mariadb.org