MySQL Bugs: #28620: mysql client cannot display the values of the first column in result set.

Bug #28620	mysql client cannot display the values of the first column in result set.
Submitted:	23 May 2007 14:12	Modified:	16 Jul 2007 9:12
Reporter:	William ZHANG	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Command-line Clients	Severity:	S2 (Serious)
Version:	5.0.41-community-nt	OS:	Windows (Windows XP SP2)
Assigned to:	Ramil Kalimullin	CPU Architecture:	Any
Tags:	5.0.41

Description:
I happened to encounter this bug when doing the experiments for "Database Tuning". You can download the SQL statements and data files here: http://www.distlab.dk/dbtune/?sec=experiments.

How to repeat:
drop table if exists ROOM;

create table ROOM ( id int auto_increment, number int, max_guests int, hotel_id int, room_type varchar(10), PRIMARY KEY (id), INDEX (hotel_id)) type = InnoDB;

LOAD DATA LOCAL INFILE 'C:/Tuning/data/HotelData/roomdata' REPLACE INTO TABLE ROOM FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n' IGNORE 1 LINES (hotel_id, number, max_guests, room_type);

mysql> select @@version
    -> \g
+---------------------+
| @@version           |
+---------------------+
| 5.0.41-community-nt |
+---------------------+
1 row in set (0.00 sec)

mysql>

!!! The values of the first column are missing!

mysql> select * from room limit 2
    -> \g
+----+--------+------------+----------+-----------+
| id | number | max_guests | hotel_id | room_type |
+----+--------+------------+----------+-----------+
    |     958 |          1 |      115 | type4
    |     444 |          2 |      159 | type1
+----+--------+------------+----------+-----------+
2 rows in set (0.00 sec)

!!! Now the values are correct!

mysql> select * from room limit 2
    -> \G
*************************** 1. row ***************************
        id: 1
    number: 958
max_guests: 1
  hotel_id: 115
 room_type: type4
*************************** 2. row ***************************
        id: 2
    number: 444
max_guests: 2
  hotel_id: 159
 room_type: type1
2 rows in set (0.00 sec)

Suggested fix:
Fix it ASAP.

Thank you for the bug report. Could you please attach here the file
roomdata which I wasn't able to find where you pointed to get it.
Thanks in advance.

You can find the link on page: http://www.distlab.dk/dbtune/?sec=experiments. 
And you can download the data file here: It is
http://www.distlab.dk/dbtune/scripts/HotelData.zip.

Thank you for the bug report.

Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.41-community-nt MySQL Community Edition (GPL)

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

mysql> create database db4;
Query OK, 1 row affected (0.12 sec)

mysql> use db4
Database changed
mysql> create table ROOM ( id int auto_increment, number int, max_guests int, hotel_id
    -> int, room_type varchar(10), PRIMARY KEY (id), INDEX (hotel_id)) type = InnoDB;
Query OK, 0 rows affected, 1 warning (0.60 sec)

mysql> LOAD DATA LOCAL INFILE 'C:/files/roomdata' REPLACE INTO TABLE
    -> ROOM FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n' IGNORE 1 LINES (hotel_id,
    -> number, max_guests, room_type);
Query OK, 315459 rows affected (12.12 sec)
Records: 315459  Deleted: 0  Skipped: 0  Warnings: 0

mysql> select * from room limit 2
    -> \g
+----+--------+------------+----------+-----------+
| id | number | max_guests | hotel_id | room_type |
+----+--------+------------+----------+-----------+
    |     958 |          1 |      115 | type4
    |     444 |          2 |      159 | type1
+----+--------+------------+----------+-----------+
2 rows in set (0.05 sec)

mysql> select * from room limit 2
    -> \G
*************************** 1. row ***************************
        id: 1
    number: 958
max_guests: 1
  hotel_id: 115
 room_type: type4
*************************** 2. row ***************************
        id: 2
    number: 444
max_guests: 2
  hotel_id: 159
 room_type: type1
2 rows in set (0.00 sec)

mysql> select id from room limit 2\g
+----+
| id |
+----+
|  1 |
|  2 |
+----+
2 rows in set (0.00 sec)

mysql> select id, room_type from room limit 2\g
+----+-----------+
| id | room_type |
+----+-----------+
    |  type4
    |  type1
+----+-----------+
2 rows in set (0.00 sec)

Hi William!

The last character of the last field "room_type" in your "select *"
is '\r' (carriage return, CR), so the client moves the cursor to 
the first position on the same line then continues printing.
As a result we have overprinted "id" values.

That's an intentional behavior that mysql command-line client had always.
I'm closing the issue as 'not a bug'.