Daniel Black added a comment - 2024-10-11 05:48 - edited mariadb --column-type-info Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 3 Server version: 10.5.27-MariaDB Source distribution   Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.   Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.   MariaDB [(none)]> select case when true then ('i' || 'o') else '0' end as c; Field 1: `c` Org_field: `` Catalog: `def` Database: `` Table: `` Org_table: `` Type: VAR_STRING Collation: utf8_general_ci (33) Length: 3 Max_length: 1 Decimals: 39 Flags: NOT_NULL     +---+ | c | +---+ | 0 | +---+ 1 row in set, 4 warnings (0.001 sec)   MariaDB [(none)]> show warnings; .. +---------+------+---------------------------------------+ | Level | Code | Message | +---------+------+---------------------------------------+ | Warning | 1292 | Truncated incorrect DOUBLE value: 'i' | | Warning | 1292 | Truncated incorrect DOUBLE value: 'o' | | Warning | 1292 | Truncated incorrect DOUBLE value: 'i' | | Warning | 1292 | Truncated incorrect DOUBLE value: 'o' | +---------+------+---------------------------------------+ 4 rows in set (0.001 sec)   MariaDB [(none)]> select ('i' || 'o') as c; Field 1: `c` Org_field: `` Catalog: `def` Database: `` Table: `` Org_table: `` Type: LONG Collation: binary (63) Length: 1 Max_length: 1 Decimals: 0 Flags: NOT_NULL BINARY NUM     +---+ | c | +---+ | 0 | +---+ 1 row in set, 4 warnings (0.001 sec)   MariaDB [(none)]> show warnings; ... +---------+------+---------------------------------------+ | Level | Code | Message | +---------+------+---------------------------------------+ | Warning | 1292 | Truncated incorrect DOUBLE value: 'i' | | Warning | 1292 | Truncated incorrect DOUBLE value: 'o' | | Warning | 1292 | Truncated incorrect DOUBLE value: 'i' | | Warning | 1292 | Truncated incorrect DOUBLE value: 'o' | +---------+------+---------------------------------------+ 4 rows in set (0.001 sec) Where you expecting || to be a concatination? Because its a logical or . In Oracle mode it would be a concatination. As such 'o' and 'i' are converted to a double before comparison. In the first case, the result is a string, but its still a 0 resulting from 0.0 || 0.0. second case if we take 0 as the result: MariaDB [(none)]> select FIELD(0, 'c') as c0; Field 1: `c0` Org_field: `` Catalog: `def` Database: `` Table: `` Org_table: `` Type: LONG Collation: binary (63) Length: 3 Max_length: 1 Decimals: 0 Flags: NOT_NULL BINARY NUM     +----+ | c0 | +----+ | 1 | +----+ 1 row in set, 1 warning (0.001 sec)   MariaDB [(none)]> show warnings; +---------+------+---------------------------------------+ | Level | Code | Message | +---------+------+---------------------------------------+ | Warning | 1292 | Truncated incorrect DOUBLE value: 'c' | +---------+------+---------------------------------------+ So c is converted to double, the type of the first arg, then they match, hence returning 1.

HeShan added a comment - 2024-10-11 07:08 Thank you for your explanation danblack . I now understand the logic behind the results of these two SQL statements: select FIELD(0, 'c' ) as c0; + ----+ | c0 | + ----+ | 1 | + ----+ 1 row in set , 1 warning (0.00 sec)   select FIELD( '0' , 'c' ) as c0; + ----+ | c0 | + ----+ | 0 | + ----+ 1 row in set (0.00 sec) Additionally, it would seem that CASE WHEN TRUE THEN ('i' || 'o') ELSE '0' END and ('i' || 'o') should logically return the same results. However, due to implicit type conversion, one returned '0' while the other returned 0, leading to completely different outcomes when using NULLIF and FIELD. I believe this should be modified to eliminate such discrepancies, especially since I've noticed this issue: select nullif (0,0); + -------------+ | nullif (0,0) | + -------------+ | NULL | + -------------+ 1 row in set (0.01 sec)   select nullif (0, '0' ); + ---------------+ | nullif (0, '0' ) | + ---------------+ | NULL | + ---------------+ 1 row in set (0.00 sec)     select FIELD(0,0); + ------------+ | FIELD(0,0) | + ------------+ | 1 | + ------------+ 1 row in set (0.00 sec)   select FIELD(0, '0' ); + --------------+ | FIELD(0, '0' ) | + --------------+ | 1 | + --------------+ 1 row in set (0.00 sec)       It appears that the difference between 0 and '0' should not impact these two functions.

Daniel Black added a comment - 2024-10-11 08:01 Agree this is quite weird. A optimization in the case statement internally within MariaDB could just eliminate the conversion. Leaving for the expert Bar to decide. However odd it may be, it could still be standards compliant. As I've learnt a few times, always check your warnings and data types. As much as data types are hidden they do have an impact.

Alexander Barkov added a comment - 2024-11-21 08:08 This is expected behavior: The data type for the CASE statement is VARCHAR in this query: select FIELD(( case when true then ( 'i' || 'o' ) else '0' end ), 'c' ) as c0; So the function FIELD compares '0' to 'c' as strings. They are not equal. In order to get the desired result, please rewrite the query as: select FIELD(( case when true then ( 'i' || 'o' ) else 0 end ), 'c' ) as c0; In this case the data type for the CASE statement is INT. So it compares 0 and 0, which give equality, and the query returns 1: + ----+ | c0 | + ----+ | 1 | + ----+