Stange behaviour of Lazarus Trunk version

[marcov]

Do you also use FPC trunk?

commit f0e34e8fabafd121ee2a7e4f6e3f005727a271f7
Author: Michaël Van Canneyt <michael@freepascal.org>
Date:   Fri Mar 8 11:59:01 2024 +0100

    * Map text field on ftMemo, since string has limited length. Fixes issue #40686

M   packages/fcl-db/src/sqldb/sqlite/sqlite3conn.pp

https://gitlab.com/freepascal.org/fpc/source/-/issues/40686

[rvk]

    * Map text field on ftMemo, since string has limited length. Fixes issue #40686

Current implementation assigns ftString to Query-Results on FTS-Tables, which cuts resulting fields off at 1020 characters.

Weird... Does this mean ftString (for Sqlite) was limited to 1020 characters in FPC ???

Maybe it was better to look into WHY there was a limit of 1020 and extend that.

[madref]

Have them in Private

[Zvoni]

Yes. and 3=0 will evaluate to a boolean. What's wrong with this.
:Wed_Seizoen_ID=0 is the same as 0=:Wed_Seizoen_ID
It just checks of the parameter :Wed_Seizoen_ID is 0, and if it is it will not check Fluit_Seizoen_ID=:Wed_Seizoen_ID (because of the or).

This is perfectly fine for when you don't want to change the SQL and do want to check if the input parameter is 0.
I also have similar constructions but I even have a COALESCE around :Wed_Seizoen_ID so if the parameter is NULL it also skips the check for Fluit_Seizoen_ID.

How would you create a SQL where for a parameter <> 0 (or NULL) it will only give you the Fluit_Seizoen_ID = parameter, AND if parameter is 0 (or NULL) it will return everything? (without changing the SQL)

OK, now i've got you!

But....

Code: SQL  [Select][+][-]

1. 'WHERE ' +
2.     '((Fluit_Seizoen_ID=:Wed_Seizoen_ID) or :Wed_Seizoen_ID=0) ' +  /*?!?!?!?!?!?!*/
3.     'OR ' +
4.     '(Scheids_ID IN (1,2,3,4)) ' +

