Hi Paul, I think I addressed your concerns in my last commit. I changed the
QueryPlanner to use multiple PartitioningForStatement objects in case of
union statement (one per child SQL) to verify partitioning for an
individual select and then iterate over them to make sure that only 0 or 1
of them has count of independent partitions greater than 0. Since we don't
allow for a same table to appear more than once in the whole union this
should guarantee that we will have 1 or 2 fragments only for the entire SQL.

My only concern is the last line from that statement of yours -
What we need to avoid is cases like "SELECT ID, DATA FROM T1 WHERE ID=1
UNION SELECT ID, DATA FROM T2 WHERE ID=2" where each SELECT would normally
run as a single-partition plan and contain a single fragment, but the
PartitioningForStatement state would indicate that the single-fragment
plans are only valid when run each on their own partition.

How would PartitioningForStatement indicate such a state?
partitioning.wasSpecifiedAsSingle() == true &&
partitioning.getCountOfIndependentlyPartitionedTables() == 0

If so, then I don't think it's an issue there since when I create
PartitioningForStatement object, the first parameter to its constructor
(m_specifiedValue) is set to null which makes wasSpecifiedAsSingle() to
return false.

Mike


On Fri, Sep 28, 2012 at 8:46 AM, Paul A Martel <notifications@github.com>wrote:

> In tests/frontend/org/voltdb/planner/TestUnion.java:
>
> > +            aide.compile("select A from T1 EXCEPT ALL select B from T2", 0, false, null);
> > +            fail();
> > +        }
> > +        catch (Exception ex) {}
> > +    }
> > +
> > +    @Override
> > +    protected void setUp() throws Exception {
> > +        aide = new PlannerTestAideDeCamp(TestUnion.class.getResource("testjoinorder-ddl.sql"),
> > +                                         "testjoinorder");
> > +
> > +        // Set all tables to non-replicated.
> > +        Cluster cluster = aide.getCatalog().getClusters().get("cluster");
> > +        CatalogMap<Table> tmap = cluster.getDatabases().get("database").getTables();
> > +        for (Table t : tmap) {
> > +            t.setIsreplicated(true);
>
> The issue with T UNION T stems from the same sort of limitations that
> prevent T JOIN T from working correctly -- at this point, I would suggest
> giving an error if any table mentioned in a UNION's (UNION TREE's) children
> occurs twice.
>
> For now, I would also suggest just living with the current restriction
> that the final plan can only have 1 or 2 fragments, meaning that only one
> of the selects can be multi-partition.
>
> To me, a higher priority is either error handling or disabling "inferred
> SP" support. Or BOTH error handling AND disabling -- try planning with
> inferredSP and on failure retry without inferred SP -- I THINK we do
> something similar with multi-statement batches. What we need to avoid is
> cases like "SELECT ID, DATA FROM T1 WHERE ID=1 UNION SELECT ID, DATA FROM
> T2 WHERE ID=2" where each SELECT would normally run as a single-partition
> plan and contain a single fragment, but the PartitioningForStatement state
> would indicate that the single-fragment plans are only valid when run each
> on their own partition.
>
> In theory, in some cases where there are exactly 2 such single-partition
> SELECT plans -- or where there are only 2 different inferred partition keys
> shared by the two or more SELECTs, the SP plans can be stitched together
> into an MP plan by adding a Receive/Send pair to the top (and possibly a
> Distinct Node) but this would be very ambitious and could only be a
> specialized feature for specific SetOperations.
>
> —
> Reply to this email directly or view it on GitHub<https://github.com/alexeevm/voltdb/pull/3/files#r1716910>.
>
>