JDBC Query : Why limit and offset are commented in log ?

[pnux_]

Hello,

I'm on Nuxeo 10.10 (LTS 2019) and Postgresql 11. (Elastic search is not enabled yet) [I have upgraded from nuxeo 5.5 to LTS 2019.]

I try to find performance issue. In log4j2.xml i've enabled

    <!-- Uncomment to debug SQL statements -->
    <Logger name="org.nuxeo.ecm.core.storage.sql.jdbc" level="trace" />

In log, i can see for example :

2019-02-21T16:38:29,835 TRACE [http-nio-0.0.0.0-8080-exec-14] [org.nuxeo.ecm.core.storage.sql.jdbc.JDBCLogger] (8) SQL: SELECT "hierarchy"."id" AS "_C1" FROM "hierarchy" LEFT JOIN "dublincore" "_F1" ON "hierarchy"."id" = "_F1"."id" WHERE (("hierarchy"."primarytype" IN ('TemplateRoot', 'Courriers', 'ModelDocFolder', 'Chemise', 'Collections', 'Client', 'ListeningFolder', 'CopyFolder', 'Domain', 'ListeningFolderWorkspace', 'OrderedFolder', 'Site', 'Folder', 'WorkspaceRoot', 'Factures', 'SectionRoot', 'Section', 'Workspace')) AND ("hierarchy"."parentid" = 7c262f57-b98c-46d6-9770-2e45f71ac258) AND ("hierarchy"."isversion" IS NULL) AND (("hierarchy"."istrashed" = false OR "hierarchy"."istrashed" IS NULL))) ORDER BY "hierarchy"."pos", "_F1"."title" -- LIMIT 50 OFFSET 0 -- COUNT TOTAL UP TO 50

So if i want to replay this query i have to uncomment LIMIT 50 OFFSET 0 .

• Does the real query (against database) use this commented limit and offset ?
• Why JDBC log shows this commented ?

Thank you.

[Florent_Guillau]

The logs are not exactly the SQL sent to the database, for instance we send a prepared statement with ? in them and the associated parameter values, but for the logs we replace those with readable versions of the values. For instance for dates the SQL will not be exactly database SQL. In the same vein, LIMIT and OFFSET and not always sent as is to the database, as some databases have different syntaxes to express this. But we include the information in the log nevertheless, as a pseudo-comment. Same for "COUNT TOTAL UP TO" which is not actual SQL but will be expressed in some way that depends on the database.

[pnux_]

Thank you for your answer, it's clear.