SQL ignoring where condition in having clause

Question

Actually SQL is ignoring the where condition in my having clauses. It doesn't matter what I fill in there. It always finds "all" results.

SELECT `status`, count(*) AS count
FROM `wa_re_jo`
WHERE
    EXISTS (
        SELECT *
        FROM `ad_ac_wa`
        WHERE `plattform` = 'adwords'
        AND `wa_re_jo`.`watchlist_id` = `ad_ac_wa`.`id`
        AND EXISTS (
            SELECT *
            FROM `ad_cu_ac`
            WHERE `ad_ac_wa`.`external_id` = `ad_cu_ac`.`externalId`
            AND EXISTS (
                SELECT *
                FROM `ç`
                WHERE `ad_cu_ac`.`id` = `ad_cu_ac`.`adwords_customer_account_id`
                AND `startDate` <= '2023-04-04'
                AND `endDate` + interval 1 DAY >= '2023-04-04'
            )
        )
    )
AND `created_at` LIKE '2023-04-04%'
OR `updated_at` LIKE '2023-04-04%'
GROUP BY `status`

I tried to change the values and the order of the conditions.

Not sure on your full structure or performance, but have you tried changing these to INNER JOINS? — Brad, Apr 04 '23 at 15:54
Which of the 4 (four) `WHERE`-clauses are you taking about, and which `HAVING`-clause (I do not find any one....) — Luuk, Apr 04 '23 at 16:02
Add parenthasis around the AND and OR to aid correct interpratation `( AND \`created_at\` LIKE '2023-04-04%' OR \`updated_at\` LIKE '2023-04-04%')` — RiggsFolly, Apr 04 '23 at 16:04
Do you store dates in character columns? Use a proper date/time data type instead. — jarlh, Apr 04 '23 at 17:05

Joel Coehoorn · Answer 1 · 2023-04-04T16:14:03.360

Separate from the fact there is no HAVING clause anywhere in the posted code, the problem is almost certainly the OR conditional expression:

AND `created_at` LIKE '2023-04-04%'
OR `updated_at` LIKE '2023-04-04%'

You probably want a set of parentheses, like this:

AND (`created_at` LIKE '2023-04-04%'
OR `updated_at` LIKE '2023-04-04%')

With the original code, matching just the right side of the OR clause was enough to match the entire WHERE clause. That is, it was as if you had written the WHERE clause like this:

WHERE
  (  EXISTS ( ...    )
      AND `created_at` LIKE '2023-04-04%'
   )
  OR `updated_at` LIKE '2023-04-04%'

I'll add that, thanks to cultural/internationalization issues, using string expressions to do date comparisons is pretty much always the slowest and most error-prone approach possible. You WILL get better and faster results by re-working those comparisons to use real date expressions, rather than the fake date expressions it's using right now.

Finally, when I see multiple levels of EXISTS() conditions like this, it's usually a sign something should be re-written to use a JOIN instead.

Would you mind expanding on what you mean by "real" and "fake" date expressions? I'm interested in seeing if I can improve my date filtering. — UncleCarl, Apr 04 '23 at 17:41
@UncleCarl "fake" is anything that treats date values as strings. For example, even if the question's `updated_at` field is stored as a datetime, the DB will have to convert that value to a string for the LIKE comparison. It will do that for **every row in the table**, and it won't be able to use any indexes to help. But if you instead use `updated_at >= '2023-04-04' AND updated_at < '2023-04-05'`, now the database is able to interpret those boundaries as date literals. No conversion happens at all on the values in the database, and it's able to use indexes. **MUCH** better performance. — Joel Coehoorn, Apr 04 '23 at 18:24

SQL ignoring where condition in having clause

1 Answers1