What is the difference between these 3 queries? WHERE & HAVING - mysql version?

Question

What is the difference between the 3 queries I have here?

The first version works on my localhost xampp mysql database (10.1.9-MariaDB). However it retrieves 0 results with my online test server mysql database (5.7.9-log MySQL Community Server GPL).

The 2nd version retrieves the expected results with both servers. But on my localhost query 2 retrieves the same result set albeit in a different order, but i'm guessing that's because I don't have an order by.

Is this an issue with the different version of mysql? and how, if at all are my queries different?

Query 1

SELECT 
    s.eid, e.fname, e.lname, s.id, s.starttime, s.finishtime
FROM
    employees AS e
        RIGHT JOIN
    scheduling_shifts AS s USING (eid)
GROUP BY s.id
HAVING COUNT(s.date = '2016-01-15' OR NULL) > 0
    AND COUNT(s.eid = '1' OR NULL) = 0;

Query2

SELECT 
    s.eid, e.fname, e.lname, s.id, s.starttime, s.finishtime
FROM
    employees AS e
        RIGHT JOIN
    scheduling_shifts AS s USING (eid)
WHERE
    s.date = '2016-01-15'
GROUP BY s.id
HAVING COUNT(s.eid = '1' OR NULL) = 0;

EDIT:: Query3

This query also retrieves the correct result set minus the NULL s.eid's

    SELECT 
    s.eid, e.fname, e.lname, s.id, s.starttime, s.finishtime
FROM
    employees AS e
        RIGHT JOIN
    scheduling_shifts AS s USING (eid)
WHERE
    s.date = '2016-01-15' AND s.eid <> '1'

EDIT:: Here is a test case specific to my own query on SQLfiddle

The objective is to fetch a list of possible swaps for the selected employee on the selected date. Our selected date is the 15th Jan 2016, and selected employee who we want to swap his shift is employee 1. The list cannot fetch his own shift, because he cannot swap shifts with himself. But it should bring up the unassigned shifts. (shifts which exist where the eid is NULL). So the option is bring up a list of other employees and their shifts, OR unassigned shifts on this day which we can swap ours with.

Notice that Query 1 - does produce the correct results on this, did produce the correct result on xampp (mariadb 10), but didn't on my online test server.

Query 2 also fetches the correct result, and also did both xampp and online test server.

Query 3 fetches all the shifts that are assigned, but not the unassigned ones.

Hope this explains my question a bit better.

Answer 1

The WHERE clause considers conditions per row, the HAVING clause considers aggregations, but I do not believe that is the cause of the differences.

Your use of "OR NULL" is misleading and will at some point (if not now) produce unexpected or incorrect results, and please note that COUNT() only increments when an expression is NON-NULL.

Below, using MySQL 5.6 at SQL Fiddle, I attempt to display the effect:

CREATE TABLE Table1
    (`ID` int, `Other` varchar(4))
;

INSERT INTO Table1
    (`ID`, `Other`)
VALUES
    (1, 'ABC'),
    (2, 'DEF'),
    (3, NULL)
;

Query 1:

SELECT COUNT(`other` > '0' OR NULL)
FROM Table1

Results:

| COUNT(OTHER > '0' OR NULL) |
|----------------------------|
|                          2 |

Query 2:

SELECT COUNT(`other` > '0' OR `other` IS NULL)
FROM Table1

Results:

| COUNT(`other` > '0' OR `other` IS NULL) |
|-----------------------------------------|
|                                       3 |

In my Query1 above you can see that "OR NULL" is NOT being counted but in my Query2 the expression returns true for all rows hence all rows get counted.

"OR NULL" is a constant NULL and COUNT() does not increment for NULLs

[EDIT] I suggest you use COUNT(*) as it appears all you want to do is count every row, that's exactly what COUNT(*) does is does not access every column, all it cares about is 'does the row exist'.

[EDIT 2] by the way...

Comparison of NULL via <> (not equal) does NOT work. NULL cannot be equal to anything, so it is also impossible to know if it is not equal (because you must know if it is equal before it is not equal)

Look in articular at columns y & z in the following result:

   SELECT
          COUNT(OTHER > '0' OR NULL) orig
        , COUNT(`other` > '0' OR `other` IS NULL) x
        , count(case when `other` <> 'abc' then NULL else 1 end) y
        , count(case when `other` <> 'abc' or `other` is null then NULL else 1 end) z
    FROM Table1

Results:

| orig | x | y | z |
|------|---|---|---|
|    2 | 3 | 2 | 1 |

---

AS a sqlfiddle test case is available, this how I would structure the query. It lists 8 of the 9 rows in the provided table scheduling_shifts:

SELECT 
        s.eid, e.fname, e.lname, s.id, s.date, s.starttime, s.finishtime
FROM scheduling_shifts s
      LEFT JOIN employees AS e on s.eid = e.eid
WHERE (s.eid <> 1 OR s.eid is null)
      AND s.date = '2016-01-15';

As I cannot reproduce your specific issue (different results from different platforms) I regret to say that only your use of "or NULL" strikes me as being unusual and likely to produce unpredictable results. As a secondary feature of your queries is that you are using a having clause when there is no particular reason to do so, which I would also avoid.