JdbcScanNode.java
// Licensed to the Apache Software Foundation (ASF) under one
// or more contributor license agreements. See the NOTICE file
// distributed with this work for additional information
// regarding copyright ownership. The ASF licenses this file
// to you under the Apache License, Version 2.0 (the
// "License"); you may not use this file except in compliance
// with the License. You may obtain a copy of the License at
//
// http://www.apache.org/licenses/LICENSE-2.0
//
// Unless required by applicable law or agreed to in writing,
// software distributed under the License is distributed on an
// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
// KIND, either express or implied. See the License for the
// specific language governing permissions and limitations
// under the License.
package org.apache.doris.datasource.jdbc.source;
import org.apache.doris.analysis.BinaryPredicate;
import org.apache.doris.analysis.BoolLiteral;
import org.apache.doris.analysis.CastExpr;
import org.apache.doris.analysis.CompoundPredicate;
import org.apache.doris.analysis.CompoundPredicate.Operator;
import org.apache.doris.analysis.DateLiteral;
import org.apache.doris.analysis.Expr;
import org.apache.doris.analysis.ExprSubstitutionMap;
import org.apache.doris.analysis.FunctionCallExpr;
import org.apache.doris.analysis.InPredicate;
import org.apache.doris.analysis.NullLiteral;
import org.apache.doris.analysis.SlotDescriptor;
import org.apache.doris.analysis.SlotRef;
import org.apache.doris.analysis.TupleDescriptor;
import org.apache.doris.catalog.ArrayType;
import org.apache.doris.catalog.Column;
import org.apache.doris.catalog.JdbcTable;
import org.apache.doris.catalog.TableIf;
import org.apache.doris.catalog.TableIf.TableType;
import org.apache.doris.common.UserException;
import org.apache.doris.datasource.ExternalFunctionRules;
import org.apache.doris.datasource.ExternalScanNode;
import org.apache.doris.datasource.jdbc.JdbcExternalTable;
import org.apache.doris.datasource.jdbc.client.JdbcClient;
import org.apache.doris.datasource.jdbc.client.JdbcClientConfig;
import org.apache.doris.planner.PlanNodeId;
import org.apache.doris.planner.ScanContext;
import org.apache.doris.qe.ConnectContext;
import org.apache.doris.statistics.StatisticalType;
import org.apache.doris.thrift.TExplainLevel;
import org.apache.doris.thrift.TJdbcScanNode;
import org.apache.doris.thrift.TOdbcTableType;
import org.apache.doris.thrift.TPlanNode;
import org.apache.doris.thrift.TPlanNodeType;
import com.google.common.base.Joiner;
import com.google.common.base.MoreObjects;
import com.google.common.collect.Lists;
import org.jetbrains.annotations.NotNull;
import java.util.ArrayList;
import java.util.List;
import java.util.stream.Collectors;
public class JdbcScanNode extends ExternalScanNode {
private final List<String> columns = new ArrayList<String>();
private final List<String> filters = new ArrayList<String>();
private final List<Expr> pushedDownConjuncts = new ArrayList<>();
private String tableName;
private TOdbcTableType jdbcType;
private String graphQueryString = "";
private boolean isTableValuedFunction = false;
private String query = "";
private boolean projectsTimestamps;
private java.util.Set<String> mysqlSqlMode;
private JdbcTable tbl;
private long catalogId;
public JdbcScanNode(PlanNodeId id, TupleDescriptor desc, boolean isJdbcExternalTable, ScanContext scanContext) {
super(id, desc, "JdbcScanNode", StatisticalType.JDBC_SCAN_NODE, scanContext, false);
if (isJdbcExternalTable) {
JdbcExternalTable jdbcExternalTable = (JdbcExternalTable) (desc.getTable());
tbl = jdbcExternalTable.getJdbcTable();
} else {
tbl = (JdbcTable) (desc.getTable());
}
jdbcType = tbl.getJdbcTableType();
tableName = tbl.getProperRemoteFullTableName(jdbcType);
}
public JdbcScanNode(PlanNodeId id, TupleDescriptor desc, boolean isTableValuedFunction, String query,
ScanContext scanContext) {
super(id, desc, "JdbcScanNode", StatisticalType.JDBC_SCAN_NODE, scanContext, false);
this.isTableValuedFunction = isTableValuedFunction;
this.query = query;
tbl = (JdbcTable) desc.getTable();
jdbcType = tbl.getJdbcTableType();
tableName = tbl.getExternalTableName();
catalogId = tbl.getCatalogId();
if (isTableValuedFunction && (jdbcType == TOdbcTableType.MYSQL || jdbcType == TOdbcTableType.OCEANBASE)
&& desc.getSlots().stream().anyMatch(slot -> slot.getColumn().getType().isTimeStampTz())) {
// Bind the mode to this scan's connection snapshot, not a catalog that ALTER/DROP can replace.
// A private single-connection client avoids borrowing a pool that catalog reset can close.
mysqlSqlMode = JdbcClient.readSessionSqlMode(new JdbcClientConfig()
.setCatalog(String.valueOf(catalogId))
.setJdbcUrl(tbl.getJdbcUrl()).setUser(tbl.getJdbcUser()).setPassword(tbl.getJdbcPasswd())
.setDriverClass(tbl.getDriverClass()).setDriverUrl(tbl.getDriverUrl())
.setConnectionPoolMinSize(0).setConnectionPoolMaxSize(1)
.setConnectionPoolMaxWaitTime(tbl.getConnectionPoolMaxWaitTime())
.setConnectionPoolMaxLifeTime(tbl.getConnectionPoolMaxLifeTime()));
}
}
/**
* Used for Nereids. Should NOT use this function in anywhere else.
*/
@Override
public void init() throws UserException {
super.init();
numNodes = numNodes <= 0 ? 1 : numNodes;
cardinality = -1;
}
private void createJdbcFilters() {
if (conjuncts.isEmpty()) {
return;
}
List<SlotRef> slotRefs = Lists.newArrayList();
Expr.collectList(conjuncts, SlotRef.class, slotRefs);
ExprSubstitutionMap sMap = new ExprSubstitutionMap();
for (SlotRef slotRef : slotRefs) {
SlotRef slotRef1 = (SlotRef) slotRef.clone();
slotRef1.setTblName(null);
slotRef1.setLabel(JdbcTable.properNameWithRemoteName(jdbcType, slotRef1.getColumnName()));
sMap.put(slotRef, slotRef1);
}
ArrayList<Expr> conjunctsList = Expr.cloneList(conjuncts, sMap);
List<String> errors = Lists.newArrayList();
List<Expr> pushDownConjuncts = collectConjunctsToPushDown(conjunctsList, errors,
tbl.getExternalFunctionRules());
for (Expr individualConjunct : pushDownConjuncts) {
String filter = conjunctExprToString(jdbcType, individualConjunct, tbl);
filters.add(filter);
pushedDownConjuncts.add(individualConjunct);
}
}
private List<Expr> collectConjunctsToPushDown(List<Expr> conjunctsList, List<String> errors,
ExternalFunctionRules functionRules) {
List<Expr> pushDownConjuncts = new ArrayList<>();
for (Expr p : conjunctsList) {
if (shouldPushDownConjunct(jdbcType, p)) {
List<Expr> individualConjuncts = p.getConjuncts();
for (Expr individualConjunct : individualConjuncts) {
Expr newp = JdbcFunctionPushDownRule.processFunctions(jdbcType, individualConjunct, errors,
functionRules);
if (!errors.isEmpty()) {
errors.clear();
continue;
}
pushDownConjuncts.add(newp);
}
}
}
return pushDownConjuncts;
}
private void createJdbcColumns() {
columns.clear();
projectsTimestamps = false;
for (SlotDescriptor slot : desc.getSlots()) {
Column col = slot.getColumn();
String remoteName = tbl.getProperRemoteColumnName(jdbcType, col.getName());
org.apache.doris.catalog.Type leaf = col.getType();
while (leaf.isArrayType()) {
leaf = ((ArrayType) leaf).getItemType();
}
if ((jdbcType == TOdbcTableType.MYSQL || jdbcType == TOdbcTableType.OCEANBASE)
&& col.getType().isTimeStampTz()) {
// Text projection prevents Connector/J's cached server zone from shifting UTC session fields.
columns.add("CAST(" + remoteName + " AS CHAR) AS " + remoteName);
projectsTimestamps = true;
} else if (jdbcType == TOdbcTableType.CLICKHOUSE && leaf.isTimeStampTz()) {
// JDBC v1 loses the offset at DST overlaps, including for scalar ZonedDateTime.
// Send epoch microseconds for both scalar and nested instants before JDBC decoding.
columns.add(clickHouseTimestampProjection(remoteName, col.getType(), 0) + " AS " + remoteName);
projectsTimestamps = true;
} else if ((jdbcType == TOdbcTableType.TRINO || jdbcType == TOdbcTableType.PRESTO)
&& leaf.isTimeStampTz()) {
// Trino and Presto JDBC lose the offset when decoding a named-zone timestamp in a DST fold.
// Convert instants to UTC on the server before scalar or array values reach the driver.
columns.add(trinoTimestampProjection(remoteName, col.getType(), 0) + " AS " + remoteName);
projectsTimestamps = true;
} else {
columns.add(remoteName);
}
}
if (columns.isEmpty()) {
columns.add("*");
}
}
private boolean shouldPushDownLimit() {
return limit != -1 && conjuncts.size() == pushedDownConjuncts.size();
}
private static String clickHouseTimestampProjection(String value, org.apache.doris.catalog.Type type,
int depth) {
if (type.isArrayType()) {
String element = "t" + depth;
return "arrayMap(" + element + " -> "
+ clickHouseTimestampProjection(element, ((ArrayType) type).getItemType(), depth + 1)
+ ", " + value + ")";
}
return "toUnixTimestamp64Micro(toDateTime64(" + value + ", 6))";
}
private static String trinoTimestampProjection(String value, org.apache.doris.catalog.Type type, int depth) {
if (type.isArrayType()) {
String element = "t" + depth;
return "transform(" + value + ", " + element + " -> "
+ trinoTimestampProjection(element, ((ArrayType) type).getItemType(), depth + 1) + ")";
}
return "at_timezone(" + value + ", 'UTC')";
}
private String getTvfQuery() {
if (!projectsTimestamps) {
return query;
}
boolean mysql = jdbcType == TOdbcTableType.MYSQL || jdbcType == TOdbcTableType.OCEANBASE;
String source = stripTerminalDelimiter(query.trim(),
mysql && mysqlSqlMode.contains("NO_BACKSLASH_ESCAPES"), mysql && mysqlSqlMode.contains("ANSI_QUOTES"),
mysql && mysqlSqlMode.contains("MSSQL"));
// WITH SESSION must stay outside the derived table; terminate trailing line comments with a newline.
int start = jdbcType == TOdbcTableType.TRINO ? trinoSessionQueryStart(source) : 0;
return source.substring(0, start) + "SELECT " + Joiner.on(", ").join(columns)
+ " FROM (" + source.substring(start) + "\n) doris_jdbc_source";
}
private String stripTerminalDelimiter(String sql, boolean noBackslashEscapes, boolean ansiQuotes, boolean mssql) {
boolean mysql = jdbcType == TOdbcTableType.MYSQL || jdbcType == TOdbcTableType.OCEANBASE;
List<Integer> delimiters = new java.util.ArrayList<>();
for (int i = 0; i < sql.length();) {
char c = sql.charAt(i);
if (Character.isWhitespace(c)) {
i++;
} else if (jdbcType == TOdbcTableType.CLICKHOUSE
&& (sql.startsWith("//", i) || sql.startsWith("#!", i) || sql.startsWith("# ", i))) {
// ClickHouse comments may contain unmatched quotes; their contents end only at LF.
int newline = sql.indexOf('\n', i + 2);
i = newline < 0 ? sql.length() : newline + 1;
// MySQL's second dash needs a following whitespace/control character; --1 is arithmetic.
} else if ((sql.startsWith("--", i) && (!mysql || (i + 2 < sql.length()
&& (Character.isWhitespace(sql.charAt(i + 2)) || Character.isISOControl(sql.charAt(i + 2))))))
|| (c == '#'
&& (jdbcType == TOdbcTableType.MYSQL || jdbcType == TOdbcTableType.OCEANBASE))) {
while (i < sql.length() && sql.charAt(i) != '\n' && sql.charAt(i) != '\r') {
i++;
}
} else if (sql.startsWith("/*", i)) {
i = skipBlockComment(sql, i, jdbcType == TOdbcTableType.CLICKHOUSE);
} else if (jdbcType == TOdbcTableType.CLICKHOUSE && c == '$') {
i = skipClickHouseHeredoc(sql, i);
delimiters.clear();
} else if (c == '\'' || c == '"' || c == '`' || (mysql && mssql && c == '[')) {
// MariaDB MSSQL identifiers use brackets; only a doubled closing bracket escapes the delimiter.
char quote = c == '[' ? ']' : c;
for (i++; i < sql.length(); i++) {
// ANSI_QUOTES makes double quotes identifiers, where backslashes never escape the closing quote.
// Both quoting modes belong to the remote session.
// ClickHouse also permits backslash escapes inside backtick-quoted identifiers.
if (sql.charAt(i) == '\\' && quote != ']' && !(mysql && noBackslashEscapes)
&& !(mysql && ansiQuotes && quote == '"')
&& (quote != '`' || jdbcType == TOdbcTableType.CLICKHOUSE)
&& jdbcType != TOdbcTableType.TRINO && jdbcType != TOdbcTableType.PRESTO) {
i++;
} else if (sql.charAt(i) == quote) {
if (i + 1 < sql.length() && sql.charAt(i + 1) == quote) {
i++;
} else {
i++;
break;
}
}
}
delimiters.clear();
} else {
if (c == ';') {
delimiters.add(i);
} else {
delimiters.clear();
}
i++;
}
}
// A statement delimiter may precede trailing comments, which must stay outside SQL literals.
// Remove only the terminal delimiter run; interior delimiters still fail as multi-statements.
if (delimiters.isEmpty()) {
return sql;
}
StringBuilder result = new StringBuilder(sql);
for (int i = delimiters.size() - 1; i >= 0; i--) {
result.deleteCharAt(delimiters.get(i));
}
return result.toString();
}
private static int skipClickHouseHeredoc(String sql, int start) {
// A dollar sign inside a bare identifier is not the beginning of a heredoc.
if (start > 0 && (isClickHouseWordCharacter(sql.charAt(start - 1)) || sql.charAt(start - 1) == '$')) {
return start + 1;
}
int tagEnd = start + 1;
while (tagEnd < sql.length() && isClickHouseWordCharacter(sql.charAt(tagEnd))) {
tagEnd++;
}
if (tagEnd == sql.length() || sql.charAt(tagEnd) != '$') {
return start + 1;
}
// Heredocs contain raw text: embedded quotes, comments and delimiters must not affect scanning.
String tag = sql.substring(start, tagEnd + 1);
int end = sql.indexOf(tag, tagEnd + 1);
return end < 0 ? start + 1 : end + tag.length();
}
private static boolean isClickHouseWordCharacter(char c) {
return c >= 'a' && c <= 'z' || c >= 'A' && c <= 'Z' || c >= '0' && c <= '9' || c == '_';
}
private static int skipBlockComment(String sql, int start, boolean nested) {
// Trino, Presto and MySQL end at the first closing marker; ClickHouse permits nested comments.
int depth = 1;
int i = start + 2;
while (i < sql.length() && depth > 0) {
if (nested && sql.startsWith("/*", i)) {
depth++;
i += 2;
} else if (sql.startsWith("*/", i)) {
depth--;
i += 2;
} else {
i++;
}
}
return i;
}
private static int trinoSessionQueryStart(String sql) {
int depth = 0;
int prefixWords = 0;
for (int i = 0; i < sql.length();) {
char c = sql.charAt(i);
if (c == '\'' || c == '"') {
char quote = c;
for (i++; i < sql.length(); i++) {
if (sql.charAt(i) == quote) {
if (i + 1 < sql.length() && sql.charAt(i + 1) == quote) {
i++;
} else {
i++;
break;
}
}
}
} else if (sql.startsWith("--", i)) {
int newline = sql.indexOf('\n', i + 2);
i = newline < 0 ? sql.length() : newline + 1;
} else if (sql.startsWith("/*", i)) {
i = skipBlockComment(sql, i, false);
} else if (c == '(') {
depth++;
i++;
} else if (c == ')') {
depth--;
i++;
} else if (Character.isLetterOrDigit(c) || c == '_') {
int start = i++;
while (i < sql.length() && (Character.isLetterOrDigit(sql.charAt(i)) || sql.charAt(i) == '_')) {
i++;
}
if (depth != 0) {
continue;
}
String word = sql.substring(start, i);
if (prefixWords < 2) {
if (!word.equalsIgnoreCase(prefixWords == 0 ? "WITH" : "SESSION")) {
return 0;
}
prefixWords++;
} else if (word.equalsIgnoreCase("SELECT") || word.equalsIgnoreCase("WITH")
|| word.equalsIgnoreCase("TABLE") || word.equalsIgnoreCase("VALUES")) {
return start;
}
} else {
i++;
}
}
return 0;
}
private String getJdbcQueryStr() {
StringBuilder sql = new StringBuilder("SELECT ");
// Oracle use the where clause to do top n
if (shouldPushDownLimit() && (jdbcType == TOdbcTableType.ORACLE
|| jdbcType == TOdbcTableType.OCEANBASE_ORACLE)) {
filters.add("ROWNUM <= " + limit);
}
// MSSQL use select top to do top n
if (shouldPushDownLimit() && jdbcType == TOdbcTableType.SQLSERVER) {
sql.append("TOP " + limit + " ");
}
sql.append(Joiner.on(", ").join(columns));
sql.append(" FROM ").append(tableName);
if (!filters.isEmpty()) {
sql.append(" WHERE (");
sql.append(Joiner.on(") AND (").join(filters));
sql.append(")");
}
// Other DataBase use limit do top n
if (shouldPushDownLimit()
&& (jdbcType == TOdbcTableType.MYSQL
|| jdbcType == TOdbcTableType.POSTGRESQL
|| jdbcType == TOdbcTableType.MONGODB
|| jdbcType == TOdbcTableType.CLICKHOUSE
|| jdbcType == TOdbcTableType.SAP_HANA
|| jdbcType == TOdbcTableType.TRINO
|| jdbcType == TOdbcTableType.PRESTO
|| jdbcType == TOdbcTableType.OCEANBASE
|| jdbcType == TOdbcTableType.GBASE)) {
sql.append(" LIMIT ").append(limit);
}
if (jdbcType == TOdbcTableType.CLICKHOUSE
&& ConnectContext.get() != null
&& ConnectContext.get().getSessionVariable().jdbcClickhouseQueryFinal) {
sql.append(" SETTINGS final = 1");
}
return sql.toString();
}
@Override
public String getNodeExplainString(String prefix, TExplainLevel detailLevel) {
StringBuilder output = new StringBuilder();
if (isTableValuedFunction) {
output.append(prefix).append("TABLE VALUE FUNCTION\n");
output.append(prefix).append("CATALOG ID: ").append(catalogId).append("\n");
output.append(prefix).append("QUERY: ").append(getTvfQuery()).append("\n");
} else {
output.append(prefix).append("CATALOG ID: ").append(catalogId).append("\n");
output.append(prefix).append("TABLE: ").append(tableName).append("\n");
if (detailLevel == TExplainLevel.BRIEF) {
return output.toString();
}
output.append(prefix).append("QUERY: ").append(getJdbcQueryStr()).append("\n");
if (!conjuncts.isEmpty()) {
Expr expr = convertConjunctsToAndCompoundPredicate(conjuncts);
output.append(prefix).append("PREDICATES: ").append(expr.toSql()).append("\n");
}
}
if (useTopnFilter()) {
String topnFilterSources = String.join(",",
topnFilterSortNodes.stream()
.map(node -> node.getId().asInt() + "").collect(Collectors.toList()));
output.append(prefix).append("TOPN OPT:").append(topnFilterSources).append("\n");
}
return output.toString();
}
@Override
public void finalizeForNereids() throws UserException {
createJdbcColumns();
createJdbcFilters();
createScanRangeLocations();
}
@Override
protected void createScanRangeLocations() throws UserException {
scanRangeLocations = Lists.newArrayList(createSingleScanRangeLocations(backendPolicy));
}
@Override
protected void toThrift(TPlanNode msg) {
msg.node_type = TPlanNodeType.JDBC_SCAN_NODE;
msg.jdbc_scan_node = new TJdbcScanNode();
msg.jdbc_scan_node.setTupleId(desc.getId().asInt());
msg.jdbc_scan_node.setTableName(tableName);
if (isTableValuedFunction) {
msg.jdbc_scan_node.setQueryString(getTvfQuery());
} else {
msg.jdbc_scan_node.setQueryString(getJdbcQueryStr());
}
msg.jdbc_scan_node.setTableType(jdbcType);
msg.jdbc_scan_node.setIsTvf(isTableValuedFunction);
super.toThrift(msg);
}
@Override
protected String debugString() {
MoreObjects.ToStringHelper helper = MoreObjects.toStringHelper(this);
return helper.addValue(super.debugString()).toString();
}
@Override
public int getNumInstances() {
ConnectContext context = ConnectContext.get();
if (context == null) {
return 1;
}
return context.getSessionVariable().getParallelExecInstanceNum(scanContext.getClusterName());
}
private static boolean shouldPushDownConjunct(TOdbcTableType tableType, Expr expr) {
// PostgreSQL instants outside the Doris range become NULL during decoding. Remote null
// checks and even column comparisons can therefore disagree with the values Doris sees.
if (tableType == TOdbcTableType.POSTGRESQL
&& expr.contains((Expr child) -> child.getType().isTimeStampTz())) {
return false;
}
// JDBC dialects do not share Doris' zoned literal syntax or session timezone.
// Keep instant comparisons local until each dialect has a lossless literal serializer.
List<DateLiteral> dates = Lists.newArrayList();
expr.collect(DateLiteral.class, dates);
if (dates.stream().anyMatch(date -> date.getType().isTimeStampTz())) {
return false;
}
if ((containsFunctionCallExpr(expr) || containsCastExpr(expr))
&& expr.contains((Expr child) -> child.getType().isTimeStampTz())) {
// Calendar extraction and casts use the Doris session zone. A remote UTC session
// (or a ClickHouse column zone) can filter out rows before Doris evaluates them.
return false;
}
// These dialects do not accept Doris X'...' as binary literals (PostgreSQL reads bit
// strings, DB2 uses BX, and Oracle modes require HEXTORAW). Keep these predicates local.
if ((tableType == TOdbcTableType.POSTGRESQL || tableType == TOdbcTableType.ORACLE
|| tableType == TOdbcTableType.SQLSERVER || tableType == TOdbcTableType.DB2
|| tableType == TOdbcTableType.OCEANBASE_ORACLE)
&& expr.contains(org.apache.doris.analysis.VarBinaryLiteral.class)) {
return false;
}
// Prevent pushing down expressions with NullLiteral to Oracle
if (ConnectContext.get() != null
&& !ConnectContext.get().getSessionVariable().enableJdbcOracleNullPredicatePushDown
&& containsNullLiteral(expr)
&& tableType.equals(TOdbcTableType.ORACLE)) {
return false;
}
// Prevent pushing down cast expressions if ConnectContext is null or cast pushdown is disabled
if (ConnectContext.get() == null || !ConnectContext.get()
.getSessionVariable().enableJdbcCastPredicatePushDown) {
if (containsCastExpr(expr)) {
return false;
}
}
if (containsFunctionCallExpr(expr)) {
if (tableType.equals(TOdbcTableType.MYSQL) || tableType.equals(TOdbcTableType.CLICKHOUSE)
|| tableType.equals(TOdbcTableType.ORACLE)) {
if (ConnectContext.get() != null) {
return ConnectContext.get().getSessionVariable().enableExtFuncPredPushdown;
} else {
return true;
}
} else {
return false;
}
} else {
return true;
}
}
private static boolean containsFunctionCallExpr(Expr expr) {
List<FunctionCallExpr> fnExprList = Lists.newArrayList();
expr.collect(FunctionCallExpr.class, fnExprList);
return !fnExprList.isEmpty();
}
public static String conjunctExprToString(TOdbcTableType tableType, Expr expr, TableIf tbl) {
if (expr == null) {
return "";
}
if (expr instanceof CompoundPredicate) {
CompoundPredicate compoundPredicate = (CompoundPredicate) expr;
if (compoundPredicate.getOp() == Operator.NOT) {
String childString = conjunctExprToString(tableType, compoundPredicate.getChild(0), tbl);
return "(NOT " + childString + ")";
} else {
String leftString = conjunctExprToString(tableType, compoundPredicate.getChild(0), tbl);
String rightString = "";
if (compoundPredicate.getChildren().size() > 1) {
rightString = conjunctExprToString(tableType, compoundPredicate.getChild(1), tbl);
}
String opString = compoundPredicate.getOp().toString();
return "(" + leftString + " " + opString + " " + rightString + ")";
}
}
if (expr.contains(DateLiteral.class) && expr instanceof BinaryPredicate) {
ArrayList<Expr> children = expr.getChildren();
String filter = children.get(0).toExternalSql(TableType.JDBC_EXTERNAL_TABLE, tbl);
filter += " " + ((BinaryPredicate) expr).getOp().toString() + " ";
if (tableType.equals(TOdbcTableType.ORACLE)) {
filter += handleOracleDateFormat(children.get(1), tbl);
} else if (tableType.equals(TOdbcTableType.TRINO) || tableType.equals(TOdbcTableType.PRESTO)) {
filter += handleTrinoDateFormat(children.get(1), tbl);
} else if (tableType.equals(TOdbcTableType.SQLSERVER)) {
filter += handleSQLServerDateFormat(children.get(1), tbl);
} else {
filter += children.get(1).toExternalSql(TableType.JDBC_EXTERNAL_TABLE, tbl);
}
return filter;
}
if (expr.contains(DateLiteral.class) && expr instanceof InPredicate) {
InPredicate inPredicate = (InPredicate) expr;
Expr leftChild = inPredicate.getChild(0);
String filter = leftChild.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++) {
Expr inItem = inPredicate.getChild(i);
if (tableType.equals(TOdbcTableType.ORACLE)) {
inItemStrings.add(handleOracleDateFormat(inItem, tbl));
} else if (tableType.equals(TOdbcTableType.TRINO) || tableType.equals(TOdbcTableType.PRESTO)) {
inItemStrings.add(handleTrinoDateFormat(inItem, tbl));
} else if (tableType.equals(TOdbcTableType.SQLSERVER)) {
inItemStrings.add(handleSQLServerDateFormat(inItem, tbl));
} else {
inItemStrings.add(inItem.toExternalSql(TableType.JDBC_EXTERNAL_TABLE, tbl));
}
}
filter += String.join(", ", inItemStrings);
filter += ")";
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";
}
return expr.toExternalSql(TableType.JDBC_EXTERNAL_TABLE, tbl);
}
private static String handleOracleDateFormat(Expr expr, TableIf tbl) {
if (expr.isConstant()
&& (expr.getType().isDatetime() || expr.getType().isDatetimeV2())) {
String dateStr = expr.getStringValue();
// Check if the date string contains milliseconds/microseconds
if (dateStr.contains(".")) {
// For Oracle, we need to use to_timestamp for fractional seconds
// Extract date part and fractional seconds part
String formatModel = getString(dateStr);
return "to_timestamp('" + dateStr + "', '" + formatModel + "')";
}
// Regular datetime without fractional seconds
return "to_date('" + dateStr + "', 'yyyy-mm-dd hh24:mi:ss')";
}
return expr.toExternalSql(TableType.JDBC_EXTERNAL_TABLE, tbl);
}
@NotNull
private static String getString(String dateStr) {
String[] parts = dateStr.split("\\.");
String fractionPart = parts[1];
// Determine the format model based on the length of fractional seconds
String formatModel;
if (fractionPart.length() <= 3) {
// Milliseconds (up to 3 digits)
formatModel = "yyyy-mm-dd hh24:mi:ss.FF3";
} else {
// Microseconds (up to 6 digits)
formatModel = "yyyy-mm-dd hh24:mi:ss.FF6";
}
return formatModel;
}
private static String handleTrinoDateFormat(Expr expr, TableIf tbl) {
if (expr.isConstant()) {
if (expr.getType().isDate() || expr.getType().isDateV2()) {
return "date '" + expr.getStringValue() + "'";
} else if (expr.getType().isDatetime() || expr.getType().isDatetimeV2()) {
return "timestamp '" + expr.getStringValue() + "'";
}
}
return expr.toExternalSql(TableType.JDBC_EXTERNAL_TABLE, tbl);
}
private static String handleSQLServerDateFormat(Expr expr, TableIf tbl) {
if (expr.isConstant()) {
if (expr.getType().isDatetime() || expr.getType().isDatetimeV2()) {
// Use CONVERT with style 121 (ODBC canonical: yyyy-mm-dd hh:mi:ss.mmm)
// which is language-independent and handles fractional seconds
return "CONVERT(DATETIME, '" + expr.getStringValue() + "', 121)";
} else if (expr.getType().isDate() || expr.getType().isDateV2()) {
// Use CONVERT with style 23 (ISO8601: yyyy-mm-dd)
// which is language-independent
return "CONVERT(DATE, '" + expr.getStringValue() + "', 23)";
}
}
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);
return !nullExprList.isEmpty();
}
private static boolean containsCastExpr(Expr expr) {
List<CastExpr> castExprList = Lists.newArrayList();
expr.collect(CastExpr.class, castExprList);
return !castExprList.isEmpty();
}
}