Query Bug with stored function and table

[tvvwild]

Hi!
I work with Potgresql. Any heavy queries I build with stored functions. And what I found. Some times If I join function with table(view), some fields have the wrong datatype. Like this

Code: Select all

CREATE OR REPLACE FUNCTION p_test ()
RETURNS TABLES(
 A bigint,
 B integer 
)
$body$
DECLARE
 ...
BEGIN
 ...
 return;  
END;
$body$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100 ROWS 1000;

CREATE TABLE test_table (
  A integer, 
  B varchar
) WITHOUT OIDS;

and the sql query

Code: Select all

select p.a, p.b
from p_test() p
join test_table t on p.b = t.a

we think that field's types will be bigint and integer but not we have integer and string(4) !!!

so go to source "ZDbcResultSetMetadata.pas"

Code: Select all

procedure TZAbstractResultSetMetadata.LoadColumn(ColumnIndex: Integer;
  ColumnInfo: TZColumnInfo; SelectSchema: IZSelectSchema);
var
  I: Integer;
  FieldRef: TZFieldRef;
  TableRef: TZTableRef;
  Found: Boolean;
begin
  { Initializes single columns with specified table. }
  FieldRef := SelectSchema.LinkFieldByIndexAndShortName(
    ColumnIndex, ColumnInfo.ColumnLabel);
  ReadColumnByRef(FieldRef, ColumnInfo);
  if ColumnInfo.ColumnName <> '' then
    Exit;

  { Initializes single columns without specified table. }
  I := 0;
  Found := False;
  while (ColumnInfo.ColumnName = '') and (I < SelectSchema.TableCount)
    and not Found do
  begin
    TableRef := SelectSchema.Tables[I];
    if Assigned(FieldRef) then
      Found := ReadColumnByName(FieldRef.Field, TableRef, ColumnInfo)
    else
      Found := ReadColumnByName(ColumnInfo.ColumnLabel, TableRef, ColumnInfo);
    Inc(I);
  end;
end;

So what we have "{ Initializes single columns without specified table. }"
why? because we work with function and procedure "ReadColumnByRef" not change "ColumnInfo.ColumnName" (request for metadata for function returns an empty query)
fix this problem after lines

Code: Select all

if ColumnInfo.ColumnName <> '' then
  Exit;
//==fix problem with function/procedure===========
if Assigned(FieldRef) and Assigned(FieldRef.TableRef) then
  Exit;
//====================================

this problem can be solved by adding define FOSNOMETA but we losing with update.
testing on 6.6 and 7.0
wanted to include this patch in the next version
Thanks, All

[EgonHugeist]

tvvwild,

as far as i know did we remove this define. You can use TZConnection.UseMetadate := False instead.

According your patch can you attach a *.diff or *.patch file please?

Michael

[tvvwild]

here.
I install from "svn://zeos.firmos.at/zeos/trunk" and "Zeos.inc" have rows

Code: Select all

//PATCH TO DO NO METADATALOADING / UNTESTED HIGH RISK
{.$DEFINE FOSNOMETA}

[tvvwild]

I think that fix this bug fully but not. This patch doesn't fix follow

Code: Select all

select p.* 
 from p_test() p 
 join test_table t on p.b = t.a

so add second fix in "function TZAbstractResultSetMetadata.ReadColumnByName(FieldName: string; TableRef: TZTableRef; ColumnInfo: TZColumnInfo): Boolean;"
before

Code: Select all

  { Reads a column information. }
  Result := True;

insert following lines

Code: Select all

if not TableColumns.IsNull(5) then
  if TZSQLType(TableColumns.GetInt(5)) <> ColumnInfo.ColumnType then Exit;

this code doesn't update column's info if the different datatype, but if same then we have info from table's field. this is better than have string(4)
Thanks, All

[EgonHugeist]

tvvwild,

i saw you use the old svn://zeos.firmos.at/zeos/trunk download locations. Here i can't say how " up to date" they are. Can you please download and install TortoiseSVN and use this URL: http://svn.code.sf.net/p/zeoslib/code-0 ... es/testing to get the latest files?

It makes no sence for me to reproduce an issue on old revisions..

Michael

[mdaems]

tvvwild,
Did this TZConnection.UseMetadate := False fix your problem?

Mark