PDO prepared statement seems to ignore HAVING clause

Question

I've included a DB-fiddle, and you can adjust the input parameter accordingly. This returns how I would expect it to, and differs from the results I am seeing in PDO.

I have the following minified table-view and query:

CREATE TABLE `tagged` {
    `tag` SMALLINT(5) UNSIGNED NOT NULL
}

Table has an assortment of values, but you can use 1-10 for tags in the DB:

INSERT INTO tagged (tag) VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)

query:

SELECT tagged.tag,
    (@t := :tag),
    @t AS temp_var,
    (@t IS NULL OR FIND_IN_SET(tagged.tag, @t) > 0) AS is_match
FROM tagged
HAVING is_match = 1
LIMIT 150

This seems well and good when run in a client, command line, jdbc, etc. If I put in an input of '' or NULL, I get all results. Similarly an input of '1' yields only tags of 1, and an input of '1,4' would retrieve all tags with 1 or 4.

The way the query restricts these results is via the is_match = 1 in the HAVING clause. When run with PDO, the parameter seems to bind correctly but it completely ignores the condition in the clause:

Array
(
    [0] => stdClass Object
        (
            [tag] => 3
            [(@t := ?)] => 1,4
            [temp_var] => 1,4
            [is_match] => 0     ## should not have been returned
        )

    [1] => stdClass Object
        (
            [tag] => 4
            [(@t := ?)] => 1,4
            [temp_var] => 1,4
            [is_match] => 1
        )

PHP code used to run this (simplified):

$conn = /* pdo connection object */;
$stmt = $conn->prepare(DB::queryOf('test')); //uses our above query from a file
$stmt->bindValue(':tag', $args['tag'], PDO::PARAM_STR); //hardcode binding '1,4'
$stmt->execute(); //also tried plain #execute($args)
return $stmt->fetchAll(PDO::FETCH_OBJ);

Is there something I'm missing? I am binding a direct string parameter, and it seems the temporary variable is there and set correctly. Why is PDO returning the results for elements where is_match = 0?

`DB::queryOf()` - This is not PDF. You should tell us, which library you are using. — Paul Spiegel, Feb 18 '19 at 14:03
It's not a library, just a function I wrote which loads an `.sql` file (in this case named `test.sql`). Literally just a `file_get_contents` call. The query in question of course is, well, in the question. — Rogue, Feb 18 '19 at 14:04
Without an ORDER BY clause, this is all pretty meaningless anyway. See: [Why should I provide an MCVE for what seems to me to be a very simple SQL query?](https://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query) — Strawberry, Feb 18 '19 at 14:09
Did you try any simple query with a HAVING clause? Sorry.. but your example is not complete and too complex. And the query makes no sense to me. — Paul Spiegel, Feb 18 '19 at 14:09
This is the simplified query wherein the problem occurs, doing something like `SELECT tagged.tag FROM tagged HAVING tag = 1` will execute correctly in PDO. @PaulSpiegel — Rogue, Feb 18 '19 at 14:12
I'm surely wrong but `HAVING` isn't only with the `GROUP BY` statement ? — Frankich, Feb 18 '19 at 14:13
@MacBooc `HAVING` can be used as a conditional for computed columns, and for that reason @Mihai we can't use `WHERE` against the `is_match` result — Rogue, Feb 18 '19 at 14:14
I'm not sure what else is needed aside from a table creation, data values, and the problematic query. The actual problem involves hundreds of lines of code, and is reproduced with what I've shown here (and the included parameter is involved with the issue I believe). — Rogue, Feb 18 '19 at 14:19
I've tested it - And could not reproduce - It returns only 1 an 4. Thank you for not taking the time to write a complete script, so I would only need to C&P it. -1 — Paul Spiegel, Feb 18 '19 at 14:53
That is precisely my point, it returns 1 and 4 on anything I'm testing, _except_ when I was running it in PDO. Thanks for not reading the question at all. — Rogue, Feb 18 '19 at 14:55
The results I'm posting here are run with a copy/paste of the examples I posted in this question, so that's definitely strange. It seems the post below about the `GROUP BY` actually resolved the problem in PDO for me, which admittedly I'm not fully caught up yet on why. — Rogue, Feb 18 '19 at 15:02

mickmackusa · Accepted Answer · 2019-02-18T15:03:56.640

2

I believe this behavior is dependent on the RDBMS being used.

In the absence of the GROUP BY clause, it seems that in some circumstances, the entire result can be considered as "one group". Because one row in the results satisfies the HAVING condition, all shall pass.

Additional reading:

Use of HAVING without GROUP BY in SQL queries

HAVING without GROUP BY

p.s. I don't think the > 0 is necessary.

I suppose I'd write your query something like this:

SELECT tag,
    @t := '1,4' AS temp_var,
    1 AS is_match
FROM tagged
WHERE @t IS NULL OR FIND_IN_SET(tag, @t)
LIMIT 150;

edited Feb 18 '19 at 15:03

answered Feb 18 '19 at 14:49

mickmackusa

43,625
12
83
136

If my understanding (only recently discovered via researching this question) is incorrect, I can happily edit or remove my post. You might like to test other HAVING conditions where no rows satisfy. – mickmackusa Feb 18 '19 at 14:52
I'm testing some things right now and this seems to be a plausible fix for the sample query I provided. I'm trying to adapt the solution now to my actual query (since there's multiple tables involved) to see if that fixes it for PDO. – Rogue Feb 18 '19 at 14:56
1

You're correct on both fronts (`> 0` removed, made sense). In adapting this to my query I ended up having to append a grouping condition to my query, which in the end now looks like `GROUP BY media_source.name, media.id, tagged.media` (where `tagged.media` is the media identifier used in our discussed tag table). However this completely fixed the comparison I used in the `HAVING` clauses for PDO specifically. Spooky stuff! – Rogue Feb 18 '19 at 15:00
I've added my thoughts on an altered query, but it may be too basic for your actual project needs. – mickmackusa Feb 18 '19 at 15:05
You say it depends on RDBMS. OP says it depends on the client (PHP-PDO). That's an interesting agreement. BTW: The modified query still makes no sense. The same can be achieved with `select tag from tagged where tag in (1,4)`. – Paul Spiegel Feb 18 '19 at 15:15
Yes, I further boiled down to a similar query, but I didn't post it because I reckon the OP's real query is likely to be more complex. Like you, I don't see the need for mysql variables, but I do appreciate that the OP did not just slap a big nasty query down and say: "somebody fix my codez". – mickmackusa Feb 18 '19 at 15:20
the reason I did not go with `IN` was due to a lack of being able to use an arbitrary amount of values for a single prepared statement parameter. Using `FIND_IN_SET` I can exclude tags from searching within a string of exclusive results (think of it like an AND search). As for it using `HAVING`, it's because I'm outputting the results as a generated column and filtering on that (mostly for debug, it can be moved when it's finalized). – Rogue Feb 18 '19 at 16:46

PDO prepared statement seems to ignore HAVING clause

1 Answers1