ERROR: "failed to locate grouping columns" - Mailing list pgsql-hackers

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