Thread: ERROR: "failed to locate grouping columns"
Hi all, I'm sending this to -hackers because i don't now if it is a bug or a expected behavior. I have the view bellow (if the selects bellow shows unformated in this email, i put this in http://guedesoft.net/txt/vw_error.txt too. ): CREATE OR REPLACE VIEW vw_my_test ASSELECT DISTINCT cv.cv_cdct AS cdct, -- returns a int4 cv.cv_cdcp AS cdcp, --returns a numeric ( SELECT cp.cp_nmfts FROM cptv cp WHERE cp.cp_cdcp = cv.cv_cdcp) AS nmfts, -- returnsa varchar epr.epr_nrctn AS nrctn, -- returns a numeric cv.cv_tpvnc AS tpvnc, -- returns a int4 ( SELECT rg.rg_dsc FROM rgst rg WHERE rg.rg_idrg = cv.cv_tpvnc) AS dsc_vnc,-- returns a varchar cv.cv_ndcdv AS ndcdv_prnc, -- returns a varchar ( SELECT ps.ps_nm FROM pss ps WHERE ps.ps_nrdc = cv.cv_ndcdv) AS nmdvprnc, -- returns a varchar cvd.cvd_nmdvsld AS ndcdv_sld, -- returns a varchar ( SELECT ps.ps_nm FROM pss ps WHERE ps.ps_nrdc = cvd.cvd_nmdvsld)AS nmdvsld, -- returns a varchar cv.cv_vltt AS vltt, -- returns a numeric(18,2) ( SELECT max(oc.oc_dtagn) AS max FROM ocr oc WHERE oc.oc_cdct = ev.ev_cdct) AS dtagn, -- returns a date ( SELECT CASE WHEN abs(min(pe.pe_dtvnc) - date(now())) <= 15 THEN 1231230 WHEN abs(min(pe.pe_dtvnc) - date(now())) >= 16 AND abs(min(pe.pe_dtvnc) - date(now())) <= 30 THEN1341231 WHEN abs(min(pe.pe_dtvnc) - date(now())) >= 31 AND abs(min(pe.pe_dtvnc) - date(now())) <= 45THEN 2345342 WHEN abs(min(pe.pe_dtvnc) - date(now())) >= 46 AND abs(min(pe.pe_dtvnc) - date(now())) <=60 THEN 654653 WHEN abs(min(pe.pe_dtvnc) - date(now())) >= 61 AND abs(min(pe.pe_dtvnc) - date(now()))<= 90 THEN 45254 WHEN abs(min(pe.pe_dtvnc) - date(now())) >= 91 AND abs(min(pe.pe_dtvnc) -date(now())) <= 180 THEN 13425 WHEN abs(min(pe.pe_dtvnc) - date(now())) >= 181 AND abs(min(pe.pe_dtvnc)- date(now())) <= 360 THEN 12346 ELSE 13417 END AS "case" FROMpcep pe WHERE pe.pe_nrcntr = ev.ev_nrcntr) AS dsatr, cv.cv_stc AS stc, rg.rg_cdrgs AS cdrgs, rg.rg_dscAS dsc_stcFROM epvnc evJOIN ctvn cv ON cv.cv_cdct = ev.ev_cdctJOIN eptm epr ON epr.epr_nrcntr = ev.ev_nrcntrJOINrgst rg ON cv.cv_stc = rg.rg_idrgLEFT JOIN cvdvsld cvd ON cvd.cvd_cdct = cv.cv_cdct ; And bellow is the select that returns: "ERROR: failed to locate grouping columns" when no rows is returned by the View above, but it runs well when one or more rows is returned by same view. --- SELECT cdcp, nmfts, nrctn, tpvnc, dsc_vnc, ndcdv_prnc, nmdvpr, ndcdv_sld, max(vltt) FROM vw_my_test_ GROUP BY cdcp, nmfts, nrctn, tpvnc, dsc_vnc, ndcdv_prnc, nmdvpr, ndcdv_sld; --- If i group only by the *int* or *numeric* fields the error don't occurs, it only shows if i use a varchar in group by and the view returns 0 records If i change the view above to use JOINs then all works fine... meaning the problem is something in SUBSELECTs and VARCHAR used in that way. Is this a bug or a expected behavior? best regards. -- Dickson S. Guedes - mail/xmpp: guedes@guedesoft.net - skype: guediz http://guedesoft.net - http://planeta.postgresql.org.br
"Dickson S. Guedes" <listas@guedesoft.net> writes:
> And bellow is the select that returns: "ERROR: failed to locate grouping columns"
> when no rows is returned by the View above, but it runs well when one or more
> rows is returned by same view.
I really have a hard time believing that whether you get that error is
contingent on whether the view returns some rows or not. That's a
planner message and couldn't possibly have to do with what happens
at runtime.
Would you put together a complete example, instead of leaving us to
guess what's underlying the view? And what PG version is this?
regards, tom lane
Em Sáb, 2009-03-07 às 19:38 -0500, Tom Lane escreveu: > "Dickson S. Guedes" <listas@guedesoft.net> writes: > > And bellow is the select that returns: "ERROR: failed to locate grouping columns" > > when no rows is returned by the View above, but it runs well when one or more > > rows is returned by same view. > > I really have a hard time believing that whether you get that error is > contingent on whether the view returns some rows or not. That's a > planner message and couldn't possibly have to do with what happens > at runtime. And I was really confused when I've tested. I've seen that it's a planner message, then I discard SUBSELECTs and use JOINs and it works. > Would you put together a complete example, instead of leaving us to > guess what's underlying the view? Ok, I'll prepare a full test and send it. > And what PG version is this? Oh! I forgot to say, the version is 8.3.6. Thanks. -- Dickson S. Guedes - mail/xmpp: guedes@guedesoft.net - skype: guediz http://guedesoft.net - http://planeta.postgresql.org.br
Em Sáb, 2009-03-07 às 19:38 -0500, Tom Lane escreveu: > I really have a hard time believing that whether you get that error is > contingent on whether the view returns some rows or not. That's a > planner message and couldn't possibly have to do with what happens > at runtime. Well, today I have more time to study the environment and I'd see that was a coincidence in the fact that when the grouping by in the view works fine and it was returning values, it was tested in a 8.1.4 PG version. Now I made a complete test in 8.1.4 and 8.3.6. In the first the error not occurs, in the last yes. > Would you put together a complete example, instead of leaving us to > guess what's underlying the view? And what PG version is this? Attached there is a dump with the tables and views related: vw_cnt_vnc_tst -> is my view before I changed sub-selects to JOIN vw_that_works - an example view that works without grouping some columns vw_that_not_works -> an example view that throws an error thanks. -- Dickson S. Guedes - mail/xmpp: guedes@guedesoft.net - skype: guediz http://guedesoft.net - http://planeta.postgresql.org.br
Attachment
"Dickson S. Guedes" <listas@guedesoft.net> writes:
> Em Sáb, 2009-03-07 às 19:38 -0500, Tom Lane escreveu:
>> Would you put together a complete example, instead of leaving us to
>> guess what's underlying the view? And what PG version is this?
> Attached there is a dump with the tables and views related:
> vw_that_works - an example view that works without grouping some columns
> vw_that_not_works -> an example view that throws an error
OK, reproduced here on HEAD:
dg=# select * from vw_that_not_works;
ERROR: failed to locate grouping columns
Off to do some debugging. Thanks for the test case!
regards, tom lane
OK, I poked into this. The test case can be simplified to this:
regression=# create table t1 (f1 numeric(14,0), f2 varchar(30));
CREATE TABLE
regression=# create view vv as select distinct f1,f2,(select f2 from t1 x where x.f1=aa.f1) as fs from t1 aa;
CREATE VIEW
regression=# select * from vv group by f1,f2,fs;
ERROR: failed to locate grouping columns
The reason that locate_grouping_columns fails is that it's being asked
to match up a Var with type varchar(30) (representing the result of the
view's fs column) to a Var with typmod -1, and those are not equal
according to equal(). The Var with default typmod is being manufactured
by build_physical_tlist(), which is looking at a subquery RTE whose
targetlist contains a SubPlan node. Since exprTypmod just punts on
SubPlans, it constructs a Var with typmod -1.
So there are a couple of places we could assign blame here:
1. Subqueries in RTE nodes are supposed to be virgin, unplanned
querytrees, so finding a SubPlan in the targetlist is unexpected.
On this theory, the fault is that of set_subquery_pathlist(), which
ought to copy the RTE's subquery before it turns subquery_planner
loose on it (not to mention the changes it itself makes...). More
generally it's another reason to fix the planner to not scribble on
its input, but that's a task for some other day.
2. It would still work if only SubPlans didn't lose information relative
to SubLinks. On this theory we ought to add a firstColTypmod field to
SubPlan. (The reason we didn't see this behavior before 8.3 is that
exprTypmod punted on SubLinks, too, before 8.3, and so the output of
the calling view would have been assigned typmod -1 anyway.)
Solution #1 is a bit annoying from a planner performance point of view,
but is probably the safest thing in the near term. Solution #2 is
seeming like a good idea in the long run; but it also seems like it is
just fixing one symptom of the general issue that we're scribbling on
the content of a subquery RTE. I'm also a tad hesitant to back-patch it
because I'm not sure if there are any places where it would change
user-visible behavior in unexpected ways.
So what I'm inclined to do is insert a copyObject() call into
set_subquery_pathlist(), and maybe in the future add a typmod field to
SubPlan. I remain a bit uncertain about how far back to back-patch.
We know that 8.3 is broken and that 8.2 and before do not exhibit this
particular symptom. It seems like there might be other problems with
the same root cause that do afflict pre-8.3 versions, but if we've gone
this long without finding them, are they really there? Should we slow
down the planner in back versions to prevent a hypothetical problem?
regards, tom lane