MySQL Bugs: #56514: Mixing utf8, group by, IF and Datefield cause a truncate problem (Only in views)

Bug #56514	Mixing utf8, group by, IF and Datefield cause a truncate problem (Only in views)
Submitted:	2 Sep 2010 18:00	Modified:	11 Feb 2018 12:57
Reporter:	Ian Lord	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	5.5.5 m3	OS:	FreeBSD (7.2)
Assigned to:		CPU Architecture:	Any

Description:
Probably related to Bug #54916

Combining set names utf8 with a group by and a IF on a datefield cause the date to be truncated.

This bug didnt exist in 5.5.4 (bug #54916 was reported by me in 5.5.4)

How to repeat:
mysql> create database test;
Query OK, 1 row affected (0.00 sec)

mysql> use test;
Database changed
mysql> CREATE TABLE `tTest` (
    ->   `ID` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
    ->   `Date` date NOT NULL DEFAULT '0000-00-00',
    ->   PRIMARY KEY (`ID`)
    -> ) ENGINE=InnoDB  DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO tTest (`Date`) VALUES ('2010-12-31');
Query OK, 1 row affected (0.01 sec)

mysql> create SQL SECURITY INVOKER VIEW vTest AS SELECT IF(Date = '0000-00-00', '', Date) AS Date FROM tTest group by ID;
Query OK, 0 rows affected (0.00 sec)

~~~
mysql> SELECT IF(Date = '0000-00-00', '', Date) AS Date FROM tTest group by ID; ###This works fine
+------------+
| Date       |
+------------+
| 2010-12-31 |
+------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM vTest; ###This works fine
+------------+
| Date       |
+------------+
| 2010-12-31 |
+------------+
1 row in set (0.00 sec)

~~~

mysql> set names utf8;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM vTest; ###This works fine
+------------+
| Date       |
+------------+
| 2010-12-31 |
+------------+
1 row in set (0.00 sec)

~~~
###dropping the view and recreating it with set names utf8 
mysql> drop view vTest;
Query OK, 0 rows affected (0.00 sec)

mysql> create SQL SECURITY INVOKER VIEW vTest AS SELECT IF(Date = '0000-00-00', '', Date) AS Date FROM tTest group by ID;
Query OK, 0 rows affected (0.00 sec)

~~~
###Now we can see the bug only with the view
mysql> SELECT IF(Date = '0000-00-00', '', Date) AS Date FROM tTest group by ID; ###This works fine
+------------+
| Date       |
+------------+
| 2010-12-31 |
+------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM vTest; ###OOPS... truncated
+------+
| Date |
+------+
| 201  |
+------+
1 row in set (0.00 sec)

Suggested fix:
Temporarely until the bug is fixed, user can cast the date as CHAR like this:

mysql> drop view vTest;
Query OK, 0 rows affected (0.00 sec)

mysql> create SQL SECURITY INVOKER VIEW vTest AS SELECT IF(Date = '0000-00-00', '', CAST(Date AS CHAR))  AS Date FROM tTest group by ID;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from vTest;
+------------+
| Date       |
+------------+
| 2010-12-31 |
+------------+
1 row in set (0.00 sec)

Can not repeat with current mysql-5.5 from bzr (where that other bug you mentioned is already fixed it seems):

macbook-pro:5.1 openxs$ bin/mysql -uroot test
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 5
Server version: 5.5.7-m3-debug Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE TABLE `tTest` (
    ->   `ID` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
    ->   `Date` date NOT NULL DEFAULT '0000-00-00',
    ->   PRIMARY KEY (`ID`)
    -> ) ENGINE=InnoDB  DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.10 sec)

mysql> INSERT INTO tTest (`Date`) VALUES ('2010-12-31');
Query OK, 1 row affected (0.00 sec)

mysql> create SQL SECURITY INVOKER VIEW vTest AS SELECT IF(Date = '0000-00-00', '', Date)
    -> AS Date FROM tTest group by ID;
Query OK, 0 rows affected (0.26 sec)

mysql> SELECT IF(Date = '0000-00-00', '', Date) AS Date FROM tTest group by ID;
+------------+
| Date       |
+------------+
| 2010-12-31 |
+------------+
1 row in set (0.04 sec)

mysql> SELECT * FROM vTest;
+------------+
| Date       |
+------------+
| 2010-12-31 |
+------------+
1 row in set (0.00 sec)

mysql> 
mysql> set names utf8;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM vTest;
+------------+
| Date       |
+------------+
| 2010-12-31 |
+------------+
1 row in set (0.01 sec)

mysql> drop view vTest;
Query OK, 0 rows affected (0.00 sec)

mysql> create SQL SECURITY INVOKER VIEW vTest AS SELECT IF(Date = '0000-00-00', '', Date)
    -> AS Date FROM tTest group by ID;
Query OK, 0 rows affected (0.13 sec)

mysql> SELECT IF(Date = '0000-00-00', '', Date) AS Date FROM tTest group by ID; +------------+
| Date       |
+------------+
| 2010-12-31 |
+------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM vTest;
+------------+
| Date       |
+------------+
| 2010-12-31 |
+------------+
1 row in set (0.00 sec)

Am I missing anything?

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".

Posted by developer:
 
Not reproducible on 5.6.40.