[
https://issues.apache.org/jira/browse/ASTERIXDB-2380?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Xikui Wang reassigned ASTERIXDB-2380:
-------------------------------------
Assignee: Dmitry Lychagin
> Join with additional predicate 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
> Assignee: Dmitry Lychagin
> Priority: Major
> Attachments: country.adm, mountain.adm, sea.adm, starter.sqlpp
>
>
> 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)
