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

[Douglas Surber]

Mark,

The Oracle Database Driver uses the first interpretation, any occurrence of ‘/‘ must be doubled except for the single ‘/‘ that precedes the ‘}’ that ends the escape sequence. Your formal definition is correct modulo character sequence literals and comments.

I appreciate your agreement and ruefully acknowledge that your protest and moaning is fully justified. Unfortunately for everyone, the standard setting process broke down last time this was considered.

Douglas

> On Oct 9, 2018, at 12:35 PM, Mark Rotteveel <mark at lawinegevaar.nl> wrote:
> 
> 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