Sample Header Ad - 728x90

Why does PostgreSQL mangle my hex input in a LIKE query?

1 vote
1 answer
303 views
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