Skip to content

Commit e69a9ce

Browse files
morningmanclaude
andauthored
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: #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]>
1 parent 60ca71e commit e69a9ce

3 files changed

Lines changed: 150 additions & 0 deletions

File tree

‎fe/fe-core/src/main/java/org/apache/doris/datasource/jdbc/source/JdbcScanNode.java‎

Lines changed: 44 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -391,6 +391,34 @@ public static String conjunctExprToString(TOdbcTableType tableType, Expr expr, T
391391
return filter;
392392
}
393393

394+
// Databases without a native boolean type (SQL Server bit, Oracle number) reject
395+
// the TRUE/FALSE keyword and report it as an identifier (e.g. SQL Server:
396+
// "Invalid column name 'TRUE'"). Render boolean literals as 1/0 there. See #64464.
397+
if (needRewriteBoolLiteralToInt(tableType) && expr.contains(BoolLiteral.class)
398+
&& expr instanceof BinaryPredicate) {
399+
ArrayList<Expr> children = expr.getChildren();
400+
return handleBooleanLiteral(children.get(0), tbl)
401+
+ " " + ((BinaryPredicate) expr).getOp().toString() + " "
402+
+ handleBooleanLiteral(children.get(1), tbl);
403+
}
404+
405+
if (needRewriteBoolLiteralToInt(tableType) && expr.contains(BoolLiteral.class)
406+
&& expr instanceof InPredicate) {
407+
InPredicate inPredicate = (InPredicate) expr;
408+
String filter = inPredicate.getChild(0).toExternalSql(TableType.JDBC_EXTERNAL_TABLE, tbl);
409+
if (inPredicate.isNotIn()) {
410+
filter += " NOT";
411+
}
412+
filter += " IN (";
413+
List<String> inItemStrings = new ArrayList<>();
414+
for (int i = 1; i < inPredicate.getChildren().size(); i++) {
415+
inItemStrings.add(handleBooleanLiteral(inPredicate.getChild(i), tbl));
416+
}
417+
filter += String.join(", ", inItemStrings);
418+
filter += ")";
419+
return filter;
420+
}
421+
394422
// Only for old planner
395423
if (expr.contains(BoolLiteral.class) && "1".equals(expr.getStringValue()) && expr.getChildren().isEmpty()) {
396424
return "1 = 1";
@@ -458,6 +486,22 @@ private static String handleSQLServerDateFormat(Expr expr, TableIf tbl) {
458486
return expr.toExternalSql(TableType.JDBC_EXTERNAL_TABLE, tbl);
459487
}
460488

489+
private static boolean needRewriteBoolLiteralToInt(TOdbcTableType tableType) {
490+
// These databases have no native boolean type and reject the TRUE/FALSE keyword,
491+
// so boolean literals must be pushed down as 1/0 instead.
492+
return tableType == TOdbcTableType.SQLSERVER
493+
|| tableType == TOdbcTableType.ORACLE
494+
|| tableType == TOdbcTableType.OCEANBASE_ORACLE;
495+
}
496+
497+
private static String handleBooleanLiteral(Expr expr, TableIf tbl) {
498+
if (expr instanceof BoolLiteral) {
499+
// BoolLiteral.getStringValue() returns "1"/"0".
500+
return expr.getStringValue();
501+
}
502+
return expr.toExternalSql(TableType.JDBC_EXTERNAL_TABLE, tbl);
503+
}
504+
461505
private static boolean containsNullLiteral(Expr expr) {
462506
List<NullLiteral> nullExprList = Lists.newArrayList();
463507
expr.collect(NullLiteral.class, nullExprList);

‎fe/fe-core/src/test/java/org/apache/doris/datasource/jdbc/source/JdbcScanNodeTest.java‎

Lines changed: 85 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -626,4 +626,89 @@ String getProperColumnName(TOdbcTableType tableType, String colName) {
626626
Assert.assertTrue(result.contains("\"ID\" = 1"));
627627
Assert.assertTrue(result.contains(" AND "));
628628
}
629+
630+
@Test
631+
public void testBoolLiteralSQLServerBinaryPredicate() {
632+
new Expectations() {{
633+
mockTable.getProperRemoteColumnName((TOdbcTableType) any, anyString);
634+
result = new mockit.Delegate() {
635+
String getProperColumnName(TOdbcTableType tableType, String colName) {
636+
return "\"" + colName + "\"";
637+
}
638+
};
639+
}};
640+
641+
// SQL Server `bit` maps to Doris BOOLEAN. `bit_value = '1'` is folded to a boolean
642+
// literal during analysis and must be pushed down as `= 1`, not `= TRUE`, otherwise
643+
// SQL Server reports "Invalid column name 'TRUE'". See issue #64464.
644+
SlotRef bitSlot = new SlotRef(null, "bit_value");
645+
646+
BinaryPredicate eqTrue = new BinaryPredicate(Operator.EQ, bitSlot, new BoolLiteral(true));
647+
String result = JdbcScanNode.conjunctExprToString(TOdbcTableType.SQLSERVER, eqTrue, mockTable);
648+
Assert.assertEquals("\"bit_value\" = 1", result);
649+
Assert.assertFalse(result.contains("TRUE"));
650+
651+
BinaryPredicate eqFalse = new BinaryPredicate(Operator.EQ, bitSlot, new BoolLiteral(false));
652+
result = JdbcScanNode.conjunctExprToString(TOdbcTableType.SQLSERVER, eqFalse, mockTable);
653+
Assert.assertEquals("\"bit_value\" = 0", result);
654+
Assert.assertFalse(result.contains("FALSE"));
655+
}
656+
657+
@Test
658+
public void testBoolLiteralSQLServerInPredicate() {
659+
new Expectations() {{
660+
mockTable.getProperRemoteColumnName((TOdbcTableType) any, anyString);
661+
result = new mockit.Delegate() {
662+
String getProperColumnName(TOdbcTableType tableType, String colName) {
663+
return "\"" + colName + "\"";
664+
}
665+
};
666+
}};
667+
668+
SlotRef bitSlot = new SlotRef(null, "bit_value");
669+
List<Expr> inList = Arrays.asList(new BoolLiteral(true), new BoolLiteral(false));
670+
InPredicate inPred = new InPredicate(bitSlot, inList, false);
671+
672+
String result = JdbcScanNode.conjunctExprToString(TOdbcTableType.SQLSERVER, inPred, mockTable);
673+
Assert.assertEquals("\"bit_value\" IN (1, 0)", result);
674+
Assert.assertFalse(result.contains("TRUE"));
675+
Assert.assertFalse(result.contains("FALSE"));
676+
}
677+
678+
@Test
679+
public void testBoolLiteralOracleBinaryPredicate() {
680+
new Expectations() {{
681+
mockTable.getProperRemoteColumnName((TOdbcTableType) any, anyString);
682+
result = new mockit.Delegate() {
683+
String getProperColumnName(TOdbcTableType tableType, String colName) {
684+
return "\"" + colName + "\"";
685+
}
686+
};
687+
}};
688+
689+
// Oracle has no boolean type either; render as 1/0.
690+
SlotRef flagSlot = new SlotRef(null, "FLAG");
691+
BinaryPredicate eqTrue = new BinaryPredicate(Operator.EQ, flagSlot, new BoolLiteral(true));
692+
String result = JdbcScanNode.conjunctExprToString(TOdbcTableType.ORACLE, eqTrue, mockTable);
693+
Assert.assertEquals("\"FLAG\" = 1", result);
694+
}
695+
696+
@Test
697+
public void testBoolLiteralMysqlKeepsKeyword() {
698+
new Expectations() {{
699+
mockTable.getProperRemoteColumnName((TOdbcTableType) any, anyString);
700+
result = new mockit.Delegate() {
701+
String getProperColumnName(TOdbcTableType tableType, String colName) {
702+
return "\"" + colName + "\"";
703+
}
704+
};
705+
}};
706+
707+
// MySQL accepts TRUE/FALSE and PostgreSQL requires it (strict boolean type),
708+
// so the keyword rendering must be preserved for non-integer-boolean dialects.
709+
SlotRef flagSlot = new SlotRef(null, "flag");
710+
BinaryPredicate eqTrue = new BinaryPredicate(Operator.EQ, flagSlot, new BoolLiteral(true));
711+
String result = JdbcScanNode.conjunctExprToString(TOdbcTableType.MYSQL, eqTrue, mockTable);
712+
Assert.assertEquals("(\"flag\" = TRUE)", result);
713+
}
629714
}

‎regression-test/suites/external_table_p0/jdbc/test_sqlserver_jdbc_catalog.groovy‎

Lines changed: 21 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -139,6 +139,27 @@ suite("test_sqlserver_jdbc_catalog", "p0,external,sqlserver,external_docker,exte
139139

140140
order_qt_desc """ desc test_binary; """
141141
order_qt_query """ select * from test_binary order by id; """
142+
143+
// Regression test for https://github.com/apache/doris/issues/64464
144+
// SQL Server `bit` maps to Doris BOOLEAN, so a predicate like `bit_value = '1'`
145+
// is folded to a boolean literal during analysis. When the filter is pushed down
146+
// it must be rendered as an integer (`= 1` / `= 0`) for SQL Server, never the
147+
// `TRUE`/`FALSE` keyword: SQL Server has no boolean literal and would otherwise
148+
// report "Invalid column name 'TRUE'".
149+
explain {
150+
sql("select * from test_binary where bit_value = '1'")
151+
contains "[bit_value] = 1"
152+
}
153+
explain {
154+
sql("select * from test_binary where bit_value = '0'")
155+
contains "[bit_value] = 0"
156+
}
157+
// Execute the predicates end-to-end; on the buggy path these throw
158+
// "Invalid column name 'TRUE'" against SQL Server.
159+
sql """ select * from test_binary where bit_value = '1' order by id; """
160+
sql """ select * from test_binary where bit_value = '0' order by id; """
161+
sql """ select * from test_binary where bit_value in ('1', '0') order by id; """
162+
142163
sql """ insert into test_binary values (4, 4, X"ABAB", X"AB") """
143164
order_qt_query_after_insert """ select * from test_binary order by id; """
144165
sql """ drop catalog if exists ${catalog_name} """

0 commit comments

Comments
 (0)