Bug #6525 query with index give wrong result
Submitted: 9 Nov 2004 16:21 Modified: 9 Nov 2004 17:52
Reporter: Andreas Sakowski Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server Severity:S1 (Critical)
Version:4.1.7 OS:Linux (Linux)
Assigned to: CPU Architecture:Any

[9 Nov 2004 16:21] Andreas Sakowski
Description:
Hello,

if i use an index in a join-query i get a wrong result. This seems to be
depend on the data. With the data below this problem can repeat.

use test ;

create table offeneposten (
    kontonummer int
    , offenerbetrag int
    , geloeschtid int ) ;

Insert as described below.

SELECT
    t1.kontonummer
    , t1.offenerbetrag
    , t1.geloeschtid
    , t2.kontonummer
    , t2.offenerbetrag
    , t2.geloeschtid
FROM offeneposten AS t1
LEFT JOIN offeneposten AS t2 ON
    t2.kontonummer = t1.kontonummer
    AND t2.offenerbetrag <0
    AND t2.geloeschtid <=0
WHERE
    t1.offenerbetrag >0
    AND t2.offenerbetrag IS NOT NULL
    AND t1.geloeschtid <=0 ;

result as expects:

70005 |    30420 |      0 |  70005 |   -49089 |      0 |
70005 |    18669 |      0 |  70005 |   -49089 |      0 |

alter table offeneposten
    add key ( kontonummer , offenerbetrag )
    , add key ( offenerbetrag ) ;

select as before:

The result are 28 rows.

Andreas Sakowski

INSERT INTO `offeneposten` VALUES (73405, 0, 0),
(70002, 0, 0),
(70002, 0, 0),
(70002, 0, 0),
(70002, 0, 0),
(70002, 0, 0),
(70005, 0, 0),
(70002, 0, 0),
(70002, 0, 0),
(70005, 0, 0),
(70002, 0, 0),
(70002, 0, 0),
(70002, 0, 0),
(70002, 0, 0),
(70002, 0, 0),
(70002, 0, 0),
(70002, 0, 0),
(70002, 0, 0),
(73405, 0, 0),
(70002, 0, 0),
(70002, 0, 0),
(70002, 0, 0),
(70002, 0, 0),
(70002, 0, 0),
(70002, 0, 0),
(70005, 0, 0),
(70002, 0, 0),
(70002, 0, 0),
(70002, 0, 0),
(70002, 0, 0),
(70002, 0, 0),
(70005, 0, 0),
(70005, 0, 0),
(70005, 0, 0),
(70005, 0, 0),
(73405, 0, 0),
(71400, 0, 0),
(70002, 0, 0),
(70002, 0, 0),
(70002, 0, 0),
(70002, 0, 0),
(70005, 0, 0),
(73405, -207207, 23),
(71400, 0, 0),
(70002, 0, 0),
(70002, 0, 0),
(70002, 0, 0),
(70002, 0, 0),
(70005, 0, 0),
(70005, 0, 0),
(70005, 0, 0),
(70005, 0, 0),
(70002, 0, 0),
(70002, 0, 0),
(70002, 0, 0),
(70005, 0, 0),
(70005, 0, 0),
(70005, 0, 0),
(70005, 0, 0),
(70002, 0, 0),
(70002, 0, 0),
(70002, 0, 0),
(70002, 0, 0),
(70002, 0, 0),
(70002, 0, 0),
(70002, 0, 0),
(70002, 0, 0),
(70002, 0, 0),
(70002, 0, 0),
(70002, 0, 0),
(70002, 0, 0),
(70002, 0, 0),
(70002, 0, 0),
(70002, 0, 0),
(70002, 0, 0),
(70002, 0, 0),
(70002, 0, 0),
(70002, 0, 0),
(70002, 14416, 27),
(73405, 0, 0),
(70002, 0, 0),
(70002, 0, 0),
(70002, 0, 0),
(70002, 0, 0),
(70002, 0, 0),
(70002, 0, 0),
(70002, 0, 0),
(70002, 0, 0),
(70002, 0, 0),
(70005, 0, 0),
(70005, 0, 0),
(70005, 0, 0),
(70002, 0, 0),
(70002, 0, 0),
(70002, 0, 0),
(70002, 0, 0),
(70002, 0, 0),
(73405, 0, 0),
(70005, 0, 0),
(70005, 0, 0),
(70002, 0, 0),
(70002, 5554000, 33),
(70002, 5547600, 34),
(70002, 0, 0),
(70002, 0, 0),
(70002, 0, 0),
(70002, 0, 0),
(70002, 0, 0),
(70005, 0, 0),
(70005, 0, 0),
(70005, 0, 0),
(70002, 0, 0),
(70002, 0, 0),
(70002, 0, 0),
(70002, 0, 0),
(70005, 0, 0),
(70005, 0, 0),
(70005, 0, 0),
(70002, 0, 0),
(70005, 0, 0),
(70005, 0, 0),
(70002, 0, 0),
(70002, 0, 0),
(70002, 0, 0),
(70005, 0, 0),
(70005, 0, 0),
(70005, 0, 0),
(70005, 0, 0),
(70002, 0, 0),
(70002, 0, 0),
(70005, 2763, 43),
(70002, 0, 0),
(70005, 0, 0),
(70005, 0, 0),
(70005, 0, 0),
(70002, 0, 0),
(73405, 0, 0),
(70002, 0, 0),
(70002, 0, 0),
(70005, 0, 0),
(70002, 0, 0),
(70005, 0, 0),
(70002, 0, 0),
(70005, 0, 0),
(73405, 0, 0),
(70005, 0, 0),
(70005, 0, 0),
(70005, 0, 0),
(70002, 0, 0),
(70002, 0, 0),
(70002, 0, 0),
(70002, 0, 0),
(70002, 0, 0),
(70005, 0, 0),
(70005, 0, 0),
(70005, 0, 0),
(70005, 0, 0),
(70002, 0, 0),
(70002, 0, 0),
(70002, 0, 0),
(70002, 0, 0),
(70002, 0, 0),
(70002, 0, 0),
(70002, 0, 0),
(70002, 0, 0),
(73405, 0, 0),
(70002, 0, 0),
(70002, 0, 0),
(70002, 0, 0),
(70002, 0, 0),
(70002, 0, 0),
(70002, 0, 0),
(70002, 0, 0),
(70002, 0, 0),
(70002, 0, 0),
(70002, 0, 0),
(70002, 0, 0),
(70002, 0, 0),
(70002, 0, 0),
(73405, 0, 0),
(71400, 1857, 0),
(71400, 21392, 0),
(70002, 0, 0),
(70002, 0, 0),
(70002, 0, 0),
(70002, 0, 0),
(70002, 0, 0),
(70002, 0, 0),
(70002, 0, 0),
(70002, 0, 0),
(70002, 0, 0),
(70002, 0, 0),
(70002, 0, 0),
(70002, 0, 0),
(70002, 0, 0),
(70002, 0, 0),
(70002, 0, 0),
(70002, 0, 0),
(70002, 0, 0),
(70002, 0, 0),
(70002, 0, 0),
(70002, 0, 0),
(70002, 0, 0),
(70002, 0, 0),
(71400, 22382, 0),
(71400, 6339, 0),
(71400, 31284, 0),
(70005, 0, 0),
(70005, 0, 0),
(70005, 0, 0),
(70002, 0, 0),
(70005, 0, 0),
(70002, 0, 0),
(70005, 0, 0),
(70005, 15039, 57),
(70005, 0, 0),
(70005, 0, 0),
(70002, 0, 0),
(70005, 0, 0),
(73405, 0, 0),
(70005, 0, 0),
(70002, 26010, 0),
(70002, 3482, 0),
(70005, -49089, 0),
(70002, 5867, 0),
(70002, 11586, 0),
(70002, 8780, 0),
(70002, 1955, 0),
(70002, 1668, 0),
(70005, 30420, 0),
(70005, 18669, 0);

