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@guedesoft.net - skype: guediz
http://guedesoft.net - http://planeta.postgresql.org.br

Search Discussions

  • Tom Lane at Mar 8, 2009 at 12:38 am

    "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
  • Dickson S. Guedes at Mar 8, 2009 at 1:05 am

    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
  • Dickson S. Guedes at Mar 8, 2009 at 8:29 pm

    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
  • Tom Lane at Mar 8, 2009 at 8:34 pm

    "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
  • Tom Lane at Mar 8, 2009 at 11:04 pm
    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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-hackers @
categoriespostgresql
postedMar 8, '09 at 12:28a
activeMar 8, '09 at 11:04p
posts6
users2
websitepostgresql.org...
irc#postgresql

2 users in discussion

Tom Lane: 3 posts Dickson S. Guedes: 3 posts

People

Translate

site design / logo © 2022 Grokbase