Bug #13622 VIEW depending on information_schema.TRIGGERS claims it does not exist
Submitted: 29 Sep 2005 20:23 Modified: 20 Nov 2005 4:49
Reporter: Roland Bouman Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Views Severity:S3 (Non-critical)
Version:5.0.13/BK source OS:Linux (Linux Suse, WinXP prof)
Assigned to: Evgeny Potemkin CPU Architecture:Any

[29 Sep 2005 20:23] Roland Bouman
Description:
A view created from a UNION ALL query does not see the information_schema.TRIGGERS system view depending upon the order of the union members.
When the query depending on the TRIGGERS system view appears as the first operand of the UNION ALL, the view is created, and a SELECT query on the view succeeds. However, when the query depending on the TRIGGERS system view appears as the second operand of the UNION ALL, the SELECT  query on the view fails, and returns the following error:

ERROR 1109 (42S02): Unknown table 'triggers' in field list

It was expected that the result would be the same, regardless of the order of the UNION ALL operands. 

How to repeat:
(I tried to simplify the query, but now luck yet, you'll have to do it with this for now, sorry):

use test;

create or replace
view 
schema_objects (
    db
,   name
,   type
,   subtype
,   created
,   comment
)
as
select  table_schema     
,       table_name       
,       table_type
,       engine
,       create_time      
,       table_comment
from    information_schema.tables
union all
select  trigger_schema   
,       trigger_name     
,       _utf8'trigger'
,       concat(
            action_timing
        ,   _utf8' '
        ,   event_manipulation
        ,   _utf8' '
        ,   _utf8'FOR EACH '
        ,   action_orientation
        )
,       created       
,       _utf8''
from    information_schema.triggers
union all
select  routine_schema
,       routine_name
,       _utf8'routine'
,       routine_type
,       created
,       routine_comment
from    information_schema.routines
;

select * from schema_objects;

/*
This gives me:

ERROR 1109 (42S02): Unknown table 'triggers' in field list

However, the equivalent view definition:

*/
create or replace
view 
schema_objects (
    db
,   name
,   type
,   subtype
,   created
,   comment
)
as
select  table_schema     
,       table_name       
,       table_type
,       engine
,       create_time      
,       table_comment
from    information_schema.tables
union all
select  trigger_schema   
,       trigger_name     
,       _utf8'trigger'
,       concat(
            action_timing
        ,   _utf8' '
        ,   event_manipulation
        ,   _utf8' '
        ,   _utf8'FOR EACH '
        ,   action_orientation
        )
,       created       
,       _utf8''
from    information_schema.triggers
union all
select  routine_schema
,       routine_name
,       _utf8'routine'
,       routine_type
,       created
,       routine_comment
from    information_schema.routines
;

select * from schema_objects;

/*
works fine. What is the matter here?
*/

Suggested fix:
The view should work regardless of the order of the UNION ALL operands.
[29 Sep 2005 20:27] Roland Bouman
So sorry! My working (2nd) example should read this:

view
schema_objects (
    db
,   name
,   type
,   subtype
,   created
,   comment
)
as
select  trigger_schema  
,       trigger_name    
,       _utf8'trigger'
,       concat(
            action_timing
        ,   _utf8' '
        ,   event_manipulation
        ,   _utf8' '
        ,   _utf8'FOR EACH '
        ,   action_orientation
        )
,       created      
,       _utf8''
from    information_schema.triggers
union all
select  table_schema    
,       table_name      
,       table_type
,       engine
,       create_time     
,       table_comment
from    information_schema.tables
union all
select  routine_schema
,       routine_name
,       _utf8'routine'
,       routine_type
,       created
,       routine_comment
from    information_schema.routines
;
[30 Sep 2005 0:00] Miguel Solorzano
miguel@hegel:~/dbs/5.0> bin/mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5 to server version: 5.0.14-rc-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create or replace
    -> view 
    -> schema_objects (
    ->     db
    -> ,   name
    -> ,   type
    -> ,   subtype
    -> ,   created
    -> ,   comment
    -> )
    -> as
    -> select  table_schema     
    -> ,       table_name       
    -> ,       table_type
    -> ,       engine
    -> ,       create_time      
    -> ,       table_comment
    -> from    information_schema.tables
    -> union all
    -> select  trigger_schema   
    -> ,       trigger_name     
    -> ,       _utf8'trigger'
    -> ,       concat(
    ->             action_timing
    ->         ,   _utf8' '
    ->         ,   event_manipulation
    ->         ,   _utf8' '
    ->         ,   _utf8'FOR EACH '
    ->         ,   action_orientation
    ->         )
    -> ,       created       
    -> ,       _utf8''
    -> from    information_schema.triggers
    -> union all
    -> select  routine_schema
    -> ,       routine_name
    -> ,       _utf8'routine'
    -> ,       routine_type
    -> ,       created
    -> ,       routine_comment
    -> from    information_schema.routines
    -> ;
Query OK, 0 rows affected (0.01 sec)

mysql> 
mysql> select * from schema_objects;
ERROR 1109 (42S02): Unknown table 'triggers' in field list

mysql> select  table_schema     
    -> ,       table_name       
    -> ,       table_type
    -> ,       engine
    -> ,       create_time      
    -> ,       table_comment
    -> from    information_schema.tables
    -> union all
    -> select  trigger_schema   
    -> ,       trigger_name     
    -> ,       _utf8'trigger'
    -> ,       concat(
    ->             action_timing
    ->         ,   _utf8' '
    ->         ,   event_manipulation
    ->         ,   _utf8' '
    ->         ,   _utf8'FOR EACH '
    ->         ,   action_orientation
    ->         )
    -> ,       created       
    -> ,       _utf8''
    -> from    information_schema.triggers
    -> union all
    -> select  routine_schema
    -> ,       routine_name
    -> ,       _utf8'routine'
    -> ,       routine_type
    -> ,       created
    -> ,       routine_comment
    -> from    information_schema.routines\G
*************************** 1. row ***************************
 table_schema: information_schema
   table_name: CHARACTER_SETS
   table_type: SYSTEM VIEW
       engine: MEMORY
  create_time: NULL
table_comment: 
*************************** 2. row ***************************

<cut>

       engine: FUNCTION
  create_time: 2005-09-29 11:06:14
table_comment: 
64 rows in set (0.18 sec)
[30 Sep 2005 0:06] Roland Bouman
BTW, I'm running MySQL on WinXP Professional
[17 Oct 2005 20:02] Roland Bouman
I think I have found some more information that could help solve this, or at least pinpoint the problem. 

It seems that the CONCAT is the troublemaker, not the information_schema.triggers table:

create or replace
view
schema_objects (
    schema_name
,   object_type
,   object_name
,   object_subtype
,   created
,   comment
)
as
select   table_schema
,        table_type
,        table_name
,        engine
,        create_time
,        table_comment
from     information_schema.tables
union all
select   routine_schema
,        routine_type
,        routine_name
,        concat(
             if(is_deterministic='NO','NOT','')
         ,   _utf8' DETERMINISTIC '
         ,   sql_data_access
         ,   ' SQL SECURITY '
         ,   security_type
         )
,        created
,        routine_comment
from     information_schema.routines
;

mysql> select * from schema_objects;
ERROR 1109 (42S02): Unknown table 'routines' in field list

What's more, the error can be prevented from occurring by adding an alias to the CONCAT:

create or replace
view
schema_objects (
    schema_name
,   object_type
,   object_name
,   object_subtype
,   created
,   comment
)
as
select   table_schema
,        table_type
,        table_name
,        engine
,        create_time
,        table_comment
from     information_schema.tables
union all
select   routine_schema
,        routine_type
,        routine_name
,        concat(
             if(is_deterministic='NO','NOT','')
         ,   _utf8' DETERMINISTIC '
         ,   sql_data_access
         ,   ' SQL SECURITY '
         ,   security_type
         ) as subobject_type
,        created
,        routine_comment
from     information_schema.routines
;
[3 Nov 2005 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/internals/31868
[11 Nov 2005 17:03] 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/32185
[18 Nov 2005 20:20] Evgeny Potemkin
View .frm parser assumes that query string will take only 1 line, with \n in
aliases query stringmay take several lines thus produces bad .frm file.

Fixed in 5.0.17, cset 1.1944.67.1
[20 Nov 2005 4:49] Paul Dubois
Noted in 5.0.17 changelog.