mtr/t/crud_doc_criteria_args.test

###########  ../t/crud_doc_criteria_args.test              #############
###                                                                    #
### Manipulating on valid rows by criteria with placeholders           #
### Test covers                                                        #
###  - FIND with different operators                                   #
###  - UPDATE with different operators                                 #
###  - UPDATE (ITEM_SET, ITEM_REPLACE, ITEM_REMOVE, ARRAY_APPEND,      #
###            ARRAY_INSERT, ITEM_MERGE)                               #
###  - DELETE with different operators                                 #
###                                                                    #
########################################################################
#

--echo =============================================
--echo     CRUD DOC CRITERIA ARGS SCENARIOS
--echo =============================================
--echo

--echo ================================================================================
--echo PREAMBLE
--echo ================================================================================
## Preamble
--source ../include/xplugin_preamble.inc
## Test starts here
--echo ================================================================================
--echo TEST START
--echo ================================================================================

--write_file $MYSQL_TMP_DIR/crud_doc_criteria_args.tmp
## Test data
-->sql
drop schema if exists xtest;
create schema xtest;
use xtest;
create table mycoll (doc JSON, _id VARCHAR(32) NOT NULL PRIMARY KEY);
insert into mycoll (doc, _id) values ('{"_id": "1", "name": "Robb", "amount": 125.21}', json_unquote(json_extract(doc, '$._id')));
insert into mycoll (doc, _id) values ('{"_id": "2", "name": "Bran", "amount": 542.75}', json_unquote(json_extract(doc, '$._id')));
insert into mycoll (doc, _id) values ('{"_id": "3", "name": "Arya", "amount": 98.89}', json_unquote(json_extract(doc, '$._id')));
insert into mycoll (doc, _id) values ('{"_id": "4", "name": "Tassadar", "amount": 57.52}', json_unquote(json_extract(doc, '$._id')));
insert into mycoll (doc, _id) values ('{"_id": "5", "name": "Sansa", "amount": null}', json_unquote(json_extract(doc, '$._id')));
SELECT * FROM xtest.mycoll;
-->endsql

-->echo Find with > Operator and placeholder
Mysqlx.Crud.Find {
  collection {
    name: "mycoll"
    schema: "xtest"
  }
  data_model: DOCUMENT
  criteria {
    type: OPERATOR
    operator {
      name: ">"
      param {
        type: IDENT identifier {
          document_path {
            type: MEMBER
            value: "amount"
          }
        }
      }
      param {
        type: PLACEHOLDER position: 0
      }
    }
  }
  args {
     type: V_DOUBLE v_double: 98.89
  }
}

## expect Mysqlx.Sql.StmtExecuteOk
-->recvresult

-->echo Find with < Operator and placeholder
Mysqlx.Crud.Find {
  collection {
    name: "mycoll"
    schema: "xtest"
  }
  data_model: DOCUMENT
  criteria {
    type: OPERATOR
    operator {
      name: "<"
      param {
        type: IDENT identifier {
          document_path {
            type: MEMBER
            value: "amount"
          }
        }
      }
      param {
        type: PLACEHOLDER position: 0
      }
    }
  }
  args {
      type: V_DOUBLE v_double: 98.89
  }
}

## expect Mysqlx.Sql.StmtExecuteOk
-->recvresult

-->echo Find with >= Operator and placeholder
Mysqlx.Crud.Find {
  collection {
    name: "mycoll"
    schema: "xtest"
  }
  data_model: DOCUMENT
  criteria {
    type: OPERATOR
    operator {
      name: ">="
      param {
        type: IDENT identifier {
          document_path {
            type: MEMBER
            value: "amount"
          }
        }
      }
      param {
        type: PLACEHOLDER position: 0
      }
    }
  }
  args {
      type: V_DOUBLE v_double: 98.89
  }
}

## expect Mysqlx.Sql.StmtExecuteOk
-->recvresult

-->echo Find with <= Operator and placeholder
Mysqlx.Crud.Find {
  collection {
    name: "mycoll"
    schema: "xtest"
  }
  data_model: DOCUMENT
  criteria {
    type: OPERATOR
    operator {
      name: "<="
      param {
        type: IDENT identifier {
          document_path {
            type: MEMBER
            value: "amount"
          }
        }
      }
      param {
        type: PLACEHOLDER position: 0
      }
    }
  }
  args {
      type: V_DOUBLE v_double: 98.89
  }
}

## expect Mysqlx.Sql.StmtExecuteOk
-->recvresult

