Bug #27875 Having Filter Condition with Column Alaise Not Working
Submitted: 17 Apr 2007 7:00 Modified: 29 Sep 2007 12:27
Reporter: Saravanan Ramamoorthy Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server Severity:S1 (Critical)
Version:5.0.37- Community OS:Windows (2000 Professional)
Assigned to: CPU Architecture:Any

[17 Apr 2007 7:00] Saravanan Ramamoorthy 
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
[17 Apr 2007 8:17] Valeriy Kravchuk 
Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html:

"- ONLY_FULL_GROUP_BY

...
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."
[19 Jul 2007 19:50] William Stockall 
Since the column specified in the error message in this example actually IS an aggregate column, your response is not valid.

This problem is also occurring on server version 5.0.45 on CentOS 5.  Using column aliases from columns that actually ARE in the group by will also trigger the bug.
[19 Jul 2007 23:22] William Stockall 
Also tested on MySQL server version 5.1.20-beta-community-nt-debug running on Windows 2000.  Here is a simple example query to demonstrate the problem (using the mysql database):

select u1.user, u1.host, min(u2.host) 'lowest' from user u1 inner join user u2 on u1.user = u2.user where u1.user = 'root' group by u1.user, u1.host having u1.host = `lowest`;

This should just return one row where the host entry is alphabetically first.  What is actually returned is this:

ERROR 1463 (42000): non-grouping field 'lowest' is used in HAVING clause
[29 Sep 2007 12:27] Valeriy Kravchuk 
I can not repeat the latest simple test case with 5.1.21:

mysql> select version();
+-----------------------------+
| version()                   |
+-----------------------------+
| 5.1.21-beta-community-debug |
+-----------------------------+
1 row in set (0.08 sec)

mysql> use mysql;
Database changed
mysql> select u1.user, u1.host, min(u2.host) 'lowest' from user u1 inner join us
er u2 on u1.user
    -> = u2.user where u1.user = 'root' group by u1.user, u1.host having u1.host
 = `lowest`;
+------+------+--------+
| user | host | lowest |
+------+------+--------+
| root | %    | %      |
+------+------+--------+
1 row in set (0.20 sec)