[BUGS] Postgresql query HAVING do not work - Mailing list pgsql-bugs

Version: Postgresql 9.5
OS: Debian 8 jessie run on docker

Following this tutorial The Nested Set Model on
http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/
 

Section: Depth of a Sub-Tree.
SELECT node.name, (COUNT(parent.name) - (sub_tree.depth + 1)) AS depth
FROM nested_category AS node,
        nested_category AS parent,
        nested_category AS sub_parent,
        (
                SELECT node.name, (COUNT(parent.name) - 1) AS depth
                FROM nested_category AS node,
                nested_category AS parent
                WHERE node.lft BETWEEN parent.lft AND parent.rgt
                AND node.name = 'PORTABLE ELECTRONICS'
                GROUP BY node.name, node.lft
                ORDER BY node.lft
        )AS sub_tree
WHERE node.lft BETWEEN parent.lft AND parent.rgt
        AND node.lft BETWEEN sub_parent.lft AND sub_parent.rgt
        AND sub_parent.name = sub_tree.name
GROUP BY node.name, node.lft, sub_tree.depth
ORDER BY node.lft;
+----------------------+---------+
| name                 |   depth |
|----------------------+---------|
| PORTABLE ELECTRONICS |       0 |
| MP3 PLAYERS          |       1 |
| FLASH                |       2 |
| CD PLAYERS           |       1 |
| 2 WAY RADIOS         |       1 |
+----------------------+---------+


Section: Find the Immediate Subordinates of a Node.
SELECT node.name, (COUNT(parent.name) - (sub_tree.depth + 1)) AS depth
FROM nested_category AS node,
        nested_category AS parent,
        nested_category AS sub_parent,
        (
                SELECT node.name, (COUNT(parent.name) - 1) AS depth
                FROM nested_category AS node,
                nested_category AS parent
                WHERE node.lft BETWEEN parent.lft AND parent.rgt
                AND node.name = 'PORTABLE ELECTRONICS'
                GROUP BY node.name, node.lft
                ORDER BY node.lft
        )AS sub_tree
WHERE node.lft BETWEEN parent.lft AND parent.rgt
        AND node.lft BETWEEN sub_parent.lft AND sub_parent.rgt
        AND sub_parent.name = sub_tree.name
GROUP BY node.name, node.lft, sub_tree.depth
HAVING depth <= 1
ORDER BY node.lft;
Adding 'HAVING depth <= 1' to the query still return the same results as
above instead of this:
+----------------------+---------+
| name                 |   depth |
|----------------------+---------|
| PORTABLE ELECTRONICS |       0 |
| MP3 PLAYERS          |       1 |
| FLASH                |       1 |
| CD PLAYERS           |       1 |
| 2 WAY RADIOS         |       1 |
+----------------------+---------+

I don't know if I'm doing anything wrong?

Note: Edit the post query by adding node.lft, sub_tree.depth to the
GROUP BY.



-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs