You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@asterixdb.apache.org by "Dmitry Lychagin (JIRA)" <ji...@apache.org> on 2018/07/13 00:27:00 UTC
[jira] [Resolved] (ASTERIXDB-2380) Join with additional predicate
in subquery returns different results.
[ https://issues.apache.org/jira/browse/ASTERIXDB-2380?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Dmitry Lychagin resolved ASTERIXDB-2380.
----------------------------------------
Resolution: Fixed
> 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)