Skip to content

Commit 2c89bdf

Browse files
morningmanclaude
andcommitted
[fix](jdbc) Push SQL Server/Oracle boolean predicates as 1/0 instead of TRUE/FALSE
SQL Server `bit` (and Oracle, which has no native boolean type) maps to Doris BOOLEAN. A predicate such as `bit_value = '1'` is folded to a boolean literal during analysis, and JdbcScanNode.conjunctExprToString() pushes it down via Expr.toExternalSql(), which renders BoolLiteral as the dialect-agnostic TRUE/FALSE keyword. SQL Server has no boolean literal and reports it as an identifier: SQLServerException: Invalid column name 'TRUE'. Render 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 and Trino keep TRUE/FALSE (PostgreSQL requires the keyword for its strict boolean type). This matches the behavior already present on master, where the JDBC pushdown was refactored to the connector SPI and JdbcQueryBuilder.formatBooleanLiteral() renders booleans per dialect. Related apache#64464 Co-Authored-By: Claude Opus 4.8 (1M context) <[email protected]>
1 parent 2c6ceab commit 2c89bdf

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)