Using `rand()` with `having`

Question

I have a table that contains a list of records. Each iteration, a random set of these must be picked, starting from a specific offset. Each row has a chance to be picked (so e.g. new or not often picked rows are picked more).

However, something doesn't work, causing rows to be returned that do not satisfy a condition using an aliased rand().

I'm attempting to use the following query:

select
    id,
    probability,
    rand() rolledChance
from records
where id > :offset
having rolledChance < probability;

Where :offset is a prepared statement parameter, and is the last scanned id in the last iteration for this user.

On a table created like this (which is the relevant subset of the table):

CREATE TABLE records (id INT, probability FLOAT);

Where probability is a value between 0 and 1 on the table records. However, this returns rows where the condition does not satisfy. I checked this with the following query:

select
    *,
    x.rolledChance < x.probability shouldPick
from
    (select
        id,
        probability,
        rand() rolledChance
    from records
    having rolledChance < probability
) x;

A few rows returned are:

id      probability     rolledChance            shouldPick
12      0.546358        0.015139976530466207    1
26      0.877424        0.9730734508233829      0
46      0.954425        0.35213605347288407     1

When I repurpose the second query as follows, it works as expected, and only returns rows where rolledChance is actually lower than probability:

select
    *,
    x.rolledChance < x.probability shouldPick
from
    (select id, probability, rand() rolledChance from records) x
where rolledChance < probability;

So what am I missing? Are the probability and rolledChance used differently than I thought in the comparison? Is the rand() evaluated every time the alias is used in the same query?

Version output: mysql Ver 15.1 Distrib 10.0.28-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2, running on Debian Jessie.

Really nice question. I made [a fiddle](http://sqlfiddle.com/#!9/b165d/2) to reproduce. I searched on my side and can't find a decent answer. I am really looking forward for an explanation. — Thomas G, Jun 12 '17 at 10:26
I will add one, borrowed from the (nicely minimalized fiddle) of @ThomasG. I did not know you could also Fiddle SQL, by the way, so thank you for that example! — Erik S, Jun 12 '17 at 11:08
Check [B.5.4.8 Problems with Floating-Point Values](https://dev.mysql.com/doc/refman/5.7/en/problems-with-float.html). Try changing `FLOAT` by `DECIMAL`. — wchiquito, Jun 12 '17 at 12:26
Should likely not be the issue, I think. The numbers are already off by 1 decimal, so rounding issues should not be at play. — Erik S, Jun 12 '17 at 18:03
Another option, try adding a `ORDER BY`. `SELECT... ORDER BY \`rolledChance\`;`. — wchiquito, Jun 12 '17 at 20:49
@wchiquito, not an option for us. It's a large table, where we want to guarantee that people will pass every record once at least, so the ones with high probability will quickly be chosen more often. The `offset` is missing in this example, and I will add it to make it more clear why we are doing this like this. — Erik S, Jun 13 '17 at 06:07
@letmutx, that's interesting. We're using 5 major versions later, but this being a difference might point to either odd behaviour, or a bug. — Erik S, Jun 13 '17 at 06:12

score 2 · Accepted Answer · answered Jun 26 '17 at 01:02

I think the problem is that HAVING is applied after GROUP BY, but still before the SELECT phase. I realise it's confusing because the HAVING clause references a column from the SELECT statement, but I think it basically just executes whatever is in the SELECT statement twice - once for the having, and then again for the SELECT.

Eg, see this answer.

Note, it's especially confusing because if you refer to a column name that doesn't appear in the SELECT statement in a HAVING clause it'll throw an error.

Eg, this fiddle

But as per that fiddle above, it'll still let you actually filter based on the result of a function that doesn't appear in the output. Long story short, the HAVING clause is still doing what you want, but you can't both filter on a random value and display it at the same time using that approach. If you need to do that, you need to use a subquery to fix the value first, then the outer query can filter and display on it.

Also, to make it clear, it's probably worth just using RAND() in the having clause, not the SQL part. Though I get that this question is asking why it's doing this rather than trying to solve the problem specifically.

Using `rand()` with `having`

1 Answers1

Linked