This is an automated email from the ASF dual-hosted git repository. yiguolei pushed a commit to branch branch-2.1 in repository https://gitbox.apache.org/repos/asf/doris.git
commit 853dbdcb00e7f182f967b50546d9a1328faab231 Author: lihangyu <[email protected]> AuthorDate: Thu May 9 10:39:46 2024 +0800 [Feature](PreparedStatement) implement general server side prepared (#33807) --- be/src/pipeline/exec/result_sink_operator.cpp | 9 +- be/src/runtime/runtime_state.h | 5 + be/src/service/point_query_executor.cpp | 1 - be/src/vec/sink/vresult_sink.cpp | 12 +- .../main/java/org/apache/doris/catalog/Type.java | 4 + fe/fe-core/src/main/cup/sql_parser.cup | 8 +- .../java/org/apache/doris/analysis/Analyzer.java | 16 +- .../org/apache/doris/analysis/BinaryPredicate.java | 8 +- .../main/java/org/apache/doris/analysis/Expr.java | 2 +- .../org/apache/doris/analysis/LiteralExpr.java | 16 +- .../apache/doris/analysis/NativeInsertStmt.java | 11 ++ .../org/apache/doris/analysis/PlaceHolderExpr.java | 16 +- .../org/apache/doris/analysis/PrepareStmt.java | 124 +++++++++------ .../org/apache/doris/analysis/StatementBase.java | 15 +- .../org/apache/doris/analysis/StringLiteral.java | 1 + .../java/org/apache/doris/catalog/OlapTable.java | 11 +- .../org/apache/doris/planner/OlapScanNode.java | 13 +- .../main/java/org/apache/doris/qe/Coordinator.java | 3 + .../org/apache/doris/qe/MysqlConnectProcessor.java | 42 ++--- .../java/org/apache/doris/qe/PointQueryExec.java | 2 +- .../org/apache/doris/qe/PrepareStmtContext.java | 3 + .../java/org/apache/doris/qe/SessionVariable.java | 6 + .../java/org/apache/doris/qe/StmtExecutor.java | 62 +++++--- fe/fe-core/src/main/jflex/sql_scanner.flex | 1 - .../data/point_query_p0/test_point_query.out | 36 ----- .../test_point_query_cluster_key.out | 111 -------------- .../data/prepared_stmt_p0/prepared_stmt.out | 55 +++++++ .../org/apache/doris/regression/suite/Suite.groovy | 5 + .../apache/doris/regression/util/JdbcUtils.groovy | 30 ++++ .../suites/point_query_p0/test_point_query.groovy | 12 +- .../test_point_query_cluster_key.groovy | 12 +- .../suites/prepared_stmt_p0/prepared_stmt.groovy | 170 +++++++++++++++++++++ 32 files changed, 527 insertions(+), 295 deletions(-) diff --git a/be/src/pipeline/exec/result_sink_operator.cpp b/be/src/pipeline/exec/result_sink_operator.cpp index d0cd130cc8a..ec54e1bdd5c 100644 --- a/be/src/pipeline/exec/result_sink_operator.cpp +++ b/be/src/pipeline/exec/result_sink_operator.cpp @@ -81,8 +81,13 @@ Status ResultSinkLocalState::open(RuntimeState* state) { // create writer based on sink type switch (p._sink_type) { case TResultSinkType::MYSQL_PROTOCAL: - _writer.reset(new (std::nothrow) vectorized::VMysqlResultWriter( - _sender.get(), _output_vexpr_ctxs, _profile)); + if (state->mysql_row_binary_format()) { + _writer.reset(new (std::nothrow) vectorized::VMysqlResultWriter<true>( + _sender.get(), _output_vexpr_ctxs, _profile)); + } else { + _writer.reset(new (std::nothrow) vectorized::VMysqlResultWriter<false>( + _sender.get(), _output_vexpr_ctxs, _profile)); + } break; default: return Status::InternalError("Unknown result sink type"); diff --git a/be/src/runtime/runtime_state.h b/be/src/runtime/runtime_state.h index 4b5d3641705..30402630463 100644 --- a/be/src/runtime/runtime_state.h +++ b/be/src/runtime/runtime_state.h @@ -178,6 +178,11 @@ public: _query_options.enable_common_expr_pushdown; } + bool mysql_row_binary_format() const { + return _query_options.__isset.mysql_row_binary_format && + _query_options.mysql_row_binary_format; + } + bool enable_faster_float_convert() const { return _query_options.__isset.faster_float_convert && _query_options.faster_float_convert; } diff --git a/be/src/service/point_query_executor.cpp b/be/src/service/point_query_executor.cpp index 5f8675b2650..3f04ed44eb4 100644 --- a/be/src/service/point_query_executor.cpp +++ b/be/src/service/point_query_executor.cpp @@ -44,7 +44,6 @@ #include "vec/exprs/vexpr.h" #include "vec/exprs/vexpr_context.h" #include "vec/jsonb/serialize.h" -#include "vec/sink/vmysql_result_writer.cpp" #include "vec/sink/vmysql_result_writer.h" namespace doris { diff --git a/be/src/vec/sink/vresult_sink.cpp b/be/src/vec/sink/vresult_sink.cpp index a7da5a6a5ff..e089f788160 100644 --- a/be/src/vec/sink/vresult_sink.cpp +++ b/be/src/vec/sink/vresult_sink.cpp @@ -93,10 +93,16 @@ Status VResultSink::prepare(RuntimeState* state) { // create writer based on sink type switch (_sink_type) { - case TResultSinkType::MYSQL_PROTOCAL: - _writer.reset(new (std::nothrow) - VMysqlResultWriter(_sender.get(), _output_vexpr_ctxs, _profile)); + case TResultSinkType::MYSQL_PROTOCAL: { + if (state->mysql_row_binary_format()) { + _writer.reset(new (std::nothrow) VMysqlResultWriter<true>( + _sender.get(), _output_vexpr_ctxs, _profile)); + } else { + _writer.reset(new (std::nothrow) VMysqlResultWriter<false>( + _sender.get(), _output_vexpr_ctxs, _profile)); + } break; + } case TResultSinkType::ARROW_FLIGHT_PROTOCAL: { std::shared_ptr<arrow::Schema> arrow_schema; RETURN_IF_ERROR(convert_expr_ctxs_arrow_schema(_output_vexpr_ctxs, &arrow_schema)); diff --git a/fe/fe-common/src/main/java/org/apache/doris/catalog/Type.java b/fe/fe-common/src/main/java/org/apache/doris/catalog/Type.java index 99081349755..258a0f57bfd 100644 --- a/fe/fe-common/src/main/java/org/apache/doris/catalog/Type.java +++ b/fe/fe-common/src/main/java/org/apache/doris/catalog/Type.java @@ -412,6 +412,10 @@ public abstract class Type { return isScalarType(PrimitiveType.INVALID_TYPE); } + public boolean isUnsupported() { + return isScalarType(PrimitiveType.UNSUPPORTED); + } + public boolean isValid() { return !isInvalid(); } diff --git a/fe/fe-core/src/main/cup/sql_parser.cup b/fe/fe-core/src/main/cup/sql_parser.cup index a0564670bb2..4808285c076 100644 --- a/fe/fe-core/src/main/cup/sql_parser.cup +++ b/fe/fe-core/src/main/cup/sql_parser.cup @@ -1211,7 +1211,11 @@ stmt ::= | insert_overwrite_stmt : stmt {: RESULT = stmt; :} | update_stmt : stmt - {: RESULT = stmt; :} + {: + RESULT = stmt; + stmt.setPlaceHolders(parser.placeholder_expr_list); + parser.placeholder_expr_list.clear(); + :} | backup_stmt : stmt {: RESULT = stmt; :} | restore_stmt : stmt @@ -5541,7 +5545,7 @@ expr_or_default ::= prepare_stmt ::= KW_PREPARE variable_name:name KW_FROM select_stmt:s {: - RESULT = new PrepareStmt(s, name, false); + RESULT = new PrepareStmt(s, name); s.setPlaceHolders(parser.placeholder_expr_list); parser.placeholder_expr_list.clear(); :} diff --git a/fe/fe-core/src/main/java/org/apache/doris/analysis/Analyzer.java b/fe/fe-core/src/main/java/org/apache/doris/analysis/Analyzer.java index 195e6e6e0b3..e4a57732dce 100644 --- a/fe/fe-core/src/main/java/org/apache/doris/analysis/Analyzer.java +++ b/fe/fe-core/src/main/java/org/apache/doris/analysis/Analyzer.java @@ -618,6 +618,14 @@ public class Analyzer { return callDepth; } + public void setPrepareStmt(PrepareStmt stmt) { + prepareStmt = stmt; + } + + public PrepareStmt getPrepareStmt() { + return prepareStmt; + } + public void setInlineView(boolean inlineView) { isInlineView = inlineView; } @@ -630,14 +638,6 @@ public class Analyzer { explicitViewAlias = alias; } - public void setPrepareStmt(PrepareStmt stmt) { - prepareStmt = stmt; - } - - public PrepareStmt getPrepareStmt() { - return prepareStmt; - } - public String getExplicitViewAlias() { return explicitViewAlias; } diff --git a/fe/fe-core/src/main/java/org/apache/doris/analysis/BinaryPredicate.java b/fe/fe-core/src/main/java/org/apache/doris/analysis/BinaryPredicate.java index f0b35fe758b..8a0228a5f75 100644 --- a/fe/fe-core/src/main/java/org/apache/doris/analysis/BinaryPredicate.java +++ b/fe/fe-core/src/main/java/org/apache/doris/analysis/BinaryPredicate.java @@ -508,11 +508,13 @@ public class BinaryPredicate extends Predicate implements Writable { public void analyzeImpl(Analyzer analyzer) throws AnalysisException { super.analyzeImpl(analyzer); this.checkIncludeBitmap(); - // Ignore placeholder - if (getChild(0) instanceof PlaceHolderExpr || getChild(1) instanceof PlaceHolderExpr) { + // Ignore placeholder, when it type is invalid. + // Invalid type could happen when analyze prepared point query select statement, + // since the value is occupied but not assigned + if ((getChild(0) instanceof PlaceHolderExpr && getChild(0).type == Type.UNSUPPORTED) + || (getChild(1) instanceof PlaceHolderExpr && getChild(1).type == Type.UNSUPPORTED)) { return; } - for (Expr expr : children) { if (expr instanceof Subquery) { Subquery subquery = (Subquery) expr; diff --git a/fe/fe-core/src/main/java/org/apache/doris/analysis/Expr.java b/fe/fe-core/src/main/java/org/apache/doris/analysis/Expr.java index 32db7c1acd7..91fd0dc9827 100644 --- a/fe/fe-core/src/main/java/org/apache/doris/analysis/Expr.java +++ b/fe/fe-core/src/main/java/org/apache/doris/analysis/Expr.java @@ -1471,7 +1471,7 @@ public abstract class Expr extends TreeNode<Expr> implements ParseNode, Cloneabl * failure to convert a string literal to a date literal */ public final Expr castTo(Type targetType) throws AnalysisException { - if (this instanceof PlaceHolderExpr && this.type.isInvalid()) { + if (this instanceof PlaceHolderExpr && this.type.isUnsupported()) { return this; } // If the targetType is NULL_TYPE then ignore the cast because NULL_TYPE diff --git a/fe/fe-core/src/main/java/org/apache/doris/analysis/LiteralExpr.java b/fe/fe-core/src/main/java/org/apache/doris/analysis/LiteralExpr.java index 89e87699e71..613e96e0fb9 100644 --- a/fe/fe-core/src/main/java/org/apache/doris/analysis/LiteralExpr.java +++ b/fe/fe-core/src/main/java/org/apache/doris/analysis/LiteralExpr.java @@ -343,14 +343,6 @@ public abstract class LiteralExpr extends Expr implements Comparable<LiteralExpr return getStringValue(); } - // Parse from binary data, the format follows mysql binary protocal - // see https://dev.mysql.com/doc/dev/mysql-server/latest/page_protocol_binary_resultset.html. - // Return next offset - public void setupParamFromBinary(ByteBuffer data) { - Preconditions.checkState(false, - "should implement this in derived class. " + this.type.toSql()); - } - public static LiteralExpr getLiteralByMysqlType(int mysqlType) throws AnalysisException { switch (mysqlType) { // MYSQL_TYPE_TINY @@ -499,4 +491,12 @@ public abstract class LiteralExpr extends Expr implements Comparable<LiteralExpr default: throw new AnalysisException("Wrong type from thrift;"); } } + + // Parse from binary data, the format follows mysql binary protocal + // see https://dev.mysql.com/doc/dev/mysql-server/latest/page_protocol_binary_resultset.html. + // Return next offset + public void setupParamFromBinary(ByteBuffer data) { + Preconditions.checkState(false, + "should implement this in derived class. " + this.type.toSql()); + } } diff --git a/fe/fe-core/src/main/java/org/apache/doris/analysis/NativeInsertStmt.java b/fe/fe-core/src/main/java/org/apache/doris/analysis/NativeInsertStmt.java index 4c0574b4e1c..73adf7d03f2 100644 --- a/fe/fe-core/src/main/java/org/apache/doris/analysis/NativeInsertStmt.java +++ b/fe/fe-core/src/main/java/org/apache/doris/analysis/NativeInsertStmt.java @@ -175,6 +175,17 @@ public class NativeInsertStmt extends InsertStmt { } } + public NativeInsertStmt(NativeInsertStmt other) { + super(other.label, null, null); + this.tblName = other.tblName; + this.targetPartitionNames = other.targetPartitionNames; + this.label = other.label; + this.queryStmt = other.queryStmt; + this.planHints = other.planHints; + this.targetColumnNames = other.targetColumnNames; + this.isValuesOrConstantSelect = other.isValuesOrConstantSelect; + } + public NativeInsertStmt(InsertTarget target, String label, List<String> cols, InsertSource source, List<String> hints) { super(new LabelName(null, label), null, null); diff --git a/fe/fe-core/src/main/java/org/apache/doris/analysis/PlaceHolderExpr.java b/fe/fe-core/src/main/java/org/apache/doris/analysis/PlaceHolderExpr.java index 9b7c7932f3a..3e6ab9a8272 100644 --- a/fe/fe-core/src/main/java/org/apache/doris/analysis/PlaceHolderExpr.java +++ b/fe/fe-core/src/main/java/org/apache/doris/analysis/PlaceHolderExpr.java @@ -36,7 +36,7 @@ public class PlaceHolderExpr extends LiteralExpr { int mysqlTypeCode = -1; public PlaceHolderExpr() { - + type = Type.UNSUPPORTED; } public void setTypeCode(int mysqlTypeCode) { @@ -45,10 +45,12 @@ public class PlaceHolderExpr extends LiteralExpr { protected PlaceHolderExpr(LiteralExpr literal) { this.lExpr = literal; + this.type = literal.getType(); } protected PlaceHolderExpr(PlaceHolderExpr other) { this.lExpr = other.lExpr; + this.type = other.type; } public void setLiteral(LiteralExpr literal) { @@ -161,7 +163,17 @@ public class PlaceHolderExpr extends LiteralExpr { @Override public String toSqlImpl() { - return getStringValue(); + if (this.lExpr == null) { + return "?"; + } + return "_placeholder_(" + this.lExpr.toSqlImpl() + ")"; + } + + // @Override + public Expr reset() { + this.lExpr = null; + this.type = Type.UNSUPPORTED; + return this; } @Override diff --git a/fe/fe-core/src/main/java/org/apache/doris/analysis/PrepareStmt.java b/fe/fe-core/src/main/java/org/apache/doris/analysis/PrepareStmt.java index 2e116ae80e9..90da98c14f4 100644 --- a/fe/fe-core/src/main/java/org/apache/doris/analysis/PrepareStmt.java +++ b/fe/fe-core/src/main/java/org/apache/doris/analysis/PrepareStmt.java @@ -17,6 +17,7 @@ package org.apache.doris.analysis; +// import org.apache.doris.catalog.Column; import org.apache.doris.catalog.OlapTable; import org.apache.doris.common.UserException; import org.apache.doris.qe.ConnectContext; @@ -40,42 +41,40 @@ import java.util.Map; import java.util.UUID; public class PrepareStmt extends StatementBase { + // We provide bellow types of prepared statement: + // NONE, which is not prepared + // FULL_PREPARED, which is real prepared, which will cache analyzed statement and planner + // STATEMENT, which only cache statement it self, but need to analyze each time executed. + public enum PreparedType { + NONE, FULL_PREPARED, STATEMENT + } + private static final Logger LOG = LogManager.getLogger(PrepareStmt.class); private StatementBase inner; private String stmtName; - // Cached for better CPU performance, since serialize DescriptorTable and // outputExprs are heavy work private ByteString serializedDescTable; private ByteString serializedOutputExpr; private ByteString serializedQueryOptions; - private TDescriptorTable descTable; + private UUID id; - // whether return binary protocol mysql row or not - private boolean binaryRowFormat; - int schemaVersion = -1; - OlapTable tbl; - ConnectContext context; + private int schemaVersion = -1; + private OlapTable tbl; + private ConnectContext context; + private PreparedType preparedType = PreparedType.STATEMENT; + boolean isPointQueryShortCircuit = false; + + private TDescriptorTable descTable; // Serialized mysql Field, this could avoid serialize mysql field each time sendFields. // Since, serialize fields is too heavy when table is wide Map<String, byte[]> serializedFields = Maps.newHashMap(); - // We provide bellow types of prepared statement: - // NONE, which is not prepared - // FULL_PREPARED, which is really prepared, which will cache analyzed statement and planner - // STATEMENT, which only cache statement itself, but need to analyze each time executed - public enum PreparedType { - NONE, FULL_PREPARED, STATEMENT - } - - private PreparedType preparedType = PreparedType.STATEMENT; - - public PrepareStmt(StatementBase stmt, String name, boolean binaryRowFormat) { + public PrepareStmt(StatementBase stmt, String name) { this.inner = stmt; this.stmtName = name; this.id = UUID.randomUUID(); - this.binaryRowFormat = binaryRowFormat; } public void setContext(ConnectContext ctx) { @@ -83,7 +82,8 @@ public class PrepareStmt extends StatementBase { } public boolean needReAnalyze() { - if (preparedType == PreparedType.FULL_PREPARED && schemaVersion == tbl.getBaseSchemaVersion()) { + if (preparedType == PreparedType.FULL_PREPARED + && schemaVersion == tbl.getBaseSchemaVersion()) { return false; } reset(); @@ -98,10 +98,6 @@ public class PrepareStmt extends StatementBase { return id; } - public boolean isBinaryProtocol() { - return binaryRowFormat; - } - public byte[] getSerializedField(String colName) { return serializedFields.getOrDefault(colName, null); } @@ -158,23 +154,45 @@ public class PrepareStmt extends StatementBase { return serializedQueryOptions; } + public boolean isPointQueryShortCircuit() { + return isPointQueryShortCircuit; + } + @Override public void analyze(Analyzer analyzer) throws UserException { + // TODO support more Statement + if (!(inner instanceof SelectStmt) && !(inner instanceof NativeInsertStmt)) { + throw new UserException("Only support prepare SelectStmt or NativeInsertStmt"); + } + analyzer.setPrepareStmt(this); if (inner instanceof SelectStmt) { - // Use tmpAnalyzer since selectStmt will be reAnalyzed - Analyzer tmpAnalyzer = new Analyzer(context.getEnv(), context); + // Try to use FULL_PREPARED to increase performance SelectStmt selectStmt = (SelectStmt) inner; - inner.analyze(tmpAnalyzer); - if (!selectStmt.checkAndSetPointQuery()) { - throw new UserException("Only support prepare SelectStmt point query now"); + try { + // Use tmpAnalyzer since selectStmt will be reAnalyzed + Analyzer tmpAnalyzer = new Analyzer(context.getEnv(), context); + inner.analyze(tmpAnalyzer); + // Case 1 short circuit point query + if (selectStmt.checkAndSetPointQuery()) { + tbl = (OlapTable) selectStmt.getTableRefs().get(0).getTable(); + schemaVersion = tbl.getBaseSchemaVersion(); + preparedType = PreparedType.FULL_PREPARED; + isPointQueryShortCircuit = true; + LOG.debug("using FULL_PREPARED prepared"); + return; + } + } catch (UserException e) { + LOG.debug("fallback to STATEMENT prepared, {}", e); + } finally { + // will be reanalyzed + selectStmt.reset(); + } + // use session var to decide whether to use full prepared or let user client handle to do fail over + if (preparedType != PreparedType.FULL_PREPARED + && !ConnectContext.get().getSessionVariable().enableServeSidePreparedStatement) { + throw new UserException("Failed to prepare statement" + + "try to set enable_server_side_prepared_statement = true"); } - tbl = (OlapTable) selectStmt.getTableRefs().get(0).getTable(); - schemaVersion = tbl.getBaseSchemaVersion(); - // reset will be reAnalyzed - selectStmt.reset(); - analyzer.setPrepareStmt(this); - // tmpAnalyzer.setPrepareStmt(this); - preparedType = PreparedType.FULL_PREPARED; } else if (inner instanceof NativeInsertStmt) { LabelName label = ((NativeInsertStmt) inner).getLoadLabel(); if (label == null || Strings.isNullOrEmpty(label.getLabelName())) { @@ -183,9 +201,9 @@ public class PrepareStmt extends StatementBase { } else { throw new UserException("Only support prepare InsertStmt without label now"); } - } else { - throw new UserException("Only support prepare SelectStmt or InsertStmt now"); } + preparedType = PreparedType.STATEMENT; + LOG.debug("using STATEMENT prepared"); } public String getName() { @@ -197,10 +215,6 @@ public class PrepareStmt extends StatementBase { return RedirectStatus.NO_FORWARD; } - public StatementBase getInnerStmt() { - return inner; - } - public List<PlaceHolderExpr> placeholders() { return inner.getPlaceHolders(); } @@ -209,6 +223,10 @@ public class PrepareStmt extends StatementBase { return inner.getPlaceHolders().size(); } + public PreparedType getPreparedType() { + return preparedType; + } + public List<Expr> getPlaceHolderExprList() { ArrayList<Expr> slots = new ArrayList<>(); for (PlaceHolderExpr pexpr : inner.getPlaceHolders()) { @@ -225,6 +243,24 @@ public class PrepareStmt extends StatementBase { return lables; } + public StatementBase getInnerStmt() { + if (preparedType == PreparedType.FULL_PREPARED) { + // For performance reason we could reuse the inner statement when FULL_PREPARED + return inner; + } + // Make a copy of Statement, since anlyze will modify the structure of Statement. + // But we should keep the original statement + if (inner instanceof SelectStmt) { + return new SelectStmt((SelectStmt) inner); + } + // Other statement could reuse the inner statement + return inner; + } + + public int argsSize() { + return inner.getPlaceHolders().size(); + } + public void asignValues(List<LiteralExpr> values) throws UserException { if (values.size() != inner.getPlaceHolders().size()) { throw new UserException("Invalid arguments size " @@ -240,10 +276,6 @@ public class PrepareStmt extends StatementBase { } } - public PreparedType getPreparedType() { - return preparedType; - } - @Override public void reset() { serializedDescTable = null; diff --git a/fe/fe-core/src/main/java/org/apache/doris/analysis/StatementBase.java b/fe/fe-core/src/main/java/org/apache/doris/analysis/StatementBase.java index ca6501409e3..c0ebab251f8 100644 --- a/fe/fe-core/src/main/java/org/apache/doris/analysis/StatementBase.java +++ b/fe/fe-core/src/main/java/org/apache/doris/analysis/StatementBase.java @@ -28,12 +28,17 @@ import org.apache.doris.rewrite.ExprRewriter; import org.apache.doris.thrift.TQueryOptions; import com.google.common.base.Preconditions; +import org.apache.logging.log4j.LogManager; +import org.apache.logging.log4j.Logger; import java.util.ArrayList; import java.util.Collections; import java.util.List; public abstract class StatementBase implements ParseNode { + private static final Logger LOG = LogManager.getLogger(StatementBase.class); + private String clusterName; + // Set this variable if this QueryStmt is the top level query from an EXPLAIN <query> protected ExplainOptions explainOptions = null; @@ -51,7 +56,6 @@ public abstract class StatementBase implements ParseNode { private UserIdentity userInfo; private boolean isPrepared = false; - // select * from tbl where a = ? and b = ? // `?` is the placeholder private ArrayList<PlaceHolderExpr> placeholders = new ArrayList<>(); @@ -100,14 +104,15 @@ public abstract class StatementBase implements ParseNode { this.explainOptions = options; } - public boolean isExplain() { - return this.explainOptions != null; - } - public void setPlaceHolders(ArrayList<PlaceHolderExpr> placeholders) { + LOG.debug("setPlaceHolders {}", placeholders); this.placeholders = new ArrayList<PlaceHolderExpr>(placeholders); } + public boolean isExplain() { + return this.explainOptions != null; + } + public ArrayList<PlaceHolderExpr> getPlaceHolders() { return this.placeholders; } diff --git a/fe/fe-core/src/main/java/org/apache/doris/analysis/StringLiteral.java b/fe/fe-core/src/main/java/org/apache/doris/analysis/StringLiteral.java index 5b2836a318d..40051fa9113 100644 --- a/fe/fe-core/src/main/java/org/apache/doris/analysis/StringLiteral.java +++ b/fe/fe-core/src/main/java/org/apache/doris/analysis/StringLiteral.java @@ -350,5 +350,6 @@ public class StringLiteral extends LiteralExpr { if (LOG.isDebugEnabled()) { LOG.debug("parsed value '{}'", value); } + type = Type.VARCHAR; } } diff --git a/fe/fe-core/src/main/java/org/apache/doris/catalog/OlapTable.java b/fe/fe-core/src/main/java/org/apache/doris/catalog/OlapTable.java index a38c749e109..9cf9be00915 100644 --- a/fe/fe-core/src/main/java/org/apache/doris/catalog/OlapTable.java +++ b/fe/fe-core/src/main/java/org/apache/doris/catalog/OlapTable.java @@ -2092,6 +2092,12 @@ public class OlapTable extends Table implements MTMVRelatedTableIf { return false; } + public int getBaseSchemaVersion() { + MaterializedIndexMeta baseIndexMeta = indexIdToMeta.get(baseIndexId); + return baseIndexMeta.getSchemaVersion(); + } + + public void setEnableSingleReplicaCompaction(boolean enableSingleReplicaCompaction) { if (tableProperty == null) { tableProperty = new TableProperty(new HashMap<>()); @@ -2220,11 +2226,6 @@ public class OlapTable extends Table implements MTMVRelatedTableIf { return null; } - public int getBaseSchemaVersion() { - MaterializedIndexMeta baseIndexMeta = indexIdToMeta.get(baseIndexId); - return baseIndexMeta.getSchemaVersion(); - } - public int getIndexSchemaVersion(long indexId) { MaterializedIndexMeta indexMeta = indexIdToMeta.get(indexId); return indexMeta.getSchemaVersion(); diff --git a/fe/fe-core/src/main/java/org/apache/doris/planner/OlapScanNode.java b/fe/fe-core/src/main/java/org/apache/doris/planner/OlapScanNode.java index 72024952853..743e9966185 100644 --- a/fe/fe-core/src/main/java/org/apache/doris/planner/OlapScanNode.java +++ b/fe/fe-core/src/main/java/org/apache/doris/planner/OlapScanNode.java @@ -29,6 +29,7 @@ import org.apache.doris.analysis.InPredicate; import org.apache.doris.analysis.IntLiteral; import org.apache.doris.analysis.LiteralExpr; import org.apache.doris.analysis.PartitionNames; +import org.apache.doris.analysis.PrepareStmt; import org.apache.doris.analysis.SlotDescriptor; import org.apache.doris.analysis.SlotId; import org.apache.doris.analysis.SlotRef; @@ -213,6 +214,7 @@ public class OlapScanNode extends ScanNode { // only used in short circuit plan at present private final PartitionPruneV2ForShortCircuitPlan cachedPartitionPruner = new PartitionPruneV2ForShortCircuitPlan(); + PrepareStmt preparedStatment = null; // Constructs node to scan given data files of table 'tbl'. public OlapScanNode(PlanNodeId id, TupleDescriptor desc, String planNodeName) { @@ -559,9 +561,9 @@ public class OlapScanNode extends ScanNode { super.init(analyzer); filterDeletedRows(analyzer); - // lazy evaluation, since stmt is a prepared statment - isFromPrepareStmt = analyzer.getPrepareStmt() != null; - if (!isFromPrepareStmt) { + // point query could do lazy evaluation, since stmt is a prepared statment + preparedStatment = analyzer.getPrepareStmt(); + if (preparedStatment == null || !preparedStatment.isPointQueryShortCircuit()) { if (olapTable.getPartitionInfo().enableAutomaticPartition()) { partitionsInfo = olapTable.getPartitionInfo(); analyzerPartitionExpr(analyzer, partitionsInfo); @@ -627,7 +629,7 @@ public class OlapScanNode extends ScanNode { } // prepare stmt evaluate lazily in Coordinator execute - if (!isFromPrepareStmt) { + if (preparedStatment == null || !preparedStatment.isPointQueryShortCircuit()) { try { createScanRangeLocations(); } catch (AnalysisException e) { @@ -1119,7 +1121,8 @@ public class OlapScanNode extends ScanNode { } public boolean isPointQuery() { - return this.pointQueryEqualPredicats != null; + return this.pointQueryEqualPredicats != null + || (preparedStatment != null && preparedStatment.isPointQueryShortCircuit()); } private void computeTabletInfo() throws UserException { diff --git a/fe/fe-core/src/main/java/org/apache/doris/qe/Coordinator.java b/fe/fe-core/src/main/java/org/apache/doris/qe/Coordinator.java index 1bc45bb358f..bd777c5be64 100644 --- a/fe/fe-core/src/main/java/org/apache/doris/qe/Coordinator.java +++ b/fe/fe-core/src/main/java/org/apache/doris/qe/Coordinator.java @@ -39,6 +39,7 @@ import org.apache.doris.datasource.ExternalScanNode; import org.apache.doris.datasource.FileQueryScanNode; import org.apache.doris.load.loadv2.LoadJob; import org.apache.doris.metric.MetricRepo; +import org.apache.doris.mysql.MysqlCommand; import org.apache.doris.nereids.NereidsPlanner; import org.apache.doris.nereids.stats.StatsErrorEstimator; import org.apache.doris.planner.DataPartition; @@ -415,6 +416,8 @@ public class Coordinator implements CoordInterface { this.queryOptions.setEnableScanNodeRunSerial(context.getSessionVariable().isEnableScanRunSerial()); this.queryOptions.setFeProcessUuid(ExecuteEnv.getInstance().getProcessUUID()); this.queryOptions.setWaitFullBlockScheduleTimes(context.getSessionVariable().getWaitFullBlockScheduleTimes()); + this.queryOptions.setMysqlRowBinaryFormat( + context.getCommand() == MysqlCommand.COM_STMT_EXECUTE); } public ConnectContext getConnectContext() { diff --git a/fe/fe-core/src/main/java/org/apache/doris/qe/MysqlConnectProcessor.java b/fe/fe-core/src/main/java/org/apache/doris/qe/MysqlConnectProcessor.java index 61c93adbf96..d117eeba949 100644 --- a/fe/fe-core/src/main/java/org/apache/doris/qe/MysqlConnectProcessor.java +++ b/fe/fe-core/src/main/java/org/apache/doris/qe/MysqlConnectProcessor.java @@ -110,32 +110,33 @@ public class MysqlConnectProcessor extends ConnectProcessor { } prepareCtx.stmt.setIsPrepared(); int paramCount = prepareCtx.stmt.getParmCount(); + LOG.debug("execute prepared statement {}, paramCount {}", stmtId, paramCount); // null bitmap - byte[] nullbitmapData = new byte[(paramCount + 7) / 8]; - packetBuf.get(nullbitmapData); String stmtStr = ""; try { - // new_params_bind_flag - if ((int) packetBuf.get() != 0) { - // parse params's types - for (int i = 0; i < paramCount; ++i) { - int typeCode = packetBuf.getChar(); - if (LOG.isDebugEnabled()) { + List<LiteralExpr> realValueExprs = new ArrayList<>(); + if (paramCount > 0) { + byte[] nullbitmapData = new byte[(paramCount + 7) / 8]; + packetBuf.get(nullbitmapData); + // new_params_bind_flag + if ((int) packetBuf.get() != 0) { + // parse params's types + for (int i = 0; i < paramCount; ++i) { + int typeCode = packetBuf.getChar(); LOG.debug("code {}", typeCode); + prepareCtx.stmt.placeholders().get(i).setTypeCode(typeCode); } - prepareCtx.stmt.placeholders().get(i).setTypeCode(typeCode); } - } - List<LiteralExpr> realValueExprs = new ArrayList<>(); - // parse param data - for (int i = 0; i < paramCount; ++i) { - if (isNull(nullbitmapData, i)) { - realValueExprs.add(new NullLiteral()); - continue; + // parse param data + for (int i = 0; i < paramCount; ++i) { + if (isNull(nullbitmapData, i)) { + realValueExprs.add(new NullLiteral()); + continue; + } + LiteralExpr l = prepareCtx.stmt.placeholders().get(i).createLiteralFromType(); + l.setupParamFromBinary(packetBuf); + realValueExprs.add(l); } - LiteralExpr l = prepareCtx.stmt.placeholders().get(i).createLiteralFromType(); - l.setupParamFromBinary(packetBuf); - realValueExprs.add(l); } ExecuteStmt executeStmt = new ExecuteStmt(String.valueOf(stmtId), realValueExprs); // TODO set real origin statement @@ -151,7 +152,7 @@ public class MysqlConnectProcessor extends ConnectProcessor { if (preparedStmtContext != null && !(preparedStmtContext.stmt.getInnerStmt() instanceof InsertStmt)) { stmtStr = executeStmt.toSql(); } - } catch (Throwable e) { + } catch (Throwable e) { // Catch all throwable. // If reach here, maybe doris bug. LOG.warn("Process one query failed because unknown reason: ", e); @@ -201,7 +202,6 @@ public class MysqlConnectProcessor extends ConnectProcessor { break; case COM_QUERY: case COM_STMT_PREPARE: - // Process COM_QUERY statement, handleQuery(command); break; case COM_STMT_EXECUTE: diff --git a/fe/fe-core/src/main/java/org/apache/doris/qe/PointQueryExec.java b/fe/fe-core/src/main/java/org/apache/doris/qe/PointQueryExec.java index 5742a99de2f..f31251dae17 100644 --- a/fe/fe-core/src/main/java/org/apache/doris/qe/PointQueryExec.java +++ b/fe/fe-core/src/main/java/org/apache/doris/qe/PointQueryExec.java @@ -119,7 +119,7 @@ public class PointQueryExec implements CoordInterface { this.cacheID = prepareStmt.getID(); this.serializedDescTable = prepareStmt.getSerializedDescTable(); this.serializedOutputExpr = prepareStmt.getSerializedOutputExprs(); - this.isBinaryProtocol = prepareStmt.isBinaryProtocol(); + this.isBinaryProtocol = true; this.serializedQueryOptions = prepareStmt.getSerializedQueryOptions(); } else { // TODO diff --git a/fe/fe-core/src/main/java/org/apache/doris/qe/PrepareStmtContext.java b/fe/fe-core/src/main/java/org/apache/doris/qe/PrepareStmtContext.java index 3df2b277c29..400f5047b53 100644 --- a/fe/fe-core/src/main/java/org/apache/doris/qe/PrepareStmtContext.java +++ b/fe/fe-core/src/main/java/org/apache/doris/qe/PrepareStmtContext.java @@ -23,8 +23,11 @@ import org.apache.doris.planner.OriginalPlanner; import org.apache.doris.planner.Planner; import com.google.common.base.Preconditions; +import org.apache.logging.log4j.LogManager; +import org.apache.logging.log4j.Logger; public class PrepareStmtContext { + private static final Logger LOG = LogManager.getLogger(PrepareStmtContext.class); public PrepareStmt stmt; public ConnectContext ctx; public Planner planner; diff --git a/fe/fe-core/src/main/java/org/apache/doris/qe/SessionVariable.java b/fe/fe-core/src/main/java/org/apache/doris/qe/SessionVariable.java index 8d345d39f44..eab4c0f7608 100644 --- a/fe/fe-core/src/main/java/org/apache/doris/qe/SessionVariable.java +++ b/fe/fe-core/src/main/java/org/apache/doris/qe/SessionVariable.java @@ -129,6 +129,8 @@ public class SessionVariable implements Serializable, Writable { public static final String ENABLE_INSERT_STRICT = "enable_insert_strict"; public static final String ENABLE_SPILLING = "enable_spilling"; public static final String ENABLE_EXCHANGE_NODE_PARALLEL_MERGE = "enable_exchange_node_parallel_merge"; + + public static final String ENABLE_SERVER_SIDE_PREPARED_STATEMENT = "enable_server_side_prepared_statement"; public static final String PREFER_JOIN_METHOD = "prefer_join_method"; public static final String ENABLE_FOLD_CONSTANT_BY_BE = "enable_fold_constant_by_be"; @@ -1330,6 +1332,10 @@ public class SessionVariable implements Serializable, Writable { @VariableMgr.VarAttr(name = TOPN_OPT_LIMIT_THRESHOLD) public long topnOptLimitThreshold = 1024; + @VariableMgr.VarAttr(name = ENABLE_SERVER_SIDE_PREPARED_STATEMENT, needForward = true, description = { + "是否启用开启服务端prepared statement", "Set whether to enable server side prepared statement."}) + public boolean enableServeSidePreparedStatement = false; + // Default value is false, which means the group by and having clause // should first use column name not alias. According to mysql. @VariableMgr.VarAttr(name = GROUP_BY_AND_HAVING_USE_ALIAS_FIRST) diff --git a/fe/fe-core/src/main/java/org/apache/doris/qe/StmtExecutor.java b/fe/fe-core/src/main/java/org/apache/doris/qe/StmtExecutor.java index c48cb2ce2ab..1cf499f0c6c 100644 --- a/fe/fe-core/src/main/java/org/apache/doris/qe/StmtExecutor.java +++ b/fe/fe-core/src/main/java/org/apache/doris/qe/StmtExecutor.java @@ -48,6 +48,7 @@ import org.apache.doris.analysis.NullLiteral; import org.apache.doris.analysis.OutFileClause; import org.apache.doris.analysis.PartitionNames; import org.apache.doris.analysis.PrepareStmt; +import org.apache.doris.analysis.PrepareStmt.PreparedType; import org.apache.doris.analysis.Queriable; import org.apache.doris.analysis.QueryStmt; import org.apache.doris.analysis.RedirectStatus; @@ -123,6 +124,7 @@ import org.apache.doris.load.loadv2.LoadManagerAdapter; import org.apache.doris.mysql.MysqlChannel; import org.apache.doris.mysql.MysqlCommand; import org.apache.doris.mysql.MysqlEofPacket; +import org.apache.doris.mysql.MysqlOkPacket; import org.apache.doris.mysql.MysqlSerializer; import org.apache.doris.mysql.ProxyMysqlChannel; import org.apache.doris.mysql.privilege.PrivPredicate; @@ -267,6 +269,9 @@ public class StmtExecutor { // The profile of this execution private final Profile profile; + private ExecuteStmt execStmt; + PrepareStmtContext preparedStmtCtx = null; + // The result schema if "dry_run_query" is true. // Only one column to indicate the real return row numbers. private static final CommonResultSetMetaData DRY_RUN_QUERY_METADATA = new CommonResultSetMetaData( @@ -1062,9 +1067,8 @@ public class StmtExecutor { parseByLegacy(); boolean preparedStmtReanalyzed = false; - PrepareStmtContext preparedStmtCtx = null; if (parsedStmt instanceof ExecuteStmt) { - ExecuteStmt execStmt = (ExecuteStmt) parsedStmt; + execStmt = (ExecuteStmt) parsedStmt; preparedStmtCtx = context.getPreparedStmt(execStmt.getName()); if (preparedStmtCtx == null) { throw new UserException("Could not execute, since `" + execStmt.getName() + "` not exist"); @@ -1085,7 +1089,8 @@ public class StmtExecutor { } // continue analyze preparedStmtReanalyzed = true; - preparedStmtCtx.stmt.analyze(analyzer); + preparedStmtCtx.stmt.reset(); + // preparedStmtCtx.stmt.analyze(analyzer); } // yiguolei: insert stmt's grammar analysis will write editlog, @@ -1100,7 +1105,7 @@ public class StmtExecutor { if (parsedStmt instanceof PrepareStmt || context.getCommand() == MysqlCommand.COM_STMT_PREPARE) { if (context.getCommand() == MysqlCommand.COM_STMT_PREPARE) { prepareStmt = new PrepareStmt(parsedStmt, - String.valueOf(context.getEnv().getNextStmtId()), true /*binary protocol*/); + String.valueOf(context.getEnv().getNextStmtId())); } else { prepareStmt = (PrepareStmt) parsedStmt; } @@ -1138,7 +1143,6 @@ public class StmtExecutor { "enable_unified_load=true, should be insert stmt"); } } - if (parsedStmt instanceof QueryStmt || (parsedStmt instanceof InsertStmt && !((InsertStmt) parsedStmt).needLoadManager()) || parsedStmt instanceof CreateTableAsSelectStmt @@ -1201,7 +1205,9 @@ public class StmtExecutor { throw new AnalysisException("Unexpected exception: " + e.getMessage()); } } - if (preparedStmtReanalyzed) { + if (preparedStmtReanalyzed + && preparedStmtCtx.stmt.getPreparedType() == PrepareStmt.PreparedType.FULL_PREPARED) { + prepareStmt.asignValues(execStmt.getArgs()); if (LOG.isDebugEnabled()) { LOG.debug("update planner and analyzer after prepared statement reanalyzed"); } @@ -1265,6 +1271,12 @@ public class StmtExecutor { queryStmt.removeOrderByElements(); } } + if (prepareStmt != null) { + analyzer.setPrepareStmt(prepareStmt); + if (execStmt != null && prepareStmt.getPreparedType() != PreparedType.FULL_PREPARED) { + prepareStmt.asignValues(execStmt.getArgs()); + } + } parsedStmt.analyze(analyzer); if (parsedStmt instanceof QueryStmt || parsedStmt instanceof InsertStmt) { if (parsedStmt instanceof NativeInsertStmt && ((NativeInsertStmt) parsedStmt).isGroupCommit()) { @@ -1323,14 +1335,14 @@ public class StmtExecutor { Lists.newArrayList(parsedStmt.getColLabels()); // Re-analyze the stmt with a new analyzer. analyzer = new Analyzer(context.getEnv(), context); - - if (prepareStmt != null) { - // Re-analyze prepareStmt with a new analyzer - prepareStmt.reset(); - prepareStmt.analyze(analyzer); - } // query re-analyze parsedStmt.reset(); + if (prepareStmt != null) { + analyzer.setPrepareStmt(prepareStmt); + if (execStmt != null && prepareStmt.getPreparedType() != PreparedType.FULL_PREPARED) { + prepareStmt.asignValues(execStmt.getArgs()); + } + } analyzer.setReAnalyze(true); parsedStmt.analyze(analyzer); @@ -1603,7 +1615,8 @@ public class StmtExecutor { // handle selects that fe can do without be, so we can make sql tools happy, especially the setup step. // TODO FE not support doris field type conversion to arrow field type. - if (!context.getConnectType().equals(ConnectType.ARROW_FLIGHT_SQL)) { + if (!context.getConnectType().equals(ConnectType.ARROW_FLIGHT_SQL) + && context.getCommand() != MysqlCommand.COM_STMT_EXECUTE) { Optional<ResultSet> resultSet = planner.handleQueryInFe(parsedStmt); if (resultSet.isPresent()) { sendResultSet(resultSet.get()); @@ -2340,12 +2353,12 @@ public class StmtExecutor { // register prepareStmt if (LOG.isDebugEnabled()) { LOG.debug("add prepared statement {}, isBinaryProtocol {}", - prepareStmt.getName(), prepareStmt.isBinaryProtocol()); + prepareStmt.getName(), context.getCommand() == MysqlCommand.COM_STMT_PREPARE); } context.addPreparedStmt(prepareStmt.getName(), new PrepareStmtContext(prepareStmt, - context, planner, analyzer, prepareStmt.getName())); - if (prepareStmt.isBinaryProtocol()) { + context, planner, analyzer, prepareStmt.getName())); + if (context.getCommand() == MysqlCommand.COM_STMT_PREPARE) { sendStmtPrepareOK(); } } @@ -2422,13 +2435,18 @@ public class StmtExecutor { serializer.writeField(colNames.get(i), Type.fromPrimitiveType(types.get(i))); context.getMysqlChannel().sendOnePacket(serializer.toByteBuffer()); } + serializer.reset(); + if (!context.getMysqlChannel().clientDeprecatedEOF()) { + MysqlEofPacket eofPacket = new MysqlEofPacket(context.getState()); + eofPacket.writeTo(serializer); + } else { + MysqlOkPacket okPacket = new MysqlOkPacket(context.getState()); + okPacket.writeTo(serializer); + } + context.getMysqlChannel().sendOnePacket(serializer.toByteBuffer()); } - // send EOF if nessessary - if (!context.getMysqlChannel().clientDeprecatedEOF()) { - context.getState().setEof(); - } else { - context.getState().setOk(); - } + context.getMysqlChannel().flush(); + context.getState().setNoop(); } private void sendFields(List<String> colNames, List<Type> types) throws IOException { diff --git a/fe/fe-core/src/main/jflex/sql_scanner.flex b/fe/fe-core/src/main/jflex/sql_scanner.flex index 153e7295588..39f6f62bce0 100644 --- a/fe/fe-core/src/main/jflex/sql_scanner.flex +++ b/fe/fe-core/src/main/jflex/sql_scanner.flex @@ -581,7 +581,6 @@ import org.apache.doris.qe.SqlModeHelper; tokenIdMap.put(new Integer(SqlParserSymbols.BITXOR), "^"); tokenIdMap.put(new Integer(SqlParserSymbols.NUMERIC_OVERFLOW), "NUMERIC OVERFLOW"); tokenIdMap.put(new Integer(SqlParserSymbols.PLACEHOLDER), "?"); - } public static boolean isKeyword(Integer tokenId) { diff --git a/regression-test/data/point_query_p0/test_point_query.out b/regression-test/data/point_query_p0/test_point_query.out index d23a62474c3..ff4b1932b3a 100644 --- a/regression-test/data/point_query_p0/test_point_query.out +++ b/regression-test/data/point_query_p0/test_point_query.out @@ -71,18 +71,6 @@ -- !sql -- 6120202020646464 6C616F6F71 32.92200050354004 --- !sql -- -1231 119291.110000000 ddd laooq \N 2020-01-01T12:36:38 \N 1022-01-01 \N 1.111 [119181.111100000, 819019.119100000, null] \N 0 0 - --- !sql -- -1237 120939.111300000 a ddd laooq 2030-01-02 2020-01-01T12:36:38 22.822 7022-01-01 false 90696620686827832.374 [1.100000000, 2.200000000, 3.300000000, 4.400000000, 5.500000000] [] 0 0 - --- !sql -- -1231 119291.110000000 ddd laooq \N 2020-01-01T12:36:38 \N 1022-01-01 \N 1.111 [119181.111100000, 819019.119100000, null] \N 0 0 - --- !sql -- -1237 120939.111300000 a ddd laooq 2030-01-02 2020-01-01T12:36:38 22.822 7022-01-01 false 90696620686827832.374 [1.100000000, 2.200000000, 3.300000000, 4.400000000, 5.500000000] [] 0 0 - -- !sql -- 0 1 2 3 @@ -158,18 +146,6 @@ -- !sql -- 6120202020646464 6C616F6F71 32.92200050354004 --- !sql -- -1231 119291.110000000 ddd laooq \N 2020-01-01T12:36:38 \N 1022-01-01 \N 1.111 [119181.111100000, 819019.119100000, null] \N 0 0 - --- !sql -- -1237 120939.111300000 a ddd laooq 2030-01-02 2020-01-01T12:36:38 22.822 7022-01-01 false 90696620686827832.374 [1.100000000, 2.200000000, 3.300000000, 4.400000000, 5.500000000] [] 0 0 - --- !sql -- -1231 119291.110000000 ddd laooq \N 2020-01-01T12:36:38 \N 1022-01-01 \N 1.111 [119181.111100000, 819019.119100000, null] \N 0 0 - --- !sql -- -1237 120939.111300000 a ddd laooq 2030-01-02 2020-01-01T12:36:38 22.822 7022-01-01 false 90696620686827832.374 [1.100000000, 2.200000000, 3.300000000, 4.400000000, 5.500000000] [] 0 0 - -- !sql -- 0 1 2 3 @@ -245,18 +221,6 @@ -- !sql -- 6120202020646464 6C616F6F71 32.92200050354004 --- !sql -- -1231 119291.110000000 ddd laooq \N 2020-01-01T12:36:38 \N 1022-01-01 \N 1.111 [119181.111100000, 819019.119100000, null] \N 0 0 - --- !sql -- -1237 120939.111300000 a ddd laooq 2030-01-02 2020-01-01T12:36:38 22.822 7022-01-01 false 90696620686827832.374 [1.100000000, 2.200000000, 3.300000000, 4.400000000, 5.500000000] [] 0 0 - --- !sql -- -1231 119291.110000000 ddd laooq \N 2020-01-01T12:36:38 \N 1022-01-01 \N 1.111 [119181.111100000, 819019.119100000, null] \N 0 0 - --- !sql -- -1237 120939.111300000 a ddd laooq 2030-01-02 2020-01-01T12:36:38 22.822 7022-01-01 false 90696620686827832.374 [1.100000000, 2.200000000, 3.300000000, 4.400000000, 5.500000000] [] 0 0 - -- !sql -- 0 1 2 3 diff --git a/regression-test/data/point_query_p0/test_point_query_cluster_key.out b/regression-test/data/point_query_p0/test_point_query_cluster_key.out index d23a62474c3..f92dc58341b 100644 --- a/regression-test/data/point_query_p0/test_point_query_cluster_key.out +++ b/regression-test/data/point_query_p0/test_point_query_cluster_key.out @@ -71,18 +71,6 @@ -- !sql -- 6120202020646464 6C616F6F71 32.92200050354004 --- !sql -- -1231 119291.110000000 ddd laooq \N 2020-01-01T12:36:38 \N 1022-01-01 \N 1.111 [119181.111100000, 819019.119100000, null] \N 0 0 - --- !sql -- -1237 120939.111300000 a ddd laooq 2030-01-02 2020-01-01T12:36:38 22.822 7022-01-01 false 90696620686827832.374 [1.100000000, 2.200000000, 3.300000000, 4.400000000, 5.500000000] [] 0 0 - --- !sql -- -1231 119291.110000000 ddd laooq \N 2020-01-01T12:36:38 \N 1022-01-01 \N 1.111 [119181.111100000, 819019.119100000, null] \N 0 0 - --- !sql -- -1237 120939.111300000 a ddd laooq 2030-01-02 2020-01-01T12:36:38 22.822 7022-01-01 false 90696620686827832.374 [1.100000000, 2.200000000, 3.300000000, 4.400000000, 5.500000000] [] 0 0 - -- !sql -- 0 1 2 3 @@ -158,105 +146,6 @@ -- !sql -- 6120202020646464 6C616F6F71 32.92200050354004 --- !sql -- -1231 119291.110000000 ddd laooq \N 2020-01-01T12:36:38 \N 1022-01-01 \N 1.111 [119181.111100000, 819019.119100000, null] \N 0 0 - --- !sql -- -1237 120939.111300000 a ddd laooq 2030-01-02 2020-01-01T12:36:38 22.822 7022-01-01 false 90696620686827832.374 [1.100000000, 2.200000000, 3.300000000, 4.400000000, 5.500000000] [] 0 0 - --- !sql -- -1231 119291.110000000 ddd laooq \N 2020-01-01T12:36:38 \N 1022-01-01 \N 1.111 [119181.111100000, 819019.119100000, null] \N 0 0 - --- !sql -- -1237 120939.111300000 a ddd laooq 2030-01-02 2020-01-01T12:36:38 22.822 7022-01-01 false 90696620686827832.374 [1.100000000, 2.200000000, 3.300000000, 4.400000000, 5.500000000] [] 0 0 - --- !sql -- -0 1 2 3 - --- !point_select -- -1231 119291.110000000 ddd laooq \N 2020-01-01 12:36:38 \N 1022-01-01 \N 1.111 \N [119181.111100000, 819019.119100000, null] - --- !point_select -- -1231 119291.110000000 ddd laooq \N 2020-01-01 12:36:38 \N 1022-01-01 \N 1.111 \N [119181.111100000, 819019.119100000, null] - --- !point_select -- -1237 120939.111300000 a ddd laooq 2030-01-02 2020-01-01 12:36:38 22.822 7022-01-01 false 90696620686827832.374 [1.100000000, 2.200000000, 3.300000000, 4.400000000, 5.500000000] [] - --- !point_select -- -1232 12222.991211350 xxx laooq 2023-01-02 2020-01-01 12:36:38 522.762 2022-01-01 true 212.111 \N \N - --- !point_select -- -251 120939.111300000 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa laooq 2030-01-02 2020-01-01 12:36:38 251.0 7022-01-01 true 90696620686827832.374 [11111.000000000] [] - --- !point_select -- -252 120939.111300000 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa laooq 2030-01-02 2020-01-01 12:36:38 252.0 7022-01-01 false 90696620686827832.374 \N [0.000000000] - --- !point_select -- -298 120939.111300000 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa laooq 2030-01-02 2020-01-01 12:36:38 298.0 7022-01-01 true 90696620686827832.374 [] [] - --- !point_select -- -1235 991129292901.111380000 dd \N 2120-01-02 2020-01-01 12:36:38 652.692 5022-01-01 false 90696620686827832.374 [119181.111100000] ["aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"] - --- !point_select -- -646464 6C616F6F71 - --- !point_select -- -646464 6C616F6F71 - --- !point_select -- -646464 6C616F6F71 - --- !point_select -- -1235 120939.111300000 a ddd laooq 2030-01-02 2020-01-01 12:36:38 22.822 7022-01-01 true 1.111 [119291.192910000] ["111", "222", "333"] 1 - --- !point_select -- -1235 120939.111300000 a ddd laooq 2030-01-02 2020-01-01 12:36:38 22.822 7022-01-01 true 1.111 [119291.192910000] ["111", "222", "333"] 1 - --- !point_select -- -1235 120939.111300000 a ddd xxxxxx 2030-01-02 2020-01-01 12:36:38 22.822 7022-01-01 false 1929111.111 [119291.192910000] ["111", "222", "333"] 2 - --- !point_select -- -1235 120939.111300000 a ddd xxxxxx 2030-01-02 2020-01-01 12:36:38 22.822 7022-01-01 false 1929111.111 [119291.192910000] ["111", "222", "333"] 2 - --- !point_select -- -1235 120939.111300000 a ddd xxxxxx 2030-01-02 2020-01-01 12:36:38 22.822 7022-01-01 false 1929111.111 [119291.192910000] ["111", "222", "333"] 2 - --- !point_select -- -1235 120939.111300000 a ddd xxxxxx 2030-01-02 2020-01-01 12:36:38 22.822 7022-01-01 false 1929111.111 [119291.192910000] ["111", "222", "333"] 2 0 - --- !point_select -- -1235 120939.111300000 a ddd xxxxxx 2030-01-02 2020-01-01 12:36:38 22.822 7022-01-01 false 1929111.111 [119291.192910000] ["111", "222", "333"] 2 0 - --- !point_select -- -1235 120939.111300000 a ddd xxxxxx 2030-01-02 2020-01-01 12:36:38 22.822 7022-01-01 false 1929111.111 [119291.192910000] ["111", "222", "333"] 2 - --- !point_select -- -1235 120939.111300000 a ddd xxxxxx 2030-01-02 2020-01-01 12:36:38 22.822 7022-01-01 false 1929111.111 [119291.192910000] ["111", "222", "333"] 2 - --- !point_select -- -1235 120939.111300000 a ddd xxxxxx 2030-01-02 2020-01-01 12:36:38 22.822 7022-01-01 false 1929111.111 [119291.192910000] ["111", "222", "333"] 2 0 - --- !sql -- -1231 119291.110000000 ddd laooq \N 2020-01-01T12:36:38 \N 1022-01-01 \N 1.111 [119181.111100000, 819019.119100000, null] \N 0 0 - --- !sql -- -1237 120939.111300000 a ddd laooq 2030-01-02 2020-01-01T12:36:38 22.822 7022-01-01 false 90696620686827832.374 [1.100000000, 2.200000000, 3.300000000, 4.400000000, 5.500000000] [] 0 0 - --- !sql -- -6120202020646464 6C616F6F71 32.92200050354004 - --- !sql -- -1231 119291.110000000 ddd laooq \N 2020-01-01T12:36:38 \N 1022-01-01 \N 1.111 [119181.111100000, 819019.119100000, null] \N 0 0 - --- !sql -- -1237 120939.111300000 a ddd laooq 2030-01-02 2020-01-01T12:36:38 22.822 7022-01-01 false 90696620686827832.374 [1.100000000, 2.200000000, 3.300000000, 4.400000000, 5.500000000] [] 0 0 - --- !sql -- -1231 119291.110000000 ddd laooq \N 2020-01-01T12:36:38 \N 1022-01-01 \N 1.111 [119181.111100000, 819019.119100000, null] \N 0 0 - --- !sql -- -1237 120939.111300000 a ddd laooq 2030-01-02 2020-01-01T12:36:38 22.822 7022-01-01 false 90696620686827832.374 [1.100000000, 2.200000000, 3.300000000, 4.400000000, 5.500000000] [] 0 0 - -- !sql -- 0 1 2 3 diff --git a/regression-test/data/prepared_stmt_p0/prepared_stmt.out b/regression-test/data/prepared_stmt_p0/prepared_stmt.out new file mode 100644 index 00000000000..396ee931683 --- /dev/null +++ b/regression-test/data/prepared_stmt_p0/prepared_stmt.out @@ -0,0 +1,55 @@ +-- This file is automatically generated. You should know what you did if you want to edit this +-- !sql -- +1231 119291.110000000 ddd laooq \N 2020-01-01 12:36:38 \N 1022-01-01 ["2022-01-01 11:30:38", "2022-01-01 11:30:38", "2022-01-01 11:30:38"] +1232 12222.991211350 xxx laooq 2023-01-02 2020-01-01 12:36:38 522.762 2022-01-01 ["2023-01-01 11:30:38", "2023-01-01 11:30:38"] +1233 1.392932911 yyy laooq 2024-01-02 2020-01-01 12:36:38 52.862 3022-01-01 ["2024-01-01 11:30:38", "2024-01-01 11:30:38", "2024-01-01 11:30:38"] +1234 12919291.129191137 xxddd laooq 2025-01-02 2020-01-01 12:36:38 552.872 4022-01-01 ["2025-01-01 11:30:38", "2025-01-01 11:30:38", "2025-01-01 11:30:38"] +1235 991129292901.111380000 dd \N 2120-01-02 2020-01-01 12:36:38 652.692 5022-01-01 [] +1236 100320.111390000 laa ddd laooq 2220-01-02 2020-01-01 12:36:38 2.7692 6022-01-01 [null] +1237 120939.111300000 a ddd laooq 2030-01-02 2020-01-01 12:36:38 22.822 7022-01-01 ["2025-01-01 11:30:38"] + +-- !sql -- +1231 119291.110000000 ddd laooq \N 2020-01-01 12:36:38 \N 1022-01-01 ["2022-01-01 11:30:38", "2022-01-01 11:30:38", "2022-01-01 11:30:38"] +1232 12222.991211350 xxx laooq 2023-01-02 2020-01-01 12:36:38 522.762 2022-01-01 ["2023-01-01 11:30:38", "2023-01-01 11:30:38"] +1233 1.392932911 yyy laooq 2024-01-02 2020-01-01 12:36:38 52.862 3022-01-01 ["2024-01-01 11:30:38", "2024-01-01 11:30:38", "2024-01-01 11:30:38"] +1234 12919291.129191137 xxddd laooq 2025-01-02 2020-01-01 12:36:38 552.872 4022-01-01 ["2025-01-01 11:30:38", "2025-01-01 11:30:38", "2025-01-01 11:30:38"] +1235 991129292901.111380000 dd \N 2120-01-02 2020-01-01 12:36:38 652.692 5022-01-01 [] +1236 100320.111390000 laa ddd laooq 2220-01-02 2020-01-01 12:36:38 2.7692 6022-01-01 [null] +1237 120939.111300000 a ddd laooq 2030-01-02 2020-01-01 12:36:38 22.822 7022-01-01 ["2025-01-01 11:30:38"] + +-- !select0 -- +1231 119291.110000000 ddd laooq \N 2020-01-01 12:36:38 \N 1022-01-01 ["2022-01-01 11:30:38", "2022-01-01 11:30:38", "2022-01-01 11:30:38"] + +-- !select0 -- +1232 12222.991211350 xxx laooq 2023-01-02 2020-01-01 12:36:38 522.762 2022-01-01 ["2023-01-01 11:30:38", "2023-01-01 11:30:38"] + +-- !select0 -- +1232 12222.991211350 xxx laooq 2023-01-02 2020-01-01 12:36:38 522.762 2022-01-01 ["2023-01-01 11:30:38", "2023-01-01 11:30:38"] + +-- !select1 -- +646464 xxxx--- + +-- !select1 -- +787878 yyyy--- + +-- !select1 -- +787878 yyyy--- + +-- !select2 -- +1237 120939.111300000 a ddd laooq 2030-01-02 2020-01-01 12:36:38 22.822 7022-01-01 ["2025-01-01 11:30:38"] 1237 120939.111300000 a ddd laooq 2030-01-02 2020-01-01 12:36:38 22.822 7022-01-01 ["2025-01-01 11:30:38"] + +-- !select2 -- +1237 120939.111300000 a ddd laooq 2030-01-02 2020-01-01 12:36:38 22.822 7022-01-01 ["2025-01-01 11:30:38"] 1237 120939.111300000 a ddd laooq 2030-01-02 2020-01-01 12:36:38 22.822 7022-01-01 ["2025-01-01 11:30:38"] + +-- !select2 -- +1237 120939.111300000 a ddd laooq 2030-01-02 2020-01-01 12:36:38 22.822 7022-01-01 ["2025-01-01 11:30:38"] 1237 120939.111300000 a ddd laooq 2030-01-02 2020-01-01 12:36:38 22.822 7022-01-01 ["2025-01-01 11:30:38"] + +-- !select3 -- +1 1 user1 30 1234 12345 + +-- !select4 -- +10 + +-- !select5 -- +1 + diff --git a/regression-test/framework/src/main/groovy/org/apache/doris/regression/suite/Suite.groovy b/regression-test/framework/src/main/groovy/org/apache/doris/regression/suite/Suite.groovy index 28b865ea6e9..3efe1d90a8f 100644 --- a/regression-test/framework/src/main/groovy/org/apache/doris/regression/suite/Suite.groovy +++ b/regression-test/framework/src/main/groovy/org/apache/doris/regression/suite/Suite.groovy @@ -840,6 +840,11 @@ class Suite implements GroovyInterceptable { } return result } + List<List<Object>> exec(Object stmt) { + logger.info("Execute sql: ${stmt}".toString()) + def (result, meta )= JdbcUtils.executeToList(context.getConnection(), (PreparedStatement) stmt) + return result + } void quickRunTest(String tag, Object arg, boolean isOrder = false) { if (context.config.generateOutputFile || context.config.forceGenerateOutputFile) { diff --git a/regression-test/framework/src/main/groovy/org/apache/doris/regression/util/JdbcUtils.groovy b/regression-test/framework/src/main/groovy/org/apache/doris/regression/util/JdbcUtils.groovy index bc02f08f80c..821b80c3365 100644 --- a/regression-test/framework/src/main/groovy/org/apache/doris/regression/util/JdbcUtils.groovy +++ b/regression-test/framework/src/main/groovy/org/apache/doris/regression/util/JdbcUtils.groovy @@ -47,6 +47,36 @@ class JdbcUtils { return conn.prepareStatement(sql); } + static List<Map<String, Object>> executeToMapArray(Connection conn, String sql) { + def (result, meta) = JdbcUtils.executeToList(conn, sql) + + // get all column names as list + List<String> columnNames = new ArrayList<>() + for (int i = 0; i < meta.getColumnCount(); i++) { + columnNames.add(meta.getColumnName(i + 1)) + } + + // add result to res map list, each row is a map with key is column name + List<Map<String, Object>> res = new ArrayList<>() + for (int i = 0; i < result.size(); i++) { + Map<String, Object> row = new HashMap<>() + for (int j = 0; j < columnNames.size(); j++) { + row.put(columnNames.get(j), result.get(i).get(j)) + } + res.add(row) + } + return res; + } + + static Tuple2<List<List<Object>>, ResultSetMetaData> executeToList(Connection conn, PreparedStatement stmt) { + boolean hasResultSet = stmt.execute() + if (!hasResultSet) { + return [ImmutableList.of(ImmutableList.of(stmt.getUpdateCount())), null] + } else { + return toList(stmt.resultSet) + } + } + static Tuple2<List<List<Object>>, ResultSetMetaData> executeToStringList(Connection conn, PreparedStatement stmt) { return toStringList(stmt.executeQuery()) } diff --git a/regression-test/suites/point_query_p0/test_point_query.groovy b/regression-test/suites/point_query_p0/test_point_query.groovy index 653c151d89c..28a4eaa2a59 100644 --- a/regression-test/suites/point_query_p0/test_point_query.groovy +++ b/regression-test/suites/point_query_p0/test_point_query.groovy @@ -226,13 +226,13 @@ suite("test_point_query") { qt_sql """select /*+ SET_VAR(enable_nereids_planner=false) */ * from ${tableName} where k1 = 1237 and k2 = 120939.11130 and k3 = 'a ddd'""" qt_sql """select /*+ SET_VAR(enable_nereids_planner=false) */ hex(k3), hex(k4), k7 + 10.1 from ${tableName} where k1 = 1237 and k2 = 120939.11130 and k3 = 'a ddd'""" // prepared text - sql """ prepare stmt1 from select * from ${tableName} where k1 = % and k2 = % and k3 = % """ - qt_sql """execute stmt1 using (1231, 119291.11, 'ddd')""" - qt_sql """execute stmt1 using (1237, 120939.11130, 'a ddd')""" + // sql """ prepare stmt1 from select * from ${tableName} where k1 = % and k2 = % and k3 = % """ + // qt_sql """execute stmt1 using (1231, 119291.11, 'ddd')""" + // qt_sql """execute stmt1 using (1237, 120939.11130, 'a ddd')""" - sql """prepare stmt2 from select * from ${tableName} where k1 = % and k2 = % and k3 = %""" - qt_sql """execute stmt2 using (1231, 119291.11, 'ddd')""" - qt_sql """execute stmt2 using (1237, 120939.11130, 'a ddd')""" + // sql """prepare stmt2 from select * from ${tableName} where k1 = % and k2 = % and k3 = %""" + // qt_sql """execute stmt2 using (1231, 119291.11, 'ddd')""" + // qt_sql """execute stmt2 using (1237, 120939.11130, 'a ddd')""" tableName = "test_query" sql """DROP TABLE IF EXISTS ${tableName}""" sql """CREATE TABLE ${tableName} ( diff --git a/regression-test/suites/point_query_p0/test_point_query_cluster_key.groovy b/regression-test/suites/point_query_p0/test_point_query_cluster_key.groovy index a117199297d..0d23afbf4d5 100644 --- a/regression-test/suites/point_query_p0/test_point_query_cluster_key.groovy +++ b/regression-test/suites/point_query_p0/test_point_query_cluster_key.groovy @@ -226,13 +226,13 @@ suite("test_point_query_cluster_key") { qt_sql """select /*+ SET_VAR(enable_nereids_planner=false) */ * from ${tableName} where k1 = 1237 and k2 = 120939.11130 and k3 = 'a ddd'""" qt_sql """select /*+ SET_VAR(enable_nereids_planner=false) */ hex(k3), hex(k4), k7 + 10.1 from ${tableName} where k1 = 1237 and k2 = 120939.11130 and k3 = 'a ddd'""" // prepared text - sql """ prepare stmt1 from select * from ${tableName} where k1 = % and k2 = % and k3 = % """ - qt_sql """execute stmt1 using (1231, 119291.11, 'ddd')""" - qt_sql """execute stmt1 using (1237, 120939.11130, 'a ddd')""" + // sql """ prepare stmt1 from select * from ${tableName} where k1 = % and k2 = % and k3 = % """ + // qt_sql """execute stmt1 using (1231, 119291.11, 'ddd')""" + // qt_sql """execute stmt1 using (1237, 120939.11130, 'a ddd')""" - sql """prepare stmt2 from select * from ${tableName} where k1 = % and k2 = % and k3 = %""" - qt_sql """execute stmt2 using (1231, 119291.11, 'ddd')""" - qt_sql """execute stmt2 using (1237, 120939.11130, 'a ddd')""" + // sql """prepare stmt2 from select * from ${tableName} where k1 = % and k2 = % and k3 = %""" + // qt_sql """execute stmt2 using (1231, 119291.11, 'ddd')""" + // qt_sql """execute stmt2 using (1237, 120939.11130, 'a ddd')""" tableName = "test_query_cluster_key" sql """DROP TABLE IF EXISTS ${tableName}""" sql """CREATE TABLE ${tableName} ( diff --git a/regression-test/suites/prepared_stmt_p0/prepared_stmt.groovy b/regression-test/suites/prepared_stmt_p0/prepared_stmt.groovy new file mode 100644 index 00000000000..bf8959e5473 --- /dev/null +++ b/regression-test/suites/prepared_stmt_p0/prepared_stmt.groovy @@ -0,0 +1,170 @@ +// 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. + +import java.math.BigDecimal; + +suite("test_prepared_stmt", "nonConcurrent") { + def tableName = "tbl_prepared_stmt" + def user = context.config.jdbcUser + def password = context.config.jdbcPassword + def url = context.config.jdbcUrl + "&useServerPrepStmts=true" + // def url = context.config.jdbcUrl + def result1 = connect(user=user, password=password, url=url) { + sql "set global enable_server_side_prepared_statement = true" + def insert_prepared = { stmt, k1 , k2, k3, k4, k5, k6, k7, k8, k9 -> + java.text.SimpleDateFormat formater = new java.text.SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); + if (k1 == null) { + stmt.setNull(1, java.sql.Types.INTEGER); + } else { + stmt.setInt(1, k1) + } + if (k2 == null) { + stmt.setNull(2, java.sql.Types.DECIMAL); + } else { + stmt.setBigDecimal(2, k2) + } + if (k3 == null) { + stmt.setNull(3, java.sql.Types.VARCHAR); + } else { + stmt.setString(3, k3) + } + if (k4 == null) { + stmt.setNull(4, java.sql.Types.VARCHAR); + } else { + stmt.setString(4, k4) + } + if (k5 == null) { + stmt.setNull(5, java.sql.Types.DATE); + } else { + stmt.setDate(5, java.sql.Date.valueOf(k5)) + } + if (k6 == null) { + stmt.setNull(6, java.sql.Types.TIMESTAMP); + } else { + stmt.setTimestamp(6, new java.sql.Timestamp(formater.parse(k6).getTime())) + } + if (k7 == null) { + stmt.setNull(7, java.sql.Types.FLOAT); + } else { + stmt.setFloat(7, k7) + } + if (k8 == null) { + stmt.setNull(8, java.sql.Types.DATE); + } else { + stmt.setTimestamp(8, new java.sql.Timestamp(formater.parse(k8).getTime())) + } + if (k9 == null) { + stmt.setNull(9, java.sql.Types.VARCHAR); + } else { + stmt.setString(9, k9) + } + exec stmt + } + + sql """DROP TABLE IF EXISTS ${tableName} """ + sql """ + CREATE TABLE IF NOT EXISTS ${tableName} ( + `k1` int(11) NULL COMMENT "", + `k2` decimalv3(27, 9) NULL COMMENT "", + `k3` varchar(30) NULL COMMENT "", + `k4` varchar(30) NULL COMMENT "", + `k5` date NULL COMMENT "", + `k6` datetime NULL COMMENT "", + `k7` float NULL COMMENT "", + `k8` datev2 NULL COMMENT "", + `k9` array<datetime> NULL COMMENT "" + ) ENGINE=OLAP + DUPLICATE KEY(`k1`, `k2`, `k3`) + DISTRIBUTED BY HASH(`k1`, k2, k3) BUCKETS 1 + PROPERTIES ( + "replication_allocation" = "tag.location.default: 1", + "light_schema_change" = "true", + "storage_format" = "V2" + ) + """ + + def insert_stmt = prepareStatement """ INSERT INTO ${tableName} VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?) """ + assertEquals(insert_stmt.class, com.mysql.cj.jdbc.ServerPreparedStatement); + insert_prepared insert_stmt, 1231, 119291.11, "ddd", "laooq", null, "2020-01-01 12:36:38", null, "1022-01-01 11:30:38", "[2022-01-01 11:30:38, 2022-01-01 11:30:38, 2022-01-01 11:30:38]" + insert_prepared insert_stmt, 1232, 12222.99121135, "xxx", "laooq", "2023-01-02", "2020-01-01 12:36:38", 522.762, "2022-01-01 11:30:38", "[2023-01-01 11:30:38, 2023-01-01 11:30:38]" + insert_prepared insert_stmt, 1233, 1.392932911136, "yyy", "laooq", "2024-01-02", "2020-01-01 12:36:38", 52.862, "3022-01-01 11:30:38", "[2024-01-01 11:30:38, 2024-01-01 11:30:38, 2024-01-01 11:30:38]" + insert_prepared insert_stmt, 1234, 12919291.129191137, "xxddd", "laooq", "2025-01-02", "2020-01-01 12:36:38", 552.872, "4022-01-01 11:30:38", "[2025-01-01 11:30:38, 2025-01-01 11:30:38, 2025-01-01 11:30:38]" + insert_prepared insert_stmt, 1235, 991129292901.11138, "dd", null, "2120-01-02", "2020-01-01 12:36:38", 652.692, "5022-01-01 11:30:38", "[]" + insert_prepared insert_stmt, 1236, 100320.11139, "laa ddd", "laooq", "2220-01-02", "2020-01-01 12:36:38", 2.7692, "6022-01-01 11:30:38", "[null]" + insert_prepared insert_stmt, 1237, 120939.11130, "a ddd", "laooq", "2030-01-02", "2020-01-01 12:36:38", 22.822, "7022-01-01 11:30:38", "[2025-01-01 11:30:38]" + + qt_sql """select * from ${tableName} order by 1, 2, 3""" + qt_sql """select * from ${tableName} order by 1, 2, 3""" + + def stmt_read = prepareStatement "select * from ${tableName} where k1 = ? order by k1" + assertEquals(stmt_read.class, com.mysql.cj.jdbc.ServerPreparedStatement); + stmt_read.setInt(1, 1231) + qe_select0 stmt_read + stmt_read.setInt(1, 1232) + qe_select0 stmt_read + qe_select0 stmt_read + def stmt_read1 = prepareStatement "select hex(k3), ? from ${tableName} where k1 = ? order by 1" + assertEquals(stmt_read1.class, com.mysql.cj.jdbc.ServerPreparedStatement); + stmt_read1.setString(1, "xxxx---") + stmt_read1.setInt(2, 1231) + qe_select1 stmt_read1 + stmt_read1.setString(1, "yyyy---") + stmt_read1.setInt(2, 1232) + qe_select1 stmt_read1 + qe_select1 stmt_read1 + def stmt_read2 = prepareStatement "select * from ${tableName} as t1 join ${tableName} as t2 on t1.`k1` = t2.`k1` where t1.`k1` >= ? and t1.`k2` >= ? and size(t1.`k9`) > ? order by 1, 2, 3" + assertEquals(stmt_read2.class, com.mysql.cj.jdbc.ServerPreparedStatement); + stmt_read2.setInt(1, 1237) + stmt_read2.setBigDecimal(2, new BigDecimal("120939.11130")) + stmt_read2.setInt(3, 0) + qe_select2 stmt_read2 + qe_select2 stmt_read2 + qe_select2 stmt_read2 + + sql "DROP TABLE IF EXISTS mytable1" + sql """ + CREATE TABLE mytable1 + ( + siteid INT DEFAULT '10', + citycode SMALLINT, + username VARCHAR(32) DEFAULT '', + pv BIGINT SUM DEFAULT '0' + ) + AGGREGATE KEY(siteid, citycode, username) + DISTRIBUTED BY HASH(siteid) BUCKETS 10 + PROPERTIES("replication_num" = "1"); + """ + + sql """insert into mytable1 values(1,1,'user1',10);""" + sql """insert into mytable1 values(1,1,'user1',10);""" + sql """insert into mytable1 values(1,1,'user1',10);""" + stmt_read = prepareStatement "SELECT *, ? FROM (select *, ? from mytable1 where citycode = ?) AS `SpotfireCustomQuery1` WHERE 1 = 1" + stmt_read.setInt(1, 12345) + stmt_read.setInt(2, 1234) + stmt_read.setInt(3, 1) + qe_select3 stmt_read + + stmt_read = prepareStatement "SELECT 10" + assertEquals(stmt_read.class, com.mysql.cj.jdbc.ServerPreparedStatement); + qe_select4 stmt_read + stmt_read = prepareStatement "SELECT 1" + assertEquals(stmt_read.class, com.mysql.cj.jdbc.ServerPreparedStatement); + qe_select5 stmt_read + } + + sql "set global enable_server_side_prepared_statement = false" +} --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
