[
https://issues.apache.org/jira/browse/CALCITE-6521?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17872166#comment-17872166
]
EveyWu edited comment on CALCITE-6521 at 8/9/24 1:30 AM:
----------------------------------------------------------
[~julianhyde] Thanks for reply, I found that the above sql execution in calcite
is inconsistent with the results of the engine PostgreSQL or Presto, but I am
not sure whether it is the design of calcite.
Calcite's Cross Join Unnest is more biased towards cross join？The Calcite
execution result is consistent with the following sql in PostgreSQL.
{code:java}
select * from (
SELECT t1.*, t2.*
FROM
(SELECT unnest(ARRAY[2, 5]) AS num) t1
CROSS JOIN
(SELECT unnest(ARRAY['dog', 'cat', 'bird']) AS animal) t2
UNION
SELECT t3.*, t4.*
FROM
(SELECT unnest(ARRAY[7, 8, 9]) AS num) t3
CROSS JOIN
(SELECT unnest(ARRAY['cow', 'pig']) AS animal) t4
);
{code}
was (Author: eveywu):
[~julianhyde] Thanks for reply, I found that the above sql execution in calcite
is inconsistent with the results of the engine PostgreSQL or Presto, but I am
not sure whether it is the design of calcite.
Calcite's Cross Join Unnest is more biased towards cross join？The calcite
execution result is consistent with the following sql in PostgreSQL.
{code:java}
select * from (
SELECT t1.*, t2.*
FROM
(SELECT unnest(ARRAY[2, 5]) AS num) t1
CROSS JOIN
(SELECT unnest(ARRAY['dog', 'cat', 'bird']) AS animal) t2
UNION
SELECT t3.*, t4.*
FROM
(SELECT unnest(ARRAY[7, 8, 9]) AS num) t3
CROSS JOIN
(SELECT unnest(ARRAY['cow', 'pig']) AS animal) t4
);
{code}
The Result in PostgreSQL:
> CROSS JOIN UNNEST The results do not match expectations.
> --------------------------------------------------------
>
> Key: CALCITE-6521
> URL: https://issues.apache.org/jira/browse/CALCITE-6521
> Project: Calcite
> Issue Type: Bug
> Reporter: EveyWu
> Priority: Minor
> Attachments: image-2024-08-08-22-03-13-643.png
>
>
> {code:java}
> SELECT n, a
> FROM (
> VALUES
> (ARRAY[2, 5], ARRAY['dog', 'cat', 'bird']),
> (ARRAY[7, 8, 9], ARRAY['cow', 'pig'])
> ) AS x (numbers, animals)
> CROSS JOIN UNNEST(numbers, animals) AS t (n, a);
> {code}
> Postgres result:
> |2|dog |
> |5|cat |
> |null|bird|
> |7|cow |
> |8|pig |
> |9|null |
>
> Calcite result：
> |2|dog |
> |2|cat |
> |2|bird|
> |5|dog |
> |5|cat |
> |5|bird|
> |7|cow |
> |7|pig |
> |8|cow |
> |8|pig |
> |9|cow |
> |9|pig |
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
