RingsC
commented
1 year ago The root cause maybe is not due to turn semijoin on. when it turns semijoin on, and run the query statement and it will lead to service coredump in debug mode. Therefore, if, the server runs in release mode, it's maybe not down, and returns wrong result.
#0 0x0000564b4d61758c in Tianmu::types::TianmuDataType::IsNull (this=0x0) at /home/xxx/workshop/stonedb-ver-1/storage/tianmu/types/tianmu_data_types.h:158
#1 0x0000564b4d614f2d in Tianmu::core::Engine::Convert (is_null=@0x7f7abe0f5bd4: 0, value=@0x7f7abe0f5be0: 140153439952744, tianmu_item=..., f_type=MYSQL_TYPE_LONGLONG, unsigned_flag=false) at /home/xxx/workshop/stonedb-ver-1/storage/tianmu/core/engine_convert.cpp:366
#2 0x0000564b4d6207f1 in Tianmu::core::ResultSender::SendRecord (this=0x564b53dd15c0, r=std::vector of length 1, capacity 1 = {...}) at /home/xxx/workshop/stonedb-ver-1/storage/tianmu/core/engine_results.cpp:342
#3 0x0000564b4d62015b in Tianmu::core::ResultSender::Send (this=0x564b53dd15c0, iter=...) at /home/xxx/workshop/stonedb-ver-1/storage/tianmu/core/engine_results.cpp:239
#4 0x0000564b4d93041d in Tianmu::core::AggregationAlgorithm::MultiDimensionalGroupByScan (this=0x7f7abe0f6500, gbw=..., limit=@0x7f7abe0f60c0: 358, offset=@0x7f7abe0f64d0: 0, sender=0x564b53dd15c0, limit_less_than_no_groups=false)
at /home/xxx/workshop/stonedb-ver-1/storage/tianmu/optimizer/aggregation_algorithm.cpp:413
#5 0x0000564b4d92f2b5 in Tianmu::core::AggregationAlgorithm::Aggregate (this=0x7f7abe0f6500, just_distinct=false, limit=@0x7f7abe0f64d8: -1, offset=@0x7f7abe0f64d0: 0, sender=0x564b53dd15c0)
at /home/xxx/workshop/stonedb-ver-1/storage/tianmu/optimizer/aggregation_algorithm.cpp:209
(gdb) p this
$1 = (const Tianmu::types::TianmuDataType * const) 0x0
(gdb) f 1
#1 0x000056031f8e0f2d in Tianmu::core::Engine::Convert (is_null=@0x7f311d42cbd4: 0, value=@0x7f311d42cbe0: 139835411030408, tianmu_item=..., f_type=MYSQL_TYPE_LONGLONG, unsigned_flag=false) at /home/xxx/workshop/stonedb-ver-1/storage/tianmu/core/engine_convert.cpp:366
366 if (tianmu_item.IsNull())
(gdb) p tianmu_item
$2 = (Tianmu::types::TianmuDataType &) <error reading variable>
(gdb)
tianmu_item is not a right var, and corrupted. and in TianmuDataType, this is 0x0.
in Query::BuildCondsIfPossible, only three types join type supported: JO_INNER, JO_LEFT, JO_RIGHT, JO_FULL.
sql/sql_resolver.cc file converts in statement to semi-join in sql layer, but tianmu only supports three types, That means the semi-join cannot be recognized properly.
The requirement of semi-join:
/*
Check if we're in subquery that is a candidate for flattening into a
semi-join (which is done in flatten_subqueries()). The requirements are:
0. Semi-join is enabled (cf. hints)
1. Subquery predicate is an IN/=ANY or EXISTS predicate
2. Subquery is a single query block (not a UNION, EXCEPT or INTERSECT)
3. Subquery is not grouped (explicitly or implicitly)
3x: outer aggregated expression are not accepted
4. Subquery does not use HAVING
5. Subquery does not use windowing functions
6. Subquery predicate is (a) in an ON/WHERE clause,
and (b) at the AND-top-level of that clause. Note for 6a:
Semijoin transformations of subqueries in ON cause the
join nests to no longer be acceptable as a join tree, which
disturbs the hypergraph optimizer, so we disable them
for that case (6x). However, we enable them when secondary
engine optimization is ON because it is easy to reject a
possible wrong plan when its not supporting nested loop
joins.
7. Parent query block accepts semijoins (i.e we are not in a subquery of
a single table UPDATE/DELETE (TODO: We should handle this at some
point by switching to multi-table UPDATE/DELETE)
8. We're not in a confluent table-less subquery, like "SELECT 1".
9. No execution method was already chosen (by a prepared statement)
10. Parent query block is not a confluent table-less query block.
11. Neither parent nor child query block has straight join.
12. Parent query block does not prohibit semi-join.
13. LHS of IN predicate is deterministic
14. The surrounding truth test, and the nullability of expressions,
are compatible with the conversion.
15. Antijoins are supported, or it's not an antijoin (it's a semijoin).
16. OFFSET starts from the first row and LIMIT is not 0.
*/and if transform failed, then it will converted to derived table. [from MySQL]
If semijoin failed, try a transformation to a derived table:
FROM ot WHERE ot.x IN (SELECT y FROM it1, it2)
=>
FROM ot LEFT JOIN (SELECT DISTINCT y FROM it1, it2) AS derived
ON ot.x=derived.y
WHERE derived.y IS NOT NULL.
Applicability constraints have numbers which are the same as in the list of
the previous block. Reasons may be different though.
1. Subquery predicate is an IN/=ANY or EXISTS predicate
2. Subquery is a single query block (not a UNION, INTERSECT or EXCEPT);
this is because a certain secondary engine has no support for setop
DISTINCT
3. If this is [NOT] EXISTS, there is no aggregation; see
transform_table_subquery_to_join_with_derived()
6. Subquery predicate is
6a. in WHERE clause (we have not implemented the transformation for the
ON clause)
6b. linked to the root of that clause with ANDs or ORs.
7. Parent query block accepts semijoins (i.e we are not in a subquery of
a single table UPDATE/DELETE (TODO: We should handle this at some
point by switching to multi-table UPDATE/DELETE)
9. No execution method was already chosen (by a prepared statement)
10. Parent select has tables, as we'll link to them with LEFT JOIN
12. Parent query block does not prohibit semi-join.
13. LHS of IN predicate is deterministic
14. The surrounding truth test, and the nullability of expressions,
are compatible with the conversion.
16. The left argument isn't a row (multi-column) subquery; it would lead
to creating conditions like WHERE (outer_subq) =
ROW(derived.col1,derived.col2), which would complicate code.
17. Certain other subquery transformations, incompatible with this one,
have not been done.│ 1298 JoinType Query::GetJoinTypeAndCheckExpr(uint outer_join, Item *on_expr) { │
│ 1299 if (outer_join) │
│ 1300 ASSERT(on_expr != 0, "on_expr shouldn't be null when outer_join != 0"); │
│ 1301 │
│ 1302 JoinType join_type; │
│ 1303 │
│ 1304 if ((outer_join & JOIN_TYPE_LEFT) && (outer_join & JOIN_TYPE_RIGHT)) │
│ 1305 join_type = JoinType::JO_FULL; │
│ 1306 else if (outer_join & JOIN_TYPE_LEFT) │
│ 1307 join_type = JoinType::JO_LEFT; │
│ 1308 else if (outer_join & JOIN_TYPE_RIGHT) │
│ 1309 join_type = JoinType::JO_RIGHT; │
│ 1310 else │
│ >1311 join_type = JoinType::JO_INNER; │
│ 1312 │
│ 1313 return join_type; │
│ 1314 } Tiamnu generates join clause according to which type we support. From the code, we only support FULL, LEFT, RIGHT, INNER.
adofsauron
Have you read the Contributing Guidelines on issues?
Please confirm if bug report does NOT exists already ?
Describe the problem
Expected behavior
No response
How To Reproduce
No response
Environment
./mysqld Ver 5.7.36-StoneDB-v1.0.3 for Linux on x86_64 (build-) build information as follow: Repository address: https://github.com/stoneatom/stonedb.git:stonedb-5.7-dev Branch name: stonedb-5.7-dev Last commit ID: 758bce8 Last commit time: Date: Mon Apr 10 19:46:20 2023 +0800 Build time: Date: Tue Apr 11 11:34:37 CST 2023
Are you interested in submitting a PR to solve the problem?