[SOLVED] Call to isc_dsql_sql_info() causes temporary directory overflow

Call to isc_dsql_sql_info() causes temporary directory overflow

There's a SQL query joining a temporary table with a view (joining two tables):

select main.*
  from tmp_table_srt sub -- temporary table
  inner join vw_s_ad_conjunct main -- joining tables M_S_AD_CONJUNCTION and M_S_AD
    on sub.I_SRTREF = 94646 and
       sub.O_ID = main.ID
  where ASCJTREF = 1678744 and
        SOURCEADSREF = 1193467 and
        isnodummy(ID) = 1

It has a query plan, that looks good to me:

PLAN JOIN (SUB INDEX (UNQ_TMP_TABLE_SRT), MAIN ADS INDEX (PK_M_S_AD), MAIN ADSCJT INDEX (FK_M_S_AD_CONJUNCTION_SUBADS))

In my database IDE, which is IBExpert, this query is executed fast enough (less than a second). But in the client application this happens: When the query gets executed, the server temporary directory runs completely full. There are about 23 GB free space before. As soon there's no free space left, the application crashes.

At first, I thought the query causes that. But then I examined, that it runs fast (without overflowing the temp directory) when executed via my database IDE and uses a query plan with good indices. Furthermore I recognized that this happens not when the query is opened but when a API call to isc_dsql_sql_info() is made by FIBPlus database component after the query has been opened (in order to get aliases - I presume).

The functions request parameter is filled with this:

InfoRequest[0]:= AnsiChar(isc_info_sql_select);         // 4
InfoRequest[1]:= AnsiChar(isc_info_sql_describe_vars);  // 7
InfoRequest[2]:= AnsiChar(isc_info_sql_sqlda_seq);      // 9
InfoRequest[3]:= AnsiChar(frb_info_sql_relation_alias); // 25
InfoRequest[4]:= AnsiChar(isc_info_sql_describe_end);   // 8

Something about this API call causes Firebird to need a huge amount of temporary space. Unfortunately I found nearly nothing about this function (besides this Interbase API guide, which tells me nothing about the request values).

Maybe there are some Firebird or Interbase experts here, who can help me to find out what causes this problem. I use Firebird (classic server) 2.5.5.26952 and fbclient.dll 2.5.5.26952

Solution

[these] client API calls can't result temp allocation at server. Firebird allocates temp space in several known cases: - creating index (not your case) - sorting query result (not your case, sinse there is no SORT word in PLAN) - using function LIST (seems not your case) - ...

So, maybe your application in addition executes some another query, that results huge sorting or temp usage. Or, you gave us wrong query :-) Btw, what are the file names (and their size) that allocates your temp? Can you turn on monitoring (using FIBPlus) to check what goes to server with this query?