random generated string matching index in inexplicable ways

From: Myles Miller <pg(at)q7r7(dot)com>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: random generated string matching index in inexplicable ways
Date: 2019-05-07 11:53:45
Message-ID: 20190507115345.GA56585@50pop.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Lu, Dan 2019-05-07 12:12:18 Question on binding VIP to Postgresql instance
Previous Message Nicklas Avén 2019-05-07 07:49:11 Re: PostgreSQL on Amazon RDS