[HACKERS] ERROR: "failed to locate grouping columns"

[Dickson S. Guedes]

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



-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers