Re: [BUGS] Postgresql query HAVING do not work
От | Vitaly Burovoy |
---|---|
Тема | Re: [BUGS] Postgresql query HAVING do not work |
Дата | |
Msg-id | CAKOSWNnbzw1zjACxop5g-EWc8JFAPf=zq8OdbeSDYXAcC=hrNA@mail.gmail.com обсуждение исходный текст |
Ответ на | Re: [BUGS] Postgresql query HAVING do not work (Vitaly Burovoy <vitaly.burovoy@gmail.com>) |
Список | 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
В списке pgsql-bugs по дате отправления:
Следующее
От: Michael PaquierДата:
Сообщение: Re: [BUGS] [BUG] pg9.4.10 Logical decoding did not get the correct oldtuplelen