Bug #47313 assert in check_key_in_view during CALL procedure
Submitted: 14 Sep 2009 15:30 Modified: 7 Mar 2010 1:43
Reporter: Matthias Leich Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Stored Routines Severity:S3 (Non-critical)
Version:5.4 OS:Any
Assigned to: Jon Olav Hauglid CPU Architecture:Any

[14 Sep 2009 15:30] Matthias Leich
Description:
The relevant source line in sql_view.cc is:
DBUG_ASSERT(table != 0 && view->field_translation != 0);

My script:
----------
CREATE TABLE t1 ( f1 INTEGER, PRIMARY KEY (f1));
CREATE  ALGORITHM = TEMPTABLE VIEW t2  AS SELECT * FROM t1;
delimiter |; CREATE PROCEDURE p1 () BEGIN UPDATE t2 SET f1 = 9 LIMIT 1; END| delimiter ;|
--error ER_NON_UPDATABLE_TABLE
CALL p1;
CREATE TEMPORARY TABLE IF NOT EXISTS t2 AS SELECT f1 FROM t1 AS A UNION SELECT * FROM t2;
# The next statement gets an assertion
--error ER_NON_UPDATABLE_TABLE
CALL p1;

Backtrace from mysql-next-bugfixing 2009-09-14:
-----------------------------------------------
Thread 1 (process 21397):
#0  0x00007f064cda9ce6 in pthread_kill () from /lib64/libpthread.so.0
#1  0x0000000000b58078 in my_write_core (sig=6) at stacktrace.c:309
#2  0x00000000006efb31 in handle_segfault (sig=6) at mysqld.cc:2738
#3  <signal handler called>
#4  0x00007f064bca55c5 in raise () from /lib64/libc.so.6
#5  0x00007f064bca6bb3 in abort () from /lib64/libc.so.6
#6  0x00007f064bc9e1e9 in __assert_fail () from /lib64/libc.so.6
#7  0x00000000008cfd9e in check_key_in_view (thd=0x166e808, view=0x16af8f0) at sql_view.cc:1773
#8  0x00000000007c946a in mysql_update (thd=0x166e808, table_list=0x16af8f0, fields=@0x16aeea8, values=@0x16af2b8, conds=0x0, order_num=0, order=0x0, limit=1, handle_duplicates=DUP_ERROR, ignore=false) at sql_update.cc:270
#9  0x00000000007045af in mysql_execute_command (thd=0x166e808) at sql_parse.cc:3105
#10 0x00000000008e540f in sp_instr_stmt::exec_core (this=0x16b0100, thd=0x166e808, nextp=0x424c3128) at sp_head.cc:2926
#11 0x00000000008e5635 in sp_lex_keeper::reset_lex_and_exec_core (this=0x16b0140, thd=0x166e808, nextp=0x424c3128, open_tables=false, instr=0x16b0100) at sp_head.cc:2751
#12 0x00000000008eb4ee in sp_instr_stmt::execute (this=0x16b0100, thd=0x166e808, nextp=0x424c3128) at sp_head.cc:2864
#13 0x00000000008e7728 in sp_head::execute (this=0x16adf90, thd=0x166e808) at sp_head.cc:1248
#14 0x00000000008e853a in sp_head::execute_procedure (this=0x16adf90, thd=0x166e808, args=0x1670d20) at sp_head.cc:1988
#15 0x0000000000708918 in mysql_execute_command (thd=0x166e808) at sql_parse.cc:4389
#16 0x000000000070a382 in mysql_parse (thd=0x166e808, inBuf=0x160b9a0 "CALL p1", length=7, found_semicolon=0x424c4f20) at sql_parse.cc:5946
#17 0x000000000070afa0 in dispatch_command (command=COM_QUERY, thd=0x166e808, packet=0x1607949 "CALL p1", packet_length=7) at sql_parse.cc:1062
#18 0x000000000070c444 in do_command (thd=0x166e808) at sql_parse.cc:744
#19 0x00000000006f99bf in handle_one_connection (arg=0x166e808) at sql_connect.cc:1163
#20 0x00007f064cda5040 in start_thread () from /lib64/libpthread.so.0
#21 0x00007f064bd4608d in clone () from /lib64/libc.so.6
#22 0x0000000000000000 in ?? ()

My environment:
---------------
- mysql-next-bugfixing 2009-09-14
  ./BUILD/compile-pentium64-debug-max
- Linux OpenSuSE 11.0 (64 Bit)
- Intel Core2Duo

How to repeat:
See above
[25 Sep 2009 13:13] Tor Didriksen
An even simpler test case:

delimiter |; 
CREATE PROCEDURE p1 () BEGIN UPDATE t1 SET f1 = 9 LIMIT 1; END| 
delimiter ;|

CREATE VIEW t1 AS SELECT 10 as f1;

--error ER_NON_UPDATABLE_TABLE
CALL p1;

CREATE TEMPORARY TABLE t1 (f1 int);

CALL p1;
[25 Sep 2009 13:28] Tor Didriksen
delimiter |; 
CREATE PROCEDURE p1 () BEGIN UPDATE t1 SET f1 = 9 LIMIT 1; END| 
delimiter ;|

