Bug #11606 Duplicate key error in SELECT query
Submitted: 28 Jun 2005 9:53 Modified: 28 Jul 2005 17:10
Reporter: Aron Steg Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S2 (Serious)
Version:4.1.11-standard-log OS:Linux (Fedora Core 2)
Assigned to: CPU Architecture:Any

[28 Jun 2005 9:53] Aron Steg 
Description:
This same error was reported in v5nt in bug #10292.

I am trying to execute a reasonably large query on a few tables which generates about 25,000 records. If I use LIMIT 100 it works fine. If I use LIMIT 50000 it works fine. If I use no limit I get a duplicate key error message.

Why would a select statement ever cause a duplicate key error?

How to repeat:
I am not really sure. My tables are too large and complex to include here, but whoever resolved it in v5nt should be able to repeat it.

Suggested fix:
No idea.
[28 Jun 2005 9:57] Aron Steg 
Sorry I meant to include the query ...

 select address, provider, co.id as libraryid, co.name as libraryname, sum(st.lastmessagesent < cm.messageid) as 'remaining'
                  from pss_subscription st
                                  left join pss_subscriber su on st.subscriberid = su.id
                                  left join pss_channel ch on st.channelid = ch.id
                                  left join pss_content co on ch.contentlib = co.id
                          left join pss_content_rules cr on co.id = cr.contentid
                                  left join pss_content_message cm on cm.contentid = co.id
                  where st.active=1
                    and ch.active=1
                        and co.active=1
                        and cr.ruletype = 1
                        and cr.parameters = 2
                  group by st.id
                  having remaining <= 2
                  limit 50000
[28 Jun 2005 17:10] MySQL Verification Team 
Could you please test against 4.1.12 or provide us with a test
case with table definition and data for to run the query.

You can upload the dump file at:

ftp://ftp.mysql.com/pub/mysql/upload/

with a name identifying this bug report.

Thanks in advance.
[28 Jul 2005 23:00] Bugs System 
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".