Skip to content

Commit 2c08154

Browse files
committed
Treat # as the XOR operator it is, not as a comment marker
Fixes dbcli#1646. > select 17 # 5; +----------+ | ?column? | |----------| | 17 | +----------+ The answer is 20. sqlparse follows MySQL and reads # as the start of a comment (andialbrecht/sqlparse#539), so sqlparse.format(strip_comments=True) dropped the rest of the line before the statement reached the server, and the wrong answer came back without a warning. Only the TRAILING comments need to go there, which is what that call was added for in dbcli#1559: rstrip(";") cannot find the semicolon when a comment follows it. strip_trailing_comments() does that with PostgreSQL's rules instead of sqlparse's: - "--" runs to the end of the line, "/* */" nest; - those markers mean nothing inside a string literal, a quoted identifier or a dollar-quoted body; - "#" is an operator like any other. A comment in the middle of a statement is kept, so the server sees what was written. Both call sites move over: the one before execution in pgexecute, and _is_complete() in pgbuffer, where "select 17 # 5;" would otherwise never look finished and the prompt would keep waiting. 18 tests, including nested block comments, dollar-quoted function bodies, doubled quotes inside strings and unterminated comments.
1 parent 101e523 commit 2c08154

6 files changed

Lines changed: 237 additions & 5 deletions

File tree

‎changelog.rst‎

Lines changed: 13 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1,6 +1,19 @@
11
Upcoming
22
========
33

