polardbxengine/mysql-test/suite/x/t/crud_find_doc_groupby.test

## Doc: Find with group by clause
## BUG#22950240
## BUG#23738896

--source include/xplugin_preamble.inc
--source include/xplugin_create_user.inc

CREATE SCHEMA xtest DEFAULT CHARSET 'utf8mb4';


## Test starts here
--write_file $MYSQL_TMP_DIR/crud_find_doc_groupby.tmp
-->quiet

-->stmtadmin create_collection	{"schema":"xtest","name":"xcoll"}
-->recvresult

INSERT INTO xtest.xcoll (doc) VALUES
('{"_id": 1, "age": 1, "name": "foo"}'),
('{"_id": 2, "age": 2, "name": "bar"}'),
('{"_id": 3, "age": 3, "name": "baz", "date": {"day": 20, "month": "Apr"}} '),
('{"_id": 4, "age": 7, "name": "foo"}'),
('{"_id": 5, "age": 17, "name": "buz"}');

-->echo group by name
Mysqlx.Crud.Find {
  collection { name: "xcoll" schema: "xtest" }
  data_model: DOCUMENT
  projection {
    alias: "name"
    source {
      type: IDENT
      identifier {
        document_path {
          type: MEMBER
          value: "name"
        }
      }
    }
  }
  grouping {
    type: IDENT
    identifier {
      document_path {
        type: MEMBER
        value: "name"
      }
    }
  }
}
-->recvresult

-->echo group by name and count
Mysqlx.Crud.Find {
  collection { name: "xcoll" schema: "xtest" }
  data_model: DOCUMENT
  projection {
    alias: "name"
    source {
      type: IDENT
      identifier {
        document_path {
          type: MEMBER
          value: "name"
        }
      }
    }
  }
  projection {
    alias: "count"
    source {
      type: FUNC_CALL
      function_call {
        name {
          name: "count"
        }
        param {
          type: OPERATOR operator {
            name: "*"
          }
        }
      }
    }
  }
  grouping {
      type: IDENT
      identifier {
        document_path {
          type: MEMBER
          value: "name"
        }
      }
  }
}
-->recvresult

-->echo group by name, age and count
Mysqlx.Crud.Find {
  collection { name: "xcoll" schema: "xtest" }
  data_model: DOCUMENT
  projection {
    alias: "name"
    source {
      type: IDENT
      identifier {
        document_path {
          type: MEMBER
          value: "name"
        }
      }
    }
  }
  projection {
    alias: "age"
    source {
      type: IDENT
      identifier {
        document_path {
          type: MEMBER
          value: "age"
        }
      }
    }
  }
  projection {
    alias: "count"
    source {
      type: FUNC_CALL
      function_call {
        name {
          name: "count"
        }
        param {
          type: OPERATOR operator {
            name: "*"
          }
        }
      }
    }
  }
  grouping {
    type: IDENT
    identifier {
      document_path {
        type: MEMBER
        value: "name"
      }
    }
  }
  grouping {
    type: IDENT
    identifier {
      document_path {
        type: MEMBER
        value: "age"
      }
    }
  }
}
-->recvresult

-->echo no projection - error expected
Mysqlx.Crud.Find {
  collection { name: "xcoll" schema: "xtest" }
  data_model: DOCUMENT
  grouping {
    type: IDENT
    identifier {
      document_path {
        type: MEMBER
        value: "name"
      }
    }
  }
}
-->expecterror ER_X_BAD_PROJECTION
-->recvresult

-->echo group by name, count name and criteria on count
Mysqlx.Crud.Find {
  collection { name: "xcoll" schema: "xtest" }
  data_model: DOCUMENT
  projection {
    alias: "name"
    source {
      type: IDENT
      identifier {
        document_path {
          type: MEMBER
          value: "name"
        }
      }
    }
  }
  projection {
    alias: "count"
    source {
      type: FUNC_CALL
      function_call {
        name {
          name: "count"
        }
        param {
          type: OPERATOR operator {
            name: "*"
          }
        }
      }
    }
  }
  grouping {
    type: IDENT
    identifier {
      document_path {
        type: MEMBER
        value: "name"
      }
    }
  }
  grouping_criteria {
    type: OPERATOR
    operator {
      name: ">"
      param {
        type: IDENT
        identifier {
          document_path {
            type: MEMBER
            value: "count"
          }
        }
      }
      param {
        type: LITERAL
        literal {
          type: V_UINT
          v_unsigned_int: 1
        }
      }
    }
  }
}
-->expecterror ER_X_EXPR_BAD_VALUE
-->recvresult


-->echo group by name, criteria on max(age)
Mysqlx.Crud.Find {
  collection { name: "xcoll" schema: "xtest" }
  data_model: DOCUMENT
  projection {
    source {
      type: IDENT
      identifier {
        document_path {
          type: MEMBER
          value: "name"
        }
      }
    }
    alias: "my_name"
  }
  projection {
    source {
      type: FUNC_CALL
      function_call {
        name {
          name: "max"
        }
        param {
          type: IDENT
          identifier {
            document_path {
              type: MEMBER
              value: "age"
            }
          }
        }
      }
    }
    alias: "my_age"
  }
  order {
    expr {
      type: IDENT
      identifier {
        name: "my_age"
      }
    }
  }
  grouping {
    type: IDENT
    identifier {
      document_path {
        type: MEMBER
        value: "name"
      }
    }
  }
  grouping_criteria {
    type: OPERATOR
    operator {
      name: ">"
      param {
        type: FUNC_CALL
        function_call {
          name {
            name: "max"
          }
          param {
            type: IDENT
            identifier {
              document_path {
                type: MEMBER
                value: "age"
              }
            }
          }
        }
      }
      param {
        type: LITERAL
        literal {
          type: V_UINT
          v_unsigned_int: 5
        }
      }
    }
  }
}
-->recvresult
EOF

--exec $MYSQLXTEST -ux_root --password='' --file=$MYSQL_TMP_DIR/crud_find_doc_groupby.tmp 2>&1


## Cleanup
DROP SCHEMA IF EXISTS xtest;
--remove_file $MYSQL_TMP_DIR/crud_find_doc_groupby.tmp
--source include/xplugin_drop_user.inc