Thread: Query breaking with unknown expression type (lost subquery from v iew?)
Query breaking with unknown expression type (lost subquery from v iew?)
From
Matthew Gabeler-Lee
Date:
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
Matthew Gabeler-Lee <mgabelerlee@zycos.com> writes:
> 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 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!
How do you expect us to debug this when you haven't provided the view
definition?
I'm willing to dig into it if I have a test case to look at, but I don't
have time to try to intuit a test case from an incomplete bug report.
Give me a script to reproduce the failure, please.
regards, tom lane