[go: up one dir, main page]
More Web Proxy on the site http://driver.im/
Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Unexpected result when RIGHT JOIN a subquery #16951

Closed
suyZhong opened this issue Nov 11, 2024 · 6 comments · Fixed by #17087
Closed

Unexpected result when RIGHT JOIN a subquery #16951

suyZhong opened this issue Nov 11, 2024 · 6 comments · Fixed by #17087
Assignees
@mkleen
Labels
bug Clear identification of incorrect behaviour

Comments

@suyZhong
Copy link

CrateDB version

CrateDB 5.10.0-SNAPSHOT built 87c6dfa

CrateDB setup information

Manual build following the instructions here: https://github.com/crate/crate/blob/master/devs/docs/basics.rst

Problem description

Consider the following test case. The third query returns an unexpected result: The second query returns NULL NULL 1, and the third query with the filter which is the negation should return an empty set. However, one row is fetched.

Besides, PostgreSQL returns '' NULL 1 for the first query.

Steps to Reproduce

DROP TABLE IF EXISTS t0;
DROP TABLE IF EXISTS t1;

CREATE  TABLE  t0(c1 VARCHAR(500));
CREATE  TABLE  t1(c0 VARCHAR(500));
INSERT INTO t0(c1) VALUES ('');
REFRESH TABLE t0;



SELECT * FROM t0, t1 RIGHT  JOIN  (SELECT 1 ) AS sub0  ON true; 
-- CrateDB: NULL NULL 1 
-- Postgres: '' NULL 1

SELECT * FROM t0, t1 RIGHT  JOIN  (SELECT 1 ) AS sub0  ON true WHERE ((t0.c1)>=(t0.c1)); -- NULL NULL 1
SELECT * FROM t0, t1 RIGHT  JOIN  (SELECT 1 ) AS sub0  ON true WHERE (NOT ((t0.c1)>=(t0.c1))); 
-- Expected: empty result
-- Actual: NULL NULL 1

Actual Result

As mentioned above

Expected Result

As mentioned above.
@suyZhong suyZhong added the triage An issue that needs to be triaged by a maintainer label Nov 11, 2024
@matriv
Copy link
Contributor
matriv commented Nov 12, 2024

Tested with 5.8.5 and we get the same results, so seems not related to the Hash join optimization recently added to master.

@matriv matriv self-assigned this Nov 12, 2024
@matriv
Copy link
Contributor
matriv commented Nov 13, 2024 •
edited
Loading

For the first query without the filtering

if I insert value 'foo' in t0 instead of '':

matriv=# insert into t0 values('foo');
INSERT 0 1
matriv=# SELECT * FROM t0, t1 RIGHT  JOIN  (SELECT 1 ) AS sub0  ON true;
 c1  | c0 | ?column?
-----+----+----------
 foo |    |        1
(1 row)

matriv=# select * from t0;
 c1
-----
 foo
(1 row)

matriv=# select * from t1;
 c0
----
(0 rows)

matriv=# select * from t0, t1;
 c1 | c0
----+----
(0 rows)

matriv=# SELECT * FROM t0, t1 RIGHT  JOIN  (SELECT 1 ) AS sub0  ON true;
 c1  | c0 | ?column?
-----+----+----------
 foo |    |        1
(1 row)

matriv=# SELECT * FROM (SELECT * FROM t0, t1) as sub1 RIGHT  JOIN  (SELECT 1 ) AS sub2  ON true;
 c1 | c0 | ?column?
----+----+----------
    |    |        1
(1 row)

So it seems that postgres gives precedence to t1 RIGHT JOIN (SELECT 1), where as CrateDB follows the left-to-right order of the joins. Need to check with SQL standard if this is defined.

Additionally, for postgres it seems that when t0 & t1 are joined with the explicit CROSS JOIN keyword, then the query is evaluated with left-to-right precedence, so the FROM t0, t1 join expression seems to have lower precedence than the explicit JOIN keywords:

