Bug #21809 Error 1356 while selecting from view with grouping though underlying select OK.
Submitted: 24 Aug 2006 12:17 Modified: 24 Jan 2007 0:32
Reporter: Andrey Kazachkov Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Views Severity:S2 (Serious)
Version:5.0.25-BK, 5.0.24 OS:Linux (Linux, Windows)
Assigned to: Georgi Kodinov CPU Architecture:Any

[24 Aug 2006 12:17] Andrey Kazachkov
Description:
Error 1356 occurs while selecting from view with grouping and an aggregation function as argument of user-defined function. 

It looks like bug #12298. 

How to repeat:
MySQL server was installed from mysql-5.0.24-win32.zip. 

Following script reproduces the behaviour. It worked in version 5.0.22. 

DELIMITER GO

use mysql
GO

DROP DATABASE IF EXISTS `my_test_db`
GO

CREATE DATABASE `my_test_db` /*!40100 DEFAULT CHARACTER SET ascii */
GO

USE my_test_db
GO

DROP TABLE IF EXISTS t_a
GO

CREATE TABLE t_a(
	nId int PRIMARY KEY auto_increment,
	wstrName VARCHAR(256) UNICODE,
	tmCreated DATETIME NOT NULL
)
GO

DROP FUNCTION IF EXISTS AK_DATEDIFF_SEC
GO

CREATE FUNCTION AK_DATEDIFF_SEC(
    startdate	datetime,
    enddate	datetime)
RETURNS int
    DETERMINISTIC
BEGIN
    DECLARE diff int;
    if startdate IS NULL OR enddate IS NULL THEN
    RETURN NULL;
    END IF;
    SET diff = 	UNIX_TIMESTAMP(enddate) - UNIX_TIMESTAMP(startdate);
    RETURN diff;
END
GO

DROP FUNCTION IF EXISTS AK_DATEDIFF_DAY
GO

CREATE FUNCTION AK_DATEDIFF_DAY(
    startdate	datetime,
    enddate	datetime) 
RETURNS int
    DETERMINISTIC
BEGIN
    RETURN AK_DATEDIFF_SEC(startdate, enddate)/86400;
END
GO

DROP VIEW IF EXISTS v_t_a 
GO

CREATE VIEW v_t_a 
AS 
SELECT 	`nId`  AS `nId`  , 
		`AK_DATEDIFF_DAY`(MIN(t_a.tmCreated), UTC_TIMESTAMP( )) AS `MinCreated`  
FROM `t_a` 
GROUP BY `t_a`.`nId` 
GO

SELECT * FROM v_t_a
GO

ERROR 1356 (HY000): View 'my_test_db.v_t_a' references invalid table(s) or colum
n(s) or function(s) or definer/invoker of view lack rights to use them

Though select without view works. 

SELECT 	`nId`  AS `nId`  , 
		`AK_DATEDIFF_DAY`(MIN(t_a.tmCreated), UTC_TIMESTAMP( )) AS `MinCreated`  
FROM `t_a` 
GROUP BY `t_a`.`nId` 
GO
[24 Aug 2006 12:49] Valeriy Kravchuk
Thank you for a bug report. Verified just as described with 5.0.25-BK on Linux.
[19 Oct 2006 11:09] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/13970

ChangeSet@1.2302, 2006-10-19 14:09:24+03:00, gkodinov@macbook.gmz +8 -0
  Bug #21809: Error 1356 while selecting from view with grouping though underlying
              select OK.
  The SQL parser was using Item::name to transfer user defined function attributes
  to the user defined function. It was not distinguishing between user defined 
  function call arguments and stored procedure call arguments. Setting Item::name 
  was causing Item::print() method to print the argument as quoted identifier and 
  caused views that reference such functions (and rely on Item::print() for the 
  text of the view to store) to throw an undefined identifier error.
  Fixed by making a designated member of Item for user defined functions 
  attributes and properly detecting using AS clause in stored procedure arguments
  as an error.
[24 Oct 2006 12:27] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/14254

ChangeSet@1.2302, 2006-10-24 15:26:41+03:00, gkodinov@macbook.gmz +8 -0
  Bug #21809: Error 1356 while selecting from view with grouping though underlying
              select OK.
  The SQL parser was using Item::name to transfer user defined function attributes
  to the user defined function (udf). It was not distinguishing between user defined 
  function call arguments and stored procedure call arguments. Setting Item::name 
  was causing Item_ref::print() method to print the argument as quoted identifiers 
  and caused views that reference aggregate functions as udf call arguments (and 
  rely on Item::print() for the text of the view to store) to throw an undefined 
  identifier error.
  Overloaded Item_ref::print to print aggregate functions as such when printing
  the references to aggregate functions taken out of context by split_sum_func2()
  Fixed the parser to properly detect using AS clause in stored procedure arguments
  as an error.
  Fixed printing the arguments of udf call to print properly the udf attribute.
[27 Nov 2006 17:15] Georgi Kodinov
Pushed in 5.0.32/5.1.14-beta
[24 Jan 2007 0:32] Paul Dubois
Noted in 5.0.32, 5.1.14 changelogs.

In some cases, the parser failed to distinguish a user-defined
function from a stored function.
[21 Mar 2011 23:10] Jian Xu
Confirm this bug is still there on mysql 5.1.49
 suppose the schema is V(a, b, c, d) doing
select *, c/d from V is ok
but 
select c/d from V results in Error 1356

same Error 1356 for 
select a, b, avg(c/d) from V group by a, b