[jira] [Commented] (SPARK-25150) Joining DataFrames derived from the same source yields confusing/incorrect results
[
https://issues.apache.org/jira/browse/SPARK-25150?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17459494#comment-17459494
]
Nicholas Chammas commented on SPARK-25150:
--
I re-ran my test (described in the issue description + summarized in my comment
just above) on Spark 3.2.0, and this issue appears to be resolved! Whether with
cross joins enabled or disabled, I now get the correct results.
Obviously, I have no clue what change since Spark 2.4.3 (the last time I reran
this test) was responsible for the fix.
But to be clear, in case anyone wants to reproduce my test:
# Download all 6 files attached to this issue into a folder.
# Then, from within that folder, run {{spark-submit zombie-analysis.py}} and
inspect the output.
# Then, enable cross joins (commented out on line 9), rerun the script, and
reinspect the output.
# Compare the final bit of output from both runs against
{{{}expected-output.txt{}}}.
> Joining DataFrames derived from the same source yields confusing/incorrect
> results
> --
>
> Key: SPARK-25150
> URL: https://issues.apache.org/jira/browse/SPARK-25150
> Project: Spark
> Issue Type: Bug
> Components: SQL
>Affects Versions: 2.3.1, 2.4.3
>Reporter: Nicholas Chammas
>Priority: Major
> Labels: correctness
> Attachments: expected-output.txt,
> output-with-implicit-cross-join.txt, output-without-implicit-cross-join.txt,
> persons.csv, states.csv, zombie-analysis.py
>
>
> I have two DataFrames, A and B. From B, I have derived two additional
> DataFrames, B1 and B2. When joining A to B1 and B2, I'm getting a very
> confusing error:
> {code:java}
> Join condition is missing or trivial.
> Either: use the CROSS JOIN syntax to allow cartesian products between these
> relations, or: enable implicit cartesian products by setting the configuration
> variable spark.sql.crossJoin.enabled=true;
> {code}
> Then, when I configure "spark.sql.crossJoin.enabled=true" as instructed,
> Spark appears to give me incorrect answers.
> I am not sure if I am missing something obvious, or if there is some kind of
> bug here. The "join condition is missing" error is confusing and doesn't make
> sense to me, and the seemingly incorrect output is concerning.
> I've attached a reproduction, along with the output I'm seeing with and
> without the implicit cross join enabled.
> I realize the join I've written is not "correct" in the sense that it should
> be left outer join instead of an inner join (since some of the aggregates are
> not available for all states), but that doesn't explain Spark's behavior.
--
This message was sent by Atlassian Jira
(v8.20.1#820001)
-
To unsubscribe, e-mail: issues-unsubscr...@spark.apache.org
For additional commands, e-mail: issues-h...@spark.apache.org
[jira] [Commented] (SPARK-25150) Joining DataFrames derived from the same source yields confusing/incorrect results
[
https://issues.apache.org/jira/browse/SPARK-25150?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16813554#comment-16813554
]
Sean Owen commented on SPARK-25150:
---
What happens on master, and what happens if you run the SQL query in your
example -- is it different?
Your second example is unexpected to me, so I think there is probably an issue
here somewhere, especially if ANSI SQL mandates a different behavior here (does
it? I don't know)
> Joining DataFrames derived from the same source yields confusing/incorrect
> results
> --
>
> Key: SPARK-25150
> URL: https://issues.apache.org/jira/browse/SPARK-25150
> Project: Spark
> Issue Type: Bug
> Components: SQL
>Affects Versions: 2.3.1
>Reporter: Nicholas Chammas
>Priority: Major
> Attachments: expected-output.txt,
> output-with-implicit-cross-join.txt, output-without-implicit-cross-join.txt,
> persons.csv, states.csv, zombie-analysis.py
>
>
> I have two DataFrames, A and B. From B, I have derived two additional
> DataFrames, B1 and B2. When joining A to B1 and B2, I'm getting a very
> confusing error:
> {code:java}
> Join condition is missing or trivial.
> Either: use the CROSS JOIN syntax to allow cartesian products between these
> relations, or: enable implicit cartesian products by setting the configuration
> variable spark.sql.crossJoin.enabled=true;
> {code}
> Then, when I configure "spark.sql.crossJoin.enabled=true" as instructed,
> Spark appears to give me incorrect answers.
> I am not sure if I am missing something obvious, or if there is some kind of
> bug here. The "join condition is missing" error is confusing and doesn't make
> sense to me, and the seemingly incorrect output is concerning.
> I've attached a reproduction, along with the output I'm seeing with and
> without the implicit cross join enabled.
> I realize the join I've written is not "correct" in the sense that it should
> be left outer join instead of an inner join (since some of the aggregates are
> not available for all states), but that doesn't explain Spark's behavior.
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
-
To unsubscribe, e-mail: issues-unsubscr...@spark.apache.org
For additional commands, e-mail: issues-h...@spark.apache.org
[jira] [Commented] (SPARK-25150) Joining DataFrames derived from the same source yields confusing/incorrect results
[
https://issues.apache.org/jira/browse/SPARK-25150?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16813511#comment-16813511
]
Brandon Perry commented on SPARK-25150:
---
[~srowen], I ran into this situation yesterday as well, and I think there may
be some miscommunication about expected behavior vs actual here. Many people
are accustomed to writing joins in a sequential manner in SQL; using the sample
scenario here:
{code:SQL|borderstyle=solid}
SELECT
a.State,
a.`Total Population`,
b.count AS `Total Humans`,
c.count AS `Total Zombies`
FROM states AS a
JOIN total_humans AS b
ON a.state = b.state
JOIN total_zombies AS c
ON a.state = c.state
ORDER BY a.state ASC;
{code}
On virtually all ANSI SQL systems, this will result in the output which
[~nchammas] mentions is expected. However, it looks like Spark actually
evaluates the chained joins by doing something like (states JOIN humans ON
state) JOIN (states JOIN zombies ON state) ON (_no condition specified_).
Part of the problem is that even when you attempt to fix the states['State']
join, you get the "trivially inferred" warning with inappropriate output, as
they share the same lineage and Spark optimizes past the intended logic:
{code:Python|borderstyle=solid}
states_with_humans = states \
.join(
total_humans,
on=(states['State'] == total_humans['State'])
)
analysis = states_with_humans \
.join(
total_zombies,
on=(states_with_humans['State'] == total_zombies['State'])
) \
.orderBy(states['State'], ascending=True) \
.select(
states_with_humans['State'],
states_with_humans['Total Population'],
states_with_humans['count'].alias('Total Humans'),
total_zombies['count'].alias('Total Zombies'),
)
)
{code}
Is there something we're all missing here? This seems to be a cookie-cutter
example of a three-way join not functioning as expected without explicit
aliasing. Is there a reason this behavior is desirable?
> Joining DataFrames derived from the same source yields confusing/incorrect
> results
> --
>
> Key: SPARK-25150
> URL: https://issues.apache.org/jira/browse/SPARK-25150
> Project: Spark
> Issue Type: Bug
> Components: SQL
>Affects Versions: 2.3.1
>Reporter: Nicholas Chammas
>Priority: Major
> Attachments: expected-output.txt,
> output-with-implicit-cross-join.txt, output-without-implicit-cross-join.txt,
> persons.csv, states.csv, zombie-analysis.py
>
>
> I have two DataFrames, A and B. From B, I have derived two additional
> DataFrames, B1 and B2. When joining A to B1 and B2, I'm getting a very
> confusing error:
> {code:java}
> Join condition is missing or trivial.
> Either: use the CROSS JOIN syntax to allow cartesian products between these
> relations, or: enable implicit cartesian products by setting the configuration
> variable spark.sql.crossJoin.enabled=true;
> {code}
> Then, when I configure "spark.sql.crossJoin.enabled=true" as instructed,
> Spark appears to give me incorrect answers.
> I am not sure if I am missing something obvious, or if there is some kind of
> bug here. The "join condition is missing" error is confusing and doesn't make
> sense to me, and the seemingly incorrect output is concerning.
> I've attached a reproduction, along with the output I'm seeing with and
> without the implicit cross join enabled.
> I realize the join I've written is not "correct" in the sense that it should
> be left outer join instead of an inner join (since some of the aggregates are
> not available for all states), but that doesn't explain Spark's behavior.
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
-
To unsubscribe, e-mail: issues-unsubscr...@spark.apache.org
For additional commands, e-mail: issues-h...@spark.apache.org
[jira] [Commented] (SPARK-25150) Joining DataFrames derived from the same source yields confusing/incorrect results
[
https://issues.apache.org/jira/browse/SPARK-25150?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16645484#comment-16645484
]
Sean Owen commented on SPARK-25150:
---
The second join joins "states-joined-with-humans" with "zombies", but the join
condition references a column in dataframe "states", which isn't one of those
two dataframes being joined. Obviously all of these tables have a column
"State" but that's not quite what this code is specifying. I had thought that
wasn't allowed or didn't work? Can you try breaking the join down into two
statements and making sure the column references only refer to dataframes in
each join?
If that condition is being ignored, then you end up with a full cross join,
right? Spark seems to think so because it asks if that's what you're doing. And
its answer is correct as if it were doing a cross join. It looks correct to me.
You do see NH and RI zombie stats mixed with each other; that count is 1 in
every case though. So you get double the rows as in the result of the first
join, with 1 zombie each.
> Joining DataFrames derived from the same source yields confusing/incorrect
> results
> --
>
> Key: SPARK-25150
> URL: https://issues.apache.org/jira/browse/SPARK-25150
> Project: Spark
> Issue Type: Bug
> Components: SQL
>Affects Versions: 2.3.1
>Reporter: Nicholas Chammas
>Priority: Major
> Attachments: expected-output.txt,
> output-with-implicit-cross-join.txt, output-without-implicit-cross-join.txt,
> persons.csv, states.csv, zombie-analysis.py
>
>
> I have two DataFrames, A and B. From B, I have derived two additional
> DataFrames, B1 and B2. When joining A to B1 and B2, I'm getting a very
> confusing error:
> {code:java}
> Join condition is missing or trivial.
> Either: use the CROSS JOIN syntax to allow cartesian products between these
> relations, or: enable implicit cartesian products by setting the configuration
> variable spark.sql.crossJoin.enabled=true;
> {code}
> Then, when I configure "spark.sql.crossJoin.enabled=true" as instructed,
> Spark appears to give me incorrect answers.
> I am not sure if I am missing something obvious, or if there is some kind of
> bug here. The "join condition is missing" error is confusing and doesn't make
> sense to me, and the seemingly incorrect output is concerning.
> I've attached a reproduction, along with the output I'm seeing with and
> without the implicit cross join enabled.
> I realize the join I've written is not "correct" in the sense that it should
> be left outer join instead of an inner join (since some of the aggregates are
> not available for all states), but that doesn't explain Spark's behavior.
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
-
To unsubscribe, e-mail: issues-unsubscr...@spark.apache.org
For additional commands, e-mail: issues-h...@spark.apache.org
[jira] [Commented] (SPARK-25150) Joining DataFrames derived from the same source yields confusing/incorrect results
[
https://issues.apache.org/jira/browse/SPARK-25150?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16632512#comment-16632512
]
Nicholas Chammas commented on SPARK-25150:
--
Correct, this isn't a cross join. It's just a plain inner join.
In theory, whether cross joins are enabled or not should have no bearing on the
result. However, what we're seeing is that without them enabled we get an
incorrect error and with them enabled we get incorrect results.
If we were actually trying a cross join (i.e. no {{on=(...)}} condition
specified) I think those results (with the 4 output rows) would still be
incorrect since you'd expect NH's population to be combined with RI's stats in
one of the output rows, but that's not the case. You'd also expect MA to show
up in the output, too.
> The second join joins on a column in {{states}}, but that is not a DataFrame
> used in that join. Is that the problem?
Not sure what you mean here. Both joins join on {{states}}, which is the first
DataFrame in the definition of {{analysis}}.
> Joining DataFrames derived from the same source yields confusing/incorrect
> results
> --
>
> Key: SPARK-25150
> URL: https://issues.apache.org/jira/browse/SPARK-25150
> Project: Spark
> Issue Type: Bug
> Components: SQL
>Affects Versions: 2.3.1
>Reporter: Nicholas Chammas
>Priority: Major
> Attachments: expected-output.txt,
> output-with-implicit-cross-join.txt, output-without-implicit-cross-join.txt,
> persons.csv, states.csv, zombie-analysis.py
>
>
> I have two DataFrames, A and B. From B, I have derived two additional
> DataFrames, B1 and B2. When joining A to B1 and B2, I'm getting a very
> confusing error:
> {code:java}
> Join condition is missing or trivial.
> Either: use the CROSS JOIN syntax to allow cartesian products between these
> relations, or: enable implicit cartesian products by setting the configuration
> variable spark.sql.crossJoin.enabled=true;
> {code}
> Then, when I configure "spark.sql.crossJoin.enabled=true" as instructed,
> Spark appears to give me incorrect answers.
> I am not sure if I am missing something obvious, or if there is some kind of
> bug here. The "join condition is missing" error is confusing and doesn't make
> sense to me, and the seemingly incorrect output is concerning.
> I've attached a reproduction, along with the output I'm seeing with and
> without the implicit cross join enabled.
> I realize the join I've written is not "correct" in the sense that it should
> be left outer join instead of an inner join (since some of the aggregates are
> not available for all states), but that doesn't explain Spark's behavior.
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
-
To unsubscribe, e-mail: issues-unsubscr...@spark.apache.org
For additional commands, e-mail: issues-h...@spark.apache.org
[jira] [Commented] (SPARK-25150) Joining DataFrames derived from the same source yields confusing/incorrect results
[
https://issues.apache.org/jira/browse/SPARK-25150?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16632485#comment-16632485
]
Sean Owen commented on SPARK-25150:
---
Hm, I am not sure I understand the example yet – help me clarify here. We have
three dataframes, really; states, humans, zombies:
{code:java}
State,Total Population,Total Area
RI,120,30
MA,800,1700
NH,330,910
+-+-+
|State|count|
+-+-+
| RI|2|
| NH|1|
+-+-+
+-+-+
|State|count|
+-+-+
| RI|1|
| MA|1|
+-+-+{code}
You join all three on state:
{code:java}
analysis = (
states
.join(
total_humans,
on=(states['State'] == total_humans['State'])
)
.join(
total_zombies,
on=(states['State'] == total_zombies['State'])
)
.orderBy(states['State'], ascending=True)
.select(
states['State'],
states['Total Population'],
total_humans['count'].alias('Total Humans'),
total_zombies['count'].alias('Total Zombies'),
)
)
{code}
and you get
{code:java}
+-+++-+
|State|Total Population|Total Humans|Total Zombies|
+-+++-+
| NH| 330| 1|1|
| NH| 330| 1|1|
| RI| 120| 2|1|
| RI| 120| 2|1|
+-+++-+{code}
But say you expect
{code:java}
+-+++-+
|State|Total Population|Total Humans|Total Zombies|
+-+++-+
| RI| 120| 2|1|
+-+++-+{code}
First, this isn't a cross join right? the message says it thinks there is no
join condition and wonders if you're really trying to do a cross join, but
you're not, so enabling it isn't helping. If these were cross-joins, the output
would be correct I think?
The second join joins on a column in {{states}}, but that is not a DataFrame
used in that join. Is that the problem?
> Joining DataFrames derived from the same source yields confusing/incorrect
> results
> --
>
> Key: SPARK-25150
> URL: https://issues.apache.org/jira/browse/SPARK-25150
> Project: Spark
> Issue Type: Bug
> Components: SQL
>Affects Versions: 2.3.1
>Reporter: Nicholas Chammas
>Priority: Major
> Attachments: expected-output.txt,
> output-with-implicit-cross-join.txt, output-without-implicit-cross-join.txt,
> persons.csv, states.csv, zombie-analysis.py
>
>
> I have two DataFrames, A and B. From B, I have derived two additional
> DataFrames, B1 and B2. When joining A to B1 and B2, I'm getting a very
> confusing error:
> {code:java}
> Join condition is missing or trivial.
> Either: use the CROSS JOIN syntax to allow cartesian products between these
> relations, or: enable implicit cartesian products by setting the configuration
> variable spark.sql.crossJoin.enabled=true;
> {code}
> Then, when I configure "spark.sql.crossJoin.enabled=true" as instructed,
> Spark appears to give me incorrect answers.
> I am not sure if I am missing something obvious, or if there is some kind of
> bug here. The "join condition is missing" error is confusing and doesn't make
> sense to me, and the seemingly incorrect output is concerning.
> I've attached a reproduction, along with the output I'm seeing with and
> without the implicit cross join enabled.
> I realize the join I've written is not "correct" in the sense that it should
> be left outer join instead of an inner join (since some of the aggregates are
> not available for all states), but that doesn't explain Spark's behavior.
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
-
To unsubscribe, e-mail: issues-unsubscr...@spark.apache.org
For additional commands, e-mail: issues-h...@spark.apache.org
[jira] [Commented] (SPARK-25150) Joining DataFrames derived from the same source yields confusing/incorrect results
[
https://issues.apache.org/jira/browse/SPARK-25150?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16632381#comment-16632381
]
Nicholas Chammas commented on SPARK-25150:
--
([~petertoth] - Seeing your comment edit now.) OK, so it seems the two problems
I identified are accurate, but they have a common root cause. Thanks for
confirming.
[~srowen] - Given Peter's confirmation that the results with cross join enabled
are incorrect, I believe we should mark this as a correctness issue.
> Joining DataFrames derived from the same source yields confusing/incorrect
> results
> --
>
> Key: SPARK-25150
> URL: https://issues.apache.org/jira/browse/SPARK-25150
> Project: Spark
> Issue Type: Bug
> Components: SQL
>Affects Versions: 2.3.1
>Reporter: Nicholas Chammas
>Priority: Major
> Attachments: expected-output.txt,
> output-with-implicit-cross-join.txt, output-without-implicit-cross-join.txt,
> persons.csv, states.csv, zombie-analysis.py
>
>
> I have two DataFrames, A and B. From B, I have derived two additional
> DataFrames, B1 and B2. When joining A to B1 and B2, I'm getting a very
> confusing error:
> {code:java}
> Join condition is missing or trivial.
> Either: use the CROSS JOIN syntax to allow cartesian products between these
> relations, or: enable implicit cartesian products by setting the configuration
> variable spark.sql.crossJoin.enabled=true;
> {code}
> Then, when I configure "spark.sql.crossJoin.enabled=true" as instructed,
> Spark appears to give me incorrect answers.
> I am not sure if I am missing something obvious, or if there is some kind of
> bug here. The "join condition is missing" error is confusing and doesn't make
> sense to me, and the seemingly incorrect output is concerning.
> I've attached a reproduction, along with the output I'm seeing with and
> without the implicit cross join enabled.
> I realize the join I've written is not "correct" in the sense that it should
> be left outer join instead of an inner join (since some of the aggregates are
> not available for all states), but that doesn't explain Spark's behavior.
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
-
To unsubscribe, e-mail: issues-unsubscr...@spark.apache.org
For additional commands, e-mail: issues-h...@spark.apache.org
[jira] [Commented] (SPARK-25150) Joining DataFrames derived from the same source yields confusing/incorrect results
[
https://issues.apache.org/jira/browse/SPARK-25150?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16632281#comment-16632281
]
Nicholas Chammas commented on SPARK-25150:
--
I've uploaded the expected output.
I realize that the reproduction I've attached to this ticket
(zombie-analysis.py plus the related files), though complete and
self-contained, is a bit verbose. If it's not helpful enough I will see if I
can boil it down further.
[~petertoth] - I suggest you take another look at the output with cross joins
enabled and compare it to what (I think) is the correct expected output. If I'm
understanding things correctly, there are two issues: 1) the bad error when
cross join is not enabled (there should be no error), and 2) the incorrect
results when cross join _is_ enabled (the results I just uploaded).
Your PR doesn't appear to investigate or address the incorrect results issue,
so I'm not sure if it would fix that too of if I am just mistaken about there
being a second issue.
> Joining DataFrames derived from the same source yields confusing/incorrect
> results
> --
>
> Key: SPARK-25150
> URL: https://issues.apache.org/jira/browse/SPARK-25150
> Project: Spark
> Issue Type: Bug
> Components: SQL
>Affects Versions: 2.3.1
>Reporter: Nicholas Chammas
>Priority: Major
> Attachments: expected-output.txt,
> output-with-implicit-cross-join.txt, output-without-implicit-cross-join.txt,
> persons.csv, states.csv, zombie-analysis.py
>
>
> I have two DataFrames, A and B. From B, I have derived two additional
> DataFrames, B1 and B2. When joining A to B1 and B2, I'm getting a very
> confusing error:
> {code:java}
> Join condition is missing or trivial.
> Either: use the CROSS JOIN syntax to allow cartesian products between these
> relations, or: enable implicit cartesian products by setting the configuration
> variable spark.sql.crossJoin.enabled=true;
> {code}
> Then, when I configure "spark.sql.crossJoin.enabled=true" as instructed,
> Spark appears to give me incorrect answers.
> I am not sure if I am missing something obvious, or if there is some kind of
> bug here. The "join condition is missing" error is confusing and doesn't make
> sense to me, and the seemingly incorrect output is concerning.
> I've attached a reproduction, along with the output I'm seeing with and
> without the implicit cross join enabled.
> I realize the join I've written is not "correct" in the sense that it should
> be left outer join instead of an inner join (since some of the aggregates are
> not available for all states), but that doesn't explain Spark's behavior.
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
-
To unsubscribe, e-mail: issues-unsubscr...@spark.apache.org
For additional commands, e-mail: issues-h...@spark.apache.org
[jira] [Commented] (SPARK-25150) Joining DataFrames derived from the same source yields confusing/incorrect results
[
https://issues.apache.org/jira/browse/SPARK-25150?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16632274#comment-16632274
]
Peter Toth commented on SPARK-25150:
[~nchammas], sorry for the late reply.
There is only one issue here. Please see zombie-analysis.py, it contains 2
joins and both joins define the condition explicitly, so setting
spark.sql.crossJoin.enabled=true {color:#33}should not have any
effect.{color}
{color:#33}Simply the SQL statement should not fail, please see my PR's
description for further details:
[https://github.com/apache/spark/pull/22318]{color}
> Joining DataFrames derived from the same source yields confusing/incorrect
> results
> --
>
> Key: SPARK-25150
> URL: https://issues.apache.org/jira/browse/SPARK-25150
> Project: Spark
> Issue Type: Bug
> Components: SQL
>Affects Versions: 2.3.1
>Reporter: Nicholas Chammas
>Priority: Major
> Attachments: output-with-implicit-cross-join.txt,
> output-without-implicit-cross-join.txt, persons.csv, states.csv,
> zombie-analysis.py
>
>
> I have two DataFrames, A and B. From B, I have derived two additional
> DataFrames, B1 and B2. When joining A to B1 and B2, I'm getting a very
> confusing error:
> {code:java}
> Join condition is missing or trivial.
> Either: use the CROSS JOIN syntax to allow cartesian products between these
> relations, or: enable implicit cartesian products by setting the configuration
> variable spark.sql.crossJoin.enabled=true;
> {code}
> Then, when I configure "spark.sql.crossJoin.enabled=true" as instructed,
> Spark appears to give me incorrect answers.
> I am not sure if I am missing something obvious, or if there is some kind of
> bug here. The "join condition is missing" error is confusing and doesn't make
> sense to me, and the seemingly incorrect output is concerning.
> I've attached a reproduction, along with the output I'm seeing with and
> without the implicit cross join enabled.
> I realize the join I've written is not "correct" in the sense that it should
> be left outer join instead of an inner join (since some of the aggregates are
> not available for all states), but that doesn't explain Spark's behavior.
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
-
To unsubscribe, e-mail: issues-unsubscr...@spark.apache.org
For additional commands, e-mail: issues-h...@spark.apache.org
[jira] [Commented] (SPARK-25150) Joining DataFrames derived from the same source yields confusing/incorrect results
[
https://issues.apache.org/jira/browse/SPARK-25150?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16632252#comment-16632252
]
Nicholas Chammas commented on SPARK-25150:
--
The attachments on this ticket contain a complete reproduction. The comment
towards the beginning of zombie-analysis.py points to the config that, when
enabled, appears to yield incorrect results. (Without the config enabled we get
a confusing/incorrect error, which is a second issue.)
The results with and without the config enabled are also attached here. I will
add another attachment showing the expected results.
I believe some folks over on the linked PR provided a simpler reproduction of
part of this issue, but I haven't taken a close look at it to see if it
captures the same two issues (incorrect results + confusing/incorrect error).
> Joining DataFrames derived from the same source yields confusing/incorrect
> results
> --
>
> Key: SPARK-25150
> URL: https://issues.apache.org/jira/browse/SPARK-25150
> Project: Spark
> Issue Type: Bug
> Components: SQL
>Affects Versions: 2.3.1
>Reporter: Nicholas Chammas
>Priority: Major
> Attachments: output-with-implicit-cross-join.txt,
> output-without-implicit-cross-join.txt, persons.csv, states.csv,
> zombie-analysis.py
>
>
> I have two DataFrames, A and B. From B, I have derived two additional
> DataFrames, B1 and B2. When joining A to B1 and B2, I'm getting a very
> confusing error:
> {code:java}
> Join condition is missing or trivial.
> Either: use the CROSS JOIN syntax to allow cartesian products between these
> relations, or: enable implicit cartesian products by setting the configuration
> variable spark.sql.crossJoin.enabled=true;
> {code}
> Then, when I configure "spark.sql.crossJoin.enabled=true" as instructed,
> Spark appears to give me incorrect answers.
> I am not sure if I am missing something obvious, or if there is some kind of
> bug here. The "join condition is missing" error is confusing and doesn't make
> sense to me, and the seemingly incorrect output is concerning.
> I've attached a reproduction, along with the output I'm seeing with and
> without the implicit cross join enabled.
> I realize the join I've written is not correct in the sense that it should be
> left outer join instead of an inner join (since some of the aggregates are
> not available for all states), but that doesn't explain Spark's behavior.
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
-
To unsubscribe, e-mail: issues-unsubscr...@spark.apache.org
For additional commands, e-mail: issues-h...@spark.apache.org
[jira] [Commented] (SPARK-25150) Joining DataFrames derived from the same source yields confusing/incorrect results
[
https://issues.apache.org/jira/browse/SPARK-25150?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16632234#comment-16632234
]
Sean Owen commented on SPARK-25150:
---
What's an example of expected vs actual results here that show the bug? is it
simple to summarize?
> Joining DataFrames derived from the same source yields confusing/incorrect
> results
> --
>
> Key: SPARK-25150
> URL: https://issues.apache.org/jira/browse/SPARK-25150
> Project: Spark
> Issue Type: Bug
> Components: SQL
>Affects Versions: 2.3.1
>Reporter: Nicholas Chammas
>Priority: Major
> Attachments: output-with-implicit-cross-join.txt,
> output-without-implicit-cross-join.txt, persons.csv, states.csv,
> zombie-analysis.py
>
>
> I have two DataFrames, A and B. From B, I have derived two additional
> DataFrames, B1 and B2. When joining A to B1 and B2, I'm getting a very
> confusing error:
> {code:java}
> Join condition is missing or trivial.
> Either: use the CROSS JOIN syntax to allow cartesian products between these
> relations, or: enable implicit cartesian products by setting the configuration
> variable spark.sql.crossJoin.enabled=true;
> {code}
> Then, when I configure "spark.sql.crossJoin.enabled=true" as instructed,
> Spark appears to give me incorrect answers.
> I am not sure if I am missing something obvious, or if there is some kind of
> bug here. The "join condition is missing" error is confusing and doesn't make
> sense to me, and the seemingly incorrect output is concerning.
> I've attached a reproduction, along with the output I'm seeing with and
> without the implicit cross join enabled.
> I realize the join I've written is not correct in the sense that it should be
> left outer join instead of an inner join (since some of the aggregates are
> not available for all states), but that doesn't explain Spark's behavior.
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
-
To unsubscribe, e-mail: issues-unsubscr...@spark.apache.org
For additional commands, e-mail: issues-h...@spark.apache.org
[jira] [Commented] (SPARK-25150) Joining DataFrames derived from the same source yields confusing/incorrect results
[
https://issues.apache.org/jira/browse/SPARK-25150?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=1663#comment-1663
]
Nicholas Chammas commented on SPARK-25150:
--
[~cloud_fan] / [~srowen] - Would you consider this issue (particularly the one
expressed when spark.sql.crossJoin.enabled is set to true) to be a correctness
bug? I think it is, but I'd like a committer to confirm and add the appropriate
label if necessary.
> Joining DataFrames derived from the same source yields confusing/incorrect
> results
> --
>
> Key: SPARK-25150
> URL: https://issues.apache.org/jira/browse/SPARK-25150
> Project: Spark
> Issue Type: Bug
> Components: SQL
>Affects Versions: 2.3.1
>Reporter: Nicholas Chammas
>Priority: Major
> Attachments: output-with-implicit-cross-join.txt,
> output-without-implicit-cross-join.txt, persons.csv, states.csv,
> zombie-analysis.py
>
>
> I have two DataFrames, A and B. From B, I have derived two additional
> DataFrames, B1 and B2. When joining A to B1 and B2, I'm getting a very
> confusing error:
> {code:java}
> Join condition is missing or trivial.
> Either: use the CROSS JOIN syntax to allow cartesian products between these
> relations, or: enable implicit cartesian products by setting the configuration
> variable spark.sql.crossJoin.enabled=true;
> {code}
> Then, when I configure "spark.sql.crossJoin.enabled=true" as instructed,
> Spark appears to give me incorrect answers.
> I am not sure if I am missing something obvious, or if there is some kind of
> bug here. The "join condition is missing" error is confusing and doesn't make
> sense to me, and the seemingly incorrect output is concerning.
> I've attached a reproduction, along with the output I'm seeing with and
> without the implicit cross join enabled.
> I realize the join I've written is not correct in the sense that it should be
> left outer join instead of an inner join (since some of the aggregates are
> not available for all states), but that doesn't explain Spark's behavior.
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
-
To unsubscribe, e-mail: issues-unsubscr...@spark.apache.org
For additional commands, e-mail: issues-h...@spark.apache.org
[jira] [Commented] (SPARK-25150) Joining DataFrames derived from the same source yields confusing/incorrect results
[
https://issues.apache.org/jira/browse/SPARK-25150?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16623788#comment-16623788
]
Nicholas Chammas commented on SPARK-25150:
--
Given that Spark appears to provide incorrect results when
spark.sql.crossJoin.enabled is set to true, shall we mark this as a correctness
issue?
[~petertoth] / [~EeveeB] - Would you agree with that characterization?
> Joining DataFrames derived from the same source yields confusing/incorrect
> results
> --
>
> Key: SPARK-25150
> URL: https://issues.apache.org/jira/browse/SPARK-25150
> Project: Spark
> Issue Type: Bug
> Components: SQL
>Affects Versions: 2.3.1
>Reporter: Nicholas Chammas
>Priority: Major
> Attachments: output-with-implicit-cross-join.txt,
> output-without-implicit-cross-join.txt, persons.csv, states.csv,
> zombie-analysis.py
>
>
> I have two DataFrames, A and B. From B, I have derived two additional
> DataFrames, B1 and B2. When joining A to B1 and B2, I'm getting a very
> confusing error:
> {code:java}
> Join condition is missing or trivial.
> Either: use the CROSS JOIN syntax to allow cartesian products between these
> relations, or: enable implicit cartesian products by setting the configuration
> variable spark.sql.crossJoin.enabled=true;
> {code}
> Then, when I configure "spark.sql.crossJoin.enabled=true" as instructed,
> Spark appears to give me incorrect answers.
> I am not sure if I am missing something obvious, or if there is some kind of
> bug here. The "join condition is missing" error is confusing and doesn't make
> sense to me, and the seemingly incorrect output is concerning.
> I've attached a reproduction, along with the output I'm seeing with and
> without the implicit cross join enabled.
> I realize the join I've written is not correct in the sense that it should be
> left outer join instead of an inner join (since some of the aggregates are
> not available for all states), but that doesn't explain Spark's behavior.
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
-
To unsubscribe, e-mail: issues-unsubscr...@spark.apache.org
For additional commands, e-mail: issues-h...@spark.apache.org
[jira] [Commented] (SPARK-25150) Joining DataFrames derived from the same source yields confusing/incorrect results
[
https://issues.apache.org/jira/browse/SPARK-25150?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16612026#comment-16612026
]
Evelyn Bayes commented on SPARK-25150:
--
Hey Peter, don't stress it. I'm new to the community as well but I'm been a
busy so all good :)
> Joining DataFrames derived from the same source yields confusing/incorrect
> results
> --
>
> Key: SPARK-25150
> URL: https://issues.apache.org/jira/browse/SPARK-25150
> Project: Spark
> Issue Type: Bug
> Components: SQL
>Affects Versions: 2.3.1
>Reporter: Nicholas Chammas
>Priority: Major
> Attachments: output-with-implicit-cross-join.txt,
> output-without-implicit-cross-join.txt, persons.csv, states.csv,
> zombie-analysis.py
>
>
> I have two DataFrames, A and B. From B, I have derived two additional
> DataFrames, B1 and B2. When joining A to B1 and B2, I'm getting a very
> confusing error:
> {code:java}
> Join condition is missing or trivial.
> Either: use the CROSS JOIN syntax to allow cartesian products between these
> relations, or: enable implicit cartesian products by setting the configuration
> variable spark.sql.crossJoin.enabled=true;
> {code}
> Then, when I configure "spark.sql.crossJoin.enabled=true" as instructed,
> Spark appears to give me incorrect answers.
> I am not sure if I am missing something obvious, or if there is some kind of
> bug here. The "join condition is missing" error is confusing and doesn't make
> sense to me, and the seemingly incorrect output is concerning.
> I've attached a reproduction, along with the output I'm seeing with and
> without the implicit cross join enabled.
> I realize the join I've written is not correct in the sense that it should be
> left outer join instead of an inner join (since some of the aggregates are
> not available for all states), but that doesn't explain Spark's behavior.
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
-
To unsubscribe, e-mail: issues-unsubscr...@spark.apache.org
For additional commands, e-mail: issues-h...@spark.apache.org
[jira] [Commented] (SPARK-25150) Joining DataFrames derived from the same source yields confusing/incorrect results
[
https://issues.apache.org/jira/browse/SPARK-25150?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16601786#comment-16601786
]
Peter Toth commented on SPARK-25150:
[~EeveeB], sorry, I have just noticed that you might have started working on a
patch. I think I came to the same conclusion as you and submitted a PR, but I'm
quite new to Spark so any comments are welcome.
> Joining DataFrames derived from the same source yields confusing/incorrect
> results
> --
>
> Key: SPARK-25150
> URL: https://issues.apache.org/jira/browse/SPARK-25150
> Project: Spark
> Issue Type: Bug
> Components: SQL
>Affects Versions: 2.3.1
>Reporter: Nicholas Chammas
>Priority: Major
> Attachments: output-with-implicit-cross-join.txt,
> output-without-implicit-cross-join.txt, persons.csv, states.csv,
> zombie-analysis.py
>
>
> I have two DataFrames, A and B. From B, I have derived two additional
> DataFrames, B1 and B2. When joining A to B1 and B2, I'm getting a very
> confusing error:
> {code:java}
> Join condition is missing or trivial.
> Either: use the CROSS JOIN syntax to allow cartesian products between these
> relations, or: enable implicit cartesian products by setting the configuration
> variable spark.sql.crossJoin.enabled=true;
> {code}
> Then, when I configure "spark.sql.crossJoin.enabled=true" as instructed,
> Spark appears to give me incorrect answers.
> I am not sure if I am missing something obvious, or if there is some kind of
> bug here. The "join condition is missing" error is confusing and doesn't make
> sense to me, and the seemingly incorrect output is concerning.
> I've attached a reproduction, along with the output I'm seeing with and
> without the implicit cross join enabled.
> I realize the join I've written is not correct in the sense that it should be
> left outer join instead of an inner join (since some of the aggregates are
> not available for all states), but that doesn't explain Spark's behavior.
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
-
To unsubscribe, e-mail: issues-unsubscr...@spark.apache.org
For additional commands, e-mail: issues-h...@spark.apache.org
[jira] [Commented] (SPARK-25150) Joining DataFrames derived from the same source yields confusing/incorrect results
[
https://issues.apache.org/jira/browse/SPARK-25150?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16601734#comment-16601734
]
Apache Spark commented on SPARK-25150:
--
User 'peter-toth' has created a pull request for this issue:
https://github.com/apache/spark/pull/22318
> Joining DataFrames derived from the same source yields confusing/incorrect
> results
> --
>
> Key: SPARK-25150
> URL: https://issues.apache.org/jira/browse/SPARK-25150
> Project: Spark
> Issue Type: Bug
> Components: SQL
>Affects Versions: 2.3.1
>Reporter: Nicholas Chammas
>Priority: Major
> Attachments: output-with-implicit-cross-join.txt,
> output-without-implicit-cross-join.txt, persons.csv, states.csv,
> zombie-analysis.py
>
>
> I have two DataFrames, A and B. From B, I have derived two additional
> DataFrames, B1 and B2. When joining A to B1 and B2, I'm getting a very
> confusing error:
> {code:java}
> Join condition is missing or trivial.
> Either: use the CROSS JOIN syntax to allow cartesian products between these
> relations, or: enable implicit cartesian products by setting the configuration
> variable spark.sql.crossJoin.enabled=true;
> {code}
> Then, when I configure "spark.sql.crossJoin.enabled=true" as instructed,
> Spark appears to give me incorrect answers.
> I am not sure if I am missing something obvious, or if there is some kind of
> bug here. The "join condition is missing" error is confusing and doesn't make
> sense to me, and the seemingly incorrect output is concerning.
> I've attached a reproduction, along with the output I'm seeing with and
> without the implicit cross join enabled.
> I realize the join I've written is not correct in the sense that it should be
> left outer join instead of an inner join (since some of the aggregates are
> not available for all states), but that doesn't explain Spark's behavior.
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
-
To unsubscribe, e-mail: issues-unsubscr...@spark.apache.org
For additional commands, e-mail: issues-h...@spark.apache.org
[jira] [Commented] (SPARK-25150) Joining DataFrames derived from the same source yields confusing/incorrect results
[
https://issues.apache.org/jira/browse/SPARK-25150?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16598284#comment-16598284
]
Evelyn Bayes commented on SPARK-25150:
--
Sorry my attachment doesn't want to stick,I'll give it another try.
[^zombie-analysis.py]
> Joining DataFrames derived from the same source yields confusing/incorrect
> results
> --
>
> Key: SPARK-25150
> URL: https://issues.apache.org/jira/browse/SPARK-25150
> Project: Spark
> Issue Type: Bug
> Components: SQL
>Affects Versions: 2.3.1
>Reporter: Nicholas Chammas
>Priority: Major
> Attachments: output-with-implicit-cross-join.txt,
> output-without-implicit-cross-join.txt, persons.csv, states.csv,
> zombie-analysis.py
>
>
> I have two DataFrames, A and B. From B, I have derived two additional
> DataFrames, B1 and B2. When joining A to B1 and B2, I'm getting a very
> confusing error:
> {code:java}
> Join condition is missing or trivial.
> Either: use the CROSS JOIN syntax to allow cartesian products between these
> relations, or: enable implicit cartesian products by setting the configuration
> variable spark.sql.crossJoin.enabled=true;
> {code}
> Then, when I configure "spark.sql.crossJoin.enabled=true" as instructed,
> Spark appears to give me incorrect answers.
> I am not sure if I am missing something obvious, or if there is some kind of
> bug here. The "join condition is missing" error is confusing and doesn't make
> sense to me, and the seemingly incorrect output is concerning.
> I've attached a reproduction, along with the output I'm seeing with and
> without the implicit cross join enabled.
> I realize the join I've written is not correct in the sense that it should be
> left outer join instead of an inner join (since some of the aggregates are
> not available for all states), but that doesn't explain Spark's behavior.
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
-
To unsubscribe, e-mail: issues-unsubscr...@spark.apache.org
For additional commands, e-mail: issues-h...@spark.apache.org
[jira] [Commented] (SPARK-25150) Joining DataFrames derived from the same source yields confusing/incorrect results
[
https://issues.apache.org/jira/browse/SPARK-25150?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16598275#comment-16598275
]
Evelyn Bayes commented on SPARK-25150:
--
I'd love the chance to bug patch this.
I've included a simplified version of the python script which produces it, if
you switch out the second join to the commented join it works as it should.
> Joining DataFrames derived from the same source yields confusing/incorrect
> results
> --
>
> Key: SPARK-25150
> URL: https://issues.apache.org/jira/browse/SPARK-25150
> Project: Spark
> Issue Type: Bug
> Components: SQL
>Affects Versions: 2.3.1
>Reporter: Nicholas Chammas
>Priority: Major
> Attachments: output-with-implicit-cross-join.txt,
> output-without-implicit-cross-join.txt, persons.csv, states.csv,
> zombie-analysis.py
>
>
> I have two DataFrames, A and B. From B, I have derived two additional
> DataFrames, B1 and B2. When joining A to B1 and B2, I'm getting a very
> confusing error:
> {code:java}
> Join condition is missing or trivial.
> Either: use the CROSS JOIN syntax to allow cartesian products between these
> relations, or: enable implicit cartesian products by setting the configuration
> variable spark.sql.crossJoin.enabled=true;
> {code}
> Then, when I configure "spark.sql.crossJoin.enabled=true" as instructed,
> Spark appears to give me incorrect answers.
> I am not sure if I am missing something obvious, or if there is some kind of
> bug here. The "join condition is missing" error is confusing and doesn't make
> sense to me, and the seemingly incorrect output is concerning.
> I've attached a reproduction, along with the output I'm seeing with and
> without the implicit cross join enabled.
> I realize the join I've written is not correct in the sense that it should be
> left outer join instead of an inner join (since some of the aggregates are
> not available for all states), but that doesn't explain Spark's behavior.
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
-
To unsubscribe, e-mail: issues-unsubscr...@spark.apache.org
For additional commands, e-mail: issues-h...@spark.apache.org
[jira] [Commented] (SPARK-25150) Joining DataFrames derived from the same source yields confusing/incorrect results
[
https://issues.apache.org/jira/browse/SPARK-25150?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16584279#comment-16584279
]
Tomasz Gawęda commented on SPARK-25150:
---
[~nchammas] Maybe it's related to:
[https://twitter.com/KurtFehlhauer/status/1030490707641790474]
It looks like Spark is resolving columns to the ones in it's lineage, but not
always in current schema
> Joining DataFrames derived from the same source yields confusing/incorrect
> results
> --
>
> Key: SPARK-25150
> URL: https://issues.apache.org/jira/browse/SPARK-25150
> Project: Spark
> Issue Type: Bug
> Components: SQL
>Affects Versions: 2.3.1
>Reporter: Nicholas Chammas
>Priority: Major
> Attachments: output-with-implicit-cross-join.txt,
> output-without-implicit-cross-join.txt, persons.csv, states.csv,
> zombie-analysis.py
>
>
> I have two DataFrames, A and B. From B, I have derived two additional
> DataFrames, B1 and B2. When joining A to B1 and B2, I'm getting a very
> confusing error:
> {code:java}
> Join condition is missing or trivial.
> Either: use the CROSS JOIN syntax to allow cartesian products between these
> relations, or: enable implicit cartesian products by setting the configuration
> variable spark.sql.crossJoin.enabled=true;
> {code}
> Then, when I configure "spark.sql.crossJoin.enabled=true" as instructed,
> Spark appears to give me incorrect answers.
> I am not sure if I am missing something obvious, or if there is some kind of
> bug here. The "join condition is missing" error is confusing and doesn't make
> sense to me, and the seemingly incorrect output is concerning.
> I've attached a reproduction, along with the output I'm seeing with and
> without the implicit cross join enabled.
> I realize the join I've written is not correct in the sense that it should be
> left outer join instead of an inner join (since some of the aggregates are
> not available for all states), but that doesn't explain Spark's behavior.
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
-
To unsubscribe, e-mail: issues-unsubscr...@spark.apache.org
For additional commands, e-mail: issues-h...@spark.apache.org
[jira] [Commented] (SPARK-25150) Joining DataFrames derived from the same source yields confusing/incorrect results
[
https://issues.apache.org/jira/browse/SPARK-25150?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16584239#comment-16584239
]
Nicholas Chammas commented on SPARK-25150:
--
I know there are a bunch of pending bug fixes in 2.3.2. I'm not sure if this is
covered by any of them, and didn't have time to setup 2.3.2 to see if this
problem is still present there.
cc [~marmbrus].
> Joining DataFrames derived from the same source yields confusing/incorrect
> results
> --
>
> Key: SPARK-25150
> URL: https://issues.apache.org/jira/browse/SPARK-25150
> Project: Spark
> Issue Type: Bug
> Components: SQL
>Affects Versions: 2.3.1
>Reporter: Nicholas Chammas
>Priority: Major
> Attachments: output-with-implicit-cross-join.txt,
> output-without-implicit-cross-join.txt, persons.csv, states.csv,
> zombie-analysis.py
>
>
> I have two DataFrames, A and B. From B, I have derived two additional
> DataFrames, B1 and B2. When joining A to B1 and B2, I'm getting a very
> confusing error:
> {code:java}
> Join condition is missing or trivial.
> Either: use the CROSS JOIN syntax to allow cartesian products between these
> relations, or: enable implicit cartesian products by setting the configuration
> variable spark.sql.crossJoin.enabled=true;
> {code}
> Then, when I configure "spark.sql.crossJoin.enabled=true" as instructed,
> Spark appears to give me incorrect answers.
> I am not sure if I am missing something obvious, or if there is some kind of
> bug here. The "join condition is missing" error is confusing and doesn't make
> sense to me, and the seemingly incorrect output is concerning.
> I've attached a reproduction, along with the output I'm seeing with and
> without the implicit cross join enabled.
> I realize the join I've written is not correct in the sense that it should be
> left outer join instead of an inner join (since some of the aggregates are
> not available for all states), but that doesn't explain Spark's behavior.
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
-
To unsubscribe, e-mail: issues-unsubscr...@spark.apache.org
For additional commands, e-mail: issues-h...@spark.apache.org
