Bug #10970 Views: dependence on temporary table allowed

• Submitted: 30 May 2005 19:39
• Modified: 24 Aug 2005 18:02
• Reporter: Peter Gulutzan
• Status: Closed
• 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] MySQL Verification Team

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 [e-mail] +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)