Bug #52400 Extract statement need bracket without a space between

• Submitted: 26 Mar 2010 18:16
• Modified: 27 Mar 2010 15:13
• Reporter: gabriele f
• Status: Not a Bug
• Category: MySQL Server: General
• Severity: S4 (Feature request)
• Version: 5.0.87
• OS: Linux
• CPU Architecture: Any
• Tags: date, EXTRACT, syntax

[26 Mar 2010 18:16] gabriele f

Description:
when I use a query with extract statement if I type a space between extract word and the bracket mysql tell me that there's an error. I'd like you remove this trouble.

How to repeat:
using a query like this:
select * from address_book where extract (year from borndate)  = 2009
please see the space between extract word and the opening bracket.

[27 Mar 2010 15:13] Valeriy Kravchuk

This is not a bug. Please, read the manual, http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html#sqlmode_ignore_space. Look:

77-52-28-202:5.0 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.91-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select extract(year from '2009-01-01') as a;
+------+
| a    |
+------+
| 2009 | 
+------+
1 row in set (0.00 sec)

mysql> select extract (year from '2009-01-01') as a;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'from '2009-01-01') as a' at line 1
mysql> set session sql_mode='IGNORE_SPACE';
Query OK, 0 rows affected (0.00 sec)

mysql> select extract (year from '2009-01-01') as a;
+------+
| a    |
+------+
| 2009 | 
+------+
1 row in set (0.00 sec)