Bug #10970 Views: dependence on temporary table allowed
Submitted: 30 May 2005 19:39 Modified: 24 Aug 2005 18:02
Reporter: Peter Gulutzan Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.7-beta-debug OS:Linux (SUSE 9.2)
Assigned to: Igor Babaev CPU Architecture:Any

[30 May 2005 19:39] Peter Gulutzan
Description:
I should not be able to create a view which depends on  a temporary table.
If I say "create view ... as select ... from temporary_table_name" I get an error -- good.
But if I say "create view ... as select function_name() ..." and function_name() refers
to a temporary table, I get no error -- bad.

How to repeat:
mysql> create temporary table t18 (s1 int);
Query OK, 0 rows affected (0.01 sec)

mysql> delimiter //
mysql> create function f18 () returns int return (select count(*) from t18)//
Query OK, 0 rows affected (0.00 sec)

mysql> create view v18 as select f18()//
Query OK, 0 rows affected (0.00 sec)

mysql> select * from v18//
+-------+
| f18() |
+-------+
|     0 |
+-------+
1 row in set (0.00 sec)
[30 May 2005 20:00] Miguel Solorzano
Thank you for the bug report.
[18 Aug 2005 5:19] 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/28434
[20 Aug 2005 4:55] Igor Babaev
ChangeSet
  1.1988 05/08/17 22:19:12 igor@rurik.mysql.com +4 -0
  view.test:
    Added a test case for bug #10970.
  view.result:
    Added a test case for bug #10970.
    Modified the error messages for error ER_VIEW_SELECT_TMPTABLE.
  sql_view.cc:
    Fixed bug #10970.
    In the function mysql_create_view if a view does not refer to
    any tables directly the variable table must be updated
    after the call of open_and_lock_tables.
  errmsg.txt:
    Modified the error messages for error ER_VIEW_SELECT_TMPTABLE
    (when fixing bug #10970).

The fix will appear in 5.0.12.
[24 Aug 2005 18:02] Mike Hillyer
Documented in 5.0.12 changelog:

<listitem><para>A view was allowed to depend on a function that referred to a temporary table. (Bug #10970)</para></listitem>
[24 Nov 2005 18:54] Konstantin Osipov
The bug has not been fully fixed: I can recreate the table as temporary, and the view will still work.

mysql> create temporary table t18 (s1 int);
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter //
mysql> create function f18 () returns int return (select count(*) from t18)//
Query OK, 0 rows affected (0.03 sec)

mysql> create view v18 as select f18()//
ERROR 1352 (HY000): View's SELECT refers to a temporary table 't18'
mysql> drop table t18//
Query OK, 0 rows affected (0.00 sec)

mysql> create table t18 (s1 int)//
Query OK, 0 rows affected (0.13 sec)

mysql> create view v18 as select f18()//
Query OK, 0 rows affected (0.00 sec)

mysql> select * from v18//
+-------+
| f18() |
+-------+
|     0 |
+-------+
1 row in set (0.01 sec)

mysqlql> drop table t18//
Query OK, 0 rows affected (0.00 sec)

mysql> create temporary table t18 (s1 int)//
Query OK, 0 rows affected (0.00 sec)

mysql> select * from v18//
+-------+
| f18() |
+-------+
|     0 |
+-------+
1 row in set (0.00 sec)