Thread: Improve correlation names in sanity tests

I had to do some analysis on the "sanity" tests in the regression test 
suite (opr_sanity, type_sanity) recently, and I found some of the 
queries very confusing.  One main stumbling block is that for some 
probably ancient reason many of the older queries are written with 
correlation names p1, p2, etc. independent of the name of the catalog. 
This one is a good example:

SELECT p1.oid, p1.oprname, p2.oid, p2.proname
FROM pg_operator AS p1, pg_proc AS p2          <-- HUH?!?
WHERE p1.oprcode = p2.oid AND
     p1.oprkind = 'l' AND
     (p2.pronargs != 1
      OR NOT binary_coercible(p2.prorettype, p1.oprresult)
      OR NOT binary_coercible(p1.oprright, p2.proargtypes[0])
      OR p1.oprleft != 0);

I think this is better written as

SELECT o1.oid, o1.oprname, p1.oid, p1.proname
FROM pg_operator AS o1, pg_proc AS p1
WHERE o1.oprcode = p1.oid AND
     o1.oprkind = 'l' AND
     (p1.pronargs != 1
      OR NOT binary_coercible(p1.prorettype, o1.oprresult)
      OR NOT binary_coercible(o1.oprright, p1.proargtypes[0])
      OR o1.oprleft != 0);

Attached is a patch that cleans up all the queries in this manner.

(As in the above case, I kept the digits like o1 and p1 even in cases 
where only one of each letter is used in a query.  This is mainly to 
keep the style consistent, but if people don't like that at all, it 
could be changed.)

Peter Eisentraut <peter.eisentraut@enterprisedb.com> writes:
> I had to do some analysis on the "sanity" tests in the regression test 
> suite (opr_sanity, type_sanity) recently, and I found some of the 
> queries very confusing.  One main stumbling block is that for some 
> probably ancient reason many of the older queries are written with 
> correlation names p1, p2, etc. independent of the name of the catalog. 

I think that was at least partially my fault, and no there isn't
any very good reason for it.  Your proposal seems fine.

            regards, tom lane