MySQL Bugs: #69734: Left column truncatiopn returned from * but not for field ID

Bug #69734	Left column truncatiopn returned from * but not for field ID
Submitted:	12 Jul 2013 21:18	Modified:	15 Jul 2013 12:22
Reporter:	David Hart	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Information schema	Severity:	S3 (Non-critical)
Version:	5.5.32	OS:	Linux (Fedora 18)
Assigned to:		CPU Architecture:	Any

Description:
mysql> describe pub78;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| ein     | varchar(24) | NO   | PRI |         |       |
| name    | varchar(64) | YES  |     | NULL    |       |
| city    | varchar(32) | YES  |     | NULL    |       |
| state   | varchar(2)  | YES  |     | NULL    |       |
| country | varchar(16) | YES  |     | NULL    |       |
| type    | varchar(8)  | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
mysql> select * from pub78 where ein like '86098%' and city='gilbert';
+-----------+--------------------------------------------+---------+-------+---------------+------+
| ein       | name                                       | city    | state | country       | type |
+-----------+--------------------------------------------+---------+-------+---------------+------+
  |60981658 | Global Helping to Advance Women & Children | Gilbert | AZ    | United States | PC
+-----------+--------------------------------------------+---------+-------+---------------+------+

Note that EIN=60981658. However, same query limited to one field:

mysql> select ein from pub78 where ein like '86098%' and city='gilbert';
+-----------+
| ein       |
+-----------+
| 860981658 |
+-----------+

Now we know that the first return result dropped the leading 8 from ein.

Inexplicable. 

How to repeat:
Same types of queries

Thank you for the bug report. Could you please provide the dump file (create table/insert data). Thanks.

Given the missing | at the end of the full result row and the blank at the beginnging, with | only in the second column i'd suspect that this is as simple as the type value actually being 'PC\r' or similar, with the CR control character (the \r) moving the cursor back to the first column of the line and so overwriting the beginning of the output

The output of the following query should reveal whether this is really the case:

  select hex(type) from pub78 where ein like '86098%' and city='gilbert';

mysql> select hex(type) from pub78 where ein like '86098%' and
    -> city='gilbert';
+-----------+
| hex(type) |
+-----------+
| 50430D    |
+-----------+

"Could you please provide the dump file (create table/insert data). Thanks."

I am not sure what you want me to do. Perhaps you should know that this DB has over 6 million records. Works like a champ if I could just figure this out.

As expected: last character in the type string is Carriage Return (CR, ASCII 0x0D), the control character that moves the cursor back to the beginning of the line. So the " |" at the beginning of the line are actually the formatting characters for the end of the last cell on the line.

So not a bug, just interaction of non-escaped control characters in the data with the output device ....

Not bug.  Small testcase:

drop temporary table if exists t;
create temporary table t(a blob,b blob)engine=myisam;
insert into t set a=0x68656C6C6F0D, b='test';
select * from t;
select * from t\G