From: mayuran [mailto:[EMAIL PROTECTED] > Here is my question: > > The following query returns incorrect rows and I dont understand why. > > mysql> SELECT * FROM testing WHERE MONTH(date) = (MONTH(NOW()) OR > MONTH(NOW())-1); > +------------+ > | date | > +------------+ > | 2004-01-01 | > +------------+ > > I wanted the query to return the rows whose months are from > this month or last month. > > This query however, returns the correct rows: > mysql> SELECT * FROM testing WHERE MONTH(date) = MONTH(now()) OR > MONTH(date) = MONTH(NOW())-1; > +------------+ > | date | > +------------+ > | 2004-04-10 | > | 2004-04-15 | > +------------+ > > Why does the first one not work? its shorter to type :)
The first one, while it may look valid, isn't. `MONTH(NOW())' currently evaluates to 5. `MONTH(NOW())-1' currently evaluates to 4. As such, your first query is essentially the following: SELECT * FROM testing WHERE MONTH(date) = (5 OR 4); Which becomes: SELECT * FROM testing WHERE MONTH(date) = 1; `(MONTH(NOW()) OR MONTH(NOW())-1)' aka `(5 OR 4)' evaluates to 1, because it's just ORing two integers. Note that it's returning exactly what it's supposed to be returning, the date in January. So while it may look like it makes sense in pseudo-code, on paper it's not how MySQL evaluates things. It builds the right side of the equality and then compares. Stick with your second query and you'll be fine, even if it is more typing. ;) HTH! -- Mike Johnson Web Developer Smarter Living, Inc. phone (617) 886-5539 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
