Address co-existence of SQL comment and cache hint

An sql query, such as the following, parses but drops the cache hint

/* leading comment */ /*+ cache(ttl:180000) */ SELECT col1 FROM tbl

However, the same sql without the 'leading comment' retains the cache hint.

The reason is that Designer uses the same parser handling syntax as the Teiid parser. Both use a regular expression for handling the cache hint, ie.

"/\\*\\+?\\s*cache(\\(\\s*(pref_mem)?\\s*(ttl:\\d{1,19})?\\s*(updatable)?\\s*(scope:(session|vdb|user))?\\s*(min:\\d{1,19})?[^\\)]*\\))?[^\\*]*\\*\\/.*";

The dot-star (.*) at the end allows for the rest of the sql after the cache hint but there is no allowance for any comments prior to the cache hint. In order to handle this an extra pragma needs to prefix the regular expression above:

"(?:/\\*[^\\+].+?\\*/(?:\\s+)?)?"

• (...)? => an optional group so no comment is handled
• ?: => the group is non-capturing so ignored by the matcher group count
• [^\\+] => checks for a + following the comment opener. Cache hints are expected to use this so this distinguishes a normal comment from a cache hint
• .+? => makes the comment contents non-greedy so the first comment closer is found rather than the last
• (?:
  s+)? => non-capturing optional group that checks for any whitespace between the end of the comment and the cache hint

Example patch for Designer parser added.

This cannot be rectified in Designer until it is rectified in Teiid. Even then, older versions of Teiid will never support this.