How to repeat:
see above
[9 Nov 2004 17:52] MySQL Verification Team
Hi,

Thank you for the report, but I wasn't able to repeat it with 4.1.8-debug-log.

mysql> alter table offeneposten
    ->     add key ( kontonummer , offenerbetrag )
    ->     , add key ( offenerbetrag ) ;
Query OK, 231 rows affected (0.11 sec)
Records: 231  Duplicates: 0  Warnings: 0

mysql>
mysql>
mysql> SELECT
    ->     t1.kontonummer
    ->     , t1.offenerbetrag
    ->     , t1.geloeschtid
    ->     , t2.kontonummer
    ->     , t2.offenerbetrag
    ->     , t2.geloeschtid
    -> FROM offeneposten AS t1
    -> LEFT JOIN offeneposten AS t2 ON
    ->     t2.kontonummer = t1.kontonummer
    ->     AND t2.offenerbetrag <0
    ->     AND t2.geloeschtid <=0
    -> WHERE
    ->     t1.offenerbetrag >0
    ->     AND t2.offenerbetrag IS NOT NULL
    ->     AND t1.geloeschtid <=0 ;
+-------------+---------------+-------------+-------------+---------------+-------------+
| kontonummer | offenerbetrag | geloeschtid | kontonummer | offenerbetrag | geloeschtid |
+-------------+---------------+-------------+-------------+---------------+-------------+
|       70005 |         30420 |           0 |       70005 |        -49089 |           0 |
|       70005 |         18669 |           0 |       70005 |        -49089 |           0 |
+-------------+---------------+-------------+-------------+---------------+-------------+
2 rows in set (0.00 sec)
[18 Nov 2004 13:06] Andreas Sakowski
Hi,

i have made the test again with 4.1.8. There is no problem as you have written.
But in 4.1.7 there it is still a problem. And i think, it is a big problem. So my question: when 4.1.8 will be released?

Andreas Sakowski