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.
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.