Re: [BUGS] Postgresql query HAVING do not work

On 1/4/17, Vitaly Burovoy <vitaly.burovoy@gmail.com> wrote:
> On 1/4/17, Gwork <nnj@riseup.net> wrote:
>> 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.
>
> Hello, Gwork,
>
> HAVING works fine, it is just confusing because of naming. HAVING
> works with column names from sources (which is "sub_tree.depth" in
> your example), not with names of final columns (because they get
> aliases later).
>
> You can check it adding depth to your SELECT part:
> SELECT node.name, (COUNT(parent.name) - (sub_tree.depth + 1)) AS depth
>     ,array_agg(depth)
> FROM nested_category AS node,
> ...
>
> and you can see that values there are not bigger than 1.
>
> You must use the same expression in HAVING clause as in SELECT one to
> get what you want:
> HAVING (COUNT(parent.name) - (sub_tree.depth + 1)) <= 1
>
> but the result will not have "FLASH" because it has "2" even in your
> example.
> +----------------------+-------+
> |         name         | depth |
> +----------------------+-------+
> | PORTABLE ELECTRONICS |     0 |
> | MP3 PLAYERS          |     1 |
> | CD PLAYERS           |     1 |
> | 2 WAY RADIOS         |     1 |
> +----------------------+-------+
> (4 rows)

I'm sorry, forgot to mention: If you want to deal with hierarchical
data, Postgres has better solution - recursive query[1]. When you
understand principles, it will be much easier for you to write queries
instead of mentioned in the article.

For example, "Retrieving a Single Path" from "Adjacency model" can be
written as:
WITH RECURSIVE
sel(name, parent, depth) AS (
    SELECT name, parent, 0 FROM category WHERE name='FLASH'
    UNION ALL
    SELECT c.name, c.parent, depth + 1 FROM category c, sel WHERE
c.category_id=sel.parent
)
SELECT name FROM sel
ORDER BY depth DESC;

which gives the same result and not depends on "parent.lft" which
don't have to increase.

Moreover, you don't need to lock a table when you change data and you
can even add a constraint to keep consistency:
ALTER TABLE category ADD FOREIGN KEY (parent) REFERENCES
category(category_id) ON UPDATE CASCADE ON DELETE RESTRICT;

[1]https://www.postgresql.org/docs/current/static/queries-with.html

-- 
Best regards,
Vitaly Burovoy


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