CREATE SCHEMA xtest; CREATE TABLE xtest.xtable (doc JSON); INSERT INTO xtest.xtable (doc) VALUES ('{"array_10":[10], "array_40":[40], "array_10_20_30":[10,20,30], "array_red":["red"], "array_banana":["banana"], "array_true_true":[true, true], "array_false":[false], "array_red_green_pear":["red","green","pear"], "numeric_10":{"ten":10}, "numeric_40":{"forty":40}, "red":"red"}'); ## 1. Using 'overlaps' operator with literals ## 1.1 with literal array (numeric) Verify [Operator "overlaps" returns value 1] Verify [Operator "not_overlaps" returns value 0] Verify [Operator "overlaps" returns value 0] Verify [Operator "not_overlaps" returns value 1] ## 1. Using 'overlaps' operator with literals ## 1.1 with literal array (string) Verify [Operator "overlaps" returns value 1] Verify [Operator "not_overlaps" returns value 0] Verify [Operator "overlaps" returns value 0] Verify [Operator "not_overlaps" returns value 1] ## 1. Using 'overlaps' operator with literals ## 1.1 with literal array (bool) Verify [Operator "overlaps" returns value 1] Verify [Operator "not_overlaps" returns value 0] Verify [Operator "overlaps" returns value 0] Verify [Operator "not_overlaps" returns value 1] Verify [Operator "overlaps" returns value 1] Verify [Operator "not_overlaps" returns value 0] Verify [Operator "overlaps" returns value 0] Verify [Operator "not_overlaps" returns value 1] ## 1. Using 'overlaps' operator with literals ## 1.1 with literal array (plain octets string) Verify [Operator "overlaps" returns value 1] Verify [Operator "not_overlaps" returns value 0] Verify [Operator "overlaps" returns value 0] Verify [Operator "not_overlaps" returns value 1] ## 1. Using 'overlaps' operator with literals ## 1.1 with literal array (numeric octets) Verify [Operator "overlaps" returns value 1] Verify [Operator "not_overlaps" returns value 0] Verify [Operator "overlaps" returns value 0] Verify [Operator "not_overlaps" returns value 1] ## 1. Using 'overlaps' operator with literals ## 1.1 with literal array (string octets) Verify [Operator "overlaps" returns value 1] Verify [Operator "not_overlaps" returns value 0] Verify [Operator "overlaps" returns value 0] Verify [Operator "not_overlaps" returns value 1] ## 1. Using 'overlaps' operator with literals ## 1.1 with literal array (bool octets) Verify [Operator "overlaps" returns value 1] Verify [Operator "not_overlaps" returns value 0] Verify [Operator "overlaps" returns value 0] Verify [Operator "not_overlaps" returns value 1] Verify [Operator "overlaps" returns value 1] Verify [Operator "not_overlaps" returns value 0] Verify [Operator "overlaps" returns value 0] Verify [Operator "not_overlaps" returns value 1] ## 2. Using 'overlaps' operator with idetifiers ## 2.1 with document member array (numeric) Verify [Operator "overlaps" returns value 1] Verify [Operator "not_overlaps" returns value 0] Verify [Operator "overlaps" returns value 0] Verify [Operator "not_overlaps" returns value 1] Verify [Operator "overlaps" returns value 1] Verify [Operator "not_overlaps" returns value 0] Verify [Operator "overlaps" returns value 0] Verify [Operator "not_overlaps" returns value 1] ## 2. Using 'overlaps' operator with idetifiers ## 2.1 with document member array (bool) Verify [Operator "overlaps" returns value 1] Verify [Operator "not_overlaps" returns value 0] Verify [Operator "overlaps" returns value 0] Verify [Operator "not_overlaps" returns value 1] Verify [Operator "overlaps" returns value 1] Verify [Operator "not_overlaps" returns value 0] Verify [Operator "overlaps" returns value 0] Verify [Operator "not_overlaps" returns value 1] ## 2. Using 'overlaps' operator with idetifiers ## 2.1 with document member array (string) Verify [Operator "overlaps" returns value 1] Verify [Operator "not_overlaps" returns value 0] Verify [Operator "overlaps" returns value 0] Verify [Operator "not_overlaps" returns value 1] Verify [Operator "overlaps" returns value 1] Verify [Operator "not_overlaps" returns value 0] Verify [Operator "overlaps" returns value 0] Verify [Operator "not_overlaps" returns value 1] Verify [Operator "overlaps" returns value 1] Verify [Operator "not_overlaps" returns value 0] Verify [Operator "overlaps" returns value 0] Verify [Operator "not_overlaps" returns value 1] ## 1. Using 'overlaps' operator with literals ## 1.2 with literal object (numeric) Verify [Operator "overlaps" returns value 1] Verify [Operator "not_overlaps" returns value 0] Verify [Operator "overlaps" returns value 0] Verify [Operator "not_overlaps" returns value 1] ## 1. Using 'overlaps' operator with literals ## 1.2 with literal object (string) Verify [Operator "overlaps" returns value 1] Verify [Operator "not_overlaps" returns value 0] Verify [Operator "overlaps" returns value 0] Verify [Operator "not_overlaps" returns value 1] ## 1. Using 'overlaps' operator with literals ## 1.2 with literal object (numeric octets) Verify [Operator "overlaps" returns value 1] Verify [Operator "not_overlaps" returns value 0] Verify [Operator "overlaps" returns value 0] Verify [Operator "not_overlaps" returns value 1] ## 1. Using 'overlaps' operator with literals ## 1.2 with literal object (string octets) Verify [Operator "overlaps" returns value 1] Verify [Operator "not_overlaps" returns value 0] Verify [Operator "overlaps" returns value 0] Verify [Operator "not_overlaps" returns value 1] ## 2. Using 'overlaps' operator with idetifiers ## 2.1 with document member array (document root) Verify [Operator "overlaps" returns value 1] Verify [Operator "not_overlaps" returns value 0] Verify [Operator "overlaps" returns value 0] Verify [Operator "not_overlaps" returns value 1] ## 2. Using 'overlaps' operator with idetifiers ## 2.2 with document member object (document root) Verify [Operator "overlaps" returns value 1] Verify [Operator "not_overlaps" returns value 0] Verify [Operator "overlaps" returns value 0] Verify [Operator "not_overlaps" returns value 1] ## 2. Using 'overlaps' operator with idetifiers ## 2.2 with document member object Verify [Operator "overlaps" returns value 1] Verify [Operator "not_overlaps" returns value 0] Verify [Operator "overlaps" returns value 0] Verify [Operator "not_overlaps" returns value 1] ## 3. Using 'overlaps' operator with operators (not supported operator) Got expected error: OVERLAPS expression requires operator that produce a JSON value. (code 5154) ## 3. Using 'overlaps' operator with operators (cast to json) Verify [Operator "overlaps" returns value 1] ## 1. Using 'overlaps' operator with literals ## 1.1 with literal array (null) Verify [Operator "overlaps" returns value 1] Verify [Operator "overlaps" returns value 1] Verify [Operator "not_overlaps" returns value 1] Verify [Operator "not_overlaps" returns value 1] ## 1. Using 'overlaps' operator with literals ## 1.2 with literal object (null) Verify [Operator "overlaps" returns value 1] Verify [Operator "overlaps" returns value 1] Verify [Operator "not_overlaps" returns value 1] Verify [Operator "not_overlaps" returns value 1] ## 4. Using 'overlaps' operator with function calls (not supported function) Got expected error: OVERLAPS expression requires function that produce a JSON value. (code 5154) ## 4. Using 'overlaps' operator with function calls (supported functions) JSON_ARRAY - Create JSON array ------------------------------ Verify [Operator "overlaps" returns value 1] Verify [Operator "not_overlaps" returns value 0] Verify [Operator "overlaps" returns value 0] Verify [Operator "not_overlaps" returns value 1] JSON_ARRAY_APPEND - Append data to JSON document ------------------------------------------------ Verify [Operator "overlaps" returns value 1] Verify [Operator "not_overlaps" returns value 0] Verify [Operator "overlaps" returns value 0] Verify [Operator "not_overlaps" returns value 1] JSON_ARRAY_INSERT - Insert into JSON array ------------------------------------------ Verify [Operator "overlaps" returns value 1] Verify [Operator "not_overlaps" returns value 0] Verify [Operator "overlaps" returns value 0] Verify [Operator "not_overlaps" returns value 1] JSON_CONTAINS - Whether JSON document contains specific object at path ---------------------------------------------------------------------- Got expected error: OVERLAPS expression requires function that produce a JSON value. (code 5154) JSON_CONTAINS_PATH - Whether JSON document contains any data at path -------------------------------------------------------------------- Got expected error: OVERLAPS expression requires function that produce a JSON value. (code 5154) JSON_DEPTH - Maximum depth of JSON document ------------------------------------------- Got expected error: OVERLAPS expression requires function that produce a JSON value. (code 5154) JSON_EXTRACT - Return data from JSON document --------------------------------------------- Verify [Operator "overlaps" returns value 1] Verify [Operator "not_overlaps" returns value 0] Verify [Operator "overlaps" returns value 0] Verify [Operator "not_overlaps" returns value 1] JSON_INSERT - Insert data into JSON document -------------------------------------------- Verify [Operator "overlaps" returns value 1] Verify [Operator "not_overlaps" returns value 0] Verify [Operator "overlaps" returns value 0] Verify [Operator "not_overlaps" returns value 1] JSON_KEYS - Array of keys from JSON document -------------------------------------------- Verify [Operator "overlaps" returns value 1] Verify [Operator "not_overlaps" returns value 0] Verify [Operator "overlaps" returns value 0] Verify [Operator "not_overlaps" returns value 1] JSON_LENGTH - Number of elements in JSON document ------------------------------------------------- Got expected error: OVERLAPS expression requires function that produce a JSON value. (code 5154) JSON_MERGE - Merge JSON documents --------------------------------- Verify [Operator "overlaps" returns value 1] Verify [Operator "not_overlaps" returns value 0] Verify [Operator "overlaps" returns value 0] Verify [Operator "not_overlaps" returns value 1] JSON_OBJECT - Create JSON object -------------------------------- Verify [Operator "overlaps" returns value 1] Verify [Operator "not_overlaps" returns value 0] Verify [Operator "overlaps" returns value 0] Verify [Operator "not_overlaps" returns value 1] JSON_REMOVE - Remove data from JSON document -------------------------------------------- Verify [Operator "overlaps" returns value 1] Verify [Operator "not_overlaps" returns value 0] Verify [Operator "overlaps" returns value 0] Verify [Operator "not_overlaps" returns value 1] JSON_REPLACE - Replace values in JSON document ---------------------------------------------- Verify [Operator "overlaps" returns value 1] Verify [Operator "not_overlaps" returns value 0] Verify [Operator "overlaps" returns value 0] Verify [Operator "not_overlaps" returns value 1] JSON_SEARCH - Path to value within JSON document ------------------------------------------------ Verify [Operator "overlaps" returns value 1] Verify [Operator "not_overlaps" returns value 0] Verify [Operator "overlaps" returns value 0] Verify [Operator "not_overlaps" returns value 1] JSON_SET - Insert data into JSON document ----------------------------------------- Verify [Operator "overlaps" returns value 1] Verify [Operator "not_overlaps" returns value 0] Verify [Operator "overlaps" returns value 0] Verify [Operator "not_overlaps" returns value 1] JSON_TYPE - Type of JSON value ------------------------------ Got expected error: OVERLAPS expression requires function that produce a JSON value. (code 5154) JSON_VALID - Whether JSON value is valid ---------------------------------------- Got expected error: OVERLAPS expression requires function that produce a JSON value. (code 5154) ## 5. Validating number of 'overlaps' arguments Got expected error: OVERLAPS expression requires two parameters. (code 5151) Got expected error: OVERLAPS expression requires two parameters. (code 5151) overlaps 0 command ok Got expected error (code 0) Got expected error: OVERLAPS expression requires two parameters. (code 5151) # Some extra scenarios Verify [Operator "overlaps" returns value 0] Verify [Operator "not_overlaps" returns value 1] Verify [Operator "overlaps" returns value 0] Verify [Operator "not_overlaps" returns value 1] Verify [Operator "overlaps" returns value 0] Verify [Operator "not_overlaps" returns value 1] Verify [Operator "overlaps" returns value 1] Verify [Operator "not_overlaps" returns value 0] Verify [Operator "overlaps" returns value 1] Verify [Operator "not_overlaps" returns value 0] Verify [Operator "overlaps" returns value 0] Verify [Operator "not_overlaps" returns value 1] Verify [Operator "overlaps" returns value 1] Verify [Operator "not_overlaps" returns value 0] Mysqlx.Ok { msg: "bye!" } ok DROP SCHEMA IF EXISTS xtest;