Parser pandemonium in FROM procedure call

[Breck_Carter]

Here's a CALL that behaves as expected, and as documented:

CALL sa_locks ( connection = 2 );

conn_name,conn_id,user_id,table_type,creator,table_name,index_id,lock_class,lock_duration,lock_type,row_identifier
'ddd16-2',2,'DBA','BASE','DBA','t',,'Row','Transaction','Write',37552128
'ddd16-2',2,'DBA','BASE','DBA','t',,'Position','Transaction','Insert',
'ddd16-2',2,'DBA','BASE','DBA','t',0,'Position','Transaction','Insert',
'ddd16-2',2,'DBA','BASE','DBA','t',,'Schema','Transaction','Shared',
'ddd16-2',2,'DBA','BASE','DBA','t',,'Table','Transaction','Intent',

This one also behaves as expected, but NOT as documented (the docs don't allow for the "parameter =" usage like Syntax 2 of the CALL st...:

SELECT @@VERSION, * FROM sa_locks ( connection = 2 );

@@VERSION,conn_name,conn_id,user_id,table_type,creator,table_name,index_id,lock_class,lock_duration,lock_type,row_identifier
'16.0.0.1915','ddd16-2',2,'DBA','BASE','DBA','t',,'Row','Transaction','Write',37552128
'16.0.0.1915','ddd16-2',2,'DBA','BASE','DBA','t',,'Position','Transaction','Insert',
'16.0.0.1915','ddd16-2',2,'DBA','BASE','DBA','t',0,'Position','Transaction','Insert',
'16.0.0.1915','ddd16-2',2,'DBA','BASE','DBA','t',,'Schema','Transaction','Shared',
'16.0.0.1915','ddd16-2',2,'DBA','BASE','DBA','t',,'Table','Transaction','Intent',

Here is a CALL that fails as expected:

CALL sa_locks ( garbage = 2 );

Could not execute statement.
Parameter 'garbage' not found in procedure 'sa_locks'
SQLCODE=-615, ODBC 3 State="42000"
Line 1, column 1
CALL sa_locks ( garbage = 2 )

This one neither works nor fails as expected; it just ignores the "garbage = 2" and returns all the rows:

SELECT @@VERSION, * FROM sa_locks ( garbage = 2 );

@@VERSION,conn_name,conn_id,user_id,table_type,creator,table_name,index_id,lock_class,lock_duration,lock_type,row_identifier
'16.0.0.1915','ddd16-2',2,'DBA','BASE','DBA','t',,'Row','Transaction','Write',37552128
'16.0.0.1915','ddd16-2',2,'DBA','BASE','DBA','t',,'Position','Transaction','Insert',
'16.0.0.1915','ddd16-2',2,'DBA','BASE','DBA','t',0,'Position','Transaction','Insert',
'16.0.0.1915','ddd16-1',1,'DBA','BASE','DBA','t',,'Schema','Transaction','Shared',
'16.0.0.1915','ddd16-2',2,'DBA','BASE','DBA','t',,'Schema','Transaction','Shared',
'16.0.0.1915','ddd16-2',2,'DBA','BASE','DBA','t',,'Table','Transaction','Intent',

...and that's what's got me scrambling this morning, hunting through all my code for OTHER "FROM procedure ( parameter =" calls that might be silently doing the wrong thing.

Sigh 🙂

[VolkerBarth]

Call it a bug...

FWIW, the FROM clause is documented to accept parameters for procedure calls both by position and by name, cf. that paragraph from the cited doc page:

procedure-name [...]
The argument list can be specified by position or by using keyword format. By position, the arguments match up with the corresponding parameter in the parameter list for the procedure (DEFAULT can be used for an optional parameter). By keyword, the arguments are matched up with the named parameters. For information about the supported syntax for named parameters, see Named parameters.

(That seems to be new in the v16 docs, the v12.0.1 docs do not contain that paragraph.)

---

Personally, I feel save as I seem to always use parameters by position...

[Breck_Carter]

Good catch, Volker! The informal representation used for the FROM syntax in the Help does not seem to allow for parameter-name = expression, but I guess that's what "informal" means: the word "parameter" can mean anything you want it to 🙂

procedure-name :
[ owner.]procedure-name ( [ parameter, ... ] )
[ WITH ( column-name data-type, ... ) ]
[ [ AS ] correlation-name ]

[Former Member]

This stuff is better than a morning cup of coffee. I finally woke up after noticing this to be a feature change.

This change seems to be related to the new "SQL/2011 named parameter syntax" compliance and is documented in the change in the From-clause link Volker noted as well as in a new 'SQL Ref.', 'Language Elements' article on this feature Named Parameters

HTH

[VolkerBarth]

This stuff is better than a morning cup of coffee.

My mileage does vary.

[Breck_Carter]

How does the documentation explain why the following does NOT throw a syntax error even though it is CLEARLY incorrect?

SELECT @@VERSION, * FROM sa_locks ( garbage = 2 );

[Former Member]

It doesn't completely. It explains why it now works in 16.

It is possible the issue identified in version 12 has been there since the inclusion of stored procedure calls in the from clause back in version 9. That feature is a Std. SQL feature.

All through that patch named parameters were being handled as a T/SQL feature (introduced in version 5.0).

It seems that Version 12 accepts the value as a parameter but is accessing that value positionally; as noted.

[Former Member]

The plot gets a little thicker than just that too.

This all seems to work with @parm (ie T/SQL) style parameter names.

It only fails with identifier names that don't include the preceding (again t/sql required) @.

If have a repro that works in V12 with '@parms' but fail with unannoted 'parms'. So a further (much more subtle) mixing of dialects.

{just in case you haven't noticed ... still investigating}

[Former Member]

Ignoring the documented differences between the two versions, this specific lack of throwing an error appears to be related to sa_locks( ) being a built-in procedure. Creating a clone version of dba.sa_locks( ) (which just wraps it with a select from the dbo.sa_locks( .... )) causes the expected error to be thrown.

If one wraps sa_locks with a dba procedure of same name and similar definition that will cause the correct -615 error to be thrown.

I am sending that result to product development's attention now.