matriv=# SELECT * FROM t0 CROSS JOIN t1 RIGHT  JOIN  (SELECT 1 ) AS sub0  ON true;
 c1 | c0 | ?column?
----+----+----------
    |    |        1
(1 row)

or:

matriv=# SELECT * FROM t0 JOIN t1 ON TRUE RIGHT  JOIN  (SELECT 1 ) AS sub0  ON true;
 c1 | c0 | ?column?
----+----+----------
    |    |        1
(1 row)

@matriv
Copy link
Contributor
matriv commented Nov 13, 2024 •
edited
Loading

Haven't found anything in SQL specification about this behavior. From postgres:
https://www.postgresql.org/docs/current/tutorial-join.html:

This syntax pre-dates the JOIN/ON syntax, which was introduced in SQL-92. The tables are simply listed in the FROM clause, and the comparison expression is added to the WHERE clause. The results from this older implicit syntax and the newer explicit JOIN/ON syntax are identical. But for a reader of the query, the explicit syntax makes its meaning easier to understand: The join condition is introduced by its own key word whereas previously the condition was mixed into the WHERE clause together with other conditions.

but this seems to not be true based on the example in the previous comment.
additionally: https://www.postgresql.org/docs/17/explicit-joins.html

Explicit inner join syntax (INNER JOIN, CROSS JOIN, or unadorned JOIN) is semantically the same as listing the input relations in FROM, so it does not constrain the join order.

also contradicts with the behavior.

@matriv
Copy link
Contributor
matriv commented Nov 13, 2024 •
edited
Loading

For the second query with the filtering

Postgres (using the syntax to ensure the same join order):

matriv=# SELECT * FROM t0 JOIN t1 ON TRUE RIGHT JOIN (SELECT 1) AS sub0  ON true WHERE ((t0.c1)>=(t0.c1));
 c1 | c0 | ?column?
----+----+----------
(0 rows)

matriv=# explain verbose SELECT * FROM t0 JOIN t1 ON TRUE RIGHT JOIN (SELECT 1) AS sub0  ON true WHERE ((t0.c1)>=(t0.c1));
                               QUERY PLAN
-------------------------------------------------------------------------
 Nested Loop  (cost=0.00..117.25 rows=7500 width=1036)
   Output: t0.c1, t1.c0, 1
   ->  Seq Scan on public.t1  (cost=0.00..11.50 rows=150 width=516)
         Output: t1.c0
   ->  Materialize  (cost=0.00..12.12 rows=50 width=516)
         Output: t0.c1
         ->  Seq Scan on public.t0  (cost=0.00..11.88 rows=50 width=516)
               Output: t0.c1
               Filter: ((t0.c1)::text >= (t0.c1)::text)
(9 rows)

matriv=# select * from t0 where ((t0.c1)>=(t0.c1));
 c1
-----
 foo


matriv=# SELECT * FROM t0 JOIN t1 ON TRUE RIGHT JOIN (SELECT 1) AS sub0  ON true WHERE NOT ((t0.c1)>=(t0.c1));
 c1 | c0 | ?column?
----+----+----------
(0 rows)

matriv=# explain verbose SELECT * FROM t0 JOIN t1 ON TRUE RIGHT JOIN (SELECT 1) AS sub0  ON true WHERE NOT ((t0.c1)>=(t0.c1));
                               QUERY PLAN
-------------------------------------------------------------------------
 Nested Loop  (cost=0.00..117.25 rows=7500 width=1036)
   Output: t0.c1, t1.c0, 1
   ->  Seq Scan on public.t1  (cost=0.00..11.50 rows=150 width=516)
         Output: t1.c0
   ->  Materialize  (cost=0.00..12.12 rows=50 width=516)
         Output: t0.c1
         ->  Seq Scan on public.t0  (cost=0.00..11.88 rows=50 width=516)
               Output: t0.c1
               Filter: ((t0.c1)::text < (t0.c1)::text)
