MySQL Bugs: #91082: warning 1292

Bug #91082	warning 1292
Submitted:	30 May 2018 23:32	Modified:	1 Jun 2018 13:23
Reporter:	Don Cohen	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.5.38-log	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
select id, if(id<'b',null,'4.33') x from behavior1 having x is not null \
limit 3; 
+----+------+ 
| id | x    | 
+----+------+ 
| LA | 4.33 | 
| LB | 4.33 | 
| LC | 4.33 | 
+----+------+ 
3 rows in set, 3 warnings (0.00 sec) 
 
MySQL [dropbox2002_201804]> show warnings; 
+---------+------+-------------------------------------------+ 
| Level   | Code | Message                                   | 
+---------+------+-------------------------------------------+ 
| Warning | 1292 | Truncated incorrect INTEGER value: '4.33' | 
| Warning | 1292 | Truncated incorrect INTEGER value: '4.33' | 
| Warning | 1292 | Truncated incorrect INTEGER value: '4.33' | 
+---------+------+-------------------------------------------+ 
3 rows in set (0.00 sec) 

How to repeat:
above relies on having something in behavior1 table id column other than 'b'

There's no reason to expect the if expression to return integer, is there?
I don't get the warning if I change '4.33' to 4, but then I also don't get it
when I use 4.33
I also don't get it when I change is not null to is null, even though the same
tests have to be done.
So I think this is just a bug.
And I can't figure out what internal errors might be causing it.

I did see many previous complaints about this warning, but none with a plausible
explanation that applies to this case.

Hi,

Thank you for your report, but it is not a bug.

As our Reference Manual clearly explains, integer is a default number type, unless specified differently.

You can either use CAST() or even better set first your `x` to be a user variable assigned to some floating point value, like 0.00.

Not a bug.

First, what does "a default number type" have to do with anything?
Where did I specify any number at all?  Does if(id<'b','a','b') have a default type that's a number?
Second, where is this clear explanation in the manual?
Third, I don't understand your suggestion of changing x to a user variable.
It's supposed to be the name of a column, right?

Hi,

First of all, `x` is not a column !!! It is an alias !!! 

Second, without specifying types with means at your disposal, which I described in my previous comment, MySQL server is trying to do its best.

Third , you did not specify 'a' or 'b', but '4.33', which defaults to a number.

Fourth, you are supposed to read the entire manual, except for parts that you do not use at all, and then ask the questions.

Not a bug.

I'd say x is an alias for a column of the output.
Please note that the same thing happens if I use 'b' instead of '4.33'.
Does if(a<'b",'a','b') also default to a number even though it can NEVER be a number or even interpreted as one?  Why would that be?
Your statement that I am supposed to read the entire manual is clearly not reasonable.  I bet you won't be able to find a single user who has ever read the entire manual.  I search the manual regularly, including before I send bug reports.  I'm still looking for both the clear statement that explains the type assignment to expressions and also the way I can override it.

Hi,

Actually, you were right.

However, I can not repeat this behaviour any more on 5.7.

Version 5.5 is in the maintenance mode only, so only hard bugs will be fixed in it and this is not one of those.

These are my results from my table where used column is of the VARCHAR type:

mysql> select name, if(name < 'b', 'a', 'b') x  from test12";

+------+---+
| name | x |
+------+---+
| Tom1 | b |
| Tom2 | b |
| Tom3 | b |
+------+---+

mysql> show warnings;
Empty set (0.00 sec)

mysql> select name, if(name < 'b', null, '4.33') as x  from test12 having x is not null;

+------+------+
| name | x    |
+------+------+
| Tom1 | 4.33 |
| Tom2 | 4.33 |
| Tom3 | 4.33 |
+------+------+

mysql> show warnings;
Empty set (0.00 sec)

Not a bug.

ok, I accept the verdict that it's not a bug.
And that I should always be upgrading to the most recent version (last time I did that resulted in some unexpected problems though).

I still can't find where the manual tells me what type to expect to be assigned to an if expression, or any other expression for that matter.  I'm assuming that a column of a table is assigned the type declared for the column, but after that I'm at a loss.
Also I'm still looking for some explanation of "set first your `x` to be a user variable".
So far I see that the warning disappears if I use cast(IF(...) as CHAR).
Is that what you're recommending for every expression?
BTW, I'm still looking for an explanation of why 'a'='a ' returns 1 and for that matter some other comparison operation for which this is not true (and for that matter how to do case sensitive string comparison).
I realize these are getting off the original subject, but would still appreciate answers.  Maybe the answers are all in the manual but I just don't understand how to search for them.  I wish I did.  I've been trying to use the search box but still not finding answers to these questions.
Let me know if I ought be be directing these questions somewhere else.

Hi,

You can use local variables in your code, but this is not the way you should go for aliases. Better just use CAST() for the values or expressions or columns in the 2nd and 3rd argument of the IF() function.

Regarding expressions, look in the manual on the CAST() function. 
On the same subject, read chapters 9 and 10. Read chapter 11 for data types that you need. Some sub-chapters of the chapter 12 are also dealing with these issues. That should be it.

Regarding discussing problems, there are MySQL user groups on Facebook and Twitter.