On some
LIKE
queries on a bytea
column, PostgreSQL is mangling my input byte sequence. Why could this be happening?
Expected query plan: b >= '\x4cbf996e2c527620d067571c6f650643edfc5cdc0bd39c29'::bytea
From actual query plan: b >= '\x4cbf996e2c527620d067571c6f650643edfcdc0bd39c29'::bytea
Notice the difference in the byte sequences (spaces show missing byte):
Expected: \x4cbf996e2c527620d067571c6f650643edfc5cdc0bd39c29
Actual: \x4cbf996e2c527620d067571c6f650643edfc dc0bd39c29
Table:
=> \d tbl
table "tbl"
Column | Type | Collation | Nullable | Default
--------+-----------+-----------+----------+---------
id | int | | |
b | bytea | | |
Query:
SELECT * FROM tbl WHERE b LIKE '\x4cbf996e2c527620d067571c6f650643edfc5cdc0bd39c29'::bytea||'%'
Query planner:
EXPLAIN ANALYZE SELECT * FROM tbl WHERE b LIKE '\x4cbf996e2c527620d067571c6f650643edfc5cdc0bd39c29'::bytea||'%';
QUERY PLAN
-----------------------------------------------------------------------------------------------------
Index Scan using idx on tbl (cost=0.56..8.58 rows=670 width=172) (actual time=0.020..0.021 rows=0 loops=1)
Index Cond: ((b >= '\x4cbf996e2c527620d067571c6f650643edfcdc0bd39c29'::bytea) AND (b < '\x4cbf996e2c527620d067571c6f650643edfcdc0bd39c2a'::bytea))
Filter: (b ~~ '\x4cbf996e2c527620d067571c6f650643edfc5cdc0bd39c2925'::bytea)
Planning Time: 0.090 ms
Execution Time: 0.033 ms
(5 rows)
Asked by D Schlachter
(131 rep)
Feb 13, 2023, 05:08 PM
Last activity: Feb 13, 2023, 05:38 PM
Last activity: Feb 13, 2023, 05:38 PM