(9 rows)

matriv=# select * from t0 where NOT ((t0.c1)>=(t0.c1));
 c1
----
(0 rows)

CrateDB:

cr> SELECT * FROM t0 JOIN t1 ON TRUE RIGHT JOIN (SELECT 1) AS sub0  ON true WHERE ((t0.c1)>=(t0.c1));
+------+------+---+
| c1   | c0   | 1 |
+------+------+---+
| NULL | NULL | 1 |
+------+------+---+
SELECT 1 row in set (0.010 sec)
cr> explain verbose SELECT * FROM t0 JOIN t1 ON TRUE RIGHT JOIN (SELECT 1) AS sub0  ON true WHERE ((t0.c1)>=(t0.c1));
+------------------------------------------------------+-------------------------------------------------------------+
| STEP                                                 | QUERY PLAN                                                  |
+------------------------------------------------------+-------------------------------------------------------------+
| Initial logical plan                                 | Join[RIGHT | true] (rows=unknown)                           |
|                                                      |   ├ Filter[(c1 >= c1)] (rows=0)                             |
|                                                      |   │  └ Join[INNER | true] (rows=unknown)                    |
|                                                      |   │    ├ Collect[doc.t0 | [c1] | true] (rows=unknown)       |
|                                                      |   │    └ Collect[doc.t1 | [c0] | true] (rows=unknown)       |
|                                                      |   └ Rename["1"] AS sub0 (rows=unknown)                      |
|                                                      |     └ Eval[1] (rows=unknown)                                |
|                                                      |       └ TableFunction[empty_row | [] | true] (rows=unknown) |
| optimizer_move_constant_join_conditions_beneath_join | Join[RIGHT | true] (rows=unknown)                           |
|                                                      |   ├ Filter[(c1 >= c1)] (rows=0)                             |
|                                                      |   │  └ Join[INNER | true] (rows=unknown)                    |
|                                                      |   │    ├ Collect[doc.t0 | [c1] | true] (rows=unknown)       |
|                                                      |   │    └ Collect[doc.t1 | [c0] | true] (rows=unknown)       |
|                                                      |   └ Rename["1"] AS sub0 (rows=unknown)                      |
|                                                      |     └ Eval[1] (rows=unknown)                                |
|                                                      |       └ TableFunction[empty_row | [] | true] (rows=unknown) |
| optimizer_rewrite_join_plan                          | NestedLoopJoin[RIGHT | true] (rows=unknown)                 |
|                                                      |   ├ Filter[(c1 >= c1)] (rows=0)                             |
|                                                      |   │  └ Join[INNER | true] (rows=unknown)                    |
|                                                      |   │    ├ Collect[doc.t0 | [c1] | true] (rows=unknown)       |
|                                                      |   │    └ Collect[doc.t1 | [c0] | true] (rows=unknown)       |
|                                                      |   └ Rename["1"] AS sub0 (rows=unknown)                      |
|                                                      |     └ Eval[1] (rows=unknown)                                |
|                                                      |       └ TableFunction[empty_row | [] | true] (rows=unknown) |
| optimizer_move_filter_beneath_join                   | NestedLoopJoin[RIGHT | true] (rows=unknown)                 |
|                                                      |   ├ Join[INNER | true] (rows=unknown)                       |
|                                                      |   │  ├ Filter[(c1 >= c1)] (rows=0)                          |
|                                                      |   │  │  └ Collect[doc.t0 | [c1] | true] (rows=unknown)      |
|                                                      |   │  └ Collect[doc.t1 | [c0] | true] (rows=unknown)         |
|                                                      |   └ Rename["1"] AS sub0 (rows=unknown)                      |
|                                                      |     └ Eval[1] (rows=unknown)                                |
|                                                      |       └ TableFunction[empty_row | [] | true] (rows=unknown) |
| optimizer_move_constant_join_conditions_beneath_join | NestedLoopJoin[RIGHT | true] (rows=unknown)                 |
|                                                      |   ├ Join[INNER | true] (rows=unknown)                       |
|                                                      |   │  ├ Filter[(c1 >= c1)] (rows=0)                          |
|                                                      |   │  │  └ Collect[doc.t0 | [c1] | true] (rows=unknown)      |
|                                                      |   │  └ Collect[doc.t1 | [c0] | true] (rows=unknown)         |
|                                                      |   └ Rename["1"] AS sub0 (rows=unknown)                      |
|                                                      |     └ Eval[1] (rows=unknown)                                |
|                                                      |       └ TableFunction[empty_row | [] | true] (rows=unknown) |
| optimizer_rewrite_join_plan                          | NestedLoopJoin[RIGHT | true] (rows=unknown)                 |
|                                                      |   ├ NestedLoopJoin[INNER | true] (rows=unknown)             |
|                                                      |   │  ├ Filter[(c1 >= c1)] (rows=0)                          |
|                                                      |   │  │  └ Collect[doc.t0 | [c1] | true] (rows=unknown)      |
|                                                      |   │  └ Collect[doc.t1 | [c0] | true] (rows=unknown)         |
|                                                      |   └ Rename["1"] AS sub0 (rows=unknown)                      |
|                                                      |     └ Eval[1] (rows=unknown)                                |
|                                                      |       └ TableFunction[empty_row | [] | true] (rows=unknown) |
| optimizer_merge_filter_and_collect                   | NestedLoopJoin[RIGHT | true] (rows=unknown)                 |
|                                                      |   ├ NestedLoopJoin[INNER | true] (rows=unknown)             |
|                                                      |   │  ├ Collect[doc.t0 | [c1] | (c1 >= c1)] (rows=unknown)   |
|                                                      |   │  └ Collect[doc.t1 | [c0] | true] (rows=unknown)         |
|                                                      |   └ Rename["1"] AS sub0 (rows=unknown)                      |
|                                                      |     └ Eval[1] (rows=unknown)                                |
|                                                      |       └ TableFunction[empty_row | [] | true] (rows=unknown) |
| optimizer_remove_redundant_eval                      | TableFunction[empty_row | [1] | true] (rows=unknown)        |
| Final logical plan                                   | NestedLoopJoin[RIGHT | true] (rows=unknown)                 |
|                                                      |   ├ NestedLoopJoin[INNER | true] (rows=unknown)             |
|                                                      |   │  ├ Collect[doc.t0 | [c1] | (c1 >= c1)] (rows=unknown)   |
|                                                      |   │  └ Collect[doc.t1 | [c0] | true] (rows=unknown)         |
|                                                      |   └ Rename["1"] AS sub0 (rows=unknown)                      |
|                                                      |     └ TableFunction[empty_row | [1] | true] (rows=unknown)  |
+------------------------------------------------------+-------------------------------------------------------------+
EXPLAIN 9 rows in set (0.009 sec)

