PostgreSQL Bugs - BUG #1694: LIKE fails on column with domain after ANALYZE

Author

BUG #1694: LIKE fails on column with domain after ANALYZE

Kevin J. Grittner

2005-06-01, 9:25 am

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

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Tom Lane

2005-06-01, 11:24 am

"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

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere
" to majordomo@postgresql
.org)

Tom Lane

2005-06-01, 1:24 pm

"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_numdist
inct(VariableStatDat
a *vardata);
static bool get_variable_maximum
(Query *root, VariableStatData *vardata,
Oid sortop, Datum *max);
! static Selectivity prefix_selectivity(Q
uery *root, VariableStatData *vardata,
Oid opclass, Const *prefix);
static Selectivity pattern_selectivity(
Const *patt, Pattern_Type ptype);
static Datum string_to_datum(cons
t char *str, Oid datatype);
--- 164,170 ----
static double get_variable_numdist
inct(VariableStatDat
a *vardata);
static bool get_variable_maximum
(Query *root, VariableStatData *vardata,
Oid sortop, Datum *max);
! static Selectivity prefix_selectivity(Q
uery *root, Node *variable,
Oid opclass, Const *prefix);
static Selectivity pattern_selectivity(
Const *patt, Pattern_Type ptype);
static Datum string_to_datum(cons
t 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(Direc
tFunctionCall4(eqsel
,
PointerGetDatum(root
),
ObjectIdGetDatum(eqo
pr),
--- 942,948 ----

if (eqopr == InvalidOid)
elog(ERROR, "no = operator for opclass %u", opclass);
! eqargs = list_make2(variable,
prefix);
result = DatumGetFloat8(Direc
tFunctionCall4(eqsel
,
PointerGetDatum(root
),
ObjectIdGetDatum(eqo
pr),
***************
*** 959,965 ****
Selectivity selec;

if (pstatus == Pattern_Prefix_Parti
al)
! prefixsel = prefix_selectivity(r
oot, &vardata, opclass, prefix);
else
prefixsel = 1.0;
restsel = pattern_selectivity(
rest, ptype);
--- 961,967 ----
Selectivity selec;

if (pstatus == Pattern_Prefix_Parti
al)
! prefixsel = prefix_selectivity(r
oot, 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(Q
uery *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(Q
uery *root, Node *variable,
Oid opclass, Const *prefixcon)
{
Selectivity prefixsel;
***************
*** 3707,3713 ****
BTGreaterEqualStrate
gyNumber);
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(Direc
tFunctionCall4(scala
rgtsel,
PointerGetDatum(root
),
--- 3709,3715 ----
BTGreaterEqualStrate
gyNumber);
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(Direc
tFunctionCall4(scala
rgtsel,
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(Direc
tFunctionCall4(scala
rltsel,
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(Direc
tFunctionCall4(scala
rltsel,
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

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Sponsored Links