sha family hash function errors when meetting '\'

[NiuBlibing]

Describe the bug

When using sha family hash with input containing '', it will failed.

Error message/log

ERROR:  QueryError: Expr error: Parse error: invalid input syntax for type bytea

To Reproduce

-- sha family hash failed
select sha1('\');
select sha224('\');
select sha256('\');
select sha384('\');
select sha512('\');

-- MD5 success
select md5('\');

Expected behavior

I expect sha family functions work well with ''.
Or support convert_to so we can convert the varchat to utf8 bytes.

How did you deploy RisingWave?

via docker risingwave playground: ./bin/risingwave playground

The version of RisingWave

PostgreSQL 9.5-RisingWave-1.3.0 (unknown)
risingwavelabs/risingwave latest 11a43456396f 2 weeks ago 4.81GB

Additional context

No response

[fuyufjh]

Thanks for reporting this. Let me take a look.

[fuyufjh]

The behavior is consistent with PostgresQL:

test=# select sha384('\');
ERROR:  invalid input syntax for type bytea
LINE 1: select sha384('\');
                      ^
test=# select md5('\');
               md5                
----------------------------------
 28d397e87306b8631f3ed80d858d35f0
(1 row)

This is because sha functions accept a bytea, so you need to cast a string to bytea:

sha256 ( bytea ) → bytea

https://pgpedia.info/s/sha256.html

example:

postgres=# SELECT sha256('foo'::bytea);
                               sha256                               
--------------------------------------------------------------------
 \x2c26b46b68ffc68ff99b453c1d30413413422d706483bfa0f98a5e886266e7ae

[NiuBlibing]

But convert 'select '\'::bytea; will failed also.

[xiangjinwu]

But convert 'select '\'::bytea; will failed also.

There are two ways to input a bytea value of a single byte 0x5c (\):

test=# select '\\'::bytea;
 bytea 
-------
 \x5c
(1 row)

test=# select '\x5c'::bytea;
 bytea 
-------
 \x5c
(1 row)

test=# select '\\'::bytea = '\x5c'::bytea;
 ?column? 
----------
 t
(1 row)

More options if using conversion functions:

• select '\\'::bytea;
• select '\x5c'::bytea;
• select decode('\\', 'escape');
• select decode('5c', 'hex');
• select decode('XA==', 'base64');
• select convert_to('\', 'utf8'); (since feat: support convert_from/convert_to function that only support UTF8 encoding #13597 on 2023-11-23)
• select convert_to(e'\\', 'utf8'); (since feat: support convert_from/convert_to function that only support UTF8 encoding #13597 on 2023-11-23)
• select varcharsend('\'); (not in RisingWave because it is a PostgreSQL internal function)
• select textsend('\'); (not in RisingWave because it is a PostgreSQL internal function)

[NiuBlibing]

Is there a way to convert it in table so that I can use sha family hash?

-- create table
create table test (
    test varchar
);

-- insert data
insert into test values ('\=');

-- this will failed
-- ERROR:  QueryError: Internal(Expr error: Parse error: invalid input syntax for type bytea)
select test::bytea from test;
select sha512(test::bytea) from test;

[xiangjinwu]

replace(test, '\', '\\')::bytea

[NiuBlibing]

Thanks, it worked for me.