You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@pinot.apache.org by xi...@apache.org on 2026/09/22 00:57:58 UTC
(pinot) branch master updated: Support PostgreSQL hex bytea literals (#19263)
This is an automated email from the ASF dual-hosted git repository.
xiangfu0 pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/pinot.git
The following commit(s) were added to refs/heads/master by this push:
new ff2eee7fafb Support PostgreSQL hex bytea literals (#19263)
ff2eee7fafb is described below
commit ff2eee7fafb83d9a0886fc40bd620e010196e30a
Author: Xiang Fu <xi...@apache.org>
AuthorDate: Mon Sep 21 17:57:51 2026 -0700
Support PostgreSQL hex bytea literals (#19263)
* Support PostgreSQL hex bytea literals
Accept PostgreSQL hex-format BYTEA constants in both the infix `'\x0102'::bytea`
form and the standard `CAST('\x0102' AS BYTEA)` spelling, normalizing them to
Pinot's existing binary literal representation before either engine plans the
query. The SQL-standard `X'0102'` form is unchanged.
Only quoted constants are normalized. A dynamic STRING-to-BYTES conversion would
have to be implemented per row in each engine, and the two would be easy to
drift apart. PostgreSQL's legacy octal escape format stays unsupported.
Parsing:
- `InfixCast` appends the operator and target type to the enclosing expression
list as a `SqlParserUtil.ToTreeListItem`, so precedence is resolved by
`Expression2` the same way as the adjacent `SqlAtTimeZone` and Calcite's own
Babel production. Collapsing the list eagerly would make `::` bind everything
to its left, which breaks the most common shape:
`WHERE bytesCol = '\x01'::bytea` would parse as
`CAST(bytesCol = '\x01' AS BYTEA)`.
- The `[]` and `.ident` postfix forms need no handling here; `Expression2`
already owns those branches.
Rewriting:
- Only ASCII hex digits and ASCII whitespace are accepted. `Character.digit` and
`Character.isWhitespace` also accept full-width and non-Latin forms, which
PostgreSQL rejects. Whitespace is allowed between byte pairs but not inside
one, matching PostgreSQL.
- A `::` whose target type does not survive as a type spec (`col::bytea[1]`,
where the item accessor binds tighter) is rejected rather than passed on as a
malformed call.
- Non-BYTEA `::` casts are rejected. This is a deliberate scope limit, not a
technical one: `INFIX_CAST` is an ordinary `SqlCastOperator` of kind `CAST`,
so `intCol::double` would plan like `CAST(intCol AS DOUBLE)`. Supporting the
full operator means committing to `::` type-name semantics across both
engines and is left to a follow-up. The grammar accepts `::` so that this
rewriter can report a clear error instead of a parse failure.
- Error messages name the offending type or expression and distinguish a
malformed hex constant from a non-constant operand from an unsupported target.
Tests cover scalar literals, BYTES array construction, bytea constants as the
right operand of `=`, `<>`, `>`, `<=` and inside compound predicates, agreement
between the `::`, `CAST` and `X'...'` spellings in both parse paths and both
cases, ingested multi-value BYTES predicates, planner type inference, and
runtime execution against H2.
* Rewrite bytea constants in place to preserve Pinot statement nodes
The rewriter extended SqlShuttle, which is copy-on-write: replacing a constant
rebuilt every ancestor through SqlOperator.createCall, and that does not
preserve Pinot's own statement classes. EXPLAIN IMPLEMENTATION PLAN FOR with a
bytea constant came back as a plain SqlExplain, so the broker silently returned
the logical plan, and CREATE MATERIALIZED VIEW came back as a SqlBasicCall
classified as DQL, so the DDL compiler rejected it.
Replace only the constant's direct parent, through SqlCall.setOperand or
SqlNodeList.set, so every other node keeps its identity and class. Every node
under which Pinot's grammar accepts an arbitrary expression supports this; the
exceptions are positions the grammar restricts, such as SqlOrderBy's numeric
offset and fetch, and Pinot's own statement nodes, which hold queries,
identifiers and literals. A parent that cannot be updated in place is rejected
with a clear error instead of being rebuilt.
Record the position of the `::` token itself in InfixCast, as
BinaryRowOperator does. s.pos() started at the token before the enclosing
expression, so the cast and its rewritten literal claimed spans such as
`WHERE b = '\x01'::bytea`, which misplaced validator error highlights.
Also from review:
- Name hexToBytes(<expr>) as the per-row alternative when a BYTEA cast has a
non-constant operand.
- Say that only the hex format is supported when a constant lacks the \x
prefix. PostgreSQL reads '0102'::bytea as escape format (four bytes).
- Run the rewrite outside compileToExpression's catch-all so its messages reach
the caller instead of only its cause.
- Link the BYTEA-only scope of `::` to the follow-up issue, #19628.
- Test X'...'::bytea and CAST(X'...' AS BYTEA), the case-sensitive \x prefix,
an IN list, the literal's source span, node class and statement type for
EXPLAIN and CREATE MATERIALIZED VIEW (including a nested subquery), the
physical plan returned by EXPLAIN IMPLEMENTATION PLAN, and the stored
definition of a materialized view whose body contains a bytea constant.
---
.../LiteralOnlyBrokerRequestTest.java | 50 ++++-
pinot-common/src/main/codegen/config.fmpp | 2 +
.../src/main/codegen/includes/parserImpls.ftl | 25 +++
.../apache/pinot/sql/parsers/CalciteSqlParser.java | 3 +
.../pinot/sql/parsers/PostgreSqlCastRewriter.java | 201 ++++++++++++++++++
.../pinot/common/request/LiteralSerDeTest.java | 2 +
.../common/utils/request/RequestUtilsTest.java | 6 +
.../pinot/sql/parsers/CalciteSqlParserTest.java | 233 +++++++++++++++++++++
.../integration/tests/custom/BytesMvTypeTest.java | 56 ++---
.../integration/tests/custom/BytesTypeTest.java | 21 ++
.../apache/pinot/query/QueryCompilationTest.java | 26 +++
.../src/test/resources/queries/BinaryTypes.json | 27 ++-
.../compile/DdlCompilerMaterializedViewTest.java | 22 ++
13 files changed, 634 insertions(+), 40 deletions(-)
diff --git a/pinot-broker/src/test/java/org/apache/pinot/broker/requesthandler/LiteralOnlyBrokerRequestTest.java b/pinot-broker/src/test/java/org/apache/pinot/broker/requesthandler/LiteralOnlyBrokerRequestTest.java
index 5db658610dc..fa3e4eca876 100644
--- a/pinot-broker/src/test/java/org/apache/pinot/broker/requesthandler/LiteralOnlyBrokerRequestTest.java
+++ b/pinot-broker/src/test/java/org/apache/pinot/broker/requesthandler/LiteralOnlyBrokerRequestTest.java
@@ -97,12 +97,16 @@ public class LiteralOnlyBrokerRequestTest {
assertEquals((int[]) resultTable.getRows().get(0)[0], new int[]{1, 2});
assertEquals((String[]) resultTable.getRows().get(0)[1], new String[]{"one", "two"});
- brokerResponse = requestHandler.handleRequest("SELECT ARRAY[X'00', X'0102'] AS bytes");
- resultTable = brokerResponse.getResultTable();
- assertEquals(resultTable.getDataSchema().getColumnName(0), "bytes");
- assertEquals(resultTable.getDataSchema().getColumnDataType(0), DataSchema.ColumnDataType.BYTES_ARRAY);
- assertEquals(resultTable.getRows().size(), 1);
- assertEquals(resultTable.getRows().get(0), new Object[]{new String[]{"00", "0102"}});
+ // The SQL-standard and PostgreSQL spellings must produce the same BYTES_ARRAY response.
+ for (String arrayLiteral : List.of("ARRAY[X'00', X'0102']", "ARRAY['\\x00'::bytea, '\\x0102'::bytea]",
+ "ARRAY[CAST('\\x00' AS BYTEA), CAST('\\x0102' AS BYTEA)]")) {
+ brokerResponse = requestHandler.handleRequest("SELECT " + arrayLiteral + " AS bytes");
+ resultTable = brokerResponse.getResultTable();
+ assertEquals(resultTable.getDataSchema().getColumnName(0), "bytes");
+ assertEquals(resultTable.getDataSchema().getColumnDataType(0), DataSchema.ColumnDataType.BYTES_ARRAY);
+ assertEquals(resultTable.getRows().size(), 1);
+ assertEquals(resultTable.getRows().get(0), new Object[]{new String[]{"00", "0102"}});
+ }
brokerResponse = requestHandler.handleRequest(
"SELECT ARRAYS_OVERLAP(ARRAY[X'00', X'0102'], ARRAY[X'03', X'0102']) AS overlaps");
@@ -111,6 +115,40 @@ public class LiteralOnlyBrokerRequestTest {
assertEquals(resultTable.getRows().get(0)[0], true);
}
+ /// A scalar bytea constant must be answered by the literal-only path exactly like `X'...'`, in both spellings.
+ @Test
+ public void testScalarBytesLiteralBrokerRequestFromSQL()
+ throws Exception {
+ SingleConnectionBrokerRequestHandler requestHandler =
+ new SingleConnectionBrokerRequestHandler(new PinotConfiguration(), "testBrokerId",
+ new BrokerRequestIdGenerator(), null, ACCESS_CONTROL_FACTORY, null, null, null, null,
+ mock(ServerRoutingStatsManager.class), mock(FailureDetector.class),
+ ThreadAccountantUtils.getNoOpAccountant(), null, null);
+
+ for (String literal : List.of("X'0102'", "'\\x0102'::bytea", "CAST('\\x0102' AS BYTEA)")) {
+ assertTrue(isLiteralOnlyQuery(CalciteSqlParser.compileToPinotQuery("SELECT " + literal)));
+ BrokerResponse brokerResponse = requestHandler.handleRequest("SELECT " + literal + " AS b");
+ ResultTable resultTable = brokerResponse.getResultTable();
+ assertTrue(brokerResponse.getExceptions().isEmpty(), literal);
+ assertEquals(resultTable.getDataSchema().getColumnDataType(0), DataSchema.ColumnDataType.BYTES, literal);
+ assertEquals(resultTable.getRows().get(0)[0], "0102", literal);
+ }
+
+ // An empty bytea constant is legal in PostgreSQL and yields zero-length BYTES.
+ BrokerResponse brokerResponse = requestHandler.handleRequest("SELECT '\\x'::bytea AS b");
+ assertEquals(brokerResponse.getResultTable().getDataSchema().getColumnDataType(0),
+ DataSchema.ColumnDataType.BYTES);
+ assertEquals(brokerResponse.getResultTable().getRows().get(0)[0], "");
+
+ // Constant folding over bytea elements still happens at parse time, so the query stays literal-only.
+ String folded = "SELECT ARRAY_LENGTH(ARRAY['\\x00'::bytea, '\\x0102'::bytea]) AS n";
+ assertTrue(isLiteralOnlyQuery(CalciteSqlParser.compileToPinotQuery(folded)));
+ brokerResponse = requestHandler.handleRequest(folded);
+ assertEquals(brokerResponse.getResultTable().getDataSchema().getColumnDataType(0),
+ DataSchema.ColumnDataType.INT);
+ assertEquals(brokerResponse.getResultTable().getRows().get(0)[0], 2);
+ }
+
@Test
public void testLiteralOnlyTransformBrokerRequestFromSQL() {
assertTrue(isLiteralOnlyQuery(CalciteSqlParser.compileToPinotQuery("SELECT now()")));
diff --git a/pinot-common/src/main/codegen/config.fmpp b/pinot-common/src/main/codegen/config.fmpp
index 32b71647386..7e3493d183b 100644
--- a/pinot-common/src/main/codegen/config.fmpp
+++ b/pinot-common/src/main/codegen/config.fmpp
@@ -654,11 +654,13 @@ data: {
# Binary operators tokens.
# Example: "< INFIX_CAST: \"::\" >".
binaryOperatorsTokens: [
+ "< INFIX_CAST: \"::\" >"
]
# Binary operators initialization.
# Example: "InfixCast".
extraBinaryExpressions: [
+ "InfixCast"
"SqlAtTimeZone"
]
diff --git a/pinot-common/src/main/codegen/includes/parserImpls.ftl b/pinot-common/src/main/codegen/includes/parserImpls.ftl
index 06070cff935..161fad10ca3 100644
--- a/pinot-common/src/main/codegen/includes/parserImpls.ftl
+++ b/pinot-common/src/main/codegen/includes/parserImpls.ftl
@@ -88,6 +88,31 @@ void SqlAtTimeZone(List<Object> list, ExprContext exprContext, Span s) :
}
}
+/// Parses the PostgreSQL infix `::` cast operator. The operator and its target type are appended to the enclosing
+/// expression list so `SqlParserUtil.toTree` applies the standard precedence rules, exactly like `SqlAtTimeZone`
+/// above; collapsing the list here instead would make `::` bind the whole expression to its left. PostgreSQL BYTEA
+/// literals are normalized after parsing so both query engines receive the same binary literal representation as
+/// SQL `X'...'`.
+///
+/// The operator records the position of the `::` token itself, as `BinaryRowOperator` does; `toTree` then widens it
+/// to cover the operands. `s.pos()` would instead start at the token before the enclosing expression, so the cast
+/// and the literal it is rewritten to would claim a span such as `WHERE b = '\x01'::bytea`.
+void InfixCast(List<Object> list, ExprContext exprContext, Span s) :
+{
+ final SqlParserPos pos;
+ final SqlDataTypeSpec dataType;
+}
+{
+ <INFIX_CAST> {
+ checkNonQueryExpression(exprContext);
+ pos = getPos();
+ }
+ dataType = DataType() {
+ list.add(new SqlParserUtil.ToTreeListItem(SqlLibraryOperators.INFIX_CAST, pos));
+ list.add(dataType);
+ }
+}
+
SqlNode SqlPhysicalExplain() :
{
SqlNode stmt;
diff --git a/pinot-common/src/main/java/org/apache/pinot/sql/parsers/CalciteSqlParser.java b/pinot-common/src/main/java/org/apache/pinot/sql/parsers/CalciteSqlParser.java
index d50f0f1e8dd..0d85fb9b03d 100644
--- a/pinot-common/src/main/java/org/apache/pinot/sql/parsers/CalciteSqlParser.java
+++ b/pinot-common/src/main/java/org/apache/pinot/sql/parsers/CalciteSqlParser.java
@@ -140,6 +140,7 @@ public class CalciteSqlParser {
try (StringReader inStream = new StringReader(sql)) {
SqlParserImpl sqlParser = newSqlParser(inStream);
SqlNodeList sqlNodeList = sqlParser.parseSqlStmtList();
+ sqlNodeList = (SqlNodeList) PostgreSqlCastRewriter.rewrite(sqlNodeList);
// Extract OPTION statements from sql.
SqlNodeAndOptions sqlNodeAndOptions = extractSqlNodeAndOptions(sqlNodeList);
// add legacy OPTIONS keyword-based options
@@ -672,6 +673,8 @@ public class CalciteSqlParser {
} catch (Throwable e) {
throw new SqlCompilationException("Caught exception while parsing expression: " + expression, e);
}
+ // Outside the try: the rewriter already throws SqlCompilationException, and wrapping it would drop its message.
+ sqlNode = PostgreSqlCastRewriter.rewrite(sqlNode);
return toExpression(sqlNode);
}
diff --git a/pinot-common/src/main/java/org/apache/pinot/sql/parsers/PostgreSqlCastRewriter.java b/pinot-common/src/main/java/org/apache/pinot/sql/parsers/PostgreSqlCastRewriter.java
new file mode 100644
index 00000000000..ed6974e0e78
--- /dev/null
+++ b/pinot-common/src/main/java/org/apache/pinot/sql/parsers/PostgreSqlCastRewriter.java
@@ -0,0 +1,201 @@
+/**
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+package org.apache.pinot.sql.parsers;
+
+import java.util.List;
+import org.apache.calcite.sql.SqlBinaryStringLiteral;
+import org.apache.calcite.sql.SqlCall;
+import org.apache.calcite.sql.SqlCharStringLiteral;
+import org.apache.calcite.sql.SqlDataTypeSpec;
+import org.apache.calcite.sql.SqlKind;
+import org.apache.calcite.sql.SqlNode;
+import org.apache.calcite.sql.SqlNodeList;
+import org.apache.calcite.sql.fun.SqlLibraryOperators;
+import org.apache.calcite.sql.util.SqlShuttle;
+
+
+/// Normalizes PostgreSQL hex-format BYTEA constants (`'\x0102'::bytea` and `CAST('\x0102' AS BYTEA)`) to Calcite
+/// binary literals, so both query engines see the same representation they would get from SQL `X'0102'`. Only quoted
+/// constants are normalized: a dynamic `STRING`-to-`BYTES` conversion would have to be implemented per row in each
+/// engine, and the two would be easy to drift apart.
+///
+/// Every other use of the PostgreSQL `::` operator is rejected here. That is a deliberate scope limit, not a
+/// technical one — `SqlLibraryOperators.INFIX_CAST` is an ordinary `SqlCastOperator` of kind
+/// [org.apache.calcite.sql.SqlKind#CAST], so `intCol::double` would in fact plan and run like `CAST(intCol AS
+/// DOUBLE)`. Supporting the full operator means committing to `::` type-name semantics across both engines and is
+/// tracked in https://github.com/apache/pinot/issues/19628; until then the grammar accepts `::` only so that this
+/// rewriter can give a clear error instead of a parse failure. Rejecting now keeps that open: relaxing a rejection
+/// later is backward compatible, while narrowing an accepted type would not be.
+///
+/// The tree is rewritten in place. A plain [SqlShuttle] is copy-on-write: it rebuilds every ancestor of a replaced
+/// node through `SqlOperator.createCall`, and that does not preserve Pinot's own statement classes. A
+/// `SqlPhysicalExplain` comes back as a plain `SqlExplain` and a `SqlPinotCreateMaterializedView` as a `SqlBasicCall`,
+/// which silently changes how the statement is planned or classified. Here only a constant's direct parent is
+/// modified, through `SqlCall.setOperand` or `SqlNodeList.set`, so every other node keeps its identity and class.
+/// Every node under which Pinot's grammar accepts an arbitrary expression supports that. The exceptions are positions
+/// the grammar restricts: `SqlOrderBy` holds its offset and fetch directly, but they must be numeric, and Pinot's own
+/// statement nodes hold queries, identifiers and literals rather than bare expressions. A parent that cannot be
+/// updated in place is rejected with a clear error rather than rebuilt.
+///
+/// Stateless and safe to share across threads. It mutates only the tree passed to `rewrite`, which must be owned by
+/// the caller, such as a freshly parsed statement.
+final class PostgreSqlCastRewriter extends SqlShuttle {
+ private static final PostgreSqlCastRewriter INSTANCE = new PostgreSqlCastRewriter();
+ private static final String HEX_PREFIX = "\\x";
+ private static final String BYTEA_TYPE_NAME = "BYTEA";
+
+ private PostgreSqlCastRewriter() {
+ }
+
+ /// Rewrites `sqlNode` in place and returns it, or returns the replacement literal when `sqlNode` is itself a bytea
+ /// constant, as for an expression parsed on its own.
+ static SqlNode rewrite(SqlNode sqlNode) {
+ return sqlNode.accept(INSTANCE);
+ }
+
+ @Override
+ public SqlNode visit(SqlNodeList nodeList) {
+ for (int i = 0; i < nodeList.size(); i++) {
+ SqlNode node = nodeList.get(i);
+ if (node != null) {
+ SqlNode rewritten = node.accept(this);
+ if (rewritten != node) {
+ try {
+ nodeList.set(i, rewritten);
+ } catch (UnsupportedOperationException e) {
+ throw unsupportedParent("an immutable node list", e);
+ }
+ }
+ }
+ }
+ return nodeList;
+ }
+
+ @Override
+ public SqlNode visit(SqlCall call) {
+ List<SqlNode> operands = call.getOperandList();
+ for (int i = 0; i < operands.size(); i++) {
+ SqlNode operand = operands.get(i);
+ if (operand != null) {
+ SqlNode rewritten = operand.accept(this);
+ if (rewritten != operand) {
+ setOperand(call, i, rewritten);
+ }
+ }
+ }
+ return rewriteCast(call);
+ }
+
+ private static void setOperand(SqlCall parent, int index, SqlNode replacement) {
+ try {
+ parent.setOperand(index, replacement);
+ } catch (UnsupportedOperationException e) {
+ throw unsupportedParent(parent.getKind().toString(), e);
+ }
+ }
+
+ /// Not reachable from Pinot's grammar today. Fails with a clear error if a future node holds a bytea constant where
+ /// it cannot be replaced in place, rather than falling back to rebuilding the node.
+ private static SqlCompilationException unsupportedParent(String parent, UnsupportedOperationException cause) {
+ return new SqlCompilationException(
+ "PostgreSQL BYTEA constants are not supported inside " + parent + "; use X'...' instead", cause);
+ }
+
+ /// Returns the binary literal that a supported bytea constant normalizes to, or `call` itself when it is not a cast
+ /// this rewriter handles. Throws for every other use of the PostgreSQL `::` operator.
+ private static SqlNode rewriteCast(SqlCall call) {
+ List<SqlNode> operands = call.getOperandList();
+ boolean infixCast = call.getOperator() == SqlLibraryOperators.INFIX_CAST;
+ if (call.getKind() != SqlKind.CAST || operands.size() != 2 || !(operands.get(1) instanceof SqlDataTypeSpec)) {
+ if (infixCast) {
+ // The target type did not survive as a type spec, e.g. `col::bytea[1]`, where the item accessor binds
+ // tighter than `::`. Reject it here rather than letting the malformed call reach the planner.
+ throw new SqlCompilationException("Unsupported PostgreSQL :: cast target in '" + call
+ + "'. Note that [] binds tighter than ::, so write CAST(<expr> AS <type>) instead");
+ }
+ return call;
+ }
+
+ SqlDataTypeSpec targetType = (SqlDataTypeSpec) operands.get(1);
+ boolean bytea = targetType.getTypeName().isSimple()
+ && targetType.getTypeName().getSimple().equalsIgnoreCase(BYTEA_TYPE_NAME);
+ if (!bytea) {
+ if (infixCast) {
+ throw new SqlCompilationException("PostgreSQL-style :: casts are supported only for BYTEA hex constants, "
+ + "not for target type '" + targetType.getTypeName() + "'. Use CAST(<expr> AS <type>) instead");
+ }
+ return call;
+ }
+
+ SqlNode source = operands.get(0);
+ if (source instanceof SqlBinaryStringLiteral) {
+ return source;
+ }
+ if (!(source instanceof SqlCharStringLiteral)) {
+ throw new SqlCompilationException("BYTEA casts are supported only for quoted hex constants such as "
+ + "'\\x0102', not for the expression '" + source + "'. To convert hex strings per row, use "
+ + "hexToBytes(<expr>), which takes plain hexadecimal digits without the \\x prefix");
+ }
+ String value = ((SqlCharStringLiteral) source).getValueAs(String.class);
+ if (!value.startsWith(HEX_PREFIX)) {
+ throw invalidByteaLiteral(value);
+ }
+ return SqlBinaryStringLiteral.createBinaryString(normalizeHex(value), call.getParserPosition());
+ }
+
+ /// Decodes the digits after the leading `\x`. PostgreSQL allows whitespace between byte pairs but not inside one.
+ private static String normalizeHex(String literal) {
+ String value = literal.substring(HEX_PREFIX.length());
+ StringBuilder hex = new StringBuilder(value.length());
+ for (int i = 0; i < value.length(); i++) {
+ char c = value.charAt(i);
+ if (isAsciiWhitespace(c)) {
+ if ((hex.length() & 1) != 0) {
+ throw invalidByteaLiteral(literal);
+ }
+ } else if (isAsciiHexDigit(c)) {
+ hex.append(c);
+ } else {
+ throw invalidByteaLiteral(literal);
+ }
+ }
+ if ((hex.length() & 1) != 0) {
+ throw invalidByteaLiteral(literal);
+ }
+ return hex.toString();
+ }
+
+ /// Deliberately not `Character.digit(c, 16)`: that also accepts full-width and non-Latin digits such as `Ａ` and
+ /// `١`, which PostgreSQL rejects.
+ private static boolean isAsciiHexDigit(char c) {
+ return (c >= '0' && c <= '9') || (c >= 'a' && c <= 'f') || (c >= 'A' && c <= 'F');
+ }
+
+ /// Deliberately not `Character.isWhitespace(c)`, for the same reason as [#isAsciiHexDigit]: PostgreSQL only skips
+ /// ASCII whitespace between byte pairs, so a non-ASCII space such as U+205F between them is an error.
+ private static boolean isAsciiWhitespace(char c) {
+ return c == ' ' || c == '\t' || c == '\n' || c == '\r' || c == '\f' || c == 0x0B;
+ }
+
+ private static SqlCompilationException invalidByteaLiteral(String value) {
+ return new SqlCompilationException("Invalid PostgreSQL BYTEA constant '" + value + "': only the hex format is "
+ + "supported, so it must begin with \\x and contain complete hexadecimal byte pairs, optionally separated by "
+ + "ASCII whitespace");
+ }
+}
diff --git a/pinot-common/src/test/java/org/apache/pinot/common/request/LiteralSerDeTest.java b/pinot-common/src/test/java/org/apache/pinot/common/request/LiteralSerDeTest.java
index 1056f9a11ba..5727cb145ed 100644
--- a/pinot-common/src/test/java/org/apache/pinot/common/request/LiteralSerDeTest.java
+++ b/pinot-common/src/test/java/org/apache/pinot/common/request/LiteralSerDeTest.java
@@ -93,7 +93,9 @@ public class LiteralSerDeTest {
public void testSingleStageQueryUsesNativeBytesArrayLiteral()
throws TException {
for (String sql : List.of("SELECT ARRAY[X'00', X'0102'] FROM myTable",
+ "SELECT ARRAY['\\x00'::bytea, CAST('\\x0102' AS BYTEA)] FROM myTable",
"SELECT id FROM myTable WHERE ARRAYS_OVERLAP(bytesMV, ARRAY[X'01'])",
+ "SELECT id FROM myTable WHERE ARRAYS_OVERLAP(bytesMV, ARRAY['\\x01'::bytea])",
"SELECT ARRAY[X'02'], COUNT(*) FROM myTable GROUP BY ARRAY[X'02']",
"SELECT COUNT(*) FROM myTable HAVING ARRAYS_OVERLAP(ARRAYAGG(bytesColumn, 'BYTES'), ARRAY[X'03'])",
"SELECT id FROM myTable "
diff --git a/pinot-common/src/test/java/org/apache/pinot/common/utils/request/RequestUtilsTest.java b/pinot-common/src/test/java/org/apache/pinot/common/utils/request/RequestUtilsTest.java
index e9acb5336de..a65148b3790 100644
--- a/pinot-common/src/test/java/org/apache/pinot/common/utils/request/RequestUtilsTest.java
+++ b/pinot-common/src/test/java/org/apache/pinot/common/utils/request/RequestUtilsTest.java
@@ -126,6 +126,12 @@ public class RequestUtilsTest {
assertTrue(expression.getLiteral().isSetBytesArrayValue());
assertEquals(RequestUtils.getBytesArrayValue(expression.getLiteral()), expected);
+ expression = CalciteSqlParser.compileToPinotQuery(
+ "SELECT ARRAY['\\x00'::bytea, CAST('\\xDEADBEEF' AS BYTEA)] FROM myTable").getSelectList().get(0);
+ assertTrue(expression.isSetLiteral());
+ assertTrue(expression.getLiteral().isSetBytesArrayValue());
+ assertEquals(RequestUtils.getBytesArrayValue(expression.getLiteral()), expected);
+
expression = CalciteSqlParser.compileToPinotQuery(
"SELECT ARRAYS_OVERLAP(ARRAY[X'00', X'0102'], ARRAY[X'03', X'0102'])").getSelectList().get(0);
assertTrue(expression.isSetLiteral());
diff --git a/pinot-common/src/test/java/org/apache/pinot/sql/parsers/CalciteSqlParserTest.java b/pinot-common/src/test/java/org/apache/pinot/sql/parsers/CalciteSqlParserTest.java
index dec47000efb..552aeea604c 100644
--- a/pinot-common/src/test/java/org/apache/pinot/sql/parsers/CalciteSqlParserTest.java
+++ b/pinot-common/src/test/java/org/apache/pinot/sql/parsers/CalciteSqlParserTest.java
@@ -18,17 +18,29 @@
*/
package org.apache.pinot.sql.parsers;
+import java.io.StringReader;
import java.util.List;
+import org.apache.calcite.sql.SqlBinaryStringLiteral;
+import org.apache.calcite.sql.SqlCall;
+import org.apache.calcite.sql.SqlExplain;
+import org.apache.calcite.sql.SqlNode;
+import org.apache.calcite.sql.SqlNodeList;
+import org.apache.calcite.sql.SqlOrderBy;
+import org.apache.calcite.sql.SqlSelect;
+import org.apache.calcite.sql.parser.SqlParserPos;
import org.apache.pinot.common.request.Expression;
import org.apache.pinot.common.request.Function;
import org.apache.pinot.common.request.PinotQuery;
import org.apache.pinot.common.utils.request.RequestUtils;
+import org.apache.pinot.sql.parsers.parser.SqlPhysicalExplain;
+import org.apache.pinot.sql.parsers.parser.SqlPinotCreateMaterializedView;
import org.testng.annotations.AfterMethod;
import org.testng.annotations.DataProvider;
import org.testng.annotations.Test;
import static org.apache.pinot.sql.parsers.CalciteSqlParser.CALCITE_SQL_PARSER_IDENTIFIER_MAX_LENGTH;
import static org.testng.Assert.assertEquals;
+import static org.testng.Assert.assertFalse;
import static org.testng.Assert.assertThrows;
import static org.testng.Assert.assertTrue;
import static org.testng.Assert.expectThrows;
@@ -57,6 +69,226 @@ public class CalciteSqlParserTest {
RequestUtils.setUseLegacyLiteralUnescaping(true);
}
+ @Test
+ public void testPostgreSqlByteaHexLiterals() {
+ PinotQuery pinotQuery = CalciteSqlParser.compileToPinotQuery("SELECT '\\x0102'::bytea");
+ assertEquals(pinotQuery.getSelectList().get(0).getLiteral().getBinaryValue(), new byte[]{1, 2});
+
+ pinotQuery = CalciteSqlParser.compileToPinotQuery("SELECT CAST('\\xDe Ad Be Ef' AS BYTEA)");
+ assertEquals(pinotQuery.getSelectList().get(0).getLiteral().getBinaryValue(),
+ new byte[]{(byte) 0xde, (byte) 0xad, (byte) 0xbe, (byte) 0xef});
+
+ pinotQuery = CalciteSqlParser.compileToPinotQuery("SELECT '\\x'::ByTeA");
+ assertEquals(pinotQuery.getSelectList().get(0).getLiteral().getBinaryValue(), new byte[0]);
+
+ Expression expression = CalciteSqlParser.compileToExpression("'\\x0102'::bytea");
+ assertEquals(expression.getLiteral().getBinaryValue(), new byte[]{1, 2});
+ }
+
+ /// `::` must bind only to the literal on its left, not to the whole expression accumulated so far, so that a bytea
+ /// constant can be used as the right operand of a comparison.
+ @Test(dataProvider = "binaryOperatorsTakingByteaLiterals")
+ public void testPostgreSqlByteaLiteralBindsTighterThanBinaryOperators(String predicate, String expectedOperator) {
+ PinotQuery pinotQuery = CalciteSqlParser.compileToPinotQuery(
+ "SELECT id FROM myTable WHERE bytesColumn " + predicate + " '\\x0102'::bytea");
+ Function filter = pinotQuery.getFilterExpression().getFunctionCall();
+ assertEquals(filter.getOperator(), expectedOperator);
+ assertEquals(filter.getOperands().get(0).getIdentifier().getName(), "bytesColumn");
+ assertEquals(filter.getOperands().get(1).getLiteral().getBinaryValue(), new byte[]{1, 2});
+ }
+
+ @DataProvider
+ public static Object[][] binaryOperatorsTakingByteaLiterals() {
+ return new Object[][]{
+ {"=", "EQUALS"},
+ {"<>", "NOT_EQUALS"},
+ {">", "GREATER_THAN"},
+ {"<=", "LESS_THAN_OR_EQUAL"}
+ };
+ }
+
+ @Test
+ public void testPostgreSqlByteaLiteralInCompoundPredicate() {
+ PinotQuery pinotQuery = CalciteSqlParser.compileToPinotQuery(
+ "SELECT id FROM myTable WHERE id = 1 AND bytesColumn = '\\x0102'::bytea");
+ Function and = pinotQuery.getFilterExpression().getFunctionCall();
+ assertEquals(and.getOperator(), "AND");
+ Function byteaEquals = and.getOperands().get(1).getFunctionCall();
+ assertEquals(byteaEquals.getOperands().get(0).getIdentifier().getName(), "bytesColumn");
+ assertEquals(byteaEquals.getOperands().get(1).getLiteral().getBinaryValue(), new byte[]{1, 2});
+ }
+
+ /// The infix and standard cast spellings must normalize to the same binary literal wherever they appear, including
+ /// when the source is already a binary literal.
+ @Test
+ public void testPostgreSqlByteaLiteralMatchesStandardCastSpelling() {
+ for (String bytea : List.of("'\\x0102'::bytea", "'\\x0102' :: ByTeA", "CAST('\\x0102' AS BYTEA)",
+ "cast('\\x0102' as bytea)", "X'0102'", "X'0102'::bytea", "CAST(X'0102' AS BYTEA)")) {
+ PinotQuery pinotQuery =
+ CalciteSqlParser.compileToPinotQuery("SELECT id FROM myTable WHERE bytesColumn = " + bytea);
+ assertEquals(pinotQuery.getFilterExpression().getFunctionCall().getOperands().get(1).getLiteral()
+ .getBinaryValue(), new byte[]{1, 2}, bytea);
+ assertEquals(CalciteSqlParser.compileToExpression(bytea).getLiteral().getBinaryValue(), new byte[]{1, 2},
+ bytea);
+ }
+ }
+
+ /// Regression coverage for rebuilding Pinot's own statement nodes. A copy-on-write rewrite turned a
+ /// `SqlPhysicalExplain` into a plain `SqlExplain`, which silently returned the logical plan, and a
+ /// `SqlPinotCreateMaterializedView` into a `SqlBasicCall` classified as DQL. The statement must keep the class and
+ /// type it has with the equivalent `X'...'` literal, and the constant must still be normalized inside it.
+ @Test(dataProvider = "statementsContainingByteaLiterals")
+ public void testPostgreSqlByteaLiteralPreservesStatementNode(String sql, Class<?> expectedClass,
+ PinotSqlType expectedType) {
+ for (String bytea : List.of("X'01'", "'\\x01'::bytea", "CAST('\\x01' AS BYTEA)")) {
+ String statement = sql.replace("?", bytea);
+ SqlNodeAndOptions sqlNodeAndOptions = CalciteSqlParser.compileToSqlNodeAndOptions(statement);
+ SqlNode sqlNode = sqlNodeAndOptions.getSqlNode();
+ assertEquals(sqlNode.getClass(), expectedClass, statement);
+ assertEquals(sqlNodeAndOptions.getSqlType(), expectedType, statement);
+ String unparsed = sqlNode.toString();
+ assertTrue(unparsed.contains("X'01'"), statement + " unparsed as " + unparsed);
+ assertFalse(unparsed.toUpperCase().contains("BYTEA"), statement + " unparsed as " + unparsed);
+ }
+ }
+
+ @DataProvider
+ public static Object[][] statementsContainingByteaLiterals() {
+ return new Object[][]{
+ {"EXPLAIN IMPLEMENTATION PLAN FOR SELECT a FROM t WHERE b = ?", SqlPhysicalExplain.class, PinotSqlType.DQL},
+ {"EXPLAIN PLAN FOR SELECT a FROM t WHERE b = ?", SqlExplain.class, PinotSqlType.DQL},
+ {"CREATE MATERIALIZED VIEW mv AS SELECT a FROM t WHERE b = ?", SqlPinotCreateMaterializedView.class,
+ PinotSqlType.DDL},
+ {"CREATE MATERIALIZED VIEW mv AS SELECT a FROM t WHERE a IN (SELECT a FROM u WHERE b = ?)",
+ SqlPinotCreateMaterializedView.class, PinotSqlType.DDL},
+ {"SELECT a FROM t WHERE b = ? ORDER BY a LIMIT 5", SqlOrderBy.class, PinotSqlType.DQL}
+ };
+ }
+
+ /// A node that cannot replace an operand in place must fail with a clear error rather than be rebuilt with a
+ /// different class. Pinot's grammar never puts a bytea constant directly under such a node, so the tree is built
+ /// by hand.
+ @Test
+ public void testPostgreSqlByteaLiteralUnderNodeWithoutSetOperandIsRejected()
+ throws Exception {
+ SqlNode query = CalciteSqlParser.newSqlParser(new StringReader("SELECT a FROM t")).parseSqlStmtEof();
+ SqlNode bytea = CalciteSqlParser.newSqlParser(new StringReader("'\\x01'::bytea")).parseSqlExpressionEof();
+ SqlOrderBy orderBy = new SqlOrderBy(SqlParserPos.ZERO, query, SqlNodeList.EMPTY, bytea, null);
+ SqlCompilationException e =
+ expectThrows(SqlCompilationException.class, () -> PostgreSqlCastRewriter.rewrite(orderBy));
+ assertTrue(e.getMessage().contains("not supported inside ORDER_BY"), e.getMessage());
+ }
+
+ /// Same guard for a node list that cannot be updated in place, such as one wrapping an immutable list.
+ @Test
+ public void testPostgreSqlByteaLiteralInImmutableNodeListIsRejected()
+ throws Exception {
+ SqlNode bytea = CalciteSqlParser.newSqlParser(new StringReader("'\\x01'::bytea")).parseSqlExpressionEof();
+ SqlNodeList immutable = SqlNodeList.of(SqlParserPos.ZERO, List.of(bytea));
+ SqlCompilationException e =
+ expectThrows(SqlCompilationException.class, () -> PostgreSqlCastRewriter.rewrite(immutable));
+ assertTrue(e.getMessage().contains("not supported inside an immutable node list"), e.getMessage());
+ }
+
+ private static final String BYTEA_CONSTANT = "'\\x01'::bytea";
+
+ /// The literal a bytea constant is rewritten to must span exactly that constant, so validation errors point at it.
+ /// Recording the enclosing expression's start instead made it claim, e.g., `WHERE b = '\x01'::bytea`.
+ @Test
+ public void testPostgreSqlByteaLiteralKeepsItsSourcePosition()
+ throws Exception {
+ String sql = "SELECT a FROM t WHERE b = " + BYTEA_CONSTANT;
+ SqlSelect select = (SqlSelect) PostgreSqlCastRewriter.rewrite(
+ CalciteSqlParser.newSqlParser(new StringReader(sql)).parseSqlStmtEof());
+ assertSpansByteaConstant(((SqlCall) select.getWhere()).operand(1), sql);
+
+ sql = "SELECT a FROM t WHERE c LIKE 'x' AND b = " + BYTEA_CONSTANT;
+ select = (SqlSelect) PostgreSqlCastRewriter.rewrite(
+ CalciteSqlParser.newSqlParser(new StringReader(sql)).parseSqlStmtEof());
+ assertSpansByteaConstant(((SqlCall) ((SqlCall) select.getWhere()).operand(1)).operand(1), sql);
+
+ sql = "a || " + BYTEA_CONSTANT;
+ SqlCall concat = (SqlCall) PostgreSqlCastRewriter.rewrite(
+ CalciteSqlParser.newSqlParser(new StringReader(sql)).parseSqlExpressionEof());
+ assertSpansByteaConstant(concat.operand(1), sql);
+ }
+
+ private static void assertSpansByteaConstant(SqlNode literal, String sql) {
+ assertTrue(literal instanceof SqlBinaryStringLiteral, sql + " -> " + literal);
+ int start = sql.indexOf(BYTEA_CONSTANT) + 1;
+ SqlParserPos pos = literal.getParserPosition();
+ assertEquals(pos.getLineNum(), 1, sql);
+ assertEquals(pos.getColumnNum(), start, sql);
+ assertEquals(pos.getEndColumnNum(), start + BYTEA_CONSTANT.length() - 1, sql);
+ }
+
+ /// An IN list is a node list reached through the filter rather than the select list.
+ @Test
+ public void testPostgreSqlByteaLiteralInInList() {
+ PinotQuery pinotQuery = CalciteSqlParser.compileToPinotQuery(
+ "SELECT id FROM myTable WHERE bytesColumn IN ('\\x01'::bytea, X'02', CAST('\\x03' AS BYTEA))");
+ Function in = pinotQuery.getFilterExpression().getFunctionCall();
+ assertEquals(in.getOperator(), "IN");
+ assertEquals(in.getOperands().get(0).getIdentifier().getName(), "bytesColumn");
+ assertEquals(in.getOperands().get(1).getLiteral().getBinaryValue(), new byte[]{1});
+ assertEquals(in.getOperands().get(2).getLiteral().getBinaryValue(), new byte[]{2});
+ assertEquals(in.getOperands().get(3).getLiteral().getBinaryValue(), new byte[]{3});
+ }
+
+ /// The expression parse path must surface the rewriter's own message rather than only a generic wrapper.
+ @Test
+ public void testPostgreSqlByteaErrorsSurfaceFromCompileToExpression() {
+ SqlCompilationException e =
+ expectThrows(SqlCompilationException.class, () -> CalciteSqlParser.compileToExpression("bytesColumn::bytea"));
+ assertTrue(e.getMessage().contains(NOT_A_CONSTANT), e.getMessage());
+ assertTrue(e.getMessage().contains("hexToBytes(<expr>)"), e.getMessage());
+
+ e = expectThrows(SqlCompilationException.class, () -> CalciteSqlParser.compileToExpression("'\\x0'::bytea"));
+ assertTrue(e.getMessage().contains(INVALID_CONSTANT), e.getMessage());
+ }
+
+ @Test(dataProvider = "invalidPostgreSqlByteaLiterals")
+ public void testInvalidPostgreSqlByteaHexLiterals(String sql, String expectedMessageFragment) {
+ SqlCompilationException e =
+ expectThrows(SqlCompilationException.class, () -> CalciteSqlParser.compileToPinotQuery(sql));
+ assertTrue(e.getMessage().contains(expectedMessageFragment),
+ "Expected <" + expectedMessageFragment + "> for " + sql + " but got: " + e.getMessage());
+ }
+
+ private static final String INVALID_CONSTANT = "Invalid PostgreSQL BYTEA constant";
+ private static final String NOT_A_CONSTANT = "BYTEA casts are supported only for quoted hex constants";
+
+ @DataProvider
+ public static Object[][] invalidPostgreSqlByteaLiterals() {
+ return new Object[][]{
+ // Malformed hex constants.
+ {"SELECT '\\x0'::bytea", INVALID_CONSTANT},
+ {"SELECT '\\x0g'::bytea", INVALID_CONSTANT},
+ {"SELECT '\\x0 1'::bytea", INVALID_CONSTANT},
+ // PostgreSQL reads this as escape format (four bytes), which is not supported.
+ {"SELECT '0102'::bytea", "only the hex format is supported"},
+ // The \x prefix is case-sensitive, as in PostgreSQL.
+ {"SELECT '\\X0102'::bytea", INVALID_CONSTANT},
+ // Full-width and non-Latin digits are hex digits to Character.digit but not to PostgreSQL.
+ {"SELECT '\\x\uFF21\uFF22'::bytea", INVALID_CONSTANT},
+ {"SELECT '\\x\u0660\u0661'::bytea", INVALID_CONSTANT},
+ // Non-ASCII whitespace is whitespace to Character.isWhitespace but not to PostgreSQL.
+ {"SELECT '\\x01\u205F02'::bytea", INVALID_CONSTANT},
+
+ // BYTEA casts of something that is not a constant.
+ {"SELECT bytesColumn::bytea FROM myTable", NOT_A_CONSTANT},
+ {"SELECT CAST(bytesColumn AS BYTEA) FROM myTable", "use hexToBytes(<expr>)"},
+ {"SELECT id FROM myTable WHERE id = 1 AND bytesColumn::bytea = X'01'", NOT_A_CONSTANT},
+
+ // `::` to a target type other than BYTEA.
+ {"SELECT 1::int", "not for target type 'INTEGER'"},
+ {"SELECT bytesColumn::varchar FROM myTable", "not for target type 'VARCHAR'"},
+
+ // The item accessor binds tighter than `::`, so the target type does not survive as a type spec.
+ {"SELECT bytesColumn::bytea[1] FROM myTable", "Unsupported PostgreSQL :: cast target"}
+ };
+ }
+
@Test
public void testIdentifierLength() {
String tableName = extendIdentifierToMaxLength("exampleTable");
@@ -102,6 +334,7 @@ public class CalciteSqlParserTest {
new Object[]{"string"},
new Object[]{"varchar"},
new Object[]{"bytes"},
+ new Object[]{"bytea"},
new Object[]{"binary"},
new Object[]{"varbinary"},
new Object[]{"variant"},
diff --git a/pinot-integration-tests/src/test/java/org/apache/pinot/integration/tests/custom/BytesMvTypeTest.java b/pinot-integration-tests/src/test/java/org/apache/pinot/integration/tests/custom/BytesMvTypeTest.java
index 25b440b931f..c0e95d86c0d 100644
--- a/pinot-integration-tests/src/test/java/org/apache/pinot/integration/tests/custom/BytesMvTypeTest.java
+++ b/pinot-integration-tests/src/test/java/org/apache/pinot/integration/tests/custom/BytesMvTypeTest.java
@@ -188,17 +188,19 @@ public class BytesMvTypeTest extends CustomDataQueryClusterIntegrationTest {
public void testBytesArrayLiteral(boolean useMultiStageQueryEngine)
throws Exception {
setUseMultiStageQueryEngine(useMultiStageQueryEngine);
- String arrayLiteral = "ARRAY[X'07', X'0708', X'07090A']";
- for (boolean withFrom : new boolean[]{true, false}) {
- String query = withFrom ? String.format("SELECT %s FROM %s WHERE %s = 7 LIMIT 1", arrayLiteral,
- getTableName(), ID_COLUMN) : "SELECT " + arrayLiteral;
- JsonNode result = postQuery(query).get("resultTable");
- assertEquals(result.get("dataSchema").get("columnDataTypes").get(0).asText(), "BYTES_ARRAY");
- JsonNode values = result.get("rows").get(0).get(0);
- assertEquals(values.size(), MV_LENGTH);
- assertEquals(values.get(0).asText(), "07");
- assertEquals(values.get(1).asText(), "0708");
- assertEquals(values.get(2).asText(), "07090a");
+ for (String arrayLiteral : List.of("ARRAY[X'07', X'0708', X'07090A']",
+ "ARRAY['\\x07'::bytea, '\\x0708'::bytea, '\\x07090A'::bytea]")) {
+ for (boolean withFrom : new boolean[]{true, false}) {
+ String query = withFrom ? String.format("SELECT %s FROM %s WHERE %s = 7 LIMIT 1", arrayLiteral,
+ getTableName(), ID_COLUMN) : "SELECT " + arrayLiteral;
+ JsonNode result = postQuery(query).get("resultTable");
+ assertEquals(result.get("dataSchema").get("columnDataTypes").get(0).asText(), "BYTES_ARRAY");
+ JsonNode values = result.get("rows").get(0).get(0);
+ assertEquals(values.size(), MV_LENGTH);
+ assertEquals(values.get(0).asText(), "07");
+ assertEquals(values.get(1).asText(), "0708");
+ assertEquals(values.get(2).asText(), "07090a");
+ }
}
}
@@ -219,15 +221,18 @@ public class BytesMvTypeTest extends CustomDataQueryClusterIntegrationTest {
throws Exception {
setUseMultiStageQueryEngine(useMultiStageQueryEngine);
for (String mvCol : MV_COLUMNS) {
- String positiveQuery = String.format(
- "SELECT COUNT(*) FROM %s WHERE ARRAYS_OVERLAP(%s, ARRAY[X'07'])", getTableName(), mvCol);
- JsonNode rows = postQuery(positiveQuery).get("resultTable").get("rows");
- assertEquals(rows.get(0).get(0).asLong(), 1L);
-
- String negativeQuery = String.format(
- "SELECT COUNT(*) FROM %s WHERE ARRAYS_OVERLAP(%s, ARRAY[X'FF'])", getTableName(), mvCol);
- rows = postQuery(negativeQuery).get("resultTable").get("rows");
- assertEquals(rows.get(0).get(0).asLong(), 0L);
+ for (String[] literals : List.of(new String[]{"X'07'", "X'FF'"},
+ new String[]{"'\\x07'::bytea", "'\\xFF'::bytea"})) {
+ String positiveQuery = String.format(
+ "SELECT COUNT(*) FROM %s WHERE ARRAYS_OVERLAP(%s, ARRAY[%s])", getTableName(), mvCol, literals[0]);
+ JsonNode rows = postQuery(positiveQuery).get("resultTable").get("rows");
+ assertEquals(rows.get(0).get(0).asLong(), 1L);
+
+ String negativeQuery = String.format(
+ "SELECT COUNT(*) FROM %s WHERE ARRAYS_OVERLAP(%s, ARRAY[%s])", getTableName(), mvCol, literals[1]);
+ rows = postQuery(negativeQuery).get("resultTable").get("rows");
+ assertEquals(rows.get(0).get(0).asLong(), 0L);
+ }
}
}
@@ -235,11 +240,14 @@ public class BytesMvTypeTest extends CustomDataQueryClusterIntegrationTest {
public void testArraysOverlapWithLiterals(boolean useMultiStageQueryEngine)
throws Exception {
setUseMultiStageQueryEngine(useMultiStageQueryEngine);
- JsonNode result = postQuery(
- "SELECT ARRAYS_OVERLAP(ARRAY[X'00', X'0102'], ARRAY[X'03', X'0102'])").get("resultTable");
- assertTrue(result.get("rows").get(0).get(0).asBoolean());
+ for (String overlapping : List.of("ARRAYS_OVERLAP(ARRAY[X'00', X'0102'], ARRAY[X'03', X'0102'])",
+ "ARRAYS_OVERLAP(ARRAY[CAST('\\x00' AS BYTEA), CAST('\\x0102' AS BYTEA)], "
+ + "ARRAY['\\x03'::bytea, '\\x0102'::bytea])")) {
+ JsonNode result = postQuery("SELECT " + overlapping).get("resultTable");
+ assertTrue(result.get("rows").get(0).get(0).asBoolean());
+ }
- result = postQuery(
+ JsonNode result = postQuery(
"SELECT ARRAYS_OVERLAP(ARRAY[X'00', X'0102'], ARRAY[X'03', X'04'])").get("resultTable");
assertFalse(result.get("rows").get(0).get(0).asBoolean());
}
diff --git a/pinot-integration-tests/src/test/java/org/apache/pinot/integration/tests/custom/BytesTypeTest.java b/pinot-integration-tests/src/test/java/org/apache/pinot/integration/tests/custom/BytesTypeTest.java
index ff392d5a691..af9b3ca0fb0 100644
--- a/pinot-integration-tests/src/test/java/org/apache/pinot/integration/tests/custom/BytesTypeTest.java
+++ b/pinot-integration-tests/src/test/java/org/apache/pinot/integration/tests/custom/BytesTypeTest.java
@@ -307,4 +307,25 @@ public class BytesTypeTest extends CustomDataQueryClusterIntegrationTest {
Assert.assertEquals(rows.get(i).get(0).asLong(), NUM_TOTAL_DOCS);
}
}
+
+ /// Regression coverage for the PostgreSQL `::` cast binding to the whole expression on its left instead of just
+ /// the literal, which made a bytea constant unusable as the right operand of a comparison.
+ @Test(dataProvider = "useBothQueryEngines")
+ public void testPostgreSqlByteaLiteralAsPredicateOperand(boolean useMultiStageQueryEngine)
+ throws Exception {
+ setUseMultiStageQueryEngine(useMultiStageQueryEngine);
+ for (String byteaLiteral : List.of("'\\x" + FIXED_HEX_STRING_VALUE + "'::bytea",
+ "CAST('\\x" + FIXED_HEX_STRING_VALUE + "' AS BYTEA)", "X'" + FIXED_HEX_STRING_VALUE + "'")) {
+ String query = String.format("SELECT count(*) FROM %s WHERE %s = %s", getTableName(), FIXED_BYTES,
+ byteaLiteral);
+ JsonNode rows = postQuery(query).get("resultTable").get("rows");
+ Assert.assertEquals(rows.get(0).get(0).asLong(), NUM_TOTAL_DOCS, query);
+
+ // Same literal on the right of a compound predicate, where the accumulated expression list is non-empty.
+ query = String.format("SELECT count(*) FROM %s WHERE %s IS NOT NULL AND %s = %s", getTableName(), FIXED_BYTES,
+ FIXED_BYTES, byteaLiteral);
+ rows = postQuery(query).get("resultTable").get("rows");
+ Assert.assertEquals(rows.get(0).get(0).asLong(), NUM_TOTAL_DOCS, query);
+ }
+ }
}
diff --git a/pinot-query-planner/src/test/java/org/apache/pinot/query/QueryCompilationTest.java b/pinot-query-planner/src/test/java/org/apache/pinot/query/QueryCompilationTest.java
index 30f74e5d60c..714d3983944 100644
--- a/pinot-query-planner/src/test/java/org/apache/pinot/query/QueryCompilationTest.java
+++ b/pinot-query-planner/src/test/java/org/apache/pinot/query/QueryCompilationTest.java
@@ -143,6 +143,32 @@ public class QueryCompilationTest extends QueryEnvironmentTestBase {
}
}
+ @Test
+ public void testPostgreSqlByteaLiteralTypeInference() {
+ RelDataType rowType = _queryEnvironment.compile(
+ "SELECT '\\x0102'::bytea, ARRAY['\\x00'::bytea, CAST('\\x0102' AS BYTEA)] FROM a")
+ .getRelRoot().validatedRowType;
+ assertEquals(rowType.getFieldList().get(0).getType().getSqlTypeName(), SqlTypeName.BINARY);
+ RelDataType arrayType = rowType.getFieldList().get(1).getType();
+ assertEquals(arrayType.getSqlTypeName(), SqlTypeName.ARRAY);
+ assertEquals(arrayType.getComponentType().getSqlTypeName(), SqlTypeName.VARBINARY);
+ }
+
+ /// A bytea constant must not change which plan EXPLAIN returns. Rewriting it used to rebuild the statement node, so
+ /// `EXPLAIN IMPLEMENTATION PLAN` lost its physical-plan marker and silently returned the logical plan instead.
+ @Test
+ public void testPostgreSqlByteaLiteralKeepsPhysicalExplain() {
+ long requestId = RANDOM_REQUEST_ID_GEN.nextLong();
+ String expected =
+ _queryEnvironment.explainQuery("EXPLAIN IMPLEMENTATION PLAN FOR SELECT col1, X'01' FROM a", requestId);
+ assertTrue(expected.contains("MAIL_RECEIVE"), expected);
+ for (String bytea : List.of("'\\x01'::bytea", "CAST('\\x01' AS BYTEA)")) {
+ String explain = _queryEnvironment.explainQuery(
+ "EXPLAIN IMPLEMENTATION PLAN FOR SELECT col1, " + bytea + " FROM a", requestId);
+ assertEquals(explain, expected, bytea);
+ }
+ }
+
/// `jsonPath` must resolve to a literal, but the operand type checker deliberately does not demand a literal
/// `SqlNode` in that position: operand checking runs before `PinotEvaluateLiteralRule` folds constant
/// expressions, so an argument such as `CONCAT('$.', 'foo')` folds to a literal and plans and executes
diff --git a/pinot-query-runtime/src/test/resources/queries/BinaryTypes.json b/pinot-query-runtime/src/test/resources/queries/BinaryTypes.json
index 6ce66cea165..33bb20b4e71 100644
--- a/pinot-query-runtime/src/test/resources/queries/BinaryTypes.json
+++ b/pinot-query-runtime/src/test/resources/queries/BinaryTypes.json
@@ -18,21 +18,28 @@
},
{
"psql": "8.4.1",
- "ignored": true,
- "comments": [
- "looks like we don't support constants, this is treated as a normal string",
- "we also require using calcite syntax for byte strings to parse, which is x'deadbeef' instead",
- "of postgres syntax which is '\\xdeadbeaf'"
- ],
- "description": "bytea hex constants",
- "sql": "SELECT x'DEADBEEF', data from {bytea}"
+ "description": "PostgreSQL bytea hex constants",
+ "sql": "SELECT '\\xDEADBEEF'::bytea, data FROM {bytea}",
+ "h2Sql": "SELECT X'DEADBEEF', data FROM {bytea}"
+ },
+ {
+ "psql": "8.4.1",
+ "description": "PostgreSQL bytea hex constant as a comparison operand",
+ "sql": "SELECT data FROM {bytea} WHERE data = '\\xDEADBEEF'::bytea",
+ "h2Sql": "SELECT data FROM {bytea} WHERE data = X'DEADBEEF'"
+ },
+ {
+ "psql": "8.4.1",
+ "description": "PostgreSQL bytea hex constant in a compound predicate",
+ "sql": "SELECT data FROM {bytea} WHERE data <> '\\x00'::bytea AND data = CAST('\\xDEADBEEF' AS BYTEA)",
+ "h2Sql": "SELECT data FROM {bytea} WHERE data <> X'00' AND data = X'DEADBEEF'"
},
{
"psql": "8.4.2",
"ignored": true,
"comments": [
- "in order to specify bytea constants using escape syntax, we need type casting",
- "which we don't support. also, calcite doesn't support the escape syntax for bytes"
+ "PostgreSQL's legacy octal bytea escape syntax remains unsupported",
+ "only PostgreSQL hex-format bytea literals are normalized to Pinot bytes"
],
"description": "bytea escape constants",
"sql": "SELECT CAST('\\046' AS BINARY), data from {bytea}"
diff --git a/pinot-sql-ddl/src/test/java/org/apache/pinot/sql/ddl/compile/DdlCompilerMaterializedViewTest.java b/pinot-sql-ddl/src/test/java/org/apache/pinot/sql/ddl/compile/DdlCompilerMaterializedViewTest.java
index 801041baa94..10b88f3d94c 100644
--- a/pinot-sql-ddl/src/test/java/org/apache/pinot/sql/ddl/compile/DdlCompilerMaterializedViewTest.java
+++ b/pinot-sql-ddl/src/test/java/org/apache/pinot/sql/ddl/compile/DdlCompilerMaterializedViewTest.java
@@ -18,6 +18,7 @@
*/
package org.apache.pinot.sql.ddl.compile;
+import java.util.List;
import java.util.Map;
import org.apache.pinot.spi.config.table.TableConfig;
import org.apache.pinot.spi.config.table.TableType;
@@ -423,6 +424,27 @@ public class DdlCompilerMaterializedViewTest {
"Select Carrier /* hot column */, ts From src");
}
+ /// A PostgreSQL bytea constant in the view body must leave the statement a materialized view (rewriting it once
+ /// rebuilt the statement as a plain call, which was then classified as a query), and the stored text must be
+ /// exactly what the user typed, since it is sliced from the union of every node's parser position.
+ @Test
+ public void definedSqlWithPostgreSqlByteaConstant() {
+ for (String bytea : List.of("'\\x01'::bytea", "CAST('\\x01' AS BYTEA)")) {
+ String query = "SELECT ts, carrier FROM src WHERE payload = " + bytea + " AND carrier <> 'x'";
+ CompiledCreateMaterializedView c = compileMaterializedView(
+ "CREATE MATERIALIZED VIEW mv ("
+ + " ts TIMESTAMP DATETIME FORMAT '1:MILLISECONDS:TIMESTAMP' GRANULARITY '1:DAYS',"
+ + " carrier STRING"
+ + ")"
+ + " REFRESH EVERY 1 DAY"
+ + " PROPERTIES ('timeColumnName' = 'ts', 'bucketTimePeriod' = '1d')"
+ + " AS " + query);
+ assertEquals(c.getOperation(), DdlOperation.CREATE_MATERIALIZED_VIEW, bytea);
+ assertEquals(materializedViewTaskConfig(c.getTableConfig()).get(MaterializedViewTask.DEFINED_SQL_KEY), query,
+ bytea);
+ }
+ }
+
@Test
public void definedSqlAcrossMultipleLinesPreserved() {
String sql =
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org
For additional commands, e-mail: commits-help@pinot.apache.org