Itgensql006: MAX and MIN operator with uint32 on @Mysql container not supported

Questions
, ,
1

It seems that the MAX operator is not supported with datatype INT on a @mysql container.

SELECT MAX(ID) from tbllog@catserver

itgensql006
Max is not supported on data type ‘uint32’ for column ‘TBLLOG.MAX_ID_’.

similarly MIN operator gives same error (itgensql007)

Tested the MIN/MAX filters on SQL Server container; it works.

Version: Query Tool 22.0.480

2

Thanks for bringing this problem to our attention. The unsigned-integer types had no support for the following group functions:

  • min
  • max
  • sum
  • product
  • first
  • last
  • count
  • listagg

In the next 22.0-release (available by the end of the week) support for the unsigned integer types will be included.

3

Thanks, this now works with 22.0.482.
I fell after testing that this is client side filtering, not forwarded to the server ?
When the table is big, that creates huge payloads, possibly breaking the limits.

4

Yes, group functions are evaluated client-side only.

It would technically be possible to off-load it to a limited number of backend servers such as the ANSI SQL-based sources, but that is commercially not yet viable.

To reduce volume, we recommend using a native statement or using a pre-aggregated database view on MySQL. For example a piece of code to select the most recent entry in a logging table of over 50 GB:

begin
  create or replace table lgg_selection@inmemorystorage
  as
  select 1 lgg_id_max, sysdateutc lgg_date_created_min
  from   DUAL@DataDictionary
  where  1=0
  ;
  for r in
  ( execute native
       ' select lgg1.lgg_id_max'
    || ' ,      lgg2.date_created lgg_date_created_min'
    || ' from   (select max(lgg.id) lgg_id_max, min(lgg.id) lgg_id_min from my_logging lgg where lgg.id >= ${LGG_ID_START}) lgg1'
    || ' join   my_logging lgg2'
    || ' on     lgg2.id = lgg1.lgg_id_min'
    datacontainer 'somecontaineralias'
  )
  loop
    insert into lgg_selection@inmemorystorage
    ( lgg_id_max
    , lgg_date_created_min
    )
    values
    ( r.lgg_id_max
    , r.lgg_date_created_min
    );
  end loop;
end;