cr> select * from t0 WHERE ((t0.c1)>=(t0.c1));
+-----+
| c1  |
+-----+
| foo |
+-----+
SELECT 1 row in set (0.010 sec)



cr> SELECT * FROM t0 JOIN t1 ON TRUE RIGHT JOIN (SELECT 1) AS sub0  ON true WHERE NOT ((t0.c1)>=(t0.c1));
+------+------+---+
| c1   | c0   | 1 |
+------+------+---+
| NULL | NULL | 1 |
+------+------+---+
SELECT 1 row in set (0.014 sec)
cr> explain verbose SELECT * FROM t0 JOIN t1 ON TRUE RIGHT JOIN (SELECT 1) AS sub0  ON true WHERE NOT ((t0.c1)>=(t0.c1));
+------------------------------------------------------+-----------------------------------------------------------------+
| STEP                                                 | QUERY PLAN                                                      |
+------------------------------------------------------+-----------------------------------------------------------------+
| Initial logical plan                                 | Join[RIGHT | true] (rows=unknown)                               |
|                                                      |   ├ Filter[(NOT (c1 >= c1))] (rows=0)                           |
|                                                      |   │  └ Join[INNER | true] (rows=unknown)                        |
|                                                      |   │    ├ Collect[doc.t0 | [c1] | true] (rows=unknown)           |
|                                                      |   │    └ Collect[doc.t1 | [c0] | true] (rows=unknown)           |
|                                                      |   └ Rename["1"] AS sub0 (rows=unknown)                          |
|                                                      |     └ Eval[1] (rows=unknown)                                    |
|                                                      |       └ TableFunction[empty_row | [] | true] (rows=unknown)     |
| optimizer_move_constant_join_conditions_beneath_join | Join[RIGHT | true] (rows=unknown)                               |
|                                                      |   ├ Filter[(NOT (c1 >= c1))] (rows=0)                           |
|                                                      |   │  └ Join[INNER | true] (rows=unknown)                        |
|                                                      |   │    ├ Collect[doc.t0 | [c1] | true] (rows=unknown)           |
|                                                      |   │    └ Collect[doc.t1 | [c0] | true] (rows=unknown)           |
|                                                      |   └ Rename["1"] AS sub0 (rows=unknown)                          |
|                                                      |     └ Eval[1] (rows=unknown)                                    |
|                                                      |       └ TableFunction[empty_row | [] | true] (rows=unknown)     |
| optimizer_rewrite_join_plan                          | NestedLoopJoin[RIGHT | true] (rows=unknown)                     |
|                                                      |   ├ Filter[(NOT (c1 >= c1))] (rows=0)                           |
|                                                      |   │  └ Join[INNER | true] (rows=unknown)                        |
|                                                      |   │    ├ Collect[doc.t0 | [c1] | true] (rows=unknown)           |
|                                                      |   │    └ Collect[doc.t1 | [c0] | true] (rows=unknown)           |
|                                                      |   └ Rename["1"] AS sub0 (rows=unknown)                          |
|                                                      |     └ Eval[1] (rows=unknown)                                    |
|                                                      |       └ TableFunction[empty_row | [] | true] (rows=unknown)     |
| optimizer_move_filter_beneath_join                   | NestedLoopJoin[RIGHT | true] (rows=unknown)                     |
|                                                      |   ├ Join[INNER | true] (rows=unknown)                           |
|                                                      |   │  ├ Filter[(NOT (c1 >= c1))] (rows=0)                        |
|                                                      |   │  │  └ Collect[doc.t0 | [c1] | true] (rows=unknown)          |
|                                                      |   │  └ Collect[doc.t1 | [c0] | true] (rows=unknown)             |
|                                                      |   └ Rename["1"] AS sub0 (rows=unknown)                          |
|                                                      |     └ Eval[1] (rows=unknown)                                    |
|                                                      |       └ TableFunction[empty_row | [] | true] (rows=unknown)     |
| optimizer_move_constant_join_conditions_beneath_join | NestedLoopJoin[RIGHT | true] (rows=unknown)                     |
|                                                      |   ├ Join[INNER | true] (rows=unknown)                           |
|                                                      |   │  ├ Filter[(NOT (c1 >= c1))] (rows=0)                        |
|                                                      |   │  │  └ Collect[doc.t0 | [c1] | true] (rows=unknown)          |
|                                                      |   │  └ Collect[doc.t1 | [c0] | true] (rows=unknown)             |
|                                                      |   └ Rename["1"] AS sub0 (rows=unknown)                          |
|                                                      |     └ Eval[1] (rows=unknown)                                    |
|                                                      |       └ TableFunction[empty_row | [] | true] (rows=unknown)     |
| optimizer_rewrite_join_plan                          | NestedLoopJoin[RIGHT | true] (rows=unknown)                     |
|                                                      |   ├ NestedLoopJoin[INNER | true] (rows=unknown)                 |
|                                                      |   │  ├ Filter[(NOT (c1 >= c1))] (rows=0)                        |
|                                                      |   │  │  └ Collect[doc.t0 | [c1] | true] (rows=unknown)          |
|                                                      |   │  └ Collect[doc.t1 | [c0] | true] (rows=unknown)             |
|                                                      |   └ Rename["1"] AS sub0 (rows=unknown)                          |
|                                                      |     └ Eval[1] (rows=unknown)                                    |
|                                                      |       └ TableFunction[empty_row | [] | true] (rows=unknown)     |
| optimizer_merge_filter_and_collect                   | NestedLoopJoin[RIGHT | true] (rows=unknown)                     |
|                                                      |   ├ NestedLoopJoin[INNER | true] (rows=unknown)                 |
|                                                      |   │  ├ Collect[doc.t0 | [c1] | (NOT (c1 >= c1))] (rows=unknown) |
|                                                      |   │  └ Collect[doc.t1 | [c0] | true] (rows=unknown)             |
|                                                      |   └ Rename["1"] AS sub0 (rows=unknown)                          |
|                                                      |     └ Eval[1] (rows=unknown)                                    |
|                                                      |       └ TableFunction[empty_row | [] | true] (rows=unknown)     |
| optimizer_remove_redundant_eval                      | TableFunction[empty_row | [1] | true] (rows=unknown)            |
| Final logical plan                                   | NestedLoopJoin[RIGHT | true] (rows=unknown)                     |
|                                                      |   ├ NestedLoopJoin[INNER | true] (rows=unknown)                 |
|                                                      |   │  ├ Collect[doc.t0 | [c1] | (NOT (c1 >= c1))] (rows=unknown) |
|                                                      |   │  └ Collect[doc.t1 | [c0] | true] (rows=unknown)             |
|                                                      |   └ Rename["1"] AS sub0 (rows=unknown)                          |
|                                                      |     └ TableFunction[empty_row | [1] | true] (rows=unknown)      |
+------------------------------------------------------+-----------------------------------------------------------------+
EXPLAIN 9 rows in set (0.007 sec)

