| Bug #60435 | return values truncated | ||
|---|---|---|---|
| Submitted: | 11 Mar 2011 21:38 | Modified: | 11 Feb 2018 13:20 |
| Reporter: | Jon Holford | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server: DML | Severity: | S1 (Critical) |
| Version: | 5.5.9, 5.5.8, 5.1.54 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | regression | ||
[11 Mar 2011 21:38]
Jon Holford
[12 Mar 2011 1:54]
Jon Holford
MySQL supports an extension for optionally specifying the display width of integer data types in parentheses following the base keyword for the type. For example, INT(4) specifies an INT with a display width of four digits. http://dev.mysql.com/doc/refman/4.1/en/numeric-types.html
[12 Mar 2011 8:38]
Valeriy Kravchuk
With 5.5.8 the result was even worse (note also non-existent id=22):
C:\Program Files\MySQL\MySQL Server 5.5\bin>mysql -uroot -proot -P3312 test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 5.5.8 MySQL Community Server (GPL)
Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> CREATE TABLE `A` (
-> `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
-> `name` varchar(80) NOT NULL,
-> `method` tinyint(4) NOT NULL,
-> PRIMARY KEY (`id`)
-> ) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=latin1
-> ;
Query OK, 0 rows affected (0.75 sec)
mysql> CREATE TABLE `B` (
-> `id` mediumint(8) unsigned NOT NULL,
-> `time` int(4) NOT NULL,
-> KEY `myKey` (`time`)
-> ) ENGINE=InnoDB DEFAULT CHARSET=latin1
-> ;
Query OK, 0 rows affected (0.13 sec)
mysql> insert into `A` (
-> `id` ,
-> `name` ,
-> `method`
-> )
-> VALUES (
-> NULL , 'AAA', '0'
-> ), (
-> NULL , 'ZZZ', '0'
-> );
Query OK, 2 rows affected (0.16 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> insert into `B` (
-> `id` ,
-> `time`
-> )
-> VALUES (
-> '21', '1199870000'
-> ), (
-> '31', '1199870083'
-> );
Query OK, 2 rows affected (0.05 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> insert into `B` (
-> `id` ,
-> `time`
-> )
-> VALUES (
-> '21', '1299870000'
-> ), (
-> '31', '1299870083'
-> );
Query OK, 2 rows affected (0.03 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from `B`;
+----+------------+
| id | time |
+----+------------+
| 21 | 1199870000 |
| 31 | 1199870083 |
| 21 | 1299870000 |
| 31 | 1299870083 |
+----+------------+
4 rows in set (0.05 sec)
mysql> SELECT A.id, if(`time` IS NULL,'',`time`) thelastrun,`name` FROM A LEFT J
OIN B ON
-> A.id=B.id AND B.`time`=(SELECT MAX(`time`) FROM B WHERE id=A.id) WHERE me
thod='0' GROUP
-> BY A.id ORDER BY `name`;
+----+------------+------+
| id | thelastrun | name |
+----+------------+------+
| 21 | 1299 | AAA |
| 22 | | ZZZ |
+----+------------+------+
2 rows in set (0.09 sec)
mysql> explain SELECT A.id, if(`time` IS NULL,'',`time`) thelastrun,`name` FROM
A LEFT JOIN B ON
-> A.id=B.id AND B.`time`=(SELECT MAX(`time`) FROM B WHERE id=A.id) WHERE me
thod='0' GROUP
-> BY A.id ORDER BY `name`;
+----+--------------------+-------+------+---------------+------+---------+-----
-+------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref
| rows | Extra |
+----+--------------------+-------+------+---------------+------+---------+-----
-+------+----------------------------------------------+
| 1 | PRIMARY | A | ALL | NULL | NULL | NULL | NULL
| 2 | Using where; Using temporary; Using filesort |
| 1 | PRIMARY | B | ALL | myKey | NULL | NULL | NULL
| 4 | |
| 2 | DEPENDENT SUBQUERY | B | ALL | NULL | NULL | NULL | NULL
| 4 | Using where |
+----+--------------------+-------+------+---------------+------+---------+-----
-+------+----------------------------------------------+
3 rows in set (0.02 sec)
[30 Mar 2011 3:28]
Valeriy Kravchuk
Truncation does NOT happen with 5.1.48: mysql> insert into `A` ( `id` , `name` , `method` ) VALUES ( NULL , 'AAA', '0' ), ( NULL , 'ZZZ', '0' );Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> insert into `B` ( `id` , `time` ) VALUES ( '21', '1199870000' ), ( '31', '1199870083' );Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> insert into `B` ( `id` , `time` ) VALUES ( '21', '1299870000' ), ( '31', '1299870083' );Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> SELECT A.id, if(`time` IS NULL,'',`time`) thelastrun,`name` FROM A LEFT JOIN B ON A.id=B.id AND B.`time`=(SELECT MAX(`time`) FROM B WHERE id=A.id) WHERE method='0' GROUP BY A.id ORDER BY `name`; +----+------------+------+ | id | thelastrun | name | +----+------------+------+ | 21 | 1299870000 | AAA | | 22 | | ZZZ | +----+------------+------+ 2 rows in set (0.00 sec) mysql> SELECT A.id, if(`time` IS NULL,'',`time`) thelastrun,`name` FROM A LEFT JOIN B ON A.id=B.id AND B.`time`=(SELECT MAX(`time`) FROM B WHERE id=A.id) WHERE method='0' GROUP BY A.id; +----+------------+------+ | id | thelastrun | name | +----+------------+------+ | 21 | 1299870000 | AAA | | 22 | | ZZZ | +----+------------+------+ 2 rows in set (0.00 sec) mysql> select version(); +-----------+ | version() | +-----------+ | 5.1.48 | +-----------+ 1 row in set (0.00 sec) mysql> select * from a; +----+------+--------+ | id | name | method | +----+------+--------+ | 21 | AAA | 0 | | 22 | ZZZ | 0 | +----+------+--------+ 2 rows in set (0.00 sec) so this looks like a recent enough regression.
[11 Feb 2018 13:20]
Roy Lyseng
Posted by developer: Closing as not a bug. Query requires ONLY_FULL_GROUP_BY and non-deterministic results must be expected.
