Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Discussion" <derby-user@db.apache.org>
Received-SPF: pass (athena.apache.org: domain of blair@orcaware.com designates
 12.11.234.124 as permitted sender)
Message-ID: <4B909242.7020509@orcaware.com>
Date: Thu, 04 Mar 2010 21:10:26 -0800
From: Blair Zajac <blair@orcaware.com>
User-Agent: Thunderbird 2.0.0.23 (Macintosh/20090812)
MIME-Version: 1.0
To: derby-user@db.apache.org
Subject: Allowing ()'s to group UNIONs in the grammar
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

We have a query on a snowflake schema and want to generate a list of PKs on the 
master parent table from UNION, INTERSECT and EXCEPT on child tables.

In trying to write a clearer query I added ()'s around the sub-queries that are 
being UNIONed together, but Derby doesn't accept it.  For example, this query works:

SELECT
   parent.pk_parent
FROM
   parent
WHERE
   EXISTS (
     SELECT
       1
     FROM (
       SELECT
         child1.pk_parent
       FROM
         child1
       WHERE
         child1.name = 'foobar'
       UNION
       SELECT
         child2.pk_parent
       FROM
         child2
       WHERE
         child2.zipcode = '12345'
     )
   AS results WHERE results.pk_parent = parent.pk_parent);

but this one doesn't:

SELECT
   parent.pk_parent
FROM
   parent
WHERE
   EXISTS (
     SELECT
       1
     FROM (
       (
         SELECT
           child1.pk_parent
         FROM
           child1
         WHERE
           child1.name = 'foobar'
       ) UNION (
         SELECT
           child2.pk_parent
         FROM
           child2
         WHERE
           child2.zipcode = '12345'
       )
     )
   AS results WHERE results.pk_parent = parent.pk_parent);

Either form works written this way, but when all the whitespace and newlines are 
removed and appearing in my Java server's log it's easier to read it with ()'s.

Is this worth opening an enhancement request for?

Regards,
Blair

Here's the DML to create the tables:


CREATE TABLE child1 (pk_child1 INT PRIMARY KEY,
                      pk_parent INT NOT NULL,
                      name VARCHAR(1024));
ALTER TABLE
   child1
ADD CONSTRAINT
   fk_child1_pk_parent
FOREIGN KEY
   (pk_parent)
REFERENCES
   parent(pk_parent);

CREATE TABLE child2 (pk_child2 INT PRIMARY KEY,
                      pk_parent INT NOT NULL,
                      zipcode VARCHAR(1024));
ALTER TABLE
   child2
ADD CONSTRAINT
   fk_child2_pk_parent
FOREIGN KEY
   (pk_parent)
REFERENCES
   parent(pk_parent);