361 lines
8.1 KiB
Plaintext
361 lines
8.1 KiB
Plaintext
## Find with groupby and having clause
|
|
|
|
--source include/xplugin_preamble.inc
|
|
--source include/xplugin_create_user.inc
|
|
|
|
|
|
## Test starts here
|
|
--let $xtest_file= $MYSQL_TMP_DIR/crud_find_groupby.tmp
|
|
--write_file $xtest_file
|
|
-->quiet
|
|
|
|
-->stmtadmin create_collection {"schema":"xtest","name":"coll1"}
|
|
-->recvresult
|
|
|
|
INSERT INTO xtest.coll1 (doc) VALUES
|
|
('{"F1": 1, "F2": 110, "_id": "1100"}'),
|
|
('{"F1": 2, "F2": 111, "_id": "1101"}'),
|
|
('{"F1": 3, "F2": 112, "_id": "1102"}'),
|
|
('{"F1": 1, "F2": 120, "_id": "1200"}'),
|
|
('{"F1": 2, "F2": 121, "_id": "1201"}'),
|
|
('{"F1": 3, "F2": 122, "_id": "1202"}');
|
|
|
|
CREATE TABLE xtest.tab1 (id VARCHAR(16), F1 INT, F2 INT)
|
|
SELECT _id AS id,
|
|
JSON_UNQUOTE(JSON_EXTRACT(doc, "$.F1")) AS F1,
|
|
JSON_UNQUOTE(JSON_EXTRACT(doc, "$.F2")) AS F2
|
|
FROM xtest.coll1;
|
|
|
|
-->title =Issue #1
|
|
-->echo Table approach
|
|
Mysqlx.Crud.Find {
|
|
collection { name: "tab1" schema: "xtest"}
|
|
data_model: TABLE
|
|
projection {
|
|
alias: "sum_f2"
|
|
source {
|
|
type: FUNC_CALL function_call {
|
|
name { name: "sum" }
|
|
param { type: IDENT identifier { name: "F2" } }
|
|
}
|
|
}
|
|
}
|
|
projection {
|
|
alias: "sum_f1"
|
|
source {
|
|
type: FUNC_CALL function_call {
|
|
name { name: "sum" }
|
|
param { type: IDENT identifier { name: "F1" } }
|
|
}
|
|
}
|
|
}
|
|
grouping { type: IDENT identifier { name: "F1" } }
|
|
grouping_criteria {
|
|
type: OPERATOR
|
|
operator {
|
|
name: ">"
|
|
param {
|
|
type: FUNC_CALL function_call {
|
|
name { name: "sum" }
|
|
param { type: IDENT identifier { name: "F1" } }
|
|
}
|
|
}
|
|
param {
|
|
type: LITERAL literal { type: V_UINT v_unsigned_int: 2 }
|
|
}
|
|
}
|
|
}
|
|
order {
|
|
expr { type: IDENT identifier { name: "F1" } }
|
|
direction: ASC
|
|
}
|
|
}
|
|
-->recvresult
|
|
|
|
-->echo Collection approach
|
|
Mysqlx.Crud.Find {
|
|
collection { name: "coll1" schema: "xtest"}
|
|
data_model: DOCUMENT
|
|
projection {
|
|
alias: "sum_f2"
|
|
source {
|
|
type: FUNC_CALL function_call {
|
|
name { name: "sum" }
|
|
param {
|
|
type: OPERATOR operator {
|
|
name: "cast"
|
|
param {
|
|
type: IDENT
|
|
identifier { document_path { type: MEMBER value: "F2" } }
|
|
}
|
|
param {
|
|
type : LITERAL
|
|
literal { type: V_OCTETS v_octets {value:"SIGNED INTEGER"} }
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
projection {
|
|
alias: "sum_f1"
|
|
source {
|
|
type: FUNC_CALL function_call {
|
|
name { name: "sum" }
|
|
param {
|
|
type: OPERATOR operator {
|
|
name: "cast"
|
|
param {
|
|
type: IDENT
|
|
identifier { document_path { type: MEMBER value: "F1" } }
|
|
}
|
|
param {
|
|
type : LITERAL
|
|
literal { type: V_OCTETS v_octets {value:"SIGNED INTEGER"} }
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
grouping {
|
|
type: IDENT
|
|
identifier { document_path { type: MEMBER value: "F1" } }
|
|
}
|
|
grouping_criteria {
|
|
type: OPERATOR
|
|
operator {
|
|
name: ">"
|
|
param {
|
|
type: FUNC_CALL function_call {
|
|
name { name: "sum" }
|
|
param {
|
|
type: OPERATOR operator {
|
|
name: "cast"
|
|
param {
|
|
type: IDENT
|
|
identifier { document_path { type: MEMBER value: "F1" } }
|
|
}
|
|
param {
|
|
type : LITERAL
|
|
literal { type: V_OCTETS v_octets {value:"SIGNED INTEGER"} }
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
param {
|
|
type: LITERAL literal { type: V_UINT v_unsigned_int: 2 }
|
|
}
|
|
}
|
|
}
|
|
order {
|
|
expr {
|
|
type: IDENT
|
|
identifier { document_path { type: MEMBER value: "F1" } }
|
|
}
|
|
direction: ASC
|
|
}
|
|
}
|
|
-->recvresult
|
|
|
|
-->title =Issue #2
|
|
-->echo Table approach
|
|
Mysqlx.Crud.Find {
|
|
collection { name: "tab1" schema: "xtest"}
|
|
data_model: TABLE
|
|
projection {
|
|
alias: "Fld"
|
|
source {
|
|
type: FUNC_CALL function_call {
|
|
name { name: "any_value" }
|
|
param { type: IDENT identifier { name: "F1" } }
|
|
}
|
|
}
|
|
}
|
|
projection {
|
|
alias: "sum_f2"
|
|
source {
|
|
type: FUNC_CALL function_call {
|
|
name { name: "sum" }
|
|
param { type: IDENT identifier { name: "F2" } }
|
|
}
|
|
}
|
|
}
|
|
grouping { type: IDENT identifier { name: "F1" } }
|
|
grouping_criteria {
|
|
type: OPERATOR
|
|
operator {
|
|
name: ">"
|
|
param { type: IDENT identifier { name: "F1" } }
|
|
param { type: LITERAL literal { type: V_UINT v_unsigned_int: 2 } }
|
|
}
|
|
}
|
|
order {
|
|
expr { type: IDENT identifier { name: "F1" } }
|
|
direction: ASC
|
|
}
|
|
}
|
|
-->recvresult
|
|
|
|
-->echo Collection approach (doc member in grouping criteria)
|
|
Mysqlx.Crud.Find {
|
|
collection { name: "coll1" schema: "xtest"}
|
|
data_model: DOCUMENT
|
|
projection {
|
|
alias: "Fld"
|
|
source {
|
|
type: FUNC_CALL function_call {
|
|
name { name: "any_value" }
|
|
param {
|
|
type: OPERATOR operator {
|
|
name: "cast"
|
|
param {
|
|
type: IDENT
|
|
identifier { document_path { type: MEMBER value: "F1" } }
|
|
}
|
|
param {
|
|
type : LITERAL
|
|
literal { type: V_OCTETS v_octets {value:"SIGNED INTEGER"} }
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
projection {
|
|
alias: "sum_f2"
|
|
source {
|
|
type: FUNC_CALL function_call {
|
|
name { name: "sum" }
|
|
param {
|
|
type: OPERATOR operator {
|
|
name: "cast"
|
|
param {
|
|
type: IDENT
|
|
identifier { document_path { type: MEMBER value: "F2" } }
|
|
}
|
|
param {
|
|
type : LITERAL
|
|
literal { type: V_OCTETS v_octets {value:"SIGNED INTEGER"} }
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
grouping {
|
|
type: IDENT
|
|
identifier { document_path { type: MEMBER value: "F1" } }
|
|
}
|
|
grouping_criteria {
|
|
type: OPERATOR
|
|
operator {
|
|
name: ">"
|
|
param {
|
|
type: IDENT
|
|
identifier { document_path { type: MEMBER value: "F1" } }
|
|
}
|
|
param {
|
|
type: LITERAL
|
|
literal { type: V_UINT v_unsigned_int: 2 }
|
|
}
|
|
}
|
|
}
|
|
order {
|
|
expr {
|
|
type: IDENT
|
|
identifier { document_path { type: MEMBER value: "F1" } }
|
|
}
|
|
direction: ASC
|
|
}
|
|
}
|
|
-->expecterror ER_X_EXPR_BAD_VALUE
|
|
-->recvresult
|
|
|
|
-->echo Collection approach (alias in grouping criteria)
|
|
Mysqlx.Crud.Find {
|
|
collection { name: "coll1" schema: "xtest"}
|
|
data_model: DOCUMENT
|
|
projection {
|
|
alias: "Fld"
|
|
source {
|
|
type: FUNC_CALL function_call {
|
|
name { name: "any_value" }
|
|
param {
|
|
type: OPERATOR operator {
|
|
name: "cast"
|
|
param {
|
|
type: IDENT
|
|
identifier { document_path { type: MEMBER value: "F1" } }
|
|
}
|
|
param {
|
|
type : LITERAL
|
|
literal { type: V_OCTETS v_octets {value:"SIGNED INTEGER"} }
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
projection {
|
|
alias: "sum_f2"
|
|
source {
|
|
type: FUNC_CALL function_call {
|
|
name { name: "sum" }
|
|
param {
|
|
type: OPERATOR operator {
|
|
name: "cast"
|
|
param {
|
|
type: IDENT
|
|
identifier { document_path { type: MEMBER value: "F2" } }
|
|
}
|
|
param {
|
|
type : LITERAL
|
|
literal { type: V_OCTETS v_octets {value:"SIGNED INTEGER"} }
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
grouping {
|
|
type: IDENT
|
|
identifier { document_path { type: MEMBER value: "F1" } }
|
|
}
|
|
grouping_criteria {
|
|
type: OPERATOR
|
|
operator {
|
|
name: ">"
|
|
param {
|
|
type: IDENT
|
|
identifier { name: "Fld" }
|
|
}
|
|
param {
|
|
type: LITERAL
|
|
literal { type: V_UINT v_unsigned_int: 2 }
|
|
}
|
|
}
|
|
}
|
|
order {
|
|
expr {
|
|
type: IDENT
|
|
identifier { document_path { type: MEMBER value: "F1" } }
|
|
}
|
|
direction: ASC
|
|
}
|
|
}
|
|
-->recvresult
|
|
EOF
|
|
|
|
|
|
CREATE SCHEMA xtest DEFAULT CHARSET 'utf8mb4';
|
|
|
|
--exec $MYSQLXTEST -ux_root --password='' --file=$xtest_file 2>&1
|
|
|
|
## Cleanup
|
|
DROP SCHEMA IF EXISTS xtest;
|
|
--remove_file $xtest_file
|
|
--source include/xplugin_drop_user.inc
|
|
|