GBase 8a QUALIFY Clause: Filtering Window Functions the Smart Way

When you use window functions in SQL, you can't filter their results directly in a WHERE or HAVING clause — that's a well‑known limitation across many databases. GBase 8a, the China‑domestically developed MPP database from GBASE, solves this elegantly with the QUALIFY clause. Let's break down how it works, what it can do, and where you need to be careful.

Sample Table

DROP TABLE IF EXISTS emp;
CREATE TABLE emp (
    id INT,
    name VARCHAR(30) NOT NULL,
    gender VARCHAR(30) NOT NULL,
    sex INT NOT NULL,
    salary INT NOT NULL,
    dept_id INT NOT NULL
);
INSERT INTO emp VALUES(1,'Xiang Yu','Marshal',1,9000,1);
INSERT INTO emp VALUES(2,'Guan Yu','General',1,4000,2);
INSERT INTO emp VALUES(3,'Zhang Fei','Vice General',1,3000,2);
INSERT INTO emp VALUES(4,'Tang Seng','Leader',1,800,3);
INSERT INTO emp VALUES(5,'Wukong','Guard',1,700,3);
INSERT INTO emp VALUES(6,'Liu Bang','Marshal',1,6000,1);

Why QUALIFY Exists

Suppose you want to select the employee with the lowest salary in each department. You'd typically use ROW_NUMBER() and then filter on the result. Without QUALIFY, you have to wrap the query in a subquery:

-- Traditional subquery approach
SELECT * FROM (
    SELECT id, name, dept_id, salary,
           ROW_NUMBER() OVER(PARTITION BY dept_id ORDER BY salary) rwn
    FROM emp
) sub WHERE rwn = 1;

With QUALIFY, it's a single flat query:

SELECT id, name, dept_id, salary,
       ROW_NUMBER() OVER(PARTITION BY dept_id ORDER BY salary) rwn
FROM emp
QUALIFY ROW_NUMBER() OVER(PARTITION BY dept_id ORDER BY salary) = 1;

Versatility of QUALIFY

1. Filtering on a Window Function

SELECT ..., ROW_NUMBER() OVER(...) rwn FROM emp QUALIFY rwn = 1;

2. Filtering on Regular Columns or Functions

You can mix window functions and plain conditions:

SELECT ..., ROW_NUMBER() OVER(...) rwn FROM emp QUALIFY dept_id = 1;
SELECT ..., ROW_NUMBER() OVER(...) rwn FROM emp QUALIFY SUBSTR(dept_id,1,1)='1';

3. Using Column Aliases

QUALIFY recognizes column aliases defined in the SELECT list:

SELECT id, name, dept_id AS f, ROW_NUMBER() OVER(...) rwn FROM emp QUALIFY rwn = 1;
SELECT id, name, dept_id AS f, ROW_NUMBER() OVER(...) rwn FROM emp QUALIFY f = 1;

If you'd like WHERE and HAVING to also accept such aliases, enable:

SET _t_gcluster_support_alias_dependent = 1;

Restrictions and Gotchas

1. No BLOB or LONG BLOB columns in the QUALIFY condition, for either windowed or plain filters.

2. A window function must be present somewhere in the query — either in the SELECT list or inside the QUALIFY clause itself. A plain QUALIFY without any window function is illegal:
   

   -- Wrong
   SELECT id, name, dept_id FROM emp QUALIFY dept_id = 1;
   -- Correct
   SELECT id, name, dept_id, ROW_NUMBER() OVER(...) rwn FROM emp QUALIFY dept_id = 1;

1. Strict GROUP BY alignment – When GROUP BY is present, columns in the window function's PARTITION BY / ORDER BY must match the GROUP BY columns exactly:

   -- Acceptable: window function only references dept_id
   SELECT dept_id, COUNT(*) FROM emp GROUP BY dept_id
   QUALIFY ROW_NUMBER() OVER(PARTITION BY dept_id ORDER BY id) = 1;

1. Cannot coexist with HAVING – A query may use either QUALIFY or HAVING, not both.

2. WHERE comes before QUALIFY – If both are present, WHERE must appear first:
   

   SELECT ... FROM emp WHERE dept_id = 1 QUALIFY id = 1;

1. Window functions cannot be used with IN / NOT IN subqueries – The following are invalid:

   QUALIFY rwn IN (SELECT a FROM t1);           -- error
   QUALIFY ROW_NUMBER() OVER(...) IN (SELECT a FROM t1); -- error

However, you can still use subqueries with regular columns inside QUALIFY:

   QUALIFY dept_id IN (SELECT a FROM t1);       -- OK

1. OR combined with IN subqueries is not allowed – Conditions like rwn=1 OR dept_id IN (...) are rejected. But AND with IN subqueries, OR with equality subqueries, or OR with static lists are fine:

   -- OK
   QUALIFY rwn=1 AND dept_id IN (SELECT ...);
   QUALIFY rwn=1 OR dept_id = (SELECT ...);
   QUALIFY rwn=1 OR dept_id IN (1,2);

Wrap‑Up

The QUALIFY clause makes window‑function filtering natural and removes one layer of nesting from many analytical queries in your gbase database. Just keep the restrictions above in mind, and you'll write cleaner, faster SQL with GBASE's GBase 8a.