Xikui Wang created ASTERIXDB-2380:
-------------------------------------
Summary: Join with additional predicates in subquery returns
different results.
Key: ASTERIXDB-2380
URL: https://issues.apache.org/jira/browse/ASTERIXDB-2380
Project: Apache AsterixDB
Issue Type: Bug
Reporter: Xikui Wang
There is query issue that popped up when we were helping UW students using
AsterixDB. The following query looks for countries that share mountain and sea.
One problem about this query is if add another predicate which compares a field
having the same value, the result changes. The query is as follow:
{code}
USE hw5index;
WITH commons AS (SELECT m1c AS first_country, m2c AS second_country,
m1.name AS mountain, s1.name AS sea
FROM mountain AS m1, sea AS s1, mountain AS m2, sea AS s2,
split(m1.`-country`, " ") AS m1c, split(s1.`-country`, "
") AS s1c,
split(m2.`-country`, " ") AS m2c, split(s2.`-country`, "
") AS s2c
WHERE m1c = s1c AND m2c = s2c AND m1c != m2c AND m1.name =
m2.name AND
s1.name = s2.name AND m1c > m2c)
SELECT DISTINCT c.first_country, c.second_country,
(SELECT VALUE c2
FROM commons AS c2
where c2.first_country = c.first_country) AS mountains
FROM commons AS c;
{code}
Query with an additional predicate
{code}
USE hw5index;
WITH commons AS (SELECT m1c AS first_country, m2c AS second_country,
m1.name AS mountain, s1.name AS sea
FROM mountain AS m1, sea AS s1, mountain AS m2, sea AS s2,
split(m1.`-country`, " ") AS m1c, split(s1.`-country`, "
") AS s1c,
split(m2.`-country`, " ") AS m2c, split(s2.`-country`, "
") AS s2c
WHERE m1c = s1c AND m2c = s2c AND m1c != m2c AND m1.name =
m2.name AND
s1.name = s2.name AND m1c > m2c)
SELECT DISTINCT c.first_country, c.second_country,
(SELECT VALUE c2
FROM commons AS c2
where c2.first_country = c.first_country AND
c2.second_country = c.second_country) AS mountains
FROM commons AS c;
{code}
The DDL and data are attached
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
