Actually this is a very interesting topic not only for the particular case
of NOT IN subqueries demonstrated at the beginning of this thread but for
other kinds of subqueries as well.
If I understood well the initial proposal of Haisheng is to introduce
another special kind of join at the logical
I am not sure I get your idea.
What will the logical plan and physical plan look like for the following query?
SELECT * FROM foo WHERE a NOT IN (SELECT b FROM bar); -- bar.b is nullable
On 2020/07/23 01:35:44, Julian Hyde wrote:
> How about a semi-join algorithm that adds column that hold the
How about a semi-join algorithm that adds column that hold the nature of the
match? This algorithm can be used to evaluate 3-valued IN and 3-valued NOT IN
queries.
Generalizing further, it could compute any “ANY (predicate)” or “ALL
(predicate)” condition as a column with values
Julian> Vladimir said he *expected* Oracle would implement (3-valued) NOT
IN efficiently. (Back in the day, when I was at Oracle, they certainly did
not.) Does anyone have any evidence that they do?
Well, Oracle has "null aware" joins since Oracle 11g which is more than 10
years old.
I have not
urity guard saying
>>>>>>> that it
>>>>>>> is supported. Not everyone requires the Enumerable operators to support
>>>>>>> everything. More importantly, currently there is no logic or rules to
>>>>>>> translate sub-query directly to S
f some day, some contributor extends Calcite's parser and
> > > > > > SubqueryRemovalRule to be able to transform NOT_IN subquery into
> > > > > > NOT IN
> > > > > > anti join, we still have chance to disable it. Is that still a
> > > > > > concern?
> > > > > > > >
> > > > > > > > There are many ways t
Just in case: Oracle DB has 'null aware' joins. For instance: 'hash join
anti na' which is a single join with two inputs.
I expect that databases can implement 'not in' semantics efficiently (at
least they should be more efficient than enumerable)
Vladimir
t; > > > > > > > Brainstorming: maybe we could consider it as a separate logical
> > > > > operator
> > > > > > > > (with its corresponding enumerable implementation)?
> > > > > > > It doesn't sound cool. It requires much more work. You have to
> >
th LogicalJoin,
> > > > and
> > > > for some rule that matches Join base class, you have to check it is a
> > > > LogicalJoin or the logical operator for ANTI_NOTIN.
> > > > > >
> > > > > > On 2020/07/20 08:28:42, Ruben Q L wrote:
> &g
> > > > > > logic, rules and enumerable implementations.
> > > > > >
> > > > > > Brainstorming: maybe we could consider it as a separate logical
> > > operator
> > > > > > (with its corresponding enumerable imp
>
> > > > > Brainstorming: maybe we could consider it as a separate logical
> > operator
> > > > > (with its corresponding enumerable implementation)?
> > > > >
> > > > >
> > > > > Le lun. 20 jui
sponding enumerable implementation)?
> > > >
> > > >
> > > > Le lun. 20 juil. 2020 à 06:08, Haisheng Yuan
> a
> > > > écrit :
> > > >
> > > > > I agree that NOT IN is toxic, and it is error-prone.
> > > >
is toxic, and it is error-prone.
> > > But you can't prevent people writing SQL with not in sub-queries, would
> > > you rather let optimizer generate inefficient plan?
> > >
> > > - Haisheng
> > >
> > >
people writing SQL with not in sub-queries, would
> > you rather let optimizer generate inefficient plan?
> >
> > - Haisheng
> >
> > --
> > 发件人：Julian Hyde
> > 日 期：2020年07月20日 11:56:35
> > 收件人：dev@
---
> 发件人：Julian Hyde
> 日 期：2020年07月20日 11:56:35
> 收件人：dev@calcite.apache.org
> 主 题：Re: [DISCUSS] New Join Type: ANTI_NOTIN
>
> Yuck!
>
> NOT IN is toxic. I'd rather keep it out of the algebra.
>
> On Sun, Jul 19, 2020 at 8:24 PM Haisheng Yuan wrote:
> &
收件人：dev@calcite.apache.org
主 题：Re: [DISCUSS] New Join Type: ANTI_NOTIN
Yuck!
NOT IN is toxic. I'd rather keep it out of the algebra.
On Sun, Jul 19, 2020 at 8:24 PM Haisheng Yuan wrote:
>
> Hi all,
>
> Currently, JoinRelType.ANTI only represents NOT_EXISTS subquery (thanks
Yuck!
NOT IN is toxic. I'd rather keep it out of the algebra.
On Sun, Jul 19, 2020 at 8:24 PM Haisheng Yuan wrote:
>
> Hi all,
>
> Currently, JoinRelType.ANTI only represents NOT_EXISTS subquery (thanks to
> Ruben for reminding).
> For some simple boolean context NOT_IN subquery, we can't
Hi all,
Currently, JoinRelType.ANTI only represents NOT_EXISTS subquery (thanks to
Ruben for reminding).
For some simple boolean context NOT_IN subquery, we can't transform it to ANTI
join. e.g.:
SELECT * FROM foo WHERE a NOT IN (SELECT b FROM bar); -- bar.b is nullable
Because if there is a
18 matches
Mail list logo
