ERROR ExecInitIndexScan: both left and right op's a real-vars - Mailing list pgsql-sql

From Sergei M. Suntsov
Subject ERROR ExecInitIndexScan: both left and right op's a real-vars
Date
Msg-id Pine.LNX.3.95.990128162819.7705A-100000@ns.uic.nsu.ru
Whole thread Raw
List pgsql-sql
    Hello

I'm currently trying to implement some kind of home grown full text
search. Basic idea as in fulltextindex examples with postgresql-6.4.2,
but everything is done on client side. (It doesn't matter actually)

My tables layout:
create table faqs (
    id int PRIMARY KEY DEFAULT nextval('s_faqs_id'),
    product_id int NOT NULL, // reference to table products
    category_id int , // reference to table categories
    source_id int NOT NULL, // reference to table faq_sources
        cleared bool NOT NULL DEFAULT 'f', // if entry is cleared
        accepted bool NOT NULL DEFAULT 'f', // if entry is accepted by
        rating int NOT NULL DEFAULT 0,
        mod_time datetime DEFAULT TEXT 'now', // date of last modification
        faq_version int NOT NULL DEFAULT 0, // version of entry
);

create table faq_keywords (
        faq_id int,
        keyword char(32),
        times_found int,
        category int
);

create unique index pk_faq_keywords on faq_keywords(keyword, faq_id,
category);



Select statement which causes error mentioned in header:

select distinct f.id from faqs f, faq_keywords fk1, faq_keywords fk2,
faq_keywords fk3 where cleared = '0' and accepted = '0' and fk1.keyword =
'error' and fk2.keyword = 'manual' and fk1.faq_id = fk2.faq_id and
fk3.keyword = 'user' and fk2.faq_id = fk3.faq_id and f.id=fk3.faq_id;

If I drop pk_faq_keywords, then create index only on 'keyword' field of
table 'faq_keywords' everything works fine. Everything also works fine if
I try to to search only on 2 keywords.

Another funny thing is: if you add 'order by f.mod_time' to the end of
this statement (then it works ) it will ignore 'distinct' keyword.

Sincerely, Sergei





pgsql-sql by date:

Previous
From: The Hermit Hacker
Date:
Subject: Test ...
Next
From: Marcus Better
Date:
Subject: Re: [SQL] Serial numbers