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)