This is an automated email from the ASF dual-hosted git repository.
yiguolei pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/doris.git
The following commit(s) were added to refs/heads/master by this push:
new b74d0a4747 [feature](table-valued-function) Support `desc from s3()`
and modify the syntax of tvf (#14047)
b74d0a4747 is described below
commit b74d0a4747ef77d589295d9516d180f33b5bfcec
Author: Tiewei Fang <[email protected]>
AuthorDate: Wed Nov 9 14:12:43 2022 +0800
[feature](table-valued-function) Support `desc from s3()` and modify the
syntax of tvf (#14047)
This pr does two things:
Support desc function s3()
modify the syntax of tvf
---
.../sql-functions/table-functions/numbers.md | 11 +-
.../sql-functions/table-functions/numbers.md | 11 +-
fe/fe-core/src/main/cup/sql_parser.cup | 8 +-
.../org/apache/doris/analysis/DescribeStmt.java | 46 +++++--
.../doris/analysis/TableValuedFunctionRef.java | 4 +-
.../tablefunction/NumbersTableValuedFunction.java | 45 +++++--
.../doris/tablefunction/S3TableValuedFunction.java | 39 ++++--
.../doris/tablefunction/TableValuedFunctionIf.java | 4 +-
.../table_valued_function/test_numbers.groovy | 134 ++++++++++-----------
.../http_rest_api/post/test_query_stmt.groovy | 2 +-
.../datetime_functions/test_date_function.groovy | 2 +-
11 files changed, 188 insertions(+), 118 deletions(-)
diff --git a/docs/en/docs/sql-manual/sql-functions/table-functions/numbers.md
b/docs/en/docs/sql-manual/sql-functions/table-functions/numbers.md
index ca0f5de915..2d7d034cf3 100644
--- a/docs/en/docs/sql-manual/sql-functions/table-functions/numbers.md
+++ b/docs/en/docs/sql-manual/sql-functions/table-functions/numbers.md
@@ -34,16 +34,19 @@ This function is used in FROM clauses.
grammar:
```
-FROM numbers(n[,m]);
+numbers(
+ "number" = "n",
+ "backend_num" = "m"
+ );
```
parameter：
-- `n`: It means to generate rows [0, n).
-- `m`: Optional parameters. It means this function is executed simultaneously
on `m` be nodes (multiple BEs need to be deployed).
+- `number`: It means to generate rows [0, n).
+- `backend_num`: Optional parameters. It means this function is executed
simultaneously on `m` be nodes (multiple BEs need to be deployed).
### example
```
-mysql> select * from numbers("5");
+mysql> select * from numbers("number" = "10");
+--------+
| number |
+--------+
diff --git
a/docs/zh-CN/docs/sql-manual/sql-functions/table-functions/numbers.md
b/docs/zh-CN/docs/sql-manual/sql-functions/table-functions/numbers.md
index 60605f7282..a5a0162ed4 100644
--- a/docs/zh-CN/docs/sql-manual/sql-functions/table-functions/numbers.md
+++ b/docs/zh-CN/docs/sql-manual/sql-functions/table-functions/numbers.md
@@ -35,16 +35,19 @@ under the License.
语法：
```
-FROM numbers(n[,m]);
+numbers(
+ "number" = "n",
+ "backend_num" = "m"
+ );
```
参数：
-- `n`: 代表生成[0,n)的行。
-- `m`: 可选参数,代表`m`个be节点同时执行该函数（需要部署多个be）。
+- `number`: 代表生成[0,n)的行。
+- `backend_num`: 可选参数,代表`m`个be节点同时执行该函数（需要部署多个be）。
### example
```
-mysql> select * from numbers("10");
+mysql> select * from numbers("number" = "10");
+--------+
| number |
+--------+
diff --git a/fe/fe-core/src/main/cup/sql_parser.cup
b/fe/fe-core/src/main/cup/sql_parser.cup
index adec23db2c..587b7f865d 100644
--- a/fe/fe-core/src/main/cup/sql_parser.cup
+++ b/fe/fe-core/src/main/cup/sql_parser.cup
@@ -3818,6 +3818,10 @@ describe_stmt ::=
{:
RESULT = new DescribeStmt(table, false);
:}
+ | describe_command KW_FUNCTION table_valued_function_ref:tvf
+ {:
+ RESULT = new DescribeStmt(tvf);
+ :}
| describe_command table_name:table KW_ALL
{:
RESULT = new DescribeStmt(table, true);
@@ -4962,9 +4966,9 @@ table_ref ::=
;
table_valued_function_ref ::=
- ident:func_name LPAREN string_list:param_list RPAREN opt_table_alias:alias
+ ident:func_name LPAREN key_value_map:properties RPAREN opt_table_alias:alias
{:
- RESULT = new TableValuedFunctionRef(func_name, alias, param_list);
+ RESULT = new TableValuedFunctionRef(func_name, alias, properties);
:}
;
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/analysis/DescribeStmt.java
b/fe/fe-core/src/main/java/org/apache/doris/analysis/DescribeStmt.java
index 8e481da21c..c76db30c8a 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/analysis/DescribeStmt.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/analysis/DescribeStmt.java
@@ -33,8 +33,8 @@ import org.apache.doris.common.ErrorCode;
import org.apache.doris.common.ErrorReport;
import org.apache.doris.common.FeConstants;
import org.apache.doris.common.UserException;
+import org.apache.doris.common.proc.IndexSchemaProcNode;
import org.apache.doris.common.proc.ProcNodeInterface;
-import org.apache.doris.common.proc.ProcResult;
import org.apache.doris.common.proc.ProcService;
import org.apache.doris.common.proc.TableProcDir;
import org.apache.doris.common.util.Util;
@@ -84,23 +84,48 @@ public class DescribeStmt extends ShowStmt {
private TableName dbTableName;
private ProcNodeInterface node;
- List<List<String>> totalRows;
+ List<List<String>> totalRows = new LinkedList<List<String>>();
private boolean isAllTables;
- private boolean isOlapTable;
+ private boolean isOlapTable = false;
+
+ TableValuedFunctionRef tableValuedFunctionRef;
+ boolean isTableValuedFunction;
public DescribeStmt(TableName dbTableName, boolean isAllTables) {
this.dbTableName = dbTableName;
- this.totalRows = new LinkedList<List<String>>();
this.isAllTables = isAllTables;
}
+ public DescribeStmt(TableValuedFunctionRef tableValuedFunctionRef) {
+ this.tableValuedFunctionRef = tableValuedFunctionRef;
+ this.isTableValuedFunction = true;
+ this.isAllTables = false;
+ }
+
public boolean isAllTables() {
return isAllTables;
}
@Override
public void analyze(Analyzer analyzer) throws UserException {
+ if (!isAllTables && isTableValuedFunction) {
+ List<Column> columns =
tableValuedFunctionRef.getTableFunction().getTable().getBaseSchema();
+ for (Column column : columns) {
+ List<String> row = Arrays.asList(
+ column.getDisplayName(),
+ column.getOriginType().toString(),
+ column.isAllowNull() ? "Yes" : "No",
+ ((Boolean) column.isKey()).toString(),
+ column.getDefaultValue() == null
+ ? FeConstants.null_string :
column.getDefaultValue(),
+ "NONE"
+ );
+ totalRows.add(row);
+ }
+ return;
+ }
+
dbTableName.analyze(analyzer);
if (!Env.getCurrentEnv().getAuth().checkTblPriv(ConnectContext.get(),
dbTableName, PrivPredicate.SHOW)) {
@@ -241,6 +266,9 @@ public class DescribeStmt extends ShowStmt {
if (isAllTables) {
return totalRows;
} else {
+ if (isTableValuedFunction) {
+ return totalRows;
+ }
Preconditions.checkNotNull(node);
return node.fetchResult().getRows();
}
@@ -250,15 +278,7 @@ public class DescribeStmt extends ShowStmt {
public ShowResultSetMetaData getMetaData() {
if (!isAllTables) {
ShowResultSetMetaData.Builder builder =
ShowResultSetMetaData.builder();
-
- ProcResult result = null;
- try {
- result = node.fetchResult();
- } catch (AnalysisException e) {
- return builder.build();
- }
-
- for (String col : result.getColumnNames()) {
+ for (String col : IndexSchemaProcNode.TITLE_NAMES) {
builder.addColumn(new Column(col,
ScalarType.createVarchar(30)));
}
return builder.build();
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/analysis/TableValuedFunctionRef.java
b/fe/fe-core/src/main/java/org/apache/doris/analysis/TableValuedFunctionRef.java
index a697e68fef..328b1819fa 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/analysis/TableValuedFunctionRef.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/analysis/TableValuedFunctionRef.java
@@ -23,14 +23,14 @@ import org.apache.doris.planner.PlanNodeId;
import org.apache.doris.planner.ScanNode;
import org.apache.doris.tablefunction.TableValuedFunctionIf;
-import java.util.List;
+import java.util.Map;
public class TableValuedFunctionRef extends TableRef {
private Table table;
private TableValuedFunctionIf tableFunction;
- public TableValuedFunctionRef(String funcName, String alias, List<String>
params) throws UserException {
+ public TableValuedFunctionRef(String funcName, String alias, Map<String,
String> params) throws UserException {
super(new TableName(null, null, "_table_valued_function_" + funcName),
alias);
this.tableFunction = TableValuedFunctionIf.getTableFunction(funcName,
params);
if (hasExplicitAlias()) {
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/tablefunction/NumbersTableValuedFunction.java
b/fe/fe-core/src/main/java/org/apache/doris/tablefunction/NumbersTableValuedFunction.java
index 3be38e7a0f..5b334dbda9 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/tablefunction/NumbersTableValuedFunction.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/tablefunction/NumbersTableValuedFunction.java
@@ -32,20 +32,30 @@ import org.apache.doris.thrift.TDataGenScanRange;
import org.apache.doris.thrift.TScanRange;
import org.apache.doris.thrift.TTVFNumbersScanRange;
+import com.google.common.base.Strings;
+import com.google.common.collect.ImmutableSet;
import com.google.common.collect.Lists;
+import com.google.common.collect.Maps;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;
+import java.util.Map;
// Table function that generate int64 numbers
// have a single column number
/**
- * The Implement of table valued function——numbers(N,M).
+ * The Implement of table valued function——numbers("number" = "N",
"backend_num" = "M").
*/
public class NumbersTableValuedFunction extends DataGenTableValuedFunction {
public static final String NAME = "numbers";
+ public static final String NUMBER = "number";
+ public static final String BACKEND_NUM = "backend_num";
+ private static final ImmutableSet<String> PROPERTIES_SET = new
ImmutableSet.Builder<String>()
+ .add(NUMBER)
+ .add(BACKEND_NUM)
+ .build();
// The total numbers will be generated.
private long totalNumbers;
// The total backends will server it.
@@ -56,17 +66,30 @@ public class NumbersTableValuedFunction extends
DataGenTableValuedFunction {
* @param params params from user
* @throws UserException exception
*/
- public NumbersTableValuedFunction(List<String> params) throws
UserException {
- if (params.size() < 1 || params.size() > 2) {
- throw new UserException(
- "numbers table function only support numbers(10000 /*total
numbers*/)"
- + "or numbers(10000, 2 /*number of tablets to run*/)");
+ public NumbersTableValuedFunction(Map<String, String> params) throws
UserException {
+ Map<String, String> validParams = Maps.newHashMap();
+ for (String key : params.keySet()) {
+ if (!PROPERTIES_SET.contains(key.toLowerCase())) {
+ throw new AnalysisException(key + " is invalid property");
+ }
+ validParams.put(key.toLowerCase(), params.get(key));
+ }
+
+ try {
+ tabletsNum =
Integer.parseInt(validParams.getOrDefault(BACKEND_NUM, "1"));
+ } catch (NumberFormatException e) {
+ throw new UserException("can not parse `backend_num` param to
natural number");
}
- totalNumbers = Long.parseLong(params.get(0));
- // default tabletsNum is 1.
- tabletsNum = 1;
- if (params.size() == 2) {
- tabletsNum = Integer.parseInt(params.get(1));
+ String numberStr = validParams.get(NUMBER);
+ if (!Strings.isNullOrEmpty(numberStr)) {
+ try {
+ totalNumbers = Long.parseLong(numberStr);
+ } catch (NumberFormatException e) {
+ throw new UserException("can not parse `number` param to
natural number");
+ }
+ } else {
+ throw new UserException(
+ "can not find `number` param, please specify `number`,
like: numbers(\"number\" = \"10\")");
}
}
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/tablefunction/S3TableValuedFunction.java
b/fe/fe-core/src/main/java/org/apache/doris/tablefunction/S3TableValuedFunction.java
index 19655fbedd..1a615e787c 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/tablefunction/S3TableValuedFunction.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/tablefunction/S3TableValuedFunction.java
@@ -26,40 +26,55 @@ import org.apache.doris.common.util.S3URI;
import org.apache.doris.thrift.TFileFormatType;
import org.apache.doris.thrift.TFileType;
+import com.google.common.collect.ImmutableSet;
import com.google.common.collect.Maps;
import org.apache.log4j.LogManager;
import org.apache.log4j.Logger;
-import java.util.List;
+import java.util.Map;
/**
- * The Implement of table valued function——S3(path, AK, SK, format).
+ * The Implement of table valued function
+ * S3("uri" = "xxx", "access_key" = "xx", "SECRET_KEY" = "qqq", "FORMAT" =
"csv").
*/
public class S3TableValuedFunction extends ExternalFileTableValuedFunction {
public static final Logger LOG =
LogManager.getLogger(S3TableValuedFunction.class);
public static final String NAME = "s3";
+ public static final String S3_URI = "URI";
public static final String S3_AK = "AWS_ACCESS_KEY";
public static final String S3_SK = "AWS_SECRET_KEY";
public static final String S3_ENDPOINT = "AWS_ENDPOINT";
public static final String S3_REGION = "AWS_REGION";
+ public static final String FORMAT = "FORMAT";
+ private static final String AK = "ACCESS_KEY";
+ private static final String SK = "SECRET_KEY";
+
public static final String USE_PATH_STYLE = "use_path_style";
+ private static final ImmutableSet<String> PROPERTIES_SET = new
ImmutableSet.Builder<String>()
+ .add(S3_URI)
+ .add(AK)
+ .add(SK)
+ .add(FORMAT)
+ .build();
private S3URI s3uri;
private String s3AK;
private String s3SK;
- public S3TableValuedFunction(List<String> params) throws UserException {
- if (params.size() != 4) {
- throw new UserException(
- "s3 table function only support 4 params now: S3(path, AK,
SK, format)");
+ public S3TableValuedFunction(Map<String, String> params) throws
UserException {
+ Map<String, String> validParams = Maps.newHashMap();
+ for (String key : params.keySet()) {
+ if (!PROPERTIES_SET.contains(key.toUpperCase())) {
+ throw new AnalysisException(key + " is invalid property");
+ }
+ validParams.put(key.toUpperCase(), params.get(key));
}
- s3uri = S3URI.create(params.get(0));
- s3AK = params.get(1);
- s3SK = params.get(2);
-
- String formatString = params.get(3).toLowerCase();
- switch (formatString) {
+ s3uri = S3URI.create(validParams.get(S3_URI));
+ s3AK = validParams.getOrDefault(AK, "");
+ s3SK = validParams.getOrDefault(SK, "");
+ String formatString = validParams.getOrDefault(FORMAT, "");
+ switch (formatString.toLowerCase()) {
case "csv":
this.fileFormatType = TFileFormatType.FORMAT_CSV_PLAIN;
break;
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/tablefunction/TableValuedFunctionIf.java
b/fe/fe-core/src/main/java/org/apache/doris/tablefunction/TableValuedFunctionIf.java
index 09ef70ccb5..3063880c92 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/tablefunction/TableValuedFunctionIf.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/tablefunction/TableValuedFunctionIf.java
@@ -27,6 +27,7 @@ import org.apache.doris.planner.PlanNodeId;
import org.apache.doris.planner.ScanNode;
import java.util.List;
+import java.util.Map;
public abstract class TableValuedFunctionIf {
private FunctionGenTable table = null;
@@ -40,7 +41,8 @@ public abstract class TableValuedFunctionIf {
}
// All table functions should be registered here
- public static TableValuedFunctionIf getTableFunction(String funcName,
List<String> params) throws UserException {
+ public static TableValuedFunctionIf getTableFunction(String funcName,
Map<String, String> params)
+ throws UserException {
switch (funcName.toLowerCase()) {
case NumbersTableValuedFunction.NAME:
return new NumbersTableValuedFunction(params);
diff --git
a/regression-test/suites/correctness_p0/table_valued_function/test_numbers.groovy
b/regression-test/suites/correctness_p0/table_valued_function/test_numbers.groovy
index a70e25e6c3..fd2bc9b444 100644
---
a/regression-test/suites/correctness_p0/table_valued_function/test_numbers.groovy
+++
b/regression-test/suites/correctness_p0/table_valued_function/test_numbers.groovy
@@ -18,105 +18,105 @@
suite("test_numbers") {
// Test basic features
- qt_basic1 """ select * from numbers("1"); """
- qt_basic2 """ select * from numbers("10"); """
- qt_basic3 """ select * from numbers("100"); """
- qt_basic4_limit """ select * from numbers("10") limit 5; """
+ qt_basic1 """ select * from numbers("number" = "1"); """
+ qt_basic2 """ select * from numbers("number" = "10"); """
+ qt_basic3 """ select * from numbers("number" = "100"); """
+ qt_basic4_limit """ select * from numbers("number" = "10") limit 5; """
- // Test aggregate function withh numbers(N)
- qt_agg_sum """ select sum(number) from numbers("100"); """
- qt_agg_avg """ select avg(number) from numbers("100"); """
- qt_agg_count """ select count(*) from numbers("100"); """
- qt_agg_min """ select min(number) from numbers("100"); """
- qt_agg_max """ select max(number) from numbers("100"); """
+ // Test aggregate function withh numbers("number" = N)
+ qt_agg_sum """ select sum(number) from numbers("number" = "100"); """
+ qt_agg_avg """ select avg(number) from numbers("number" = "100"); """
+ qt_agg_count """ select count(*) from numbers("number" = "100"); """
+ qt_agg_min """ select min(number) from numbers("number" = "100"); """
+ qt_agg_max """ select max(number) from numbers("number" = "100"); """
- // Test join with numbers(N)
+ // Test join with numbers("number" = N)
qt_inner_join1 """
select a.number as num1, b.number as num2
- from numbers("10") a inner join numbers("10") b
+ from numbers("number" = "10") a inner join
numbers("number" = "10") b
on a.number=b.number;
"""
qt_inner_join2 """
select a.number as num1, b.number as num2
- from numbers("6") a inner join numbers("6") b
+ from numbers("number" = "6") a inner join numbers("number"
= "6") b
on a.number>b.number;
"""
qt_inner_join3 """
select a.number as num1, b.number as num2
- from numbers("10") a inner join numbers("10") b
+ from numbers("number" = "10") a inner join
numbers("number" = "10") b
on a.number=b.number and b.number%2 = 0;
"""
qt_left_join """
select a.number as num1, b.number as num2
- from numbers("10") a left join numbers("5") b
+ from numbers("number" = "10") a left join numbers("number"
= "5") b
on a.number=b.number order by num1;
"""
qt_right_join """
select a.number as num1, b.number as num2
- from numbers("5") a right join numbers("10") b
+ from numbers("number" = "5") a right join numbers("number"
= "10") b
on a.number=b.number order by num2;
"""
// Test where and GroupBy
- qt_where_equal """ select * from numbers("10") where number%2 = 1; """
- qt_where_gt """ select * from numbers("10") where number-1 > 1; """
- qt_where_lt """ select * from numbers("10") where number+1 < 9; """
- qt_groupby """ select number from numbers("10") where number>=4 group by
number order by number; """
+ qt_where_equal """ select * from numbers("number" = "10") where number%2 =
1; """
+ qt_where_gt """ select * from numbers("number" = "10") where number-1 > 1;
"""
+ qt_where_lt """ select * from numbers("number" = "10") where number+1 < 9;
"""
+ qt_groupby """ select number from numbers("number" = "10") where number>=4
group by number order by number; """
qt_join_where """
select a.number as num1, b.number as num2
- from numbers("10") a inner join numbers("10") b
+ from numbers("number" = "10") a inner join
numbers("number" = "10") b
on a.number=b.number where a.number>4;
"""
// Test Sub Query
- qt_subquery1 """ select * from numbers("10") where number = (select number
from numbers("10") where number=1); """
- qt_subquery2 """ select * from numbers("10") where number in (select
number from numbers("10") where number>5); """
- qt_subquery3 """ select a.number from numbers("10") a where number in
(select number from numbers("10") b where a.number=b.number); """
+ qt_subquery1 """ select * from numbers("number" = "10") where number =
(select number from numbers("number" = "10") where number=1); """
+ qt_subquery2 """ select * from numbers("number" = "10") where number in
(select number from numbers("number" = "10") where number>5); """
+ qt_subquery3 """ select a.number from numbers("number" = "10") a where
number in (select number from numbers("number" = "10") b where
a.number=b.number); """
// Test window function
- qt_window_1 """ SELECT row_number() OVER (ORDER BY number) AS id,number
from numbers("10"); """
- qt_window_2 """ SELECT number, rank() OVER (order by number) AS sum_three
from numbers("10"); """
- qt_window_3 """ SELECT number, dense_rank() OVER (order by number) AS
sum_three from numbers("10"); """
- qt_window_4 """ SELECT number, sum(number) OVER (ORDER BY number rows
between 1 preceding and 1 following) AS result from numbers("10"); """
- qt_window_5 """ SELECT number, min(number) OVER (ORDER BY number rows
between 1 PRECEDING and 1 following) AS result from numbers("10"); """
- qt_window_6 """ SELECT number, min(number) OVER (ORDER BY number rows
between UNBOUNDED PRECEDING and 1 following) AS result from numbers("10"); """
- qt_window_7 """ SELECT number, max(number) OVER (ORDER BY number rows
between 1 preceding and 1 following) AS result from numbers("10"); """
- qt_window_8 """ SELECT number, max(number) OVER (ORDER BY number rows
between UNBOUNDED PRECEDING and 1 following) AS result from numbers("10"); """
- qt_window_9 """ SELECT number, avg(number) OVER (ORDER BY number rows
between 1 preceding and 1 following) AS result from numbers("10"); """
- qt_window_10 """ SELECT number, count(number) OVER (ORDER BY number rows
between 1 preceding and 1 following) AS result from numbers("10"); """
- qt_window_11 """ SELECT number, first_value(number) OVER (ORDER BY number
rows between 1 preceding and 1 following) AS result from numbers("10"); """
- qt_window_12 """ SELECT number, last_value(number) OVER (ORDER BY number
rows between 1 preceding and 1 following) AS result from numbers("10"); """
- qt_window_13 """ SELECT number, LAG(number,2,-1) OVER (ORDER BY number) AS
result from numbers("10"); """
+ qt_window_1 """ SELECT row_number() OVER (ORDER BY number) AS id,number
from numbers("number" = "10"); """
+ qt_window_2 """ SELECT number, rank() OVER (order by number) AS sum_three
from numbers("number" = "10"); """
+ qt_window_3 """ SELECT number, dense_rank() OVER (order by number) AS
sum_three from numbers("number" = "10"); """
+ qt_window_4 """ SELECT number, sum(number) OVER (ORDER BY number rows
between 1 preceding and 1 following) AS result from numbers("number" = "10");
"""
+ qt_window_5 """ SELECT number, min(number) OVER (ORDER BY number rows
between 1 PRECEDING and 1 following) AS result from numbers("number" = "10");
"""
+ qt_window_6 """ SELECT number, min(number) OVER (ORDER BY number rows
between UNBOUNDED PRECEDING and 1 following) AS result from numbers("number" =
"10"); """
+ qt_window_7 """ SELECT number, max(number) OVER (ORDER BY number rows
between 1 preceding and 1 following) AS result from numbers("number" = "10");
"""
+ qt_window_8 """ SELECT number, max(number) OVER (ORDER BY number rows
between UNBOUNDED PRECEDING and 1 following) AS result from numbers("number" =
"10"); """
+ qt_window_9 """ SELECT number, avg(number) OVER (ORDER BY number rows
between 1 preceding and 1 following) AS result from numbers("number" = "10");
"""
+ qt_window_10 """ SELECT number, count(number) OVER (ORDER BY number rows
between 1 preceding and 1 following) AS result from numbers("number" = "10");
"""
+ qt_window_11 """ SELECT number, first_value(number) OVER (ORDER BY number
rows between 1 preceding and 1 following) AS result from numbers("number" =
"10"); """
+ qt_window_12 """ SELECT number, last_value(number) OVER (ORDER BY number
rows between 1 preceding and 1 following) AS result from numbers("number" =
"10"); """
+ qt_window_13 """ SELECT number, LAG(number,2,-1) OVER (ORDER BY number) AS
result from numbers("number" = "10"); """
// Cast BITINT to STRING and test string function.
- qt_stringfunction_1 """ select cast (number as string) as string_num from
numbers("10"); """
- qt_stringfunction_2 """ select append_trailing_char_if_absent(cast (number
as string),'a') as string_fucntion_res from numbers("10"); """
- qt_stringfunction_3 """ select concat(cast (number as string),'abc','d')
as string_fucntion_res from numbers("10"); """
- qt_stringfunction_4 """ select concat(cast (number as string), cast
(number as string)) as string_fucntion_res from numbers("10"); """
- qt_stringfunction_5 """ select ascii(cast (number as string)) as
string_fucntion_res from numbers("12"); """
- qt_stringfunction_6 """ select bit_length(cast (number as string)) as
string_fucntion_res from numbers("14") where number>5; """
- qt_stringfunction_7 """ select char_length(cast (number as string)) as
string_fucntion_res from numbers("14") where number>5; """
- qt_stringfunction_8 """ select concat_ws('-',cast (number as string),'a')
as string_fucntion_res from numbers("14") where number>5; """
- qt_stringfunction_9 """ select number, ends_with(cast (number as
string),'1') as string_fucntion_res from numbers("12"); """
- qt_stringfunction_10 """ select number,find_in_set(cast (number as
string),'0,1,2,3,4,5,6,7') as string_fucntion_res from numbers("10"); """
- qt_stringfunction_11 """ select number,hex(number) as string_fucntion_res
from numbers("13") where number>5; """
- qt_stringfunction_12 """ select number,hex(cast (number as string)) as
string_fucntion_res from numbers("13") where number>5; """
- qt_stringfunction_13 """ select number,instr(cast (number as string),'1')
as string_fucntion_res from numbers("13") where number>5; """
- qt_stringfunction_14 """ select number,left(cast (number as string),'2')
as string_fucntion_res from numbers("1000") where number>120 limit 10; """
- qt_stringfunction_15 """ select number,length(cast (number as string)) as
string_fucntion_res from numbers("1000") where number>120 limit 10; """
- qt_stringfunction_16 """ select number,locate('2',cast (number as string))
as string_fucntion_res from numbers("1000") where number>120 limit 10; """
- qt_stringfunction_17 """ select number,locate('2',cast (number as
string),3) as string_fucntion_res from numbers("1000") where number>120 limit
10; """
- qt_stringfunction_18 """ select number,lpad(cast (number as string),3,'0')
as string_fucntion_res from numbers("1000") where number>95 limit 15; """
- qt_stringfunction_19 """ select ltrim( concat(' a',cast (number as
string))) as string_fucntion_res from numbers("10"); """
- qt_stringfunction_20 """ select repeat(cast (number as string),2) as
string_fucntion_res from numbers("13"); """
- qt_stringfunction_21 """ select replace(cast (number as string),'1','a')
as string_fucntion_res from numbers("13"); """
- qt_stringfunction_22 """ select reverse(cast (number as string)) as
string_fucntion_res from numbers("20") where number>9; """
- qt_stringfunction_23 """ select right(cast (number as string),1) as
string_fucntion_res from numbers("20") where number>9; """
- qt_stringfunction_24 """ select number,rpad(cast (number as string),3,'0')
as string_fucntion_res from numbers("1000") where number>95 limit 15; """
- qt_stringfunction_25 """ select STARTS_WITH(cast (number as string),'1')
as string_fucntion_res from numbers("15"); """
- qt_stringfunction_26 """ select strleft(cast (number as string),'2') as
string_fucntion_res from numbers("200") where number>105 limit 10; """
- qt_stringfunction_27 """ select strright(cast (number as string),'2') as
string_fucntion_res from numbers("1000") where number>105 limit 10; """
- qt_stringfunction_28 """ select substring(cast (number as string),2) as
string_fucntion_res from numbers("1000") where number>105 limit 10; """
- qt_stringfunction_29 """ select substring(cast (number as string),-1) as
string_fucntion_res from numbers("1000") where number>105 limit 10; """
- qt_stringfunction_30 """ select number,unhex(cast (number as string)) as
string_fucntion_res from numbers("100") limit 30; """
+ qt_stringfunction_1 """ select cast (number as string) as string_num from
numbers("number" = "10"); """
+ qt_stringfunction_2 """ select append_trailing_char_if_absent(cast (number
as string),'a') as string_fucntion_res from numbers("number" = "10"); """
+ qt_stringfunction_3 """ select concat(cast (number as string),'abc','d')
as string_fucntion_res from numbers("number" = "10"); """
+ qt_stringfunction_4 """ select concat(cast (number as string), cast
(number as string)) as string_fucntion_res from numbers("number" = "10"); """
+ qt_stringfunction_5 """ select ascii(cast (number as string)) as
string_fucntion_res from numbers("number" = "12"); """
+ qt_stringfunction_6 """ select bit_length(cast (number as string)) as
string_fucntion_res from numbers("number" = "14") where number>5; """
+ qt_stringfunction_7 """ select char_length(cast (number as string)) as
string_fucntion_res from numbers("number" = "14") where number>5; """
+ qt_stringfunction_8 """ select concat_ws('-',cast (number as string),'a')
as string_fucntion_res from numbers("number" = "14") where number>5; """
+ qt_stringfunction_9 """ select number, ends_with(cast (number as
string),'1') as string_fucntion_res from numbers("number" = "12"); """
+ qt_stringfunction_10 """ select number,find_in_set(cast (number as
string),'0,1,2,3,4,5,6,7') as string_fucntion_res from numbers("number" =
"10"); """
+ qt_stringfunction_11 """ select number,hex(number) as string_fucntion_res
from numbers("number" = "13") where number>5; """
+ qt_stringfunction_12 """ select number,hex(cast (number as string)) as
string_fucntion_res from numbers("number" = "13") where number>5; """
+ qt_stringfunction_13 """ select number,instr(cast (number as string),'1')
as string_fucntion_res from numbers("number" = "13") where number>5; """
+ qt_stringfunction_14 """ select number,left(cast (number as string),'2')
as string_fucntion_res from numbers("number" = "1000") where number>120 limit
10; """
+ qt_stringfunction_15 """ select number,length(cast (number as string)) as
string_fucntion_res from numbers("number" = "1000") where number>120 limit 10;
"""
+ qt_stringfunction_16 """ select number,locate('2',cast (number as string))
as string_fucntion_res from numbers("number" = "1000") where number>120 limit
10; """
+ qt_stringfunction_17 """ select number,locate('2',cast (number as
string),3) as string_fucntion_res from numbers("number" = "1000") where
number>120 limit 10; """
+ qt_stringfunction_18 """ select number,lpad(cast (number as string),3,'0')
as string_fucntion_res from numbers("number" = "1000") where number>95 limit
15; """
+ qt_stringfunction_19 """ select ltrim( concat(' a',cast (number as
string))) as string_fucntion_res from numbers("number" = "10"); """
+ qt_stringfunction_20 """ select repeat(cast (number as string),2) as
string_fucntion_res from numbers("number" = "13"); """
+ qt_stringfunction_21 """ select replace(cast (number as string),'1','a')
as string_fucntion_res from numbers("number" = "13"); """
+ qt_stringfunction_22 """ select reverse(cast (number as string)) as
string_fucntion_res from numbers("number" = "20") where number>9; """
+ qt_stringfunction_23 """ select right(cast (number as string),1) as
string_fucntion_res from numbers("number" = "20") where number>9; """
+ qt_stringfunction_24 """ select number,rpad(cast (number as string),3,'0')
as string_fucntion_res from numbers("number" = "1000") where number>95 limit
15; """
+ qt_stringfunction_25 """ select STARTS_WITH(cast (number as string),'1')
as string_fucntion_res from numbers("number" = "15"); """
+ qt_stringfunction_26 """ select strleft(cast (number as string),'2') as
string_fucntion_res from numbers("number" = "200") where number>105 limit 10;
"""
+ qt_stringfunction_27 """ select strright(cast (number as string),'2') as
string_fucntion_res from numbers("number" = "1000") where number>105 limit 10;
"""
+ qt_stringfunction_28 """ select substring(cast (number as string),2) as
string_fucntion_res from numbers("number" = "1000") where number>105 limit 10;
"""
+ qt_stringfunction_29 """ select substring(cast (number as string),-1) as
string_fucntion_res from numbers("number" = "1000") where number>105 limit 10;
"""
+ qt_stringfunction_30 """ select number,unhex(cast (number as string)) as
string_fucntion_res from numbers("number" = "100") limit 30; """
}
diff --git a/regression-test/suites/http_rest_api/post/test_query_stmt.groovy
b/regression-test/suites/http_rest_api/post/test_query_stmt.groovy
index 00bc133b6b..7a03cdc48b 100644
--- a/regression-test/suites/http_rest_api/post/test_query_stmt.groovy
+++ b/regression-test/suites/http_rest_api/post/test_query_stmt.groovy
@@ -49,7 +49,7 @@ suite("test_query_stmt") {
def url= "/api/query/default_cluster/" + context.config.defaultDb
// test select
- def stmt1 = """ select * from numbers('10', '1') """
+ def stmt1 = """ select * from numbers('number' = '10', 'backend_num' =
'1') """
def stmt1_json = JsonOutput.toJson(new Stmt(stmt: stmt1));
def resJson = http_post(url, stmt1_json)
diff --git
a/regression-test/suites/query_p0/sql_functions/datetime_functions/test_date_function.groovy
b/regression-test/suites/query_p0/sql_functions/datetime_functions/test_date_function.groovy
index b63de704e1..e9544a3983 100644
---
a/regression-test/suites/query_p0/sql_functions/datetime_functions/test_date_function.groovy
+++
b/regression-test/suites/query_p0/sql_functions/datetime_functions/test_date_function.groovy
@@ -263,7 +263,7 @@ suite("test_date_function") {
qt_sql """ select yearweek('1989-03-21', 6) """
qt_sql """ select yearweek('1989-03-21', 7) """
- qt_sql """ select count(*) from (select * from numbers("200")) tmp1 WHERE
0 <= UNIX_TIMESTAMP(); """
+ qt_sql """ select count(*) from (select * from numbers("number" = "200"))
tmp1 WHERE 0 <= UNIX_TIMESTAMP(); """
sql """ drop table ${tableName} """
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
