Welcome toVigges Developer Community-Open, Learning,Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
981 views
in Technique[技术] by (71.8m points)

postgresql - How can I trigger a [42P09] error (ambiguous alias)

I have tried several ways to trigger a 42P09 (ambiguous alias) error, but none of them succeeded.

  1. This just "works", even when combined with group by x or order by x:

    select 1x, 1x;
    
  2. This also "works":

    with t as (select 1x, 1x, 1x) select * from t;
    select x from (with x as (select 1x) select x.x x from x group by x order by x) as x;
    
  3. These only raise a [42712] (duplicate alias):

    with t as (select 1x), t as (select 1x) select * from t;
    with t as (select 1x, 1x) select * from t, t;
    
  4. These only raise a [42702] (ambiguous column):

    select x from (select 1x, 1x) as t;
    with t1 as (select 1x), t2 as (select 1x) select x from t1, t2;
    

My google-foo didn't bring up any relevant results, either. I could only find mentions of this error existing, but noone seems to encounter it.

Is there any way to trigger a 42P09 error?


Based on @Adrian Klaver's answer, here's a SQL statement that actually yields a 42P09 error:

with t1 as (select 1x), t2 as (select 1y) 
   select * from t1 x CROSS JOIN 
   (t2 x CROSS JOIN lateral ( select * from t2 where t1.x = t2.y ) y) z;

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

If it helps, from source(backend/parser/parse_relation.c):

" Search the query's table namespace for an RTE matching the given unqualified refname. Return the RTE if a unique match, or NULL if no match. Raise error if multiple matches.

Note: it might seem that we shouldn't have to worry about the possibility of multiple matches; after all, the SQL standard disallows duplicate table aliases within a given SELECT level. Historically, however, Postgres has been laxer than that. For example, we allow

           SELECT ... FROM tab1 x CROSS JOIN (tab2 x CROSS JOIN tab3 y) z

on the grounds that the aliased join (z) hides the aliases within it, therefore there is no conflict between the two RTEs named "x". However, if tab3 is a LATERAL subquery, then from within the subquery both "x"es are visible. Rather than rejecting queries that used to work, we allow this situation, and complain only if there's actually an ambiguous reference to "x". "


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to Vigges Developer Community for programmer and developer-Open, Learning and Share
...