CODEWITHSUNDEEP
sqlpostgresql
Lee Eather
Lee Eather

Reputation: 355

My sql HAVING clause doesnt seems to run when my `WHERE CASE WHEN` evaluates to false

I have a statement where it works if I remove my CASE WHEN 1 != 1 condition. I get microposts back that have m.content = '<p>hi</p>' and tags that have ARRAY['hello', 'gday'] in them.

My knowledge is that my HAVING clause should still run and return microposts with matching tags when the WHERE CASE WHEN condition evaluates to false IE it doesnt filter by m.content = '<p>hi</p>', and return microposts that have ARRAY['hello', 'gday'] tags associated with them.

But this is not the case, when my WHERE condition doesn't run because the CASE condition evaluates to false it seems HAVING also doesn't run, and I get returned 0 results. Is this expected behavior and if so why??

SELECT m.* 
FROM microposts AS m 
JOIN taggings tt ON m.id = tt.taggable_id 
JOIN tags t ON t.id = tt.tag_id 
WHERE CASE WHEN 1 != 1 THEN m.content = '<p>hi</p>' END\
GROUP BY m.id 
HAVING ARRAY_AGG(t.name ORDER BY t.name)::text[] @> ARRAY['hello', 'gday']

EDIT Actual code with ruby string interpolation

SELECT m.* 
FROM microposts AS m 
JOIN taggings tt ON m.id = tt.taggable_id 
JOIN tags t ON t.id = tt.tag_id 
WHERE CASE WHEN #{my_string} != '' THEN m.content = #{my_string} END
GROUP BY m.id 
HAVING ARRAY_AGG(t.name ORDER BY t.name)::text[] @> ARRAY['hello', 'gday']

Upvotes: 1

Views: 50

Answers (2)

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

Where clause simply goes with :

where (1 != 1 and m.content = '<p>hi</p>')

However, first condition would never return true (1 != 1). So, you can simplify with this :

where (m.content = '<p>hi</p>')

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269445

If you were to use case, then the logic would be:

WHERE CASE WHEN 1 != 1 THEN m.content = '<p>hi</p>' ELSE true END

That is, you need to explicitly return true. Otherwise the CASE expressions returns NULL, which is treated as "false" in a WHERE clause.

However, I don't recommend a CASE expression for this. Instad:

WHERE (1 <> 1 AND m.content = '<p>hi</p>') OR
      (1 = 1)

Of course, this can be simplified, but I assume that the 1 <> 1 is due to parameter substitution.

Upvotes: 1

Related Questions