cr> select * from t0 WHERE NOT ((t0.c1)>=(t0.c1));
+----+
| c1 |
+----+
+----+
SELECT 0 rows in set (0.012 sec)

There could be an issue with CrateDB and the fact that the filtering in WHERE clause is pushed down to the Collect, where it seems it should stay on top of everything? (after the right join) but:

  1. postgres seems to not do that, but filter on the t0,t1 join, so I don't get why it gives different results
  2. with CrateDB it's clear (from explain verbose) that even if we disable any rules, the filtering will remain beneath the right join so it won't filter out any rows.

@matriv
Copy link
Contributor
matriv commented Nov 13, 2024

After some more investigation together with @mkleen (thx), the issue drills down to the NL Join iterators, for the RIGHT JOIN:

cr> SELECT * FROM t0 JOIN t1 ON TRUE RIGHT JOIN (SELECT 1) AS sub0  ON true WHERE NOT ((t0.c1)>=(t0.c1));
+------+------+---+
| c1   | c0   | 1 |
+------+------+---+
| NULL | NULL | 1 |
+------+------+---+
SELECT 1 row in set (95.062 sec)

it shouldn't return anything, as the left side (j0 JOIN t1 ON true) returns 0 rows if run standalone, but it seems that the iterator emits all rows from the right side. more visible with this example:

