random generated string matching index in inexplicable ways

Поиск
Список
Период
Сортировка
От Myles Miller
Тема random generated string matching index in inexplicable ways
Дата
Msg-id 20190507115345.GA56585@50pop.com
обсуждение исходный текст
Ответы Re: random generated string matching index in inexplicable ways  (Alban Hertroys <haramrae@gmail.com>)
Список 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.




В списке pgsql-general по дате отправления:

Предыдущее
От: Nicklas Avén
Дата:
Сообщение: Re: PostgreSQL on Amazon RDS
Следующее
От: "Lu, Dan"
Дата:
Сообщение: Question on binding VIP to Postgresql instance