[MDEV-8649] Table functions: SHOW FUNCTION STATUS gives an error - JIRA

Details

Type: Bug
Status: Open
Priority: Major
Resolution: Unresolved
Affects Version/s: N/A
Fix Version/s: None
Component/s: None
Labels:
None

Description

This task is a part of diwas joshi 's GSoC project.

I am using the current table functions tree and I run the following:

delimiter |

CREATE FUNCTION f10(param1 VARCHAR(11))
RETURNS TABLE return_table(name VARCHAR(11)) 
deterministic
BEGIN
insert into return_table values('foo');
END|

DELIMITER ;

Then I run:

MariaDB [test]> show function status;
ERROR 1313 (42000): RETURN is only allowed in a FUNCTION

Apparently some of the code is not aware of the presence of table functions.

Gliffy Diagrams

Attachments

Issue Links

is part of

MDEV-8100 Table functions (aka SQL functions returning tables)

Open

Activity

Ascending order - Click to sort in descending order

Hide

Permalink

Sergei Petrunia added a comment - 2015-08-19 16:30

The error is produced by this line in sql_yacc.yy:

              my_message(ER_SP_BADRETURN, ER(ER_SP_BADRETURN), MYF(0));

Stack trace:

(gdb) wher
  #0  MYSQLparse (thd=0x555558164140) at /home/psergey/dev-git/10.1-gsoc-repo/sql/sql_yacc.yy:3750
  #1  0x0000555555a6d454 in parse_sql (thd=0x555558164140, parser_state=0x7ffff7ef4290, creation_ctx=0x7fff9000c018, do_pfs_digest=false) at /home/psergey/dev-git/10.1-gsoc-repo/sql/sql_parse.cc:9131
  #2  0x0000555555dadbbf in sp_compile (thd=0x555558164140, defstr=0x7ffff7ef4540, sql_mode=0, creation_ctx=0x7fff9000c018) at /home/psergey/dev-git/10.1-gsoc-repo/sql/sp.cc:750
  #3  0x0000555555db24ec in sp_load_for_information_schema (thd=0x555558164140, proc_table=0x7fff900216b0, db=0x7ffff7ef66a0, name=0x7ffff7ef66c0, sql_mode=0, type=TYPE_ENUM_FUNCTION, returns=0x7fff9003b670 "TABLE return_table(name varchar(11))", params=0x555556371260 "", free_sp_head=0x7ffff7ef674f) at /home/psergey/dev-git/10.1-gsoc-repo/sql/sp.cc:2377
  #4  0x0000555555aff3e8 in store_schema_proc (thd=0x555558164140, table=0x7fff9003b938, proc_table=0x7fff900216b0, wild=0x0, full_access=true, sp_user=0x7ffff7ef7b50 "root@localhost") at /home/psergey/dev-git/10.1-gsoc-repo/sql/sql_show.cc:5716
  #5  0x0000555555affdc9 in fill_schema_proc (thd=0x555558164140, tables=0x7fff90007150, cond=0x0) at /home/psergey/dev-git/10.1-gsoc-repo/sql/sql_show.cc:5840
  #6  0x0000555555b086f8 in get_schema_tables_result (join=0x7fff90009cc8, executed_place=PROCESSED_BY_JOIN_EXEC) at /home/psergey/dev-git/10.1-gsoc-repo/sql/sql_show.cc:7971
  #7  0x0000555555aad14f in JOIN::exec_inner (this=0x7fff90009cc8) at /home/psergey/dev-git/10.1-gsoc-repo/sql/sql_select.cc:2544
  #8  0x0000555555aac7ab in JOIN::exec (this=0x7fff90009cc8) at /home/psergey/dev-git/10.1-gsoc-repo/sql/sql_select.cc:2393
  #9  0x0000555555aafd59 in mysql_select (thd=0x555558164140, rref_pointer_array=0x555558168478, tables=0x7fff90007150, wild_num=0, fields=..., conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2684619520, result=0x7fff90009ca8, unit=0x555558167b00, select_lex=0x555558168200) at /home/psergey/dev-git/10.1-gsoc-repo/sql/sql_select.cc:3323
  #10 0x0000555555aa5fd7 in handle_select (thd=0x555558164140, lex=0x555558167a38, result=0x7fff90009ca8, setup_tables_done_option=0) at /home/psergey/dev-git/10.1-gsoc-repo/sql/sql_select.cc:371
  #11 0x0000555555a65fbf in execute_sqlcom_select (thd=0x555558164140, all_tables=0x7fff90007150) at /home/psergey/dev-git/10.1-gsoc-repo/sql/sql_parse.cc:5799
  #12 0x0000555555a5c25f in mysql_execute_command (thd=0x555558164140) at /home/psergey/dev-git/10.1-gsoc-repo/sql/sql_parse.cc:2939
  #13 0x0000555555a69554 in mysql_parse (thd=0x555558164140, rawbuf=0x7fff90006408 "show function status", length=20, parser_state=0x7ffff7ef90f0) at /home/psergey/dev-git/10.1-gsoc-repo/sql/sql_parse.cc:7175

Show

