[teiid-users] issues related to TEIID-1237

[Steven Hawkins]

To be more clear, the parser will accept the existing syntax, but any toString of the resulting command (such as what happens in Designer) would cause output to be in the expected form.

Another approach to getting the update counts would be to introduce another reserved variable, such as ROWCOUNT.  Then something like "ROWS_UPDATED = INSERT ...;" would get parsed into "INSERT ...; ROWS_UPDATED = VARIABLES.ROWCOUNT;".  We could add a validation to 7.1.1 that treats ROWCOUNT as reserved for forward compatibility and then there is no potential of a subtle breaking change.  ROWCOUNT could also be used for other purposes, such as Oracle's SQL%rowcount that can also tell you the position of the cursor for non-update statements.

----- "Steven Hawkins" <shawkins at redhat.com> wrote:

> Hello all,
> 
> In looking at expanding our support for stored procedure handling in
> our procedure language there are a couple of other cleanups that would
> be good to discuss.
> 
> 1. We should not allow the direct usage of stored procedures as
> subqueries.  Instead of "WHERE col = (EXEC foo())", we should use
> "WHERE col = (SELECT * FROM (EXEC foo()) x)".  I propose that we
> deprecate this usage and allow the parser to automatically convert to
> the latter syntax.
> 
> 2. To be more concise/closer to standard syntax we should allow
> dynamic SQL statements to have the form "EXECUTE [(STRING|IMMEDIATE)]
> ...".  The choice to use the STRING keyword in 5.5 was due to not
> having IMMEDIATE as a reserved word.
> 
> Our assignment syntax has several issues.  I propose that the parser
> continue to allow the existing syntax, but with the following changes.
> 
> 3. The plsql assignment operator := will also be accepted.  We do not
> yet need to make this the preferred syntax, but it may be a good idea
> as it disambiguates an assignment from the equality predicate boolean
> expression.
> 
> 4. We should deprecate the direct assignment of commands to the
> variable.  For example:
> a = select ...;
> b = insert into ...;
> c = update ...;
> d = delete ...;
> 
> The proper syntax for query expressions is a scalar subquery, thus "a
> = select ...;" should be "a = (select ...);".
> 
> For nearly every other database, DML statements do not project the
> rows updated.  They also do have the possibility of providing a proper
> result set, such as with the PostgreSQL/Oracle insert returning
> clause.  Both of these are reasons why you don't typically see syntax
> such as "b = insert into...;".  The more common method for getting the
> update count is from an implicit variable.  One possibility is to use
> our existing "ROWS_UPDATED" as that implicit variable.  So rather than
> "ROWS_UPDATED = insert into ...; x = ROWS_UPDATED;", you would just
> have "insert into ...; x = ROWS_UPDATED;".  For most update procedures
> this obviates the need to explicitly set the ROWS_UPDATED value as it
> will be set implicitly by the last DML statement.  However this could
> potentially be a breaking change in an existing procedure, such as
> "...; ROWS_UPDATED = ...; insert into ...; END".  Another option is to
> use more explicit syntax, like PostgreSQL, with "GET DIAGNOSTICS x =
> ROWCOUNT"
> 
> This should also change the meaning of "e = exec foo...;".  As per
> TEIID-1237, what we would like is "e = exec foo...;" to be a command
> statement with the side affect of setting e to the return value of the
> procedure.  This syntax would only be valid if the procedure has a
> return parameter (currently it would be valid if the procedure has a
> resultset that has only a single column or no result set and only one
> out/inout or return parameter).  The other change would be that the
> this statement would also set the implicit return cursor to the result
> set of the procedure if it has one.  Since these would be potentially
> breaking changes (albeit in very narrow circumstances) we could put a
> validation in 7.1.1 that only allows that assignment if the procedure
> returns a result value and if there is a subsequent command statement.
>  Otherwise we would have to use a slightly less intuitive syntax, like
> SQL Server, such as "exec e = foo...;".
> 
> Any thoughts?
> 
> Steve