Bug #39101 client -i (--ignore-spaces) option does not seem to work
Submitted: 28 Aug 2008 15:41 Modified: 25 Jun 2009 2:22
Reporter: Clinton Jones Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Command-line Clients Severity:S3 (Non-critical)
Version:5.0.67 OS:MacOS (version 10.5.4)
Assigned to: Jim Winstead CPU Architecture:Any
Tags: command-line, IGNORE_SPACE, ignore-spaces, option

[28 Aug 2008 15:41] Clinton Jones
Description:
I wanted to enable the IGNORE_SPACE mode when using the command-line client, so I read the man page and found that the "-i" (--ignore-spaces) option does just that.  I tried using this option (both the abbreviated and the full versions), but it did not seem to work.

Running the following query:

select concat ('hey', ' you');

I expected to see:

+------------------------+
| concat ('hey', ' you') |
+------------------------+
| hey you                | 
+------------------------+
1 row in set (0.00 sec)

But instead I saw:
ERROR 1305 (42000): FUNCTION databasename.concat does not exist

I then ran the command:
set sql_mode='IGNORE_SPACE';
and then repeated the previous query, and got the desired results.

This makes it seem to me that the -i option does not work (at least for the Mac OS X client)

How to repeat:
Run the following command in a shell (I have my username and password defined in a .my.cnf file):

mysql -i -h <host_name> <database_name>

Then run this query from the mysql command line client:
select concat ('hey', ' you');

To see expected behavior, run these commands from the mysql command line client:
set sql_mode='IGNORE_SPACE';
select concat ('hey', ' you');

Suggested fix:
Set the IGNORE_SPACE sql_mode when the -i option is used when the command-line client is called.  (I don't really know what to put here, so I used the obvious)
[28 Aug 2008 16:30] MySQL Verification Team
Thank you for the bug report. It happens on Windows too, 5.1 isn't affected.

C:\dbs\5.0>bin\mysql -uroot --port=3500 --ignore-spaces
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.70-nt-debug-log Source distribution

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

mysql> select concat ('hey', ' you');
ERROR 1305 (42000): FUNCTION concat does not exist

C:\dbs>5.1\bin\mysql -uroot --port=3510 --ignore-spaces
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.28-nt-debug-log Source distribution

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

mysql> select concat ('hey', ' you');
+------------------------+
| concat ('hey', ' you') |
+------------------------+
| hey you                |
+------------------------+
1 row in set (0.05 sec)
[28 Aug 2008 23:21] Jim Winstead
It's not SQL_MODE that needs to be changed, there's a flag that can be set on the connection to enable the ignore-space mode (CLIENT_IGNORE_SPACES). But what's strange is that the mysql command-line client isn't doing anything with -i in the 4.1 or later trees. I'm not sure if this was removed intentionally (but incompletely) or when.

I'm not sure why it works for Miguel with 5.1 -- the client code clearly doesn't do anything in response to -i or --ignore-spaces.
[29 Aug 2008 0:33] Marc ALFF
Jim, Miguel,

IGNORE_SPACE affects almost every function in 5.0,
but only a few functions in 5.1 and up.

See:
http://dev.mysql.com/doc/refman/6.0/en/function-resolution.html

and in particular:

The requirement that function calls be written with no whitespace between the name and the parenthesis applies only to the built-in functions that have special considerations. COUNT is one such name. The exact list of function names for which following whitespace determines their interpretation are those listed in the sql_functions[] array of the sql/lex.h source file. Before MySQL 5.1, these names are rather numerous (about 200), so you may find it easiest to treat the no-whitespace requirement as applying to all function calls. In MySQL 5.1 and later, parser improvements reduce to about 30 the number of affected function names.

concat() is one of the functions that were broken in 5.0 and fixed in 5.1,
which explains the difference: parsing the call to concat() does not depends
on IGNORE_SPACE anymore in 5.1, which is why it works.
[3 Sep 2008 0:16] Omer Barnir
triage: P3 - embarrassment factor setting tag to CHECKED
[7 May 2009 17:29] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/73598

2736 Jim Winstead	2009-05-07
      Fix support for -i (--ignore-spaces) in the mysql command line application,
      which didn't actually do anything. (Bug #39101)
      modified:
        client/mysql.cc
        mysql-test/r/mysql.result
        mysql-test/t/mysql.test
[12 May 2009 21:26] Jim Winstead
Queued to 5.0-bugteam, will be merged upward from there.
[28 May 2009 7:42] Bugs System
Pushed into 5.0.83 (revid:joro@sun.com-20090528073529-q9b8s60vlpu28fny) (version source revid:jimw@mysql.com-20090507172829-du4903q9zfd08v2w) (merge vers: 5.0.82) (pib:6)
[28 May 2009 8:13] Bugs System
Pushed into 5.1.36 (revid:joro@sun.com-20090528073639-yohsb4q1jzg7ycws) (version source revid:jimw@mysql.com-20090513154800-ae963ppyd254q86j) (merge vers: 5.1.35) (pib:6)
[29 May 2009 2:01] Paul DuBois
Noted in 5.0.83, 5.1.36 changelogs.

The mysql option --ignore-spaces was non-functional.

Setting report to NDI pending push into 6.0.x.
[17 Jun 2009 19:21] Bugs System
Pushed into 5.4.4-alpha (revid:alik@sun.com-20090616183122-chjzbaa30qopdra9) (version source revid:jimw@mysql.com-20090514020104-7u5cqf5cyywa8aks) (merge vers: 6.0.12-alpha) (pib:11)
[25 Jun 2009 2:22] Paul DuBois
Noted in 5.4.4 changelog.
[12 Aug 2009 22:52] Paul DuBois
Noted in 5.4.2 changelog because next 5.4 version will be 5.4.2 and not 5.4.4.
[15 Aug 2009 2:07] Paul DuBois
Ignore previous comment about 5.4.2.
[26 Aug 2009 13:45] Bugs System
Pushed into 5.1.37-ndb-7.0.8 (revid:jonas@mysql.com-20090826132541-yablppc59e3yb54l) (version source revid:jonas@mysql.com-20090826132541-yablppc59e3yb54l) (merge vers: 5.1.37-ndb-7.0.8) (pib:11)
[26 Aug 2009 13:46] Bugs System
Pushed into 5.1.37-ndb-6.3.27 (revid:jonas@mysql.com-20090826105955-bkj027t47gfbamnc) (version source revid:jonas@mysql.com-20090826105955-bkj027t47gfbamnc) (merge vers: 5.1.37-ndb-6.3.27) (pib:11)
[26 Aug 2009 13:48] Bugs System
Pushed into 5.1.37-ndb-6.2.19 (revid:jonas@mysql.com-20090825194404-37rtosk049t9koc4) (version source revid:jonas@mysql.com-20090825194404-37rtosk049t9koc4) (merge vers: 5.1.37-ndb-6.2.19) (pib:11)
[27 Aug 2009 16:32] Bugs System
Pushed into 5.1.35-ndb-7.1.0 (revid:magnus.blaudd@sun.com-20090827163030-6o3kk6r2oua159hr) (version source revid:jonas@mysql.com-20090826132541-yablppc59e3yb54l) (merge vers: 5.1.37-ndb-7.0.8) (pib:11)
[8 Oct 2009 20:12] Paul DuBois
The 5.4 fix has been pushed to 5.4.2.