mORMot Retrieve using BETWEEN returns wrong data using MySQL

leus · 2021-01-12 19:35:54

I have a table with two values (COLFROM and COLTO). When I do the following:

ServerDB.Retrieve('? BETWEEN COLFROM AND COLTO', [], [SomeNumber], Result);

Using SQLite3 as backend, the results are as expected, but using MySQL, it gets all rows and returns whatever is first.

After some debugging, I found out that in this function, right after callin "InternalAdaptSQL", the StaticSQL variable loses its WHERE clause when using a MySQL server with Zeos (SQLite3 works fine). I *think* the problem may be in TSynTableStatement, because its Where property returns empty.

function TSQLRestServer.EngineList(const SQL: RawUTF8; ForceAJAX: Boolean;
  ReturnedRowCount: PPtrInt): RawUTF8;
var Rest: TSQLRest;
    StaticSQL: RawUTF8;
begin
  StaticSQL := SQL;
  Rest := InternalAdaptSQL(Model.GetTableIndexFromSQLSelect(SQL,false),StaticSQL); // <-- here
  if Rest=nil then
    result := MainEngineList(SQL,ForceAJAX,ReturnedRowCount) else
    result := Rest.EngineList(StaticSQL,ForceAJAX,ReturnedRowCount);
end;

As a workaround, I replaced the code as follows, and it works as expected in both databases.

ServerDB.Retrieve('COLFROM <= ? AND COLTO >= ?', [], [SomeNumber,SomeNumber], Result);

Last edited by leus (2021-01-12 19:38:07)

ab · 2021-01-13 09:33:19

What is the SQL value before InternalAdaptSQL() call?

leus · 2021-01-15 02:27:49

It was this (columns removed):

'SELECT RowID,...COLFROM,COLTO FROM SomeObject WHERE :(3001): BETWEEN COLFROM AND COLTO LIMIT 1'

It all went well until SynTable.TSynTableStatement.Create, around line 7616. Here the P buffer was

P = ':(3001): BETWEEN COLFROM AND COLTO LIMIT 1'

And in line 7616, GetPropIndex returns -1 and it causes Stmt.Where to remain empty, which causes the problem.

It seems that the problem is only when the parameter is first in the WHERE clause, because if I do '1=1 AND ? BETWEEN COLFROM AND COLTO' it works without problems (however it discarded it and switched engines because it was a complex statement).

Last edited by leus (2021-01-15 02:38:48)

ab · 2021-01-15 08:39:10

It should indeed ignore such a statement, and switch to the virtual tables content.
Please check https://synopse.info/fossil/info/58a925fd5a

Note that BETWEEN is not supported by the internal SQL adapter, so plain COLFROM <= ? AND COLTO >= ? is the best way to go.

leus · 2021-01-15 17:18:29

Excellent!

A related question: are parenthesis, IS and NOT statements supported by the internal SQL adapter? Something like 'A=1 and B=1 and not (D is null or D = 0)'?

Thank you for your incredible work, Arnaud.

ab · 2021-01-15 17:36:35

No, parenthesis should be avoided.

Only basic where clause is supported by now with a list of ... AND ... AND ... or a list of ... OR ... OR .... but not mixed AND + OR in the same clause, nor any parenthesis.
But NOT, IS NULL, IS NOT NULL, IN (....), LIKE, and function calls are supported.

mORMot Open Source

#1 2021-01-12 19:35:54

mORMot Retrieve using BETWEEN returns wrong data using MySQL

#2 2021-01-13 09:33:19

Re: mORMot Retrieve using BETWEEN returns wrong data using MySQL

#3 2021-01-15 02:27:49

Re: mORMot Retrieve using BETWEEN returns wrong data using MySQL

#4 2021-01-15 08:39:10

Re: mORMot Retrieve using BETWEEN returns wrong data using MySQL

#5 2021-01-15 17:18:29

Re: mORMot Retrieve using BETWEEN returns wrong data using MySQL

#6 2021-01-15 17:36:35

Re: mORMot Retrieve using BETWEEN returns wrong data using MySQL

Board footer