MySQL Bugs: #33611: Warning: "Truncated incorrect INTEGER value" on querying view

Bug #33611	Warning: "Truncated incorrect INTEGER value" on querying view
Submitted:	1 Jan 2008 19:18	Modified:	9 Sep 2008 6:21
Reporter:	[ name withheld ]	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: Views	Severity:	S3 (Non-critical)
Version:	5.0.37, 5.0.54	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	INTEGER, merge, stored function, truncated, VIEW

Description:
I am using views with merge algorithm to transparently filter tables, like this:
create function get_filter () returns char(10) charset utf8 return @filter;
create algorithm=merge view `v_table` AS SELECT * FROM `table` WHERE filterfield=get_filter();

When joining two views, i got warning: "Truncated incorrect INTEGER value: filter", thou functionality seems to be correct.

How to repeat:
Using this schema and default data:
http://tequila.sda.bme.hu/~rsc/x.sql.gz

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

mysql> SELECT t.msg_id FROM phpbb_v_privmsgs_to t, phpbb_v_privmsgs p WHERE t.user_id = 1321 AND t.msg_id = p.msg_id ;
+--------+
| msg_id |
+--------+
|  20371 |
+--------+
1 row in set, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+---------------------------------------------------------------------+
| Level   | Code | Message                                                             |
+---------+------+---------------------------------------------------------------------+
| Warning | 1292 | Truncated incorrect INTEGER value: 'forum3                        ' |
+---------+------+---------------------------------------------------------------------+
1 row in set (0.00 sec)

Thank you for a problem report. Please, try to repeat with a newer version, 5.0.45 at least, and inform about the results. 

This is what I've got:

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

mysql> SELECT t.msg_id FROM phpbb_v_privmsgs_to t, phpbb_v_privmsgs p WHERE t.us
er_id =
    -> 1321 AND t.msg_id = p.msg_id ;
Empty set (0.00 sec)

mysql> explain SELECT t.msg_id FROM phpbb_v_privmsgs_to t, phpbb_v_privmsgs p WH
ERE t.user_id = 1321 AND t.msg_id = p.msg_id ;
+----+-------------+-----------------+------+---------------+---------+---------
+-------------------+------+--------------------------+
| id | select_type | table           | type | possible_keys | key     | key_len
| ref               | rows | Extra                    |
+----+-------------+-----------------+------+---------------+---------+---------
+-------------------+------+--------------------------+
|  1 | SIMPLE      | t               | ALL  | NULL          | NULL    | NULL
| NULL              |    1 | Using where              |
|  1 | SIMPLE      | phpbb3_privmsgs | ref  | PRIMARY       | PRIMARY | 3
| bug33611.t.msg_id |  129 | Using where; Using index |
+----+-------------+-----------------+------+---------------+---------+---------
+-------------------+------+--------------------------+
2 rows in set (0.38 sec)

mysql> select version();
+--------------------------+
| version()                |
+--------------------------+
| 5.0.54-enterprise-gpl-nt |
+--------------------------+
1 row in set (0.02 sec)

No warnings, and different result on your schema.

Thank you for the quick reply. 

See the following queries on my 5.0.37:
-- I query the original tables and filter them "by hand":
mysql> SELECT t.msg_id FROM phpbb3_privmsgs_to t, phpbb3_privmsgs p WHERE t.user_id = 1321 and t.msg_id = p.msg_id and p.forum="forum3" and t.forum="forum3";
+--------+
| msg_id |
+--------+
|  20371 |
+--------+
1 row in set (0.00 sec)

-- Now I want MySQL to do the same using those merge views:
mysql> set @filter = "forum3";
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT t.msg_id FROM phpbb_v_privmsgs_to t, phpbb_v_privmsgs p WHERE t.user_id = 1321 and t.msg_id = p.msg_id ;
+--------+
| msg_id |
+--------+
|  20371 |
+--------+
1 row in set, 1 warning (0.00 sec)

-- And we got the bug.
-- Then I did the same with 5.0.45:

mysql> SELECT t.msg_id FROM phpbb3_privmsgs_to t, phpbb3_privmsgs p WHERE t.user_id = 1321 and t.msg_id = p.msg_id and p.forum="forum3" and t.forum="forum3";
+--------+
| msg_id |
+--------+
|  20371 |
+--------+
1 row in set (0.01 sec)

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

