Bug #6158 ANSI: Parser requires leading zero for decimal number in math operation

• Submitted: 19 Oct 2004 16:05
• Modified: 10 Nov 2004 18:40
• Reporter: Trudy Pelzer
• Status: Not a Bug
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 5.0.2-debug
• OS: Linux (SuSE 9.1)
• Assigned to: Antony Curtis
• CPU Architecture: Any

[19 Oct 2004 16:05] Trudy Pelzer

Description:
When sql_mode='ansi', the parser is returning a syntax error
in cases where a decimal number less than 1 is used as the
first operand in a math operation. This does not happen in
the default sql mode, nor in traditional mode.

Note: The same bugs exists in version 4.1.4-gamma.

How to repeat:
mysql> set sql_mode='';
Query OK, 0 rows affected (0.00 sec)

mysql> select .7777 - 0.1;
+-------------+
| .7777 - 0.1 |
+-------------+
|      0.6777 |
+-------------+
1 row in set (0.01 sec)

mysql> select 0.7777 - 0.1;
+--------------+
| 0.7777 - 0.1 |
+--------------+
|       0.6777 |
+--------------+
1 row in set (0.01 sec)

mysql> select 0.7777 - .1;
+-------------+
| 0.7777 - .1 |
+-------------+
|      0.6777 |
+-------------+
1 row in set (0.00 sec)

mysql> select .7777 - .1;
+------------+
| .7777 - .1 |
+------------+
|     0.6777 |
+------------+
1 row in set (0.00 sec)
-- Note that all 4 combinations of operands, with and without a
leading zero, are processed without problem.

mysql> set sql_mode=ansi;
Query OK, 0 rows affected (0.00 sec)

mysql> select .7777 - 0.1;
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 'selec
t .7777 - 0.1' at line 1
-- Not the same behaviour. Even in ansi mode, there is no
reason to require the leading zero for the first operand.

mysql> select 0.7777 - 0.1;
+--------------+
| 0.7777 - 0.1 |
+--------------+
|       0.6777 |
+--------------+
1 row in set (0.00 sec)

mysql> select 0.7777 - .1;
+-------------+
| 0.7777 - .1 |
+-------------+
|      0.6777 |
+-------------+
1 row in set (0.00 sec)

mysql> select .7777 - .1;
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 'selec
t .7777 - .1' at line 1
-- Not the same behaviour. Even in ansi mode, there is no
reason to require the leading zero for the first operand.

[19 Oct 2004 16:13] MySQL Verification Team

Verified on latest BK source.

[10 Nov 2004 10:35] Per-Erik Martin

As per email: If it's not fixed in 4.1 already, ok to push.

[10 Nov 2004 18:40] Antony Curtis

Thank you for taking the time to report a problem.  Unfortunately
you are not using a current version of the product your reported a
problem with -- the problem might already be fixed. Please download
a new version from http://www.mysql.com/downloads/

If you are able to reproduce the bug with one of the latest versions,
please change the version on this bug report to the version you
tested and change the status back to "Open".  Again, thank you for
your continued support of MySQL.