Problem sa dupliranjem nekih rezultata u SELECT DISTINCT ... LEFT JOIN upitu

[IcecreamMan]

Pozdrav,

Imam sledeci problem,
SELECT DISTINCT upit sa vise LEFT JOIN -a duplira neke rezultate
na verziji mysql -a 3.23.58, dok na verziji 3.23.56 radi ocekivano (kako bi ja hteo )

Evo upita:
SELECT DISTINCT (cs_update_queries.reference_number)
FROM cs_update_queries, cs_update_draft_stage, cs_listing_numbers, cs_personal_info, cs_personal_info a

LEFT JOIN cs_listing_info ON cs_listing_info.property_id = cs_update_queries.property_id
LEFT JOIN cs_rental_info ON cs_rental_info.property_id = cs_update_queries.property_id
LEFT JOIN cs_appraisal_info ON cs_appraisal_info.property_id = cs_update_queries.property_id
LEFT JOIN cs_personal_info l ON l.person_id = cs_listing_info.assigned_to
LEFT JOIN cs_personal_info r ON r.person_id = cs_rental_info.assigned_to
LEFT JOIN cs_personal_info ap ON ap.person_id = cs_appraisal_info.assigned_to

WHERE cs_update_queries.completed = '0'
AND cs_update_queries.reference_number = cs_update_draft_stage.reference_number
AND cs_update_queries.property_id = cs_listing_numbers.listing_id
AND cs_update_queries.submitter_id = cs_personal_info.person_id
AND a.person_id
IN ( 0, 11837, 11668, 11658, 11921, 11652, 11675, 16580, 11665, 12485, 11679, 11672, 11666, 11688 ) AND ((l.person_id = a.person_id OR cs_listing_info.assigned_to = 0)
OR (r.person_id = a.person_id OR cs_rental_info.assigned_to = 0)
OR (ap.person_id = a.person_id OR cs_appraisal_info.assigned_to = 0))
AND cs_update_draft_stage.status = 'waiting'
AND (field_name
NOT IN ('assigned_to', 'present_status', 'listing_price', 'date_sold', 'selling_price', 'buyer_id', 'price_text_id', 'rental_status')
AND table_name <> 'cs_agency_info'
AND (field_name <> 'status' OR table_name <> 'cs_listing_info'))
AND office_id IN ( 0, 2, 1, 8, 6 )

ORDER BY cs_update_queries.date
LIMIT 10 , 10

ovo su rezultati na 3.23.56: nema duplikata
11665_20051220085047
11688_20051230162313
11665_20060106140929
11837_20060125114140
16580_20060125144715
11672_20060125162818
11672_20060126143555
11672_20060127121658
11672_20060127125540
11672_20060127130022

ovo su rezultati na 3.23.58:
11672_20060127131244
11672_20060130103227
11672_20060130104337

11672_20060130104643
11672_20060130104643

11672_20060130104908

11672_20060130111706
11672_20060130111706
11672_20060130111706

11675_20060201104657

Jel' ima neko ideju zasto se ovo desava?
Inace, na prvoj strani LIMIT 0, 10 nema ponavljanja.

[Milos Vukotic]

Ovako, na brzinu: Vidi duplira li kad izbacis "ORDER BY" iz upita. Ako ti daje rezultat kako treba, vidi ovo:
http://dev.mysql.com/doc/refman/5.0/...imization.html

DISTINCT combined with ORDER BY needs a temporary table in many cases.

[IcecreamMan]

Hvala puno,
nema duplikata kad izbacim ORDER BY, pogledacu dati link.

[IcecreamMan]

Pogledao sam link, DISTINCT se smatra nekom vrstom specijalnog slucaja GROUP BY,
da bi zadrzali sortiranje, u upit se dodaje GROUP BY po DISTINCT vrednosti, a zatim ORDER BY.

upit konacno izgleda ovako:
SELECT DISTINCT (cs_update_queries.reference_number)
FROM cs_update_queries, cs_update_draft_stage, cs_listing_numbers, cs_personal_info, cs_personal_info a

LEFT JOIN cs_listing_info ON cs_listing_info.property_id = cs_update_queries.property_id
LEFT JOIN cs_rental_info ON cs_rental_info.property_id = cs_update_queries.property_id
LEFT JOIN cs_appraisal_info ON cs_appraisal_info.property_id = cs_update_queries.property_id
LEFT JOIN cs_personal_info l ON l.person_id = cs_listing_info.assigned_to
LEFT JOIN cs_personal_info r ON r.person_id = cs_rental_info.assigned_to
LEFT JOIN cs_personal_info ap ON ap.person_id = cs_appraisal_info.assigned_to

WHERE cs_update_queries.completed = '0'
AND cs_update_queries.reference_number = cs_update_draft_stage.reference_number
AND cs_update_queries.property_id = cs_listing_numbers.listing_id
AND cs_update_queries.submitter_id = cs_personal_info.person_id
AND a.person_id
IN ( 0, 11837, 11668, 11658, 11921, 11652, 11675, 16580, 11665, 12485, 11679, 11672, 11666, 11688 ) AND ((l.person_id = a.person_id OR cs_listing_info.assigned_to = 0)
OR (r.person_id = a.person_id OR cs_rental_info.assigned_to = 0)
OR (ap.person_id = a.person_id OR cs_appraisal_info.assigned_to = 0))
AND cs_update_draft_stage.status = 'waiting'
AND (field_name
NOT IN ('assigned_to', 'present_status', 'listing_price', 'date_sold', 'selling_price', 'buyer_id', 'price_text_id', 'rental_status')
AND table_name <> 'cs_agency_info'
AND (field_name <> 'status' OR table_name <> 'cs_listing_info'))
AND office_id IN ( 0, 2, 1, 8, 6 )

GROUP BY cs_update_queries.reference_number
ORDER BY cs_update_queries.date
LIMIT 10 , 10

Milose, hvala na ultra brzom odgovoru