possible BUG in 'between' comparisons
>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:
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:
lrwxrwxrwx1 root root 13 Apr 16 2004 /lib/libc.so.6 ->
libc-2.3.2.so
-rwxr-xr-x1 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]
Re: possible BUG in 'between' comparisons
I have confirmed that this problem exhibits itself on the Mysql AB
compiled binaries that are compiled with gcc, both Standard and Max, but
does _not_ exhibit itself on the Mysql AB binary built with the Intel C++
Compiler 8.1 (mysql-standard-4.1.12-pc-linux-gnu-i686-icc-glibc23.tar.gz).
I have also verifed that the problem does _not_ exhibit on the mysql-4.12
server in Debian stable (Sarge), nor in Gentoo's latest ebuild.
I believe this problem likely stems from a compiler problem with the version
(2.95.3 20010315 SuSE) of GCC that Mysql AB compiles with.
# GCC: mysql-standard-4.1.12-pc-linux-gnu-i686.tar.gz
[EMAIL PROTECTED]:/usr/local# ls -l mysql
lrwxrwxrwx 1 root root 9 Jul 8 21:25 /usr/local/mysql -> mysql-gcc/
[EMAIL PROTECTED]:/usr/local# ./mysql/bin/mysqld_safe --user=mysql &
[EMAIL PROTECTED]:/# /usr/local/mysql/bin/mysql test
[...snip...]
mysql> 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)
[EMAIL PROTECTED]:/usr/local# ./mysql/bin/mysqladmin shutdown
STOPPING server from pid file /usr/local/mysql/data/ponybox.pid
050708 21:30:36 mysqld ended
# ICC: mysql-standard-4.1.12-pc-linux-gnu-i686-icc-glibc23.tar.gz
[EMAIL PROTECTED]:/usr/local# rm mysql && ln -s mysql-icc mysql
[EMAIL PROTECTED]:/usr/local# ./mysql/bin/mysqld_safe --user=mysql &
[EMAIL PROTECTED]:/# /usr/local/mysql/bin/mysql test
[...snip...]
mysql> select * from foo1 left join foo2 on foo2.test1 between foo1.test1 and
foo1.test2;
+---++++
| test1 | test2 | test1 | test2 |
+---++++
| 97.50 | 154.30 | 154.30 | 154.30 |
+---++++
--
Lester Hightower <[EMAIL PROTECTED]>
10East Corp.
On Fri, 8 Jul 2005 [EMAIL PROTECTED] wrote:
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:
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-sl
