>Description:
There appears to be a type promotion problem involving sql statements which
include a 'between' comparison and a decimal type field is one of the operands.
>How-To-Repeat:
CREATE TABLE `foo1` (
`test1` decimal(7,2) NOT NULL default '0.00',
`test2` decimal(7,2) NOT NULL default '0.00'
);
INSERT INTO `foo1` (`test1`, `test2`) VALUES ('97.50','154.30');
CREATE TABLE `foo2` (
`test1` decimal(7,2) NOT NULL default '0.00',
`test2` decimal(7,2) NOT NULL default '0.00'
);
INSERT INTO `foo2` (`test1`, `test2`) VALUES ('154.30','154.30');
CREATE TABLE `foo3` (
`test1` float(7,2) NOT NULL default '0.00',
`test2` float(7,2) NOT NULL default '0.00'
);
INSERT INTO `foo3` (`test1`, `test2`) VALUES (97.50,154.30);
CREATE TABLE `foo4` (
`test1` float(7,2) NOT NULL default '0.00',
`test2` float(7,2) NOT NULL default '0.00'
);
INSERT INTO `foo4` (`test1`, `test2`) VALUES (154.30,154.30);
select * from foo1 left join foo2 on foo2.test1 between foo1.test1 and
foo1.test2;
+-------+--------+-------+-------+
| test1 | test2 | test1 | test2 |
+-------+--------+-------+-------+
| 97.50 | 154.30 | NULL | NULL |
+-------+--------+-------+-------+
1 row in set (0.00 sec)
select * from foo3 left join foo4 on foo4.test2 between foo3.test1 and
foo3.test2;
+-------+--------+--------+--------+
| test1 | test2 | test1 | test2 |
+-------+--------+--------+--------+
| 97.50 | 154.30 | 154.30 | 154.30 |
+-------+--------+--------+--------+
1 row in set (0.00 sec)
mysql> select * from foo1 where test1 between 97.50 and 154.30;
+-------+--------+
| test1 | test2 |
+-------+--------+
| 97.50 | 154.30 |
+-------+--------+
1 row in set (0.00 sec)
mysql> select * from foo1 where 154.30 between test1 and test2;
Empty set (0.00 sec)
mysql> select * from foo1 where test2 between 154.30 and test2;
Empty set (0.00 sec)
>Fix:
A work around is to include OR conditions that are equal comparisons to the
boundary condition of the BETWEEN. This query demonstrates a work around.
mysql> select * from foo1 where test2 between 154.30 and test2 or 154.30=test2;
+-------+--------+
| test1 | test2 |
+-------+--------+
| 97.50 | 154.30 |
+-------+--------+
1 row in set (0.00 sec)
>Originator: Tom Allen
>Organization:
10 East Corp
>MySQL support: none
>Synopsis: BETWEEN comparisons with one or more DECIMAL type fields as
>operands don't handle boundary conditions properly
>Severity: <[ non-critical | serious | critical ] (one line)>
>Priority: <[ low | medium | high ] (one line)>
>Category: mysql
>Class: <[ sw-bug | doc-bug | change-request | support ] (one line)>
>Release: mysql-4.1.12-max (MySQL Community Edition - Experimental (GPL))
>C compiler: 2.95.3
>C++ compiler: 2.95.3
>Environment:
<machine, os, target, libraries (multiple lines)>
System: Linux docs2 2.6.12 #1 SMP Mon Jun 20 12:08:43 EDT 2005 i686 unknown
unknown GNU/Linux
Architecture: i686
Some paths: /usr/local/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc
/usr/bin/cc
GCC: Reading specs from /usr/lib/gcc-lib/i486-slackware-linux/3.2.3/specs
Configured with: ../gcc-3.2.3/configure --prefix=/usr --enable-shared
--enable-threads=posix --enable-__cxa_atexit --disable-checking --with-gnu-ld
--verbose --target=i486-slackware-linux --host=i486-slackware-linux
Thread model: posix
gcc version 3.2.3
Compilation info: CC='ccache gcc' CFLAGS='-O2 -mpentiumpro' CXX='ccache gcc'
CXXFLAGS='-O2 -mpentiumpro -felide-constructors' LDFLAGS='' ASFLAGS=''
LIBC:
lrwxrwxrwx 1 root root 13 Apr 16 2004 /lib/libc.so.6 ->
libc-2.3.2.so
-rwxr-xr-x 1 root root 1458907 May 18 2003 /lib/libc-2.3.2.so
-rw-r--r-- 1 root root 2467548 May 18 2003 /usr/lib/libc.a
-rw-r--r-- 1 root root 204 May 18 2003 /usr/lib/libc.so
Configure command: ./configure '--prefix=/usr/local/mysql'
'--localstatedir=/usr/local/mysql/data' '--libexecdir=/usr/local/mysql/bin'
'--with-comment=MySQL Community Edition - Experimental (GPL)'
'--with-extra-charsets=complex' '--with-server-suffix=-max'
'--enable-thread-safe-client' '--enable-local-infile' '--enable-assembler'
'--disable-shared' '--with-berkeley-db' '--with-big-tables' '--with-raid'
'--with-readline' '--with-embedded-server' '--with-archive-storage-engine'
'--with-blackhole-storage-engine' '--with-ndbcluster'
'--with-example-storage-engine' '--with-innodb' 'CC=ccache gcc' 'CFLAGS=-O2
-mpentiumpro' 'CXXFLAGS=-O2 -mpentiumpro -felide-constructors' 'CXX=ccache gcc'
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
