Thread: [BUGS] Postgresql query HAVING do not work
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
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) -- 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
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
On Wed, Jan 4, 2017 at 7:23 PM, 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. FYI, "The Nested Set Model" has terrible insertion performance. Any row inserted can cause the entire table to be updated. Not good. Materialized path approaches tend to be better in every sense. In postgres, "The Adjacency List Model" can be queried via WITH RECURSIVE. This mitigates a lot of the downsides that the OP mentions. I guess mysql does not have that feature? merlin -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs