MySQL Bugs: #12298: Cannot execute view but the underlying select statement

Bug #12298	Cannot execute view but the underlying select statement
Submitted:	1 Aug 2005 14:32	Modified:	16 Aug 2005 3:59
Reporter:	a b	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	5.0.9-beta-standard/BK source	OS:	Linux (SuSE Linux 8.2)
Assigned to:	Evgeny Potemkin	CPU Architecture:	Any

Description:
I can execute a select statement and create a view from it, but on executing the view I get this: 
ERROR 1356 (HY000): View 'kurse.C2_Performance' references invalid table(s) or column(s) or function(s)

How to repeat:
In a database where you have all privileges enter:

CREATE TABLE `c2_transaktionen` (
  `quelle` varchar(30) collate latin1_german2_ci NOT NULL,
  `action` char(4) collate latin1_german2_ci NOT NULL,
  `qty` int(11) NOT NULL,
  `symbol` char(5) collate latin1_german2_ci NOT NULL,
  `price` float NOT NULL,
  `opendate` datetime NOT NULL,
  `closeaction` char(4) collate latin1_german2_ci NOT NULL,
  `closeqty` int(11) NOT NULL,
  `closeprice` float NOT NULL,
  `closedate` datetime NOT NULL,
  `cash` float NOT NULL,
  `profit_loss` float NOT NULL,
  UNIQUE KEY `action` (`action`,`qty`,`symbol`,`price`,`opendate`,`closeaction`,`closeqty`,`closeprice`,`closedate`,`cash`,`profit_loss`),
  KEY `quelle` (`quelle`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci;

CREATE TABLE `c2_systems` (
  `id` int(11) NOT NULL,
  `name` varchar(50) collate latin1_german2_ci NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci;

select id,name,timestampdiff(day,max(closedate),now()) as letzteAktion,timestampdiff(day,min(opendate),max(closedate)) as tage,sum(profit_loss) as gewinn, sum(profit_loss) / 1000 *  360 / timestampdiff(day,min(opendate),max(closedate)) as prozent from c2_transaktionen,c2_systems where quelle=id group by quelle having letzteAktion < 10 order by prozent;

create view C2_Performance as
select id,name,timestampdiff(day,max(closedate),now()) as letzteAktion,timestampdiff(day,min(opendate),max(closedate)) as tage,sum(profit_loss) as gewinn, sum(profit_loss) / 1000 *  360 / timestampdiff(day,min(opendate),max(closedate)) as prozent from c2_transaktionen,c2_systems where quelle=id group by quelle having letzteAktion < 10 order by prozent;

select * from C2_Performance;

Typo in function name while creating view results in wrong view being created.

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/internals/28229

Fixed in 5.0.12, cset 1.1987

Documented in 5.0.12 changelog.

<listitem><para>
 Creating a view that included the <literal>TIMESTAMPDIFF()</literal> function resulted in invalid view. (Bug #12298)
</para></listitem>