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

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

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

Kevin J. Grittner

The following bug has been logged online:

Bug reference:      1694
Logged by:          Kevin J. Grittner
Email address:      [hidden email]
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
Reply | Threaded
Open this post in threaded view
|

Re: [BUGS] BUG #1694: LIKE fails on column with domain after ANALYZE

Tom Lane-2
"Kevin J. Grittner" <[hidden email]> 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 [hidden email])
Reply | Threaded
Open this post in threaded view
|

Re: [BUGS] BUG #1694: LIKE fails on column with domain after ANALYZE

Tom Lane-2
In reply to this post by Kevin J. Grittner
"Kevin J. Grittner" <[hidden email]> 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

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