mysql> SELECT t.msg_id FROM phpbb_v_privmsgs_to t, phpbb_v_privmsgs p WHERE t.user_id = 1321 and t.msg_id = p.msg_id ;
+--------+
| msg_id |
+--------+
|  20371 |
+--------+
1 row in set (0.77 sec)

As you can see, 5.0.45 works perfectly, no stupid warnings, and correct result.
Your 5.0.54 behaves differently as well, since you didnt get that hit with 20371 as msg_id.
It would be nice to see MySQL's "merged query" (as it merged the views expressions) I think these 3 versions do the same thing somehow differently because of using multiple views and joining.

Please, use EXPLAIN EXTENDED as follows:

mysql> explain extended SELECT t.msg_id FROM phpbb_v_privmsgs_to t, phpbb_v_priv
msgs p WHERE t.user_id =
    -> 1321 and t.msg_id = p.msg_id ;
+----+-------------+-----------------+------+---------------+---------+---------
+-------------------+------+--------------------------+
| id | select_type | table           | type | possible_keys | key     | key_len
| ref               | rows | Extra                    |
+----+-------------+-----------------+------+---------------+---------+---------
+-------------------+------+--------------------------+
|  1 | SIMPLE      | t               | ALL  | NULL          | NULL    | NULL
| NULL              |    1 | Using where              |
|  1 | SIMPLE      | phpbb3_privmsgs | ref  | PRIMARY       | PRIMARY | 3
| bug33611.t.msg_id |  129 | Using where; Using index |
+----+-------------+-----------------+------+---------------+---------+---------
+-------------------+------+--------------------------+
2 rows in set, 1 warning (0.00 sec)

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: select `bug33611`.`t`.`msg_id` AS `msg_id` from `bug33611`.`phpbb_v_pri
vmsgs_to` `t` join `bug33611`.`phpbb3_privmsgs` where ((`bug33611`.`phpbb3_privm
sgs`.`msg_id` = `bug33611`.`t`.`msg_id`) and (`bug33611`.`t`.`user_id` = 1321) a
nd (`bug33611`.`phpbb3_privmsgs`.`forum` = `get_filter`()))
1 row in set (0.00 sec)

mysql> select get_filter();
+--------------+
| get_filter() |
+--------------+
| forum3       |
+--------------+
1 row in set (0.00 sec)

The above is what I've got on 5.0.54. Please, check if 5.0.45 and 5.0.37 gives anything different.

As I can see, 5.0.54 rewrites the query using JOIN, which is simple not "compatible" with the original one, so there are no hits. 5.0.37 and 5.0.45 mapped the expression to the same query, but explain shows that 5.0.37 is using eq_ref instead of ref on phpbb3_privmsgs.

The complete output:

mysql> select version();
+------------+
| version()  |
+------------+
| 5.0.37-log |
+------------+
1 row in set (0.00 sec)

mysql> explain extended SELECT t.msg_id FROM phpbb_v_privmsgs_to t, phpbb_v_privmsgs p WHERE t.user_id = 1321 and t.msg_id = p.msg_id ;
+----+-------------+--------------------+--------+---------------------+--------------+---------+-----------------------------------+------+--------------------------+
| id | select_type | table              | type   | possible_keys       | key          | key_len | ref                               | rows | Extra                    |
+----+-------------+--------------------+--------+---------------------+--------------+---------+-----------------------------------+------+--------------------------+
|  1 | SIMPLE      | phpbb3_privmsgs_to | ref    | msg_id,usr_flder_id | usr_flder_id | 3       | const                             |    1 | Using where              |
|  1 | SIMPLE      | phpbb3_privmsgs    | eq_ref | PRIMARY             | PRIMARY      | 33      | x.phpbb3_privmsgs_to.msg_id,const |    1 | Using where; Using index |
+----+-------------+--------------------+--------+---------------------+--------------+---------+-----------------------------------+------+--------------------------+
2 rows in set, 2 warnings (0.14 sec)

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Warning
   Code: 1292
Message: Truncated incorrect INTEGER value: 'forum3                        '
*************************** 2. row ***************************
  Level: Note
   Code: 1003
