MYSQL: Error joining 2 select statements that include a stored function

Question

Good Afternoon,

I created a stored function to emulate the behavior of the "SHOW GRANTS FOR" command in MySQL. The script definition is as follows:

CREATE FUNCTION get_grants(p_user text, p_host text, p_type text, p_obj text) 
   RETURNS text 
   NOT DETERMINISTIC 
   READS SQL DATA
BEGIN
...
END;

If I use the function in a select statement It is executed correctly. For example:

SELECT User as USER, Host as HOST, 
      'GLOBAL' as PRIV_TYPE, NULL as DATABASE_NAME,   
       mydatabase.get_grants(User, Host, 'GLOBAL', '') as PRIVILEGES
  FROM mysql.user

However, when I use a UNION or UNION ALL statement to join 2 select statements that call the function with different parameter values, like this:

SELECT User as USER, Host as HOST, 
       'GLOBAL' as PRIV_TYPE, NULL as DATABASE_NAME,   
       mydatabase.get_grants(User, Host, 'GLOBAL', '') as PRIVILEGES
  FROM mysql.user
UNION ALL
SELECT User as USER, Host as HOST,
       'DB' as PRIV_TYPE, Db as DATABASE_NAME,
       mydatabase.get_grants(User, Host, 'DB', Db) as PRIVILEGES
  FROM mysql.db
 ORDER BY USER, HOST, DATABASE_NAME IS NULL, DATABASE_NAME;

I get the following error: #1222 - The used SELECT statements have a different number of columns.

Can anyone explain what's going on here and how to resolve this issue?

Thanks in advance.

score 0 · Answer 1 · answered Jan 30 '17 at 15:46

I think it's the UNION ALL which causes the issue. Normally if you use UNION ALL it doesn't matter if the number of columns are different, with UNION however they must be equal. So try either use UNION ALL and remove the NULL as DATABASE_NAME or use only UNION and keep the NULL as DATABASE_NAME

SELECT User as USER, Host as HOST, 
       'GLOBAL' as PRIV_TYPE, NULL as DATABASE_NAME,   
       mydatabase.get_grants(User, Host, 'GLOBAL', '') as PRIVILEGES
  FROM mysql.user
UNION
SELECT User as USER, Host as HOST,
       'DB' as PRIV_TYPE, Db as DATABASE_NAME,
       mydatabase.get_grants(User, Host, 'DB', Db) as PRIVILEGES
  FROM mysql.db
 ORDER BY USER, HOST, DATABASE_NAME IS NULL, DATABASE_NAME;

OR

SELECT User as USER, Host as HOST, 
       'GLOBAL' as PRIV_TYPE,   
       mydatabase.get_grants(User, Host, 'GLOBAL', '') as PRIVILEGES
  FROM mysql.user
UNION ALL
SELECT User as USER, Host as HOST,
       'DB' as PRIV_TYPE, Db as DATABASE_NAME,
       mydatabase.get_grants(User, Host, 'DB', Db) as PRIVILEGES
  FROM mysql.db
 ORDER BY USER, HOST, DATABASE_NAME IS NULL, DATABASE_NAME;

score 0 · Accepted Answer · answered Feb 09 '17 at 13:06

After almost frying my brain trying to find the problem it turns out that the actual issue didn't have anything to do with the error message provided by MySQL (at least not with the description provided about this error message in the documentation).

In this case the error was within the implementation of the Stored Function. In a SELECT...INTO command I was missing a "," between two variables in the INTO clause. Ones the number of columns selected corresponded to the number of variables in the INTO clause the problem was solved and I managed to use the function in the above query without any problems.

For reference the problematic query was:

SELECT Select_priv, Insert_priv, Update_priv, Delete_priv,
       Create_priv, Alter_priv, Index_priv, Drop_priv,
       Create_tmp_table_priv, Create_view_priv, Show_view_priv,
       Create_routine_priv, Alter_Routine_priv, Execute_priv, 
       Event_priv, Trigger_priv, Grant_priv, Lock_tables_priv, 
       References_priv
  INTO v_select_priv, v_insert_priv, v_update_priv, v_delete_priv,
       v_create_priv, v_alter_priv, v_index_priv, v_drop_priv,
       v_tmptables_priv, v_createview_priv, v_showview_priv, 
       v_createroutine_priv, v_alterroutine_priv, v_execute_priv,
       v_event_priv, v_trigger_priv, v_grant_priv, v_locktables_priv,
       v_references_priv
  FROM mysql.db
 WHERE User = p_user
   AND Host = p_host
   AND Db = p_obj;

MYSQL: Error joining 2 select statements that include a stored function

2 Answers2