fix: support MySQL # line comments (#2499) - #2502

SELECT 5 # 3 (PostgreSQL bitwise XOR, never supported) now parses as SELECT 5 plus comment, the same way SELECT 1 -- 2 already behaves.

Ooofffff!
This is evil syntax (which I did not even know exists) -- but so is the MySQL syntax. And my heart is much more on PostgreSql than it is on MySQL. Let me think about this and sorry for being dramatic here, I just feel like there is no good solution to this.

This is evil syntax (which I did not even know exists) -- but so is the MySQL syntax.

For completeness on the lexeme: besides the bitwise XOR row in Table 9.4, Mathematical Operators (integral_type # integral_type → integral_type, "Bitwise exclusive OR", 17 # 5 → 20), the current documentation (PostgreSQL 18) gives # three more meanings, all in Table 9.36, Geometric Operators:

• # geometric_type → integer: "Returns the number of points. Available for path, polygon." (# path '((1,0),(0,1),(-1,0))' → 3)
• geometric_type # geometric_type → point: "Computes the point of intersection, or NULL if there is none. Available for lseg, line." (lseg '[(0,0),(1,1)]' # lseg '[(1,0),(0,1)]' → (0.5,0.5))
• box # box → box: "Computes the intersection of two boxes, or NULL if there is none." (box '(2,2),(-1,-1)' # box '(1,1),(-2,-2)' → (1,1),(-1,-1))

Sources: Mathematical Functions and Operators, Geometric Functions and Operators

Honestly a tough call, we are doomed when we do and also when we don't.
Nobody cared about those Postgres Operators so far. So I see three choices:

1. we implement/add the Postgres Operators just to have a strong and sound argument why the MySQL Comment syntax won't be supported
2. we implement the MySQL Comment syntax "for the time being", just to throw it out when there is a solid demand for the Postgres Operators
3. Or we just close the cursed PR and work on something else

I am in favor of 1) or 3), but I won't oppose when you prefer 2). You have provided so much good stuff that I trust your good judgement.

My position up front: either leave it as is (closing this PR is fine with me), or fix it properly with a switch, so that no reading silently loses data. The proper fix is an architecture decision that is yours to make; I will not push it unilaterally.

Honestly a tough call, we are doomed when we do and also when we don't.

The simplest decision. Put "never silently change semantics" first, and current master is not only acceptable but safer than this PR. First-hand runs on both sides (master 1e4e92b vs PR branch eaf9884, each built locally; re-checked on current master 4c6a4fb):

All three are correct under real MySQL semantics, so for the MySQL reading this PR fixes a real gap. The problem is the other reading: data silently lost, no error at all. Master is the only state where neither reading gets silently changed.

The "I want it all" route. My gut reaction to such conflicts is "I want it all :)", so I searched the code for switch-like mechanisms and found the earlier cases:

• should bracket quotation (like sqlserver supports it) be removed to be able to support array constructs #677 (2018-2019): the square bracket deadlock (SQL Server quoting vs PG arrays), settled in v3.0 as arrays by default plus the withSquareBracketQuotation switch;
• the backslash switch (allowBackslashEscapeCharacter, in place since 4.6, 2023): the same kind of lexer-level switch;
• the Dialect enum + EXASOL gating: existing precedent for dialect-specific syntax behind a switch.

This class of problem has come up more than once, so for maintaining these switches we could consider a Feature set + Dialect enum extension, in three phases as I expect it:

1. a preset layer, on the order of a hundred lines, classifying the historical cases;
2. afterwards each new lexeme conflict remains an incremental one-Feature change hooked to the matching preset;
3. rebuilding the tokenizer itself into multi-mode (whole tokenization rule sets switched per dialect) is where effort and risk get large. This was debated in 2019: in should bracket quotation (like sqlserver supports it) be removed to be able to support array constructs #677 SerialVelocity sketched a full builder API (.withTSQLBracketNotation().withPostgresArraySyntax()...), and wumpz's response was: "Productions you can disable, but tokens? How would you do that using JavaCC? The brackets are part of a token." The answer that landed in v3.0 was to not touch the tokenizer structure: tokens match as usual, then the token action rewrites the token and backs up the input stream for re-tokenization when the switch is off. The square bracket and backslash switches still work exactly this way today. So for "one sign, two dialects, split by switch", following that historical decision (match-then-rewrite, no multi-mode rework) seems the more reasonable path.

If it is not worth it. The flag mechanism has existed for over six years and one more flag is cheap, so the real question is not feasibility but which side the default takes. My preference is PG syntax by default, for two reasons:

1. PG XOR would become usable: on master SELECT 42 # 24 is a ParseException, with a PG default it would parse as XOR;
2. fail-loudly mostly survives: a MySQL # comment would still error as long as its text is not a valid expression (real comments are mostly natural language, most would still error). The exception is text that happens to be a valid expression: 42 # 24 would silently parse as XOR 50, and 1 # note as XOR against the column note. That has to be stated as a known limitation.

With PG as the default, if real demand shows up on the MySQL side, one flag (off by default) enables # comments. And whether that single flag or the dialect presets above, once a switch exists the dividing line is the same: on the MySQL side # is an unconditional line comment (in real MySQL semantics 42#24 is a comment too; in that mode there is no competing #temp, #> or XOR need, so not even the space gate is needed); on the PG side # is all operators (#>/#>>/#- JSON family plus bare #), with no # comment. The two modes do not leak into each other.

Greetings,

this would be the best indeed and you are right: token manipulation should work although there is one particular challenge here. SPECIAL_TOKEN vs. TOKEN, so far we have manipulated only TOKEN vs. TOKEN. JavaCC is very poorly equipped for such use-cases and we won't get any help from anyone.

But if you want to do this, you have my full support. I would suggest starting to implement the Postgres INTERSECT operator, because this has some merit on its own. Once this works, we try to bend the token into a Special Token so it finds MySQL comments.

I would suggest starting to implement the Postgres INTERSECT operator, because this has some merit on its own. Once this works, we try to bend the token into a Special Token so it finds MySQL comments.

First step up for review: #2507, the Postgres # binary operator (bitwise XOR, docs Table 9.4; geometric intersection of lseg / line / box, Table 9.36). A dedicated token declared before S_IDENTIFIER wins the length tie on a lone #; longest match keeps every other # lexing untouched (#temp, ##global, #$tab1#, a#b, #>, #>>). The operator assertions were verified failing on master and passing on the branch; the guard test pins the identifier and JSON families.

One behavior delta disclosed there: a lone # can no longer be an identifier, so bare names (SELECT # FROM t, SELECT 1 #, ...) fail loudly instead of parsing silently; quoted "#" still parses.

Second step: Feature.allowHashLineComments (off by default) routing # into a Special Token per lexer state, so MySQL line comments work under the switch. A spike says MORE + SwitchTo handles it; #word adjacency and SELECT 1 # at EOF are the open edges to disclose in that PR. I will start on it once this lands.

I assume, this is obsolete after #2508?

Yes. #2508 landed the same feature behind allowHashLineComments and closed #2499. Closing this one.