cr> SELECT * FROM t0 JOIN t1 ON TRUE RIGHT JOIN (SELECT * from generate_series(1, 10, 1)) AS sub0  ON true WHERE NOT ((t0.c1)>=(t0.c1));
+------+------+-----------------+
| c1   | c0   | generate_series |
+------+------+-----------------+
| NULL | NULL |               1 |
| NULL | NULL |               2 |
| NULL | NULL |               3 |
| NULL | NULL |               4 |
| NULL | NULL |               5 |
| NULL | NULL |               6 |
| NULL | NULL |               7 |
| NULL | NULL |               8 |
| NULL | NULL |               9 |
| NULL | NULL |              10 |
+------+------+-----------------+
SELECT 10 rows in set (2.295 sec)

@matriv
Copy link
Contributor
matriv commented Nov 13, 2024

Checking this more, I think the previous statement is not correct.

psql:

matriv=# SELECT * FROM t0 JOIN t1 ON TRUE RIGHT JOIN (SELECT * from generate_series(1, 10, 1)) AS sub0  ON true;
 c1 | c0 | generate_series
----+----+-----------------
    |    |               1
    |    |               2
    |    |               3
    |    |               4
    |    |               5
    |    |               6
    |    |               7
    |    |               8
    |    |               9
    |    |              10