4+
Bug fixes:
5+
----------
6+
* Treat ``#`` as the bitwise XOR operator it is in PostgreSQL, rather than as a
7+
comment marker. ``select 17 # 5`` returned 17 instead of 20, and said nothing
8+
about it, because ``sqlparse.format(strip_comments=True)`` follows MySQL and
9+
drops everything after a ``#``. Trailing comments are now removed with
10+
PostgreSQL's own rules (``--`` to end of line, ``/* */`` which nest, and
11+
markers inside string literals, quoted identifiers or dollar-quoted bodies
12+
left alone), so a comment after the final semicolon still goes and the rest
13+
of the statement reaches the server as written. An unterminated ``/*`` is
14+
kept, so the server still reports it instead of pgcli quietly running a
15+
shorter statement.
16+
417

518
4.7.1 (2026-09-20)
619
==================

‎pgcli/packages/parseutils/__init__.py‎

Lines changed: 66 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1,3 +1,4 @@
1+
import re
12
import sqlparse
23

34
sqlparse.engine.grouping.MAX_GROUPING_DEPTH = None
@@ -71,3 +72,68 @@ def parse_destructive_warning(warning_level):
7172
"off": [],
7273
"": [],
7374
}.get(warning_level[0], warning_level)
75+
76+
77+
def strip_trailing_comments(sql):
78+
"""Return ``sql`` without the comments that trail its last statement.
79+
80+
``sqlparse.format(strip_comments=True)`` cannot be used for this. sqlparse
81+
follows MySQL and treats ``#`` as a comment marker, but in PostgreSQL it is
82+
the bitwise XOR operator, so it eats the rest of the line: ``select 17 # 5``
83+
becomes ``select 17`` and quietly returns 17 instead of 20 (dbcli/pgcli
84+
issue #1646, andialbrecht/sqlparse issue #539).
85+
86+
This scans the text with PostgreSQL's own rules instead: ``--`` to end of
87+
line and ``/* */`` (which nest) are comments, string literals, quoted
88+
identifiers and dollar-quoted bodies are not, and ``#`` is an operator like
89+
any other. Only trailing comments are dropped, so a comment in the middle
90+
of a statement is kept as the server would see it.
91+
"""
92+
last = 0 # one past the last character that is part of the statement
93+
i, n = 0, len(sql)
94+
while i < n:
95+
ch = sql[i]
96+
if ch in " \t\r\n":
97+
i += 1
98+
elif sql.startswith("--", i):
99+
end = sql.find("\n", i)
100+
i = n if end == -1 else end + 1
101+
elif sql.startswith("/*", i):
102+
depth, j = 1, i + 2
103+
while j < n and depth:
104+
if sql.startswith("/*", j):
105+
depth, j = depth + 1, j + 2
106+
elif sql.startswith("*/", j):
107+
depth, j = depth - 1, j + 2
108+
else:
109+
j += 1
110+
if depth:
111+
# Never closed. PostgreSQL rejects that ("unterminated /*
112+
# comment"), so keep the text and let it say so, rather than
113+
# dropping it and running a different statement.
114+
last = n
115+
i = j
116+
elif ch in "'\"":
117+
i += 1
118+
while i < n:
119+
if sql[i] == ch:
120+
if i + 1 < n and sql[i + 1] == ch: # '' or "" escapes itself
121+
i += 2
122+
continue
123+
i += 1
124+
break
125+
i += 1
126+
last = i
127+
elif ch == "$":
128+
match = re.match(r"\$[\w]*\$", sql[i:])
129+
if match:
130+
tag = match.group(0)
131+
end = sql.find(tag, i + len(tag))
132+
i = n if end == -1 else end + len(tag)
133+
else:
134+
i += 1
135+
last = i
136+
else:
137+
i += 1
138+
last = i
139+
return sql[:last]

‎pgcli/pgbuffer.py‎

Lines changed: 5 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1,6 +1,6 @@
11
import logging
22

3-
import sqlparse
3+
from .packages.parseutils import strip_trailing_comments
44
from prompt_toolkit.enums import DEFAULT_BUFFER
55
from prompt_toolkit.filters import Condition
66
from prompt_toolkit.application import get_app
@@ -15,7 +15,10 @@ def _is_complete(sql):
1515
# CREATE FUNCTION command.
1616
# Strip trailing comments so that "SELECT 1; -- note" is recognized as
1717
# complete (the semicolon is not at the end when a comment follows).
18-
stripped = sqlparse.format(sql, strip_comments=True).strip()
18+
# Not sqlparse's strip_comments: it reads "#" as a comment marker, which in
19+
# PostgreSQL is the bitwise XOR operator (issue #1646), so "select 17 # 5;"
20+
# would never look complete.
21+
stripped = strip_trailing_comments(sql).strip()
1922
return stripped.endswith(";") and not is_open_quote(sql)
2023

2124

‎pgcli/pgexecute.py‎

Lines changed: 7 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -9,6 +9,8 @@
99
from psycopg.conninfo import make_conninfo
1010
import sqlparse
1111

12+
from .packages.parseutils import strip_trailing_comments
13+
1214
sqlparse.engine.grouping.MAX_GROUPING_DEPTH = None
1315
sqlparse.engine.grouping.MAX_GROUPING_TOKENS = None
1416

@@ -367,10 +369,12 @@ def run(
367369
# run each sql query
368370
for sql in sqlarr:
369371
# Remove spaces, eol and semi-colons.
370-
# Strip comments first so rstrip(";") works when there are
371-
# trailing comments after the semicolon, e.g.:
372+
# Strip trailing comments first so rstrip(";") works when there are
373+
# comments after the semicolon, e.g.:
372374
# vacuum freeze verbose t; -- 82% towards emergency
373-
sql = sqlparse.format(sql, strip_comments=True).strip().rstrip(";")
375+
# Not sqlparse's strip_comments: it reads "#" as a comment marker,
376+
# which in PostgreSQL is the bitwise XOR operator (issue #1646).
377+
sql = strip_trailing_comments(sql).strip().rstrip(";")
374378
sql = sql.strip()
375379
if not sql:
376380
continue
Lines changed: 131 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,131 @@
1+
"""Dropping the comments that trail a statement.
2+
3+
sqlparse cannot do this for PostgreSQL: it follows MySQL and reads ``#`` as a
4+
comment marker, while in PostgreSQL ``#`` is the bitwise XOR operator. That is
5+
dbcli/pgcli issue #1646 and andialbrecht/sqlparse issue #539: `select 17 # 5`
6+
silently returns 17 instead of 20.
7+
"""
8+
9+
from pgcli.packages.parseutils import strip_trailing_comments
10+
11+
12+
def test_hash_is_an_operator_not_a_comment():
13+
"""The reported bug: everything after # was dropped."""
14+
assert strip_trailing_comments("select 17 # 5") == "select 17 # 5"
15+
assert strip_trailing_comments("select 17 # 5;") == "select 17 # 5;"
16+
17+
18+
def test_hash_keeps_its_own_trailing_comment_separate():
19+
assert strip_trailing_comments("select 17 # 5; -- note") == "select 17 # 5;"
20+
21+
22+
def test_trailing_line_comment_is_dropped():
23+
"""What #1559 fixed: rstrip(';') needs the comment gone first."""
24+
assert strip_trailing_comments("vacuum freeze verbose t; -- 82% towards emergency") == "vacuum freeze verbose t;"
25+
26+
27+
def test_trailing_block_comment_is_dropped():
28+
assert strip_trailing_comments("select 1; /* done */") == "select 1;"
29+
30+
31+
def test_nested_block_comments():
32+
"""PostgreSQL block comments nest, unlike C."""
33+
assert strip_trailing_comments("select 1; /* a /* b */ c */") == "select 1;"
34+
35+
36+
def test_several_trailing_comments():
37+
assert strip_trailing_comments("select 1;\n-- one\n/* two */\n-- three") == "select 1;"
38+
39+
40+
def test_comments_in_the_middle_are_kept():
41+
"""Only trailing comments go: the server should see the rest as written."""
42+
for sql in ("select 1, /* keep */ 2;", "select 1\n-- keep\n, 2;"):
43+
assert strip_trailing_comments(sql) == sql
44+
45+
46+
def test_comment_markers_inside_strings_are_text():
47+
for sql in ("select '-- not a comment';", "select 'a /* b */ c';", "select 'a # b';"):
48+
assert strip_trailing_comments(sql) == sql
49+
50+
51+
def test_doubled_quotes_inside_strings():
52+
sql = "select 'it''s -- fine';"
53+
assert strip_trailing_comments(sql) == sql
54+
55+
56+
def test_quoted_identifiers():
57+
sql = 'select 1 as "-- odd name";'
58+
assert strip_trailing_comments(sql) == sql
59+
60+
61+
def test_dollar_quoted_body_is_left_alone():
62+
"""A function body holds its own comments and must survive intact."""
63+
sql = "do $$ begin -- inside\n perform 1; end $$;"
64+
assert strip_trailing_comments(sql) == sql
65+
tagged = "create function f() returns int as $body$ select 1; -- inner\n$body$ language sql;"
66+
assert strip_trailing_comments(tagged) == tagged
67+
68+
69+
def test_only_a_comment_leaves_nothing():
70+
assert strip_trailing_comments("-- just a note") == ""
71+
assert strip_trailing_comments("/* just a note */") == ""
72+
73+
74+
def test_empty_and_whitespace():
75+
assert strip_trailing_comments("") == ""
76+
assert strip_trailing_comments(" \n ") == ""
77+
78+
79+
def test_unterminated_block_comment_is_kept():
80+
"""PostgreSQL rejects it ("unterminated /* comment"). Dropping it would run
81+
a different statement and return an answer where the server gave an error:
82+
`select 4/*2` would quietly say 4."""
83+
for sql in ("select 1; /* never closed", "select 4/*2"):
84+
assert strip_trailing_comments(sql) == sql
85+
86+
87+
def test_unterminated_string_does_not_hang():
88+
assert strip_trailing_comments("select 'never closed") == "select 'never closed"
89+
90+
91+
# --- PostgreSQL operators that look like comment markers -------------------
92+
#
93+
# Every expectation below was run against PostgreSQL 17 first; the value in the
94+
# comment is what the server answers for that expression.
95+
96+
97+
def test_hash_operators_are_left_alone():
98+
"""# starts several operators, none of them a comment."""
99+
for sql in (
100+
"select 17 # 5", # 20, bitwise XOR
101+
"""select '{"a":{"b":7}}'::jsonb #> '{a,b}'""", # 7, path extract
102+
"""select '{"a":1}'::jsonb #>> '{a}'""", # 1, path extract as text
103+
"""select '{"a":1,"b":2}'::jsonb #- '{a}'""", # {"b": 2}, delete path
104+
"select point '(0,0)' ## lseg '((1,1),(2,2))'", # (1,1), closest point
105+
"select box '((0,0),(2,2))' # box '((1,1),(3,3))'", # intersection
106+
"select # path '((1,1),(2,2),(3,3))'", # 3, number of points
107+
):
108+
assert strip_trailing_comments(sql) == sql, sql
109+
110+
111+
def test_operators_built_from_dashes_and_slashes():
112+
for sql in (
113+
"select 5 - -3", # 8, unary minus after an operator
114+
"select @ -5", # 5, absolute value
115+
"""select '{"a":1}'::json ->> 'a'""", # 1
116+
"select 1 << 3", # 8
117+
"select 'abc' ~ 'b'", # t, regex match
118+
"""select '{"a":1}'::jsonb @> '{"a":1}'""", # t, contains
119+
"select 'a' || 'b'", # ab
120+
):
121+
assert strip_trailing_comments(sql) == sql, sql
122+
123+
124+
def test_double_dash_without_spaces_is_a_comment_to_postgres_too():
125+
"""`select 5--3` answers 5, not 8: the server reads --3 as a comment, so
126+
dropping it here matches what the server would have done."""
127+
assert strip_trailing_comments("select 5--3") == "select 5"
128+
129+
130+
def test_hash_inside_a_trailing_comment_is_still_a_comment():
131+
assert strip_trailing_comments("select 1; -- see issue #1646") == "select 1;"

‎tests/test_trailing_comments.py‎

Lines changed: 15 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -45,3 +45,18 @@ def test_multiple_semicolons_with_comment(self):
4545

4646
def test_comment_with_special_chars(self):
4747
assert _is_complete("VACUUM ANALYZE; -- 81.0% towards emergency, 971 MB") is True
48+
49+
50+
class TestIsCompleteWithTheXorOperator:
51+
"""``#`` is bitwise XOR in PostgreSQL. While trailing comments were being
52+
stripped with sqlparse, which reads ``#`` as MySQL does, a statement using
53+
it never looked complete (dbcli/pgcli#1646)."""
54+
55+
def test_xor_statement_is_complete(self):
56+
assert _is_complete("select 17 # 5;") is True
57+
58+
def test_xor_statement_with_a_trailing_comment(self):
59+
assert _is_complete("select 17 # 5; -- note") is True
60+
61+
def test_xor_without_a_semicolon_is_not_complete(self):
62+
assert _is_complete("select 17 # 5") is False

0 commit comments

Comments
 (0)