Bug #15890 Partitions: Strange interpretation of partition number
Submitted: 20 Dec 2005 14:41 Modified: 2 Sep 2006 4:28
Reporter: Matthias Leich Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Partitions Severity:S3 (Non-critical)
Version:5.1 OS:
Assigned to: Mikael Ronström CPU Architecture:Any

[20 Dec 2005 14:41] Matthias Leich
Description:
There are several unfortunate effects arount the checks of 
assigned partition numbers:
1. CREATE TABLE t1 ( f1 BIGINT, f2 char(20))
    PARTITION BY HASH(f1) PARTITIONS -1;
     --> ERROR 42000: You have an error in your SQL syntax; check the 
           manual ..... for the right syntax to use near '-1' at line 2
    I think the error message
           ERROR HY000: Number of partitions = -1 is not an allowed value
    would be better.
2. CREATE TABLE t1 ( f1 BIGINT, f2 char(20))
    PARTITION BY HASH(f1) PARTITIONS 1.5;
    SHOW CREATE TABLE t1;
    Table   Create Table
    t1      CREATE TABLE `t1` (
      `f1` bigint(20) default NULL,
      `f2` char(20) default NULL
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1 PARTITION BY HASH (f1)
    PARTITIONS 1
    I think the brutal rounding/truncating of 1.5 to 1 (decrease value 33 %)
    is not acceptable. An error message would be better.
3. CREATE TABLE t1 ( f1 BIGINT, f2 char(20))
    PARTITION BY HASH(f1) PARTITIONS 0.2E+1;
    --> ERROR HY000: Number of partitions = 0 is not an allowed value
    In mathematics 0.2E+1 equals 2, why shouldn't this be also
    valid as long a (double) float value can be converted to an integer
    without rounding.
4. CREATE TABLE t1 ( f1 BIGINT, f2 char(20))
    PARTITION BY HASH(f1) PARTITIONS 1E+300;
    SHOW CREATE TABLE t1;
    Table   Create Table
     t1      CREATE TABLE `t1` (
       `f1` bigint(20) default NULL,
       `f2` char(20) default NULL
      ) ENGINE=MyISAM DEFAULT CHARSET=latin1 
      PARTITION BY HASH (f1) PARTITIONS 1
    Effect similar bad like 3.

My environment:
   - Intel PC with Linux(SuSE 9.3)
   - MySQL compiled from source
         bk-internal.mysql.com:/home/bk/mysql-5.1-wl2604-new
              last ChangeSet@1.1981, 2005-12-15   
         and also
         Version 5.1
              last ChangeSet@1.1967.3.2, 2005-12-16   

How to repeat:
Please execute the statements above.

Suggested fix:
Please implement a strict behaviour.
- The assigned partition/subpartion number must
  consist of digits without leading zeros and 
  nothing else (no fractions, no exponents).
- I like that numbers enclosed between single
  or double quotes are rejected. Such values 
  are strings and not a number.

A more non strict behaviour like accept all
values written in integer/decimal/floating point style
which can be converted (without rounding/truncating)
to an integer is thinkable, but I do not beleave that
such a feature gives any worth-while benefits.
[7 Aug 2006 16:18] Mikael Ronström
This is a general problem and I fixed a solution that can
hopefully be reused by others as well.
[8 Aug 2006 13:07] Mikael Ronström
Was reviewed by Antony Curtis with comments to
1) Add comments to new rules
2) Change name of new rules to only_ulong_num instead of
real_ulong_num
[31 Aug 2006 20:36] Mikael Ronström
fixed in 5.1-kt tree
[1 Sep 2006 7:51] Mikael Ronström
Patch will appear in 5.1.12
[2 Sep 2006 4:28] Jon Stephens
Documented in 5.1.12 changelog. Also updated Partitioning and CREATE TABLE/ALTER TABLE material in 5.1 Manual.