Sergei Petrunia added a comment - 2015-08-19 16:30 The error is produced by this line in sql_yacc.yy: my_message(ER_SP_BADRETURN, ER(ER_SP_BADRETURN), MYF(0)); Stack trace: (gdb) wher #0 MYSQLparse (thd=0x555558164140) at /home/psergey/dev-git/10.1-gsoc-repo/sql/sql_yacc.yy:3750 #1 0x0000555555a6d454 in parse_sql (thd=0x555558164140, parser_state=0x7ffff7ef4290, creation_ctx=0x7fff9000c018, do_pfs_digest=false) at /home/psergey/dev-git/10.1-gsoc-repo/sql/sql_parse.cc:9131 #2 0x0000555555dadbbf in sp_compile (thd=0x555558164140, defstr=0x7ffff7ef4540, sql_mode=0, creation_ctx=0x7fff9000c018) at /home/psergey/dev-git/10.1-gsoc-repo/sql/sp.cc:750 #3 0x0000555555db24ec in sp_load_for_information_schema (thd=0x555558164140, proc_table=0x7fff900216b0, db=0x7ffff7ef66a0, name=0x7ffff7ef66c0, sql_mode=0, type=TYPE_ENUM_FUNCTION, returns=0x7fff9003b670 "TABLE return_table(name varchar(11))", params=0x555556371260 "", free_sp_head=0x7ffff7ef674f) at /home/psergey/dev-git/10.1-gsoc-repo/sql/sp.cc:2377 #4 0x0000555555aff3e8 in store_schema_proc (thd=0x555558164140, table=0x7fff9003b938, proc_table=0x7fff900216b0, wild=0x0, full_access=true, sp_user=0x7ffff7ef7b50 "root@localhost") at /home/psergey/dev-git/10.1-gsoc-repo/sql/sql_show.cc:5716 #5 0x0000555555affdc9 in fill_schema_proc (thd=0x555558164140, tables=0x7fff90007150, cond=0x0) at /home/psergey/dev-git/10.1-gsoc-repo/sql/sql_show.cc:5840 #6 0x0000555555b086f8 in get_schema_tables_result (join=0x7fff90009cc8, executed_place=PROCESSED_BY_JOIN_EXEC) at /home/psergey/dev-git/10.1-gsoc-repo/sql/sql_show.cc:7971 #7 0x0000555555aad14f in JOIN::exec_inner (this=0x7fff90009cc8) at /home/psergey/dev-git/10.1-gsoc-repo/sql/sql_select.cc:2544 #8 0x0000555555aac7ab in JOIN::exec (this=0x7fff90009cc8) at /home/psergey/dev-git/10.1-gsoc-repo/sql/sql_select.cc:2393 #9 0x0000555555aafd59 in mysql_select (thd=0x555558164140, rref_pointer_array=0x555558168478, tables=0x7fff90007150, wild_num=0, fields=..., conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2684619520, result=0x7fff90009ca8, unit=0x555558167b00, select_lex=0x555558168200) at /home/psergey/dev-git/10.1-gsoc-repo/sql/sql_select.cc:3323 #10 0x0000555555aa5fd7 in handle_select (thd=0x555558164140, lex=0x555558167a38, result=0x7fff90009ca8, setup_tables_done_option=0) at /home/psergey/dev-git/10.1-gsoc-repo/sql/sql_select.cc:371 #11 0x0000555555a65fbf in execute_sqlcom_select (thd=0x555558164140, all_tables=0x7fff90007150) at /home/psergey/dev-git/10.1-gsoc-repo/sql/sql_parse.cc:5799 #12 0x0000555555a5c25f in mysql_execute_command (thd=0x555558164140) at /home/psergey/dev-git/10.1-gsoc-repo/sql/sql_parse.cc:2939 #13 0x0000555555a69554 in mysql_parse (thd=0x555558164140, rawbuf=0x7fff90006408 "show function status", length=20, parser_state=0x7ffff7ef90f0) at /home/psergey/dev-git/10.1-gsoc-repo/sql/sql_parse.cc:7175

Hide

Permalink

Sergei Petrunia added a comment - 2015-08-19 16:37

Looking at sp_load_for_information_schema() :

(gdb) print defstr
  $31 = {Ptr = 0x7fff90041ef0 "CREATE DEFINER=`` FUNCTION `test`.`f10`() RETURNS TABLE return_table(name varchar(11))\nRETURN NULL", str_length = 98, Alloced_length = 616, extra_alloc = 0, alloced = true, thread_specific = false, str_charset = 0x555556c9f120}

what is "RETURN NULL" ?? It comes from this line in sp_load_for_information_schema()

  sp_body= (type == TYPE_ENUM_FUNCTION ? "RETURN NULL" : "BEGIN END");

Apparently it is creating something like a "dummy function definition", where it puts "RETURN NULL" which is not valid for a table function.

Show

Sergei Petrunia added a comment - 2015-08-19 16:37 Looking at sp_load_for_information_schema() : (gdb) print defstr $31 = {Ptr = 0x7fff90041ef0 "CREATE DEFINER=`` FUNCTION `test`.`f10`() RETURNS TABLE return_table(name varchar(11))\nRETURN NULL", str_length = 98, Alloced_length = 616, extra_alloc = 0, alloced = true, thread_specific = false, str_charset = 0x555556c9f120} what is "RETURN NULL" ?? It comes from this line in sp_load_for_information_schema() sp_body= (type == TYPE_ENUM_FUNCTION ? "RETURN NULL" : "BEGIN END"); Apparently it is creating something like a "dummy function definition", where it puts "RETURN NULL" which is not valid for a table function.

Hide

Permalink

Sergei Petrunia added a comment - 2015-08-19 16:38

This code is hackish but still, we should somehow make SHOW FUNCTION STATUS to work for table functions.

Show

Sergei Petrunia added a comment - 2015-08-19 16:38 This code is hackish but still, we should somehow make SHOW FUNCTION STATUS to work for table functions.

Table functions: SHOW FUNCTION STATUS gives an error

Details

Description

Gliffy Diagrams

Attachments

Issue Links

Activity

People

Dates

Agile