Recent

Author Topic: SQLite3 MAX() syntax  (Read 950 times)

Birger52

  • Full Member
  • ***
  • Posts: 114
SQLite3 MAX() syntax
« on: October 13, 2019, 07:50:45 pm »
So ... based on all your expert advises, I decided to use SQLite3 - at least look into it, and do some experiments.
And it's all fine - until this...
Code: Pascal  [Select]
  1. function TrackAntal(aSamling : integer) : integer;
  2. var
  3.   SQL3conn : TSQLite3Connection;
  4.   Query : TSQLQuery;
  5.   qry : string;
  6.   res : integer;
  7. begin
  8.   res := -1;
  9.   SQL3conn := DBConnect;
  10.   try
  11.     Query := TSQLQuery.Create(nil);
  12.     qry := 'SELECT MAX(idx) AS "ant" FROM sam_num WHERE samling="'+IntToStr(aSamling)+'"';
  13.     Query.SQL.Text := qry;
  14.     Query.Database := SQL3conn;
  15.     Query.Open;
  16.     res := 0;
  17.     while not Query.Eof do begin
  18.       res := Query.FieldByName('ant').AsInteger;
  19.       Query.Next;
  20.     end;
  21.     Query.Close;
  22.   finally
  23.     SQL3conn.Close;
  24.     SQL3conn.Destroy;
  25.   end;
  26.   TrackAntal := res; // -1 hvis fejl, 0 hvis ingen og ellers det største idx (største track-nummer) Det er ikek nødvendigvis det samme som antallet...
  27. end;
... it raises an exception in line 18:
"... class EConvertException with message: "" is an invalid integer"

This seems to be a problem in TSQLQuery; using lazarus's lazdatadesktop.exe, the SQL (line 12) executes just fine.
Problem probably is, that there are no records containing the asked for "samling"...
So TSQLQuery should return noting (Eof should be true immediately - like it does with other SELECT queries, that return no results).

Any thoughts or suggestions?

Birger52

  • Full Member
  • ***
  • Posts: 114
Re: SQLite3 MAX() syntax
« Reply #1 on: October 13, 2019, 07:56:35 pm »
a workaround I can use, as I0 is a usable value, if no records are found, is to replace line 17 .. 20 with
Code: Pascal  [Select]
  1.     ress := '0';
  2.     if not Query.Eof then
  3.       ress := Query.FieldByName('ant').AsString;
  4.     res := StrToInt( '0' + ress);

But I sill believe TSQLQuery has a bug, as this "solution" is not generally usable (a max() value can not be defined, if no records are found). Query.Eof should be true, and return no result.

Thaddy

  • Hero Member
  • *****
  • Posts: 9183
Re: SQLite3 MAX() syntax
« Reply #2 on: October 13, 2019, 08:25:32 pm »
If it is really missing from Sqlite3 then it can be added as a user function to Sqlite and you can even write it in Pascal  :D
But there is also an error in your SQL. idx is not a string type.... so the where clause is...?
« Last Edit: October 13, 2019, 08:47:49 pm by Thaddy »
also related to equus asinus.

fred

  • Full Member
  • ***
  • Posts: 156
Re: SQLite3 MAX() syntax
« Reply #3 on: October 13, 2019, 08:47:35 pm »
Quote
Problem probably is, that there are no records containing the asked for "samling"...
Or it can return NULL which is not a valid integer
https://www.sqlite.org/lang_corefunc.html#maxoreunc
You must handle NULL or use ifnull()
Started with OmegaSoft Pascal on OS-9/68k , now Lazarus 2.0.6 / FPC 3.0.4 on Windows 7

Birger52

  • Full Member
  • ***
  • Posts: 114
Re: SQLite3 MAX() syntax
« Reply #4 on: October 13, 2019, 10:21:08 pm »
@Thaddy.
idx is an attribute in the database (not id, but another number), and is defined as being an integer. (And so is samling).
So no - SQL is correct.

@fred.
That is it, of course.
Why it may not be a bug.
"Aggregate max() returns NULL if and only if there are no non-NULL values in the group."
(https://www.sqlite.org/lang_aggfunc.html#maxggunc - it is not a simple function but an aggregate function, when only one parameter)

Still think TSQLQuery.Eof should be true (TSQLQuery return nothing), when/if there are no values to return...
That is what it does, if one SELECT * FROM en empty database or use some WHERE clause that returns no records.
So selecting MAX() from something that doesn't exist, should also return nothing - not null...

rvk

  • Hero Member
  • *****
  • Posts: 3842
Re: SQLite3 MAX() syntax
« Reply #5 on: October 13, 2019, 10:46:00 pm »
Still think TSQLQuery.Eof should be true (TSQLQuery return nothing), when/if there are no values to return...
That is what it does, if one SELECT * FROM en empty database or use some WHERE clause that returns no records.
So selecting MAX() from something that doesn't exist, should also return nothing - not null...
No, MAX() always returns a result which is how it should be.
Quote
MAX returns NULL when there is no row to select.

All aggregate functions (like SUM, COUNT etc.) do.

zamronypj

  • New Member
  • *
  • Posts: 40
    • Fano Framework, Free Pascal web application framework
Re: SQLite3 MAX() syntax
« Reply #6 on: October 14, 2019, 11:20:51 pm »
Use COALESCE() to return first non-NULL from one or more arguments.If all arguments is NULL, it returns NULL.

Code: [Select]
SELECT COALESCE(MAX(idx), 0) as ant FROM sam_num WHERE samling = :aSamling
When MAX(idx) returns non NULL, COALESCE() returns that value,
When MAX(idx) returns NULL, then COALESCE() return 0

Alternatively you can use IFNULL(), which behave like COALESCE() with exactly two parameters.

Code: [Select]
SELECT IFNULL(MAX(idx), 0) as ant FROM sam_num WHERE samling = :aSamling

https://www.sqlite.org/lang_corefunc.html#coalesce
« Last Edit: October 14, 2019, 11:34:47 pm by zamronypj »
Fano Framework, Free Pascal web application framework https://fanoframework.github.io
Personal Projects https://v3.juhara.com
Github https://github.com/zamronypj

rvk

  • Hero Member
  • *****
  • Posts: 3842
Re: SQLite3 MAX() syntax
« Reply #7 on: October 14, 2019, 11:42:51 pm »
You can also do SELECT MAX(COALESCE(idx, 0))
which makes any idx value of NULL act as if it was 0.
Could be useful too when any idx could be NULL but you still want the largest value.

MAX() always returns NULL if even one value is NULL. The COALESCE inside MAX() solves this.

Birger52

  • Full Member
  • ***
  • Posts: 114
Re: SQLite3 MAX() syntax
« Reply #8 on: October 20, 2019, 12:11:49 am »
Thx for all the input.
Getting wiser is always good  8)