-->echo Find with <= Operator, placeholder and Order desc
Mysqlx.Crud.Find {
  collection {
    name: "mycoll"
    schema: "xtest"
  }
  data_model: DOCUMENT
  criteria {
    type: OPERATOR
    operator {
      name: "!="
      param {
        type: IDENT identifier {
          document_path {
            type: MEMBER
            value: "amount"
          }
        }
      }
      param {
        type: PLACEHOLDER position: 0
      }
    }
  }
  args {
      type: V_DOUBLE v_double: 0
  }
  order {
    expr {
      type: IDENT
      identifier {
        name: "_id"
      }
    }
    direction: DESC
  }
}

## expect Mysqlx.Sql.StmtExecuteOk
-->recvresult

-->echo Find with <= Operator, placeholder and Order asc
Mysqlx.Crud.Find {
  collection {
    name: "mycoll"
    schema: "xtest"
  }
  data_model: DOCUMENT
  criteria {
    type: OPERATOR
    operator {
      name: "!="
      param {
        type: IDENT identifier {
          document_path {
            type: MEMBER
            value: "amount"
          }
        }
      }
      param {
        type: PLACEHOLDER position: 0
      }
    }
  }
  args {
      type: V_DOUBLE v_double: 0
  }
  order {
    expr {
      type: IDENT
      identifier {
        name: "_id"
      }
    }
    direction: ASC
  }
}

## expect Mysqlx.Sql.StmtExecuteOk
-->recvresult

-->echo Find with == Operator and placeholder
Mysqlx.Crud.Find {
  collection {
    name: "mycoll"
    schema: "xtest"
  }
  data_model: DOCUMENT
  criteria {
    type: OPERATOR
    operator {
      name: "=="
      param {
        type: IDENT identifier {
          document_path {
            type: MEMBER
            value: "name"
          }
        }
      }
      param {
        type: PLACEHOLDER position: 0
      }
    }
  }
  args {
      type: V_STRING v_string { value: "Robb" }
  }
}

## expect Mysqlx.Sql.StmtExecuteOk
-->recvresult

-->echo Find with != Operator and placeholder
Mysqlx.Crud.Find {
  collection {
    name: "mycoll"
    schema: "xtest"
  }
  data_model: DOCUMENT
  criteria {
    type: OPERATOR
    operator {
      name: "!="
      param {
        type: IDENT identifier {
          document_path {
            type: MEMBER
            value: "name"
          }
        }
      }
      param {
        type: PLACEHOLDER position: 0
      }
    }
  }
  args {
      type: V_STRING v_string { value: "Robb" }
  }
}

## expect Mysqlx.Sql.StmtExecuteOk
-->recvresult

-->echo Find with in Operator and placeholder
Mysqlx.Crud.Find {
  collection {
    name: "mycoll"
    schema: "xtest"
  }
  data_model: DOCUMENT
  criteria {
    type: OPERATOR
    operator {
      name: "in"
      param {
        type: IDENT identifier {
          document_path {
            type: MEMBER
            value: "_id"
          }
        }
      }
      param {
        type: PLACEHOLDER position: 0
      }
    }
  }
  args {
      type: V_STRING v_string { value: "3" }
  }
}

## expect Mysqlx.Sql.StmtExecuteOk
-->recvresult

-->echo Find with not_in Operator and placeholder
Mysqlx.Crud.Find {
  collection {
    name: "mycoll"
    schema: "xtest"
  }
  data_model: DOCUMENT
  criteria {
    type: OPERATOR
    operator {
      name: "not_in"
      param {
        type: IDENT identifier {
          document_path {
            type: MEMBER
            value: "_id"
          }
        }
      }
      param {
        type: PLACEHOLDER position: 0
      }
    }
  }
  args {
      type: V_STRING v_string { value: "3" }
  }
}

## expect Mysqlx.Sql.StmtExecuteOk
-->recvresult

-->echo Update with == operator and placeholder
Mysqlx.Crud.Update {
  collection {
    name: "mycoll"
    schema: "xtest"
  }
  data_model: DOCUMENT
  criteria {
    type: OPERATOR
    operator {
      name: "=="
      param {
        type: IDENT identifier {
          document_path {
            type: MEMBER
            value: "amount"
          }
        }
      }
      param {
        type: PLACEHOLDER position: 0
      }
    }
  }
  operation {
    source {
          document_path {
            type: MEMBER
            value: "name"
          }
    }
    operation: ITEM_SET
    value {
      type: LITERAL
      literal {
          type: V_OCTETS v_octets {value:"Update_=="}
      }
    }
  }
  args {
      type: V_DOUBLE v_double: 98.89
  }
}

## expect Mysqlx.Sql.StmtExecuteOk
-->recvresult