(10 rows)

or simpler (since t1 has no rows):

matriv=# SELECT * FROM t1 RIGHT JOIN (SELECT * from generate_series(1, 10, 1) as b) AS sub0  ON true;
 c0 | b
----+----
    |  1
    |  2
    |  3
    |  4
    |  5
    |  6
    |  7
    |  8
    |  9
    | 10
(10 rows)

so we need to emit even if the left table (or subselect) returns 0 rows.

So we turn back to the original issue that the NOT ((t0.c1)>=(t0.c1)) for psql seems to be applied after the RIGHT join, and then it eliminates all rows:

matriv=# SELECT * FROM t0 JOIN t1 ON TRUE RIGHT JOIN (SELECT * from generate_series(1, 10, 1)) AS sub0  ON true WHERE NOT ((t0.c1)>=(t0.c1));
 c1 | c0 | generate_series
----+----+-----------------
(0 rows)

So for CrateDB, not only we shouldn't push down that filter to Collect of t0, but we should keep it after the right join operation.
Currently we have the issue, that even in the original logical plan the filter is already under the right join:

cr> explain verbose SELECT * FROM t0,t1 RIGHT JOIN (SELECT 1) AS sub0  ON true WHERE NOT ((t0.c1)>=(t0.c1));
+------------------------------------------------------+-----------------------------------------------------------------+
| STEP                                                 | QUERY PLAN                                                      |
+------------------------------------------------------+-----------------------------------------------------------------+
| Initial logical plan                                 | Join[RIGHT | true] (rows=unknown)                               |
|                                                      |   ├ Filter[(NOT (c1 >= c1))] (rows=0)                           |
|                                                      |   │  └ Join[CROSS] (rows=unknown)                               |
|                                                      |   │    ├ Collect[doc.t0 | [c1] | true] (rows=unknown)           |
|                                                      |   │    └ Collect[doc.t1 | [c0] | true] (rows=unknown)           |
|                                                      |   └ Rename["1"] AS sub0 (rows=unknown)                          |
|                                                      |     └ Eval[1] (rows=unknown)                                    |
|                                                      |       └ TableFunction[empty_row | [] | true] (rows=unknown)     |
| optimizer_move_constant_join_conditions_beneath_join | Join[RIGHT | true] (rows=unknown)                               |
...

@matriv matriv added bug Clear identification of incorrect behaviour and removed triage An issue that needs to be triaged by a maintainer labels Nov 13, 2024
@matriv matriv removed their assignment Nov 13, 2024
@mkleen mkleen mentioned this issue Nov 14, 2024
Closed
4 tasks
@mkleen mkleen mentioned this issue Nov 29, 2024
Merged
4 tasks
@mergify mergify bot closed this as completed in #17087 Nov 29, 2024
@mergify mergify bot mentioned this issue Nov 29, 2024
Merged
4 tasks
@suyZhong suyZhong mentioned this issue Nov 30, 2024
Open
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@mkleen mkleen

Labels
bug Clear identification of incorrect behaviour
Projects
None yet
Milestone
No milestone
3 participants
@mkleen @matriv @suyZhong