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

[1 Aug 2005 14:32] a b
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;
[12 Aug 2005 16:25] Evgeny Potemkin
Typo in function name while creating view results in wrong view being created.
[12 Aug 2005 16:45] 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/internals/28229
[12 Aug 2005 18:43] Evgeny Potemkin
Fixed in 5.0.12, cset 1.1987
[16 Aug 2005 3:59] Mike Hillyer
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>

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.073 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.