[jsr-221-eg] SQL 2016 MATCH RECOGNIZE JDBC parameter Marker / Escape Characters

[Mark Rotteveel]

On 9-10-2018 20:47, Douglas Surber wrote:
> Oracle Database JDBC driver from release 12c recognizes ‘{\ … \}’ as escape syntax. The character sequence between ‘{\’ and ‘\}’ is sent to the database without further processing except that any occurrence of ‘\\’ is replaced with a single ‘\’. These tokens are not recognized inside literals or comments. There is no constraint on the contained character sequence other than the requirement to double backslashes that do not terminate the sequence. In particular the character sequence can be of any length, unto and including the entire SQL text.

The description is not 100% clear to me, eg "to double backslashes that 
do not terminate the sequence", does that mean all backslashes should be 
doubled, or only backslashes followed by a } that are not intended to 
end the sequence? I think you mean the first, but the second would work 
as well, so it is best to be precise.

Or slightly more formally:

escape: '{\' escapedchars+ '\}'
escapedchars: [^'\']|'\\'

?

> In 2013 when we added this to the Oracle Database JDBC driver, we thought it was the best choice available lacking consensus from the JDBC EG. We still think it is the best choice. Oracle Database JDBC must continue to support this going forward. Our preference would be for this to be added to the JDBC standard. We have no fundamental objection to additional syntax to meet specific needs. We would rather not to add both the proposed syntax and some equivalent alternate syntax as that would be unnecessary duplication. We would very much prefer that our proposed syntax be part of the standard though we will consider alternatives.

I understand that, and I'll agree - with a bit of protest and moaning ;) 
- to this syntax if the syntax specification is further 
clarified/formalized. As I said, we should avoid https://xkcd.com/927/.

However, I want to repeat my concern that this is not a thing of beauty, 
and introducing features this way should be avoided in the future. 
Especially as something that was apparently rejected by the EG (or at 
least, not accepted), is now reintroduced as a fait accompli now 
MATCH_RECOGNIZE is standardized in SQL:2016.

We should also consider explicitly allowing vendor escapes in the 
specification, so vendors know that they can always introduce a 
workaround that does not interfere with future 
specification/standardization (see 
http://mail.openjdk.java.net/pipermail/jdbc-spec-discuss/2013-February/000059.html). 
For example, Oracle could have used {oracle \...\} as a solution when 
the EG did not accept its previous proposal.

Mark
-- 
Mark Rotteveel