felerius / hyrise Goto Github PK
View Code? Open in Web Editor NEWThis project forked from hyrise/hyrise
Hyrise is a research in-memory database.
Home Page: https://hpi.de/plattner/projects/hyrise.html
License: MIT License
This project forked from hyrise/hyrise
Hyrise is a research in-memory database.
Home Page: https://hpi.de/plattner/projects/hyrise.html
License: MIT License
It seems that our SubqueryToJoin rule has a conflict with the JoinReorderingRule because it generates cross-joins. Find out what exactly goes wrong, report it, and inform @mrzzzrm.
The LQP optimizer should use a join for queries like
SELECT *
FROM customer
WHERE c_acctbal < (
SELECT MIN(o_totalprice)
FROM orders
WHERE o_custkey = c_custkey
)
(Returns all customers who cannot afford any of their orders.)
The LQP optimizer should use a join for a query like
SELECT *
FROM customer
WHERE c_custkey IN (
SELECT o_custkey
FROM orders
WHERE o_totalprice > c_acctbal
)
(Returns all customers who have orders that they cannot afford.)
Example:
SELECT *
FROM customer
WHERE c_acctbal > (
SELECT SUM(a.x)
FROM (
SELECT AVG(o_totalprice) AS x
FROM orders
WHERE o_custkey = c_custkey
GROUP BY o_orderpriority
) AS a
)
(copy-paste for hyriseConsole: SELECT * FROM customer WHERE c_acctbal > (SELECT SUM(a.x) FROM (SELECT AVG(o_totalprice) AS x FROM orders WHERE o_custkey = c_custkey GROUP BY o_orderpriority) AS a)
)
Take inspiration from the deleted tests for the old reformulation rule (still present in git history)
!=
sqlite_testrunner_queries.sql
, and add a new table if necessaryImplement the approach as described in this paper.
extract_input_lqp_info
(NOT) IN
, (NOT) EXISTS
, comparisons(NOT) IN
with constant list (NoRewriteConstantIn
)NOT IN
(NoRewriteCorrelatedNotIn
)(NOT) IN
and comparison: Left operand must be a column expression (NoRewriteIfLeftOperandIsNotAColumn
, None that check for column expressions that are not table columns)(NOT) EXISTS
not changed (NoRewriteUncorrelatedExists
)uses_correlated_parameters
/assess_correlated_parameter_usage
NoRewriteIfJoinUsesCorrelatedParameter
)NoRewriteIfCorrelatedParameterInProjection
)try_to_extract_join_predicate
NoRewriteCorrelatedNestedIn
, NoRewriteCorrelatedNestedExists
, ...)NoRewriteIfCorrelatedParameterInPredicateOtherThanEqualsBelowAggregate
)adapt_aggregate_node
adapt_alias_node
adapt_projection_node
find_pullable_predicate_nodes
try_to_extract_join_predicate
, basically just tests that the correct value for is_below_aggregate
is passed to that method)NoRewriteIfCorrelatedParameterIsUsedBelowLimitNode
)copy_and_adapt_lqp
adapt_*
methodsSample query:
SELECT s_suppkey
FROM supplier
WHERE s_suppkey IN (
SELECT c_custkey
FROM customer
WHERE c_custkey IN (
SELECT o_custkey
FROM orders
WHERE o_totalprice > c_acctbal AND o_totalprice > c_custkey AND o_totalprice > s_suppkey
)
)
Single line version for pasting into hyriseConsole:
SELECT s_suppkey FROM supplier WHERE s_suppkey IN (SELECT c_custkey FROM customer WHERE c_custkey IN (SELECT o_custkey FROM orders WHERE o_totalprice > c_acctbal AND o_totalprice > c_custkey AND o_totalprice > s_suppkey))
Probably best done by extending their node_expressions to include all columns (since we join with a semi/anti join, we don't care about removing these columns again later).
SELECT COUNT(*) FROM orders WHERE o_orderkey < 150000000000000
overflows and returns 0 for TPCH (scale factor 0.1)SELECT o_totalprice AS o, o_totalprice FROM orders
returns two columns named o
(is this expected SQL behavior?)Test for different sizes for the subquery (1, 5, 150000)
compare_benchmarks.py
SELECT c_custkey
FROM customer
WHERE c_acctbal < (
SELECT MAX(o_totalprice)
FROM orders
WHERE o_custkey = c_custkey
)
returns all customers who have at least one order but cannot afford any of them.
We currently optimize it to an inner join that has c_acctbal < o_totalprice
as its predicate.
However, the query could also be written in an even more efficient way that uses an equi join:
SELECT c_custkey
FROM customer
JOIN orders ON o_custkey = c_custkey
GROUP BY c_custkey, c_acctbal
HAVING c_acctbal < MAX(o_totalprice)
Specifically for this case, it is possible to boost it even further:
SELECT DISTINCT c_custkey
FROM customer
JOIN orders ON o_custkey = c_custkey
WHERE c_acctbal < o_totalprice
The SubqueryToJoinRule
should apply these reformulation techniques when possible.
Only optimize if it is worth it (e.g., only replace NL-JOIN with Hash-JOIN if at least 100 iterations are expected to be performed)
The LQP optimizer should use a join for queries like
SELECT *
FROM customer
WHERE EXISTS (
SELECT o_custkey
FROM orders
WHERE o_custkey = c_custkey AND o_totalprice > c_acctbal
)
(Returns all customers who have orders that they cannot afford.)
OR
as soon as the multi-predicate join supports this (Issue)A declarative, efficient, and flexible JavaScript library for building user interfaces.
๐ Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. ๐๐๐
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google โค๏ธ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.