MIME-Version: 1.0
References: <166161184718.1235920.6304070286124217754@wrigleys.postgresql.org>
In-Reply-To: 
 <166161184718.1235920.6304070286124217754@wrigleys.postgresql.org>
From: Simon Riggs <simon.riggs@enterprisedb.com>
Date: Sun, 28 Aug 2022 12:43:22 +0100
Message-ID: 
 <CANbhV-Fg9j3LKHTr8PRfSnkWBwtUg1n5Wo0vs43BtGrUyaCB+g@mail.gmail.com>
Subject: Re: Handling of quantity in recursive query example
To: jason@banfelder.net, pgsql-docs@lists.postgresql.org
Content-Type: multipart/mixed; boundary="00000000000031ae1c05e74ba8b2"
Archived-At: 
 <https://www.postgresql.org/message-id/CANbhV-Fg9j3LKHTr8PRfSnkWBwtUg1n5Wo0vs43BtGrUyaCB%2Bg%40mail.gmail.com>
Precedence: bulk

--00000000000031ae1c05e74ba8b2
Content-Type: text/plain; charset="UTF-8"

On Sat, 27 Aug 2022 at 18:04, PG Doc comments form
<noreply@postgresql.org> wrote:
>
> The following documentation comment has been logged on the website:
>
> Page: https://www.postgresql.org/docs/14/queries-with.html
> Description:
>
> I believe that the second query in section 7.8.2 (Recursive Queries) should
> be modified to properly account for quantities. In the recursive term,
> p.quantity should be multiplied by pr.quantity:
>
> WITH RECURSIVE included_parts(sub_part, part, quantity) AS (
>     SELECT sub_part, part, quantity FROM parts WHERE part = 'our_product'
>   UNION ALL
>     SELECT p.sub_part, p.part, p.quantity * pr.quantity
>     FROM included_parts pr, parts p
>     WHERE p.part = pr.sub_part
> )
> SELECT sub_part, SUM(quantity) as total_quantity
> FROM included_parts
> GROUP BY sub_part
>
> As currently written, if a car has four wheels, and each wheel has five
> bolts, the whole example returns five bolts for the parts of a car. With the
> proposed change, it will return 20 bolts.

I agree, based on the attached test.

psql -f parts.sql

Existing
 sub_part | total_quantity
----------+----------------
 wheel    |              4
 bolt      |              5
(2 rows)

Proposed change
 sub_part | total_quantity
----------+----------------
 wheel    |              4
 bolt      |             20

Doc patch attached.

-- 
Simon Riggs                http://www.EnterpriseDB.com/

--00000000000031ae1c05e74ba8b2
Content-Type: application/octet-stream; name="parts.sql"
Content-Disposition: attachment; filename="parts.sql"
Content-Transfer-Encoding: base64
Content-ID: <f_l7d9inz10>
X-Attachment-Id: f_l7d9inz10

