Skip site navigation (1) Skip section navigation (2)

Inconsistent Errors on Row Comparisons

From: "David E(dot) Wheeler" <david(at)kineticode(dot)com>
To: pgsql-hackers Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Inconsistent Errors on Row Comparisons
Date: 2009-06-30 17:18:08
Message-ID: 0283DC74-A952-400B-9C04-109E0DBC8664@kineticode.com (view raw or flat)
Thread:
Lists: pgsql-hackers
Howdy,

I'm working on functions to compare result sets for pgTAP. In the  
process, I found what appears to be an inconsistency in error handling  
when comparing incomparable results. I'm testing in 8.4RC2, but the  
issue may go back for all I know. Perhaps it's intentional?

This is what I see. This query:

     VALUES (1, 2), (3, 4) EXCEPT VALUES (1, 'foo'), (3, 'bar');

Throws 42804 DATATYPE MISMATCH. Meanwhile, this query:

     VALUES (1, 2), (3, 4) EXCEPT VALUES (1), (3);

Throws 42601 SYNTAX ERROR. It'd be nice if the error was a bit more  
specific (maybe tell me that there are different numbers of columns,  
perhaps 54011?), but at least it's distinct from the data type mismatch.

However, when I do a row-by-row comparison of rows in cursors, I get a  
different behavior. The attached test case has the details, but  
assuming a function `restults_eq(refcursor, refcursor)` that does the  
row-by-row comparison, this code:

     DECLARE cwant CURSOR FOR VALUES (1, 2), (3, 4);
     DECLARE chave CURSOR FOR VALUES (1, 'foo'), (3, 'bar');
     SELECT results_eq( 'cwant'::refcursor, 'chave'::refcursor );

Throws 42804 DATATYPE MISMATCH, as expected. On the other hand, this  
code:

     DECLARE cwant2 CURSOR FOR VALUES (1, 2), (3, 4);
     DECLARE chave2 CURSOR FOR VALUES (1), (3);
     SELECT results_eq( 'cwant2'::refcursor, 'chave2'::refcursor );

Also throws Throws 42804 DATATYPE MISMATCH. For consistency with the  
row comparisons done by EXCEPT and friends, should it not throw 42601  
SYNTAX ERROR?

Thanks,

David



Attachment: try.sql
Description: application/octet-stream (1.1 KB)

Responses

pgsql-hackers by date

Next:From: Tom LaneDate: 2009-06-30 17:21:14
Subject: Re: 8.5 development schedule
Previous:From: Tom LaneDate: 2009-06-30 17:01:11
Subject: Re: 8.5 development schedule

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group