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