Обсуждение: Re: Problems with PQfmod() returning -1 on varchar f

Bruce,

Thanks for your quick response. The column in question is defined as 'DATA
VARCHAR(20) NOT NULL'. The issue occurs when this is referenced in a fairly
complex query, such as:

SELECT data,int16_col,int64_col,float32_col,float64_col,      date_col,row_id
FROM 
(SELECT b.data,b.int16_col,b.int64_col, b.float3_col,b.float64_col,b.date_col,b.row_id FROM  table1 b  LEFT JOIN
(SELECT*  FROM table3 ) d ON b.ROW_ID = d.ROW_ID WHERE d.ROW_ID IS NULL UNION ALL SELECT
a.data,a.int16_col,a.int64_col,a.float32_col,     a.float6_col,a.date_col,a.row_idFROM  table2 a  LEFT JOIN (SELECT *
FROMtable3 ) d ON (a.ROW_ID = d.ROW_ID) WHERE d.ROW_ID IS NULL 
 
) V__282 LIMIT 0;

Where:

CREATE TABLE table1
( data varchar(20) NOT NULL, int16_col int2, int64_col int8, float32_col float4, float64_col float8, date_col
timestamp,row_id int4 NOT NULL, CONSTRAINT table1_pk PRIMARY KEY (row_id)
 
) 
WITHOUT OIDS;

CREATE TABLE table2
( data varchar(20) NOT NULL, int16_col int2, int64_col int8, float32_col float4, float64_col float8, date_col
timestamp,row_id int4 NOT NULL, CONSTRAINT table2_pk PRIMARY KEY (row_id)
 
) 
WITHOUT OIDS;

CREATE TABLE table3
( row_id int8 NOT NULL, int32_col int4 NOT NULL, int64_col int8 NOT NULL, CONSTRAINT table3_pk PRIMARY KEY (row_id)
) 
WITHOUT OIDS;

In this particular case, I can go back and describe each of the referenced
tables, but there are other times in our application where this is not
practical. Can you recommend a more reliable method to describe such
queries? Is this a current limitation in Postgres which may be addressed at
a future release?

Thanks,

Jerry

_|_|
_|_| ESRI
Jerry L. Day
Software Developer
(909)793-2853 Ext. 1477
jerry_day@esri.com

-----Original Message-----
From: Bruce Momjian [mailto:pgman@candle.pha.pa.us]
Sent: Saturday, April 02, 2005 5:27 PM
To: Jerry Day
Cc: 'pgsql-interfaces@postgresql.org'
Subject: Re: [INTERFACES] Problems with PQfmod() returning -1 on varchar
field (libpq-8.0.0 )?


Jerry Day wrote:
> Hi all,
> 
> I've recently encountered a situation where PQfmod() fails to return the
> defined length of a varchar field referenced in a query, as expected -
> returning no information (-1), instead. Has anyone else encountered this
> issue? If so, why does this occur? Can you recommended a method to more
> reliably obtain the length of varchar fields, as defined in the relations
> referenced by a query? 

Can we see the query?  I suspect the column is being manipulated in some
way and the length isn't carried through to the query output --- if so,
there might not be much we can do about it.

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073
 

Jerry Day <jerry_day@esri.com> writes:
> Thanks for your quick response. The column in question is defined as 'DATA
> VARCHAR(20) NOT NULL'. The issue occurs when this is referenced in a fairly
> complex query, such as:

> SELECT data,int16_col,int64_col,float32_col,float64_col,
>        date_col,row_id
> FROM 
> (SELECT b.data,b.int16_col,b.int64_col,
>   b.float3_col,b.float64_col,b.date_col,b.row_id 
>  FROM  table1 b  
>  LEFT JOIN 
>  (SELECT * 
>   FROM table3 
>  ) d ON b.ROW_ID = d.ROW_ID 
>  WHERE d.ROW_ID IS NULL 
>  UNION ALL 
>  SELECT a.data,a.int16_col,a.int64_col,a.float32_col,
>        a.float6_col,a.date_col,a.row_id
>  FROM  table2 a  
>  LEFT JOIN 
>  (SELECT * 
>   FROM table3 
>  ) d ON (a.ROW_ID = d.ROW_ID) 
>  WHERE d.ROW_ID IS NULL 
> ) V__282 LIMIT 0;

The UNION will drop the typmod, even if both its inputs have the same
typmod. See e.g.
http://archives.postgresql.org/pgsql-general/2005-04/msg00204.php

This is on the agenda to look at for 8.1 ...
        regards, tom lane

Tom Lane wrote:
> >  SELECT a.data,a.int16_col,a.int64_col,a.float32_col,
> >        a.float6_col,a.date_col,a.row_id
> >  FROM  table2 a  
> >  LEFT JOIN 
> >  (SELECT * 
> >   FROM table3 
> >  ) d ON (a.ROW_ID = d.ROW_ID) 
> >  WHERE d.ROW_ID IS NULL 
> > ) V__282 LIMIT 0;
> 
> The UNION will drop the typmod, even if both its inputs have the same
> typmod. See e.g.
> http://archives.postgresql.org/pgsql-general/2005-04/msg00204.php
> 
> This is on the agenda to look at for 8.1 ...

This is not on the community TODO?  Is it on yours?  Should it be on the
communties?

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073