WHERE with several ANDed conditions filters as expected but using just one of them unexpectedly returns all rows

Question

I tried to return some of the rows in table service:

SELECT ps.post_name as post, c.cname, p.place, s.date_from,
    s.date_to, e.emp_name as employee, s.court, s.place
FROM `service` s 
INNER JOIN `employee` e ON s.empno = e.empno
INNER JOIN `posts` ps ON s.post = ps.code
INNER JOIN `court` c ON s.court = c.code
INNER JOIN `place` p ON s.place = p.code
WHERE s.court=13 AND s.place=7 AND s.date_to='0000-00-00'
ORDER BY s.post

This works with results as expected with various values. But when I remove s.court=13 and s.place=7 and have WHERE s.date_to='0000-00-00' it fails and shows all the records in service. I tried various ways, including with inner join and left join, but in vain.

What is wrong?

Answer 1

check your sql_mode: SELECT @@sql_mode;

especially for NO_ZERO_DATE, which affects how MySQL interprets ‘0000-00-00’ dates

Change '0000 00 00' to other non zero data