[ 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)