Re: random generated string matching index in inexplicable ways

> On 7 May 2019, at 13:53, Myles Miller <pg(at)q7r7(dot)com> wrote:
>
> PROBLEM:
> Strings or characters generated by any random function (including pg_crypto
> gen_random_bytes) are matching a string/char index in surprising ways.

I fail to see anything surprising in your examples.

> Reduced down to its simplest example:

(…)

> -- if we use random-generated 'A' or 'B', things get inexplicable
>
> # SELECT y FROM x WHERE y = chr(round(random())::int + 65);
> y
> ---
> A
> B
> (2 rows)

Here you got a random value in the lower range of 0..1 for the record with value ‘A’, so that’s a match, and one in the higher range for value ‘B’, a match again, so you get 2 rows.

>
> # SELECT y FROM x WHERE y = chr(round(random())::int + 65);
> y
> ---
> (0 rows)

Here you got a random value in the higher range for the record with value ‘A’, so no match, and one in the lower range for value ‘B’, no match again, so you get 0 rows.

> # SELECT y FROM x WHERE y = chr(round(random())::int + 65);
> y
> ---
> B
> (1 row)

Here you got two random values in the higher range, so only the row with ‘B’ matches.

You could also get two random values in the lower range and only get a match against ‘A’.

Regards,
Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.