MySQL Bugs: #15471: Occasional formatting error

Bug #15471	Occasional formatting error
Submitted:	4 Dec 2005 20:17	Modified:	30 Jun 2006 20:13
Reporter:	Brian Wichmann	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.17-BK, 5.0.16-standard	OS:	Linux (Linux, OS X)
Assigned to:	Chad MILLER	CPU Architecture:	Any

Description:
At http://stv.sourceforge.net/ there is a BD using MySQL.

When loaded onto OSX (10.4, but i don't think that matters), some queries are formatted incorrectly, loosing information as well as spacing-type issues.

How to repeat:
At http://stv.sourceforge.net/ there is a BD using MySQL.

The query using the DB which does:

SELECT X.Rule,  X.Program
FROM Result X
GROUP BY X.Rule;

produces:
+--------------+-----------------------+
| Rule         | Program               |
+--------------+-----------------------+
    |TV        | pSTV: version 0.9
    |da        | pSTV: version 0.9
    |klin      | pSTV: version 0.9
    |bridgeSTV | pSTV: version 0.9
| CofESTV      | eSTV Reg. 12180,1.48f |
    |dorcet    | pSTV: version 0.9
    |mbs       | pSTV: version 0.9
| ERS76STV     | eSTV Reg. 12180,1.47a |
    |97STV     | pSTV: version 0.9
    |          | pSTV: version 0.9
    |kSTV      | pSTV: version 0.9
    |elandSTV  | pSTV: version 0.9
| QPQe         | Ada rule v0.04        |
| QPQr         | Ada rule v0.04        |
    |V         | pSTV: version 0.9
    |pVote     | pSTV: version 0.9
    |renSTV    | pSTV: version 0.9
| XXvote       | Ada rule v0.04        |
+--------------+-----------------------+
18 rows in set (0.06 sec)

Suggested fix:
For the manual, I just formatted the result by hand!!

See guide at http://stv.sourceforge.net/

(Could not find mysqlbug script on my system.)

Database dump downloaded from sourceforge.net

Attachment: stvdb-1.2.sql.zip (application/x-zip-compressed, text), 109.18 KiB.

Thank you for a problem report. I've got the same strange formatting of the results on 5.0.17-BK (ChangeSet@1.1973, 2005-12-03 20:52:34+01:00) on Linux, on the database dump I've downloaded from your URL (see file attached):

mysql> SELECT X.Rule,  X.Program
    -> FFROM Result X
    -> GROUP BY X.Rule;
+------------+-----------------------+
| Rule       | Program               |
+------------+-----------------------+
    |TV      | pSTV: version 0.9
    |da      | pSTV: version 0.9
    |klin    | pSTV: version 0.9
    |bridgeS | pSTV: version 0.9
| CofE       | eSTV Reg. 12180,1.48f |
    |dorcet  | pSTV: version 0.9
    |mbs     | pSTV: version 0.9
| ERS76STV   | eSTV Reg. 12180,1.47a |
    |97STV   | pSTV: version 0.9
    |        | pSTV: version 0.9
    |kSTV    | pSTV: version 0.9
    |elandST | pSTV: version 0.9
| QPQe       | Ada rule v0.04        |
| QPQr       | Ada rule v0.04        |
    |V       | pSTV: version 0.9
    |pVote   | pSTV: version 0.9
    |renSTV  | pSTV: version 0.9
| XXvote     | Ada rule v0.04        |
+------------+-----------------------+
18 rows in set (0,19 sec)

mysql> desc Result;
+-----------+--------------+------+-----+---------+-------+
| Field     | Type         | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| ID        | varchar(4)   | YES  |     | NULL    |       |
| Rule      | varchar(10)  | YES  |     | NULL    |       |
| Elected   | varchar(220) | YES  |     | NULL    |       |
| Stages    | int(11)      | YES  |     | NULL    |       |
| Random    | int(11)      | YES  |     | NULL    |       |
| First     | int(11)      | YES  |     | NULL    |       |
| Tie_Break | char(1)      | YES  |     | NULL    |       |
| Program   | varchar(31)  | YES  |     | NULL    |       |
+-----------+--------------+------+-----+---------+-------+
8 rows in set (0,00 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.0.17    |
+-----------+
1 row in set (0,01 sec)

This looks like correct behavior.  Most INSERTion statements contain a backslash-r, which becomes a carriage return upon being parsed.  When sent to the terminal, it's sent literally, without re-encoding into a SQL-safe, visible representation of that single control character. 

If you disagree, please reopen this bug.

I disagree that this is correct behaviour. There is an obvious formatting error in the display of the result of a query when using command line and output to a terminal. Perhaps the category should not be MySQL Server but something else?

Brian and Valeriy, if you think this is incorrect, how would you expect a string with a carriage return to be printed?  Shouldn't we give back data in the same way you gave it to us?

Consider
  SELECT DISTINCT Program FROM Result;
  SELECT DISTINCT REPLACE(Program, '\r', '[CARRIAGE RETURN]') FROM Result;
  SELECT DISTINCT REPLACE(Program, '\r', '>>\r<<') FROM Result;
  SELECT DISTINCT TRIM(TRAILING '\r' FROM Program) FROM Result;
  SELECT 'abcdefghijklmnop!\r1234567890!\rABC!\t\t\t***';
  SELECT 'abcdefghijklmnop!\n1234567890!\nABC!';

I apologise. My data was not supposed to have any carriage returns in it. It did, which caused the curious formatting problem!

Brian.