MySQL Bugs: #29050: Creation of a legal stored procedure fails if a database is not selected prior

Bug #29050	Creation of a legal stored procedure fails if a database is not selected prior
Submitted:	12 Jun 2007 13:18	Modified:	18 Jul 2007 19:08
Reporter:	Harrison Fisk	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S2 (Serious)
Version:	5.0.42	OS:	Any
Assigned to:	Konstantin Osipov	CPU Architecture:	Any
Tags:	backport_050044SP1, bfsm_2007_06_21, bfsm_2007_06_28, No database selected, regression

Description:
When you create a stored procedure using an unqualified table name inside of the body without first selecting any database, then you get an error, No database selected.  If you then 'use' any database, you will no longer get that error.

This might be related to bug #28551, but it has different symptoms and possibly causes.

This behavior was introduced in 5.0.23.  Prior versions allow it and it functions correctly.

How to repeat:
-- do not select a database, just login to mysql

CREATE TABLE test.tbl (id int);

delimiter ||

-- fails with error

CREATE PROCEDURE test.p1 () BEGIN SELECT * FROM tbl; END||

-- works if you use *any* database
-- 'tbl' is still referred to the 'test' database

use mysql

CREATE PROCEDURE test.p1() BEGIN SELECT * FROM tbl; END||

delimiter ;

Suggested fix:
Allow the statement to be executed without having to choose a database first.

See related Bug#28318

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/30341

ChangeSet@1.2515, 2007-07-05 03:18:23+04:00, kostja@bodhi.(none) +6 -0
  A fix and a test case for Bug#29050 Creation of a legal stored procedure
  fails if a database is not selected prior.
  
  The problem manifested itself when a user tried to
  create a routine that had non-fully-qualified identifiers in its bodies
  and there was no current database selected.
  
  This is a regression introduced by the fix for Bug 19022:
  
  The patch for Bug 19022 changes the code to always produce a warning
  if we can't resolve the current database in the parser. 
  In this case this was not necessary, since even though the produced
  parsed tree was incorrect, we never re-use sphead
  that was obtained at first parsing of CREATE PROCEDURE.
  The sphead that is anyhow used is always obtained through db_load_routine,
  and there we change the current database to sphead->m_db before
  calling yyparse.
  
  The idea of the fix is to resolve the current database directly using 
  lex->sphead->m_db member when parsing a stored routine body, when
  such is present.
  
  This patch removes the need to reset the current database
  when loading a trigger or routine definition into SP cache.
  The redundant code will be removed in 5.1.

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/30349

ChangeSet@1.2515, 2007-07-05 11:34:04+04:00, kostja@bodhi.(none) +8 -0
  A fix and a test case for Bug#29050 Creation of a legal stored procedure
  fails if a database is not selected prior.
  
  The problem manifested itself when a user tried to
  create a routine that had non-fully-qualified identifiers in its bodies
  and there was no current database selected.
  
  This is a regression introduced by the fix for Bug 19022:
  
  The patch for Bug 19022 changes the code to always produce a warning
  if we can't resolve the current database in the parser. 
  In this case this was not necessary, since even though the produced
  parsed tree was incorrect, we never re-use sphead
  that was obtained at first parsing of CREATE PROCEDURE.
  The sphead that is anyhow used is always obtained through db_load_routine,
  and there we change the current database to sphead->m_db before
  calling yyparse.
  
  The idea of the fix is to resolve the current database directly using 
  lex->sphead->m_db member when parsing a stored routine body, when
  such is present.
  
  This patch removes the need to reset the current database
  when loading a trigger or routine definition into SP cache.
  The redundant code will be removed in 5.1.

Actually queued it into 5.0 (a small patch).

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/30985

ChangeSet@1.2522, 2007-07-16 23:37:02+04:00, kostja@bodhi.(none) +1 -0
  Bug#29050 Creation of a legal stored procedure fails if a database is not 
  selected prior: ensure the fix also works for information_schema
  tables.

Pushed into 5.0.48

Pushed into 5.1.21-beta

Noted in 5.0.48, 5.1.21 changelog.

Creation of a legal stored procedure could fail if no default database 
had been selected.

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/32178

ChangeSet@1.2490, 2007-08-07 00:54:47-06:00, tsmith@ramayana.hindu.god +8 -0
  Backport fix for bug #29050, Creation of a legal stored procedure fails if a database is not selected prior