PROBLEM:
Strings or characters generated by any random function (including pg_crypto
gen_random_bytes) are matching a string/char index in surprising ways.
Reduced down to its simplest example:
-- use random 0 or 1, plus 65, to get 'A' or 'B'
# SELECT chr(round(random())::int + 65);
chr
-----
B
(1 row)
# SELECT chr(round(random())::int + 65);
chr
-----
A
(1 row)
-- simple table for matching:
CREATE TABLE x( y char(1) primary key );
INSERT INTO x(y) VALUES ('A');
INSERT INTO x(y) VALUES ('B');
-- if I query 'A' or 'B' it works as expected
# SELECT y FROM x WHERE y = 'A';
y
---
A
(1 row)
# SELECT y FROM x WHERE y = 'B';
y
---
B
(1 row)
-- 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)
# SELECT y FROM x WHERE y = chr(round(random())::int + 65);
y
---
(0 rows)
# SELECT y FROM x WHERE y = chr(round(random())::int + 65);
y
---
B
(1 row)
I've been wrestling with this for hours. Any suggestions? Thank you.
