>Description:
In certain cases the select fails (ie (a) does not return required rows, or (b)
returns non-wanted rows (cannot repeat (b)))
The problem seems to be related to negative values in decimal type fields and in query
against those fields.
the problem is applicable to several 3.22.* versions on different machines, have no
possibility to check with 3.23.*.
>How-To-Repeat:
create the table:
DROP TABLE IF EXISTS dataformats;
CREATE TABLE dataformats (
id int(11) DEFAULT '0' NOT NULL auto_increment,
datatype_id int(11) DEFAULT '0' NOT NULL,
minvalue decimal(20,10) DEFAULT '0.0000000000' NOT NULL,
maxvalue decimal(20,10) DEFAULT '0.0000000000' NOT NULL,
valuename varchar(20),
forecolor int(11),
backcolor int(11),
PRIMARY KEY (id),
UNIQUE datatype_id (datatype_id, minvalue, maxvalue)
);
.. and add the following content
INSERT INTO dataformats VALUES ( '1', '4', '0.0000000000', '0.0000000000', 'Ei saja',
'0', '16776960');
INSERT INTO dataformats VALUES ( '2', '4', '1.0000000000', '1.0000000000', 'Sajab',
'16777215', '255');
INSERT INTO dataformats VALUES ( '3', '1', '2.0000000000', '49.0000000000', '', '0',
'16777215');
INSERT INTO dataformats VALUES ( '60', '11', '0.0000000000', '0.0000000000', 'Rikkis',
'16777215', '16711680');
INSERT INTO dataformats VALUES ( '4', '12', '1.0000000000', '1.0000000000', 'nork
sadu', '65280', '14474460');
INSERT INTO dataformats VALUES ( '5', '12', '2.0000000000', '2.0000000000', 'keskmine
sadu', '255', '14474460');
INSERT INTO dataformats VALUES ( '6', '12', '3.0000000000', '3.0000000000', 'tugev
sadu', '127', '14474460');
INSERT INTO dataformats VALUES ( '43', '39', '6.0000000000', '6.0000000000',
'lobjakas', '13107327', '16763080');
INSERT INTO dataformats VALUES ( '40', '39', '2.0000000000', '2.0000000000', 'vihm',
'8355839', '16777215');
INSERT INTO dataformats VALUES ( '53', '1', '-35.0000000000', '-5.0000000000', '',
'0', '16777215');
INSERT INTO dataformats VALUES ( '41', '39', '3.0000000000', '3.0000000000', 'külm
vihm', '120', '16763080');
INSERT INTO dataformats VALUES ( '12', '21', '21.0000000000', '21.0000000000', 'Kuiv',
'13158600', '16777215');
INSERT INTO dataformats VALUES ( '13', '21', '13.0000000000', '13.0000000000', 'Märg',
'5263615', '16777215');
INSERT INTO dataformats VALUES ( '14', '21', '22.0000000000', '22.0000000000',
'Niiske', '9869055', '16777215');
INSERT INTO dataformats VALUES ( '19', '21', '33.0000000000', '33.0000000000', 'Märg',
'5263615', '16777215');
INSERT INTO dataformats VALUES ( '15', '21', '23.0000000000', '23.0000000000', 'Märg',
'5263615', '16777215');
INSERT INTO dataformats VALUES ( '16', '21', '31.0000000000', '31.0000000000', 'Kuiv',
'13158600', '16777215');
INSERT INTO dataformats VALUES ( '17', '21', '12.0000000000', '12.0000000000',
'Niiske', '9869055', '16777215');
INSERT INTO dataformats VALUES ( '18', '21', '32.0000000000', '32.0000000000',
'Niiske', '9869055', '16777215');
INSERT INTO dataformats VALUES ( '20', '21', '331.0000000000', '331.0000000000',
'Härmatise hoiatus!', '14448840', '13158600');
INSERT INTO dataformats VALUES ( '21', '21', '11.0000000000', '11.0000000000', 'Kuiv',
'13158600', '16777215');
INSERT INTO dataformats VALUES ( '22', '33', '21.0000000000', '21.0000000000',
'Pilves, kuiv', '8355711', '12632256');
INSERT INTO dataformats VALUES ( '23', '33', '13.0000000000', '13.0000000000', 'Sajab,
märg', '0', '8355839');
INSERT INTO dataformats VALUES ( '24', '33', '22.0000000000', '22.0000000000',
'Pilves, niiske', '8355711', '12632319');
INSERT INTO dataformats VALUES ( '29', '33', '33.0000000000', '33.0000000000', 'Selge,
märg', '16777215', '8355839');
INSERT INTO dataformats VALUES ( '25', '33', '23.0000000000', '23.0000000000',
'Pilves, märg', '8355711', '8355839');
INSERT INTO dataformats VALUES ( '26', '33', '31.0000000000', '31.0000000000', 'Selge,
kuiv', '16777215', '12632256');
INSERT INTO dataformats VALUES ( '27', '33', '12.0000000000', '12.0000000000', 'Sajab,
niiske', '0', '12632319');
INSERT INTO dataformats VALUES ( '28', '33', '32.0000000000', '32.0000000000', 'Selge,
niiske', '16777215', '12632319');
INSERT INTO dataformats VALUES ( '30', '33', '331.0000000000', '331.0000000000',
'Härmatis! selge,kuiv', '16711680', '12632256');
INSERT INTO dataformats VALUES ( '31', '33', '11.0000000000', '11.0000000000', 'Sajab,
kuiv', '0', '12632256');
INSERT INTO dataformats VALUES ( '32', '11', '1.0000000000', '1.0000000000', 'Korras',
'16777215', '49152');
INSERT INTO dataformats VALUES ( '33', '21', '335.0000000000', '335.0000000000',
'Härmatis!', '14448840', '11842740');
INSERT INTO dataformats VALUES ( '34', '21', '134.0000000000', '134.0000000000',
'Hoiatus, M+S!', '255', '13158600');
INSERT INTO dataformats VALUES ( '35', '21', '133.0000000000', '133.0000000000',
'Hoiatus, märg!', '5263615', '13158600');
INSERT INTO dataformats VALUES ( '36', '21', '135.0000000000', '135.0000000000',
'Härmatis!', '14448840', '11842740');
INSERT INTO dataformats VALUES ( '37', '21', '334.0000000000', '334.0000000000',
'Härmatise hoiatus!', '14448840', '13158600');
INSERT INTO dataformats VALUES ( '38', '21', '132.0000000000', '132.0000000000',
'Hoiatus, niiske!', '9869055', '13158600');
INSERT INTO dataformats VALUES ( '39', '39', '1.0000000000', '1.0000000000', 'ei
saja', '11206570', '16777215');
INSERT INTO dataformats VALUES ( '44', '39', '4.0000000000', '5.0000000000', 'lumi',
'16711680', '16763080');
INSERT INTO dataformats VALUES ( '45', '12', '0.0000000000', '0.0000000000', '',
'16777215', '14474460');
INSERT INTO dataformats VALUES ( '46', '39', '8.0000000000', '8.0000000000', 'rahe',
'9830400', '16763080');
INSERT INTO dataformats VALUES ( '47', '39', '9.0000000000', '9.0000000000', 'tüüp
ebaselge', '12582912', '16777215');
INSERT INTO dataformats VALUES ( '48', '39', '7.0000000000', '7.0000000000',
'lumetuisk', '7209070', '16763080');
INSERT INTO dataformats VALUES ( '142', '15', '2.0000000000', '49.0000000000', '',
'0', '16777215');
INSERT INTO dataformats VALUES ( '52', '1', '-4.9000000000', '-0.1000000000', '', '0',
'15774720');
INSERT INTO dataformats VALUES ( '141', '15', '-4.9000000000', '-0.1000000000', '',
'0', '15774720');
INSERT INTO dataformats VALUES ( '55', '8', '0.0000000000', '0.0000000000', '', '0',
'16777215');
INSERT INTO dataformats VALUES ( '56', '8', '0.0100000000', '0.1000000000', '', '0',
'16770560');
INSERT INTO dataformats VALUES ( '57', '8', '0.1100000000', '25.0000000000', '', '0',
'15774720');
INSERT INTO dataformats VALUES ( '58', '2', '90.0000000000', '94.9000000000', '',
NULL, '16770560');
INSERT INTO dataformats VALUES ( '59', '6', '0.0000000000', '360.0000000000', '',
NULL, '16777215');
INSERT INTO dataformats VALUES ( '61', '21', '38.0000000000', '38.0000000000',
'Niiske', '9869055', '16777215');
INSERT INTO dataformats VALUES ( '62', '38', '500.0000000000', '999.0000000000', '',
'0', '16770560');
INSERT INTO dataformats VALUES ( '63', '38', '1000.0000000000', '2000.0000000000', '',
'0', '16777215');
INSERT INTO dataformats VALUES ( '64', '17', '0.0000000000', '0.0000000000', '', NULL,
'16777215');
INSERT INTO dataformats VALUES ( '65', '17', '0.1000000000', '10.0000000000', '',
NULL, '16770560');
INSERT INTO dataformats VALUES ( '67', '21', '412.0000000000', '412.0000000000',
'Niiske', '9869055', '16777215');
INSERT INTO dataformats VALUES ( '68', '21', '413.0000000000', '413.0000000000',
'Märg', '5263615', '16777215');
INSERT INTO dataformats VALUES ( '69', '21', '113.0000000000', '113.0000000000',
'Märg', '5263615', '16777215');
INSERT INTO dataformats VALUES ( '70', '21', '416.0000000000', '416.0000000000',
'Lumine!', '16711680', '11842740');
INSERT INTO dataformats VALUES ( '71', '38', '0.0000000000', '499.0000000000', '',
NULL, '16711680');
INSERT INTO dataformats VALUES ( '72', '22', '-49.0000000000', '49.0000000000', '',
NULL, '16777215');
INSERT INTO dataformats VALUES ( '73', '13', '0.0000000000', '9.9000000000', '', NULL,
'16777215');
INSERT INTO dataformats VALUES ( '74', '13', '10.0000000000', '14.9000000000', '',
NULL, '16770560');
INSERT INTO dataformats VALUES ( '75', '7', '0.0000000000', '50.0000000000', '', NULL,
'16777215');
INSERT INTO dataformats VALUES ( '76', '18', '0.0000000000', '0.0000000000', '', NULL,
'16777215');
INSERT INTO dataformats VALUES ( '77', '18', '0.1000000000', '10.0000000000', '',
NULL, '16770560');
INSERT INTO dataformats VALUES ( '78', '19', '300.0000000000', '400.0000000000', '',
NULL, '16777215');
INSERT INTO dataformats VALUES ( '79', '19', '0.0000000000', '299.0000000000', '',
NULL, '16770560');
INSERT INTO dataformats VALUES ( '80', '23', '0.0000000000', '100.0000000000', '',
NULL, '16777215');
INSERT INTO dataformats VALUES ( '81', '24', '0.0000000000', '200.0000000000', '',
NULL, '16777215');
INSERT INTO dataformats VALUES ( '82', '26', '0.0000000000', '0.0000000000', '', NULL,
'16777215');
INSERT INTO dataformats VALUES ( '83', '26', '0.1000000000', '5.0000000000', '', NULL,
'16776960');
INSERT INTO dataformats VALUES ( '84', '21', '422.0000000000', '422.0000000000',
'Niiske', '9869055', '16777215');
INSERT INTO dataformats VALUES ( '85', '21', '411.0000000000', '411.0000000000', 'Saju
hoiat.,kuiv!', '16777215', '13158600');
INSERT INTO dataformats VALUES ( '86', '21', '423.0000000000', '423.0000000000',
'Märg', '5263615', '16777215');
INSERT INTO dataformats VALUES ( '144', '16', '-49.0000000000', '-5.0000000000', '',
NULL, '16777215');
INSERT INTO dataformats VALUES ( '88', '16', '2.0000000000', '49.0000000000', '',
NULL, '16777215');
INSERT INTO dataformats VALUES ( '89', '21', '338.0000000000', '338.0000000000',
'Härm.hoiatus, N+S!', '16744319', '13158600');
INSERT INTO dataformats VALUES ( '90', '21', '332.0000000000', '332.0000000000',
'Härm.hoiat., niiske!', '16744319', '13158600');
INSERT INTO dataformats VALUES ( '91', '21', '114.0000000000', '114.0000000000',
'Hoiatus, M+S!', '255', '13158600');
INSERT INTO dataformats VALUES ( '92', '21', '117.0000000000', '117.0000000000',
'Hoiatus, JÄÄ!', '14448840', '16711680');
INSERT INTO dataformats VALUES ( '93', '21', '116.0000000000', '116.0000000000',
'Lumine!', '16711680', '11842740');
INSERT INTO dataformats VALUES ( '94', '21', '414.0000000000', '414.0000000000',
'Hoiatus, M+S!', '255', '13158600');
INSERT INTO dataformats VALUES ( '95', '21', '325.0000000000', '325.0000000000',
'Härmatis!', '14448840', '11842740');
INSERT INTO dataformats VALUES ( '96', '21', '321.0000000000', '321.0000000000',
'Härmatise hoiatus!', '14448840', '13158600');
INSERT INTO dataformats VALUES ( '97', '21', '328.0000000000', '328.0000000000',
'Härm.hoiatus, N+S!', '16744319', '13158600');
INSERT INTO dataformats VALUES ( '98', '21', '28.0000000000', '28.0000000000', 'Niiske
ja sool', '9869055', '16777215');
INSERT INTO dataformats VALUES ( '99', '21', '118.0000000000', '118.0000000000',
'Hoiatus, N+S!', '9869055', '13158600');
INSERT INTO dataformats VALUES ( '100', '21', '418.0000000000', '418.0000000000',
'Hoiatus, N+S!', '9869055', '13158600');
INSERT INTO dataformats VALUES ( '101', '21', '322.0000000000', '322.0000000000',
'Härm.hoiat., niiske!', '16744319', '13158600');
INSERT INTO dataformats VALUES ( '102', '21', '428.0000000000', '428.0000000000',
'Hoiatus, N+S!', '9869055', '13158600');
INSERT INTO dataformats VALUES ( '103', '21', '432.0000000000', '432.0000000000',
'Hoiatus, niiske!', '7895240', '13158600');
INSERT INTO dataformats VALUES ( '104', '21', '421.0000000000', '421.0000000000',
'Saju hoiat.,kuiv!', '16777215', '13158600');
INSERT INTO dataformats VALUES ( '105', '21', '24.0000000000', '24.0000000000', 'Märg
ja sool', '255', '16777215');
INSERT INTO dataformats VALUES ( '106', '21', '438.0000000000', '438.0000000000',
'Hoiatus, N+S!', '9869055', '13158600');
INSERT INTO dataformats VALUES ( '107', '21', '112.0000000000', '112.0000000000',
'Hoiatus, niiske!', '9869055', '13158600');
INSERT INTO dataformats VALUES ( '108', '21', '34.0000000000', '34.0000000000', 'Märg
ja sool', '255', '16777215');
INSERT INTO dataformats VALUES ( '109', '21', '434.0000000000', '434.0000000000',
'Hoiatus, M+S!', '255', '13158600');
INSERT INTO dataformats VALUES ( '110', '21', '124.0000000000', '124.0000000000',
'Hoiatus, M+S!', '255', '13158600');
INSERT INTO dataformats VALUES ( '111', '21', '424.0000000000', '424.0000000000',
'Hoiatus, M+S!', '255', '13158600');
INSERT INTO dataformats VALUES ( '112', '21', '123.0000000000', '123.0000000000',
'Hoiatus, märg!', '5263615', '13158600');
INSERT INTO dataformats VALUES ( '140', '15', '-49.0000000000', '-5.0000000000', '',
'0', '16777215');
INSERT INTO dataformats VALUES ( '114', '21', '18.0000000000', '18.0000000000',
'Niiske ja sool', '9869055', '16777215');
INSERT INTO dataformats VALUES ( '115', '21', '122.0000000000', '122.0000000000',
'Hoiatus, niiske!', '9869055', '13158600');
INSERT INTO dataformats VALUES ( '116', '21', '14.0000000000', '14.0000000000', 'Märg
ja sool', '255', '16777215');
INSERT INTO dataformats VALUES ( '117', '21', '311.0000000000', '311.0000000000',
'Härmatise hoiatus!', '14448840', '13158600');
INSERT INTO dataformats VALUES ( '121', '2', '95.0000000000', '100.0000000000', '',
NULL, '15774720');
INSERT INTO dataformats VALUES ( '118', '2', '0.0000000000', '89.9000000000', '',
NULL, '16777215');
INSERT INTO dataformats VALUES ( '119', '21', '16.0000000000', '16.0000000000',
'Lumine!', '16711680', '11842740');
INSERT INTO dataformats VALUES ( '120', '21', '26.0000000000', '26.0000000000',
'Lumine!', '16711680', '11842740');
INSERT INTO dataformats VALUES ( '122', '13', '15.0000000000', '50.0000000000', '',
NULL, '15774720');
INSERT INTO dataformats VALUES ( '123', '5', '0.0000000000', '9.9000000000', '', NULL,
'16777215');
INSERT INTO dataformats VALUES ( '124', '5', '10.0000000000', '14.9000000000', '',
NULL, '16770560');
INSERT INTO dataformats VALUES ( '125', '5', '15.0000000000', '50.0000000000', '',
NULL, '15774720');
INSERT INTO dataformats VALUES ( '126', '21', '128.0000000000', '128.0000000000',
'Hoiatus, N+S!', '9869055', '13158600');
INSERT INTO dataformats VALUES ( '127', '21', '318.0000000000', '318.0000000000',
'Härm.hoiatus, N+S!', '16744319', '13158600');
INSERT INTO dataformats VALUES ( '128', '21', '312.0000000000', '312.0000000000',
'Härm.hoiat., niiske!', '16744319', '13158600');
INSERT INTO dataformats VALUES ( '129', '21', '126.0000000000', '126.0000000000',
'Lumine!', '16711680', '11842740');
INSERT INTO dataformats VALUES ( '130', '21', '324.0000000000', '324.0000000000',
'Härmatise hoiatus!', '14448840', '13158600');
INSERT INTO dataformats VALUES ( '131', '21', '316.0000000000', '316.0000000000',
'Lumine!', '16711680', '11842740');
INSERT INTO dataformats VALUES ( '132', '1', '0.0000000000', '1.9000000000', '', NULL,
'16769024');
INSERT INTO dataformats VALUES ( '134', '3', '-50.0000000000', '50.0000000000', '',
NULL, '16777215');
INSERT INTO dataformats VALUES ( '135', '8', '26.0000000000', '2000.0000000000', '',
'9868950', '15774720');
INSERT INTO dataformats VALUES ( '136', '21', '426.0000000000', '426.0000000000',
'Lumine!', '16711680', '11842740');
INSERT INTO dataformats VALUES ( '137', '21', '127.0000000000', '127.0000000000',
'Hoiatus, JÄÄ!', '14448840', '16711680');
INSERT INTO dataformats VALUES ( '138', '21', '121.0000000000', '121.0000000000',
'Kuiv', '13158600', '16777215');
INSERT INTO dataformats VALUES ( '139', '21', '326.0000000000', '326.0000000000',
'Lumine!', '16711680', '11842740');
INSERT INTO dataformats VALUES ( '143', '16', '-4.9000000000', '-0.1000000000', '',
NULL, '15774720');
INSERT INTO dataformats VALUES ( '145', '15', '0.0000000000', '1.9000000000', '', '0',
'16769024');
INSERT INTO dataformats VALUES ( '146', '16', '0.0000000000', '1.9000000000', '', '0',
'16769024');
now see results to the following queries:
(note that the less and more are substituted with ( and ) respectively to avoid
skipping text inbetween those)
mysql> select * from dataformats where datatype_id=16;
+-----+-------------+----------------+---------------+-----------+-----------+-----------+
| id | datatype_id | minvalue | maxvalue | valuename | forecolor |
|backcolor |
+-----+-------------+----------------+---------------+-----------+-----------+-----------+
| 144 | 16 | -49.0000000000 | -5.0000000000 | | NULL |
|16777215 |
| 143 | 16 | -4.9000000000 | -0.1000000000 | | NULL |
|15774720 |
| 146 | 16 | 0.0000000000 | 1.9000000000 | | 0 |
|16769024 |
| 88 | 16 | 2.0000000000 | 49.0000000000 | | NULL |
|16777215 |
+-----+-------------+----------------+---------------+-----------+-----------+-----------+
4 rows in set (0.01 sec)
mysql> select * from dataformats where minvalue(=1 and maxvalue)=1 and datatype_id=16;
+-----+-------------+--------------+--------------+-----------+-----------+-----------+
| id | datatype_id | minvalue | maxvalue | valuename | forecolor | backcolor |
+-----+-------------+--------------+--------------+-----------+-----------+-----------+
| 146 | 16 | 0.0000000000 | 1.9000000000 | | 0 | 16769024 |
+-----+-------------+--------------+--------------+-----------+-----------+-----------+
1 row in set (0.01 sec)
mysql> select * from dataformats where minvalue(=1 and maxvalue)=-1 and datatype_id=16;
+-----+-------------+---------------+---------------+-----------+-----------+-----------+
| id | datatype_id | minvalue | maxvalue | valuename | forecolor |
|backcolor |
+-----+-------------+---------------+---------------+-----------+-----------+-----------+
| 143 | 16 | -4.9000000000 | -0.1000000000 | | NULL |
|15774720 |
| 146 | 16 | 0.0000000000 | 1.9000000000 | | 0 |
|16769024 |
+-----+-------------+---------------+---------------+-----------+-----------+-----------+
2 rows in set (0.00 sec)
mysql> select * from dataformats where minvalue(=-1 and maxvalue)=-1 and
datatype_id=16;
Empty set (0.00 sec)
mysql>
BUG! Here is something wrong, because afaik -4.9 is less than -1 and -0.1 is more than
-1 .. but no records returned
>Originator: [EMAIL PROTECTED]
>Organization:
Tradenet Systems, Ltd.
>MySQL support: none
>Synopsis: select ill-behaviour
>Severity: critical
>Priority: high
>Category: mysql
>Class: sw-bug
>Release: mysql-3.22.27 (Source distribution)
>Server: /usr/local/bin/mysqladmin Ver 7.11 Distrib 3.22.27, for pc-linux-gnu on i686
TCX Datakonsult AB, by Monty
Server version 3.22.27-log
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /tmp/mysql.sock
Uptime: 17 days 16 hours 55 sec
Threads: 1 Questions: 30665 Slow queries: 30 Opens: 99 Flush tables: 1 Open
tables: 63
>Environment:
System: Linux intranet.internal 2.2.16 #4 SMP Thu Dec 28 12:37:11 GMT-2 2000 i586
unknown
Architecture: i586
Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc
GCC: Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/egcs-2.91.66/specs
gcc version egcs-2.91.66 19990314/Linux (egcs-1.1.2 release)
Compilation info: CC='gcc' CFLAGS='' CXX='c++' CXXFLAGS='' LDFLAGS=''
Configure command: ./configure
Perl: This is perl, version 5.005_03 built for i386-linux
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