-->sql
SELECT * FROM xtest.mycoll;
-->endsql

-->echo Update with > operator and placeholder
Mysqlx.Crud.Update {
  collection {
    name: "mycoll"
    schema: "xtest"
  }
  data_model: DOCUMENT
  criteria {
    type: OPERATOR
    operator {
      name: ">"
      param {
        type: IDENT identifier {
          document_path {
            type: MEMBER
            value: "amount"
          }
        }
      }
      param {
        type: PLACEHOLDER position: 0
      }
    }
  }
  operation {
    source {
          document_path {
            type: MEMBER
            value: "name"
          }
    }
    operation: ITEM_SET
    value {
      type: LITERAL
      literal {
          type: V_OCTETS v_octets {value:"Update_>"}
      }
    }
  }
  args {
      type: V_DOUBLE v_double: 98.89
  }
}

## expect Mysqlx.Sql.StmtExecuteOk
-->recvresult

-->sql
SELECT * FROM xtest.mycoll;
-->endsql

-->echo Update with >= operator and placeholder
Mysqlx.Crud.Update {
  collection {
    name: "mycoll"
    schema: "xtest"
  }
  data_model: DOCUMENT
  criteria {
    type: OPERATOR
    operator {
      name: ">="
      param {
        type: IDENT identifier {
          document_path {
            type: MEMBER
            value: "amount"
          }
        }
      }
      param {
        type: PLACEHOLDER position: 0
      }
    }
  }
  operation {
    source {
          document_path {
            type: MEMBER
            value: "name"
          }
    }
    operation: ITEM_SET
    value {
      type: LITERAL
      literal {
          type: V_OCTETS v_octets {value:"Update_>="}
      }
    }
  }
  args {
      type: V_DOUBLE v_double: 98.89
  }
}

## expect Mysqlx.Sql.StmtExecuteOk
-->recvresult

-->sql
SELECT * FROM xtest.mycoll;
-->endsql

-->echo Update with <= operator and placeholder
Mysqlx.Crud.Update {
  collection {
    name: "mycoll"
    schema: "xtest"
  }
  data_model: DOCUMENT
  criteria {
    type: OPERATOR
    operator {
      name: "<="
      param {
        type: IDENT identifier {
          document_path {
            type: MEMBER
            value: "amount"
          }
        }
      }
      param {
        type: PLACEHOLDER position: 0
      }
    }
  }
  operation {
    source {
          document_path {
            type: MEMBER
            value: "name"
          }
    }
    operation: ITEM_SET
    value {
      type: LITERAL
      literal {
          type: V_OCTETS v_octets {value:"Update_<="}
      }
    }
  }
  args {
      type: V_DOUBLE v_double: 98.89
  }
}

## expect Mysqlx.Sql.StmtExecuteOk
-->recvresult

-->sql
SELECT * FROM xtest.mycoll;
-->endsql

-->echo Update with Float operator and placeholder
Mysqlx.Crud.Update {
  collection {
    name: "mycoll"
    schema: "xtest"
  }
  data_model: DOCUMENT
  criteria {
    type: OPERATOR
    operator {
      name: "=="
      param {
        type: IDENT identifier {
          document_path {
            type: MEMBER
            value: "name"
          }
        }
      }
      param {
        type: PLACEHOLDER position: 0
      }
    }
  }
  operation {
    source {
          document_path {
            type: MEMBER
            value: "amount"
          }
    }
    operation: ITEM_SET
    value {
      type: LITERAL
      literal {
          type: V_FLOAT v_float: 256.51
      }
    }
  }
  args {
      type: V_STRING v_string { value: "Update_<=" }
  }
}

## expect Mysqlx.Sql.StmtExecuteOk
-->recvresult

-->sql
SELECT * FROM xtest.mycoll;
-->endsql

-->echo Update with Double operator and placeholder
Mysqlx.Crud.Update {
  collection {
    name: "mycoll"
    schema: "xtest"
  }
  data_model: DOCUMENT
  criteria {
    type: OPERATOR
    operator {
      name: "=="
      param {
        type: IDENT identifier {
          document_path {
            type: MEMBER
            value: "name"
          }
        }
      }
      param {
        type: PLACEHOLDER position: 0
      }
    }
  }
  operation {
    source {
          document_path {
            type: MEMBER
            value: "amount"
          }
    }
    operation: ITEM_SET
    value {
      type: LITERAL
      literal {
          type: V_DOUBLE v_double: 256.51
      }
    }
  }
  args {
      type: V_STRING v_string { value: "Update_>=" }
  }
}

## expect Mysqlx.Sql.StmtExecuteOk
-->recvresult

