Bug #30689 Wrong content in I_S.VIEWS.VIEW_DEFINITION if VIEW is based on I_S
Submitted: 29 Aug 2007 11:04 Modified: 5 Nov 2007 3:02
Reporter: Matthias Leich Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Information schema Severity:S2 (Serious)
Version:5.1 OS:Any
Assigned to: Sergei Glukhov CPU Architecture:Any

[29 Aug 2007 11:04] Matthias Leich 
Description:
If a view is based on a select from an 
information_schema table, than the corresponding
view definition presented in
INFORMATION_SCHEMA.VIEWS might be wrong.
I assume that this bug does not depend on the OS.
This bug does not occur in MySQL 5.0.

My environment:
- Linux OpenSuSE 10.2 64 Bit, Intel Core2Duo (64 Bit)
- MySQL compiled from source with 
  compile-pentium-debug-max
- mysql-5.1-build last ChangeSet@1.2544, 2007-08-27

I set P1 because this bug causes that the testsuite
funcs_1 , data dictionary tests fail.

How to repeat:
--disable_warnings
DROP VIEW IF EXISTS v1;
--enable_warnings
CREATE VIEW v1
AS SELECT *
FROM INFORMATION_SCHEMA.TABLES;
SHOW CREATE VIEW v1;
# Output:
# View Create View ... 
# v1   CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost`
#      .... SQL SECURITY DEFINER VIEW `test`.`v1` AS select 
#      .....
#      statement seems to be ok
SELECT VIEW_DEFINITION FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_NAME = 'v1';
VIEW_DEFINITION
SELECT *  # <-- That's all, no FROM INFORMATION_SCHEMA.TABLES etc.
[18 Sep 2007 10:20] 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/34368

ChangeSet@1.2557, 2007-09-18 15:15:30+05:00, gluh@mysql.com +5 -0
  Bug#30689 Wrong content in I_S.VIEWS.VIEW_DEFINITION if VIEW is based on I_S
  view_body_utf8 atribute type is changed to 'escaped string'
[28 Sep 2007 11:08] 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/34634

ChangeSet@1.2573, 2007-09-28 16:02:43+05:00, gluh@mysql.com +5 -0
  Bug#30689 Wrong content in I_S.VIEWS.VIEW_DEFINITION if VIEW is based on I_S
  view_body_utf8 attribute type is changed to 'escaped string'
[29 Oct 2007 8:47] Bugs System 
Pushed into 5.1.23-beta
[29 Oct 2007 8:51] Bugs System 
Pushed into 6.0.4-alpha
[5 Nov 2007 3:02] Paul DuBois 
Noted in 5.1.23, 6.0.4 changelogs.

INFORMATION_SCHEMA.VIEWS.VIEW_DEFINITION was incorrect for views that
were defined to select from other INFORMATION_SCHEMA tables.