Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xPU8v-00000000hoy-1ayy for pgsql-bugs@arkaria.postgresql.org; Sat, 07 Nov 2026 00:18:51 +0000 Received: from localhost ([127.0.0.1]:44554 helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.98.2) (envelope-from ) id 1xPU8v-00000001zBZ-3EfB for pgsql-bugs@arkaria.postgresql.org; Sat, 07 Nov 2026 00:18:49 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]:39070) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xPU8v-00000001zBL-25iJ for pgsql-bugs@lists.postgresql.org; Sat, 07 Nov 2026 00:18:49 +0000 Received: from meldrar.postgresql.org ([2a02:c0:301:0:ffff::31]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xPU8r-00000000dVX-3hAf for pgsql-bugs@lists.postgresql.org; Sat, 07 Nov 2026 00:18:48 +0000 DKIM-Signature: v=1; a=rsa-sha256; q=dns/txt; c=relaxed/relaxed; d=postgresql.org; s=20171124; h=Content-Transfer-Encoding:Content-Type: Mime-Version:References:In-Reply-To:From:Subject:Cc:To:Message-Id:Date:Sender :Reply-To:Content-ID:Content-Description; bh=adUFH+kNHLW4Dpzd4Jma0rxRg5EzTD6AZgiHAxEON4o=; b=0apDzRqJAPdvh+l6FREpz8WBpI Y2tXMfAj2upBJPauLkhilBb4r8ykVuWfeXrg1kYGJiOoX9LEjaJOIATE3+oAjkfnn31UJh73NpicR apOPO0qZZimS0mt89Ks941yGLlgzC07uspVA59geqGBLl8JoLqbYqj0+FnCHm7e3aGt5C/AuNwZlY Y3FP7MBcrqp79SzOI5UmdYti4T/EEZDVbsl4gVXLNS7LhwUgmr4CtXif3Uryyv3GWFrCVmqZVKqT2 WK0mDwWEO9kmpo3OdvtSl6gcrM/hwJjF15GTFPiqlRPUNcoIE9+ui2O1uNtSpcAaigSt4EB8hWg1W lVYXE6Jw==; Received: from [2409:11:4120:300:42bb:d0fd:737f:e37d] (helo=localhost) by meldrar.postgresql.org with esmtpsa (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xPU8p-00000001QU5-2PqQ; Sat, 07 Nov 2026 00:18:45 +0000 Date: Sat, 07 Nov 2026 09:18:33 +0900 (JST) Message-Id: <20261107.091833.1799286331446051531.ishii@postgresql.org> To: masao.fujii@gmail.com Cc: zhong950419@gmail.com, c.bhuvan@iitg.ac.in, pgsql-bugs@lists.postgresql.org Subject: Re: first_value()/nth_value() with IGNORE NULLS loop forever (EXCLUDE GROUP/TIES, frame past partition end, no ORDER BY) From: Tatsuo Ishii In-Reply-To: References: <20261105.142957.1216400569926986716.ishii@postgresql.org> <20261106.092531.2094637405208239588.ishii@postgresql.org> X-Mailer: Mew version 6.8 on Emacs 29.3 Mime-Version: 1.0 Content-Type: Text/Plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Host-Lookup-Failed: Reverse DNS lookup failed for 2409:11:4120:300:42bb:d0fd:737f:e37d (failed) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk > The following query succeeded before this fix but fails afterward. > Is this intentional? > > WITH t AS MATERIALIZED ( > SELECT NULLIF(i, 1) / (2 - i) AS v > FROM generate_series(1, 2) AS g(i) > ) > SELECT first_value(v) IGNORE NULLS OVER (ROWS CURRENT ROW) > FROM t > LIMIT 1; Not intentional. I think it just happened to work by chance. The outer node (WITH t AS MATERIALIZED ... FROM generate_series(1, 2)...) raises an error (division by zero) as soon as the second row is fetched. When the window aggregate node (SELECT first_value(v) IGNORE NULLS OVER (ROWS CURRENT ROW) FROM t) is executed, the outer node provides the first row with v = NULL. So the window agg node tries to fetch the second row to find out a non NULL row. Previously we called row_is_in_frame() to determine if the second row is in the frame without fetching the row in this case. row_is_in_frame() determined that the second row is out of frame because the framing option is "ROWS CURRENT ROW", meaning only the current row (in this case the first row) is within a frame. Thus it gave up searching further rows and return NULL without an error. After the fix, before calling row_is_in_frame(), we call spool_tuples() which tries to read rows from outer node to check whether we hit the partition end. In the case above, we want to read up to the second row. Unfortunately it raises the error in the outer node. I think this kind of behavior (reading beyond frame end) is not specific to window functions with IGNORE NULLLS. Consider following query: WITH t AS MATERIALIZED ( SELECT NULLIF(i, 1) / (2 - i) AS v FROM generate_series(1, 2) AS g(i) ) SELECT nth_value(v,2) RESPECT NULLS OVER (ROWS CURRENT ROW) FROM t LIMIT 1; This throws an error: ERROR: division by zero The window agg node tries to fetch the second row to evaluate nth_value(v,2) and fails. One may ask why it fetches the second row even if it is out of frame. In my understanding to determine whether a row is in frame, we need to fetch the row first. We need to check the tuple content in some frame options. Regards, -- Tatsuo Ishii SRA OSS K.K. English: http://www.sraoss.co.jp/index_en/ Japanese:http://www.sraoss.co.jp