-->sql
SELECT * FROM xtest.mycoll;
-->endsql

-->echo Update with String operator and placeholder
Mysqlx.Crud.Update {
  collection {
    name: "mycoll"
    schema: "xtest"
  }
  data_model: DOCUMENT
  criteria {
    type: OPERATOR
    operator {
      name: "=="
      param {
        type: IDENT identifier {
          document_path {
            type: MEMBER
            value: "amount"
          }
        }
      }
      param {
        type: PLACEHOLDER position: 0
      }
    }
  }
  operation {
    source {
          document_path {
            type: MEMBER
            value: "name"
          }
    }
    operation: ITEM_SET
    value {
      type: LITERAL
      literal {
          type: V_STRING v_string { value: "watermelon" }
      }
    }
  }
  args {
      type: V_DOUBLE v_double: 256.51
  }
}
## expect Mysqlx.Sql.StmtExecuteOk
-->recvresult

-->sql
SELECT * FROM xtest.mycoll;
-->endsql

-->echo Update with Null operator and placeholder
Mysqlx.Crud.Update {
  collection {
    name: "mycoll"
    schema: "xtest"
  }
  data_model: DOCUMENT
  criteria {
    type: OPERATOR
    operator {
      name: "=="
      param {
        type: IDENT identifier {
          document_path {
            type: MEMBER
            value: "name"
          }
        }
      }
      param {
        type: PLACEHOLDER position: 0
      }
    }
  }
  operation {
    source {
          document_path {
            type: MEMBER
            value: "amount"
          }
    }
    operation: ITEM_SET
    value {
      type: LITERAL
      literal {
          type:  V_NULL
      }
    }
  }
  args {
      type:  V_STRING v_string { value: "watermelon" }
  }
}
## expect Mysqlx.Sql.StmtExecuteOk
-->recvresult

-->sql
SELECT * FROM xtest.mycoll;
-->endsql

-->echo Update with ITEM_REPLACE operator and placeholder
Mysqlx.Crud.Update {
  collection {
    name: "mycoll"
    schema: "xtest"
  }
  data_model: DOCUMENT
  criteria {
    type: OPERATOR
    operator {
      name: "=="
      param {
        type: IDENT identifier {
          document_path {
            type: MEMBER
            value: "name"
          }
        }
      }
      param {
        type: PLACEHOLDER position: 0
      }
    }
  }
  operation {
    source {
          document_path {
            type: MEMBER
            value: "name"
          }
    }
    operation: ITEM_REPLACE
    value {
      type: LITERAL
      literal {
          type:  V_STRING v_string { value: "watermelon_replace" }
      }
    }
  }
  args {
      type:  V_STRING v_string { value: "watermelon" }
  }
}
## expect Mysqlx.Sql.StmtExecuteOk
-->recvresult

-->sql
SELECT * FROM xtest.mycoll;
-->endsql

-->echo Update with ITEM_REMOVE operator and placeholder
Mysqlx.Crud.Update {
  collection {
    name: "mycoll"
    schema: "xtest"
  }
  data_model: DOCUMENT
  criteria {
    type: OPERATOR
    operator {
      name: "=="
      param {
        type: IDENT identifier {
            name: "_id"
        }
      }
      param {
        type: PLACEHOLDER position: 0
      }
    }
  }
  operation {
    source {
          document_path {
            type: MEMBER
            value: "name"
          }
    }
    operation: ITEM_REMOVE
  }
  args {
      type:  V_DOUBLE v_double: 3
  }
}
## expect Mysqlx.Sql.StmtExecuteOk
-->recvresult

-->sql
SELECT * FROM xtest.mycoll;
-->endsql

-->echo Update with ARRAY_APPEND operator and placeholder
Mysqlx.Crud.Update {
  collection {
    name: "mycoll"
    schema: "xtest"
  }
  data_model: DOCUMENT
  criteria {
    type: OPERATOR
    operator {
      name: "=="
      param {
        type: IDENT identifier {
            name: "_id"
        }
      }
      param {
        type: PLACEHOLDER position: 0
      }
    }
  }
  operation {
    source {
          document_path {
            type: MEMBER
            value: "amount"
          }
    }
    operation: ARRAY_APPEND
    value {
      type: LITERAL
      literal {
          type: V_STRING
          v_string {
            value: "Update_ArrayAppend"
        }
      }
    }
  }
  args {
      type:  V_DOUBLE v_double: 3
  }
}
## expect Mysqlx.Sql.StmtExecuteOk
-->recvresult

-->sql
SELECT * FROM xtest.mycoll;
-->endsql

