Bug Report: SQL Commenting that screw up queries

Bug Report: SQL Commenting that screw up queries

  
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.
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?
I too encountered same problem one year before hence avoiding comments in Advance Queries.

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

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.




Solution

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

 @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


Rafael Valente wrote:

 @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


Hi Rafael,


I initiated the post as a word of warning to all Outsystems developers and a kind note to the Outsystems Devteam that they have a serious error which is in my humble opinion easy to track and kill.

Using -- in Outsystems syntax highlights to commented OUT until the newline as it should and as it is interpreted by MSSQL.
Hence; it is an indication that this way of commenting is supported.
The usage of it however leads to unexpected results if you expect it to comment only a single line.

The fact that SQL coding is actually changed is something that should be considered since i.e. would you actually use a newline character in your SQL the Outsystems interpreter will remove that.

The idea of bug reporting is providing feedback and expect it to be followed up whenever it would be suitable for Outsystems.
A confirmation from Outsystems that the bug is noted would be nice on this topic since until then developers will encounter this issue.

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

Miguel Rebelo wrote:

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

Hello Miguel,

I'm using version 9.1.0.603 of Service Studio. Is that version that you are referring to?


Best regards,

Rafael Valente

Hi Rafael,

Can you tell me also your platform server version? 

I'm trying to reproduce the behavior you described in the most recent version of our product but without success. Do you have a module that I can use to reproduce it?

Thanks

@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

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

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

Miguel Rebelo wrote:

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


Yes, that is correct, actually it used to be worse (as my example wasn't working on an older version)

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.