Join not recognized between two row_number() columns

[swingbit]

Describe the bug
When joining two columns that were generated with row_number(), a select over cartesian product is generated instead of join.

To Reproduce

create table a(i int);
create table b(i int);

insert into a values (10), (10);
insert into b values (20), (20);

plan
with
  a1 as (select row_number() over() as r, i from a),
  b1 as (select row_number() over() as r, i from b)
select a1.i, b1.i
from a1, b1
where a1.r = b1.r;

outputs:

+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| rel                                                                                                                                                                  |
+======================================================================================================================================================================+
| project (                                                                                                                                                            |
| | select (                                                                                                                                                           |
| | | crossproduct (                                                                                                                                                   |
| | | | project (                                                                                                                                                      |
| | | | | table("spinque"."a") [ "a"."i" MIN "10" MAX "10" NUNIQUES 1.000000 ] COUNT 2                                                                                 |
| | | | ) [ "sys"."row_number"("sys"."star"(), boolean(1) "false", boolean(1) "false") as "a1"."r", "a"."i" NUNIQUES 1.000000 MIN "10" MAX "10" as "a1"."i" ] COUNT 2, |
| | | | project (                                                                                                                                                      |
| | | | | table("spinque"."b") [ "b"."i" MIN "20" MAX "20" NUNIQUES 1.000000 ] COUNT 2                                                                                 |
| | | | ) [ "sys"."row_number"("sys"."star"(), boolean(1) "false", boolean(1) "false") as "b1"."r", "b"."i" NUNIQUES 1.000000 MIN "20" MAX "20" as "b1"."i" ] COUNT 2  |
| | | ) [  ] COUNT 4                                                                                                                                                   |
| | ) [ ("a1"."r") = ("b1"."r") ] COUNT 4                                                                                                                              |
| ) [ "a1"."i" NUNIQUES 1.000000 MIN "10" MAX "10", "b1"."i" NUNIQUES 1.000000 MIN "20" MAX "20" ] COUNT 4                                                             |
| # push_project_down                      0 actions 2 usec                                                                                                            |
| # merge_projects                         0 actions 2 usec                                                                                                            |
| # push_project_up                        0 actions 2 usec                                                                                                            |
| # split_project                          0 actions 2 usec                                                                                                            |
| # remove_redundant_join                  0 actions 1 usec                                                                                                            |
| # simplify_math                          0 actions 2 usec                                                                                                            |
| # optimize_exps                          0 actions 3 usec                                                                                                            |
| # optimize_select_and_joins_bottomup     0 actions 2 usec                                                                                                            |
| # project_reduce_casts                   0 actions 0 usec                                                                                                            |
| # optimize_projections                   0 actions 2 usec                                                                                                            |
| # optimize_joins                         0 actions 3 usec                                                                                                            |
| # join_order                             0 actions 8 usec                                                                                                            |
| # optimize_semi_and_anti                 0 actions 2 usec                                                                                                            |
| # optimize_select_and_joins_topdown      0 actions 4 usec                                                                                                            |
| # dce                                    0 actions 10 usec                                                                                                           |
| # push_func_and_select_down              0 actions 3 usec                                                                                                            |
| # get_statistics                         0 actions 54 usec                                                                                                           |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+

Software versions

• MonetDB version number 11.47.12
• OS and version: Fedora 38
• self-installed and compiled

[njnes]

fixed issue were the cardinality of the expression was wrong (causing it to become a select expression)

[swingbit]

@njnes thanks for the quick fix. Is it going to Jun2023 too?

[lrpereira]

Hey Roberto,

Fixed in Jun2023 (89309:a2f442519571)

Regards

[swingbit]

Super, thanks!