No = operator for opfamily 426
Hi everyone,
Consider the following statements:
CREATE TABLE t0(c0 TEXT);
CREATE INDEX i0 ON t0(c0 bpchar_ops);
SELECT * FROM t0 WHERE t0.c0 LIKE ''; -- ERROR: no = operator for opfamily 426
Unexpectedly, the index seems to create problems for the subsequent
query. When replacing the TEXT type by CHAR, the statements execute
successfully. Should it be possible to use this opclass for the TEXT
type?
Best,
Manuel
Manuel Rigger <rigger.manuel@gmail.com> writes:
Consider the following statements:
CREATE TABLE t0(c0 TEXT);
CREATE INDEX i0 ON t0(c0 bpchar_ops);
SELECT * FROM t0 WHERE t0.c0 LIKE ''; -- ERROR: no = operator for opfamily 426
Hm. Right offhand, I'm wondering why we don't reject that index
specification. I guess it's because we can use the index for
weird cases like
regression=# explain SELECT * FROM t0 WHERE t0.c0::bpchar = '';
QUERY PLAN
-----------------------------------------------------------------
Bitmap Heap Scan on t0 (cost=4.21..14.35 rows=7 width=32)
Recheck Cond: ((c0)::bpchar = ''::bpchar)
-> Bitmap Index Scan on i0 (cost=0.00..4.21 rows=7 width=0)
Index Cond: ((c0)::bpchar = ''::bpchar)
(4 rows)
and even
regression=# explain SELECT * FROM t0 WHERE t0.c0::bpchar like '';
QUERY PLAN
-----------------------------------------------------------------
Bitmap Heap Scan on t0 (cost=4.21..14.35 rows=7 width=32)
Filter: ((c0)::bpchar ~~ ''::text)
-> Bitmap Index Scan on i0 (cost=0.00..4.21 rows=7 width=0)
Index Cond: ((c0)::bpchar = ''::bpchar)
(4 rows)
Really what the error is showing is that like_support.c is being too
aggressive by assuming that it'll necessarily find a matching opfamily
member. It should probably just silently fail if it can't construct
the opclause it wants.
regards, tom lane
I wrote:
Manuel Rigger <rigger.manuel@gmail.com> writes:
Consider the following statements:
CREATE TABLE t0(c0 TEXT);
CREATE INDEX i0 ON t0(c0 bpchar_ops);
SELECT * FROM t0 WHERE t0.c0 LIKE ''; -- ERROR: no = operator for opfamily 426
Really what the error is showing is that like_support.c is being too
aggressive by assuming that it'll necessarily find a matching opfamily
member. It should probably just silently fail if it can't construct
the opclause it wants.
I pushed a stopgap fix that just does that, but I think really what we
ought to do about this is decouple like_support.c as far as possible
from the index opclass. The notion that we choose the target operators
based on the opclass is really backwards now that I think about it.
The operators that represent the potentially indexscannable conditions
are determined by the LIKE/regex operator, and what we should do is
just ask whether the opclass can support them.
The "pattern" opclasses put a crimp in this position, but those can
now be seen to be legacy things not a model that future code is likely
to follow. So I present the attached proposed patch that does things
this way. The only short-term advantage is that it can handle applying
an exact-match LIKE to a hash opclass:
regression=# create table t (f1 text);
CREATE TABLE
regression=# create index on t using hash(f1);
CREATE INDEX
regression=# explain select * from t where f1 like 'foo';
QUERY PLAN
-----------------------------------------------------------------------
Bitmap Heap Scan on t (cost=4.05..14.20 rows=7 width=32)
Filter: (f1 ~~ 'foo'::text)
-> Bitmap Index Scan on t_f1_idx (cost=0.00..4.05 rows=7 width=0)
Index Cond: (f1 = 'foo'::text)
(4 rows)
which isn't much of a gain, admittedly. But now this code won't need
revision when we start to think about new index AMs with new opclasses
that happen to implement btree-ish semantics.
regards, tom lane