4493d40a-92aa-4162-9dfc-bfc4ec0996e6
/
polardbxengine
2
0
Fork 0
Code Issues Pull Requests Packages Projects Releases Wiki Activity
polardbxengine/mysql-test/suite/x/t/crud_table_criteria_args.test

1063 lines
18 KiB
Plaintext
Raw Permalink Blame History

########### ../t/crud_table_criteria_args.test #############
### #
### Manipulating on valid rows by criteria with placeholders #
### Test covers #
### - FIND with different operators #
### - UPDATE (SET) with different operators #
### - DELETE with different operators #
### #
########################################################################
#
--echo =============================================
--echo CRUD TABLE CRITERIA ARGS SCENARIOS
--echo =============================================
--echo
--echo ================================================================================
--echo PREAMBLE
--echo ================================================================================
## Preamble
--source include/xplugin_preamble.inc
--source include/xplugin_create_user.inc
## Test starts here
--echo ================================================================================
--echo TEST START
--echo ================================================================================
--write_file $MYSQL_TMP_DIR/crud_criteria_args.tmp
## Test data
-->sql
drop schema if exists xtest;
create schema xtest;
use xtest;
create table mytable (id int primary key, name varchar(40), price decimal(5,2), info json);
insert into mytable values (1, 'banana', 1.20, '{"color": "yellow"}');
insert into mytable values (2, 'apple', 0.25, '{"color":"red"}');
insert into mytable values (3, 'tomato', 1.80, '{"color":"red"}');
insert into mytable values (4, 'mango', 3.14, '{"color":"green"}');
insert into mytable values (5, 'orange', 0.90, '{"color":"orange"}');
insert into mytable values (6, 'berry', null, '{"color":"orange"}');
SELECT * FROM xtest.mytable ORDER BY price;
-->endsql
-->echo Find with == Operator and placeholder
Mysqlx.Crud.Find {
collection {
name: "mytable"
schema: "xtest"
}
data_model: TABLE
criteria {
type: OPERATOR
operator {
name: "=="
param {
type: IDENT
identifier {
name: "name"
}
}
param {
type: PLACEHOLDER
position: 0
}
}
}
args {
type: V_STRING
v_string{
value: "tomato"
}
}
}
## expect Mysqlx.Sql.StmtExecuteOk
-->recvresult
-->echo Find with != Operator. placeholder and projection
Mysqlx.Crud.Find {
collection {
name: "mytable"
schema: "xtest"
}
data_model: TABLE
projection {
source {
type: IDENT
identifier {
name: "name"
}
}
}
criteria {
type: OPERATOR
operator {
name: "=="
param {
type: IDENT
identifier {
name: "name"
}
}
param {
type: PLACEHOLDER
position: 0
}
}
}
args {
type: V_STRING
v_string{
value: "tomato"
}
}
}
## expect Mysqlx.Sql.StmtExecuteOk
-->recvresult
-->echo Find with != Operator and placeholder
Mysqlx.Crud.Find {
collection {
name: "mytable"
schema: "xtest"
}
data_model: TABLE
criteria {
type: OPERATOR
operator {
name: "!="
param {
type: IDENT
identifier {
name: "name"
}
}
param {
type: PLACEHOLDER
position: 0
}
}
}
args {
type: V_STRING
v_string{
value: "tomato"
}
}
}
## expect Mysqlx.Sql.StmtExecuteOk
-->recvresult
-->echo Find with > Operator and placeholder
Mysqlx.Crud.Find {
collection {
name: "mytable"
schema: "xtest"
}
data_model: TABLE
criteria {
type: OPERATOR
operator {
name: ">"
param {
type: IDENT
identifier {
name: "price"
}
}
param {
type: PLACEHOLDER
position: 0
}
}
}
args {
type: V_DOUBLE
v_double: 1.80
}
}
## expect Mysqlx.Sql.StmtExecuteOk
-->recvresult
-->echo Find with != Operator, placeholder and Order desc
Mysqlx.Crud.Find {
collection {
name: "mytable"
schema: "xtest"
}
data_model: TABLE
criteria {
type: OPERATOR
operator {
name: "!="
param {
type: IDENT
identifier {
name: "price"
}
}
param {
type: PLACEHOLDER
position: 0
}
}
}
args {
type: V_DOUBLE
v_double: 0
}
order {
expr {
type: IDENT
identifier {
name: "name"
}
}
direction: DESC
}
}
## expect Mysqlx.Sql.StmtExecuteOk
-->recvresult
-->echo Find with != Operator, placeholder and Order asc
Mysqlx.Crud.Find {
collection {
name: "mytable"
schema: "xtest"
}
data_model: TABLE
criteria {
type: OPERATOR
operator {
name: "!="
param {
type: IDENT
identifier {
name: "price"
}
}
param {
type: PLACEHOLDER
position: 0
}
}
}
args {
type: V_DOUBLE
v_double: 0
}
order {
expr {
type: IDENT
identifier {
name: "name"
}
}
direction: ASC
}
}
## expect Mysqlx.Sql.StmtExecuteOk
-->recvresult
-->echo Find with < Operator and placeholder
Mysqlx.Crud.Find {
collection {
name: "mytable"
schema: "xtest"
}
data_model: TABLE
criteria {
type: OPERATOR
operator {
name: "<"
param {
type: IDENT
identifier {
name: "price"
}
}
param {
type: PLACEHOLDER
position: 0
}
}
}
args {
type: V_DOUBLE
v_double: 0.25
}
}
## expect Mysqlx.Sql.StmtExecuteOk
-->recvresult
-->echo Find with >= Operator and placeholder
Mysqlx.Crud.Find {
collection {
name: "mytable"
schema: "xtest"
}
data_model: TABLE
criteria {
type: OPERATOR
operator {
name: ">="
param {
type: IDENT
identifier {
name: "price"
}
}
param {
type: PLACEHOLDER
position: 0
}
}
}
args {
type: V_DOUBLE
v_double: 1.80
}
}
## expect Mysqlx.Sql.StmtExecuteOk
-->recvresult
-->echo Find with <= Operator and placeholder
Mysqlx.Crud.Find {
collection {
name: "mytable"
schema: "xtest"
}
data_model: TABLE
criteria {
type: OPERATOR
operator {
name: "<="
param {
type: IDENT
identifier {
name: "price"
}
}
param {
type: PLACEHOLDER
position: 0
}
}
}
args {
type: V_DOUBLE
v_double: 0.25
}
}
## expect Mysqlx.Sql.StmtExecuteOk
-->recvresult
-->echo Find with in Operator and placeholder
Mysqlx.Crud.Find {
collection {
name: "mytable"
schema: "xtest"
}
data_model: TABLE
criteria {
type: OPERATOR
operator {
name: "in"
param {
type: IDENT
identifier {
name: "price"
}
}
param {
type: PLACEHOLDER
position: 0
}
}
}
args {
type: V_DOUBLE
v_double: 1.80
}
}
## expect Mysqlx.Sql.StmtExecuteOk
-->recvresult
-->echo Find with not in Operator and placeholder
Mysqlx.Crud.Find {
collection {
name: "mytable"
schema: "xtest"
}
data_model: TABLE
criteria {
type: OPERATOR
operator {
name: "not_in"
param {
type: IDENT
identifier {
name: "price"
}
}
param {
type: PLACEHOLDER
position: 0
}
}
}
args {
type: V_DOUBLE
v_double: 1.80
}
}
## expect Mysqlx.Sql.StmtExecuteOk
-->recvresult
-->echo Find with == Operator, V_NULL and placeholder
Mysqlx.Crud.Find {
collection {
name: "mytable"
schema: "xtest"
}
data_model: TABLE
criteria {
type: OPERATOR
operator {
name: "is"
param {
type: IDENT
identifier {
name: "price"
}
}
param {
type: PLACEHOLDER
position: 0
}
}
}
args {
type: V_NULL
}
}
## expect Mysqlx.Sql.StmtExecuteOk
-->recvresult
## CRUD UPDATE PLACEHOLDER SCENARIOS
-->echo Update with == operator and placeholder
Mysqlx.Crud.Update {
collection {
name: "mytable"
schema: "xtest"
}
data_model: TABLE
criteria {
type: OPERATOR
operator {
name: "=="
param {
type: IDENT
identifier {
name: "price"
}
}
param {
type: PLACEHOLDER
position: 0
}
}
}
operation {
source {
name: "price"
}
operation: SET
value {
type: LITERAL
literal {
type: V_DOUBLE
v_double: 18.00
}
}
}
args {
type: V_DOUBLE
v_double: 1.80
}
}
## expect Mysqlx.Sql.StmtExecuteOk
-->recvresult
-->sql
SELECT * FROM xtest.mytable ORDER BY price;
-->endsql
-->echo Update with > operator and placeholder
Mysqlx.Crud.Update {
collection {
name: "mytable"
schema: "xtest"
}
data_model: TABLE
criteria {
type: OPERATOR
operator {
name: ">"
param {
type: IDENT
identifier {
name: "price"
}
}
param {
type: PLACEHOLDER
position: 0
}
}
}
operation {
source {
name: "price"
}
operation: SET
value {
type: LITERAL
literal {
type: V_DOUBLE
v_double: 10
}
}
}
args {
type: V_DOUBLE
v_double: 1
}
}
## expect Mysqlx.Sql.StmtExecuteOk
-->recvresult
-->sql
SELECT * FROM xtest.mytable ORDER BY price;
-->endsql
-->echo Update with >= operator and placeholder
Mysqlx.Crud.Update {
collection {
name: "mytable"
schema: "xtest"
}
data_model: TABLE
criteria {
type: OPERATOR
operator {
name: ">="
param {
type: IDENT
identifier {
name: "price"
}
}
param {
type: PLACEHOLDER
position: 0
}
}
}
operation {
source {
name: "price"
}
operation: SET
value {
type: LITERAL
literal {
type: V_DOUBLE
v_double: 895.63
}
}
}
args {
type: V_DOUBLE
v_double: 1.80
}
}
## expect Mysqlx.Sql.StmtExecuteOk
-->recvresult
-->sql
SELECT * FROM xtest.mytable ORDER BY price;
-->endsql
-->echo Update with <= operator and placeholder
Mysqlx.Crud.Update {
collection {
name: "mytable"
schema: "xtest"
}
data_model: TABLE
criteria {
type: OPERATOR
operator {
name: "<="
param {
type: IDENT
identifier {
name: "price"
}
}
param {
type: PLACEHOLDER
position: 0
}
}
}
operation {
source {
name: "price"
}
operation: SET
value {
type: LITERAL
literal {
type: V_DOUBLE
v_double: 456.54
}
}
}
args {
type: V_DOUBLE
v_double: 0.90
}
}
## expect Mysqlx.Sql.StmtExecuteOk
-->recvresult
-->sql
SELECT * FROM xtest.mytable ORDER BY price;
-->endsql
-->echo Update with Float value and placeholder
Mysqlx.Crud.Update {
collection {
name: "mytable"
schema: "xtest"
}
data_model: TABLE
criteria {
type: OPERATOR
operator {
name: "=="
param {
type: IDENT
identifier {
name: "name"
}
}
param {
type: PLACEHOLDER
position: 0
}
}
}
operation {
source {
name: "price"
}
operation: SET
value {
type: LITERAL
literal {
type: V_FLOAT
v_float: 256.53
}
}
}
args {
type: V_STRING
v_string{
value: "berry"
}
}
}
## expect Mysqlx.Sql.StmtExecuteOk
-->recvresult
-->sql
SELECT * FROM xtest.mytable ORDER BY price;
-->endsql
-->echo Update with String value and placeholder
Mysqlx.Crud.Update {
collection {
name: "mytable"
schema: "xtest"
}
data_model: TABLE
criteria {
type: OPERATOR
operator {
name: "=="
param {
type: IDENT
identifier {
name: "price"
}
}
param {
type: PLACEHOLDER
position: 0
}
}
}
operation {
source {
name: "name"
}
operation: SET
value {
type: LITERAL
literal {
type: V_STRING
v_string{
value: "watermelon"
}
}
}
}
args {
type: V_DOUBLE
v_double: 256.53
}
}
## expect Mysqlx.Sql.StmtExecuteOk
-->recvresult
-->sql
SELECT * FROM xtest.mytable ORDER BY price;
-->endsql
-->echo Update with Null value and placeholder
Mysqlx.Crud.Update {
collection {
name: "mytable"
schema: "xtest"
}
data_model: TABLE
criteria {
type: OPERATOR
operator {
name: "=="
param {
type: IDENT
identifier {
name: "name"
}
}
param {
type: PLACEHOLDER
position: 0
}
}
}
operation {
source {
name: "price"
}
operation: SET
value {
type: LITERAL
literal {
type: V_NULL
}
}
}
args {
type: V_STRING
v_string{
value: "banana"
}
}
}
## expect Mysqlx.Sql.StmtExecuteOk
-->recvresult
-->sql
SELECT * FROM xtest.mytable ORDER BY price;
-->endsql
-->echo Update with ITEM_MERGE value and placeholder
Mysqlx.Crud.Update {
collection {
name: "mytable"
schema: "xtest"
}
data_model: TABLE
criteria {
type: OPERATOR
operator {
name: "=="
param {
type: IDENT
identifier {
name: "name"
}
}
param {
type: PLACEHOLDER
position: 0
}
}
}
operation {
source {
name: 'info'
}
operation: ITEM_MERGE
value: {
type: LITERAL
literal {
type: V_OCTETS
v_octets {value:'{"third":3.0, "fourth": "four"}'}
}
}
}
args {
type: V_STRING
v_string{
value: "banana"
}
}
}
#-- Mysqlx.Sql.StmtExecuteOk
-->recvresult
-->sql
SELECT * FROM xtest.mytable ORDER BY price;
insert into xtest.mytable values (7, 'Grapes',null, '{"third": ["two"]}');
-->endsql
-->echo Update with ARRAY_INSERT value and placeholder
Mysqlx.Crud.Update {
collection {
name: "mytable"
schema: "xtest"
}
data_model: TABLE
criteria {
type: OPERATOR
operator {
name: "=="
param {
type: IDENT
identifier {
name: "name"
}
}
param {
type: PLACEHOLDER
position: 0
}
}
}
operation {
source {
name: 'info'
document_path {type: MEMBER value: 'third'}
document_path {type: ARRAY_INDEX index: 0}
}
operation: ARRAY_INSERT
value: {
type: LITERAL
literal {
type: V_OCTETS
v_octets {value:'two.1'}
}
}
}
args {
type: V_STRING
v_string{
value: "Grapes"
}
}
}
#-- Mysqlx.Sql.StmtExecuteOk
-->recvresult
-->sql
SELECT * FROM xtest.mytable ORDER BY price;
-->endsql
## CRUD DELETE PLACEHOLDER SCENARIOS
-->echo Delete with == Operator and placeholder
Mysqlx.Crud.Delete {
collection {
name: "mytable"
schema: "xtest"
}
data_model: TABLE
criteria {
type: OPERATOR
operator {
name: "=="
param {
type: IDENT
identifier {
name: "price"
}
}
param {
type: PLACEHOLDER
position: 0
}
}
}
args {
type: V_DOUBLE
v_double: 256.53
}
}
## expect Mysqlx.Sql.StmtExecuteOk
-->recvresult
-->sql
SELECT * FROM xtest.mytable;
-->endsql
-->echo Delete with != Operator and placeholder
Mysqlx.Crud.Delete {
collection {
name: "mytable"
schema: "xtest"
}
data_model: TABLE
criteria {
type: OPERATOR
operator {
name: "!="
param {
type: IDENT
identifier {
name: "price"
}
}
param {
type: PLACEHOLDER
position: 0
}
}
}
args {
type: V_DOUBLE
v_double: 256.53
}
}
## expect Mysqlx.Sql.StmtExecuteOk
-->recvresult
-->sql
SELECT * FROM xtest.mytable;
-->endsql
## Wrong or missing placeholder
-->echo Find Wrong placeholder
Mysqlx.Crud.Find {
collection {
name: "mytable"
schema: "xtest"
}
data_model: TABLE
criteria {
type: OPERATOR
operator {
name: ">"
param {
type: IDENT
identifier {
name: "price"
}
}
param {
type: PLACEHOLDER
position: 1
}
}
}
args {
type: V_DOUBLE
v_double: 0
}
}
-->expecterror 5154
-->recvresult
-->echo Find Missing placeholder
Mysqlx.Crud.Find {
collection {
name: "mytable"
schema: "xtest"
}
data_model: TABLE
criteria {
type: OPERATOR
operator {
name: ">"
param {
type: IDENT
identifier {
name: "price"
}
}
param {
type: PLACEHOLDER
position: 0
}
}
}
}
-->expecterror 5154
-->recvresult
## Cleanup
-->echo ================================================================================
-->echo CLEAN UP
-->echo ================================================================================
-->sql
drop schema if exists xtest;
-->endsql
EOF
--exec $MYSQLXTEST -ux_root --password='' --file=$MYSQL_TMP_DIR/crud_criteria_args.tmp 2>&1
## Cleanup
--remove_file $MYSQL_TMP_DIR/crud_criteria_args.tmp
--source include/xplugin_drop_user.inc
Reference in New Issue View Git Blame Copy Permalink