ZHJvcCB0YWJsZSBpZiBleGlzdHMgcGFydHM7CmNyZWF0ZSB0YWJsZSBwYXJ0cwoocGFydAkJdGV4
dAosc3ViX3BhcnQJdGV4dAoscXVhbnRpdHkJaW50ZWdlcgopOwppbnNlcnQgaW50byBwYXJ0cyB2
YWx1ZXMgKCdvdXJfcHJvZHVjdCcsICd3aGVlbCcsIDQpOwppbnNlcnQgaW50byBwYXJ0cyB2YWx1
ZXMgKCd3aGVlbCcsICdib2x0JywgNSk7CgotLSBFeGlzdGluZyBxdWVyeQpXSVRIIFJFQ1VSU0lW
RSBpbmNsdWRlZF9wYXJ0cyhzdWJfcGFydCwgcGFydCwgcXVhbnRpdHkpIEFTICgKICAgIFNFTEVD
VCBzdWJfcGFydCwgcGFydCwgcXVhbnRpdHkgRlJPTSBwYXJ0cyBXSEVSRSBwYXJ0ID0gJ291cl9w
cm9kdWN0JwogIFVOSU9OIEFMTAogICAgU0VMRUNUIHAuc3ViX3BhcnQsIHAucGFydCwgcC5xdWFu
dGl0eQogICAgRlJPTSBpbmNsdWRlZF9wYXJ0cyBwciwgcGFydHMgcAogICAgV0hFUkUgcC5wYXJ0
ID0gcHIuc3ViX3BhcnQKKQpTRUxFQ1Qgc3ViX3BhcnQsIFNVTShxdWFudGl0eSkgYXMgdG90YWxf
cXVhbnRpdHkKRlJPTSBpbmNsdWRlZF9wYXJ0cwpHUk9VUCBCWSBzdWJfcGFydDsKCi0tIFByb3Bv
c2VkIHF1ZXJ5IGNoYW5nZQpXSVRIIFJFQ1VSU0lWRSBpbmNsdWRlZF9wYXJ0cyhzdWJfcGFydCwg
cGFydCwgcXVhbnRpdHkpIEFTICgKICAgIFNFTEVDVCBzdWJfcGFydCwgcGFydCwgcXVhbnRpdHkg
RlJPTSBwYXJ0cyBXSEVSRSBwYXJ0ID0gJ291cl9wcm9kdWN0JwogIFVOSU9OIEFMTAogICAgU0VM
RUNUIHAuc3ViX3BhcnQsIHAucGFydCwgcC5xdWFudGl0eSAqIHByLnF1YW50aXR5CiAgICBGUk9N
IGluY2x1ZGVkX3BhcnRzIHByLCBwYXJ0cyBwCiAgICBXSEVSRSBwLnBhcnQgPSBwci5zdWJfcGFy
dAopClNFTEVDVCBzdWJfcGFydCwgU1VNKHF1YW50aXR5KSBhcyB0b3RhbF9xdWFudGl0eQpGUk9N
IGluY2x1ZGVkX3BhcnRzCkdST1VQIEJZIHN1Yl9wYXJ0Owo=
--00000000000031ae1c05e74ba8b2
Content-Type: application/octet-stream; name="recursive_correction.v1.patch"
Content-Disposition: attachment; filename="recursive_correction.v1.patch"
Content-Transfer-Encoding: base64
Content-ID: <f_l7d9iv6p1>
X-Attachment-Id: f_l7d9iv6p1

ZGlmZiAtLWdpdCBhL2RvYy9zcmMvc2dtbC9xdWVyaWVzLnNnbWwgYi9kb2Mvc3JjL3NnbWwvcXVl
cmllcy5zZ21sCmluZGV4IDUxNmZiY2JmMzcuLjE0MjhkOTlkMGYgMTAwNjQ0Ci0tLSBhL2RvYy9z
cmMvc2dtbC9xdWVyaWVzLnNnbWwKKysrIGIvZG9jL3NyYy9zZ21sL3F1ZXJpZXMuc2dtbApAQCAt
MjE5NSw3ICsyMTk1LDcgQEAgU0VMRUNUIHN1bShuKSBGUk9NIHQ7CiBXSVRIIFJFQ1VSU0lWRSBp
bmNsdWRlZF9wYXJ0cyhzdWJfcGFydCwgcGFydCwgcXVhbnRpdHkpIEFTICgKICAgICBTRUxFQ1Qg
c3ViX3BhcnQsIHBhcnQsIHF1YW50aXR5IEZST00gcGFydHMgV0hFUkUgcGFydCA9ICdvdXJfcHJv
ZHVjdCcKICAgVU5JT04gQUxMCi0gICAgU0VMRUNUIHAuc3ViX3BhcnQsIHAucGFydCwgcC5xdWFu
dGl0eQorICAgIFNFTEVDVCBwLnN1Yl9wYXJ0LCBwLnBhcnQsIHAucXVhbnRpdHkgKiBwci5xdWFu
dGl0eQogICAgIEZST00gaW5jbHVkZWRfcGFydHMgcHIsIHBhcnRzIHAKICAgICBXSEVSRSBwLnBh
cnQgPSBwci5zdWJfcGFydAogKQo=
--00000000000031ae1c05e74ba8b2--