bug: Table not created, prompt Table 't2' already exists

[shangyanwen]

Describe the problem

1、create database syw_mtr;
2、use syw_mtr;
3、CREATE TABLE t1 (f1 VARCHAR(255) CHARACTER SET utf8)engine=stonedb;
4、CREATE TABLE t2 AS SELECT LEFT(f1,171) AS f2 FROM t1 UNION SELECT LEFT(f1,171) AS f2 FROM t1;
ERROR 1050 (42S01): Table 't2' already exists

Expected behavior

1. No error message is displayed, and T2 can be created normally
2. note：Verify innoDB, normal without any error

How To Reproduce

1、create database syw_mtr;
2、use syw_mtr;
3、CREATE TABLE t1 (f1 VARCHAR(255) CHARACTER SET utf8)engine=stonedb;
4、CREATE TABLE t2 AS SELECT LEFT(f1,171) AS f2 FROM t1 UNION SELECT LEFT(f1,171) AS f2 FROM t1;

Environment

1. StoneDB for mysql5.7  (release)
2. Ubuntu 20.04.4

Are you interested in submitting a PR to solve the problem?

• Yes, I will!

[lylth]

Reproduce the problem in the debug version：

create database syw_mtr;
use syw_mtr;
CREATE TABLE t1 (f1 VARCHAR(255) CHARACTER SET utf8)engine=stonedb;
CREATE TABLE t2 AS SELECT LEFT(f1,171) AS f2 FROM t1 UNION SELECT LEFT(f1,171) AS f2 FROM t1;
ERROR 2013 (HY000): Lost connection to MySQL server during query

[lylth]

The line of code that reports the error：

Query_result_create::prepare (sql_insert.cc:2753)
assert(create_table->table == NULL);

[lylth]

stonedb stack:

    libc.so.6!raise (未知源:0)
    libc.so.6!abort (未知源:0)
    libc.so.6!__assert_fail_base (未知源:0)
    libc.so.6!__assert_fail (未知源:0)
    Query_result_create::prepare(Query_result_create * const this, List<Item> & values, SELECT_LEX_UNIT * u) (\opt\litaihong\stonedb\sql\sql_insert.cc:2753)
    st_select_lex::prepare(st_select_lex * const this, THD * thd) (\opt\litaihong\stonedb\sql\sql_resolver.cc:361)
    st_select_lex_unit::prepare_fake_select_lex(st_select_lex_unit * const this, THD * thd_arg) (\opt\litaihong\stonedb\sql\sql_union.cc:441)
    st_select_lex_unit::optimize_for_stonedb(st_select_lex_unit * const this) (\opt\litaihong\stonedb\storage\stonedb\core\engine_execute.cpp:586)
    stonedb::core::Engine::HandleSelect(stonedb::core::Engine * const this, THD * thd, LEX * lex, Query_result *& result, ulong setup_tables_done_option, int & res, int & optimize_after_sdb, int & sdb_free_join, int with_insert) (\opt\litaihong\stonedb\storage\stonedb\core\engine_execute.cpp:181)
    stonedb::dbhandler::SDB_HandleSelect(THD * thd, LEX * lex, Query_result *& result, ulong setup_tables_done_option, int & res, int & optimize_after_sdb, int & sdb_free_join, int with_insert) (\opt\litaihong\stonedb\storage\stonedb\handler\ha_rcengine.cpp:82)
    mysql_execute_command(THD * thd, bool first_level) (\opt\litaihong\stonedb\sql\sql_parse.cc:3265)
    mysql_parse(THD * thd, Parser_state * parser_state) (\opt\litaihong\stonedb\sql\sql_parse.cc:5621)
    dispatch_command(THD * thd, const COM_DATA * com_data, enum_server_command command) (\opt\litaihong\stonedb\sql\sql_parse.cc:1495)
    do_command(THD * thd) (\opt\litaihong\stonedb\sql\sql_parse.cc:1034)
    handle_connection(void * arg) (\opt\litaihong\stonedb\sql\conn_handler\connection_handler_per_thread.cc:313)
    pfs_spawn_thread(void * arg) (\opt\litaihong\stonedb\storage\perfschema\pfs.cc:2197)
    libpthread.so.0!start_thread (未知源:0)
    libc.so.6!clone (未知源:0)

[lylth]

Query_result_create::prepare (sql_insert.cc:2753) function prepare phase and optimize phase are called twice

