Bug #5318 failure: 'IGNORE_SPACE' affects numeric values after DEFAULT
Submitted: 31 Aug 2004 13:46 Modified: 14 Sep 2004 15:19
Reporter: Matthias Leich Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.1, 5.0 OS:
Assigned to: Ingo Strüwing CPU Architecture:Any

[31 Aug 2004 13:46] Matthias Leich
Description:
sql_mode = 'IGNORE_SPACE' can affect numeric values after
DEFAULT in a bad way.
Example (modified NIST test case, some statements omitted):
set session sql_mode = '';
create table EXACT_DEF ( min_num   dec(6,6)     default .000001);
 --> success, Attention: The default value starts with a leading '.'

set session sql_mode = 'IGNORE_SPACE';
create table EXACT_DEF ( min_num   dec(6,6)     default 0.000001);
  --> success,  Attention: The default value starts with a leading number.

create table EXACT_DEF ( min_num   dec(6,6)     default .000001);
 --> ... failed: 1064: 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 'default .000001)'

The manual mentions in 5.2.2 The Server SQL Mode, 
that 'IGNORE_SPACE' allow spaces between a function name 
and the `(' character. But in my test case there is no function involved.

My environment:
   - Intel PC with Linux(SuSE 9.0)
   - MySQL 4.1 compiled from source
        last ChangeSet@1.2022.1.4, 2004-08-30
     MySQL 5.0 compiled from source
        last ChangeSet@1.1738, 2004-08-28

How to repeat:
Please use my test file, copy it to mysql-test/t
  ./mysql-test-run ml_err30
[31 Aug 2004 13:54] Matthias Leich
test case

Attachment: ml_err30.test (application/octet-stream, text), 634 bytes.

[31 Aug 2004 13:55] Matthias Leich
expected result

Attachment: ml_err30.result (application/octet-stream, text), 355 bytes.

[11 Sep 2004 18:59] Ingo Strüwing
Added a check to recover from IGNORE_SPACE in this situation: 
<ident-character(s)><space>.<ident-character(s)>
The ignored space led to the false identification of the dot
as an ident separator (like "db.table").
[14 Sep 2004 15:19] Ingo Strüwing
Fixed in 4.1.5