MySQL Bugs: #39101: client -i (--ignore-spaces) option does not seem to work

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

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)

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)

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.

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.

triage: P3 - embarrassment factor setting tag to CHECKED

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

Queued to 5.0-bugteam, will be merged upward from there.

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)

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)

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.

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)

Noted in 5.4.4 changelog.

Noted in 5.4.2 changelog because next 5.4 version will be 5.4.2 and not 5.4.4.

Ignore previous comment about 5.4.2.

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)

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)

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)

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)

The 5.4 fix has been pushed to 5.4.2.