Query breaking with unknown expression type (lost subquery from v iew?)

Поиск
Список
Период
Сортировка
От Matthew Gabeler-Lee
Тема Query breaking with unknown expression type (lost subquery from v iew?)
Дата
Msg-id ABABFB80F35AD311848B0090279918EF010B9B65@ZYCOSNT2.hq.zycos.com
обсуждение исходный текст
Ответы Re: Query breaking with unknown expression type (lost subquery from v iew?)  (Tom Lane <tgl@sss.pgh.pa.us>)
Список pgsql-general
Query, used to work in 7.2.3:
SELECT * from VResults NATURAL LEFT JOIN qrp_events
WHERE qrp_score > 45 AND qry_charge < 3 AND vst_valstate IS NULL
  AND (
    (qpe_name = 'autoval.pl' AND qpe_version < 3)
    OR qpe_name IS NULL
  )
ORDER BY sdt_id ASC, qry_num ASC, qrp_pnum ASC

If and only if it returns rows, after running as long as it does when it
works, the query bombs with the message "ExecEvalExpr: unknown expression
type 108" and then the query.  Given that it dumps the query, I'm guessing
that it's coming from src/backend/executor/execQual.c:1782.  Rooting through
src/include/nodes/nodes.h and counting the stuff in the enum, it looks like
it's getting a T_SubLink.  If that means subquery, there is a subquery in
the VResults view, but for some reason that subquery isn't showing up in the
explain output!  Perhaps that's the problem.  Somewhere in things, the
subquery in vresults is getting lost.

If I remove the NATURAL LEFT JOIN qrp_events and change the filters on qpe_*
to EXISTS conditions, it works properly (and the explain plan shows the
subplan in vresults properly).  That's not an option, though, because I need
the columns from qrp_events, and if I add the join back in with the exists
conditions, it bombs again with the same error and the subplan from vresults
missing again.

Explain output:
 Sort  (cost=533290.67..533979.16 rows=275396 width=292)
   Sort Key: qrp.sdt_id, qrp.qry_num, qrp.qrp_pnum
   ->  Merge Join  (cost=385663.61..406167.05 rows=275396 width=292)
         Merge Cond: (("outer".sdt_id = "inner".sdt_id) AND ("outer".qry_num
= "inner".qry_num))
         Join Filter: ("outer".qrp_pnum = "inner".qrp_pnum)
         Filter: ((("inner".qpe_name = 'autoval.pl'::character varying) OR
("inner".qpe_name IS NULL)) AND (("inner".qpe_version < 3) OR
("inner".qpe_name IS NULL)))
         ->  Merge Join  (cost=385663.61..400518.37 rows=275396 width=221)
               Merge Cond: (("outer".sdt_id = "inner".sdt_id) AND
("outer".qry_num = "inner".qry_num))
               Join Filter: ("outer".qrp_pnum = "inner".qrp_pnum)
               Filter: ("inner".vst_valstate IS NULL)
               ->  Merge Join  (cost=385663.61..398554.42 rows=275396
width=190)
                     Merge Cond: ("outer".sdt_id = "inner".sdt_id)
                     ->  Merge Join  (cost=385663.61..394396.53 rows=275396
width=126)
                           Merge Cond: (("outer".sdt_id = "inner".sdt_id)
AND ("outer".qry_num = "inner".qry_num))
                           ->  Sort  (cost=188067.51..189503.36 rows=574340
width=62)
                                 Sort Key: qry.sdt_id, qry.qry_num
                                 ->  Seq Scan on queries qry
(cost=0.00..75427.45 rows=574340 width=62)
                                       Filter: (qry_charge < 3)
                           ->  Sort  (cost=197596.10..198461.32 rows=346088
width=64)
                                 Sort Key: qrp.sdt_id, qrp.qry_num
                                 ->  Seq Scan on qry_peptides qrp
(cost=0.00..149164.67 rows=346088 width=64)
                                       Filter: (qrp_score > 45::double
precision)
                     ->  Index Scan using srcdats_pkey on srcdats sdt
(cost=0.00..25.48 rows=591 width=64)
               ->  Index Scan using qrp_validation_pkey on qrp_validation
vst  (cost=0.00..1084.35 rows=21660 width=31)
         ->  Index Scan using xqpe_qrp on qrp_events  (cost=0.00..4320.53
rows=57407 width=71)


    -Matt

В списке pgsql-general по дате отправления:

Предыдущее
От: "Magnus Naeslund(f)"
Дата:
Сообщение: Re: Postgresql -- initial impressions and comments
Следующее
От: Jean-Luc Lachance
Дата:
Сообщение: Re: 7.3 no longer using indexes for LIKE queries