Thread: Port Bug Report: ERROR: RestrictionClauseSelectivity: bad value 2.213087

============================================================================
                        POSTGRESQL BUG REPORT TEMPLATE
============================================================================


Your name        : Bram Kivenko
Your email address    : bram@xspace.com

Category        : runtime: back-end: SQL
Severity        : non-critical

Summary: ERROR:  RestrictionClauseSelectivity: bad value 2.213087

System Configuration
--------------------
  Operating System   : LINUX 2.0.36

  PostgreSQL version : 6.4.2

  Compiler used      : GCC 2.8.1

Hardware:
---------
Linux mulder 2.0.36 #4 Wed Nov 18 10:49:57 EST 1998 i586

 11:11pm  up 5 days, 15:24, 11 users,  load average: 0.33, 0.26, 0.33
115 processes: 114 sleeping, 1 running, 0 zombie, 0 stopped
CPU states:  0.5% user,  3.2% system,  3.6% nice, 96.3% idle
Mem:   62312K av,  59564K used,   2748K free,  58320K shrd,   3744K buff
Swap:  92728K av,    176K used,  92552K free                 20148K cached


Versions of other tools:
------------------------
Gnu make - 3.74
Flex - 2.5.4

--------------------------------------------------------------------------

Problem Description:
--------------------
All but some spurious select queries with comparisons against
text fields return a RestrictionClauseSelectivity error.

src/backend/optimizer/util/plancat.c:
I modified the function "restriction_selectivity"
to accept values from 0.0 - 3.0, instead 0.0 to 1.0...

This gets rid of the error message, but it probably doesn't
make for a good engine.

--------------------------------------------------------------------------

Test Case:
----------
The query:
SELECT "xx_entryid" FROM "uui_extras" WHERE "News Type" LIKE 'releases';

Fails on the table:
CREATE TABLE "uui_extras" (
        "xx_entryid" int4,
        "Division" text,
        "list in directory" int2,
        "File Types" text,
        "News Type" text);

The table is currently populated with:
INSERT INTO "uui_extras" values (1000,'Corporate+Office',1,'Documentation','releases');
INSERT INTO "uui_extras" values (1002,'Solutions+-+EMEA',NULL,'Information','reports%3Aquarterly+reports');
INSERT INTO "uui_extras" values (1001,'Card+Services+-+US',NULL,'Upgrade','reports%3Aannual+reports');
INSERT INTO "uui_extras" values (1003,'Card+Services+-+Canada',NULL,'Miscellaneous','highlights');
INSERT INTO "uui_extras" values (1004,NULL,NULL,'FAQ','disclosure');



--------------------------------------------------------------------------

Solution:
---------


--------------------------------------------------------------------------

> --------------------
> All but some spurious select queries with comparisons against
> text fields return a RestrictionClauseSelectivity error.
>
> src/backend/optimizer/util/plancat.c:
> I modified the function "restriction_selectivity"
> to accept values from 0.0 - 3.0, instead 0.0 to 1.0...
>
> This gets rid of the error message, but it probably doesn't
> make for a good engine.
>

This is fixed in 6.5 beta, due out soon.  Do a vacuum analyze on the
table, and it will be fixed.  The problem is you did an alter table add,
and there was a bug there.

--
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026