Обсуждение: 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