403
Views
17
Comments
Solved
Bug Report: SQL Commenting that screw up queries
Question
Hi All,

Just as a word or warning for people creating advanced queries and that use COMMENTS in those queries.
There is a bug in the SQL processor that makes the double hyphens used for commenting disable every statement AFTER the hyphens.


Make sure not to use the two hyphens to place a comment.
Outsystems will "ignore" the linefeeds behind it and concatenate everything into one row.
Solution

Rafael Valente wrote:

Hello there,


You can use the "--" for comments as long as you don't use it inside the whole SELECT statement.


Best regards,

Rafael Valente

Yeah that was kindof allready known...

This is a BUG in the SQL parser; period.

Outsystems should parse the part having -- to make sure they don't remove the linebreak from that line before they remove all CR/LB to "minify" the query and replace all variables with the input parameters.

-- is a perfectly good single line comment indicator

https://msdn.microsoft.com/nl-nl/library/ms181627.aspx


Quote: "Comments inserted with -- are terminated by the newline character."

So don't remove the newline there; it's there for a reason.




mvp_badge
MVP
Rank: #2
This is absolutely true, and very valuable advise, but this has been the case since always (well, at least 4.2 which was the first version I worked with). The cause is that the Platform will put everything on a long line, before sending it to the database (at least that what I recall some OutSystems engineer writing here a while ago). You really mean you recently encountered this for the first time?
Rank: #134
I too encountered same problem one year before hence avoiding comments in Advance Queries.
Rank: #684

Hello there,


You can use the "--" for comments as long as you don't use it inside the whole SELECT statement.


Best regards,

Rafael Valente

mvp_badge
MVP
Rank: #2

Yup, it's a bug allright - apparently just not one that's serious enough to fix :).

Rank: #684

 @Eric,

It's kind of already known for people who work with SQL for a while. For newcomers that can be a heads up.


@Killian,

I agree with you. Unfortunately, all bugs should be serious enough to fix but i can understand that they have major things to be worried.


Best regards,

Rafael Valente


Staff
Rank: #12848

Hi guys,

Can you tell me which version you're using and provide me a simple module where you can reproduce this problem?


Best regards,

Miguel Rebelo

Rank: #684

@Miguel,


PS is 9.1. Unfortunately, i don't have any simple module available for these versions because i was aware of this in a project.

Sorry for that.

I tried to replicate this issue on version 10 and couldn't do it.

Hopefully they solved this in this version.


Best regards,

Rafael Valente

Staff
Rank: #12848

I tried the most recent versions of 10 and 9.1 and both worked fine. 

@Killian, @Eric, in which platform version you're getting that behavior? (full version number, e.g. 9.1.603.0)


Regards,

Miguel Rebelo

Staff
Rank: #12848

Hi guys,

I was finally able to reproduce the problem. The bug occurs when the line with the comment doesn't start with the "--" (e.g. empty spaces before the comment or comments in the end of the SQL lines).

Thanks for reporting it.

As workaround you can put the comments in new lines that starts with "--" or use multi-line comments (/* ... */).


Best regards,

Miguel Rebelo

mvp_badge
MVP
Rank: #2

If I'm not mistaken, any line with a -- comment not starting at the first position in a line caused this behaviour (so also e.g.

SELECT * FROM {MyTable} -- Yes, it's *my* table!
WHERE {MyTable}.[ForeignId] IS NULL

would cause to ignore the WHERE-clause.

Rank: #435

Old thread still broken in OS 10 runtime, however when using ServiceStudio SQL Test operation, the -- comments work without issue.

Rank: #1757

I noticed today two issues concerning commenting:

1. SQL modeler shows all text after a /* as commented, even when you don't end with a */

( of course you should close it.. though visually you get the wrong feedback and when running a test, it will keep running and even use all your app server cpu to try to generate SQL. Beware!)


2. When using special characters or {entityName} etc. in a commented line, it can cause the compiler to report issues in your sql statement. The generated SQL shows that it is also trying to convert commented items that are   identified as a string, entity or such.


I will not make these mistakes again but was staring at my SQL far too long before I realized the comments where causing the issue... Extra heads up or validation by OS would be a time saver for new entrants. :-)