Description:
This Query is working fine with 5.0.21 community
SELECT SQL_CALC_FOUND_ROWS
hm.HQ_ITEM_CODE,MAX(hm.ITEM_NAME) as ITEM_NAME,
sum(case when i.retail_outlet_id='2' then (case when i.MID_TEMP_TAG='Y' then i.mid_temp_qty else 0 end)+ i.mid_bal_stock end) as Outlet0,
sum(case when i.retail_outlet_id='3' then (case when i.MID_TEMP_TAG='Y' then i.mid_temp_qty else 0 end)+ i.mid_bal_stock end) as Outlet1,
sum(case when i.retail_outlet_id='4' then (case when i.MID_TEMP_TAG='Y' then i.mid_temp_qty else 0 end)+ i.mid_bal_stock end) as Outlet2,
1 AS DUMMY_FIELD from hq_itemmaster hm LEFT JOIN med_item_dtl i ON (hm.HQ_ITEM_CODE=i.RETAIL_ITEM_CODE) where i.RETAIL_ITEM_CODE is not null
group by hm.HQ_ITEM_CODE having ( (Outlet0>0) OR (Outlet1>0) OR (Outlet2>0) ) ORDER by ITEM_NAME ASC LIMIT 0,25
But the same Query is not working with 5.0.37 - Community
It shows error like "non-grouping field 'Outlet0' is used in HAVING clause" Error no:1463
Note: In Both Version SQL-MODE IS GIVEN AS 'ONLY_FULL_GROUP_BY'
How to repeat:
TABLE STRUCUTRE FOR THE ABOVE QUERY
CREATE TABLE `hq_itemmaster` (
`HQ_ITEM_CODE` int(10) unsigned NOT NULL auto_increment,
`MAIN_CAT_CODE` int(5) NOT NULL,
`HQ_MFR_CODE` varchar(100) default NULL,
`MIGM_GENERIC_CODE` int(10) unsigned default NULL,
`ITEM_NAME` varchar(100) default NULL,
`SHORT_NAME` varchar(100) default NULL,
`ITEM_TYPE` varchar(10) default NULL,
`ITEM_BUSINESS_STATUS` varchar(30) default NULL,
`ITEM_ALIAS_LIST` varchar(100) default NULL,
`ITEM_RETAIL_VERTICAL` varchar(30) default NULL,
`ITEM_CREATION_DATE` datetime default NULL,
`ITEM_PURCHASE_TYPE` varchar(30) default NULL,
`ITEM_INCLUSIVETAX` varchar(30) default NULL,
`ITEM_TAX_PERCENT` int(10) unsigned default NULL,
`ITEM_STRENGTH_OR_VOLUME` int(10) unsigned default NULL,
`ITEM_STRENGTH_OR_VOLUME_UNIT` varchar(30) default NULL,
`ITEM_ROUTE_COMBO` varchar(30) default NULL,
`ITEM_FORM` varchar(30) default NULL,
`ITEM_PACKING` varchar(30) default NULL,
`ITEM_ITEM_PER_UNIT` int(10) unsigned default '1',
`ITEM_PRICE_CODE` int(10) unsigned default NULL,
`SRC_RETAIL_OUTLET_ID` int(10) unsigned default NULL,
`RST_TAX` double(8,2) default NULL,
`RST_SURRST_TAX` double(8,2) default NULL,
`ITEM_BULK_CODE` int(10) unsigned default NULL,
`ITEM_REPACK_CONVERTION` double(8,2) default NULL,
`DECIMAL_POINT` double(8,2) default NULL,
`GRIND_PER` double(8,2) default NULL,
`BE_CONF` varchar(2) default NULL,
`PREPARED` varchar(2) default NULL,
`TRADE_CONF` varchar(2) default NULL,
`EANCODE` varchar(13) default NULL,
`CRT_BY` varchar(80) default NULL,
`CRT_DT_TIME` datetime default NULL,
`UPD_BY` varchar(80) default NULL,
`UPD_DT_TIME` datetime default NULL,
`REVISION_NO` int(5) default NULL,
`STATUS` varchar(80) default 'ACTIVE',
`BANNED_DATE` datetime default NULL,
`BANNED_STATUS` varchar(50) default NULL,
`S_TAX_ON` varchar(50) default NULL,
`MAIN_DIST_CODE` varchar(80) default NULL,
`SALES_TAX` double(8,2) default NULL,
`C_SALES_TAX` double(8,2) default NULL,
`SURCHARGE_TAX` double(8,2) default NULL,
`RESALE_TAX` double(8,2) default NULL,
`TRANSACTION_ID` int(10) unsigned NOT NULL default '1',
`SMALLIMGURL` varchar(80) default 'smallimg.gif',
`BIGIMGURL` varchar(80) default 'bigimg.gif',
`FEATURED` varchar(1) default 'N',
`MIH_VALIDMRP` int(10) default NULL,
PRIMARY KEY (`HQ_ITEM_CODE`),
UNIQUE KEY `hq_itemmaster_uidx_1` (`ITEM_NAME`),
KEY `hq_itemmaster_FKIndex1` (`MIGM_GENERIC_CODE`),
KEY `iname_index` (`ITEM_NAME`),
KEY `MAIN_CAT_CODE` (`MAIN_CAT_CODE`),
KEY `hq_itemmaster_idx_5` (`MAIN_DIST_CODE`),
KEY `hq_itemmaster_idx_2` (`HQ_MFR_CODE`),
CONSTRAINT `hq_itemmaster_ibfk_1` FOREIGN KEY (`MIGM_GENERIC_CODE`) REFERENCES `med_item_generic_mast` (`MIGM_GENERIC_CODE`) ON DELETE CASCADE,
CONSTRAINT `hq_itemmaster_ibfk_2` FOREIGN KEY (`MAIN_CAT_CODE`) REFERENCES `hq_category_mast` (`MAIN_CAT_CODE`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPACT
CREATE TABLE `med_item_dtl` (
`RETAIL_OUTLET_ID` int(10) unsigned NOT NULL default '0',
`GROUP_ID` int(10) unsigned default NULL,
`RETAIL_ITEM_CODE` int(10) unsigned NOT NULL default '0',
`MID_ROW_ID` int(10) unsigned NOT NULL default '0',
`MID_RECEIPT_QTY` double(20,4) unsigned default NULL,
`MID_RECEIPT_UNIT` int(10) unsigned default NULL,
`MID_BAL_STOCK` double(20,2) default NULL,
`MID_BATCH_DT` datetime default NULL,
`MID_EXPIRY_DT` datetime default NULL,
`MID_BATCH_NO` varchar(15) NOT NULL default '',
`MID_MRP` double(20,4) default NULL,
`MID_SALE_TAX` double(20,4) default NULL,
`MID_PUR_RATE` double(20,4) default NULL,
`MID_PUR_PRICE` double(20,4) default NULL,
`MID_PUR_TAX` double(20,4) default NULL,
`MID_MRC_PREFIX` varchar(2) default NULL,
`MID_MRC_NO` varchar(10) NOT NULL default '0',
`MID_MRC_DT` datetime default NULL,
`RETAIL_DIST_CODE` int(11) default NULL,
`MID_FREE_TAG` varchar(1) default NULL,
`MID_TEMP_QTY` double(20,4) unsigned default NULL,
`MID_TEMP_TAG` varchar(1) default NULL,
`MID_MAX_RATE` double(20,4) default NULL,
`MID_FREE_QTY` double(20,4) default NULL,
`MID_PUR_REF` int(10) unsigned default NULL,
`MID_PUR_DT` datetime default NULL,
`MID_LAST_SALE_DT` datetime default NULL,
`PUR_DIST_CODE` varchar(30) default NULL,
`MID_SOURCE_OUTLET` varchar(30) default NULL,
`MID_HOME_OUTLET` varchar(30) default NULL,
`mid_pur_disc_perc` double(20,4) default NULL,
`mid_pur_disc_amt` double(20,4) default NULL,
`mid_dmrc_no` varchar(15) default NULL,
`MID_SALE_TAX_PERC` double(20,4) default NULL,
`MID_PUR_TAX_PERC` double(20,4) default NULL,
`MID_PROFIT_PERC` double(20,4) default NULL,
`CHK` int(5) default NULL,
`MID_PUR_MRC_NO` varchar(10) default '0',
PRIMARY KEY (`RETAIL_OUTLET_ID`,`RETAIL_ITEM_CODE`,`MID_ROW_ID`,`MID_BATCH_NO`,`MID_MRC_NO`),
KEY `med_item_dtl_idx_4` (`MID_PUR_DT`),
KEY `med_item_dtl_idx_5` (`RETAIL_OUTLET_ID`,`MID_ROW_ID`),
KEY `med_item_dtl_idx_3` (`MID_PUR_REF`),
KEY `med_item_dtl_idx_1` (`RETAIL_OUTLET_ID`),
KEY `med_item_dtl_idx_2` (`RETAIL_ITEM_CODE`),
KEY `med_item_dtl_idx_6` (`MID_LAST_SALE_DT`),
CONSTRAINT `med_item_dtl_ibfk_1` FOREIGN KEY (`RETAIL_OUTLET_ID`, `RETAIL_ITEM_CODE`) REFERENCES `hq_item_retail_info` (`RETAIL_OUTLET_ID`, `RETAIL_ITEM_CODE`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPACT