MySQL Bugs: #78796: @Value IS NULL assumed to always return FALSE in "Insert" complex expression.

Bug #78796	@Value IS NULL assumed to always return FALSE in "Insert" complex expression.
Submitted:	11 Oct 2015 17:01	Modified:	18 Oct 2015 22:54
Reporter:	Sergei Kaufman	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Command-line Clients	Severity:	S3 (Non-critical)
Version:	5.5.44-0ubuntu0.14.04.1 (Ubuntu)	OS:	Linux (Ubuntu LTS 14.04)
Assigned to:		CPU Architecture:	Any

Description:
At mysql client response to typed expression was:

mysql> set @Value =  null;
Query OK, 0 rows affected (0.00 sec)

mysql> set @A = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> set @B = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> 
mysql> set @Value = insert(@Value,(select if(@Value IS NULL,1,(length(@Value)+1))),1,case 1  when @A > @B then 'a' when @A = @B then 'b' when @A < @B then 'c' end);
Query OK, 0 rows affected (0.00 sec)

mysql> 
mysql> select @Value;
+--------+
| @Value |
+--------+
| NULL   |
+--------+
1 row in set (0.00 sec)

mysql> 

How to repeat:
Assumed response was to return "1" when the @Value IS NULL, on False "length(@Value)+1".

On this case , select if expression returns anyway False when @Value IS NULL.

Assumed that mysql intepreted the expression before the return and somehow returning FALSE when assumed to response return TRUE 

Suggested fix:

Please contact me by email to fix it.

Hi Sergei,

Thank you for your bug report. However, it turns out that this is not a bug. Our server is behaving exactly as designed, which is based on current SQL standard. If you read our manual you will find that it says for INSERT: "Returns NULL if any argument is NULL.".

Not a bug.

Description:
At mysql client response to typed expression was:

mysql> set @Value =  null;
Query OK, 0 rows affected (0.00 sec)

mysql> set @A = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> set @B = 0;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> set @Value = insert(@Value,(select if(@Value IS
NULL,1,(length(@Value)+1))),1,case 1  when @A > @B then 'a' when @A = @B
then 'b' when @A < @B then 'c' end);
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> select @Value;
+--------+
| @Value |
+--------+
| NULL   |
+--------+
1 row in set (0.00 sec)

mysql

it is possible to return null by this condition, but as i said before - this is not a simple expression, which consists from insert(), select if() and case flow when insert() function is the result of two above .. so by the logic it should return true or false but not NULL or anyway 'FALSE'

What is the logic of returning NULL at complex expression ?

@Value is NULL. @Value is the first argument of the INSERT() function. Hence, if you read my reply, NULL has to be returned.

Regarding the logic, we follow SQL standards on this matter.

Anyway, logic is irrelevant. This is not a bug as everything works just as designed and it will not be changed !!!