Hi, John : I don't see any connection between corereader and this known bug in MySQL-4.0.4 ??
Bill : AFAIK, this problem has been fixed in MySQL-4.0.5, just take a look at the changelog : "Fixed a newly introduced bug that caused ORDER BY ... LIMIT # to not return all rows. " http://www.mysql.com/doc/en/News-4.0.5.html Regards, Jocelyn ----- Original Message ----- From: "John Ragan" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]>; "Bill Marrs" <[EMAIL PROTECTED]> Sent: Tuesday, November 26, 2002 9:37 PM Subject: Re: join with OR and LIMIT fails to return result > > interesting. > > of course, removing one of the limiting selection > criteria would increase the number of possible > selected records. > > and the limit clause does not seem to be > correctly constucted. > > but forgive me if i just don't understand. > > if you have a windows box for a front end, > download corereader. it will let you quickly > experiment with point-and-click queries to debug > query logic. http://corereader.com > > > > >Description: > > join with OR and LIMIT fails to return result > > >How-To-Repeat: > > > > This query fails to return a result: > > > > SELECT Subscriptions.UID > FROM Subscriptions, Accounts > WHERE Accounts.UID = Subscriptions.User > AND (Accounts.Email = 'bill' OR > Accounts.UName = 'bill') > ORDER BY Subscriptions.Created DESC limit 1; > Empty set (0.02 sec) > > > > But, if I remove one of the parenthesized OR tokens, it works: > > > > SELECT Subscriptions.UID > FROM Subscriptions, Accounts > WHERE Accounts.UID = Subscriptions.User > AND Accounts.UName = 'bill' > ORDER BY Subscriptions.Created DESC limit 1; > > +-----+ > ID | > > +-----+ > > | 255 | > > +-----+ > > 1 row in set (0.00 sec) > > > > Or, if I remove the LIMIT, it works: > > > > mysql> SELECT Subscriptions.UID FROM Subscriptions, Accounts WHERE > > Accounts.UID = Subscriptions.User AND (Accounts.Email = 'bill' OR > > Accounts.UName = 'bill') ORDER BY Subscriptions.Created DESC; > > +-----+ > > | UID | > > +-----+ > > | 255 | > > +-----+ > > 1 row in set (0.11 sec > > > > I've already worked around this, I'm just letting you guys know. > > > > > > >Fix: > > > > > -- > John Ragan > [EMAIL PROTECTED] > http://www.CoreReader.com/ > > > --------------------------------------------------------------------- > Before posting, please check: > http://www.mysql.com/manual.php (the manual) > http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > > > --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
