[SQL] Incorrect highlighting of a string containing only a \

[ciaccia]

Wrong highlighting with SQL (build 3124)

Consider the following (PostgreSQL) SQL script:

CREATE TABLE tab
(
  id bigserial,
  data text
);

INSERT INTO tab(id, data) values (1, '_');
-- CORRECT: This line is grey

INSERT INTO tab(id, data) values (2, '\');
-- WRONG: This line is yellow, it should be *GREY*

Expected behaviour

The last line should be considered a comment an be grey.

Actual behaviour

The last line is yellow, since the SQL parser does not correctly close the '' string (it thinks the last single quote is escaped, which in SQL is not).

Steps to reproduce

1. Open Sublime Text 3124 with standard settings
2. Create a new file, paste the above content in it and save it as bug.sql.
3. The last color is grey instead yellow

Environment

• Operating system and version:
  • Mac OS X 10.11.6
• Sublime Text:
  • Build 3124

[keith-hall]

Agreed, in ANSI SQL, single quotes should be doubled up to be escaped (this is working as expected), and a preceding \ character shouldn't affect them.

[wbond]

With the current architecture, this will be impossible to support.

Unfortunately one of the most popular databases is MySQL, and it uses \' unless certain options are set. Switching to only ANSI escapes and allowing '\' would break string quoting in many user's MySQL. Rather than change which flavor of SQL is broken, for now I am deferring to leaving the broken thing the same as it has been for the past few years.

The only way I see around this would be to have a way for a user to switch between a MySQL package and an ANSI SQL package. Since various other syntaxes include the SQL syntax in strings, they would both need to use source.sql, and thus only one would be able to be enabled at the same time.

The other downside here is that packages are enabled/disabled globally in Sublime Text, so you would not be able to have MySQL enabled for one project and ANSI SQL for another.

[ciaccia]

I was not aware of the MySQL non-standard escaping, this makes it (probably) impossible to fix for both sides at once.

Would it be possible to have two different parsers, one for standalone SQL files and another one for other syntaxes which include SQL? Then everybody could chose which standalone parser (ASCII or MySQL) he/she likes. In this way the other syntaxes including SQL would not be affected and would stay as today.

[wbond]

@ciaccia Yes, that is a possibility also. MySQL, PostgreSQL, SQLite, SQL Server and Oracle all have enough differences that different flavors would be nice.

This is a similar situation to the shell syntax, where having variations for bash, sh, zsh and tcsh would be handy.

[lanrion]

Also wrong in Ruby when use /,

[wbond]

@lanrion Please open a new issue and provide the source code in your screenshot so we can easily copy-paste to develop a patch

[lanrion]

@wbond Open a new issue #639 .

[tjasko]

This is also an issue with ANSI SQL when setting the escape character when using the LIKE operator: