Bug 6832

Summary: Prepared statements admin
Product: SQL Reporter: Martin Kersten <mk>
Component: allAssignee: SQL devs <bugs-sql>
Status: NEW ---    
Severity: normal CC: dnv, pedrotadim, y.zhang
Priority: Normal    
Version: -- development   
Hardware: Other   
OS: Mac OS X   

Description Martin Kersten cwiconfidential 2020-03-15 09:54:51 CET
User-Agent:       Mozilla/5.0 (Macintosh; Intel Mac OS X 10.13; rv:73.0) Gecko/20100101 Firefox/73.0
Build Identifier: 

- The prepared statement disappears when it is called wrongly without warning.
- There should be a method to explicitly drop a prepared statement
- the phrase "EXEC 7(...)" hints at arguments being expected, which is not true.
- Otherwise the calling method would be nice to be rendered as a SQL FUNCTION signature rather than the argument table
- it would be nice to give the prepared statement a name (= syntax sugar for procedure/function def. without arguments)


Reproducible: Always

Steps to Reproduce:
sql>prepare select 1;
execute prepared statement using: EXEC 13(...)
+---------+--------+-------+--------+-------+--------+
| type    | digits | scale | schema | table | column |
+=========+========+=======+========+=======+========+
| tinyint |      1 |     0 |        |       |        |
+---------+--------+-------+--------+-------+--------+
sql>select * from prepared_statements();
+-----------+----------+-------------+-------------------+----------------------------+
| sessionid | username | statementid | statement         | created                    |
+===========+==========+=============+===================+============================+
|         0 | monetdb  |          13 | prepare select 1; | 2020-03-15 08:39:28.200392 |
+-----------+----------+-------------+-------------------+----------------------------+
1 tuple
sql>exec 13();
+--------------+
| single_value |
+==============+
|            1 |
+--------------+
sql>exec 13;
syntax error, unexpected SCOLON, expecting '(' in: "exec 13;"
sql>select * from prepared_statements();
+-----------+----------+-------------+-----------+---------+
| sessionid | username | statementid | statement | created |
+===========+==========+=============+===========+=========+
+-----------+----------+-------------+-----------+---------+
0 tuples
sql>prepare select 1;
execute prepared statement using: EXEC 17(...)
sql>drop function 17();
syntax error, unexpected sqlINT in: "drop function 17"
sql>prepare myid as select 1;
syntax error, unexpected IDENT in: "prepare myid"
sql>prepare select 1;
execute prepared statement using: EXEC 21(...)
sql>exec 21(cast( 1 as tinyint));
EXEC called with wrong number of arguments: expected 0, got 1
Comment 1 Pedro Ferreira 2020-03-18 14:51:38 CET
The answers for each bullet point:

- Deleting prepared statements after error is a feature, which I think it should be revised, because we now offer the possibility to deallocate them.
- Deallocate statements have been added in default (also on Jun2020 branch) with this purpose.
- A prepared statement is seen as function that is called later. A function without arguments in SQL is called with just (). The same for prepared statements. To add the arguments to the EXEC it requires a small change in mclient.
- We allow digits in identifiers, which means calling exec "11" could mean to execute the prepared statement named "11" or the query with the cache id 11. We could disallow a numerical digit as the first letter on the prepared statement to allow this.
Comment 2 Ying Zhang cwiconfidential 2020-07-24 14:10:02 CEST
(In reply to Pedro Ferreira from comment #1)
> The answers for each bullet point:
> 
> - Deleting prepared statements after error is a feature, which I think it
> should be revised, because we now offer the possibility to deallocate them.

I'd love to see this feature be revised. How about only delete (is it called "deallocate" actually?) a prepared statement when explicitly instructed?

> - Deallocate statements have been added in default (also on Jun2020 branch)
> with this purpose.

how does this work?  do we have documentation in the monetdb.org website?
Comment 3 Pedro Ferreira 2020-07-26 11:56:15 CEST
I added the deallocate information on mhelp.
Comment 4 Karl Seguin 2020-08-18 11:24:26 CEST
In addition to revisiting the auto-deallocate on error, I'd also like to see something like postgres' unnamed prepared statements which automatically deallocates after use. 

Even better would be a prepare+execute+close in a single statement, but this would require monetdb to be able to infer the types better (e.g., allow exec 5('2010-01-10') instead of exec 5(date '2010-01-01')). I assume this would be possible since it can figure it out for non-prepared statements.

From a security perspective, drivers should be encouraged to prefer prepared statements, so improving their usability would be nice.