Bug 2521

Summary: Optimise out group by operator in SQL level by constraint
Product: SQL Reporter: Stefan de Konink <skinkie>
Component: allAssignee: SQL devs <bugs-sql>
Status: NEW ---    
Severity: enhancement    
Priority: ---    
Version: -- development   
Hardware: x86 (i?86)   
OS: Linux   
URL: http://sourceforge.net/support/tracker.php?aid=2965433

Description Stefan de Konink 2010-03-08 14:33:22 CET
CREATE TABLE "sys"."node_tags" (
        "node" int,
        "k"    varchar(255),
        "v"    varchar(1024),
        CONSTRAINT "pk_node_tags" UNIQUE ("node", "k", "v"),
        CONSTRAINT "fk_node_tags_node" FOREIGN KEY ("node") REFERENCES "sys"."nodes_legacy" ("id")
);

The above table results in a SQL catalogue that knows the way,k,v properties should be unique. The following query could therefore be answered as 0 results.
select count(node), node, k, v from node_tags group by node, k, v having count(node) > 1;

While for the following query the entire table can be returned:
select count(node), node, k, v from node_tags group by node, k, v having count(node) = 1;

Since the BAT information is not available between BATs, but is in the SQL catalogue, it can be a relational algebra optimisation step.
Comment 1 Pseudo user for Sourceforge import 2010-05-04 09:32:09 CEST
This bug was previously known as tracker item 2965433 at http://sourceforge.net/support/tracker.php?aid=2965433

The original assignee of this bug does not have
   an account here. Reassigning to the default assignee
   for the component, bugs-sql@monetdb.org.
   Previous assignee was nobody@users.sourceforge.net.