Supports prepare and execute statement

[killme2008]

What problem does the new feature solve?

For example:

PREPARE v1 AS SELECT 'Test' LIMIT ?
EXECUTE v1(1)

May depend on #470

What does the feature do?

Supports prepare and execute statements.

Implementation challenges

No response

[MichaelScofield]

Note that the "prepare" and "execute" statement in datafusion only support postgresql style. We can follow the postgresql's document for implementing this feature: https://www.postgresql.org/docs/current/sql-prepare.html

[MichaelScofield]

All other necessary features like plan cache and parameter type parsing and binding are implemented now, so I think this issue is relatively simple to Implement. Release to "good first issue".

[CookiePieWw]

Is this still active? If so, I'd like to take it :)

All stmts through client are treated as COM_QUERY so I wonder if it's appropriate to filter the prepare and execute out in the on_query here and fall back to on_prepare and on_execute:

greptimedb/src/servers/src/mysql/handler.rs

Line 357 in 7de336f

async fn on_query<'a>(

[MichaelScofield]

@CookiePieWw On first thought, it's a viable option. Thanks for your interest, assigned.

[CookiePieWw]

@MichaelScofield I created a draft PR for this. Now I get stuck in 2 problems here:

1. To execute a statement like EXECUTE stmt, we need to construct a map from the identifier of the statement to the stmt_id of the prepared one. I tried to add a field prepared_statements into QueryContext for that but it does not work actually. It seems I need to use the configuration_variables in Session for that? It's used in the SET variables stmt which may be similar.
2. I'm struggling in parsing the parameters from the string EXECUTE stmt USING param1, ... into &[ScalarValue] which is used by LogicalPlan.replace_params_with_values. I'd like to assume there're some existing tools for it? A compromising plan is to use pure strings and fall back to do_query like it in on_execute.
   

   greptimedb/src/servers/src/mysql/handler.rs

   Lines 336 to 340 in d8b51cf

   	None => {
   	let query = replace_params(params, sql_plan.query);
   	debug!("Mysql execute replaced query: {}", query);
   	self.do_query(&query, query_ctx.clone()).await
   	}

Would you like to give me some advice? Thanks! :)

[MichaelScofield]

@MichaelScofield I created a draft PR for this. Now I get stuck in 2 problems here:

1. To execute a statement like EXECUTE stmt, we need to construct a map from the identifier of the statement to the stmt_id of the prepared one. I tried to add a field prepared_statements into QueryContext for that but it does not work actually. It seems I need to use the configuration_variables in Session for that? It's used in the SET variables stmt which may be similar.
2. I'm struggling in parsing the parameters from the string EXECUTE stmt USING param1, ... into &[ScalarValue] which is used by LogicalPlan.replace_params_with_values. I'd like to assume there're some existing tools for it? A compromising plan is to use pure strings and fall back to do_query like it in on_execute.
   

   greptimedb/src/servers/src/mysql/handler.rs

   Lines 336 to 340 in d8b51cf

   	None => {
   	let query = replace_params(params, sql_plan.query);
   	debug!("Mysql execute replaced query: {}", query);
   	self.do_query(&query, query_ctx.clone()).await
   	}

Would you like to give me some advice? Thanks! :)

@CookiePieWw :

1. Better not touch the Session. What's feature of mysql stays in the crate of mysql. There's already a prepared_stmts: Arc<RwLock<HashMap<u32, SqlPlan>>> field in struct MysqlInstanceShim for similar purpose, we can reuse it. The prepared_stmts then serves storing two types of prepared stmt ids, one for the original implicit generated, the other for your explicit name in PREPARE. You can create an enum StmtKey for them. Just one caution: don't let the explicit stmt name clash with the implicit stmt id.
2. We can treat all parameters in USING as strings, create ScalarValue::Utf8s from them, then cast them to the actual datatypes.