Message: select `x`.`phpbb3_privmsgs_to`.`msg_id` AS `msg_id` from `x`.`phpbb3_privmsgs_to` join `x`.`phpbb3_privmsgs` where ((`x`.`phpbb3_privmsgs`.`forum` = `x`.`get_filter`()) and (`x`.`phpbb3_privmsgs_to`.`forum` = `x`.`get_filter`()) and (`x`.`phpbb3_privmsgs`.`msg_id` = `x`.`phpbb3_privmsgs_to`.`msg_id`) and (`x`.`phpbb3_privmsgs_to`.`user_id` = 1321))
2 rows in set (0.00 sec)

mysql> select get_filter();
+--------------+
| get_filter() |
+--------------+
| forum3       |
+--------------+
1 row in set (0.00 sec)

mysql> select version();
+------------+
| version()  |
+------------+
| 5.0.45-log |
+------------+
1 row in set (0.14 sec)

mysql> explain extended SELECT t.msg_id FROM phpbb_v_privmsgs_to t, phpbb_v_privmsgs p WHERE t.user_id = 1321 and t.msg_id = p.msg_id ;
+----+-------------+--------------------+------+---------------------+--------------+---------+-----------------------------+------+--------------------------+
| id | select_type | table              | type | possible_keys       | key          | key_len | ref                         | rows | Extra                    |
+----+-------------+--------------------+------+---------------------+--------------+---------+-----------------------------+------+--------------------------+
|  1 | SIMPLE      | phpbb3_privmsgs_to | ref  | msg_id,usr_flder_id | usr_flder_id | 3       | const                       |    1 | Using where              |
|  1 | SIMPLE      | phpbb3_privmsgs    | ref  | PRIMARY             | PRIMARY      | 3       | x.phpbb3_privmsgs_to.msg_id |  129 | Using where; Using index |
+----+-------------+--------------------+------+---------------------+--------------+---------+-----------------------------+------+--------------------------+
2 rows in set, 1 warning (0.18 sec)

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: select `x`.`phpbb3_privmsgs_to`.`msg_id` AS `msg_id` from `x`.`phpbb3_privmsgs_to` join `x`.`phpbb3_privmsgs` where ((`x`.`phpbb3_privmsgs`.`forum` = `x`.`get_filter`()) and (`x`.`phpbb3_privmsgs_to`.`forum` = `x`.`get_filter`()) and (`x`.`phpbb3_privmsgs`.`msg_id` = `x`.`phpbb3_privmsgs_to`.`msg_id`) and (`x`.`phpbb3_privmsgs_to`.`user_id` = 1321))
1 row in set (0.00 sec)

mysql> select get_filter();
+--------------+
| get_filter() |
+--------------+
| forum3       |
+--------------+
1 row in set (0.00 sec)

I can not repeat the behaviour described with latest 5.0.56-BK on Linux:

openxs@linux:~/dbs/5.0> bin/mysql -uroot x
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.0.56-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create function get_filter () returns char(10) charset utf8 return @filter;
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye
openxs@linux:~/dbs/5.0> bin/mysql -uroot x < x.sql
openxs@linux:~/dbs/5.0> bin/mysql -uroot x
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.0.56-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> SELECT t.msg_id FROM phpbb3_privmsgs_to t, phpbb3_privmsgs p WHERE t.use
r_id = 1321
    -> and t.msg_id = p.msg_id and p.forum="forum3" and t.forum="forum3";
+--------+
| msg_id |
+--------+
|  20371 |
+--------+
1 row in set (0.01 sec)

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

mysql> select get_filter();
+--------------+
| get_filter() |
+--------------+
| forum3       |
+--------------+
1 row in set (0.00 sec)

mysql> SELECT t.msg_id FROM phpbb_v_privmsgs_to t, phpbb_v_privmsgs p WHERE t.u
ser_id =
    -> 1321 and t.msg_id = p.msg_id ;
+--------+
| msg_id |
+--------+
|  20371 |
+--------+
1 row in set (0.00 sec)

So, either the bug is already fixed (again?) in 5.0.56 or there is something that influnce result somehow, like sql_mode. Please, send the results of

select @@sql_mode;

from "problematic" 5.0.37 (if you have it) and 5.0.45.

sql_mode is empty on both 5.0.37 and 45.
Could you show me the extended explain and the warnings of 5.0.56-BK please?

Please, try to repeat with a newer Community version, 5.0.67, that is already available, and inform about the results.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".