Blank string fields cause strange things?

Milos · 2021-08-29 11:18:43

In Firebird 2.5 I have a simple table with 2 string fields and if some of the values in the database are blank, retrieving the data seems wrong as low as FetchAllToJSON level.

In this example (all code on server side), I create 4 records, first one has both string fields assigned, second has first field empty but second is assigned, third is reverse situation, fourth has both fields assigned.

However, the resulting data has one of the field values repeated where an empty string should be.

  TSqlMeasurementUnit = class (TSQLRecord)
  private
    FName: RawUTF8;
    FSymbol: RawUTF8;
  public
  published
    property Name: RawUTF8 index 20 read FName write FName;
    property Symbol: RawUTF8 index 5 read FSymbol write FSymbol;
  end;

    RestServer.Execute('delete from MeasurementUnit');

    var mu : TSqlMeasurementUnit;

    mu := TSqlMeasurementUnit.Create;

    mu.Name := '+N1+'; // add a normal one
    mu.Symbol := '+S1+';
    RestServer.Add (mu, true);

    mu.Name := ''; // add one with blank name
    mu.Symbol := '-NN-';
    RestServer.Add (mu, true);

    mu.Name := '-NS-';
    mu.Symbol := ''; // add one with blank symbol
    RestServer.Add (mu, true);

    mu.Name := '+N2+';  // add another normal one
    mu.Symbol := '+S2+';
    RestServer.Add (mu, true);
    mu.Free;

    var ol := RestServer.RetrieveList<TSqlMeasurementUnit>;
    for mu in ol do
      writeln (ObjectToJSONDebug(mu));

The database viewed from db workbench shows

ID              NAME                                                                             SYMBOL              
---------------------------------------------------------------------------------------------------------------------
1               +N1+                                                                             +S1+                
2                                                                                                -NN-                
3               -NS-                                                                                                 
4               +N2+                                                                             +S2+

The server log shows 3rd record symbol field having +S1+ value (value from the first record). I traced as far as FetchAllToJSON and it returns

'[{"ID":1,"NAME":"+N1+","SYMBOL":"+S1+"},{"ID":2,"NAME":"","SYMBOL":"-NN-"},{"ID":3,"NAME":"-NS-","SYMBOL":"+S1+"},{"ID":4,"NAME":"+N2+","SYMBOL":"+S2+"}]'#$A

I will try to dig deeper a bit later.

After more digging I guess I enter Zeos territory... I updated to latest but the problem is still there...

edit#3 Tried the Zeos ZSimple example and the same query seems to return correct results...

Last edited by Milos (2022-07-15 20:39:11)

Milos · 2022-07-15 20:37:23

I am back on this Firebird project and this problem is still there for me... can someone please tell me how/what to investigate further? I cant believe a problem like this would go unnoticed by others which implies I did something wrong, but I dont see what could I have done wrong?

I dug down as low as TZInterbase6XSQLDAResultSet.GetPAnsiChar which returns bad data as well. But when called from the ZSimple zeos example project, the data is correct which means something "in between" is different and it is really hard for me to figure out what and why

if the example is confusing, a simpler example is that I have 4 records in DB, with two string fields and if I sprinkle some empty strings in the DB data, the result becomes incorrect.

 
actual data:

ID Name1 Name2
1  'A'     '1'
2  'B'     ''
3  ''     '3'
4  'D'     '4'

mormot (or zeos) returns:
{"ID":1,"Name1":"A","Name2":"1"}
{"ID":2,"Name1":"B","Name2":""}
{"ID":3,"Name1":"A","Name2":"3"} --- Name1 should be blank!
{"ID":4,"Name1":"D","Name2":"4"}

Also, the first time I encountered the problem, same code seemed to later work fine on Postgre so it seems a Firebird-only problem.

Last edited by Milos (2022-07-15 21:28:24)

ab · 2022-07-15 22:31:48

You may better ask on Zeos forum.

Milos · 2022-07-16 18:04:28

Ah well, it just needed a little bit more banging my head against the keyboard and I found the problem.

In SynDBZeos.pas

procedure TSQLDBZEOSStatement.ColumnsToJSON(WR: TJSONWriter);

for fields of UTF8 type, the function calls

          P := fResultSet.GetPAnsiChar(col+FirstDbcIndex,Len);
          WR.AddJSONEscape(P,Len);

and in my example case, for one of the blank strings, Zeos returned zero length but a non-null pointer for some reason. Mormot ignores the length and accepts the PAnsiChar as-is which is IMO correct and the fault is on Zeos side. I guess they could say "check the length" but returning a dirty pointer is not nice regardless.

btw they have another GetPAnsiChar function, without the length parameter, and that function actually fixes the problem and returns an empty string which probably explains why the Zeos example works, it probably uses that one. IDK if it makes sense for Mormot to use that one, you do seem to need the length for jsonwriter.

btw #2 perhaps it is a bit strange for TTextWriter.AddJSONEscape to write anything if length is zero? edit: actually you want to write the empty string "" which makes sense, but then the code "finds data" since pointer turns out to have some data and data actually gets written, so maybe just kind of unexpected behaviour...

Last edited by Milos (2022-07-16 18:09:07)

ab · 2022-07-16 20:21:43

Pretty weird that we didn't see it sooner.

Please try https://github.com/synopse/mORMot2/commit/c9bbe94a

Sorry for the issue.

Milos · 2022-07-16 21:29:22

Hey no problem, it is a weird one I agree. And it doesn't happen with every empty string (in example above it happens with 2nd empty string) so perhaps add "sometimes" to the "ZDBC returns P<>nil " comment in code.

I am still on Mormot 1.8, I can paste the same code change there and give it a try tomorrow.

I wrote a post on Zeos forum but it seems to be still pending Admin approval... I think they should make sure to return nil/empty string from their code.

ab · 2022-07-17 07:00:50

For mORMot 1.18, it is now https://github.com/synopse/mORMot/commi … 2473543431

mORMot Open Source

#1 2021-08-29 11:18:43

Blank string fields cause strange things?

#2 2022-07-15 20:37:23

Re: Blank string fields cause strange things?

#3 2022-07-15 22:31:48

Re: Blank string fields cause strange things?

#4 2022-07-16 18:04:28

Re: Blank string fields cause strange things?

#5 2022-07-16 20:21:43

Re: Blank string fields cause strange things?

#6 2022-07-16 21:29:22

Re: Blank string fields cause strange things?

#7 2022-07-17 07:00:50

Re: Blank string fields cause strange things?

Board footer