MySQL Bugs: #13489: DECIMAL, Result sets of Select via Index or Table scan differ, Downgrade

Bug #13489	DECIMAL, Result sets of Select via Index or Table scan differ, Downgrade
Submitted:	26 Sep 2005 16:29	Modified:	23 Nov 2005 13:36
Reporter:	Matthias Leich	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	4.1	OS:	Linux (Linux)
Assigned to:	Alexey Botchkov	CPU Architecture:	Any

Description:
Short snip of my protocol file:
   column t1_2.my_decimal_64 is of data type decimal(64,30)
      and member of three indexes    (aux1 and aux2 are BIGINT)
   KEY `I1_my_decimal_64_1_2` (`my_decimal_64`),
   KEY `I2_my_decimal_64_1_2` (`my_decimal_64`,`aux1`),
   KEY `I3_my_decimal_64_1_2` (`aux2`,`my_decimal_64`)

SELECT my_decimal_64
FROM t1_2 FORCE INDEX(I1_my_decimal_64_1_2)
WHERE my_decimal_64 = 9999999999999999999999999999999999.999999999999999999999999999999 ;
my_decimal_64
10000000000000001761595319084122112.000000000000000000000000000000
SELECT my_decimal_64
FROM t1_2 FORCE INDEX(I2_my_decimal_64_1_2)
WHERE my_decimal_64 = 9999999999999999999999999999999999.999999999999999999999999999999 ;
my_decimal_64
10000000000000001761595319084122112.000000000000000000000000000000
SELECT my_decimal_64
FROM t1_2 FORCE INDEX(I3_my_decimal_64_1_2)
WHERE my_decimal_64 = 9999999999999999999999999999999999.999999999999999999999999999999 ;
my_decimal_64
10000000000000000608673814477275136.000000000000000000000000000000
10000000000000001761595319084122112.000000000000000000000000000000
      <===Attention: The SELECT via INDEX(I3_my_decimal_64_1_2)
                      gives two rows and not one like the other indices.
DROP INDEX I1_my_decimal_64_1_2 ON t1_2;
DROP INDEX I2_my_decimal_64_1_2 ON t1_2;
DROP INDEX I3_my_decimal_64_1_2 ON t1_2;
SELECT my_decimal_64
FROM t1_2
WHERE my_decimal_64 = 9999999999999999999999999999999999.999999999999999999999999999999 ;
my_decimal_64
10000000000000001761595319084122112.000000000000000000000000000000
10000000000000000608673814477275136.000000000000000000000000000000
        <=== SELECT without any indexes gives two records

1. It is already documented that DECIMAL/NUMERIC are not very 
    accurate in MySQL 4.1. 
2. I assume it is also known that it is much better to use SELECTS in 
    the style of
       SELECT my_decimal_64
       FROM t1_2 FORCE INDEX(I3_my_decimal_64_1_2)
       WHERE my_decimal_64 BETWEEN 9999999999999999999999999999999999.999999999999999999999999999999 * 
           (1 - 1.0E-15)
AND 
9999999999999999999999999999999999.999999999999999999999999999999 * 
           (1 + 1.0E-15);
     or similar.  Maybe the function round is also usable.
3. The effect is independend of the storage engine.
3. But the really annoying fact is, that the the content of the 
    result set depends on wether an index is used or not.

MySQL 5.0 with precision math does not show this bad effect.

Customers 
- migrating their databases from MySQL 4.1 to MySQL 5.0
  and again back (via mysqldump) to MySQL 4.1     and
- using "strict" SELECTs with WHERE <decimal column> = <value>
might suffer from this bug.
I run into this problem during the development of the 
Upgrade/Downgrade testsuite.

My environment:
   - Intel PC with Linux(SuSE 9.3)
   - MySQL compiled from source
        Version 4.1 ChangeSet@1.2458, 2005-09-22
    
 

