MySQL Bugs: #21037: View With group by -- sql-mode : Only_Full_Group

Bug #21037	View With group by -- sql-mode : Only_Full_Group_By
Submitted:	13 Jul 2006 14:09	Modified:	13 Aug 2006 18:52
Reporter:	saravanan Amoor	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: Views	Severity:	S1 (Critical)
Version:	5.0.17	OS:	Windows (XP)
Assigned to:		CPU Architecture:	Any

Description:
Hi,
        I have created a view using five tables. I have tested the select statement, its working fine and the view also created successfully. But when I select the records from the view it throws error like "RO.MSM_SHOP_NAME' isn't in GROUP BY". How it happens. Kindly clarify it.

Here is the View:

CREATE OR REPLACE VIEW view_pur_return_report as 
SELECT 
MSM_SHOP_NAME,
g.RETAIL_OUTLET_ID,
H.HQ_DIST_CODE,
M.MPR_PR_DT,
M.MPR_PR_NO,
H1.MDM_DIST_NAME,
SUM(M.MPR_ITEM_QTY) AS MPR_ITEM_QTY,
SUM(M.MPR_ITEM_AMOUNT) AS MPR_ITEM_AMOUNT,
M.MPR_REASON AS MPR_REASON 
FROM med_pur_return M ,hq_distributor_retail_info H ,hq_distributor_mast H1 ,hq_retail_outlet_info RO,hq_group_info g 
where g.GROUP_ID = M.GROUP_ID AND 
g.RETAIL_OUTLET_ID = H.RETAIL_OUTLET_ID AND
RO.RETAIL_OUTLET_ID=g.RETAIL_OUTLET_ID AND
M.RETAIL_DIST_CODE= H.RETAIL_DIST_CODE AND
H.HQ_DIST_CODE=H1.HQ_DIST_CODE 
GROUP BY M.MPR_PR_NO, MSM_SHOP_NAME, g.RETAIL_OUTLET_ID, H.HQ_DIST_CODE, M.MPR_PR_DT, M.MPR_PR_NO, H1.MDM_DIST_NAME, M.MPR_REASON;

med_pur_return:
==============
GROUP_ID, RETAIL_OUTLET_ID, MPR_PR_PREFIX, MPR_PR_NO, MPR_SNO, MPR_PR_DT, MPR_MRC_NO, RETAIL_ITEM_CODE, RETAIL_DIST_CODE, MPR_ITEM_UNIT, MPR_ITEM_QTY, MPR_ITEM_RATE, MPR_TAX_AMOUNT, MPR_DISC_AMOUNT, MPR_ITEM_AMOUNT, MPR_BATCH_NO, MPR_EXPIRY_DT, MPR_REASON, MPR_TAG, MPR_ADJ_NO, MPR_ADJ_DT, MPR_CN_NO, MPR_ADJ_AMT, MPR_VNO, MPR_RETURN_TAG, MPR_ITEM_ROWID

hq_distributor_retail_info:
==========================

RETAIL_DIST_CODE, RETAIL_OUTLET_ID, HQ_DIST_CODE, DISTRIBUTOR_CREDITS, DISTRIBUTOR_DEBITS, SRC_HQ_DIST_CODE, RETAIL_DIST_NAME

hq_distributor_mast
==================
HQ_DIST_CODE, MDM_DIST_NAME, MDM_SHORT_NAME, MDM_DIST_ADDR1, MDM_DIST_ADDR2, MDM_DIST_ADDR3, MDM_DIST_PIN, MDM_DIST_TEL_RES, MDM_DIST_TEL_OFF, MDM_DIST_CELLPH, MDM_DIST_EMAIL_ID, MDM_DIST_FAX, MDM_CONT_ADMIN, MDM_CONT_BUSINESS, MDM_DIST_CR_DAYS, MDM_TRADE_DISC_PER, MDM_DIST_TNGST_NO, MDM_CREDITS, MDM_DEBITS, MDM_STATUS, MDM_OLD_DIST_CODE, MDM_RELATION_START_DATE, MDM_RATING, MDM_DRUG_LICENSE_NO, MDM_TIN_VAT_NO, MDM_URL, SRC_RETAIL_OUTLET_ID

hq_retail_outlet_info
====================

HQ_DIST_CODE, MDM_DIST_NAME, MDM_SHORT_NAME, MDM_DIST_ADDR1, MDM_DIST_ADDR2, MDM_DIST_ADDR3, MDM_DIST_PIN, MDM_DIST_TEL_RES, MDM_DIST_TEL_OFF, MDM_DIST_CELLPH, MDM_DIST_EMAIL_ID, MDM_DIST_FAX, MDM_CONT_ADMIN, MDM_CONT_BUSINESS, MDM_DIST_CR_DAYS, MDM_TRADE_DISC_PER, MDM_DIST_TNGST_NO, MDM_CREDITS, MDM_DEBITS, MDM_STATUS, MDM_OLD_DIST_CODE, MDM_RELATION_START_DATE, MDM_RATING, MDM_DRUG_LICENSE_NO, MDM_TIN_VAT_NO, MDM_URL, SRC_RETAIL_OUTLET_ID

hq_group_info:
=============
GROUP_ID, RETAIL_OUTLET_ID, MC_ID, MD_ID

With Regards

Saravanan.A.R.

arsaravanan@gofrugaltech.com
amursaran@yahoo.com

How to repeat:
in text mode

Thank you for a problem report. It is not a bug, according to the manual (http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html):

"ONLY_FULL_GROUP_BY

Do not allow queries for which the SELECT list refers to non-aggregated columns that are not named in the GROUP BY clause. The following query is invalid with this mode enabled because address is not named in the GROUP BY clause:

SELECT name, address, MAX(age) FROM t GROUP BY name;

As of MySQL 5.0.23, this mode also restricts references to non-aggregated columns in the HAVING clause that are not named in the GROUP BY clause."

If it worked in select, with this sql mode, in 5.0.17, it can be because of a bug in that old 5.0.17 version. Please, try to repeat with a newer version, 5.0.22, and inform about the results.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".