maria-developers team mailing list archive

[Sergey Petrunia <sergey@xxxxxxxxxxx>]

On Sun, Aug 02, 2015 at 08:51:44PM +0530, Diwas Joshi wrote:
> So what would be the expected result of  query select f1(1,'aa'). since it
> will be a table shouldn't we use select * from f1(1,'aa'). I know it
> shouldn't crash but what should be the output of that query?
> My first guess is that since we are storing this is as functions too(as we
> decided earlier) it is crashing so we'll have to check for table functions
> in the execution cycle of functions

Agree. When a table function is invoked in the context where a scalar value 
is expected, the statement should fail with an error (Please look in
sql/share/errmsg.txt and pick an error code. If none works, please add a 
new error code/message).

However, this is not what I wanted to complain about (I pasted the wrong
example). Let's consider a testcase where a table function is used in the 
scalar context (and should return an empty table):

== example starts ==
delimiter |

CREATE FUNCTION f1(param1 VARCHAR(11))
RETURNS TABLE t1(name VARCHAR(11)) 
deterministic
BEGIN
INSERT INTO t1 SELECT a FROM t2 WHERE id = param1;
END|

DELIMITER ;
select 1 from f1('aaa');

== example ends ==

This crashes for me, too. The crash is in your function:

  Program received signal SIGSEGV, Segmentation fault.
  [Switching to Thread 0x7ffff7eb8700 (LWP 12653)]
  0x0000555555b4c56b in Object_creation_ctx::set_n_backup (this=0xb, thd=0x55555ac4aeb0) at /home/psergey/dev-git/10.1-gsoc-repo/sql/table.cc:83
(gdb) wher
  #0  0x0000555555b4c56b in Object_creation_ctx::set_n_backup (this=0xb, thd=0x55555ac4aeb0) at /home/psergey/dev-git/10.1-gsoc-repo/sql/table.cc:83
  #1  0x0000555555a6d280 in parse_sql (thd=0x55555ac4aeb0, parser_state=0x7ffff7eb4580, creation_ctx=0xb, do_pfs_digest=false) at /home/psergey/dev-git/10.1-gsoc-repo/sql/sql_parse.cc:9100
  #2  0x0000555555a6dcbb in check_and_process_table_functions (thd=0x55555ac4aeb0, table_list=0x7fff5c007630) at /home/psergey/dev-git/10.1-gsoc-repo/sql/sql_parse.cc:9336
  #3  0x0000555555a5ab77 in mysql_execute_command (thd=0x55555ac4aeb0) at /home/psergey/dev-git/10.1-gsoc-repo/sql/sql_parse.cc:2471
  #4  0x0000555555a6942e in mysql_parse (thd=0x55555ac4aeb0, rawbuf=0x7fff5c007408 "select 1 from f1('aaa')", length=23, parser_state=0x7ffff7eb7100) at /home/psergey/dev-git/10.1-gsoc-repo/sql/sql_parse.cc:7174
  #5  0x0000555555a5838b in dispatch_command (command=COM_QUERY, thd=0x55555ac4aeb0, packet=0x55555ac51f71 "select 1 from f1('aaa')", packet_length=23) at /home/psergey/dev-git/10.1-gsoc-repo/sql/sql_parse.cc:1470
  #6  0x0000555555a570a5 in do_command (thd=0x55555ac4aeb0) at /home/psergey/dev-git/10.1-gsoc-repo/sql/sql_parse.cc:1093
  #7  0x0000555555b95929 in do_handle_one_connection (thd_arg=0x55555ac4aeb0) at /home/psergey/dev-git/10.1-gsoc-repo/sql/sql_connect.cc:1347
  #8  0x0000555555b9566e in handle_one_connection (arg=0x55555ac4aeb0) at /home/psergey/dev-git/10.1-gsoc-repo/sql/sql_connect.cc:1258
  #9  0x00007ffff691de9a in start_thread (arg=0x7ffff7eb8700) at pthread_create.c:308
  #10 0x00007ffff604e3fd in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:112
  #11 0x0000000000000000 in ?? ()

Please check what's going on. (If you don't get the crash, compile and run under valgrind).


> Also i removed the test case SELECT * FROM mysql.proc WHERE db = database()
> and name = 'f1' because it was failing as it is writing time to result file
> so the test passes if we use --record since it writes new file but fails
> for ./mysql-test-run sp.test with differences in reject and result file

Good point. However, the solution should be not to remove the testcase, but
rather make the testcase output not depend on the date/time when the testcase
was run.

Why did .result file have time/date. Let's look:

-SELECT * FROM mysql.proc WHERE db = database() and name = 'f1';
...
-definer  root@localhost
-created  2015-07-03 16:36:36
-modified 2015-07-03 16:36:36
-sql_mode 
-comment  
-character_set_client latin1
...

It did so, because the SELECT selects 'created' and 'modified' columns from 
mysql.proc.  The testcase doesn't really need these columns, please make the 
SELECT not to select them.

BR
 Sergei
-- 
Sergei Petrunia, Software Developer
MariaDB Corporation | Skype: sergefp | Blog: http://s.petrunia.net/blog