Bug #65137 Why is a column considered numeric....
Submitted: 27 Apr 2012 18:26 Modified: 20 Sep 2014 10:01
Reporter: Luuk V Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Command-line Clients Severity:S3 (Non-critical)
Version:5.5.16 OS:Any
Assigned to: CPU Architecture:Any
Triage: Needs Triage: D3 (Medium)

[27 Apr 2012 18:26] Luuk V
Description:
Why is a column considered numeric, and therfore be right aligned when not showing column names ('-N')

How to repeat:
~> mysql -Ne "select 'YY' + 3 as 'X'  UNION select '1E3' UNION select '1E3' + 4 UNION select 'X';"
+------+
|    3 |
|  1E3 |
| 1004 |
|    X |
+------+
~> mysql -e "select 'YY' + 3 as 'X'  UNION select '1E3' UNION select '1E3' + 4 UNION select 'X';"
+------+
| X    |
+------+
| 3    |
| 1E3  |
| 1004 |
| X    |
+------+
[27 Apr 2012 18:53] Valeriy Kravchuk
I'd say this is a bug. Verified on Mac OS X:

macbook-pro:5.5 openxs$ bin/mysql -uroot -Ne "select 'YY' + 3 as 'X'  UNION select '1E3' UNION select '1E3' + 4 UNION select 'X';"
+------+
|    3 |
|  1E3 |
| 1004 |
|    X |
+------+
macbook-pro:5.5 openxs$ bin/mysql -uroot -e "select 'YY' + 3 as 'X'  UNION select '1E3' UNION select '1E3' + 4 UNION select 'X';"
+------+
| X    |
+------+
| 3    |
| 1E3  |
| 1004 |
| X    |
+------+
[27 Apr 2012 18:54] Luuk V
~> mysql -e "select 'XX' UNION select 'XXXXXXXX'"
+----------+
| XX       |
+----------+
| XX       |
| XXXXXXXX |
+----------+
~> mysql -Ne "select 'XX' UNION select 'XXXXXXXX'"
+----------+
| XX       |
| XXXXXXXX |
+----------+
~> mysql -Ne "select 1,'XX' UNION select 2,'XXXXXXXX'"
+---+----------+
| 1 |       XX |
| 2 | XXXXXXXX |
+---+----------+

The last result is unexpected. The 'XX' should be left-aligned.
[20 Sep 2014 9:58] Luuk V
Behaviour changed since i posted this bug (not making it better): 

~> mysql -e "select 'XX' UNION select 'XXXXXXXX'"
+----------+
| XX       |
+----------+
| XX       |
| XXXXXXXX |
+----------+
~> mysql -Ne "select 'XX' UNION select 'XXXXXXXX'"
+----------+
|       XX |
| XXXXXXXX |
+----------+
~> mysql -Ne "select 1,'XX' UNION select 2,'XXXXXXXX'"
+---+----------+
| 1 |       XX |
| 2 | XXXXXXXX |
+---+----------+
~> mysql --version
mysql  Ver 14.14 Distrib 5.5.33, for Linux (i686) using readline 6.2
~>
[20 Sep 2014 10:01] Luuk V
Original problem is still unchanged (for the last 2.4 years...):

~> mysql -Ne "select 'YY' + 3 as 'X'  UNION select '1E3' UNION select '1E3' + 4 UNION select 'X';"
+------+
|    3 |
|  1E3 |
| 1004 |
|    X |
+------+
~> mysql -e "select 'YY' + 3 as 'X'  UNION select '1E3' UNION select '1E3' + 4 UNION select 'X';"
+------+
| X    |
+------+
| 3    |
| 1E3  |
| 1004 |
| X    |
+------+
~> mysql --version
mysql  Ver 14.14 Distrib 5.5.33, for Linux (i686) using readline 6.2
~>
[20 Sep 2014 10:20] Shane Bester
the bug is clear when looking at print_table_data function in mysql.cc

if (column_names)
{
  for (uint off=0; (field = mysql_fetch_field(result)) ; off++)
  {
    ...
    num_flag[off]= IS_NUM(field->type);
  }
}

imho the if (column_names) needs to be inside the for loop.
[20 Sep 2014 10:27] Shane Bester
and alas, by code inspection we see that num_flag array is used without initialization even.  So, results might depend on moon phase, OS, build, memory:

Conditional jump or move depends on uninitialised value(s)
at 0x4156E3: print_table_data(st_mysql_res*) (mysql.cc:3908)
by 0x41654D: com_go(String*, char*) (mysql.cc:3592)
by 0x4177ED: add_line(String&, char*, unsigned long, char*, bool*, bool) [clone .constprop.38] (mysql.cc:2587)
by 0x40D5C3: main (mysql.cc:2286)