1. The stonedb::core::Engine::HandleSelect function is called first
   st_select_lex_unit::prepare->prepare_fake_select_lex->Query_result_create::prepare
   At this point create_table->table is NULL.
2. Then call
   st_select_lex_unit::optimize_for_stonedb -> prepare_fake_select_lex -> Query_result_create::prepare
   At this time, create_table->table is not NULL, and assert(create_table->table == NULL); causes the database to exit abnormally.

[lylth]

mysql stack

Query_result_create::prepare(Query_result_create * const this, List<Item> & values, SELECT_LEX_UNIT * u) (\opt\litaihong\stonedb\sql\sql_insert.cc:2750)
st_select_lex::prepare(st_select_lex * const this, THD * thd) (\opt\litaihong\stonedb\sql\sql_resolver.cc:361)
st_select_lex_unit::prepare_fake_select_lex(st_select_lex_unit * const this, THD * thd_arg) (\opt\litaihong\stonedb\sql\sql_union.cc:441)
st_select_lex_unit::prepare(st_select_lex_unit * const this, THD * thd_arg, Query_result * sel_result, ulonglong added_options, ulonglong removed_options) (\opt\litaihong\stonedb\sql\sql_union.cc:670)
handle_query(THD * thd, LEX * lex, Query_result * result, ulonglong added_options, ulonglong removed_options, int optimize_after_bh, int free_join_from_bh) (\opt\litaihong\stonedb\sql\sql_select.cc:150)
mysql_execute_command(THD * thd, bool first_level) (\opt\litaihong\stonedb\sql\sql_parse.cc:3266)
mysql_parse(THD * thd, Parser_state * parser_state) (\opt\litaihong\stonedb\sql\sql_parse.cc:5621)
dispatch_command(THD * thd, const COM_DATA * com_data, enum_server_command command) (\opt\litaihong\stonedb\sql\sql_parse.cc:1495)
do_command(THD * thd) (\opt\litaihong\stonedb\sql\sql_parse.cc:1034)
handle_connection(void * arg) (\opt\litaihong\stonedb\sql\conn_handler\connection_handler_per_thread.cc:313)
pfs_spawn_thread(void * arg) (\opt\litaihong\stonedb\storage\perfschema\pfs.cc:2197)
libpthread.so.0!start_thread (未知源:0)
libc.so.6!clone (未知源:0)

stonedb does not support CTAS, two solutions are recommended

1. Modify the function that throws an exception Query_result_create::prepare (sql_insert.cc:2753) function
   assert(create_table->table == NULL);
2. Rewrite the st_select_lex_unit::optimize_for_stonedb function of stonedb. The changes are relatively large and involve many functions.

[xiaoguangye]

ADD test cases to cover these ctas queries for the following reason.

1: From code perspective. There are enough evidences that we want to support these ctas.
2: Our fix can cover these related queries.

CREATE TABLE t2 AS SELECT LEFT(f1,171) AS f2 FROM t1 UNION SELECT LEFT(f1,171) AS f2 FROM t1;
CREATE TABLE t3 AS SELECT t1.f1 AS f3 FROM t1 LEFT JOIN t2 ON t1.f1 = t2.f2;
CREATE TABLE t4 AS SELECT t1.f1 AS f4 FROM t1 INNER JOIN t2 ON t1.f1 = t2.f2;
CREATE TABLE t5 AS SELECT t1.f1 AS f5 FROM t1 RIGHT JOIN t2 ON t1.f1 = t2.f2;
CREATE TABLE t6 AS SELECT t1.f1 AS f6 FROM t1 UNION SELECT t2.f2 AS f6 FROM t2 LEFT JOIN t3 ON t2.f2 = t3.f3;
CREATE TABLE t7 AS SELECT t1.f1 AS f7 FROM t1 UNION SELECT t2.f2 AS f7 FROM t2 INNER JOIN t3 ON t2.f2 = t3.f3;
CREATE TABLE t8 AS SELECT t1.f1 AS f8 FROM t1 UNION SELECT t2.f2 AS f8 FROM t2 RIGHT JOIN t3 ON t2.f2 = t3.f3;
CREATE TABLE t9 AS SELECT t1.f1 AS f9 FROM t1 INNER JOIN t3 ON t1.f1 = t3.f3 UNION SELECT t2.f2 AS f9 FROM t2;

[hustjieke]

Fixed.