Details
Minor Description
I have encountered a logic error where the HAVING clause fails to filter rows correctly. A query involving COUNT(), NOT LIKE, and IS NOT TRUE returns 3 rows, whereas it is expected to return an empty set.
Evaluating the exact same expression in the SELECT list shows that the condition evaluates to 0 (FALSE) for all groups, yet the HAVING clause allows these groups to pass through.
How to repeat:
-- 1. Create table and insert data
|
CREATE TABLE t1(c1 NUMERIC); |
INSERT INTO t1(c1) VALUES (54559273),(-70416),(-9762545),(1),(1); |
 |
-- 2. Execute the problematic query
|
SELECT MAX(ca2) |
FROM (SELECT c1 AS ca1, c1 AS ca2 FROM t1) AS ta1 |
GROUP BY ca1 |
HAVING (COUNT(ca2) NOT LIKE (ca1)) IS NOT TRUE; |
+----------+ |
| MAX(ca2) | |
+----------+ |
| -9762545 |
|
| -70416 |
|
| 54559273 |
|
+----------+ |
3 rows in set |
Analysis & Evidence: The HAVING condition is (COUNT(ca2) NOT LIKE (ca1)) IS NOT TRUE
SELECT |
ca1,
|
COUNT(ca2), |
((COUNT(ca2) NOT LIKE (ca1)) IS NOT TRUE) AS condition_result |
FROM (SELECT c1 AS ca1, c1 AS ca2 FROM t1) AS ta1 |
GROUP BY ca1; |
+----------+------------+------------------+ |
| ca1 | COUNT(ca2) | condition_result | |
+----------+------------+------------------+ |
| -9762545 | 1 | 0 |
|
| -70416 | 1 | 0 |
|
| 1 | 2 | 0 |
|
| 54559273 | 1 | 0 |
|
+----------+------------+------------------+ |