I have following issue.
I created 2 dataframes from JDBC (MySQL) and joined them (t1 has fk1 to t2)
t1 = sqlCtx.read.jdbc("jdbc:mysql:, t1, id1, 0, size1, 200).cache()
t2 = sqlCtx.read.jdbc("jdbc:mysql:, t2).cache()
joined = t1.join(t2, t1.fk1 == t2.id2, "left_outer")
Important: both table are cached, so results should be the same on every query.
Then I did come counts:
t1.count() -> 5900729
sqlCtx.sql("select distinct(id1) from t1").count() -> 5900729
t2.count() -> 54298
joined.count() -> 5900729
And here magic begins - I counted distinct id1 from joined table
sqlCtx.sql("select distinct(id1) from joined").count()
Results varies (are different on every run) between 5899000 and
5900000 but never are equal to 5900729.
In addition. I did more queries:
sqlCtx.sql("select id1, count(*) from joined group by id1 having count(*) > 1").collect()
This gives some results but this query return 1
len(sqlCtx.sql("select * from joined where id1 = result").collect())
What's wrong ?