search

stoneatom / stonedb

StoneDB is an Open-Source MySQL HTAP and MySQL-Native DataBase for OLTP, Real-Time Analytics, a counterpart of MySQLHeatWave. (https://stonedb.io)
https://stonedb.io/
GNU General Public License v2.0
862 stars 139 forks source link

feat(mtr): Do some MTR tests for `roughly` option #1774

Open RingsC opened 1 year ago

RingsC commented 1 year ago

Is your feature request related to a problem? Please describe.

Adds some mtr cases for test roughly option. such as select roughly xxx from xxxx where xxx;

  current_txn_->ResetDisplay();  // switch display on
  query.SetRoughQuery(selects_list->active_options() & SELECT_ROUGHLY);

query_spec_option:
          STRAIGHT_JOIN       { $$= SELECT_STRAIGHT_JOIN; }
        | HIGH_PRIORITY       { $$= SELECT_HIGH_PRIORITY; }
        | DISTINCT            { $$= SELECT_DISTINCT; }
        | SQL_SMALL_RESULT    { $$= SELECT_SMALL_RESULT; }
        | SQL_BIG_RESULT      { $$= SELECT_BIG_RESULT; }
        | ROUGHLY_SYM         { $$= SELECT_ROUGHLY; }//TIANMU UPGRADE
        | SQL_BUFFER_RESULT   { $$= OPTION_BUFFER_RESULT; }
        | SQL_CALC_FOUND_ROWS { $$= OPTION_FOUND_ROWS; }
        | ALL                 { $$= SELECT_ALL; }
        ;

Describe the solution you'd like

Describe alternatives you've considered

Additional context

Downal commented 1 year ago

In the following test case, there exist some problems.

CREATE TABLE table_test(id INT, age INT, department INT, score DOUBLE) ENGINE=tianmu;
INSERT INTO table_test VALUES(1,20,1,81.1),(2,19,1,80.1),(3,21,1,82.1),(4,20,1,85.1),(5,21,2,83.1),(6,20,2,84.1),(7,19,2,81.1),(8,22,2,80.1),(9,21,3,80.1),(10,19,3,82.1),(11,20,3,83.1),(12,22,3,81.1);

Then the table_test is like this:

id age department score
1 20 1 81.1
2 19 1 80.1
3 21 1 82.1
4 20 1 85.1
5 21 2 83.1
6 20 2 84.1
7 19 2 81.1
8 22 2 80.1
9 21 3 80.1
10 19 3 82.1
11 20 3 83.1
12 22 3 81.1

1. Sublink —— Crash

SELECT ROUGHLY * FROM (SELECT * FROM table_test WHERE department=1) AS T WHERE age>19;

After the execution of this SQL, an error was got.

ERROR 2013 (HY000): Lost connection to MySQL server during query

Part of call stacks:

Protocol_text::store(Protocol_text * const this, double from, uint32 decimals, String * buffer) (/home/stonedb57/stonedb/sql/protocol_classic.cc:1414)
Item::send(Item * const this, Protocol * protocol, String * buffer) (/home/stonedb57/stonedb/sql/item.cc:7595)
THD::send_result_set_row(THD * const this, List<Item> * row_items) (/home/stonedb57/stonedb/sql/sql_class.cc:4757)
Query_result_send::send_data(Query_result_send * const this, List<Item> & items) (/home/stonedb57/stonedb/sql/sql_class.cc:2742)
Tianmu::core::ResultSender::SendRecord(Tianmu::core::ResultSender * const this, const std::vector<std::unique_ptr<Tianmu::types::TianmuDataType, std::default_delete<Tianmu::types::TianmuDataType> >, std::allocator<std::unique_ptr<Tianmu::types::TianmuDataType, std::default_delete<Tianmu::types::TianmuDataType> > > > & r) (/home/stonedb57/stonedb/storage/tianmu/core/engine_results.cpp:366)
Tianmu::core::ResultSender::Send(Tianmu::core::ResultSender * const this, Tianmu::core::TempTable::RecordIterator & iter) (/home/stonedb57/stonedb/storage/tianmu/core/engine_results.cpp:239)
Tianmu::core::ResultSender::Send(Tianmu::core::ResultSender * const this, Tianmu::core::TempTable * t) (/home/stonedb57/stonedb/storage/tianmu/core/engine_results.cpp:375)
Tianmu::core::TempTable::RoughAggregate(Tianmu::core::TempTable * const this, Tianmu::core::ResultSender * sender) (/home/stonedb57/stonedb/storage/tianmu/core/temp_table_roughquery.cpp:760)
Tianmu::core::TempTable::RoughMaterialize(Tianmu::core::TempTable * const this, bool in_subq, Tianmu::core::ResultSender * sender, bool lazy) (/home/stonedb57/stonedb/storage/tianmu/core/temp_table_roughquery.cpp:37)
Tianmu::core::Engine::Execute(Tianmu::core::Engine * const this, THD * thd, LEX * lex, Query_result * result_output, SELECT_LEX_UNIT * unit_for_union) (/home/stonedb57/stonedb/storage/tianmu/core/engine_execute.cpp:508)
Tianmu::core::Engine::HandleSelect(Tianmu::core::Engine * const this, THD * thd, LEX * lex, Query_result *& result, ulong setup_tables_done_option, int & res, int & is_optimize_after_tianmu, int & tianmu_free_join, int with_insert) (/home/stonedb57/stonedb/storage/tianmu/core/engine_execute.cpp:243)
Tianmu::DBHandler::ha_my_tianmu_query(THD * thd, LEX * lex, Query_result *& result_output, ulong setup_tables_done_option, int & res, int & is_optimize_after_tianmu, int & tianmu_free_join, int with_insert) (/home/stonedb57/stonedb/storage/tianmu/sql/ha_my_tianmu.cpp:95)
execute_sqlcom_select(THD * thd, TABLE_LIST * all_tables) (/home/stonedb57/stonedb/sql/sql_parse.cc:5204)
mysql_execute_command(THD * thd, bool first_level) (/home/stonedb57/stonedb/sql/sql_parse.cc:2847)
mysql_parse(THD * thd, Parser_state * parser_state) (/home/stonedb57/stonedb/sql/sql_parse.cc:5642)
dispatch_command(THD * thd, const COM_DATA * com_data, enum_server_command command) (/home/stonedb57/stonedb/sql/sql_parse.cc:1495)

2. Subquery —— Wrong results

SELECT ROUGHLY * FROM table_test WHERE id > (SELECT MAX(id) FROM table_test WHERE department=2);

The ROUGHLY query got the result as follow:

id age department score
1 19 1 80.1
12 22 3 85.1

However, a query without ROUGHLY may get:

id age department score
9 21 3 80.1
10 19 3 82.1
11 20 3 83.1
12 22 3 81.1

3.

SELECT ROUGHLY * FROM table_test WHERE id=2;
id age department score
2 19 1 80.1
2 22 3 85.1

4.

SELECT ROUGHLY * FROM table_test WHERE department=2;
id age department score
1 19 2 80.1
12 22 2 85.1