Re: 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 AS
SELECT
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, -- returns a 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 THEN 1341231
WHEN abs(min(pe.pe_dtvnc) - date(now())) >= 31 AND abs(min(pe.pe_dtvnc) - date(now())) <= 45 THEN 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"
FROM pcep pe
WHERE pe.pe_nrcntr = ev.ev_nrcntr) AS dsatr,
cv.cv_stc AS stc,
rg.rg_cdrgs AS cdrgs,
rg.rg_dsc AS dsc_stc
FROM epvnc ev
JOIN ctvn cv ON cv.cv_cdct = ev.ev_cdct
JOIN eptm epr ON epr.epr_nrcntr = ev.ev_nrcntr
JOIN rgst rg ON cv.cv_stc = rg.rg_idrg
LEFT 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(at)guedesoft(dot)net - skype: guediz
http://guedesoft.net - http://planeta.postgresql.org.br

"Dickson S. Guedes" <listas(at)guedesoft(dot)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(at)guedesoft(dot)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(at)guedesoft(dot)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(at)guedesoft(dot)net - skype: guediz
http://guedesoft.net - http://planeta.postgresql.org.br

"Dickson S. Guedes" <listas(at)guedesoft(dot)net> writes:
> Em Sb, 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