Details
-
Technical task
-
Status: Open (View Workflow)
-
Minor
-
Resolution: Unresolved
-
None
-
None
Description
Oracle uses the SQL standard syntax for simple comments: dash-dash starts a comment, the rest of the line is a comment.
DROP PROCEDURE p1; |
CREATE PROCEDURE p1 AS |
BEGIN
|
--This is a comment |
NULL; |
END; |
/
|
MariaDB uses non-standard syntaxt: dash-dash-space starts a comment. This is an example of a valid script:
SET sql_mode=DEFAULT; |
DROP PROCEDURE p1; |
DELIMITER /
|
CREATE PROCEDURE p1() |
BEGIN
|
-- This is a comment |
END; |
/
|
DELIMITER ;
|
If I remove the space character after "--", it returns a syntax error:
SET sql_mode=DEFAULT; |
DROP PROCEDURE p1; |
DELIMITER /
|
CREATE PROCEDURE p1() |
BEGIN
|
--This is a comment |
END; |
/
|
DELIMITER ;
|
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '--This is a comment
|
END' at line 3
|
That was an intentional design in early MySQL versions:
https://dev.mysql.com/doc/refman/5.7/en/ansi-diff-comments.html
We should add an option to make dash-dash without an extra space start a comment, as it makes migration from Oracle difficult.
Perhaps, a new flag in sql_mode or old_mode can solve the issue.
Attachments
Issue Links
- is duplicated by
-
MDEV-19728 Comments in SQL
-
- Closed
-
Activity
| Field | Original Value | New Value |
|---|---|---|
| Description |
Oracle uses the SQL standard syntax for simple comments: dash-dash starts a comment, the rest of the line is a comment.
{code:sql} DROP PROCEDURE p1; CREATE PROCEDURE p1 AS BEGIN --This is a comment NULL; END; / {code} MariaDB uses non-standard syntaxt: dash-dash-space starts a comment. This is an example of a valid script: {code:sql} SET sql_mode=DEFAULT; DROP PROCEDURE p1; DELIMITER / CREATE PROCEDURE p1() BEGIN -- This is a comment END; / DELIMITER ; {code} If I remove the space character after "--", it returns a syntax error: {code:sql} SET sql_mode=DEFAULT; DROP PROCEDURE p1; DELIMITER / CREATE PROCEDURE p1() BEGIN --This is a comment END; / DELIMITER ; {code} {noformat} ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '--This is a comment END' at line 3 {noformat} That was an intentional design in early MySQL versions: https://dev.mysql.com/doc/refman/5.7/en/ansi-diff-comments.html We should add an option to make dash-dash without an extra space start a comment. Perhaps, a new flag in {{sql_mode}} or {{old_mode}} can solve the issue. |
Oracle uses the SQL standard syntax for simple comments: dash-dash starts a comment, the rest of the line is a comment.
{code:sql} DROP PROCEDURE p1; CREATE PROCEDURE p1 AS BEGIN --This is a comment NULL; END; / {code} MariaDB uses non-standard syntaxt: dash-dash-space starts a comment. This is an example of a valid script: {code:sql} SET sql_mode=DEFAULT; DROP PROCEDURE p1; DELIMITER / CREATE PROCEDURE p1() BEGIN -- This is a comment END; / DELIMITER ; {code} If I remove the space character after "--", it returns a syntax error: {code:sql} SET sql_mode=DEFAULT; DROP PROCEDURE p1; DELIMITER / CREATE PROCEDURE p1() BEGIN --This is a comment END; / DELIMITER ; {code} {noformat} ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '--This is a comment END' at line 3 {noformat} That was an intentional design in early MySQL versions: https://dev.mysql.com/doc/refman/5.7/en/ansi-diff-comments.html We should add an option to make dash-dash without an extra space start a comment, as it makes migration from Oracle difficult. Perhaps, a new flag in {{sql_mode}} or {{old_mode}} can solve the issue. |
| Fix Version/s | 10.3 [ 22126 ] |
| Priority | Major [ 3 ] | Minor [ 4 ] |
| Parent Issue |
|
MDEV-10764 [ MDEV-10764 ] |
| Component/s | Parser [ 10201 ] |
| Labels | Compatibility |
| Fix Version/s | 10.4 [ 22408 ] |
| Link |
This issue is duplicated by |
The problem comes from the syntax like SELECT --1 — this is valid both in MariaDB and Oracle, and means SELECT 1. But in the standard SQL -- starts a comment, the standard does not allow more than one <minus sign> before a number (so it seems).
I don't think we can implement the standard behavior, it'll be a pretty serious compatibility break.
| Fix Version/s | 10.3 [ 22126 ] | |
| Fix Version/s | 10.4 [ 22408 ] | |
| Assignee | Alexander Barkov [ bar ] | |
| Labels | Compatibility | Compatibility Oracle |
| Workflow | MariaDB v3 [ 79688 ] | MariaDB v4 [ 140223 ] |
dash-dash with no space is the sql standard syntax, MariaDB should support it not only in the ORACLE mode.