Skip to content

Commit 0c865d9

Browse files
authored
[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: #64757 (branch-4.1), #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`: #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.
1 parent 7985a72 commit 0c865d9

4 files changed

Lines changed: 169 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/data/external_table_p0/jdbc/test_sqlserver_jdbc_catalog.out‎

Lines changed: 12 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -184,6 +184,18 @@ varbinary_value varbinary(20) Yes true \N
184184
2 true 0x4D616B6520446F72697320477265617421000000 0x4D616B6520446F72697320477265617421
185185
3 true 0x4D616B6520446F72697320477265617421000000 0x4D616B6520446F72697320477265617421
186186

187+
-- !bit_eq_false --
188+
1 false
189+
190+
-- !bit_eq_true --
191+
2 true
192+
3 true
193+
194+
-- !bit_in --
195+
1 false
196+
2 true
197+
3 true
198+
187199
-- !query_after_insert --
188200
1 false 0x4D616B6520446F72697320477265617421000000 0x4D616B6520446F72697320477265617421
189201
2 true 0x4D616B6520446F72697320477265617421000000 0x4D616B6520446F72697320477265617421

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

Lines changed: 28 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -139,6 +139,34 @@ 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 it
146+
// must be rendered with integer literals (`= 1` / `= 0` / `IN (1, 0)`) for SQL Server,
147+
// never the `TRUE`/`FALSE` keyword: SQL Server has no boolean literal and would
148+
// otherwise 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+
explain {
158+
sql("select * from test_binary where bit_value in ('1', '0')")
159+
contains "[bit_value] IN (1, 0)"
160+
}
161+
// Selective execution checks. At this point test_binary holds id=1 (bit 0),
162+
// id=2 and id=3 (bit 1), so `= '0'` returns only id=1 and `= '1'` only id=2,3:
163+
// these fail if the pushed-down filter is dropped or applied as a no-op. The IN
164+
// predicate matches every row (a bit is always 0 or 1), so its 1/0 rendering is
165+
// asserted by the explain above rather than by row selectivity.
166+
order_qt_bit_eq_false """ select id, bit_value from test_binary where bit_value = '0' order by id; """
167+
order_qt_bit_eq_true """ select id, bit_value from test_binary where bit_value = '1' order by id; """
168+
order_qt_bit_in """ select id, bit_value from test_binary where bit_value in ('1', '0') order by id; """
169+
142170
sql """ insert into test_binary values (4, 4, X"ABAB", X"AB") """
143171
order_qt_query_after_insert """ select * from test_binary order by id; """
144172
sql """ drop catalog if exists ${catalog_name} """

0 commit comments

Comments
 (0)