Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1jOoKq-0007Mj-JE for pgsql-bugs@arkaria.postgresql.org; Wed, 15 Apr 2020 20:08:36 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1jOoKp-0005DG-Fh for pgsql-bugs@arkaria.postgresql.org; Wed, 15 Apr 2020 20:08:35 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1jOoKp-0005D4-7P for pgsql-bugs@lists.postgresql.org; Wed, 15 Apr 2020 20:08:35 +0000 Received: from mail-pf1-x42e.google.com ([2607:f8b0:4864:20::42e]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1jOoKn-0005rC-1H for pgsql-bugs@lists.postgresql.org; Wed, 15 Apr 2020 20:08:34 +0000 Received: by mail-pf1-x42e.google.com with SMTP id m21so497047pff.13 for ; Wed, 15 Apr 2020 13:08:32 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=from:to:subject:date:message-id:in-reply-to:references:mime-version :content-transfer-encoding; bh=yQDJFMIpSvjDcbheWdJ2isdYqtmQ4GDXfO6vsSAJitY=; b=jF8WnF1VTaGaoQ0wHgik6rujWTYUqcWh1hs5WwIwDWOKdbTLqIaQU06gk3fAFmq8ye nUY2X40H7znbIdkuj3DMhDjYgexuFRyiqbS3Vq5wWO9wGYD2l+w2Ij2n/AtaZ2Xf7BdD lPThKiYMKkGyMM1P0H0IDV44M8SkUl8MudyvvrFCLrg5xFnBzHhe7yF6gCQRwE6zM+HU bmL7xdN79vU+gmUIxsh1zKPxWCV8zxLu6SQ16RFCJWP6lEtk+Zm6THGuF684YpaRKE2n m9tT1rRTCYwkwgcqZuLL//5dws+uWkCCzE9YK6ALUid6XVXRPjk96iKIcxBkyjn7VKlW 3ceQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:from:to:subject:date:message-id:in-reply-to :references:mime-version:content-transfer-encoding; bh=yQDJFMIpSvjDcbheWdJ2isdYqtmQ4GDXfO6vsSAJitY=; b=Hb3V3w4epZTgQqKZWq3x0DLFW9COpLE4uKIYxWwSZIn8AboXw7sT/0kJ9hLDuLGfRJ uHyhGo4KjHp1XEfXIYnkyjCip5tPcsDYtYxZjeGFaihD09iEzUpW+nnAT0AeAKGQ3ruq 1U0HFjDIPgGTbIOzuPA57R80mNnh3p0QhBsbfgHIVeuRNcSCs+5aILqEv/lTP2Mecv0Q LGyT6a7zAGugmcS6V12+tA4HBh4TeFWFAlGQc3bCZQH6UapFQeTX3vBlW6bu5vBYmrcn dJ/R8ZXugdnLz46fKysIxlsoht6aouD5o+KsZ7x9mq06RoxbsgFv5Z12ByTfvF1gb/T+ zeKA== X-Gm-Message-State: AGi0PuZ0o22qbB0x6+XfGDKw07vDFK1TrQbKFvnFuAvj4TSLw2nPdCWn V9JU72jTtTSEepnGJ2Ed9U//24Sufduaug== X-Google-Smtp-Source: APiQypKMGQPhdXuP5HJ7vKfxTW3TwY5yRT53LnJog/SmsIa+iTtaZgSIL6bdzRklK7ViaoMBrOnQfw== X-Received: by 2002:a63:6302:: with SMTP id x2mr17142239pgb.375.1586981310296; Wed, 15 Apr 2020 13:08:30 -0700 (PDT) Received: from hammer.magicstack.net ([52.119.126.69]) by smtp.gmail.com with ESMTPSA id t12sm9145975pgm.37.2020.04.15.13.08.29 (version=TLS1_3 cipher=TLS_AES_256_GCM_SHA384 bits=256/256); Wed, 15 Apr 2020 13:08:29 -0700 (PDT) From: Elvis Pranskevichus To: PostgreSQL mailing lists , "David G. Johnston" 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> In-Reply-To: References: <16368-0dd1d5ab02a3cbc0@postgresql.org> MIME-Version: 1.0 Content-Transfer-Encoding: 7Bit Content-Type: text/plain; charset="us-ascii" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk 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