This is an automated email from the ASF dual-hosted git repository.
morningman pushed a commit to branch branch-4.0
in repository https://gitbox.apache.org/repos/asf/doris.git
The following commit(s) were added to refs/heads/branch-4.0 by this push:
new 0c865d971c3 [fix](jdbc) Push SQL Server/Oracle boolean predicates as
1/0 instead of TRUE/FALSE (#64760)
0c865d971c3 is described below
commit 0c865d971c37a847e4351c61558cd1ab7444abc1
Author: Mingyu Chen (Rayner) <[email protected]>
AuthorDate: Wed Jun 24 13:48:47 2026 +0800
[fix](jdbc) Push SQL Server/Oracle boolean predicates as 1/0 instead of
TRUE/FALSE (#64760)
### What problem does this PR solve?
Issue Number: #64464
Related PR: apache/doris#64757 (branch-4.1), apache/doris#64756 (master
regression test)
Problem Summary:
When querying a SQL Server JDBC catalog, a predicate on a `bit` column
such as `WHERE bit_value = '1'` is folded to a boolean literal during
analysis. On this branch the JDBC pushdown path
(`JdbcScanNode.conjunctExprToString()` -> `Expr.toExternalSql()`)
renders `BoolLiteral` as the dialect-agnostic `TRUE`/`FALSE` keyword, so
the pushed-down SQL becomes:
```sql
... WHERE (([bit_value] = TRUE))
```
SQL Server has no boolean literal and treats `TRUE` as an identifier:
```
SQLServerException: Invalid column name 'TRUE'.
```
(2.1.10 did not have this issue; it pushed `= 1`.)
This PR renders boolean literals as `1`/`0` for databases without a
native boolean type (`SQLSERVER`, `ORACLE`, `OCEANBASE_ORACLE`),
mirroring the existing dialect-specific date-literal handling in the
same method. `MySQL` / `PostgreSQL` / `Trino` keep `TRUE`/`FALSE`
(PostgreSQL requires the keyword for its strict boolean type).
This is the `branch-4.0` backport of the same fix (`branch-4.1`:
apache/doris#64757). On master the JDBC pushdown was refactored to the
connector SPI and already renders booleans per dialect via
`JdbcQueryBuilder.formatBooleanLiteral()`.
### Release note
Fix SQL Server / Oracle JDBC catalog query failure ("Invalid column name
'TRUE'") when filtering a boolean/`bit` column.
---
.../doris/datasource/jdbc/source/JdbcScanNode.java | 44 +++++++++++
.../datasource/jdbc/source/JdbcScanNodeTest.java | 85 ++++++++++++++++++++++
.../jdbc/test_sqlserver_jdbc_catalog.out | 12 +++
.../jdbc/test_sqlserver_jdbc_catalog.groovy | 28 +++++++
4 files changed, 169 insertions(+)
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/datasource/jdbc/source/JdbcScanNode.java
b/fe/fe-core/src/main/java/org/apache/doris/datasource/jdbc/source/JdbcScanNode.java
index 7e5a636455d..efd9e2e6eba 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/datasource/jdbc/source/JdbcScanNode.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/datasource/jdbc/source/JdbcScanNode.java
@@ -391,6 +391,34 @@ public class JdbcScanNode extends ExternalScanNode {
return filter;
}
+ // Databases without a native boolean type (SQL Server bit, Oracle
number) reject
+ // the TRUE/FALSE keyword and report it as an identifier (e.g. SQL
Server:
+ // "Invalid column name 'TRUE'"). Render boolean literals as 1/0
there. See #64464.
+ if (needRewriteBoolLiteralToInt(tableType) &&
expr.contains(BoolLiteral.class)
+ && expr instanceof BinaryPredicate) {
+ ArrayList<Expr> children = expr.getChildren();
+ return handleBooleanLiteral(children.get(0), tbl)
+ + " " + ((BinaryPredicate) expr).getOp().toString() + " "
+ + handleBooleanLiteral(children.get(1), tbl);
+ }
+
+ if (needRewriteBoolLiteralToInt(tableType) &&
expr.contains(BoolLiteral.class)
+ && expr instanceof InPredicate) {
+ InPredicate inPredicate = (InPredicate) expr;
+ String filter =
inPredicate.getChild(0).toExternalSql(TableType.JDBC_EXTERNAL_TABLE, tbl);
+ if (inPredicate.isNotIn()) {
+ filter += " NOT";
+ }
+ filter += " IN (";
+ List<String> inItemStrings = new ArrayList<>();
+ for (int i = 1; i < inPredicate.getChildren().size(); i++) {
+
inItemStrings.add(handleBooleanLiteral(inPredicate.getChild(i), tbl));
+ }
+ filter += String.join(", ", inItemStrings);
+ filter += ")";
+ return filter;
+ }
+
// Only for old planner
if (expr.contains(BoolLiteral.class) &&
"1".equals(expr.getStringValue()) && expr.getChildren().isEmpty()) {
return "1 = 1";
@@ -458,6 +486,22 @@ public class JdbcScanNode extends ExternalScanNode {
return expr.toExternalSql(TableType.JDBC_EXTERNAL_TABLE, tbl);
}
+ private static boolean needRewriteBoolLiteralToInt(TOdbcTableType
tableType) {
+ // These databases have no native boolean type and reject the
TRUE/FALSE keyword,
+ // so boolean literals must be pushed down as 1/0 instead.
+ return tableType == TOdbcTableType.SQLSERVER
+ || tableType == TOdbcTableType.ORACLE
+ || tableType == TOdbcTableType.OCEANBASE_ORACLE;
+ }
+
+ private static String handleBooleanLiteral(Expr expr, TableIf tbl) {
+ if (expr instanceof BoolLiteral) {
+ // BoolLiteral.getStringValue() returns "1"/"0".
+ return expr.getStringValue();
+ }
+ return expr.toExternalSql(TableType.JDBC_EXTERNAL_TABLE, tbl);
+ }
+
private static boolean containsNullLiteral(Expr expr) {
List<NullLiteral> nullExprList = Lists.newArrayList();
expr.collect(NullLiteral.class, nullExprList);
diff --git
a/fe/fe-core/src/test/java/org/apache/doris/datasource/jdbc/source/JdbcScanNodeTest.java
b/fe/fe-core/src/test/java/org/apache/doris/datasource/jdbc/source/JdbcScanNodeTest.java
index 09cbb2786d7..6d12dbfa9cf 100644
---
a/fe/fe-core/src/test/java/org/apache/doris/datasource/jdbc/source/JdbcScanNodeTest.java
+++
b/fe/fe-core/src/test/java/org/apache/doris/datasource/jdbc/source/JdbcScanNodeTest.java
@@ -626,4 +626,89 @@ public class JdbcScanNodeTest {
Assert.assertTrue(result.contains("\"ID\" = 1"));
Assert.assertTrue(result.contains(" AND "));
}
+
+ @Test
+ public void testBoolLiteralSQLServerBinaryPredicate() {
+ new Expectations() {{
+ mockTable.getProperRemoteColumnName((TOdbcTableType) any,
anyString);
+ result = new mockit.Delegate() {
+ String getProperColumnName(TOdbcTableType tableType,
String colName) {
+ return "\"" + colName + "\"";
+ }
+ };
+ }};
+
+ // SQL Server `bit` maps to Doris BOOLEAN. `bit_value = '1'` is folded
to a boolean
+ // literal during analysis and must be pushed down as `= 1`, not `=
TRUE`, otherwise
+ // SQL Server reports "Invalid column name 'TRUE'". See issue #64464.
+ SlotRef bitSlot = new SlotRef(null, "bit_value");
+
+ BinaryPredicate eqTrue = new BinaryPredicate(Operator.EQ, bitSlot, new
BoolLiteral(true));
+ String result =
JdbcScanNode.conjunctExprToString(TOdbcTableType.SQLSERVER, eqTrue, mockTable);
+ Assert.assertEquals("\"bit_value\" = 1", result);
+ Assert.assertFalse(result.contains("TRUE"));
+
+ BinaryPredicate eqFalse = new BinaryPredicate(Operator.EQ, bitSlot,
new BoolLiteral(false));
+ result = JdbcScanNode.conjunctExprToString(TOdbcTableType.SQLSERVER,
eqFalse, mockTable);
+ Assert.assertEquals("\"bit_value\" = 0", result);
+ Assert.assertFalse(result.contains("FALSE"));
+ }
+
+ @Test
+ public void testBoolLiteralSQLServerInPredicate() {
+ new Expectations() {{
+ mockTable.getProperRemoteColumnName((TOdbcTableType) any,
anyString);
+ result = new mockit.Delegate() {
+ String getProperColumnName(TOdbcTableType tableType,
String colName) {
+ return "\"" + colName + "\"";
+ }
+ };
+ }};
+
+ SlotRef bitSlot = new SlotRef(null, "bit_value");
+ List<Expr> inList = Arrays.asList(new BoolLiteral(true), new
BoolLiteral(false));
+ InPredicate inPred = new InPredicate(bitSlot, inList, false);
+
+ String result =
JdbcScanNode.conjunctExprToString(TOdbcTableType.SQLSERVER, inPred, mockTable);
+ Assert.assertEquals("\"bit_value\" IN (1, 0)", result);
+ Assert.assertFalse(result.contains("TRUE"));
+ Assert.assertFalse(result.contains("FALSE"));
+ }
+
+ @Test
+ public void testBoolLiteralOracleBinaryPredicate() {
+ new Expectations() {{
+ mockTable.getProperRemoteColumnName((TOdbcTableType) any,
anyString);
+ result = new mockit.Delegate() {
+ String getProperColumnName(TOdbcTableType tableType,
String colName) {
+ return "\"" + colName + "\"";
+ }
+ };
+ }};
+
+ // Oracle has no boolean type either; render as 1/0.
+ SlotRef flagSlot = new SlotRef(null, "FLAG");
+ BinaryPredicate eqTrue = new BinaryPredicate(Operator.EQ, flagSlot,
new BoolLiteral(true));
+ String result =
JdbcScanNode.conjunctExprToString(TOdbcTableType.ORACLE, eqTrue, mockTable);
+ Assert.assertEquals("\"FLAG\" = 1", result);
+ }
+
+ @Test
+ public void testBoolLiteralMysqlKeepsKeyword() {
+ new Expectations() {{
+ mockTable.getProperRemoteColumnName((TOdbcTableType) any,
anyString);
+ result = new mockit.Delegate() {
+ String getProperColumnName(TOdbcTableType tableType,
String colName) {
+ return "\"" + colName + "\"";
+ }
+ };
+ }};
+
+ // MySQL accepts TRUE/FALSE and PostgreSQL requires it (strict boolean
type),
+ // so the keyword rendering must be preserved for non-integer-boolean
dialects.
+ SlotRef flagSlot = new SlotRef(null, "flag");
+ BinaryPredicate eqTrue = new BinaryPredicate(Operator.EQ, flagSlot,
new BoolLiteral(true));
+ String result =
JdbcScanNode.conjunctExprToString(TOdbcTableType.MYSQL, eqTrue, mockTable);
+ Assert.assertEquals("(\"flag\" = TRUE)", result);
+ }
}
diff --git
a/regression-test/data/external_table_p0/jdbc/test_sqlserver_jdbc_catalog.out
b/regression-test/data/external_table_p0/jdbc/test_sqlserver_jdbc_catalog.out
index 368a7b99390..3069fb1904f 100644
---
a/regression-test/data/external_table_p0/jdbc/test_sqlserver_jdbc_catalog.out
+++
b/regression-test/data/external_table_p0/jdbc/test_sqlserver_jdbc_catalog.out
@@ -184,6 +184,18 @@ varbinary_value varbinary(20) Yes true \N
2 true 0x4D616B6520446F72697320477265617421000000
0x4D616B6520446F72697320477265617421
3 true 0x4D616B6520446F72697320477265617421000000
0x4D616B6520446F72697320477265617421
+-- !bit_eq_false --
+1 false
+
+-- !bit_eq_true --
+2 true
+3 true
+
+-- !bit_in --
+1 false
+2 true
+3 true
+
-- !query_after_insert --
1 false 0x4D616B6520446F72697320477265617421000000
0x4D616B6520446F72697320477265617421
2 true 0x4D616B6520446F72697320477265617421000000
0x4D616B6520446F72697320477265617421
diff --git
a/regression-test/suites/external_table_p0/jdbc/test_sqlserver_jdbc_catalog.groovy
b/regression-test/suites/external_table_p0/jdbc/test_sqlserver_jdbc_catalog.groovy
index 6c167be4c3f..05545c18b1b 100644
---
a/regression-test/suites/external_table_p0/jdbc/test_sqlserver_jdbc_catalog.groovy
+++
b/regression-test/suites/external_table_p0/jdbc/test_sqlserver_jdbc_catalog.groovy
@@ -139,6 +139,34 @@ suite("test_sqlserver_jdbc_catalog",
"p0,external,sqlserver,external_docker,exte
order_qt_desc """ desc test_binary; """
order_qt_query """ select * from test_binary order by id; """
+
+ // Regression test for https://github.com/apache/doris/issues/64464
+ // SQL Server `bit` maps to Doris BOOLEAN, so a predicate like
`bit_value = '1'`
+ // is folded to a boolean literal during analysis. When the filter is
pushed down it
+ // must be rendered with integer literals (`= 1` / `= 0` / `IN (1,
0)`) for SQL Server,
+ // never the `TRUE`/`FALSE` keyword: SQL Server has no boolean literal
and would
+ // otherwise report "Invalid column name 'TRUE'".
+ explain {
+ sql("select * from test_binary where bit_value = '1'")
+ contains "[bit_value] = 1"
+ }
+ explain {
+ sql("select * from test_binary where bit_value = '0'")
+ contains "[bit_value] = 0"
+ }
+ explain {
+ sql("select * from test_binary where bit_value in ('1', '0')")
+ contains "[bit_value] IN (1, 0)"
+ }
+ // Selective execution checks. At this point test_binary holds id=1
(bit 0),
+ // id=2 and id=3 (bit 1), so `= '0'` returns only id=1 and `= '1'`
only id=2,3:
+ // these fail if the pushed-down filter is dropped or applied as a
no-op. The IN
+ // predicate matches every row (a bit is always 0 or 1), so its 1/0
rendering is
+ // asserted by the explain above rather than by row selectivity.
+ order_qt_bit_eq_false """ select id, bit_value from test_binary where
bit_value = '0' order by id; """
+ order_qt_bit_eq_true """ select id, bit_value from test_binary where
bit_value = '1' order by id; """
+ order_qt_bit_in """ select id, bit_value from test_binary where
bit_value in ('1', '0') order by id; """
+
sql """ insert into test_binary values (4, 4, X"ABAB", X"AB") """
order_qt_query_after_insert """ select * from test_binary order by id;
"""
sql """ drop catalog if exists ${catalog_name} """
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