If Wed_Seizoen_ID <> 0 Then the right part returns False, the left Part returns true (hopefully), otherwise the left part returns False and the right part returns True.
In any case, since it's an OR it would return True everytime
And as said above: Fluit_Seizoen_ID refers to a Field from the LEFT JOIN (since i can't see it in the table in the FROM-Clause), changing the LEFT JOIN to an INNER JOIN.
((Fluit_Seizoen_ID=:Wed_Seizoen_ID) or :Wed_Seizoen_ID=0)  actually belongs into the ON-Clause of the LEFT JOIN


THEN comes the OR to the hardcoded Scheids_ID IN

I admit: This escapes mit totally

[madref]

Do you also use FPC trunk?

commit f0e34e8fabafd121ee2a7e4f6e3f005727a271f7
Author: Michaël Van Canneyt <michael@freepascal.org>
Date:   Fri Mar 8 11:59:01 2024 +0100

    * Map text field on ftMemo, since string has limited length. Fixes issue #40686

M   packages/fcl-db/src/sqldb/sqlite/sqlite3conn.pp

https://gitlab.com/freepascal.org/fpc/source/-/issues/40686

I Use:
Lazarus 3.99 (rev main_3_99-2076-gfc41ca2ea9) FPC 3.3.1 x86_64-darwin-cocoa

[rvk]

If Wed_Seizoen_ID <> 0 Then the right part returns False, the left Part returns true (hopefully), otherwise the left part returns False and the right part returns True.
In any case, since it's an OR it would return True everytime

No, it will only return true if Wed_Seizoen_ID is equal to the :input OR :input is 0.

If input is 0 then it must return everything.
So, the last part :input = 0 will be true and because of OR the first part isn't relevant and it will return everything.

If input <> 0 then it must only return the correct Fluit_Seizoen_ID.
The last part :input = 0 will return false and then the first part IS relevant and will return only the correct Fluit_Seizoen_ID.

I admin, it could be a bit hard to follow but it's the easiest way to do both 0 and <>0 input.

And as said above: Fluit_Seizoen_ID refers to a Field from the LEFT JOIN (since i can't see it in the table in the FROM-Clause), changing the LEFT JOIN to an INNER JOIN.

You can reference fields in WHERE without having them in the SELECT part. So you can't be sure where that field is.
But it doesn't really matter. The part of the Fluit_Seizoen_ID still stays the same.

[Zvoni]

    * Map text field on ftMemo, since string has limited length. Fixes issue #40686

Current implementation assigns ftString to Query-Results on FTS-Tables, which cuts resulting fields off at 1020 characters.

Weird... Does this mean ftString (for Sqlite) was limited to 1020 characters in FPC ???

Maybe it was better to look into WHY there was a limit of 1020 and extend that.

Look inside sqlite3conn in Procedure AddFieldDefs

Code: Pascal  [Select][+][-]

1. ftString,
2.       ftFixedChar,
3.       ftFixedWideChar,
4.       ftWideString,
5.       ftBytes,
6.       ftVarBytes:
7.                begin
8.                  size1 := 255; //sql: if length is omitted then length is 1
9.                  size2 := 0;
10.                  ExtractPrecisionAndScale(FD, size1, size2);
11.                  if size1 > MaxSmallint then size1 := MaxSmallint;
12.                end;
13. //A few lines lower
14. FieldDefs.Add(FN, FT, size1, size2, NotNull=1, false, i+1, CP_UTF8);
15.  

"ExtractPrecisionAndScale" looks for the "50" in VARCHAR(50) (and similiar)
If there is NO "Precision" (Like VARCHAR, TEXT etc.) Size1 is set to 255, though i admit, i have no idea how that translates to 1020 Characters (which we determined with try and error)

[Zvoni]

You can reference fields in WHERE without having them in the SELECT part.

I know that

So you can't be sure where that field is.

Well, i AM sure that it's NOT in tbl_scheidrechters! Since it doesn't appear there in the Create Table-Statement, so it MUST come from the LEFT JOIN

[rvk]

So you can't be sure where that field is.

Well, i AM sure that it's NOT in tbl_scheidrechters! Since it doesn't appear there in the Create Table-Statement, so it MUST come from the LEFT JOIN

True. You're correct. But it doesn't matter for the actual SQL.

Maybe you can show a (better) construct to have scheidsrechters and make a join with licenties with the requirements that you input a field seizoen.

If seizoen is 0 then you need to return all known scheidsrechters/licenties.
If seizoen <> 0 then you only need to return the scheidsrechters/licenties from that seizoen.
UNLESS you enter the scheidsrechters ID 1..4 (which are probably special ID's) then you also need to return all scheidsrechters/licenties.

Note. You also need to return scheidsrechters without licentie if you supply 0 as seizoen (which is why you begin with FROM scheidsrechters).

At least, that's what I read off that SELECT is doing.

[madref]

I can't seem to get it working.


Did what you told me to do.

Code: Pascal  [Select][+][-]

1.   TQ_Referee.DataBase := Form_Lint.Connect_RefereeDB;
2.   TQ_Referee.SQL.Text := cSQL;
3.   // Dit zorgt voor dat :Sei_id uit season_selection komt
4.   TQ_Referee.DataSource := DS_Wedstrijd; // DS_Seizoen;
5.   TQ_Referee.Active := True;
6.   for i := 0 to TQ_Referee.Fields.Count -  1 do
7.     begin
8.       if (TQ_Referee.Fields[i].DataType = ftmemo) or (TQ_Referee.Fields[i].DataType = ftWideMemo) then
9.         TQ_Referee.Fields[i].OnGetText := @dbmemoGetText;
10.     end;
11.  

[Zvoni]

Maybe you can show a (better) construct to have scheidsrechters and make a join with licenties with the requirements that you input a field seizoen.

No  need. I finally understood how it works, and i agree: it's actually a pretty clever way.
It just bugs me, that the filter is in the WHERE-Clause refering to a Field from a LEFT JOIN

Because the way i read it: It will always return those 4 Scheids_ID (since it's an OR!), additionally to whatever is returned from the JOIN

[paweld]

@madref: do you have an error message or do you still see (MEMO) instead of the value?

[rvk]

Because the way i read it: It will always return those 4 Scheids_ID (since it's an OR!), additionally to whatever is returned from the JOIN

Oops. Yes, you are correct. The rest is from the JOIN/WHERE.

@madref: do you have an error message or do you still see (MEMO) instead of the value?

Shouldn't that line with ftWideString also include ftString ??

Code: [Select]

      if (TQ_Referee.Fields[i].DataType = ftmemo) or (TQ_Referee.Fields[i].DataType = ftWideMemo) then

So:

Code: [Select]

if TQ_Referee.Fields[i].DataType in [ftmemo, ftWideMemo, ftString] then
ftString was the type that was changed because of the 1020 limit.
O, no, it was ftMemo that was added for stText.

[madref]

I can't seem to get it working... So I made a working small version.
Because it's to big for a post here.


You can download it here.
The database is also included.

[rvk]

I can't seem to get it working... So I made a working small version.
Because it's to big for a post here.

THAT'S not a SMALL example project !! That's HUGE.
Simple project would be to create just one form with a few comboboxes for testing the SQL.

I did manage to get it going (although I have no Mac and it was hard to change the paths etc).

It does seem that there is something else going on.
The problem only exists in the TDBLookupComboBox.
When I change the Scheidsrechter line to just this:
    'Achternaam AS Scheidsrechter, ' +
it does work (although not complete name of course).
But when I change it to this:
    'CAST(COALESCE(Achternaam, "aa") AS TEXT) AS Scheidsrechter, ' +
it stops working and the TDBLookupComboBox shows the (MEMO).

The fields go through the dbmemoGetText so that's not a problem.
I think the TDBLookupComboBox somehow checks the field type and might overrule the DisplayText entirely.

Haven't found a fix yet.