Bug 2521 - Optimise out group by operator in SQL level by constraint
Summary: Optimise out group by operator in SQL level by constraint
Status: NEW
Alias: None
Product: SQL
Classification: Unclassified
Component: all (show other bugs)
Version: -- development
Hardware: x86 (i?86) Linux
: --- enhancement
Assignee: SQL devs
URL: http://sourceforge.net/support/tracke...
Keywords:
Depends on:
Blocks:
 
Reported: 2010-03-08 02:33 CET by Stefan de Konink
Modified: 2016-04-11 11:44 CEST (History)
0 users



Attachments

Note You need to log in before you can comment on or make changes to this bug.
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.