Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1isFEU-00041P-CE for pgsql-bugs@arkaria.postgresql.org; Fri, 17 Jan 2020 00:11:26 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1isFER-00023D-Gz for pgsql-bugs@arkaria.postgresql.org; Fri, 17 Jan 2020 00:11:23 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1isFER-0001uA-5F for pgsql-bugs@lists.postgresql.org; Fri, 17 Jan 2020 00:11:23 +0000 Received: from sss.pgh.pa.us ([66.207.139.130]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1isFEK-0007VK-Fc for pgsql-bugs@lists.postgresql.org; Fri, 17 Jan 2020 00:11:21 +0000 Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.4/8.14.4) with ESMTP id 00H0BDbo017597; Thu, 16 Jan 2020 19:11:13 -0500 From: Tom Lane To: luza.mbox@gmail.com cc: pgsql-bugs@lists.postgresql.org Subject: Re: BUG #16212: subquery block allows to overwrite table alias defined earlier In-reply-to: <16212-cc31996adcf45996@postgresql.org> References: <16212-cc31996adcf45996@postgresql.org> Comments: In-reply-to PG Bug reporting form message dated "Thu, 16 Jan 2020 22:36:16 +0000" MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-ID: <17595.1579219873.1@sss.pgh.pa.us> Date: Thu, 16 Jan 2020 19:11:13 -0500 Message-ID: <17596.1579219873@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk PG Bug reporting form writes: > Short example to illustrate the issue: > SELECT > a, > b.p, > c.p > FROM > (VALUES (1)) a > JOIN LATERAL ( > SELECT p FROM (VALUES (2)) p > ) b ON TRUE > JOIN LATERAL ( > SELECT p FROM (VALUES (3)) p > ) c ON TRUE > ; Hm. What's happening here is that when transformColumnRef() tries to resolve the last "p", it first looks it up as a column reference, and only if that fails does it try to find a whole-row match. In this example, the column-reference interpretation succeeds (finding b.p), and it just runs with that instead of noticing that a whole-row match is also possible. I'm loath to change that, really. In the first place, referencing a whole-row var without using ".*" is a semi-deprecated feature. In the second place, to handle this we'd basically need to double the lookup effort for every unqualified column reference, just on the off chance that we could find a closer interpretation of the name as a whole-row reference. And in the third place, it seems likely that we'd break more existing queries than we'd fix. The most reasonable way to avoid this problem is just not to use conflicting table aliases in the first place; you're much more likely to confuse yourself than the machine with such a coding style. If you really want to do it just as above, the trick is to write "p.*" without having SELECT interpret that as something to expand into separate column references. I think you have to do that like this: ... JOIN LATERAL ( SELECT (p.*)::record AS p FROM (VALUES (3)) p ) c ON TRUE > Another example: > SELECT > a, > b.a > FROM > (VALUES (1)) a > JOIN ( > SELECT a FROM (VALUES (2)) a > ) b ON TRUE > ; > Expected result ((1), (2)) > Actual result ((2), (2)). This is just a faulty expectation. If you try that as SELECT * FROM (VALUES (1)) a JOIN ( SELECT a FROM (VALUES (2)) a ) b ON TRUE ; you'll see that what the JOIN is producing is column1 | a ---------+----- 1 | (2) (1 row) and so either "a" or "b.a" is going to reference the same thing. Again, if you were to write "a.*" in the outer SELECT list, you'd get a different result: SELECT a.* FROM (VALUES (1)) a JOIN ( SELECT a FROM (VALUES (2)) a ) b ON TRUE ; column1 --------- 1 (1 row) Even if I thought that was a bug, there's no way we'd change it now; there are way too many queries it would break. Another point applying to both these examples is that since whole-row vars aren't in the SQL standard, resolving a name as a whole-row reference when a column reference is also possible would almost certainly result in failing to interpret some SQL-compliant queries per spec. regards, tom lane