CREATE VIEW t1 AS SELECT 10 as f1;

--error ER_NON_UPDATABLE_TABLE
CALL p1;

CREATE TEMPORARY TABLE t1 (f1 int);

--error ER_NON_UPDATABLE_TABLE
# MySQL 5.4.3-beta-debug returns error here
# MySQL 6.0.14-alpha-debug dumps core
CALL p1;

drop procedure p1;
drop view t1;
[14 Oct 2009 11:29] Matthias Leich
Some attempts on other source trees I have

assertion tree
---------------------------
no        mysql-trunk-signal 2009-09-23
no        mysql-5.1-bugteam  2009-10-06
[22 Oct 2009 14:29] 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/87781

3661 Jon Olav Hauglid	2009-10-22
      Bug #47313 assert in check_key_in_view during CALL procedure
      
      View definitions are inlined in a stored procedure when the procedure
      is fist called. This means that if a temporary table is later added
      with the same name as the view, the stored procedure will still
      use the view. This happens even if temporary tables normally shadow
      base tables/views.
      
      The reason for the assert was that even if the stored procedure
      referenced the view, open_table() still tried to open the
      temporary table. This "half view/half temporary table" state
      caused the assert.
      
      The bug was not present in 5.1 as open_table() is not called
      for the view there. This code was changed with the introduction 
      of MDL in order to properly lock the view and any objects it 
      refers to.
      
      This patch restores the 5.1 behavior by instructing open_table()
      to open base tables/views (using OT_BASE_ONLY) when reopening
      tables/views used by stored procedures.
      
      Test case added to sp.test. The test case also demonstrates
      the effect of sp cache invalidation between CALLs. The test case
      has been verified on the 5.1 and next-mr codebases.
[24 Oct 2009 13:29] Konstantin Osipov
Sent a review by email.
[26 Oct 2009 16:17] 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/88203

3673 Jon Olav Hauglid	2009-10-26
      Bug #47313 assert in check_key_in_view during CALL procedure
      
      View definitions are inlined in a stored procedure when the procedure
      is fist called. This means that if a temporary table is later added
      with the same name as the view, the stored procedure will still
      use the view. This happens even if temporary tables normally shadow
      base tables/views.
      
      The reason for the assert was that even if the stored procedure
      referenced the view, open_table() still tried to open the
      temporary table. This "half view/half temporary table" state
      caused the assert.
      
      The bug was not present in 5.1 as open_table() is not called
      for the view there. This code was changed with the introduction 
      of MDL in order to properly lock the view and any objects it 
      refers to.
      
      This patch fixes the problem by instructing open_table()
      to open base tables/views (using OT_BASE_ONLY) when reopening
      tables/views used by stored procedures. This also means that
      a prepared statement is no longer invalidated if a temporary
      table is created with the same name as a view used in the
      prepared statement.
      
      Test case added to sp.test. The test case also demonstrates
      the effect of sp cache invalidation between CALLs.
     @ mysql-test/t/ps_ddl.test
        Extended the VIEW->TEMPORARY TABLE transition test to cover not only
        merged views, but now also materialized views and views containing
        a reference to an information schema table. 
        
        Test also updated to reflect the change to prepared statement
        invalidatation.
[26 Oct 2009 16:19] Jon Olav Hauglid
Pushed to mysql-6.0-codebase (version 6.0.14-alpha).
Will be backported to mysql-next (version 5.5.0-beta).
[31 Oct 2009 8:19] Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20091031081410-qkxmjsdzjmj840aq) (version source revid:jon.hauglid@sun.com-20091026161648-s5ygxe9fn9ks8qov) (merge vers: 6.0.14-alpha) (pib:13)
[17 Nov 2009 23:16] Paul DuBois
Noted in 6.0.14 changelog.

If a temporary table was created with the same name as a view
referenced in a stored routine, routine execution could raise an
assertion. 

Setting report to NDI pending push into 5.5.x.
[10 Dec 2009 12:38] Jon Olav Hauglid
Pushed in mysql-next-4284 (5.6.0-beta).
[16 Feb 2010 16:47] Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20100216101445-2ofzkh48aq2e0e8o) (version source revid:kostja@sun.com-20091211154405-c9yhiewr9o5d20rq) (merge vers: 6.0.14-alpha) (pib:16)
[16 Feb 2010 16:57] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100216101208-33qkfwdr0tep3pf2) (version source revid:jon.hauglid@sun.com-20091210123718-t7858zpdq2daojno) (pib:16)
[17 Feb 2010 1:14] Paul DuBois
Setting report to Need Merge pending push of Celosia into release tree.
[6 Mar 2010 10:57] Bugs System
Pushed into 5.5.3-m3 (revid:alik@sun.com-20100306103849-hha31z2enhh7jwt3) (version source revid:vvaintroub@mysql.com-20100216221947-luyhph0txl2c5tc8) (merge vers: 5.5.99-m3) (pib:16)
[7 Mar 2010 1:43] Paul DuBois
Noted in 5.5.3 changelog.