mORMot Open Source

ASiwon

Member

From: Poland

Registered: 2015-01-30

Posts: 82

Oracle - problem with limiting ordered data

Hello,

I found the problem with reading objects from the Oracle database if for query are used limit and order by clauses. For example for entity class:

TSQLBaby = class(TSQLRecord)
  private
    fName: RawUTF8;
    fAddress: RawUTF8;
    fBirthDate: TDateTime;
    fSex: TSex;
  published
    property Name: RawUTF8 read fName write fName;
    property Address: RawUTF8 read fAddress write fAddress;
    property BirthDate: TDateTime read fBirthDate write fBirthDate;
    property Sex: TSex read fSex write fSex;
end;

query:

lBaby := TSQLBaby.Create(Client, 'Name = ? order by BirthDate desc limit 1', ['SomeName']);

will be executed in Oracle as:

select Id, Name, Address, BirthDate, Sex from Baby where rownum <=1 and Name = 'SomeName'  order by BirthDate

The problem is using rownum and order by in Oracle SQL. RowNum is called before executing "order by" clause and from time to time query returns data not for the youngest baby. It is known Oracle behaviour: rownum conditions should not be used with queries with order by clause. In Oracle SQL this query should looks like:

select * from (
  select Id, Name, Address, BirthDate, Sex from Baby where Name = 'SomeName'  order by BirthDate) where rownum <=1

To correct this problem I made some changes in mORMot sources. In SynDb.pas file to TSQLDBDefinitionLimitPosition type I added new item: posOuter

TSQLDBDefinitionLimitPosition = (posNone, posWhere, posSelect, posAfter, posOuter);

for class TSQLDBConnectionProperties I changed function SQLLimitClause to:

function SQLLimitClause(const AStmt: TSynTableStatement): TSQLDBDefinitionLimitClause; virtual;

function TSQLDBConnectionProperties.SQLLimitClause(const AStmt: TSynTableStatement): TSQLDBDefinitionLimitClause;
begin
  result := DB_SQLLIMITCLAUSE[DBMS];
end;

only function header was changed. In the body of the function nothing was changed. In SynDbOracle unit for class TSQLDBOracleConnectionProperties I added function SQLLimitClause overrides:

function SQLLimitClause(const AStmt: TSynTableStatement): TSQLDBDefinitionLimitClause; override;

function TSQLDBOracleConnectionProperties.SQLLimitClause(const AStmt: TSynTableStatement): TSQLDBDefinitionLimitClause;
begin
  if AStmt.OrderByField <> nil then
  begin
    Result.Position := posOuter;
    Result.InsertFmt := 'select * from (%) where rownum <=%';
  end
  else
    Result := inherited SQLLimitClause(AStmt);
end;

In mORMotDB unit I made some changes in AdaptSQLForEngineList function. Block

    if Stmt.Limit=0 then
      limit.Position := posNone else begin
      limit := fProperties.SQLLimitClause;
      if limit.Position=posNone then begin
        InternalLog('%.AdaptSQLForEngineList: unknown "%" LIMIT syntax for [%]',
          [ClassType,ToText(fProperties.DBMS)^,SQL],sllWarning);
        exit;
      end;
      limitSQL := FormatUTF8(limit.InsertFmt,[Stmt.Limit]);
    end;

was changed to

    if Stmt.Limit=0 then
      limit.Position := posNone else begin
      limit := fProperties.SQLLimitClause(Stmt);
      if limit.Position=posNone then begin
        InternalLog('%.AdaptSQLForEngineList: unknown "%" LIMIT syntax for [%]',
          [ClassType,ToText(fProperties.DBMS)^,SQL],sllWarning);
        exit;
      end;
      if  limit.Position = posOuter then
        limitSQL := FormatUTF8(limit.InsertFmt,['%', Stmt.Limit])
      else
        limitSQL := FormatUTF8(limit.InsertFmt,[Stmt.Limit]);
    end;

and block:

      W.SetText(SQL);
      result := true;

was changed to:

      W.SetText(SQL);
      if limit.Position = posOuter then
        SQL := FormatUTF8(limitSQL, [SQL]);
      result := true;

After those changes SQL queries with ORDER BY clause in Oracle looks like:

select * from (
  select Id, Name, Address, BirthDate, Sex from Baby where Name = 'SomeName'  order by BirthDate) where rownum <=1

and SQL queries without ORDER BY clause in the Oracle looks like:

select Id, Name, Address, BirthDate, Sex from Baby where rownum <=1 and Name = 'SomeName'

I hope it will be possible to add those changes to the mORMot source.

---

best regards
Adam Siwon

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,660

Website

Re: Oracle - problem with limiting ordered data

Sounds just great to me.

Could you please send me the whole modified units as a .zip archive, by mail?
Or you may fork the project on github.

ASiwon

Member

From: Poland

Registered: 2015-01-30

Posts: 82

Re: Oracle - problem with limiting ordered data

Hi ab,

As you wish I added pull request on github. (https://github.com/synopse/mORMot/pull/14)

---

best regards
Adam Siwon

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,660

Website

Re: Oracle - problem with limiting ordered data

Included as http://synopse.info/fossil/info/27e44b1d

Thanks for the feedback!