BUG #1694: LIKE fails on column with domain after ANALYZE
The following bug has been logged online:
Bug reference: 1694
Logged by: Kevin J. Grittner
Email address: kgrittn@wicourts.gov
PostgreSQL version: 8.0.3
Operating system: Both Windows 2000 and Linux
Description: LIKE fails on column with domain after ANALYZE
Details:
I couldn't get this to happen without at least two columns and two rows.
The column searched with the LIKE predicate must be defined with a domain.
Things work fine until ANALYZE or VACUUM ANALYZE is run against the table.
postgres=# create domain mydomain varchar(20);
CREATE DOMAIN
postgres=# create table t1 (f1 int not null, f2 mydomain not null);
CREATE TABLE
postgres=# insert into t1 values (1, 'one');
INSERT 0 1
postgres=# insert into t1 values (2, 'two');
INSERT 0 1
postgres=# select * from t1 where f2 like 't%';
f1 | f2
----+-----
2 | two
(1 row)
postgres=# analyze t1;
ANALYZE
postgres=# select * from t1 where f2 like 't%';
ERROR: unsupported type: 32740963
"Kevin J. Grittner" <kgrittn@wicourts.gov> writes:
postgres=# select * from t1 where f2 like 't%';
ERROR: unsupported type: 32740963
Confirmed in CVS tip --- looks like we missed a case where we need to
flatten out a domain. Thanks for the report!
regards, tom lane
"Kevin J. Grittner" <kgrittn@wicourts.gov> writes:
The column searched with the LIKE predicate must be defined with a domain.
Things work fine until ANALYZE or VACUUM ANALYZE is run against the table.
Fixed for 8.0.4. Here is the patch if you need it immediately.
regards, tom lane
Index: selfuncs.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/utils/adt/selfuncs.c,v
retrieving revision 1.169.4.4
diff -c -r1.169.4.4 selfuncs.c
*** selfuncs.c 1 Apr 2005 20:32:09 -0000 1.169.4.4
--- selfuncs.c 1 Jun 2005 16:58:50 -0000
***************
*** 164,170 ****
static double get_variable_numdistinct(VariableStatData *vardata);
static bool get_variable_maximum(Query *root, VariableStatData *vardata,
Oid sortop, Datum *max);
! static Selectivity prefix_selectivity(Query *root, VariableStatData *vardata,
Oid opclass, Const *prefix);
static Selectivity pattern_selectivity(Const *patt, Pattern_Type ptype);
static Datum string_to_datum(const char *str, Oid datatype);
--- 164,170 ----
static double get_variable_numdistinct(VariableStatData *vardata);
static bool get_variable_maximum(Query *root, VariableStatData *vardata,
Oid sortop, Datum *max);
! static Selectivity prefix_selectivity(Query *root, Node *variable,
Oid opclass, Const *prefix);
static Selectivity pattern_selectivity(Const *patt, Pattern_Type ptype);
static Datum string_to_datum(const char *str, Oid datatype);
***************
*** 813,818 ****
--- 813,819 ----
List *args = (List *) PG_GETARG_POINTER(2);
int varRelid = PG_GETARG_INT32(3);
VariableStatData vardata;
+ Node *variable;
Node *other;
bool varonleft;
Datum constval;
***************
*** 837,842 ****
--- 838,844 ----
ReleaseVariableStats(vardata);
return DEFAULT_MATCH_SEL;
}
+ variable = (Node *) linitial(args); /*
* If the constant is NULL, assume operator is strict and return zero,
***************
*** 940,946 ****
if (eqopr == InvalidOid)
elog(ERROR, "no = operator for opclass %u", opclass);
! eqargs = list_make2(vardata.var, prefix);
result = DatumGetFloat8(DirectFunctionCall4(eqsel,
PointerGetDatum(root),
ObjectIdGetDatum(eqopr),
--- 942,948 ---- if (eqopr == InvalidOid)
elog(ERROR, "no = operator for opclass %u", opclass);
! eqargs = list_make2(variable, prefix);
result = DatumGetFloat8(DirectFunctionCall4(eqsel,
PointerGetDatum(root),
ObjectIdGetDatum(eqopr),
***************
*** 959,965 ****
Selectivity selec;
if (pstatus == Pattern_Prefix_Partial)
! prefixsel = prefix_selectivity(root, &vardata, opclass, prefix);
else
prefixsel = 1.0;
restsel = pattern_selectivity(rest, ptype);
--- 961,967 ----
Selectivity selec; if (pstatus == Pattern_Prefix_Partial)
! prefixsel = prefix_selectivity(root, variable, opclass, prefix);
else
prefixsel = 1.0;
restsel = pattern_selectivity(rest, ptype);
***************
*** 3695,3701 ****
* more useful to use the upper-bound code than not.
*/
static Selectivity
! prefix_selectivity(Query *root, VariableStatData *vardata,
Oid opclass, Const *prefixcon)
{
Selectivity prefixsel;
--- 3697,3703 ----
* more useful to use the upper-bound code than not.
*/
static Selectivity
! prefix_selectivity(Query *root, Node *variable,
Oid opclass, Const *prefixcon)
{
Selectivity prefixsel;
***************
*** 3707,3713 ****
BTGreaterEqualStrategyNumber);
if (cmpopr == InvalidOid)
elog(ERROR, "no >= operator for opclass %u", opclass);
! cmpargs = list_make2(vardata->var, prefixcon);
/* Assume scalargtsel is appropriate for all supported types */
prefixsel = DatumGetFloat8(DirectFunctionCall4(scalargtsel,
PointerGetDatum(root),
--- 3709,3715 ----
BTGreaterEqualStrategyNumber);
if (cmpopr == InvalidOid)
elog(ERROR, "no >= operator for opclass %u", opclass);
! cmpargs = list_make2(variable, prefixcon);
/* Assume scalargtsel is appropriate for all supported types */
prefixsel = DatumGetFloat8(DirectFunctionCall4(scalargtsel,
PointerGetDatum(root),
***************
*** 3729,3735 ****
BTLessStrategyNumber);
if (cmpopr == InvalidOid)
elog(ERROR, "no < operator for opclass %u", opclass);
! cmpargs = list_make2(vardata->var, greaterstrcon);
/* Assume scalarltsel is appropriate for all supported types */
topsel = DatumGetFloat8(DirectFunctionCall4(scalarltsel,
PointerGetDatum(root),
--- 3731,3737 ----
BTLessStrategyNumber);
if (cmpopr == InvalidOid)
elog(ERROR, "no < operator for opclass %u", opclass);
! cmpargs = list_make2(variable, greaterstrcon);
/* Assume scalarltsel is appropriate for all supported types */
topsel = DatumGetFloat8(DirectFunctionCall4(scalarltsel,
PointerGetDatum(root),
***************
*** 3744,3750 ****
prefixsel = topsel + prefixsel - 1.0; /* Adjust for double-exclusion of NULLs */
! prefixsel += nulltestsel(root, IS_NULL, vardata->var, 0);
/*
* A zero or slightly negative prefixsel should be converted into
--- 3746,3752 ----
prefixsel = topsel + prefixsel - 1.0; /* Adjust for double-exclusion of NULLs */
! prefixsel += nulltestsel(root, IS_NULL, variable, 0);
/*
* A zero or slightly negative prefixsel should be converted into