How to repeat:
Please use my attached testscript ml019.test
  copy it to mysql-test/t
  echo "Dummy" > r/ml019.result   # Produce a dummy file with 
                                                   # expected results
  ./mysql-test-run ml019
  inspect r/ml019.reject    # The protocol of the execution.

Suggested fix:
If there is a general problem within the comparison of index content
and constants, which harms also other data types or
something within MySQL 5.0, please fix this bug.
If the problem is only related to MySQL 4.1 and the data type 
DECIMAL, please set the priority to P4 and the status to 
"Won't fix" and maybe add a note to the manual, because 
1. MySQL 5.0 precision math fixes the problem
2. It is very rare that somebody hits this bug.

testscript

Attachment: ml019.test (application/test, text), 2.46 KiB.

I was unable to repeat because the test fails on my side
miguel@hegel:~/dbs/mysql-4.1> bk changes | head
ChangeSet@1.2453.1.2, 2005-09-26 16:49:18+02:00, lars@mysql.com
  Fixed gcc error on AMD64: cast from 'char*' to 'unsigned int' loses precision
I tried to test on Suse 9.3 X86.

TEST                            RESULT
-------------------------------------------------------
ml019                          [ fail ]

Errors are (from /home/miguel/dbs/mysql-4.1/mysql-test/var/log/mysqltest-time) :
mysqltest returned unexpected code 139, it has probably crashed

my test results

Attachment: ml019.reject (application/octet-stream, text), 3.98 KiB.

The result file ml019.reject was generated with
MySQL 4.1 last ChangeSet@1.2453.1.2, 2005-09-26

After a thorough revision of the results of the Upgrade/Downgrade testsuite
on UNIX and Windows I can provide the following additional details:
1. My MySQL 4.1 source distribution on Linux suffered from that problem.
2. The problem disappeared, when I executed the test on the official 
    (MySQL Network) binary distribution.
3. We have the same effect on Windows with the official ! (www.mysql.com)
    binary distribution.
4. Any comparison of a DECIMAL column with "=" to a DECIMAL/INTEGER
    constant can suffer from non matching rows. The existence of indices
    is not needed.
    Testcase failing on Linux (1.) and Windows (3.)
       CREATE TABLE t1_template (f1 DECIMAL(64,30), f2 BIGINT);
       INSERT INTO t1_template SET f1 = 500, f2 = 1;
       CREATE TABLE t1 LIKE t1_template;
       INSERT INTO t1 SELECT * FROM t1_template;
       CREATE INDEX idx1 ON t1(f1);
       SELECT f1 FROM t1;
       f1
       500.000000000000000000000000000000
       SELECT f1 FROM t1 FORCE INDEX(idx1) WHERE f1 = 500;
       f1
       SELECT f1 FROM t1 FORCE INDEX(idx1) WHERE f1 = 500.0;
       f1
       DROP INDEX idx1 ON t1;
       SELECT f1 FROM t1 WHERE f1 = 500;
       f1
       SELECT f1 FROM t1 WHERE f1 = 500.0;
       f1

Some proposals:
1. Please alter the bug title to something like 
    NUMERIC/DECIMAL, non matching rows, downgrade
    , because the existence of indices is not important.
    The problem can occur on any OS and not only Linux.
2. The manual chapter 
    A.5.7. Solving Problems with No Matching Rows
    Point 5.  should also contain DECIMAL/NUMERIC for MySQL versions older
                than 5.0.3
    A.5.8. Problems with Floating-Point Comparisons
             By my opinion the text (not the title) and the examples of this 
             chapter are a bit too much fixed on the effects of CALCULATIONs.
             Please add that even a simple comparison to a constant
             WHERE <decimal column> 
                     = <constant in INTEGER of DECIMAL notation like 500 or 500.0>
             might suffer from the problem.

Thank you for your bug report. This issue has been addressed in the
documentation. The updated documentation will appear on our website
shortly, and will be included in the next release of the relevant
product(s).

Additional info:

Updated 4.1 and 5.0 versions of Manual as suggested.