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