This is an automated email from the ASF dual-hosted git repository.
yiguolei pushed a commit to branch branch-4.1
in repository https://gitbox.apache.org/repos/asf/doris.git
The following commit(s) were added to refs/heads/branch-4.1 by this push:
new e69a9ce0717 branch-4.1: [fix](jdbc) Push SQL Server/Oracle boolean
predicates as 1/0 instead of TRUE/FALSE (#64757)
e69a9ce0717 is described below
commit e69a9ce0717867d0b764c5e3dd3876b6744b9236
Author: Mingyu Chen (Rayner) <[email protected]>
AuthorDate: Wed Jun 24 09:57:56 2026 +0800
branch-4.1: [fix](jdbc) Push SQL Server/Oracle boolean predicates as 1/0
instead of TRUE/FALSE (#64757)
### What problem does this PR solve?
Issue Number: #64464
Related PR: 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).
On master this is already handled by the refactored connector SPI
(`JdbcQueryBuilder.formatBooleanLiteral()`); this PR is the equivalent
fix for the `branch-4.1` `JdbcScanNode` path.
### Release note
Fix SQL Server / Oracle JDBC catalog query failure ("Invalid column name
'TRUE'") when filtering a boolean/`bit` column.
### Check List (For Author)
- Test
- [x] Regression test
- [x] Unit Test
- [ ] Manual test (add detailed scripts or steps below)
- [ ] No need to test or manual test.
- Behavior changed:
- [x] No.
- [ ] Yes.
- Does this need documentation?
- [x] No.
- [ ] Yes.
🤖 Generated with [Claude Code](https://claude.com/claude-code)
Co-authored-by: Claude Opus 4.8 (1M context) <[email protected]>
---
.../doris/datasource/jdbc/source/JdbcScanNode.java | 44 +++++++++++
.../datasource/jdbc/source/JdbcScanNodeTest.java | 85 ++++++++++++++++++++++
.../jdbc/test_sqlserver_jdbc_catalog.groovy | 21 ++++++
3 files changed, 150 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/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..684c0e0b27d 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,27 @@ 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 as an integer (`= 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"
+ }
+ // Execute the predicates end-to-end; on the buggy path these throw
+ // "Invalid column name 'TRUE'" against SQL Server.
+ sql """ select * from test_binary where bit_value = '1' order by id;
"""
+ sql """ select * from test_binary where bit_value = '0' order by id;
"""
+ sql """ select * 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]
