pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Elvis Pranskevichus <elprans@gmail.com>
To: PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
To: David G. Johnston <david.g.johnston@gmail.com>
Subject: Re: BUG #16368: Incorrect function inlining in the presence of a window function
Date: Wed, 15 Apr 2020 13:08:28 -0700
Message-ID: <4425563.buerPE7WCZ@hammer.magicstack.net> (raw)
In-Reply-To: <CAKFQuwbBmEGw0hB6B3oMoWVaXjVHEziScqhz0d7FpiG6cBs0EQ@mail.gmail.com>
References: <16368-0dd1d5ab02a3cbc0@postgresql.org>
	<CAKFQuwbBmEGw0hB6B3oMoWVaXjVHEziScqhz0d7FpiG6cBs0EQ@mail.gmail.com>

On Wednesday, April 15, 2020 11:32:54 A.M. PDT David G. Johnston wrote:
> During inlining the case expression becomes:
> 
> CASE WHEN q.fmt IS NULL
> THEN '123,456'::bigint
> ELSE to_number('123,456', q.fmt)
> END;
> 
> It doesn't "take" a branch - it turns variables into constants and, as
> written, some of those constants are invalid for the types they are
> being assigned to.
> 
> > This only happens in the
> > presence of the "first_value" window call in the nested query.
> 
> The ability to optimize, and how, depends on the whole query.
> 
> I don't actually know whether this is a bug or just an expected
> downside to using inline-able functions and case statements to avoid
> malformed data parsing.
> 
> Writing the function in pl/pgsql prevents the inlining and stabilizes
> the query.

IMO, an optimization that leads to wrong query results is unquestionably 
a bug.  And "use pl/pgsql" (which is much slower) is arguably not the 
best answer here.

> inline-able functions and case statements to avoid
> malformed data parsing.

Consider any other case where an error is guarded by a "CASE WHEN", such 
as division by zero.  I think the use of "CASE WHEN" should disqualify 
the function from being inlined, or, maybe, constant folding should be 
disabled in the branches of "CASE WHEN" when inlining and when the 
optimizer is unable to reason about the "CASE" condition.

Thoughts?

                                 Elvis







view thread (8+ messages)  latest in thread

Message-ID: <4425563.buerPE7WCZ@hammer.magicstack.net>
Permalink:  ../4425563.buerPE7WCZ@hammer.magicstack.net/
Also on:    postgresql.org/message-id/4425563.buerPE7WCZ@hammer.magicstack.net

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-bugs@postgresql.org
  Cc: elprans@gmail.com, pgsql-bugs@lists.postgresql.org, david.g.johnston@gmail.com
  Subject: Re: BUG #16368: Incorrect function inlining in the presence of a window function
  In-Reply-To: <4425563.buerPE7WCZ@hammer.magicstack.net>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox