SQLA17 for json clause issue with nullable objects

[fvestjens]

Assume I have the following to tables with data

create or replace table tbl_Parent (Id integer default autoincrement,Father varchar(64));
create or replace table tbl_Child  (Id integer default autoincrement,ParentId integer,Name varchar(64),Gender varchar(8),Age integer);

insert into tbl_Parent(Father) values ('Peter Tosh');
insert into tbl_Parent(Father) values ('Jim Carrey');

insert into tbl_Child (ParentId,Name,Gender,Age) values(1,'Jawara' ,'Male'  ,40);
insert into tbl_Child (ParentId,Name,Gender,Age) values(1,'Andrew' ,'Male'  ,38);
insert into tbl_Child (ParentId,Name,Gender,Age) values(1,'Niambe' ,'Female',37);
insert into tbl_Child (ParentId,Name,Gender,Age) values(1,'Aldrina','Female',35);
insert into tbl_Child (ParentId,Name,Gender,Age) values(1,'Steve'  ,'Male'  ,33);

I created the following domains:

create domain domain_Child row( Name   tbl_Child.Name%type
                               ,Gender tbl_Child.Gender%type
                               ,Age    tbl_Child.Age%type);
create domain domain_Parent row( Name     tbl_Parent.Father%type
                                ,Children array of domain_Child);

I have created the following function:

create or replace function GetChildRows(in in_ParentId integer)
returns array(32) of domain_Child
begin
  declare array_Child array(32) of domain_child;
  //
  select array_agg(row(Name, Gender, Age)
                   order by Age)
  into variable array_child
  from tbl_Child
  where ParentId = in_ParentId;
  //
  return array_Child;
end;

I want to create a proper json using the following statement

create or replace variable array_Parents array of domain_Parent;
select array_agg(row( Father,GetChildRows(Id))
                 order by Id)
into array_Parents
from tbl_Parent;
select array_Parents as Parents for json raw;

The following result is returned:

[{"Parents": [{"Father":"Peter Tosh",
               "Children":[{"Name":"Steve","Gender":"Male","Age":33},
                           {"Name":"Aldrina","Gender":"Female","Age":35},
                           {"Name":"Niambe","Gender":"Female","Age":37},
                           {"Name":"Andrew","Gender":"Male","Age":38},
                           {"Name":"Jawara","Gender":"Male","Age":40}
                          ]},
              {"Father":"Jim Carrey",
               "Children":[]}
             ]
}]

However if I want to sort the parents on the name of the father

create or replace variable array_Parents array of domain_Parent;
select array_agg(row( Father,GetChildRows(Id))
                 order by Father)
into array_Parents
from tbl_Parent;
select array_Parents as Parents for json raw;

This will result in:

[{"Parents": [{"Father": "Jim Carrey",
               "Children": []
              },
              {"Father": "Peter Tosh",
               "Children": [null,null,null,null,null]
             }]
}]

This is probably caused by the fact that the first Children object has no result and then the array of the second object for some reason is cleared.

How can I create a case to report the issue and hopefully get it solved as soon as possible?

[VolkerBarth]

I wildly assume this bug is somewhat related to the shortcomings of sa_parse_json() you have also stumbled upon:

SQL Anywhere seems to go wrong when converting between JSON objects with varying structure (say, arrays of varying cardinality) and its own arrays of arrays with different cardinalities - and vice versa.

I don't know whether OR JSON EXPLICIT would solve the issue, however, it would certainly be way cumbersome.

In your two samples, I think the bug has to do with the fact that the first sample returns 5 for the first sub-array and 0 for the second, whereas in the second sample it's vice versa – which of course is correct, I just suspect that this influences the FOR XML RAW behaviour:

select cardinality(array_Parents),
    cardinality(array_Parents[[1]].Children),
    cardinality(array_Parents[[2]].Children);

---

As to bug reporting: AFAIK you can do so via your support contract, or use this forum to do so.