could not parse "\\000x\\0ffyz" as type bytes: invalid bytea escape sequence

[mniewrzal]

Describe the problem
I have a problem using SUBSTRING function on a column defined as BYTEA. For some values in a column, the function fails with an error:

ERROR: substring(): could not parse "���Q��=\nݻk�1\x10�O�j�%V\\\x12\rz����/\x02���'��z���w�b3EӍK�^��n\t٩�1��\f�$f �\x1e\\G�\x06: \x1a" as type bytes: invalid bytea escape sequence (SQLSTATE 22025)

I'm using pgx as a Go driver.

To Reproduce
This snippet can be used to reproduce this error:

root@localhost:26257/defaultdb> SELECT substring(x'02535497082f02bd395c', 1);
ERROR: substring(): could not parse "\x02ST�\b/\x02�9\\" as type bytes: bytea encoded value ends with escape character
SQLSTATE: 22025

The same snippet works fine for Postgres.

Expected behavior
No error.

Environment:
Build Tag: v20.2.2
Build Time: 2020/11/25 14:45:44
Distribution: CCL
Platform: linux amd64 (x86_64-unknown-linux-gnu)
Go Version: go1.13.14
C Compiler: gcc 6.3.0
Build Commit ID: 92d9495
Build Type: release

[blathers-crl]

Hello, I am Blathers. I am here to help you get the issue triaged.

It looks like you have not filled out the issue in the format of any of our templates. To best assist you, we advise you to use one of these templates.

I have CC'd a few people who may be able to assist you:

• @rafiss (found keywords: pgx)

If we have not gotten back to your issue within a few business days, you can try the following:

• Join our community slack channel and ask on #cockroachdb.
• Try find someone from here if you know they worked closely on the area and CC them.

_{🦉 Hoot! I am a Blathers, a bot for CockroachDB. My owner is otan.}

[rafiss]

thanks @mniewrzal. I think the small example you shared is actually encountering a different problem.

The first error is "could not parse ... as type bytes: invalid bytea escape sequence (SQLSTATE 22025)"
The second error is "could not parse ... as type bytes: bytea encoded value ends with escape character"

For the first one, could you provide a way to reproduce the problem?

The second one I believe is partially because we treat strings like x'...' as bytea, while Postgres treats it as a bitstring. But it also seems like the CockroachDB implementation of subtring(bytea) has a bug.

[mniewrzal]

thank you for looking into this @rafiss

I combined a full test case to reproduce the first issue SQLSTATE 22025:

package my_test

import (
	"context"
	"testing"

	"github.com/jackc/pgx/v4"
	"github.com/stretchr/testify/require"
)

func TestCRDB(t *testing.T) {
	config, err := pgx.ParseConfig("postgres://root@localhost:26257/metabase?sslmode=disable")
	require.NoError(t, err)

	ctx := context.Background()
	conn, err := pgx.ConnectConfig(ctx, config)
	require.NoError(t, err)
	defer conn.Close(ctx)

	_, err = conn.Exec(ctx,
		`DROP TABLE IF EXISTS objects`,
	)
	require.NoError(t, err)

	_, err = conn.Exec(ctx,
		`CREATE TABLE IF NOT EXISTS objects (
			object_key  BYTEA NOT NULL
		)`,
	)
	require.NoError(t, err)

	key := []byte{}
	for i := 0; i < 100; i++ {
		key = append(key, byte(i))
	}
	_, err = conn.Exec(ctx,
		`INSERT INTO objects (object_key) VALUES ($1)`, key,
	)
	require.NoError(t, err)

	value := []byte{}
	err = conn.QueryRow(ctx,
		`SELECT SUBSTRING(object_key, 2) FROM objects LIMIT 1`,
	).Scan(&value)
	require.NoError(t, err)
}

Let me know if I can help more.

[rafiss]

@mniewrzal Thank you for your help! I was able to identify the problem and created #58265 which will fix the bug

[mniewrzal]

That was quick, thank you!