-->echo Update with ITEM_MERGE value and placeholder
Mysqlx.Crud.Update {
  collection {
    name: "mycoll"
    schema: "xtest"
  }
  data_model: TABLE
  criteria {
    type: OPERATOR
    operator {
      name: "=="
      param {
        type: IDENT
        identifier {
          name: "_id"
        }
      }
      param {
        type: PLACEHOLDER
        position: 0
      }
    }
  }
  operation {
    source {
      name: 'doc'
    }
    operation: ITEM_MERGE
    value: {
      type: LITERAL
      literal {
      	type: V_OCTETS
      	v_octets {value:'{"third":3.0, "fourth": "four"}'}
     	}
    }
  }
  args {
      type: V_UINT
      v_unsigned_int: 2
  }
}

-- Mysqlx.Sql.StmtExecuteOk
-->recvresult

-->sql
SELECT * FROM xtest.mycoll;
insert into mycoll (doc, _id) values ('{"_id": "6", "third": ["two"]}', json_unquote(json_extract(doc, '$._id')));
-->endsql

-->echo Update with ARRAY_INSERT value and placeholder
Mysqlx.Crud.Update {
  collection {
    name: "mycoll"
    schema: "xtest"
  }
  data_model: TABLE
  criteria {
    type: OPERATOR
    operator {
      name: "=="
      param {
        type: IDENT
        identifier {
          name: "_id"
        }
      }
      param {
        type: PLACEHOLDER
        position: 0
      }
    }
  }
  operation {
    source {
      name: 'doc'
      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_UINT
      v_unsigned_int: 6
   }
}
-- Mysqlx.Sql.StmtExecuteOk
-->recvresult

-->sql
SELECT * FROM xtest.mycoll;
-->endsql

-->echo Delete with == operator and placeholder
Mysqlx.Crud.Delete {
  collection {
    name: "mycoll"
    schema: "xtest"
  }
  data_model: DOCUMENT
  criteria {
    type: OPERATOR
    operator {
      name: "=="
      param {
        type: IDENT identifier {
            name: "_id"
        }
      }
      param {
        type: PLACEHOLDER position: 0
      }
    }
  }
  args {
      type: V_SINT v_signed_int: 2
  }
}

## expect Mysqlx.Sql.StmtExecuteOk
-->recvresult

-->sql
SELECT * FROM xtest.mycoll;
-->endsql

-->echo Delete with != operator and placeholder
Mysqlx.Crud.Delete {
  collection {
    name: "mycoll"
    schema: "xtest"
  }
  data_model: DOCUMENT
  criteria {
    type: OPERATOR
    operator {
      name: "!="
      param {
        type: IDENT identifier {
          document_path {
            type: MEMBER
            value: "name"
          }
        }
      }
      param {
        type: PLACEHOLDER position: 0
      }
    }
  }
  args {
      type: V_STRING v_string { value: "Update_<=" }
  }
}

## expect Mysqlx.Sql.StmtExecuteOk
-->recvresult

-->sql
SELECT * FROM xtest.mycoll;
-->endsql

## Wrong or missing placeholder

Mysqlx.Crud.Find {
  collection {
    name: "mycoll"
    schema: "xtest"
  }
  data_model: DOCUMENT
  criteria {
    type: OPERATOR
    operator {
      name: ">"
      param {
        type: IDENT identifier {
          document_path {
            type: MEMBER
            value: "name"
          }
        }
      }
      param {
        type: PLACEHOLDER position: 1
      }
    }
  }
  args {
      type: V_OCTETS v_octets {value:"Jon"}
  }
}

-->expecterror 5154
-->recvresult

Mysqlx.Crud.Find {
  collection {
    name: "mycoll"
    schema: "xtest"
  }
  data_model: DOCUMENT
  criteria {
    type: OPERATOR
    operator {
      name: ">"
      param {
        type: IDENT identifier {
          document_path {
            type: MEMBER
            value: "name"
          }
        }
      }
      param {
        type: PLACEHOLDER position: 1
      }
    }
  }
}

-->expecterror 5154
-->recvresult


## Cleanup
-->echo ================================================================================
-->echo CLEAN UP
-->echo ================================================================================
-->sql
drop schema if exists xtest;
-->endsql
EOF

--exec $MYSQLXTEST -uroot --password='' --file=$MYSQL_TMP_DIR/crud_doc_criteria_args.tmp 2>&1
--remove_file $MYSQL_TMP_DIR/crud_doc_criteria_args.tmp

## Postamble
--echo ================================================================================
--echo POSTAMBLE
--echo ================================================================================
uninstall plugin mysqlx;