Issue 447 in flyway: Nested multi-line SQL comments pased in a possible incorrect manner

[fly...@googlecode.com]

Status: New
Owner: ----
Labels: Type-Defect Priority-Medium

New issue 447 by t...@stordy-allison.com: Nested multi-line SQL comments
pased in a possible incorrect manner
http://code.google.com/p/flyway/issues/detail?id=447

The following SQL would cause the final line to be executed incorrectly:

/*comment line
/*nested comment*/
more comment text*/

Flyway currently attempts to execute 'more comment text*/'.

---

It it not clear if a nested comment is even valid though: the SQL 2003
standard defines the comments using BNF, which is context-free and I don't
believe can support the idea of a comment that is nested. The Eclipse
parser does however allow for a nested comment, behaving as expected.

See:
http://stackoverflow.com/questions/728172/are-there-multiline-comment-delimiters-in-sql-that-are-vendor-agnostic
for the BNF notation.

Alex: I'll leave you to decide on this one.

--
Rationale for use:

We are in some cases using the placeholders to give us conditionally
executed block of SQL in our scripts. This is as some environments we need
to create SYNONYMs, and in other we would rather a test table. In
production, we have the SYNONYM and in local development the test table.

To achieve this we use a multi-line comment start (/*) and end (*/) to
activate/deactivate the code. If there are any multi-line comments in the
block that we try to deactivate, flyway attempts to execute invalid
statements due to the behavior above.

--

Patch and test to fix attached.


Attachments:
patch.txt 2.4 KB

--
You received this message because this project is configured to send all
issue notifications to this address.
You may adjust your notification preferences at:
https://code.google.com/hosting/settings

[fly...@googlecode.com]

Comment #1 on issue 447 by axel.fontai...@gmail.com: Nested

multi-line SQL comments pased in a possible incorrect manner
http://code.google.com/p/flyway/issues/detail?id=447

Hi Tom,

thanks for suggesting this. Do the native tools of the database you use
support this? If so which ones (name, version)?

Thanks
Axel

[fly...@googlecode.com]

Comment #2 on issue 447 by t...@stordy-allison.com: Nested multi-line SQL

comments pased in a possible incorrect manner
http://code.google.com/p/flyway/issues/detail?id=447

Good point.

We are pretty much running exclusively on Oracle 10/11 - I'll give that a
go and let you know.

[fly...@googlecode.com]

Comment #3 on issue 447 by t...@stordy-allison.com: Nested multi-line SQL

comments pased in a possible incorrect manner
http://code.google.com/p/flyway/issues/detail?id=447

The plot thickens.

When running a query such as the above in Oracle SQL Developer (3.1.07),
and highlighting all the text and Ctrl+Enter, so as it issue is one
execution to the database, it succeeds.

However the highlighting in Oracle SQL Developer (3.1.07) (see attached) is
confused. And if I ask the tool to execute the script as a whole it fails.

If I use sqlplus (the command line executor that has been with Oracle for
many many years), it breaks as above.

I think that the decision made for us - Oracle doesn't seem to support it.

The main issue is that there are examples where forgetting to close a
multi-line comment correctly would work fine in Oracle sqlplus, but then
break if flyway had nested comments.


Attachments:
KindOfSuccess.PNG 22.2 KB
Failure.PNG 24.7 KB
sqlplus.PNG 12.7 KB

[fly...@googlecode.com]

Updates:
Labels: Database-Oracle

Comment #4 on issue 447 by axel.fontai...@gmail.com: Nested

multi-line SQL comments pased in a possible incorrect manner
http://code.google.com/p/flyway/issues/detail?id=447

Hi Tom,

for me, as far as Oracle support is concerned, sqlplus is the final judge.

If you have statements that fail in Flyway, but not in sqlplus, please post
them here.
Otherwise, I would say we can close this issue.

Cheers
Axel

[fly...@googlecode.com]

Comment #5 on issue 447 by t...@stordy-allison.com: Nested multi-line SQL

comments pased in a possible incorrect manner
http://code.google.com/p/flyway/issues/detail?id=447

Yeah - agreed.

As soon as I saw its output, I think the decision on this one was made.

Case closed.

[fly...@googlecode.com]

Updates:
Status: WontFix

Comment #6 on issue 447 by axel.fontai...@gmail.com: Nested

multi-line SQL comments pased in a possible incorrect manner
http://code.google.com/p/flyway/issues/detail?id=447

(No comment was entered for this change.)