# FqlToSqlConverter.java

    /*
    ** Module   : FqlToSqlConverter.java
    ** Abstract : Handles conversion of FQL statements to SQL.
    **
    ** Copyright (c) 2019-2025, Golden Code Development Corporation.
    **
    ** -#- -I- --Date-- ---------------------------------------Description---------------------------------------
    ** 001 OM  20190915 First revision. Converts simple FQL queries into SQL.
    ** 002 OM  20201001 Improved DMO manipulation performance by caching slow Property annotation access.
    **     CA  20201003 Replaced Guava identity HashSet with Collections.newSetFromMap(IdentityHashMap).
    **     CA  20201005 Do not emit NULLS option for _multiplex and PK columns, in ORDER BY.
    **     OM  20201120 Avoid NPE in getScopedDmoInfo(). Changes error message to a more explicit one.
    **     IAS 20201126 Re-write SQL statement with CONTAINS to use words' table
    **     EVL 20210202 Workaround to eliminate NPE and resume client start.
    **     IAS 20210203 A better solution for the NPE.
    **     IAS 20210219 Word tables support for custom extents
    **     IAS 20210303 Added SQL re-writing using common table expressions (WITH clause)
    **     IAS 20210310 Added implicit ordering
    **     IAS 20210315 Use Database instead of schema in constructor, re-work implicit ordering
    **     IAS 20210321 Re-worked implicit ordering for word tables. Added implicit sorting for
    **                  _temp and 'dirty' databases.
    **     IAS 29210401 Flash _UserTableStat before access
    **     OM  20210412 Javadoc fixes. Dropped unneeded casts.
    **     ECF 20210420 Fixed schema lookup.
    **     OM  20210423 Fixed construction of word-index driven queries for specific cases.
    **     OM  20210503 The components of ORDER BY clause used by TemporaryBuffer.readAllRows() must be property
    **                  names not SQL column names. They will be converted to SQL later.
    **     RFB 20210504 Temporarily undo OM changes until the regression can be determined.
    **     IAS 20210506 Rework _UserTableStat flash logic as per Eric advise.
    **     IAS 20210905 Re-working re-writing queries with the values of the mutable SESSION attributes.
    **     OM  20210908 Used SchemaDictionary.TEMP_TABLE_DB instead of hardcoded constant.
    **     OM  20220212 Use ReservedProperty static method for testing reserved properties names.
    **     IAS 20220324 Re-worked LockTableUpdater.
    **     OM  20220409 Added support for List/array query parameters.
    **     OM  20220411 Added missing annotations for nodes created after pre-processing.
    **     OM  20220428 The extent cardinality (paramcard) is always 1.
    **     OM  20220627 rewriteSelectDynamicSubscripts() extracts the terms for list__index at top level.
    **     OM  20220628 When the parameters are reordered, a parameter permutation is created.
    **     OM  20220722 Added support for constants (strings & numbers) in list of selected elements.
    **     OM  20220727 FieldId and PropertyId are different for denormalized extent fields.
    **     SBI 20220811 Prepared the code removal.
    **         20221031 Collected fields of "select" query.
    **         20221101 Fixed possible NPE.
    **     OM  20220913 Enhanced dialect API for sorting nulls.
    **     OM  20221012 Fixed regression (invalid sort criteria) in previous commit.
    **     OM  20221103 New class names for FQLPreprocessor, FQLExpression, FQLBundle, and FQLCache.
    **     SVL 20230108 Improved performance by replacing some "for-each" loops with indexed "for" loops.
    **         20221031 Collected fields of "select" query.
    **         20221101 Fixed possible NPE.
    ** 003 RAA 20230213 Added the keyword "lazy". This can be appended to the SQL that gets generated.
    ** 004 AL2 20230316 Introduced wildcard selection for some temp-tables.
    ** 005 IAS 20230405 Second pass query generation for recursive DATA-RELATION FILL support
    **                  Added fix for H2 bug with bind parameters in recursive CTEs.
    **                  Fixed second pass query generation for queries with CONTAINS operator
    ** 006 DDF 20230306 Skip analyzing propertyStr too see whether it is a ComputedColumn when the
    **                  dialect doesn't require computed columns (refs: #7108).
    **                  Fixed second pass query generation for queries with CONTAINS operator.
    ** 007 OM  20230428 Made sure [aliases] is balanced. Avoid double-emit or skipping of FQLAst tokens.
    *                   Fixed handling of nested SUBSELECTs. Local optimizations. Bit of code cleanup.
    ** 008 GBB 20230512 Logging methods replaced by CentralLogger/ConversionStatus.
    ** 009 AL2 20230516 Include the number of SQL fields in the field counter.
    ** 010 IAS 20230706 Runtime changes for CONTAINS with non-constant search expression.
    ** 011 AB  20230803 Added support for the "ON" clause.
    **     AB  20230822 Fixed the spacing in generateJoin().
    ** 012 AL2 20231211 Use the new way of appending properties to the row structure.
    ** 013 AB  20231213 Updated generateFrom() to correctly create the inner-join.
    ** 014 ASL 20240105 Reverted changes in 013. Improved method generateWhere()
    **                  to include extent indexes-only case without an existing where clause.
    ** 015 IAS 20230208 Dialect-specific error handler support.
    ** 016 CA  20240320 Avoid BDTs within internal FWD runtime.  Replaced iterators with Java 'for', where it
    **                  applies.
    ** 017 OM  20240430 Allow normalized extent fields in query list, but once they are added to RowStructure
    **                  drop them from final SQL statement. The query will fetch them individually, later at
    **                  hydration time.
    ** 018 SP  20240513 Updated processSelect() for sql statements using contains, to avoid generating
    **                  same rewritten contains call when processing a subselect.
    ** 019 SP  20240517 Setting crwd.alwaysFalse earlier and updated containsWithWordTableAndCTE() to remove the
    **                  parameter, before appending (1 = 0) instead of the rewritten contains call.
    ** 020 TJD 20240123 Java 17 compatibility update
    ** 021 SP  20240603 Fixed cases when usages of contains on extent fields was resulting in a crash.
    **                  Use property.column directly when setting crwd.fldName instead of using NameConverter.
    ** 022 OM  20240801 Code maintenance: optimized imports, reduced accessibility of some methods.
    ** 023 SP  20240904 Small performance improvement to converted contains call. Code cleanup.
    **                  Use property.column directly when setting crwd.fldName instead of using NameConverter
    ** 024 DDF 20240709 Add alias for DATETIME-TZ offset column so that the recursive CTE name
    **                  uses the correct number of columns.
    **     DDF 20240710 Replaced appendDTZColAlias lambda with a method that has the same name.
    **     DDF 20240712 Removed appendDTZColAlias method, simplified implementation.
    ** 025 SP  20240924 Fixed a performance regression and improved the changes in H023.
    ** 026 SP  20240927 Fixed and extended optimizeContainsCTE flag for some cases.
    ** 027 SP  20241002 Don't create a CTE for CONTAINS if the result is expected to be alwaysFalse.
    **     SP  20240103 Fixed orderFields generation to support aliases used in a FUNCTION.
    ** 028 SP  20241111 Enable optimizeContainsCTE for all rewritten contains that use CTEs.
    **                  Replace P2JQuery.ParamResolver parameters with the actual resolved value.
    ** 029 AL2 20241129 Don't use DISTINCT clause for WCTE recid as it disables nested partial join optimization
    **                  in the DB engine.
    ** 030 SP  20250311 Added cache for parsed SELECT_STMT fql ASTs.
    ** 031 OM  20250326 Replaced in isVST() with getMetaTable() method in DmoMeta.
    ** 032 LS  20250502 Changed the signature of 'generateProperty'. Retrieve also the _offset column for
    **                  datetime-tz fields.
    */

    /*
    ** This program is free software: you can redistribute it and/or modify
    ** it under the terms of the GNU Affero General Public License as
    ** published by the Free Software Foundation, either version 3 of the
    ** License, or (at your option) any later version.
    **
    ** This program is distributed in the hope that it will be useful,
    ** but WITHOUT ANY WARRANTY; without even the implied warranty of
    ** MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
    ** GNU Affero General Public License for more details.
    **
    ** You may find a copy of the GNU Affero GPL version 3 at the following
    ** location: https://www.gnu.org/licenses/agpl-3.0.en.html
    **
    ** Additional terms under GNU Affero GPL version 3 section 7:
    **
    **   Under Section 7 of the GNU Affero GPL version 3, the following additional
    **   terms apply to the works covered under the License.  These additional terms
    **   are non-permissive additional terms allowed under Section 7 of the GNU
    **   Affero GPL version 3 and may not be removed by you.
    **
    **   0. Attribution Requirement.
    **
    **     You must preserve all legal notices or author attributions in the covered
    **     work or Appropriate Legal Notices displayed by works containing the covered
    **     work.  You may not remove from the covered work any author or developer
    **     credit already included within the covered work.
    **
    **   1. No License To Use Trademarks.
    **
    **     This license does not grant any license or rights to use the trademarks
    **     Golden Code, FWD, any Golden Code or FWD logo, or any other trademarks
    **     of Golden Code Development Corporation. You are not authorized to use the
    **     name Golden Code, FWD, or the names of any author or contributor, for
    **     publicity purposes without written authorization.
    **
    **   2. No Misrepresentation of Affiliation.
    **
    **     You may not represent yourself as Golden Code Development Corporation or FWD.
    **
    **     You may not represent yourself for publicity purposes as associated with
    **     Golden Code Development Corporation, FWD, or any author or contributor to
    **     the covered work, without written authorization.
    **
    **   3. No Misrepresentation of Source or Origin.
    **
    **     You may not represent the covered work as solely your work.  All modified
    **     versions of the covered work must be marked in a reasonable way to make it
    **     clear that the modified work is not originating from Golden Code Development
    **     Corporation or FWD.  All modified versions must contain the notices of
    **     attribution required in this license.
    */

    package com.goldencode.p2j.persist.orm;

    import static com.goldencode.p2j.persist.orm.FQLParserTokenTypes.*;

    import java.io.*;
    import java.util.*;
    import java.util.concurrent.atomic.*;
    import java.util.function.*;
    import java.util.logging.*;
    import java.util.stream.*;
    import com.goldencode.ast.*;
    import com.goldencode.cache.*;
    import com.goldencode.p2j.cfg.*;
    import com.goldencode.p2j.persist.*;
    import com.goldencode.p2j.persist.FQLPreprocessor.*;
    import com.goldencode.p2j.persist.Record;
    import com.goldencode.p2j.persist.dialect.*;
    import com.goldencode.p2j.persist.orm.Query.*;
    import com.goldencode.p2j.persist.meta.*;
    import com.goldencode.p2j.persist.meta.MetadataManager.*;
    import com.goldencode.p2j.persist.orm.DmoMeta.*;
    import com.goldencode.p2j.schema.*;
    import com.goldencode.p2j.util.*;
    import com.goldencode.p2j.util.LogicalExpressionConverter.*;
    import com.goldencode.p2j.util.logging.*;
    import org.apache.commons.lang3.tuple.*;
    import antlr.*;

    /**
     * This class handles conversion of FQL statements into SQL using a specified
     * {@code Dialect}. The implementation targets the correctness and performance, so a minimum
     * number of walks on the parsed tree must be executed. TRPL is obviously, excluded.
     */
    public class FqlToSqlConverter
    {
       /** Logger */
       private static final CentralLogger LOG = CentralLogger.get(FqlToSqlConverter.class.getName());

       /** Cache of parsed fql ASTs */
       private static final LRUCache<String, FQLAst> fqlAstCache =
                               CacheManager.createLRUCache(FqlToSqlConverter.class, "ast", 8192);

       /** Name of the primary key column. */
       private static final String PK = Configuration.getSchemaConfig().getPrimaryKeyName();

       /** Constant for a virtual alias when none is specified (used in case of single table statements). */
       private static final String NO_ALIAS = "";

       /** The ASTERISK literal used in aggregate SQL functions. */
       private static final char ASTERISK = '*';

       /** The dialect used to construct the SQL statement. */
       private final Dialect dialect;

       /** The database */
       private final Database db;

       /** The schema of the database. This is used to unambiguate short-names entities. */
       private final String schema;

       /** The {@code StringBuilder} used to construct the SQL statement. */
       private StringBuilder sb = null;

       /**
        * The {@code StringBuilder} used to construct an optional single property name, if not {@code null}.
        * Ignored otherwise.
        */
       private StringBuilder sbProp = null;

       /** Counter for making column aliases unique. */
       private int columnUid = 0;

       /**
        * The aliases (table names) currently used in this statement. Each level represents additional
        * aliases added at each nested select. We need to exclude them when their scope ends.
        */
       private final ArrayDeque<HashMap<String, DmoMeta>> aliases = new ArrayDeque<>();

       /** The aliases used in SQL queries. */
       private final Map<String, String> sqlTableAliasMap = new HashMap<>();

       /**
        * If this flag is {@code true} the SQL names will be used for table aliases, otherwise the
        * FQL named received in FQL string will be used.
        */
       private final boolean useSqlTableAlias;

       /**
        * If this flag is {@code true}, the selected columns are named. An unique name is computed for
        * each column.
        */
       private final boolean generateUniqueSqlColumnNames;

       /** The maximum numbers of results in a query. Used for paging results. */
       private int maxResults = -1;

       /** Flag for selecting results in a lazy manner. Used in {@code Select} statements. */
       private boolean lazyMode = false;

       /** The offset of the record to start a result with. Used for paging results. */
       private int startOffset = -1;

       /** The number of SQL parameters encountered during parsing. */
       private int paramCount = 0;

       /**
        * The structure of the result to be returned. For each record returned, the number of fields
        * is stored in order to facilitate the re-hydration of the result back to {@code Record}s.
        * This is a by-product of the query statement conversion.
        */
       private ArrayList<RowStructure> rowStructure = null;

       /** The number of subscript with substitution parameter, like <code>[?]</code>. */
       private int complexSubscript = 0;

       /** Flag identifying to alias will be emitted for properties.  Required by the UPDATE statement. */
       private boolean forceNoAlias = false;

       /** Query parameters. */
       private final QueryParams queryParams;

       /** Parameters permutation. */
       private int[] paraPerm = null;

       /** Flags the a query was re-written during conversion to SQL */
       private boolean queryWasRewritten = false;

       /** Flag indicating that the query reads from _UserTableStat VST */
       private boolean userTableStatRead = false;

       /** Flag indicating that the query reads from _Lock VST */
       private boolean lockTableRead = false;

       /** Re-write data for CONTAINS operators */
       private final ArrayList<ContainsRewriteData> containsRewriteData = new ArrayList<>();

       /** Level of nested function calls for the CONTAINS first argument */
       // TODO: do not add RTRIM for the CONTAINS field in the HQLPreprocessor
       private int nestedInContains = 0;

       /** Index of currently processed CONTAINS */
       private int currentContains = -1;

       /** positional parameters found so far */
       private int nargs = 0;

       /** number of CTE parameters */
       private int nCteArgs = 0;

       /** generator for the CTE suffix */
       private int cteNo = 0;

       /** CTE used for implicit ordering imposed by CONTAINS */
       private ContainsRewriteData cte4order = null;

       /** Holder for 'weights' used for implicit ordering imposed by CONTAINS */
       private final StringBuilder weigths = new StringBuilder();

       /** 'use word tables' flag */
       private final boolean useWordTables;

       /**
        * Flags that we can optimize CONTAINS operator. Only for contains rewritten using word tables and CTEs.
        * When the where clause is simple with no ORs
       */
       private boolean optimizeContainsCTE;

       /** Number of AND terms */
       private int andTerms = 0;

       /** Number of OR terms */
       private int orTerms = 0;

       /** List of the row fields/ aliases */
       private final List<String> rowAliases = new ArrayList<>();

       /** The recursive DATA-RELATION the query is to FILL for */
       private DataRelation relation = null;

       /** SELECT fields' aliases by legacy name */
       private Map<String, String> selectAliases = null;

       /** SELECT fields' names by legacy name */
       private Map<String, String> selectFields = null;

       /** Collector for SELECT fields' aliases */
       private BiConsumer<String, String> collectAliases = (l, a) -> {};

       /** Collector for SELECT fields' names */
       private BiConsumer<String, String> collectFields = (l, f) -> {};

       /** Flags that query bind parameters should be mapped to session variables */
       private boolean bindParam2SessionVar = false;

       /** Number of bind parameters. */
       private final AtomicInteger nbind = new AtomicInteger(0);

       /** Supplier of bind parameter placeholder */
       private Supplier<String> argRef = () -> "?" ;

       /** Mutable session attributes' values used in the query */
       private final List<SessionAttr> sessionAttrs = new ArrayList<>();

       /** The name of the checkError UDF with prepended dot */
       private final String _checkErrorFn;

       /** Flag indicating that the query contains error handling */
       private boolean containsErrorHandling = false;
       /**
        * The constructor is private. To create an instance of this class, use the {@code getInstance}
        * builder method.
        *
        * @param   dialect
        *          The SQL dialect to be used when generating SQL statements.
        * @param   db
        *          The database.
        * @param   queryParams
        *          queryParameters;
        * @param   useSqlTableAlias
        *          When {@code true}, the SQL names will be used for table aliases, otherwise the
        *          FQL named received in FQL string will be kept.
        */
       private FqlToSqlConverter(Dialect dialect, Database db, QueryParams queryParams, boolean useSqlTableAlias)
       {
          this.dialect = dialect;
          this.db = db;
          this.schema = db.isMeta() ? MetadataManager.META_SCHEMA : DatabaseManager.getSchema(db);
          this.queryParams = queryParams;
          this.useSqlTableAlias = useSqlTableAlias;
          this.generateUniqueSqlColumnNames = true;
          this.useWordTables = !SchemaDictionary.TEMP_TABLE_DB.equals(schema) &&
                               !db.isDirty() && !db.isMeta() && dialect.useWordTables();
          this._checkErrorFn = "." + dialect.checkErrorFn();
       }

       /**
        * Builder method for create an instance of this class.
        *
        * @param   dialect
        *          The SQL dialect to be used when generating SQL statements.
        * @param   db
        *          The database.
        *
        * @return  an instance of this class
        */
       public static FqlToSqlConverter getInstance(Dialect dialect, Database db)
       {
          return getInstance(dialect, db, new QueryParams());
       }

       /**
        * Builder method for create an instance of this class.
        *
        * @param   dialect
        *          The SQL dialect to be used when generating SQL statements.
        * @param   db
        *          The database.
        * @param   queryParams
        *          The query parameters.
        *
        * @return  an instance of this class
        */
       public static FqlToSqlConverter getInstance(Dialect dialect, Database db, QueryParams queryParams)
       {
          return new FqlToSqlConverter(dialect, db, queryParams, true);
       }

       /**
        * Check if the query was re-written
        *
        * @return <code>true</code> if the query was re-written
        */
       public boolean isQueryWasRewritten()
       {
          return queryWasRewritten;
       }

       /**
        * Check if the query bind parameters should be mapped to session variables.
        *
        * @return <code>true</code> if the query parameters should be mapped to session variables.
        */
       public boolean bindParam2SessionVar()
       {
          return bindParam2SessionVar;
       }

       /**
        * Get list of mutable session attributes' values used in the query.
        *
        * @return List of mutable session attributes' values used in the query.
        */
       public List<SessionAttr> sessionAttrs()
       {
          return sessionAttrs;
       }

       /**
        * Checks if the query contains error handling.
        *
        * @return  {@code true} if the query contains error handling.
        */
       public boolean containsErrorHandling()
       {
          return containsErrorHandling;
       }

       /**
        * Check if the query reads _UserTableStat VST.
        *
        * @return <code>true</code> if the query reads _UserTableStat VST.
        */
       public boolean isUserTableStatRead()
       {
          return userTableStatRead;
       }

       /**
        * Check if the query reads _Lock VST.
        *
        * @return <code>true</code> if the query reads _Lock VST.
        */
       public boolean isLockTableRead()
       {
          return lockTableRead;
       }

       /**
        * Re-write CONTAINS using word tables and CTE.
        *
        * @param   crwd
        *          Structure holding the re-write data to be processed.
        */
       private  void containsWithWordTableAndCTE(ContainsRewriteData crwd)
       {
          if (crwd.narg >= 0)
          {
             queryParams.removeParam(nargs - 1);
             paramCount = paramCount - 1;
             nargs = nargs - 1;
          }
          if (crwd.alwaysFalse)
          {
             sb.append("(1 = 0)");
             return;
          }

          queryParams.insertParam(nCteArgs, crwd.args.toArray());
          paramCount += crwd.args.size();
          nargs += crwd.args.size();
          nCteArgs += crwd.args.size();

          // "(ALIAS.recid in (select recid from wcte1))" is being skipped. We will
          // later replace the left join with a simple join which will work the same.
          if (optimizeContainsCTE)
          {
             return;
          }
          sb.append("(").
             append(crwd.tblAlias).append(".").append(PK).
             append(" in (select ").append(PK).append(" from ").append(crwd.cteName).
             append(")");
          sb.append(")");
       }

       /**
        * Re-write CONTAINS using word tables.
        *
        * @param   crwd
        *          Structure holding the re-write data to be processed.
        */
       private void containsWithWordTable(ContainsRewriteData crwd)
       {
          sb.append("(");
          crwd.containsQuery(schema, sb);
          sb.append(")");
          if (crwd.narg >= 0)
          {
             queryParams.replaceParam(nargs - 1, crwd.args.toArray());
             paramCount += crwd.args.size() - 1;
             nargs += crwd.args.size() - 1;
          }
          else
          {
             queryParams.insertParam(nargs, crwd.args.toArray());
             paramCount += crwd.args.size();
             nargs += crwd.args.size();
          }
       }

       /**
        * Set field property for CONTAINS.
        *
        * @param   crwd
        *          Structure holding the re-write data to be processed.
        */
       private void setFieldProperty(ContainsRewriteData crwd)
       {
          DmoMeta meta = getScopedDmoInfo(crwd.tblMetaKey);
          if (meta == null)
          {
             throw new IllegalStateException("No DmoMeta for [" + crwd.tblName + "]/[" + crwd.tblAlias + "]");
          }

          Property fp = meta.propsByName.get(crwd.fldName);
          if (fp == null)
          {
             fp = meta.propsByName.values().stream().
                                  filter(p -> crwd.fldName.equals(p.column)).
                                  findFirst().orElse(null);
          }
          if (fp == null)
          {
             throw new IllegalStateException("Unknown field: [" + crwd.fldName + "]" );
          }

          crwd.fp = fp;
          if (fp.extent > 0 && !fp.denormalized)
          {
             crwd.extentTable = meta.sqlTable + "__" + fp.extent;
          }
       }

       /**
        * Re-write CONTAINS using UDF.
        *
        * @param   crwd
        *          Structure holding the re-write data to be processed.
        */
       private void containsWithUdf(ContainsRewriteData crwd)
       {
          setFieldProperty(crwd);
          int extent = crwd.fp.extent;
          boolean caseSensitive = crwd.fp.caseSensitive;
          StringBuilder contains = new StringBuilder(dialect.containsUdfName()).append("(");
          toUpper(!caseSensitive, contains, () -> contains.append(crwd.fldName));
          contains.append(", ");
          toUpper(!caseSensitive, contains, () ->
          {
             if (crwd.narg >= 0)
             {
                contains.append("?");
             }
             else
             {
                contains.append("'").append(crwd.expr).append("'");
             }

          });
          contains.append(")");
          if (extent == 0)
          {
             sb.append(contains);
          }
          else
          {
             sb.append("(").append(PK).
                append(" in (select distinct parent__id  from ").
                append(crwd.extentTable).
                append(" where ").append(contains).append("))");
          }
       }

       /**
        * Re-write CONTAINS using UDF if corresponding word index is used for sorting
        *
        * @param   crwd
        *          Structure holding the re-write data to be processed.
        */
       private void containsWithUdfAndCTE(ContainsRewriteData crwd)
       {
          if (crwd.narg >= 0)
          {
             queryParams.removeParam(nargs - 1);
             paramCount = paramCount - 1;
             nargs = nargs - 1;
          }

          queryParams.insertParam(nCteArgs, crwd.args.toArray());
          paramCount += crwd.args.size();
          nargs += crwd.args.size();
          nCteArgs += crwd.args.size();

          // "(ALIAS.recid in (select recid from wcte1))" is being skipped. We will
          // later replace the left join with a simple join which will work the same.
          if (optimizeContainsCTE)
          {
             return;
          }

          sb.append("(").
             append(crwd.tblAlias).append(".").append(PK).
             append(" in (select ").append(PK).append(" from ").append(crwd.cteName).
             append(")");
          sb.append(")");
       }

       /**
        * Converts a {@code FQL} statement into its SQL representation.
        *
        * @param   fql
        *          A valid {@code FQL} statement.
        * @param   maxResults
        *          The maximum numbers of results to be returned.
        * @param   startOffset
        *          The start offset. Together with {@code maxResults} this parameter helps to implement paging of
        *          a bigger the result set.
        * @param   rowStructure
        *          (Acts as an output parameter). If not {@code null}, at return, the map will contain
        *          the list of entities returned and the number of fields coded in the generated
        *          {@code SELECT} statement for each of them.
        * @param   relation
        *          The recursive DATA-RELATION the query is to FILL for.
        * @param   lazyMode
        *          Should the returned SQL execute the statement in a lazy manner?
        *          {@code true} if the answer is yes, {@code false} otherwise.
        *
        * @return  The SQL representation of the {@code fql}.
        */
       public String toSQL(String fql,
                           int maxResults,
                           int startOffset,
                           ArrayList<RowStructure> rowStructure,
                           DataRelation relation,
                           boolean lazyMode)
       {
          if (LOG.isLoggable(Level.FINE))
          {
             LOG.log(Level.FINE,
                     "FQL: " + fql + ", LIMIT: " + maxResults + ", START AT: " + startOffset);
          }

          this.rowStructure = rowStructure;
          if ((this.relation = relation) != null)
          {
             this.selectAliases = new LinkedHashMap<>();
             this.selectFields = new LinkedHashMap<>();
             this.collectAliases = this.selectAliases::put;
             this.collectFields = this.selectFields::put;
             this.bindParam2SessionVar = !dialect.allowBindParamsInRecursiveCTE();
             if (bindParam2SessionVar)
             {
                this.argRef =  () -> "@_v" + nbind.incrementAndGet();
             }
          }
          FQLAst root;
          synchronized (fqlAstCache)
          {
             root = fqlAstCache.get(fql);
          }
          if (root == null)
          {
             FQLParser parser = new FQLParser(new FQLLexer(new StringReader(fql)));
             parser.setListener(this);
             try
             {
                parser.statement();
             }
             catch (RecognitionException | TokenStreamException e)
             {
                if (LOG.isLoggable(Level.SEVERE))
                {
                   LOG.log(Level.SEVERE, "Failed to parse FQL statement [" + fql + "]", e);
                }
                return null;
             }
             root = (FQLAst) parser.getAST();
             if (LOG.isLoggable(Level.FINER))
             {
                LOG.log(Level.FINER, "Parsed Tree: " + root.dumpTree(true));
             }
             if (root.getType() == SELECT_STMT)
             {
                synchronized (fqlAstCache)
                {
                   fqlAstCache.put(fql, (FQLAst) root.duplicate());
                }
             }
          }
          else
          {
             root = (FQLAst) root.duplicate();
          }
          paramCount = 0;
          preprocess(root);
          columnUid = 0;

          sb = new StringBuilder(fql.length());
          this.maxResults = maxResults;
          this.startOffset = startOffset;
          this.lazyMode = lazyMode;
          processStatement(root, new FQLAst[1]);

          String sql = sb.toString();
          if (LOG.isLoggable(Level.FINE))
          {
             LOG.log(Level.FINE, "SQL: " + sql);
          }

          return sql;
       }

       /**
        * Obtain the parameter permutation for the converted SQL.
        *
        * @return  The parameter permutation function, if any detected.
        */
       public int[] getParameterPermutation()
       {
          return paraPerm;
       }

       /**
        * Returns the number of parameters in the last converted statement.
        *
        * @return  The number of parameters (that is SUBST and POSITIONAL nodes) encountered while
        *          processing the last successfully converted FQL statement.
        */
       public int getLastConversionParamCount()
       {
          return paramCount;
       }

       /**
        * TODO: replace
        *          upper(rtrim(alias.ch-property)) with alias.__ich-property and
        *                rtrim(alias.ch-property)  with alias.__sch-property
        *       if the dialect requires so (H2) and only if the property is part of an indexed.
        * WARNING:  this should not be done here, see the DatabaseManager.getIgnoreCase usage - the computed
        * column should be emitted explicitly.
        *
        * Does a quick preprocess of the AST tree after the initial parsing. The following
        * transformations are executed:
        * <ul>
        *    <li>"index(PROPERTY)" is converted to "[ALIAS].list__index [PROPERTY]";</li>
        *    <li>"alias.property [PROPERTY]" is converted to "alias [ALIAS].property [PROPERTY]";</li>
        *    <li>simplify some (sub-)expressions by dropping the extra LPARENS;</li>
        *    <li>simplify [(a = b) = true] to [a = b]</li>
        *    <li>counts the parameters in {@code paramCount} field;</li>
        *    <li>for full-path DMOs, make sure the interface is registered with {@code DmoMetadataManager}</li>
        *    <li>TO be added as encountered ...</li>
        * </ul>
        *
        * @param   root
        *          The root of the AST tree of a statement.
        */
       private void preprocess(FQLAst root)
       {
          AstWalkListener awl = new AstWalkListener()
          {
             /**
              * Called whenever a transition from a parent node to its first child takes place during
              * an AST walk. This will be called <em>before</em> the next AST node in the tree
              * traversal is visited.
              *
              * @param   ast
              *          Source AST node which represents the parent in the parent to child transition
              *          of the current AST walk.
              */
             @Override
             public void descent(Aast ast)
             {
             }

             /**
              * Called when the AST walk ascends between levels of the tree. Used to close off certain
              * constructs when emitting HQL.
              *
              * @param   node
              *          AST node to which the walk is ascending.
              */
             public void ascent(Aast node)
             {
                if (node == null)
                {
                   return;
                }

                if (node.getType() == FUNCTION                  &&
                    node.getNumImmediateChildren() == 1         &&
                    node.getFirstChild().getType() == PROPERTY  &&
                    "index".equals(node.getText()))    // TODO: extract [index] as constant
                {
                   // convert from "index(PROPERTY)" to "ALIAS.list__index"
                   int nodeIdx = node.getIndexPos();
                   Aast parent = node.getParent();
                   Aast alias = (Aast) node.getFirstChild();
                   alias.remove();
                   node.remove();
                   // do the changes:
                   alias.setType(ALIAS);
                   node.setType(PROPERTY);
                   node.setText("list__index");  // TODO: extract [list__index] as constant
                   // reassemble back:
                   alias.graft(node);
                   parent.graftAt(alias, nodeIdx);
                   return; // done processing this node
                }

                if (node.getType() == EQUALS)
                {
                   // simplify [(a = b) = true] to [a = b] and [(a = b) = false] to [a != b]
                   Aast firstChild = (Aast) node.getFirstChild();
                   Aast secondChild = (Aast) firstChild.getNextSibling();
                   if (firstChild.getType() == BOOL_TRUE || firstChild.getType() == BOOL_FALSE)
                   {
                      if (secondChild.getType() == EQUALS || secondChild.getType() == NOT_EQ)
                      {
                         Aast parent = node.getParent();
                         int idx = node.getIndexPos();
                         node.remove();
                         secondChild.remove();
                         parent.graftAt(secondChild, idx);
                         if (firstChild.getType() == BOOL_FALSE)
                         {
                            secondChild.setType(secondChild.getType() == EQUALS ? NOT_EQ : EQUALS);
                         }
                         return;
                      }
                   }
                   else if (secondChild.getType() == BOOL_TRUE || secondChild.getType() == BOOL_FALSE)
                   {
                      if (firstChild.getType() == EQUALS || firstChild.getType() == NOT_EQ)
                      {
                         Aast parent = node.getParent();
                         int idx = node.getIndexPos();
                         node.remove();
                         firstChild.remove();
                         parent.graftAt(firstChild, idx);
                         if (secondChild.getType() == BOOL_FALSE)
                         {
                            firstChild.setType(firstChild.getType() == EQUALS ? NOT_EQ : EQUALS);
                         }
                         return;
                      }
                   }
                }

                switch (node.getType())
                {
                   case AND:
                      andTerms++;
                   case GT:
                   case GTE:
                   case LT:
                   case LTE:
                   case EQUALS:
                   case FUNCTION:
                      if (node.getParent() != null              &&
                          node.getParent().getType() == LPARENS &&
                          node.getParent().getParent().getType() == AND)
                      {
                         // simplify expression by dropping the extra LPARENS
                         Aast lparens = node.getParent();
                         Aast and = lparens.getParent();
                         int nodeIdx = lparens.getIndexPos();
                         lparens.remove();
                         and.graftAt(node, nodeIdx);
                      }
                      break;

                   case OR:
                      orTerms++;
                      break;

                   case IS_NULL:
                   case NOT_NULL:
                      if (node.getParent() != null && node.getParent().getType() != LPARENS)
                      {
                         // Hibernates injects a LPARENS as parent here to be sure the IS/NOT NULL is
                         // always parenthesised but we do not (at this moment) to keep the expression
                         // simplified
                      }
                      break;

                   case LPARENS:
                      if (node.getParent() != null && node.getParent().getType() == WHERE)
                      {
                         // simplify expression by dropping this extra LPARENS
                         Aast child = (Aast) node.getFirstChild();
                         Aast where = node.getParent();
                         int nodeIdx = node.getIndexPos();
                         node.remove();
                         where.graftAt(child, nodeIdx);
                      }

                   default:
                      break;
                }
             }

             /**
              * Called whenever a transition from a child node to its next right sibling takes place
              * during an AST walk. This will be called <em>before</em> the next AST node in the tree
              * traversal is visited. The parent node of the children is in scope at the time of
              * notification. This effectively mimics recursion by notifying the listener when one
              * child node has been processed, before the next is visited. This allows the listener to
              * perform parent-level processing between visits to child nodes.
              * <p>
              * This method is <em>not</em> invoked after the last child is visited, as this is the
              * purpose of the {@link #ascent} method.
              *
              * @param   ast
              *          Source AST node which represents the parent of the children between which the
              *          lateral transition is taking place in the current AST walk.
              * @param   index
              *          The index of the child.
              */
             @Override
             public void nextChild(Aast ast, int index)
             {
             }
          };

          ArrayList<Pair<FQLAst, Integer>> propsToBeExpanded = new ArrayList<>();
          Iterator<Aast> iter = root.iterator(0, awl);
          while (iter.hasNext())
          {
             Aast node = iter.next();
             int ntype = node.getType();

             if (ntype == SUBST || ntype == POSITIONAL)
             {
                node.putAnnotation("paramNo", (long) paramCount);
                node.putAnnotation("paramCard", (long) queryParams.getCardinality(paramCount));
                ++paramCount;
                continue;
             }

             FQLAst firstChild = (FQLAst) node.getFirstChild();
             if ((firstChild == null || firstChild.getType() == LBRACKET) && ntype == PROPERTY)
             {
                String nodeText = node.getText();
                int k = nodeText.indexOf('.');
                if (k != -1)
                {
                   propsToBeExpanded.add(Pair.of((FQLAst) node, k));
                }
                continue;
             }

             if (firstChild == null && ntype == DMO)
             {
                // make sure the DMO interface is loaded and have access to implementation
                String nodeText = node.getText();
                if (nodeText.startsWith(DmoMetadataManager.getDmoBasePackage()))
                {
                   try
                   {
                      DmoMeta dmoInfo = DmoMetadataManager.getDmoInfo(nodeText, schema);
                      Class<? extends Record> impl = dmoInfo.implClass;
                      node.setText(impl.getSimpleName());
                   }
                   catch (Exception e)
                   {
                      LOG.log(Level.SEVERE, "Failed to load DMO: " + nodeText, e);
                   }
                }
             }
          }

          // [alias.property:PROPERTY] nodes are converted to [alias:ALIAS].[property:PROPERTY]
          // this is done after the full tree was processed because the changes might interfere with normal
          // iteration of the AST
          for (int i = 0; i < propsToBeExpanded.size(); i++)
          {
             Pair<FQLAst, Integer> pair = propsToBeExpanded.get(i);
             FQLAst node = pair.getLeft();
             int k = pair.getRight();
             String nodeText = node.getText();
             FQLAst firstChild = (FQLAst) node.getFirstChild(); // optional LBRACKET subtree
             if (firstChild != null)
             {
                firstChild.remove();
             }

             FQLAst newNode = new FQLAst();
             newNode.setType(PROPERTY);
             newNode.setText(nodeText.substring(k + 1));
             node.setType(ALIAS);
             node.setText(nodeText.substring(0, k));
             node.graft(newNode);
             if (firstChild != null)
             {
                newNode.graft(firstChild);
             }
          }
       }

       /**
        * Converts any FQL statement into the SQL language. The output is directly appended to {@code sb}.
        *
        * @param   root
        *          The root of the {@code FQLAst} statement.
        * @param   lastProcessedAst
        *          Output parameter: the last processed Ast. Allow a caller to know where the SUBSELECT ends.
        */
       private void processStatement(FQLAst root, FQLAst[] lastProcessedAst)
       {
          switch (root.getType())
          {
             case SUBSELECT:
                // register the entire tree as processed
                Iterator<Aast> iter = root.iterator();
                while (iter.hasNext())
                {
                   lastProcessedAst[0] = (FQLAst) iter.next();
                }
             case SELECT_STMT:
                processSelect(root);
                break;

             case DELETE_STMT:
                processDelete(root);
                break;

             case UPDATE_STMT:
                processUpdate(root);
                break;

             case INSERT_STMT:
                // TODO: add INSERT for completeness
                throw new UnsupportedOperationException("FQL INSERT statement is not supported!");
          }
       }

       /**
        * Converts a {@code UPDATE} FQL statement into the SQL language. The output is directly
        * appended to {@link #sb}.
        *
        * @param   stmt
        *          The {@code UPDATE} FQL statement to be converted.
        */
       private void processUpdate(FQLAst stmt)
       {
          this.forceNoAlias = true;

          // collect the initial aliases and their DMO mappings from DMO node
          HashMap<String, DmoMeta> topLevelAliasMap = new LinkedHashMap<>();
          FQLAst dmoNode = (FQLAst) stmt.getImmediateChild(DMO, null);
          String dmoName = dmoNode.getText();
          DmoMeta dmoInfo = DmoMetadataManager.getDmoInfo(dmoName, schema);
          topLevelAliasMap.put(NO_ALIAS, dmoInfo);
          String sqlAlias = dmoName.substring(0, Math.min(dmoName.length(), 10));
          sqlAlias = sqlAlias.toLowerCase() + this.sqlTableAliasMap.size() + "_";
          sqlTableAliasMap.put(NO_ALIAS, sqlAlias);
          FQLAst whereNode = (FQLAst) stmt.getImmediateChild(WHERE, null);
          FQLAst setNode = (FQLAst) stmt.getImmediateChild(SET, null);

          // rewrite all standalone PROPERTY nodes to add the ALIAS from the SET and WHERE nodes
          rewriteNoAliasPropertyNodes(NO_ALIAS, setNode);
          if (whereNode != null)
          {
             rewriteNoAliasPropertyNodes(NO_ALIAS, whereNode);
          }

          // do this before rewriting the extent properties, so we can access the aliases
          aliases.push(topLevelAliasMap);
          collectNormalizedExtentsAliases(whereNode,
                                          topLevelAliasMap,
                                          null,
                                          this::rewriteSubscriptsAsSubselect,
                                          this::rewriteSubscriptsAsSubselect);

          sb.append("\nupdate ").append(dmoInfo.getSqlTableName()).append(" set");
          generateSet(setNode);
          generateWhere(whereNode, null);

          if (aliases.pop() != topLevelAliasMap)
          {
             warn("Imbalance in FQL scopes.", stmt);
          }
       }

       /**
        * Generate the <code>SET</code> phrase for the <code>UPDATE</code> statement.
        *
        * @param    set
        *           The AST for the SET phrase.
        */
       private void generateSet(FQLAst set)
       {
          // TODO: this does not allow extent fields.  The FWD usage at this time is only by calls from
          //       TemporaryBuffer.removeRecords.
          FQLAst child = (FQLAst) set.getFirstChild();
          boolean first = true;
          while (child != null)
          {
             if (first)
             {
                first = false;
             }
             else
             {
                sb.append(",");
             }

             StringBuilder sbBackup = sb;
             try
             {
                sb = new StringBuilder();
                generateExpression(child);
                sbBackup.append("\n\t").append(sb);
             }
             finally
             {
                sb = sbBackup;
             }
             child = (FQLAst) child.getNextSibling();
          }
       }

       /**
        * Converts a {@code DELETE} FQL statement into the SQL language. The output is directly
        * appended to {@link #sb}.
        *
        * @param   stmt
        *          The {@code UPDATE} FQL statement to be converted.
        */
       private void processDelete(FQLAst stmt)
       {
          // collect the initial aliases and their DMO mappings from FROM
          HashMap<String, DmoMeta> topLevelAliasMap = new LinkedHashMap<>();
          FQLAst fromNode = (FQLAst) stmt.getImmediateChild(FROM, null);
          FQLAst whereNode = (FQLAst) stmt.getImmediateChild(WHERE, null);
          collectTableAliases(fromNode, topLevelAliasMap);

          String alias = topLevelAliasMap.keySet().iterator().next();
          // rewrite all standalone PROPERTY nodes to add the ALIAS from the FROM
          rewriteNoAliasPropertyNodes(alias, whereNode);

          // do this before rewriting the extent properties, so we can access the aliases
          aliases.push(topLevelAliasMap);
          collectNormalizedExtentsAliases(whereNode,
                                          topLevelAliasMap,
                                          null,
                                          this::rewriteSubscriptsAsSubselect,
                                          this::rewriteSubscriptsAsSubselect);

          sb.append("\ndelete \n\t");
          generateFrom(fromNode, null);
          generateWhere(whereNode, null);

          if (aliases.pop() != topLevelAliasMap)
          {
             warn("Imbalance in FQL scopes.", stmt);
          }
       }

       /**
        * Rewrite {@link FQLParserTokenTypes#PROPERTY} ASTs so that they are parented by an <code>ALIAS</code>
        * AST.
        *
        * @param    alias
        *           The alias to add to all unqualified properties.
        * @param    where
        *           The <code>WHERE</code> AST.
        */
       private void rewriteNoAliasPropertyNodes(String alias, FQLAst where)
       {
          ArrayList<FQLAst> noAliasProps = new ArrayList<>();
          Iterator<Aast> iter = where.iterator();
          while (iter.hasNext())
          {
             FQLAst node = (FQLAst) iter.next();
             if (node.getType() == PROPERTY && node.getParent().getType() != ALIAS)
             {
                noAliasProps.add(node);
             }
          }

          for (int i = 0; i < noAliasProps.size(); i++)
          {
             FQLAst node = noAliasProps.get(i);
             FQLAst aliasAst = new FQLAst(new Token(ALIAS));
             aliasAst.setText(alias);
             int idx = node.getIndexPos();
             FQLAst parent = (FQLAst) node.getParent();
             parent.graftAt(aliasAst, idx);
             node.move(aliasAst, 0);
          }
       }

       /**
        * For DELETE and UPDATE statements which reference extent fields in the WHERE clause, these need to be
        * rewritten via subselects.  The subselect is inlined as a STRING AST, which will be emitted directly by
        * {@link #generateExpression}.
        *
        * @param    node
        *           The root WHERE node.
        * @param    subscripts
        *           The list of subscript properties which need to be rewritten.
        */
       private void rewriteSubscriptsAsSubselect(FQLAst node, List<FQLAst> subscripts)
       {
          for (FQLAst aliasAst : subscripts)
          {
             // find the first logical operator (AND/OR).
             FQLAst parent = aliasAst;
             while (!(parent == node || parent.getType() == AND || parent.getType() == OR))
             {
                parent = (FQLAst) parent.getParent();
             }

             // find the expression where the dynamic subscript is part of
             FQLAst child = (FQLAst) parent.getFirstChild();
             while (!child.isAncestorOf(0, aliasAst))
             {
                child = (FQLAst) child.getNextSibling();
             }

             int childIndex = child.getIndexPos();
             child.remove();

             // create a LPARENS root
             FQLAst lparens  = new FQLAst(new Token(LPARENS));
             lparens.setText("(");
             parent.graftAt(lparens, childIndex);

             generateExpression(child);
             String childTest = sb.toString();
             sb = new StringBuilder();

             String childAlias = aliasAst.getText();
             String childSqlTable = aliases.peek().get(childAlias).getSqlTableName();
             String subscript = (String) aliasAst.getAnnotation("subscript");
             String parentAlias = (String) aliasAst.getAnnotation("parentAlias");
             if (forceNoAlias)
             {
                parentAlias = "";
             }
             else
             {
                parentAlias = parentAlias + ".";
             }

             // add the '(select count(*) from ALIAS where ALIAS.parent__id = parent.PK and ALIAS.list__index = ?) > 0' condition
             String subselect = "(select count(*) from " + childSqlTable + " as " + childAlias;
             subselect += " where " + childAlias + ".parent__id = " + parentAlias + Session.PK;
             subselect += " and " + childAlias + ".list__index = " + subscript + " and " + childTest + ") > 0";

             FQLAst subselectAst = new FQLAst(new Token(STRING));
             subselectAst.setText(subselect);
             lparens.addChild(subselectAst);
          }
       }

       /**
        * Converts a {@code SELECT} FQL statement into the SQL language. The output is directly
        * appended to {@code sb}.
        *
        * @param   stmt
        *          The {@code SELECT} FQL statement to be converted.
        */
       private void processSelect(FQLAst stmt)
       {
          // collect the initial aliases and their DMO mappings from FROM and JOIN nodes
          HashMap<String, DmoMeta> topLevelAliasMap = new LinkedHashMap<>();
          FQLAst fromNode = (FQLAst) stmt.getImmediateChild(FROM, null);
          FQLAst whereNode = (FQLAst) stmt.getImmediateChild(WHERE, null);
          FQLAst orderNode = (FQLAst) stmt.getImmediateChild(ORDER, null);
          collectTableAliases(fromNode, topLevelAliasMap);
          Map<String, String> innerJoins = new HashMap<>();
          Map<String, String> extIdxConstraints = new HashMap<>();
          BiConsumer<FQLAst, List<FQLAst>> rewriteSelectStaticSubscripts =
             (FQLAst node, List<FQLAst> dynamicSubscripts) ->
             {
                for (FQLAst alias : dynamicSubscripts)
                {
                   extIdxConstraints.putIfAbsent(alias.getText(), (String) alias.getAnnotation("subscript"));
                }
             };

          int oldCrdSize = containsRewriteData.size();
          collectNormalizedExtentsAliases(whereNode,
                                          topLevelAliasMap,
                                          innerJoins,
                                          rewriteSelectStaticSubscripts,
                                          this::rewriteSelectDynamicSubscripts);
          if (orderNode != null)
          {
             collectNormalizedExtentsAliases(orderNode,
                                             topLevelAliasMap,
                                             innerJoins,
                                             rewriteSelectStaticSubscripts,
                                             this::rewriteSelectDynamicSubscripts);
          }
          int newCrdSize = containsRewriteData.size();
          optimizeContainsCTE = newCrdSize == 1 && orTerms == 0;
          boolean currentHasContains = oldCrdSize < newCrdSize;

          aliases.push(topLevelAliasMap);
          Set<String> tbls = new HashSet<>(topLevelAliasMap.keySet());
          topLevelAliasMap.keySet().stream().map(this::getSqlTableAliasName).forEach(tbls::add);

          if (currentHasContains)
          {
             containsRewriteData.stream().skip(oldCrdSize).
                   forEach(d -> {
                      generateProperty(d.fieldAst, false, true, d);
                      d.cteName = generateCteName("wcte", tbls);
                   });
          }

          List<FieldInfo> orderFields = new ArrayList<>();
          List<Aast> orderAsts = orderNode == null ? Collections.emptyList() :
                                                     getOrderByAsts(orderNode);
          boolean onlySpecial = true;
          for (Aast ast: orderAsts)
          {
             FieldInfo data = new FieldInfo();
             onlySpecial &= generateProperty((FQLAst)ast, false, true, data);
             orderFields.add(data);
          }
          if (onlySpecial && orderFields.size() > 0 && relation == null)
          {
             for (int i = 0; i < newCrdSize; i++)
             {
                ContainsRewriteData crwd = containsRewriteData.get(i);
                if (!crwd.alwaysFalse &&
                    crwd.tblAlias.equals(orderFields.get(0).tblAlias) &&
                    dialect.weightAggregator().isPresent())
                {
                   crwd.useCTE = true;
                   crwd.forSorting = true;
                   cte4order = crwd;
                   break;
                }
             }
          }

          if (cte4order == null || this.relation != null || !currentHasContains)
          {
             optimizeContainsCTE = false;
          }

          String sep = "";
          if (useWordTables && !dialect.useUdf4Contains() && dialect.useCTE4Contains())
          {
             String prefix = relation == null ? "with\n" : "with recursive\n";
             for (int i = oldCrdSize; i < newCrdSize; i++)
             {
                ContainsRewriteData crwd = containsRewriteData.get(i);
                if (crwd.alwaysFalse || !crwd.useCTE)
                {
                   optimizeContainsCTE = false;
                   continue;
                }
                sb.append(prefix);
                prefix = "";
                sb.append(sep);
                sep = crwd.containsWith(schema, dialect.weightAggregator(), weigths, sb) ? ",\n" : "";
             }
          }
          else if (cte4order != null && currentHasContains)
          {
             if (!cte4order.forSorting)
             {
                optimizeContainsCTE = false;
             }
             setFieldProperty(cte4order);
             sb.append("with\n");
             cte4order.containsWith4UDF(schema, dialect, weigths, sb);
             sep = ",\n";
          }

          String mcte = generateCteName("mcte", tbls);
          if (cte4order != null && currentHasContains)
          {
             sb.append(sep).append(mcte).append(" as (");
          }

          int selectStart = sb.length();
          sb.append("\nselect \n\t");
          FQLAst select = (FQLAst) stmt.getImmediateChild(SELECT, null);
          // the SELECT node may be missing in FQL in case of 'Polymorphic queries'

          if (select != null)
          {
             FQLAst child = (FQLAst) select.getFirstChild();
             int expressionCounter = 0;

             while (child != null)
             {
                FQLAst nextChild = null;
                int funcLvl = -1;

                StringBuilder pushSb = sb;
                sb = new StringBuilder();

                try
                {
                   // the first condition is caused by the lack of knowledge in the parser: it was not able to
                   // properly detect the ALIAS-es at that moment (see FQLParser.select_expr)
                   String childText = child.getText();
                   if ((child.getType() == PROPERTY && topLevelAliasMap.containsKey(childText)) ||
                       (child.getType() == ALIAS && child.getNumImmediateChildren() == 0))
                   {
                      DmoMeta scopedDmoInfo = getScopedDmoInfo(childText);
                      if (scopedDmoInfo != null)
                      {
                         if (!(scopedDmoInfo instanceof CompositeDmoInfo))
                         {
                            expandAlias(scopedDmoInfo, childText, expressionCounter == 0);
                            expressionCounter++;
                         }
                      }
                      else
                      {
                         warn("Invalid alias: ", child);
                      }
                   }
                   else
                   {
                      if (expressionCounter != 0)
                      {
                         sb.append(", ");
                      }
                      expressionCounter++;

                      if (child.getType() == FUNCTION)
                      {
                         funcLvl = 0;
                         nextChild = (FQLAst) child.getNextSibling();

                         while (child.getType() == FUNCTION)
                         {
                            sb.append(childText).append("(");
                            child = (FQLAst) child.getFirstChild();
                            childText = child.getText();

                            funcLvl = funcLvl + 1;
                         }
                      }

                      Map.Entry<String, Property>[] propertyBuffer = new Map.Entry[1];
                      generateProperty(child, false, true, null, propertyBuffer, funcLvl, currentHasContains);

                      //TODO test this code removal that doubles fields in sql query.
                      // for case-insensitive character fields we need to request the insensitive value, too
                      /* start removal
                      if (isCaseInsensitiveField(child))
                      {
                         sb.append(", ");

                         generateProperty(child, true, true);
                         if (generateUniqueSqlColumnNames)
                         {
                            sb.append(" as ");
                            appendColAlias.accept("col");
                         }
                         ++newFields;
                      }
                      end removal */
                      switch (child.getType())
                      {
                         case STRING:
                         case DEC_LITERAL:
                         case NUM_LITERAL:
                         case LONG_LITERAL:
                         case BOOL_TRUE:
                         case BOOL_FALSE:
                         case NULL:
                            // do nothing
                            break;

                         default:
                            // update the [rowStructure] with the number of columns added. If no record of this
                            // interface, create a new entry:
                            DmoMeta dmoMeta = getScopedDmoInfo(childText);
                            if (dmoMeta == null && childText.length() == 1 && childText.charAt(0) == ASTERISK)
                            {
                               // the aggregate functions use the default alias
                               dmoMeta = getScopedDmoInfo(NO_ALIAS);
                            }

                            if (dmoMeta != null)
                            {
                               // the assumption here is the DMO fields (for a certain alias) are contiguous.
                               if (rowStructure.isEmpty() ||
                                   rowStructure.get(rowStructure.size() - 1).getDmoClass() != dmoMeta.dmoImplInterface)
                               {
                                  rowStructure.add(new AdaptiveRowStructure(dmoMeta));
                               }
                               RowStructure oldStruct = rowStructure.get(rowStructure.size() - 1);
                               if (propertyBuffer[0] != null)
                               {
                                  Property prop = propertyBuffer[0].getValue();
                                  if (propertyBuffer[0].getKey().equals(Session.PK))
                                  {
                                     oldStruct.addRecid(prop);
                                  }
                                  else if (propertyBuffer[0].getKey().equals(ReservedProperty._MULTIPLEX.name))
                                  {
                                     oldStruct.addMultiplex(prop);
                                  }
                                  else
                                  {
                                     oldStruct.addProperty(prop);
                                     if (prop.extent != 0 && !prop.denormalized)
                                     {
                                        // the RowStructure was notified that this is a normalized field, and it
                                        // will take care of retrieving the extent values at the right time.
                                        // Removing now the field from SELECT list since its column is not in the
                                        // primary SQL table
                                        nextChild = (FQLAst) child.getNextSibling();
                                        child.remove();
                                        //
                                        expressionCounter--;
                                        sb = new StringBuilder(); // fast clear
                                     }
                                  }
                               }
                               else
                               {
                                  warn("Failed to append DMO property for " + childText + " ALIAS", child);
                               }
                            }
                            else
                            {
                               warn("Failed to obtain DMO data for " + childText + " ALIAS", child);
                            }
                      }
                   }
                }
                finally
                {
                   pushSb.append(sb.toString());
                   sb = pushSb;

                   if (nextChild != null)
                   {
                      child = nextChild;
                   }
                   else
                   {
                      child = (FQLAst) child.getNextSibling();
                   }
                }
             }
          }
          else
          {
             // we generate a default SQL query
             Set<Map.Entry<String, DmoMeta>> topLevelAliases = topLevelAliasMap.entrySet();
             boolean first = true;
             for (Map.Entry<String, DmoMeta> entry : topLevelAliases)
             {
                DmoMeta dmoMeta = entry.getValue();
                if (dmoMeta instanceof CompositeDmoInfo)
                {
                   continue;
                }
                String alias = entry.getKey();
                expandAlias(dmoMeta, alias, first);
                first = false;
             }
          }

          String mcteRecid = "col_recid_";
          if (cte4order != null && currentHasContains)
          {
             sb.append(", ").append(cte4order.tblAlias).append(".").append(PK).append(" as ").append(mcteRecid);
          }
          sb.append(" ");

          int fromStart = this.sb.length();
          String masterTable = generateFrom(fromNode, innerJoins);
          int fromEnd = this.sb.length();
          generateWhere(whereNode, extIdxConstraints);
          if (this.relation != null) {
             rewrite4SecondPass(this.getSqlTableAliasName(masterTable),
                      generateCteName("r", tbls), generateCteName("base", tbls),
                      selectStart, fromStart, fromEnd);
          }
          if (cte4order != null && this.relation == null && currentHasContains)
          {
             sb.append("\n)");
             sb.append("\nselect ").append(
                   rowAliases.stream().map(alias -> mcte + "." + alias).collect(Collectors.joining(", "))
             ).
             append("\nfrom ").append(mcte).
             append(optimizeContainsCTE ? "\n\tjoin " : "\n\tleft join ").
                append(cte4order.cteName).append(" on (").
                append(mcte).append(".").append(mcteRecid).
                append(" = ").
                append(cte4order.cteName).append(".").append(PK).
                append(")").
             append("\norder by ").append(weigths).append(mcte).append(".").append(mcteRecid);
          }
          else if (this.relation == null)
          {
             generateOrderBy((FQLAst) stmt.getImmediateChild(ORDER, null));
          }
          generateSingle((FQLAst) stmt.getImmediateChild(SINGLE, null));
          if (stmt.getType() == SELECT_STMT)
          {
             if (maxResults > 0 || startOffset > 0)
             {
                generatePaging();
             }

             if (lazyMode)
             {
                generateLazyMode();
             }
          }

          if (aliases.pop() != topLevelAliasMap)
          {
             warn("Imbalance in FQL scopes.", stmt);
          }
       }

       /**
        * Generate second pass query for recursive DATA-RELATION FILL support.
        *
        * @param   masterAlias
        *          Alias for the master source table
        * @param   rCteName
        *          Recursive CTE name
        * @param   baseCteName
        *          Base CTE name
        * @param   selectStart
        *          Base SELECT start position
        * @param   fromStart
        *          Base FROM start position
        * @param   fromEnd
        *          Base FROM end position
        */
       private void rewrite4SecondPass(String masterAlias,
                                       String rCteName,
                                       String baseCteName,
                                       int selectStart,
                                       int fromStart,
                                       int fromEnd)
       {
          String[] relFields = this.relation.getRelationFields(false);
          String[] parentRelFields = this.relation.getRelationFields(true);

          List<String> join = new ArrayList<>(relFields.length);
          for (int nf = 0; nf < relFields.length; ++nf)
          {
              String c = masterAlias + "." + selectFields.get(relFields[nf]) + " = " +
                         rCteName + "." + selectAliases.get(parentRelFields[nf]);
              join.add(c);
          }

          String pka = this.selectAliases.get(FqlToSqlConverter.PK);
          StringBuilder sql = new StringBuilder(selectStart == 0 ? "with recursive " : ",\n").
             append(baseCteName).append(" as (\n").
                append(sb, selectStart, sb.length()).
             append("),\n").
             append(rCteName).append("(").
             append(String.join(", ", this.selectAliases.values())).
             append(") as (\n").
                append("select * from ").
                append(baseCteName).
                append("\n").
                append("union all\n").
                append(sb.substring(selectStart, fromEnd)).
                append("\n").
                append("join ").
                append(rCteName).
                append(" on (").append(String.join(" and ", join)).append(")\n").
             append(")\n").
             append("select * from ").
             append(rCteName).append("\n").
             append("where ").append(pka).append(" not in (").
             append("select ").append(pka).append(" from ").append(baseCteName).append(")\n")
             ;
          sb.setLength(selectStart);
          sb.append(sql);
       }

       /**
        * Generate the alias for the common table expression for CONTAINS re-writing.
        *
        * @param   base
        *          The base of the name
        * @param   tbls
        *          Set of already used table names/aliases.
        *
        * @return  The generated alias.
        */
       private String generateCteName(String base, Set<String> tbls)
       {
          String cte;
          do
          {
             cte = base + (++cteNo);
          }
          while(!tbls.add(cte));
          return cte;
       }

       /**
        * Check if we can simply use an {@code alias.*} syntax instead of listing all SQL fields.
        *
        * @param    dmoMeta
        *           The meta of the table to be considered for wildcard field selection.
        *
        * @return   {@code true} if we can emit an wildcard syntax for field selection.
        */
       private boolean canUseWildcard(DmoMeta dmoMeta)
       {
          return cte4order == null && !dmoMeta.hasComputedColumns(false);
       }

       /**
        * Expands the alias into all its properties in a SELECT clause.
        *
        * @param   dmoMeta
        *          The node meta-data for the node to be expanded.
        * @param   alias
        *          The node alias (text of the node) to be expanded.
        * @param   first
        *          {@code true} if this is the first expanded node.
        */
       private void expandAlias(DmoMeta dmoMeta, String alias, boolean first)
       {
          String sqlTableAliasName = getSqlTableAliasName(alias);
          if (dialect.allowSelectWildcard() &&
              canUseWildcard(dmoMeta)       &&
              !NO_ALIAS.equals(sqlTableAliasName))
          {
             if (!first)
             {
                sb.append(", ");
             }
             sb.append(sqlTableAliasName).append(".*");

             if (rowStructure != null)
             {
                rowStructure.add(new FullRowStructure(dmoMeta));
             }

             return;
          }

          Set<Map.Entry<String, Property>> fieldEntries = dmoMeta.propsByName.entrySet();
          for (Map.Entry<String, Property> field : fieldEntries)
          {
             Property property = field.getValue();
             if (property.extent > 0 && property.index == 0)
             {
                // skip the extent normalized fields: they are found in the
                //       [dmoMeta].[table].[name] + "__" + [property.extent()]
                // these properties are located in secondary tables and will be requested with other queries
                continue;
             }

             if (first)
             {
                first = false;
             }
             else
             {
                sb.append(", ");
             }

             if (!NO_ALIAS.equals(sqlTableAliasName))
             {
                sb.append(sqlTableAliasName).append(".");
             }
             sb.append(property.column);
             if (generateUniqueSqlColumnNames)
             {
                String sqlColumnAlias = getSqlColumnAlias(property);
                sb.append(" as ").append(sqlColumnAlias);
                if (cte4order != null)
                {
                   rowAliases.add(sqlColumnAlias);
                }
                String legacy = (property.id < 0) ? property.column : property.legacy;
                collectAliases.accept(legacy, sqlColumnAlias);
                collectFields.accept(legacy, property.column);
             }

             // TODO: DO we?, if so, in which case? Need an additional parameter?
             if (false && dialect.needsComputedColumns())
             {
                // for case-insensitive character fields we need to request the insensitive value, too
                boolean caseSensitive = property.caseSensitive;
                if (!caseSensitive && property._isCharacter)
                {
                   sb.append(", ");
                   sb.append(sqlTableAliasName).append(".")
                     .append(dialect.getComputedColumnPrefix(caseSensitive))
                     .append(property.column);

                   if (generateUniqueSqlColumnNames)
                   {
                      sb.append(" as ");
                      appendColAlias("column");
                   }
                }
             }
             if (property._isDatetimeTz)
             {
                sb.append(", ");
                sb.append(sqlTableAliasName).append(".")
                  .append(property.column).append(DDLGeneratorWorker.DTZ_OFFSET);

                if (generateUniqueSqlColumnNames)
                {
                   // Covers a case during a FILL operation when there is a DataRelation.
                   // The problem is that the number of columns will not match when building
                   // the second pass query for recursive DATA-RELATION FILL because the
                   // DATETIME-TZ is split into 2 columns in the database and the Recursive
                   // CTE name is only specifying the selectAliases collection which will not
                   // contain this additional TZ alias as it was not collected.
                   sb.append(" as ");

                   String dtzColAlias = appendColAlias("column", true);
                   String legacy = property.legacy + DDLGeneratorWorker.DTZ_OFFSET;
                   collectAliases.accept(legacy, dtzColAlias);
                   collectFields.accept(legacy, property.column);
                }
             }
          }
          if (rowStructure != null)
          {
             rowStructure.add(new FullRowStructure(dmoMeta));
          }
       }

       /**
        * Analyzes the {@link #maxResults} and {@link #startOffset} values and constructs the paging clauses.
        * <p>
        * Note: MSSQL dialect ({@code SELECT TOP n}) not supported at this time.
        */
       private void generatePaging()
       {
          sb.append("\n");
          if (maxResults > 0)
          {
             sb.append(" limit ").append(argRef.get());
             ++paramCount; // add [maxResults] at the end of parameter list
          }

          if (startOffset > 0)
          {
             sb.append(" offset ").append(argRef.get());
             ++paramCount; // add [startOffset] at the end of parameter list
          }
       }

       /**
        * Add the keyword "lazy" to the end of the SQL. This works only when there is a
        * {@code Select} statement that is intended to be executed in a lazy manner.
        */
       private void generateLazyMode()
       {
          sb.append("\nlazy");
       }

       /**
        * Generates the {@code ORDER BY} part of the query. The output is directly appended to {@link #sb}.
        *
        * @param   order
        *          The FQL {@code ORDER BY} node.
        */
       private void generateOrderBy(FQLAst order)
       {
          if (order == null)
          {
             return;
          }

          sb.append("\norder by\n\t");

          boolean first = true;
          int cnt = order.getNumImmediateChildren();
          for (int k = 0; k < cnt; k++)
          {
             if (first)
             {
                first = false;
             }
             else
             {
                sb.append(", ");
             }

             // temporarily save the [sb] to [ssb] so that the next calls will create in the sort criterion in [sb]
             StringBuilder ssb = sb;
             sb = new StringBuilder();
             sbProp = dialect.injectComputedColumns() ? new StringBuilder() : null;
             // if not null, [sbProp] will get the property name, without being wrapped in functions

             boolean reserved = generateOrderByComponent((FQLAst) order.getChildAt(k));

             boolean asc = true;
             if (k < cnt - 1)
             {
                Aast dir = order.getChildAt(k + 1);
                if (dir.getType() == DESC)
                {
                   asc = false;
                   k++;
                }
                else if (dir.getType() == ASC)
                {
                   k++; // just consume the token
                }
                // otherwise assume ASC by default
             }

             // now we have the sort criterion in [sb], extract the expression and restore the original [sb]
             String sortCrit = sb.toString();
             sb = ssb;

             if (reserved)
             {
                sb.append(sortCrit).append(asc ? " asc" : " desc");
             }
             else
             {
                dialect.orderByNulls(sb, sortCrit, sbProp == null ? null : sbProp.toString(), asc, true);
             }
          }
       }

       /**
        * Generates am order-by component. Usually this is equivalent of converting a normal property,
        * but for some dialects (PostgreSQL), some SQL functions are allowed to be used. This method
        * will be called recursively in order to support nested function calls.
        * <p>
        * Note: only a one-argument functions are supported (like {@code upper} and {@code rtrim} used
        *       for character fields). If more complex expressions are needed the more generic
        *       {@link #generateExpression} should be used instead.
        *
        * @param   node
        *          The root node for this expression.
        *
        * @return  <code>true</code> if this is the '_multiplex' or PK column.
        */
       private boolean generateOrderByComponent(FQLAst node)
       {
          switch (node.getType())
          {
             case FUNCTION:
                sb.append(node.getText());
                sb.append("(");
                generateOrderByComponent((FQLAst) node.getFirstChild());
                sb.append(")");
                return false;
             case ALIAS:
                return generateProperty(node, false, true);
             case PROPERTY:
                return generateProperty(node, false, false);
             default:
                warn("Unknown token " + node.getSymbolicTokenType() + " in ORDER BY clause", node);
                return false;
          }
       }

       /**
        * Generate a list of order by fields ASTs. The resulting list of nodes are the 4GL fields
        * used in original query, not the expressions to be used in the SQL to be constructed.
        * These two lists are different.
        *
        * @param   orderNode
        *          The root node for the order by expression.
        *
        * @return  a list of order by fields ASTs.
        *
        */
       private List<Aast> getOrderByAsts(FQLAst orderNode)
       {
          List<Aast> orderAsts = new ArrayList<>();
          int cnt = orderNode.getNumImmediateChildren();
          for (int k = 0; k < cnt; k++)
          {
             generateOrderByField((FQLAst) orderNode.getChildAt(k), orderAsts);
          }
          return orderAsts;
       }

       /**
        * Append an order by field AST to {@code orderAsts}.
        *
        * @param   node
        *          The root node for this expression.
        * @param   orderAsts
        *          list of order by fields ASTs.
        */
       private void generateOrderByField(FQLAst node, List<Aast> orderAsts)
       {
          switch (node.getType())
          {
             case FUNCTION:
                generateOrderByField((FQLAst) node.getFirstChild(), orderAsts);
                break;
             case ALIAS:
                orderAsts.add(node);
                break;
          }
       }

       /**
        * Convert the SINGLE FQL token to a dialect-based <code>LIMIT 1</code> expression.
        *
        * @param    single
        *           The SINGLE AST.  May be <code>null</code>.
        */
       private void generateSingle(FQLAst single)
       {
          if (single == null)
          {
             return;
          }

          sb.append(dialect.getLimitString("", 0, 1));
       }

       /**
        * Checks whether this is case-insensitive character field.
        *
        * @param   alias
        *          The {@code alias} node. This represents the table this property belongs to.
        *          Normally it should have a single child, with the name of the {@code PROPERTY}.
        *
        * @return  {@code true} if this property is detected as a case-insensitive character field.
        */
       private boolean isCaseInsensitiveField(FQLAst alias)
       {
          String aliasStr = alias.getText();

          if (aliasStr.length() == 1 && aliasStr.charAt(0) == ASTERISK)
          {
             return false;
          }
          switch (alias.getType())
          {
             case STRING:
             case DEC_LITERAL:
             case NUM_LITERAL:
             case LONG_LITERAL:
             case BOOL_TRUE:
             case BOOL_FALSE:
             case NULL:
                return false;
          }

          DmoMeta dmoInfo = getScopedDmoInfo(aliasStr);
          if (dmoInfo == null)
          {
             warn("Invalid alias: ", alias);
             return false;
          }

          FQLAst property = (FQLAst) alias.getFirstChild();
          if (property == null)
          {
             warn("Unexpected null property: ", property);
             return false;
          }

          String propertyStr = property.getText();
          Property propertyAnn = dmoInfo.propsByName.get(propertyStr);
          if (propertyAnn == null)
          {
             warn("Invalid property: ", property);
             return false;
          }

          if (propertyAnn.caseSensitive)
          {
             return false;
          }

          return propertyAnn._isCharacter;
       }

       /**
        * Converts a FQL property to a SQL field name. The output is directly appended to {@link #sb}.
        *
        * @param   node
        *          The {@code alias} or {@code property} node. If the former, this represents the table this
        *          property belongs to. Normally it should have a single child, with the name of the {@code
        *          PROPERTY}. If the latter, this represents an unqualified property name (and a single-table
        *          statement is assumed).
        * @param   forceCsPrefix
        *          If {@code true}, the dialect specific case-sensitive prefix will be injected.
        * @param   qualified
        *          {@code true} if property is qualified by an alias; {@code false} if {@code node}
        *          represents an unqualified property name.
        *
        * @return  <code>true</code> if this is the '_multiplex' or PK column.
        */
       private boolean generateProperty(FQLAst node, boolean forceCsPrefix, boolean qualified)
       {
          return generateProperty(node, forceCsPrefix, qualified, null);
       }

       /**
        * Converts a FQL property to a SQL field name. The output is directly appended to {@link #sb}.
        *
        * @param   node
        *          The {@code alias} or {@code property} node. If the former, this represents the table this
        *          property belongs to. Normally it should have a single child, with the name of the {@code
        *          PROPERTY}. If the latter, this represents an unqualified property name (and a single-table
        *          statement is assumed).
        * @param   forceCsPrefix
        *          If {@code true}, the dialect specific case-sensitive prefix will be injected.
        * @param   qualified
        *          {@code true} if property is qualified by an alias; {@code false} if {@code node}
        *          represents an unqualified property name.
        * @param   fieldInfo
        *          Auxiliary structure holding table names and alias
        *
        * @return  <code>true</code> if this is the '_multiplex' or PK column.
        */
       private boolean generateProperty(FQLAst node,
                                        boolean forceCsPrefix,
                                        boolean qualified,
                                        FieldInfo fieldInfo)
       {
          return generateProperty(node, forceCsPrefix, qualified, fieldInfo, null, null, false);
       }

       /**
        * Converts a FQL property to a SQL field name. The output is directly appended to {@link #sb}.
        *
        * @param   node
        *          The {@code alias} or {@code property} node. If the former, this represents the table this
        *          property belongs to. Normally it should have a single child, with the name of the {@code
        *          PROPERTY}. If the latter, this represents an unqualified property name (and a single-table
        *          statement is assumed).
        * @param   forceCsPrefix
        *          If {@code true}, the dialect specific case-sensitive prefix will be injected.
        * @param   qualified
        *          {@code true} if property is qualified by an alias; {@code false} if {@code node}
        *          represents an unqualified property name.
        * @param   fieldInfo
        *          Auxiliary structure holding table names and alias
        * @param   propertyBuffer
        *          Collects an unqualified property name
        * @param   funcLvl
        *          Has two roles:
        *          <ol>
        *             <li>
        *                Keeps track of the function level for cases when {@code type == FUNCTION}
        *             </li>
        *             <li>
        *                Works as a flag that indicates if the column unique name(s) should be generated
        *                as part of the generated properties.
        *             </li>
        *          </ol>
        * @param   currentHasContains
        *          Flag indicating a statement with CONTAINS.
        *
        * @return  <code>true</code> if this is the '_multiplex' or PK column.
        */
       private boolean generateProperty(FQLAst node,
                                        boolean forceCsPrefix,
                                        boolean qualified,
                                        FieldInfo fieldInfo,
                                        Map.Entry<String, Property>[] propertyBuffer,
                                        Integer funcLvl,
                                        boolean currentHasContains)
       {
          switch (node.getType())
          {
             case MULTIPLY: // ASTERISK ?
                sb.append(ASTERISK);
                return false;

             case STRING:
             case DEC_LITERAL:
             case NUM_LITERAL:
             case LONG_LITERAL:
             case NULL:
                sb.append(node.getText());
                return false;

             case BOOL_TRUE:
             case BOOL_FALSE:
                sb.append(dialect.supportsBooleanDatatype() ? node.getText() :
                                node.getType() == BOOL_TRUE ? "1=1" : "0=1");
                return false;
          }

          String aliasStr = qualified ? node.getText() : NO_ALIAS;
          boolean withAlias = !this.forceNoAlias && !aliasStr.isEmpty();
          String aliasName = withAlias ? getSqlTableAliasName(aliasStr) : null;
          if (withAlias)
          {
             if (fieldInfo != null)
             {
                DmoMeta dmoMeta = aliases.peek().get(aliasName);
                if (dmoMeta != null)
                {
                   fieldInfo.tblMetaKey = aliasName;
                }
                else // if (dmoMeta == null) -- evidently
                {
                   dmoMeta = aliases.peek().get(aliasStr);
                   if (dmoMeta != null)
                   {
                      fieldInfo.tblMetaKey = aliasStr;
                   }
                }
                fieldInfo.tblName = dmoMeta != null ? dmoMeta.getSqlTableName() : aliasStr;
                fieldInfo.tblAlias = aliasName;
             }
             else
             {
                sb.append(aliasName);
                if (sbProp != null)
                {
                   sbProp.append(aliasName);
                }
             }
          }

          boolean reserved = false;
          FQLAst property = qualified ? (FQLAst) node.getFirstChild() : node;
          if (property != null)
          {
             boolean prefixed = false;
             String propertyStr = property.getText();
             DmoMeta dmoInfo = getScopedDmoInfo(aliasStr);
             if (dmoInfo == null)
             {
                warn("Invalid alias: ", node);
                return false;
             }

             Property propertyAnn = dmoInfo.propsByName.get(propertyStr);
             if (propertyAnn == null && fieldInfo != null)
             {
                List<Property> customExtent =  dmoInfo.customExtents.get(propertyStr);
                if (customExtent != null)
                {
                   fieldInfo.fldName = customExtent.get(0).column;
                   fieldInfo.extent = customExtent.size();
                   return false;
                }
             }

             if (propertyAnn == null && dialect.needsComputedColumns())
             {
                // analyze the propertyStr to see whether it is a ComputedColumn (used by case insensitive
                // character fields)
                String csPrefix = dialect.getComputedColumnPrefix(true);
                if (csPrefix != null && propertyStr.startsWith(csPrefix))
                {
                   propertyAnn = dmoInfo.propsByName.get(propertyStr.substring(csPrefix.length()));
                   prefixed = true;
                }
                else
                {
                   String ciPrefix = dialect.getComputedColumnPrefix(false);
                   if (ciPrefix != null && propertyStr.startsWith(ciPrefix))
                   {
                      propertyAnn = dmoInfo.propsByName.get(propertyStr.substring(ciPrefix.length()));
                      prefixed = true;
                   }
                }
             }

             if (propertyAnn == null)
             {
                // propertyAnn is [null] for [id], [_multiplex], [_rowState], etc
                if (withAlias)
                {
                   sb.append(".");
                   if (sbProp != null)
                   {
                      sbProp.append(".");
                   }
                }
                sb.append(propertyStr);
                if (sbProp != null)
                {
                   sbProp.append(propertyStr);
                }
                // we do not have information on this property. Is this a surrogate?
                if (propertyBuffer != null)
                {
                   propertyBuffer[0] = new AbstractMap.SimpleEntry<>(propertyStr, null);
                }
                warn("Property '" + propertyStr +
                     "' not found to extract column name. Assuming it is a column name already.", property);
             }
             else
             {
                reserved = (propertyAnn instanceof ReservedProperty) &&
                           (Session.PK.equals(propertyStr) ||
                            ReservedProperty._MULTIPLEX.name.equals(propertyStr));
                String prefix = (prefixed || forceCsPrefix) ?
                      dialect.getComputedColumnPrefix(propertyAnn.caseSensitive) : "";

                if (fieldInfo != null)
                {
                   fieldInfo.extent = propertyAnn.extent;
                   fieldInfo.fldName = (propertyAnn.extent > 0)?
                            propertyAnn.column : prefix + propertyAnn.column;
                }
                else
                {
                   if (withAlias)
                   {
                      sb.append(".");
                   }
                   if (sbProp != null)
                   {
                      sbProp.append(".");
                   }
                   if (prefix != null && !prefix.isEmpty())
                   {
                      sb.append(prefix); // do not add the prefix if the dialect does not need it
                      if (sbProp != null)
                      {
                         sbProp.append(prefix);
                      }
                   }
                   sb.append(propertyAnn.column);
                   if (sbProp != null)
                   {
                      sbProp.append(propertyAnn.column);
                   }
                   if (propertyBuffer != null)
                   {
                      propertyBuffer[0] = new AbstractMap.SimpleEntry<>(propertyAnn.name, propertyAnn);
                   }
                }
             }
             // indicates if the unique names should be generated
             // funcLvl != null only if this method is called from processSelect()
             if (funcLvl != null)
             {
                while (funcLvl > 0)
                {
                   sb.append(")");
                   funcLvl = funcLvl - 1;
                }
                if (generateUniqueSqlColumnNames)
                {
                   sb.append(" as ");
                   appendColAlias("col", currentHasContains);
                }
                if (propertyAnn != null && propertyAnn._isDatetimeTz)
                {
                   // for datetime-tz fields, the offset needs to be retrieved as well
                   sb.append(", ");
                   if (withAlias)
                   {
                      sb.append(aliasName);
                   }
                   sb.append(".").append(propertyAnn.column).append(DDLGeneratorWorker.DTZ_OFFSET);

                   if (generateUniqueSqlColumnNames)
                   {
                      // Covers a case during a FILL operation when there is a DataRelation.
                      // The problem is that the number of columns will not match when building
                      // the second pass query for recursive DATA-RELATION FILL because the
                      // DATETIME-TZ is split into 2 columns in the database and the Recursive
                      // CTE name is only specifying the selectAliases collection which will not
                      // contain this additional TZ alias as it was not collected.
                      sb.append(" as ");

                      String dtzColAlias = appendColAlias("col", currentHasContains);
                      String legacy = propertyAnn.legacy + DDLGeneratorWorker.DTZ_OFFSET;
                      collectAliases.accept(legacy, dtzColAlias);
                      collectFields.accept(legacy, propertyAnn.column);
                   }
                }
             }
          }
          else
          {
             warn("Invalid property: ", node);
          }

          return reserved;
       }

       /**
        * Search for a specific alias in the stack of scoped aliases declared by current statement.
        *
        * @param   aliasStr
        *          The name of the alias to be found.
        *
        * @return  The {@code DmoMeta} structure for the specified alias.
        */
       private DmoMeta getScopedDmoInfo(String aliasStr)
       {
          if (NO_ALIAS.equals(aliasStr))
          {
             HashMap<String, DmoMeta> innerScope = aliases.peek();
             if (innerScope != null && innerScope.size() == 1)
             {
                return innerScope.entrySet().iterator().next().getValue();
             }

             warn("Failed to identify the default DMO (NO-ALIAS) in local scope (" + aliases.size() +
                  "). The generated SQL might not be valid.", null);
             return null;
          }

          for (HashMap<String, DmoMeta> aliasMap : aliases)
          {
             DmoMeta dmoInfo = aliasMap.get(aliasStr);
             if (dmoInfo != null)
             {
                return dmoInfo;
             }
          }
          return null;
       }

       /**
        * Generated the {@code WHERE} predicate of the SQL query. Walks each FQL node and converts
        * it into a SQL representation which is appended to the {@link #sb}.
        *
        * @param   where
        *          The {@code WHERE} node of the FQL query.
        * @param   extIdxConstraints
        *          The set of extra constraints required by extent indexes. For the specified aliases,
        *          a new constraint for the {@code list__index} is injected.
        */
       private void generateWhere(FQLAst where, Map<String, String> extIdxConstraints)
       {
          boolean extraConstraints = extIdxConstraints != null && !extIdxConstraints.isEmpty();

          boolean hasWhere = where != null && where.getFirstChild() != null;

          if (!hasWhere && !extraConstraints)
          {
             return;
          }

          if (!optimizeContainsCTE || andTerms > 0 || extraConstraints)
          {
             sb.append("\nwhere\n\t");
          }

          if (extraConstraints)
          {
             sb.append("(");
             boolean first = true;
             for (Map.Entry<String, String> entry : extIdxConstraints.entrySet())
             {
                if (first)
                {
                   first = false;
                }
                else
                {
                   sb.append(" and ");
                }
                sb.append(entry.getKey()).append(".list__index=").append(entry.getValue());
             }

             if (hasWhere)
             {
                sb.append(") and \n\t(");
             }
          }

          if (hasWhere)
          {
             generateExpression((FQLAst) where.getFirstChild());
          }

          if (extraConstraints)
          {
             sb.append(")");
          }
       }

       /**
        * Recursively walks a FQL predicate and generates the SQL one. The output is directly
        * appended to {@link #sb}.
        *
        * @param   node
        *          The root node for a FQL predicate to be processed.
        */
       private void generateExpression(FQLAst node)
       {
          nestedInContains = 0;
          final boolean[] ffd = { false }; // FAST FORWARD mode flag
          AstWalkListener awl = new AstWalkListener()
          {
             /**
              * Called when the AST walk ascends between levels of the tree. Used to close off certain
              * constructs when emitting HQL.
              *
              * @param   ast
              *          AST node to which the walk is ascending.
              */
             public void ascent(Aast ast)
             {
                if (ffd[0] || ast == null)
                {
                   return;
                }

                if ((((FQLAst) ast).state & FQLAst.ASCENDED) != 0)
                {
                   return; //already processed
                }

                switch (ast.getType())
                {
                   case IN:
                   case LPARENS:
                   case CAST:
                      sb.append(")");
                      break;
                   case FUNCTION:
                      if (nestedInContains > 0 )
                      {
                         nestedInContains--;
                         if (nestedInContains == 0)
                         {
                            ContainsRewriteData crwd = containsRewriteData.get(currentContains);
                            queryWasRewritten = true;
                            if (useWordTables && !dialect.useUdf4Contains())
                            {
                               if (dialect.useCTE4Contains())
                               {
                                  if (crwd.useCTE)
                                  {
                                     containsWithWordTableAndCTE(crwd);
                                  }
                                  else
                                  {
                                     containsWithWordTable(crwd);
                                  }
                               }
                               else
                               {
                                  containsWithWordTable(crwd);
                               }
                            }
                            else
                            {
                               if (crwd.forSorting )
                               {
                                  containsWithUdfAndCTE(crwd);
                               }
                               else
                               {
                                  containsWithUdf(crwd);
                               }
                            }
                         }
                      }
                      else
                      {
                         sb.append(")");
                      }
                      break;
                   case TERNARY:
                      sb.append(") end");
                      break;

                   case LBRACKET:
                      sb.append("]");
                      break;

                   case IS_NULL:
                      sb.append(" is null");
                      break;

                   case NOT_NULL:
                      sb.append(" is not null");
                      break;

                   case SELECT:
                   case FROM:
                   case JOIN:
                      sb.append(" ");
                      break;

                   default:
                      break;
                }

                ((FQLAst) ast).state |= FQLAst.ASCENDED;
             }

             /**
              * Called when the AST walk descends between levels of the tree. Initially used to emit a
              * dot operator after an alias, in order to dereference a property, it also adds a space
              * to separate the text from certain nodes from the text of the first child.
              *
              * @param   ast
              *          AST node from which the walk is descending.
              */
             public void descent(Aast ast)
             {
                if (ffd[0] || ast == null)
                {
                   return;
                }

                if ((((FQLAst) ast).state & FQLAst.DESCENDED) != 0)
                {
                   return; //already processed
                }

                switch (ast.getType())
                {
                   case SELECT:
                   case FROM:
                   case JOIN:
                   case WHERE:
                   case ESCAPE:
                   case NOT:
                      sb.append(" ");
                      break;

                   default:
                      break;
                }

                ((FQLAst) ast).state |= FQLAst.DESCENDED;
             }

             /**
              * Called when the AST walk moves laterally between sibling nodes. Used to emit the
              * symbol of a binary operator between operands of a binary operation, or to emit a comma
              * or other separators between function parameters or complete the operator's structure.
              *
              * @param   ast
              *          AST parent node of the siblings between which the walk
              *          is moving laterally.
              * @param   index
              *          Zero-based index of the sibling node about to be visited.
              */
             public void nextChild(Aast ast, int index)
             {
                if (ffd[0] || ast == null)
                {
                   return;
                }

                if (isBinaryOperator(ast))
                {
                   sb.append(" ");
                   if (index == 1 || ast.getType() != LIKE)
                   {
                      Aast func = (optimizeContainsCTE && ast.getType() == AND) ?
                                  ast.getImmediateChild(FUNCTION, null) : null;
                      if (func != null && dialect.isUdfContains(func.getText()))
                      {
                         // "and" (ALIAS.recid in (select recid from wcte1)) is being skipped. We will
                         // later replace the left join with a simple join which will work the same.
                         return;
                      }
                      // LIKE is not a truly binary operator like +, -, || to use chaining
                      // in fact the other comparing operators are not, as well
                      sb.append(ast.getText());
                      sb.append(" ");
                   }
                   if (index == 1 && ast.getType() == IN)
                   {
                      sb.append("("); // start the list
                   }
                }
                else if (ast.getType() == IN)
                {
                   if (index == 1)
                   {
                      sb.append(" in ("); // start the list
                   }
                   else
                   {
                      sb.append(", "); // separate the list elements
                   }
                }
                else
                {
                   switch (ast.getType())
                   {
                      case FUNCTION:
                         if (nestedInContains == 0)
                         {
                            sb.append(", ");
                         }
                         break;

                      case CAST:
                         sb.append(" as ");
                         break;

                      case TERNARY:
                         switch (index)
                         {
                            case 1:
                               sb.append(" then (");
                               break;
                            case 2:
                               sb.append(") else (");
                               break;
                            default:
                               break;
                         }
                         break;

                      default:
                         break;
                   }
                }
             }
          };

          // walk the full tree and emit the expression into the string buffer
          Iterator<Aast> iter = node.iterator(0, awl);
          FQLAst next = null;
          // iter.hasNext() is notifying listeners if there are no more elements, and this must not be done when
          // we re-use the next AST, computed after processing a SUBSELECT (as the listeners were already notified)
          while (iter.hasNext())
          {
             next = (FQLAst) iter.next();
             int ttype = next.getType();

             if (!isBinaryOperator(next))
             {
                String text = next.getText();
                long card;
                switch (ttype)
                {
                   case POSITIONAL:
                     nargs++;
                     if (nestedInContains == 1)
                     {
                        break;
                     }
                     // fall through if not in CONTAINS
                   case SUBST:
                      sb.append(argRef.get());
                      card = (long) next.getAnnotation("paramCard");
                      if (card != 1)
                      {
                         for (int k = 1; k < card; k++)
                         {
                            sb.append(",").append(argRef.get());
                         }
                      }
                      break;

                   case CAST:
                      sb.append(text);
                      sb.append("(");
                      if (next.getNumImmediateChildren() == 0)
                      {
                         sb.append(")");
                      }
                      break;

                   case FUNCTION:
                      if (dialect.isUdfContains(text) || (nestedInContains > 0))
                      {
                         if (nestedInContains== 0)
                         {
                            currentContains++;
                         }
                         // TODO: do not add RTRIM for the CONTAINS field in the HQLPreprocessor
                         nestedInContains++;
                      }
                      else
                      {
                         if (text.equals(dialect.checkErrorFn()) || text.endsWith(_checkErrorFn))
                         {
                            containsErrorHandling = true;
                         }
                         sb.append(text);
                         sb.append("(");
                         if (next.getNumImmediateChildren() == 0)
                         {
                            sb.append(")");
                         }
                      }
                      break;

                   case TERNARY:
                      sb.append("case when ");
                      break;

                   case SUBSELECT:
                      ArrayList<RowStructure> rowStructureBackup = rowStructure;
                      rowStructure = new ArrayList<>(1);

                      FQLAst[] lastProcessedAst = new FQLAst[1];
                      processStatement(next, lastProcessedAst);

                      try
                      {
                         ffd[0] = true; // FF mode: consume this sub-select and don't emit anything

                         while (iter.hasNext() && lastProcessedAst[0] != iter.next())
                            ;
                      }
                      finally
                      {
                         ffd[0] = false;
                         rowStructure = rowStructureBackup;
                      }

                      break;

                   case IS_NULL:
                   case NOT_NULL:
                      break;

                   case IN:
                      break; // [in] is emitted in infixed form, similar to binary operators

                   case ALIAS:
                      if (next.getParent().getType() != FROM)
                      {
                         if (nestedInContains == 0)
                         {
                            generateProperty(next, false, true);
                         }
                      }
                      // otherwise already processed with the FROM parent
                      break;

                   case PROPERTY:
                      if (next.getParent().getType() != ALIAS)
                      {
                         generateProperty(next, false, false);
                      }
                      // otherwise the property was already processed with the ALIAS parent
                      break;

                   case DMO:
                      // do not output this, it was / will be used with the alias in a FROM node
                      break;

                   case STRING:
                      SessionAttr.sessionAttr(text).ifPresent(sessionAttrs::add);
                      if (nestedInContains != 1)
                      {
                         sb.append(text);
                      }
                      break;

                   default:
                      sb.append(text);
                      break;
                }
             }
          }
       }

       /**
        * Convenience method to detect whether an AST represents a binary operator, based on its
        * token type.
        *
        * @param   ast
        *          AST node to test.
        *
        * @return  {@code true} if the AST's token type indicates a binary operator, otherwise
        *          {@code false} is returned.
        */
       private boolean isBinaryOperator(Aast ast)
       {
          switch (ast.getType())
          {
             case OR:
             case AND:
             case EQUALS:
             case NOT_EQ:
             case LIKE:
             case GT:
             case LT:
             case GTE:
             case LTE:
             case PLUS:
             case MINUS:
             case MULTIPLY:
             case DIVIDE:
             case CONCAT:
                return true;
          }

          return false;
       }

       /**
        * Generates the list of source tables where the records are to be fetched (ie {@code FROM} and
        * any type of {@code JOIN} part. For the first occurrence {@code FROM} keyword is used, for
        * the subsequent, the appropriate {@code JOIN} is generated.
        *
        * @param   node
        *          A {@code FQLAst} node which contains the node/s.
        * @param   innerJoins
        *          A map with inner aliases to be joined.
        * @return  the alias of the master table of the query
        */
       private String generateFrom(FQLAst node, Map<String, String> innerJoins)
       {
          sb.append("\nfrom\n\t");
          boolean first = true;
          String rightAlias = null;
          String masterAlias = null;
          DmoMeta rightDmoInfo = null;

          int cnt = node.getNumImmediateChildren();
          for (int k = 0; k < cnt; k++)
          {
             FQLAst child = (FQLAst) node.getChildAt(k);
             if (child.getType() == ALIAS || (cnt == 1 && child.getType() == DMO))
             {
                rightAlias = child.getType() == ALIAS ? child.getText() : NO_ALIAS;
                rightDmoInfo = getScopedDmoInfo(rightAlias);
                if (rightDmoInfo != null)
                {
                   if (first)
                   {
                      first = false;
                      masterAlias = rightAlias;
                   }
                   else
                   {
                      // sb.append(", ");
                      // sb.append(" cross join ");
                      sb.append("\ncross join\n\t");
                   }
                   sb.append(rightDmoInfo.getSqlTableName()).append(" ").
                      append(getSqlTableAliasName(rightAlias));
                }
                else
                {
                   warn("Alias '" + rightAlias + "' not known ", node);
                }
             }
             else if (child.getType() == JOIN)
             {
                generateJoin(child, rightAlias, rightDmoInfo);
             }
          }

          if (innerJoins != null && !innerJoins.isEmpty())
          {
             for (Map.Entry<String, String> entry : innerJoins.entrySet())
             {
                String child = getSqlTableAliasName(entry.getKey());
                String father = getSqlTableAliasName(entry.getValue());

                String sqlTable = aliases.peek().get(child).getSqlTableName();

                sb.append("\ninner join\n\t").append(sqlTable).append(" ").append(child).
                   append(" on ").append(father).append(".").append(Session.PK).
                   append("=").append(child).append(".parent__id ");
             }
          }
          sb.append(" ");
          return masterAlias;
       }

       /**
        * Generates the JOIN part of the query statement.
        *
        * @param   joinNode
        *          The {@code FQLAst} node which has the FQL JOIN data.
        * @param   rightAlias
        *          The right alias.
        * @param   rightDmo
        *          The DMO info for the right table.
        */
       private void generateJoin(FQLAst joinNode, String rightAlias, DmoMeta rightDmo)
       {
          if (joinNode.isAnnotation("composite-join"))
          {
             FQLAst compositeDmo = (FQLAst) joinNode.getImmediateChild(ALIAS, null);
             FQLAst compositeAlias = (FQLAst) joinNode.getImmediateChild(ALIAS, compositeDmo);
             String alias = compositeAlias.getText();
             DmoMeta dmoAnn = getScopedDmoInfo(alias);
             if (dmoAnn == null)
             {
                warn("Alias '" + rightAlias + "' not known ", joinNode);
                return;
             }

             // sb.append(" inner join ");
             sb.append("\ninner join\n\t");
             String sqlCompositeTableAliasName = getSqlTableAliasName(alias);
             sb.append(dmoAnn.getSqlTableName())
               .append(" ")
               .append(sqlCompositeTableAliasName);

             // sb.append(" on ");
             sb.append("\non\n\t");
             sb.append(getSqlTableAliasName(rightAlias))
               .append(".").append(Session.PK).append("=")          // TODO: extract constant for [id]
               .append(sqlCompositeTableAliasName)
               .append(".parent__id");  // TODO: extract constant for [parent__id]
             return;
          }

          boolean cross = true;
          if (joinNode.getImmediateChild(OUTER, null) != null)
          {
             if (joinNode.getImmediateChild(LEFT, null) != null)
             {
                sb.append("\nleft ");
             }
             else if (joinNode.getImmediateChild(RIGHT, null) != null)
             {
                sb.append(" right ");
             }
             sb.append(" outer ");
             cross = false;
          }
          else if (joinNode.getImmediateChild(FULL, null) != null)
          {
             sb.append(" full ");
             cross = false;
          }
          else if (joinNode.getImmediateChild(INNER, null) != null)
          {
             sb.append(" inner ");
             cross = false;
          }

          if (cross)
          {
             sb.append(" cross ");
          }

          FQLAst dmo = (FQLAst) joinNode.getImmediateChild(DMO, null);
          String alias = dmo.getRightAdjacent().getText();
          DmoMeta dmoInfo = getScopedDmoInfo(alias);

          // sb.append(" join ");
          sb.append("join\n\t")
            .append(dmoInfo.getSqlTableName())
            .append(" ")
            .append(getSqlTableAliasName(alias));

          this.generateOnClause((FQLAst) joinNode.getImmediateChild(ON, null));
       }

      /**
       * Generates the on clause for a join.
       *
       * @param onClause
       *        The node from where we start to generate the "on" clause.
       */
       private void generateOnClause(FQLAst onClause)
       {
          if (onClause == null || onClause.getFirstChild() == null)
          {
             return;
          }

          sb.append("\non\n\t");

          generateExpression((FQLAst) onClause.getFirstChild());
       }

       /**
        * Collects into {@code aliasesMap} all occurrences of aliases with normalized accesses to extents fields
        * (that is, using bracket {@code [} notation).
        * <p>
        * Important: only the normalized tables that are already in the map are processed. If other normalized
        * extent fields are encountered, they are skipped as they will be processed at the moment their main
        * alias is collected (as part of a subselect, most likely).
        *
        * @param   node
        *          The root node to start from (the {@code WHERE} or {@code ORDER} node).
        * @param   aliasesMap
        *          The {@code Map} where the new aliases will be stored.
        * @param   innerJoins
        *          The map where to save the inner join information.
        * @param   staticSubscriptConstraints
        *          The consumer to be applied for static subscript fields.
        * @param   dynamicSubscriptConstraints
        *          The consumer to be applied for dynamic subscript fields.
        */
       private void collectNormalizedExtentsAliases(FQLAst node,
                                                    Map<String, DmoMeta> aliasesMap,
                                                    Map<String, String> innerJoins,
                                                    BiConsumer<FQLAst, List<FQLAst>> staticSubscriptConstraints,
                                                    BiConsumer<FQLAst, List<FQLAst>> dynamicSubscriptConstraints)
       {
          if (node == null || node.getFirstChild() == null)
          {
             return; // if the where predicate is absent, nothing to collect
          }

          List<FQLAst> dynamicSubscripts = new ArrayList<>(paramCount);
          List<FQLAst> staticSubscripts = new ArrayList<>(paramCount);

          final Aast[] crtSubselect = { null }; // nasty Java final requirement

          AstWalkListener awl = new AstWalkListener()
          {
             @Override
             public void descent(Aast ast)
             {
                if (ast.getType() == SUBSELECT && crtSubselect[0] == null)
                {
                   crtSubselect[0] = ast;
                }
             }

             @Override
             public void ascent(Aast ast)
             {
                if (crtSubselect[0] != null)
                {
                   if (ast == crtSubselect[0])
                   {
                      crtSubselect[0] = null;
                   }

                   return; // nothing more to process here
                }

                if (ast.getType() == LBRACKET && ast.getParent().getType() == PROPERTY)
                {
                   Aast grandFather = ast.getParent().getParent();
                   if (grandFather.getType() == ALIAS)
                   {
                      Aast contains = insideContains(grandFather);
                      if (contains != null)
                      {
                         ast.remove();
                      }
                      else
                      {
                         DmoMeta dmoMeta = aliasesMap.get(grandFather.getText());
                         String extentName = ast.getParent().getText();
                         Property extProperty = dmoMeta.propsByName.get(extentName);
                         if (extProperty == null && ReservedProperty.isReservedProperty(extentName))
                         {
                            warn("Failed to process extent ALIAS. " +
                                 "Probable caused by a WORD index which is not supported by FWD yet.",
                                 (FQLAst) ast);

                            extProperty = dmoMeta.propsByName.get(extentName.substring(3));
                            if (extProperty == null)
                            {
                               return;
                            }
                         }
                         // if the extent property was denormalized, treat it as a simple field
                         int extent = extProperty.index > 0 ? 0 : extProperty.extent;
                         if (extent == 0)
                         {
                            warn("[] syntax for a non extent property.", (FQLAst) grandFather);
                            return;
                         }
                         FQLAst subscriptAst = (FQLAst) ast.getFirstChild();
                         String extIdx = subscriptAst.getText();
                         String extIdxLit = extIdx;
                         if (subscriptAst.getType() != NUM_LITERAL)
                         {
                            // make the extent index unique - to allow multiple indexed properties with complex exprs
                            extIdx = "d" + (complexSubscript++);
                            // extIdxLit = "?";
                         }
                         String grandfatherSqlAlias = getSqlTableAliasName(grandFather.getText());
                         String newSqlAlias = grandfatherSqlAlias + "_" + extent + "x" + extIdx + "_"; // aliases(grandfather)
                         aliasesMap.putIfAbsent(newSqlAlias, new CompositeDmoInfo(dmoMeta, extent));
                         if (innerJoins != null)
                         {
                            innerJoins.putIfAbsent(newSqlAlias, grandfatherSqlAlias);
                         }
                         if (subscriptAst.getType() == NUM_LITERAL)
                         {
                            staticSubscripts.add((FQLAst) grandFather);
                         }
                         else
                         {
                            dynamicSubscripts.add((FQLAst) grandFather);
                         }
                         grandFather.putAnnotation("parentAlias", grandfatherSqlAlias);
                         grandFather.putAnnotation("subscript", extIdxLit);
                         grandFather.setText(newSqlAlias);
                         ast.remove();
                      }
                   }
                   else
                   {
                      warn("Generate SQL from unknown node.", (FQLAst) grandFather);
                   }
                }
                if (node.getType() == WHERE && ast.getType() == FUNCTION
                      && dialect.isUdfContains(ast.getText()))
                {
                   ContainsRewriteData rewriteData = new ContainsRewriteData();
                   Aast arg = ast.getImmediateChild(null, FUNCTION, ALIAS);
                   // TODO: do not add RTRIM for the CONTAINS field in the HQLPreprocessor
                   while (arg != null && arg.getType() == FUNCTION)
                   {
                      arg = arg.getImmediateChild(null, FUNCTION, ALIAS);
                   }
                   if (arg == null)
                   {
                      warn("Generate SQL from unknown node.", (FQLAst) ast);
                      throw new IllegalStateException();
                   }
                   rewriteData.fieldAst = (FQLAst) arg;
                   Aast expr = ast.getImmediateChild(null, POSITIONAL, STRING);
                   if (expr == null)
                   {
                      warn("Generate SQL from unknown node.", (FQLAst) ast);
                      throw new IllegalStateException();
                   }
                   if (expr.getType() == POSITIONAL)
                   {
                      rewriteData.narg = ((Long) expr.getAnnotation("index")).intValue();
                      Object oParam = queryParams.parameters[rewriteData.narg];
                      character param;
                      if (oParam instanceof P2JQuery.ParamResolver)
                      {
                         param = (character)((P2JQuery.ParamResolver) oParam).resolve();
                         queryParams.parameters[rewriteData.narg] = param;
                      }
                      else
                      {
                         param = (character) oParam;
                      }
                      rewriteData.expr = param == null || param.isUnknown() ? "" :
                                                          param.toStringMessage();
                   }
                   else
                   {
                      String text = expr.getText();
                      rewriteData.expr = text.substring(1, text.length() - 1);
                   }
                   if (!useWordTables || dialect.useUdf4Contains())
                   {
                      rewriteData.toRPN();
                   }
                   else
                   {
                      rewriteData.toCNF(dialect.useMixedNode4Contains());
                   }
                   if (rewriteData.terms.isEmpty())
                   {
                      rewriteData.alwaysFalse = true;
                   }
                   containsRewriteData.add(rewriteData);
                }
             }

             /**
              * Check if the given Ast is inside a CONTAINS UDF call.
              *
              * @param   node
              *          AST to be checked.
              *
              * @return  CONTAINS UDF call AST or {@code null}.
              */
             private Aast insideContains(Aast node)
             {
                Aast parent = node.getParent();
                while (parent != null && parent.getType() == FUNCTION)
                {
                   if (dialect.isUdfContains(parent.getText()))
                   {
                      return parent;
                   }
                   parent = parent.getParent();
                }
                return null;
             }

             @Override
             public void nextChild(Aast ast, int index)
             {
             }
          };
          Iterator<Aast> iter = node.iterator(0, awl);
          while (iter.hasNext())
          {
             iter.next();
          }

          staticSubscriptConstraints.accept(node, staticSubscripts);
          dynamicSubscriptConstraints.accept(node, dynamicSubscripts);
       }

       /**
        * Rewrite the dynamic subscripts in a SELECT's WHERE clause, so that the field's index is matched before
        * testing the field's value, to preserve substitution argument order.
        *
        * @param    whereNode
        *           The parent WHERE node.
        * @param    dynamicSubscripts
        *           The list of ALIAS ASTs which use dynamic subscripts.
        */
       private void rewriteSelectDynamicSubscripts(FQLAst whereNode, List<FQLAst> dynamicSubscripts)
       {
          for (FQLAst aliasAst : dynamicSubscripts)
          {
             String subStr = aliasAst.getAnnotation("subscript").toString(); // it's a String, anyway
             int subNo = Integer.parseInt(subStr.substring(1)); // skip the first character ('?')

             FQLAst exprNode = (FQLAst) whereNode.getFirstChild();
             exprNode.remove(); // it's the only child of the WHERE node, so we don't need to save its position

             // create AND operator
             FQLAst and = new FQLAst(new Token(AND));
             and.setText("and");
                // add the 'alias.list__index = ?' condition
                FQLAst eq = new FQLAst(new Token(EQUALS));
                eq.setText("=");
                   FQLAst alias = new FQLAst(new Token(ALIAS));
                   alias.setText(aliasAst.getText());
                      FQLAst property = new FQLAst(new Token(PROPERTY));
                      property.setText("list__index");
                   alias.addChild(property);
                eq.addChild(alias);
                FQLAst pos = new FQLAst(new Token(POSITIONAL));
                   pos.setText("?");
                   pos.putAnnotation("paramNo", (long) subNo);
                   pos.putAnnotation("paramCard", 1L); // the 'extent' is always *an* integer
                eq.addChild(pos);
             and.addChild(eq);
             if (exprNode.getType() == OR)
             {
                // create a LPARENS root
                FQLAst lparens  = new FQLAst(new Token(LPARENS));
                lparens.setText("(");
                lparens.addChild(exprNode);
                and.addChild(lparens);
             }
             else
             {
                and.addChild(exprNode);
             }
             whereNode.addChild(and);

             // shift the parameters, too:
             if (paraPerm == null)
             {
                paraPerm = new int[queryParams.maxParamIndex + 1];
                for (int k = 0; k < paraPerm.length; k++)
                {
                   paraPerm[k] = k;
                }
             }
             System.arraycopy(paraPerm, 0, paraPerm, 1, subNo);
             paraPerm[0] = subNo;
          }
       }

       /**
        * Collects the list of aliases declared in a FQL {@code FROM} or {@code JOIN}. For each encountered alias,
        * a {@code DmoMeta} structure is added. It will be used later to convert the table name and properties to
        * SQL values.
        *
        * @param   node
        *          The node which contains the declarations of aliases for this query.
        */
       private void collectTableAliases(FQLAst node, Map<String, DmoMeta> aliases)
       {
          if (node == null)
          {
             return;
          }
          int cnt = node.getNumImmediateChildren();
          for (int k = 0; k < cnt; k++)
          {
             FQLAst child = (FQLAst) node.getChildAt(k);
             if (child.getType() == DMO)
             {
                FQLAst alias = (FQLAst) node.getChildAt(k + 1); // childAt(n) returns null, no IOOBE is thrown
                String dmoName = child.getText();
                DmoMeta dmoInfo = DmoMetadataManager.getDmoInfo(dmoName, schema);
                if (dmoInfo != null)
                {
                   if (dmoInfo.getMetaTable() == SystemTable._UserTableStat)
                   {
                      userTableStatRead = true;
                   }
                   if (dmoInfo.getMetaTable() == SystemTable._Lock)
                   {
                      lockTableRead = true;
                   }
                   if (alias == null || alias.getType() == ALIAS)
                   {
                      String aliasName;
                      if (alias != null)
                      {
                         k++;
                         aliasName = alias.getText();
                      }
                      else
                      {
                         // special case: single table statement with no alias provided in FROM clause, only
                         // the DMO implementation name; all properties are unqualified
                         aliasName = NO_ALIAS;
                      }

                      aliases.put(aliasName, dmoInfo);
                      String sqlAlias = dmoName.substring(0, Math.min(dmoName.length(), 10));
                      sqlAlias = sqlAlias.toLowerCase() + this.sqlTableAliasMap.size() + "_";
                      sqlTableAliasMap.put(aliasName, sqlAlias);
                   }
                }
                else
                {
                   warn("Failed to resolve metadata for '" + dmoName + "' DMO in '" + schema + "' schema. " +
                        "The generated SQL might not be valid.", node);
                }
             }
             else if (child.getType() == ALIAS      &&
                      child.getFirstChild() != null &&
                      node.isAnnotation("composite-join"))
             {
                FQLAst dmo = (FQLAst) node.getPrevSibling().getPrevSibling();
                FQLAst composite = (FQLAst) child.getFirstChild();
                FQLAst extentAlias = (FQLAst) child.getNextSibling(); // @ k + 1
                DmoMeta dmoInfo = DmoMetadataManager.getDmoInfo(dmo.getText(), schema);
                // RecordMeta meta = new RecordMeta(dmoInfo.iface); -- useless at this moment
                // detecting the composite table by parsing the PROPERTY below current ALIAS
                int extent = Integer.parseInt(composite.getText().substring("composite".length()));
                aliases.put(extentAlias.getText(), new CompositeDmoInfo(dmoInfo, extent));
                String compositeTableName =
                      "composite" + sqlTableAliasMap.size() + "x" + sqlTableAliasMap.size() + "_";
                sqlTableAliasMap.put(extentAlias.getText(), compositeTableName);
                k++;
             }
             else if (child.getType() == JOIN)
             {
                collectTableAliases(child, aliases);
             }
          }
       }

       /**
        * Obtain the SQL table alias for a FQL alias.
        *
        * @param   alias
        *          The FQL alias for a table.
        *
        * @return  The SQL table alias. If not found or SQL aliases are not enabled, the FQL alias
        *          is returned.
        */
       private String getSqlTableAliasName(String alias)
       {
          if (!useSqlTableAlias)
          {
             return alias;
          }
          String sqlAlias = sqlTableAliasMap.get(alias);
          return sqlAlias == null ? alias : sqlAlias;
       }

       /**
        * Computes an unique SQL name for a field/column.
        *
        * @param   property
        *          The property that represent the field to be returned by a SELECT statement.
        *
        * @return  An unique identifier for this column.
        */
       private String getSqlColumnAlias(Property property)
       {
          if (property.propId == ReservedProperty.ID_PRIMARY_KEY)
          {
             return "id" + (columnUid++) + "_";
          }
          else if (property.propId < 0)
          {
             return "column" + (columnUid++) + "_" + (aliases.size() - 1) + "_";
          }
          else
          {
             String fqlName = property.column;
             int k = 0;
             while (k < fqlName.length() && Character.isLetter(fqlName.charAt(k)))
             {
                k++;
             }
             String sqlName = fqlName.substring(0, k);
             if (sqlName.isEmpty())
             {
                sqlName = "column";
             }
             return sqlName +  (columnUid++) + "_" + (aliases.size() - 1) + "_";
          }
       }

       /**
        * Appends a unique column alias to the sql statement.
        *
        * @param   base
        *          The base prefix used to build the alias.
        */
       private void appendColAlias(String base)
       {
          appendColAlias(base, true);
       }

       /**
        * Appends a unique column alias to the sql statement.
        *
        * @param   base
        *          The base prefix used to build the alias.
        * @param   addToRowAliases
        *          If we should add the alias to the rowAliases list when converting a statement with contains.
        *
        * @return  The unique column alias.
        */
       private String appendColAlias(String base, boolean addToRowAliases)
       {
          String columnAlias = base + (columnUid++) + "_" + (aliases.size() - 1) + "_";
          sb.append(columnAlias);
          if (cte4order != null && addToRowAliases)
          {
             rowAliases.add(columnAlias);
          }
          return columnAlias;
       }

       /**
        * Logging utility method. Writes a warning message and the node/location of the issue encountered.
        *
        * @param   msg
        *          The message to be printed.
        * @param   node
        *          The node that caused the issue (may be null).
        */
       private static void warn(String msg, FQLAst node)
       {
          if (LOG.isLoggable(Level.WARNING))
          {
             LOG.log(Level.WARNING, (node != null) ? msg + " " + node.toStringVerbose() : msg);
          }
       }

       /**
        * Generate a SQL condition ('=' or 'LIKE') for an operation on the CNF.
        *
        * @param token
        *        CNF token
        * @param args
        *        holder for the generated query parameters
        *
        * @return a SQL condition ('=' or 'LIKE') for an operation on the CNF
        */
       private static String op(Term token, List<String> args)
       {
          if (token instanceof Equals)
          {
             args.add(token.getValue());
             return "=";
          }
          if (token instanceof StartsWith)
          {
             args.add(token.getValue() + "%");
             return "like";
          }
          // CNF for a valid CONTAINS argument does not contain negations
          throw new IllegalStateException("Unexpected CNF token:" + token);
       }

       /**
        * Conditionally wrap operation on a StringBuilder with upper() call.
        *
        * @param   wrap
        *          Flag indicating that wrapping is required.
        * @param   sb
        *          The output {@code StringBuilder} instance.
        * @param   op
        *          Operation to be wrapped.
        */
       private static void toUpper(boolean wrap, StringBuilder sb, Runnable op)
       {
          if (!wrap)
          {
             op.run();
          }
          else
          {
             sb.append("upper(");
             op.run();
             sb.append(")");
          }
       }

       /**
        * The immutable structure for a composite table. It adds only the extent as internal data and
        * foreign key to its {@code parent} DMO table.
        */
       private static class CompositeDmoInfo
       extends DmoMeta
       {
          /** The parent DMO structure. */
          private final DmoMeta parentDmoInfo;

          /** The extent for the fields in this table. */
          private final int extent;

          /** The SQL name of the table. It is composed of the parent's table name and the extent. */
          private final String sqlTableName;

          /**
           * The constructor initializes the data structure.
           */
          CompositeDmoInfo(DmoMeta parent, int extent)
          {
             super(parent.dmoImplInterface, parent.iface, parent.table);
             this.parentDmoInfo = parent;
             this.extent = extent;

             this.propsByName = new LinkedHashMap<>(parent.propsByName);
             this.propsByName.put("list__index", ReservedProperty.LIST__INDEX);
             this.propsByName.put("parent__id", ReservedProperty.PARENT__ID);

             this.sqlTableName = parent.getSqlTableName() + "__" + extent; // this is weak
          }

          /**
           * Obtain the name of the SQL table for this DMO.
           *
           * @return  the name of the SQL table for this DMO.
           */
          public String getSqlTableName()
          {
             return sqlTableName;
          }

          /**
           * Get parent DmoMeta.
           *
           * @return  The parent DmoMeta.
           */
          public DmoMeta getParentDmoInfo()
          {
             return parentDmoInfo;
          }
       }

       /**
        * Field info holder
        */
       private static class FieldInfo
       {
          /** The table name. */
          public String tblName = null;

          /** The table alias. */
          public String tblAlias = null;

          /** The table key in the aliases map. */
          public String tblMetaKey = null;

          /** The field name. */
          public String fldName = null;

          /** The extent size ({@code > 0} only for custom extents). */
          public int extent = 0;
       }

       /**
        * Auxiliary date used for CONTAINS UDF call re-writing
        */
       private static class ContainsRewriteData
       extends FieldInfo
       {
          /** Field AST */
          public FQLAst fieldAst;

          /** CTE name */
          public String cteName = null;

          /** CONTAINS UDF second argument value */
          public String expr = null;

          /** CNF representation of expr */
          public List<List<Term>> cnf;

          /** Distinct terms found in the expression */
          private Map<String, Term> terms = new LinkedHashMap<>();

          /** Flags that expression is good for using CTE */
          public boolean useCTE = true;

          /** The argument index (if placeholder found). */
          public int narg = -1;

          /** the condition is always false (is empty or invalid) */
          public boolean alwaysFalse = false;

          /** Flag that word index is used for implicit sorting of the result set */
          public boolean forSorting = false;

          /** the holder for the generated query parameters */
          public List<String> args = new ArrayList<>();

          /** The field Property. */
          public Property fp;

          /** The extent table (for extent field). */
          public String extentTable;

          /**
           * Generate a replacement for the CONTAINS UDF call.
           *
           * @param   schema
           *          schema name
           * @param   sb
           *          output buffer
           */
          public void containsQuery(String schema, StringBuilder sb)
          {
             if (this.alwaysFalse)
             {
                sb.append("1 = 0");
                return;
             }
             sb.append(tblAlias).append(".").append(PK).append(" in (\n");
             containsCTE(schema, null, null, sb);
             sb.append(")");
          }

          /**
           * Convert 'expr' to CNF
           *
           * @param   useMixedMode
           *          Set 'useCTE' flag based on the expression complexity.
           */
          public void toCNF(boolean useMixedMode)
          {
             LogicalExpressionConverter converter = new LogicalExpressionConverter(expr, true);
             cnf = converter.toCNF(false);
             terms = converter.getTerms();
             if (useMixedMode)
             {
                useCTE = cnf.size() < 2;
             }
          }

          /** Validate 'expr' by converting to RPN. */
          public void toRPN()
          {
             LogicalExpressionConverter converter = new LogicalExpressionConverter(expr, true);
             converter.toRPN(false);
             terms = converter.getTerms();
             useCTE = false;
          }

          /**
           * Generate a WITH clause for the {@code CONTAINS} UDF call.
           *
           * @param   schema
           *          The schema name.
           * @param   aggregator
           *          The aggregate helper.
           * @param   weights
           *          The holder for ORDER BY components (used for implicit sorting).
           * @param   sb
           *          The output buffer.
           *
           * @return  {@code true} if a new term in the WITH clause.
           */
          public boolean containsWith(String schema,
                                      Optional<Dialect.WeightAggregator> aggregator,
                                      StringBuilder weights,
                                      StringBuilder sb)
          {
             if (this.alwaysFalse)
             {
                return false;
             }
             sb.append(cteName).append(" as (\n");
             containsCTE(schema, aggregator, weights, sb);
             sb.append(")\n");
             return true;
          }

          /**
           * Generate a WITH clause for the {@code CONTAINS} UDF call (no word tables).
           *
           * @param   schema
           *          The schema name.
           * @param   dialect
           *          The database dialect.
           * @param   weights
           *          The holder for ORDER BY components (used for implicit sorting).
           * @param   sb
           *          The output buffer.
           */
          public void containsWith4UDF(String schema,
                                   Dialect dialect,
                                   StringBuilder weights,
                                   StringBuilder sb)
          {
             sb.append(cteName).append(" as (\n");
             containsCTE4UDF(schema, dialect, weights, sb);
             sb.append(")\n");
          }

          /**
           * Generate common table expression for {@code CONTAINS} (no word tables).
           *
           * @param   schema
           *          The schema name.
           * @param   dialect
           *          The database dialect.
           * @param   weights
           *          The holder for ORDER BY components (used for implicit sorting).
           * @param   sb
           *          The output buffer.
           */
          private void containsCTE4UDF(String schema,
                                       Dialect dialect,
                                       StringBuilder weights,
                                       StringBuilder sb)
          {
             Optional<Dialect.WeightAggregator> aggregator = dialect.weightAggregator();
             sb.append("\tselect "); //distinct ").append(PK).
             String indent = "";
             String placeHolder = fp.caseSensitive? " ?" : " UPPER(?)";
             BiConsumer<StringBuilder, Runnable> aggregate = aggregator.get().aggregate();
             String minAggrValue = aggregator.get().minAggregatedValue();
             String tbl = extentTable != null ? "e" : "t";
             String contains = dialect.containsUdfName();
             for (Term t: terms.values())
             {
                args.add(t.containsExpr());
                String calias = "ww" + t.getOrder();
                aggregate.accept(sb, () -> {
                   sb.append(contains).append("(").append(tbl).append(".").append(fldName).
                      append(", ").append(placeHolder).append(")");
                });
                sb.append(" as ").append(calias).append(", ");
                weights.append("coalesce(").
                        append(cteName).append(".").append(calias).
                        append(", ").append(minAggrValue).append(") desc, ");
             }
             sb.append(indent).append("t.").append(PK).
             append("\n\t").append("from ").append(tblName).append(" t\n");
             if (extentTable != null)
             {
                sb.append("\tjoin ").
                   append(extentTable).
                   append(" e on (e.parent__id = t.").append(PK).append(")\n");
             }
             sb.append("\twhere ").append(contains).append("(").
                 append(tbl).append(".").append(fldName).
                 append(", ").append(placeHolder).append(") ");
             sb.append("group by t.").append(PK);
             args.add(expr);
          }

          /**
           * Generate common table expression for CONTAINS.
           *
           * @param   schema
           *          The schema name.
           * @param   aggregator
           *          The aggregate helper.
           * @param   weights
           *          The holder for ORDER BY components (used for implicit sorting).
           * @param   sb
           *          The output buffer.
           */
          private void containsCTE(String schema,
                                   Optional<Dialect.WeightAggregator> aggregator,
                                   StringBuilder weights,
                                   StringBuilder sb)
          {
             WordIndexData indexData = MetadataManager.getWordTableName(schema, tblName, fldName);
             if (indexData == null)
             {
                return; // TODO: not sure if this is the right action
             }

             sb.append("\tselect "); //distinct ").append(PK).
             String indent = "";
             List<Weight> w = new ArrayList<>(terms.size());
             if (forSorting)
             {
                sb.append(indent).append("t.").append(PK);
                int[] n = {1};
                Map<String, List<Term>> cnfClone = cnf.stream()
                   .map(ArrayList::new)
                   .collect(Collectors.toMap(l -> "w" + n[0]++, Function.identity()));
                terms.values().stream().forEach(term -> {
                   searchmatch:
                   for (Map.Entry<String, List<Term>> andTerm: cnfClone.entrySet())
                   {
                      Iterator<Term> ti = andTerm.getValue().iterator();
                      while (ti.hasNext())
                      {
                         Term t = ti.next();
                         if (term.expr().equals(t.expr()))
                         {
                            String walias = andTerm.getKey();
                            String calias = walias + t.getOrder();
                            w.add(new Weight(walias, calias, t));
                            ti.remove();
                            break searchmatch;
                         }
                      }
                   }
                });

                String placeHolder = indexData.caseSensitive ? " ?" : " UPPER(?)";
                BiConsumer<StringBuilder, Runnable> aggregate = aggregator.get().aggregate();
                String minAggrValue = aggregator.get().minAggregatedValue();
                for (Weight ww : w)
                {
                      sb.append(", ");
                      aggregate.accept(sb, () -> {
                         sb.append(ww.walias).append(".word ").
                            append(op(ww.term, args)).append(placeHolder);
                      });
                      sb.append(" as ").append(ww.calias);
                      weights.append("coalesce(").
                              append(cteName).append(".").append(ww.calias).
                              append(", ").append(minAggrValue).append(") desc, ");
                }
             }
             else
             {
                // don't use distinct as it rules out some DB optimizations
                // sb.append("distinct ");

                sb.append(indent).append("t.").append(PK);
             }

             sb.append("\n\t").append("from ").append(tblName).append(" t\n");

             String extentTableName = indexData.extentTableName;
             if (extentTableName == null && extent > 0) // custom extent
             {
                extentTableName = "(select distinct parent__id, list__index from " + indexData.wordTableName + ")";
             }
             if (extentTableName != null)
             {
                sb.append("\tjoin ").
                   append(extentTableName).
                   append(" e on (e.parent__id = t.").append(PK).append(")\n");
             }

             int joinN = 1;
             for (List<Term> andTerm: cnf)
             {
                String walias = "w" + joinN++;
                sb.append("\t\tjoin ").
                      append(indexData.wordTableName).append(" as ").append(walias).
                      append(" on (").append(walias);
                      if (indexData.extentTableName != null)
                      {
                         sb.append(".parent__id = e.parent__id and ").
                            append(walias).append(".list__index  = e.list__index");
                      }
                      else
                      {
                         sb.append(".parent__id = t.").append(PK);
                      }
                      sb.append(" and (");
                      onClause(sb, walias, andTerm, indexData.caseSensitive);
                 sb.append("))\n");
             }

             if (forSorting)
             {
                sb.append("\tgroup by ").append(PK).append("\n");
             }
          }

          /**
           * Generate an ON clause for a CONTAINS UDF call replacement query.
           *
           * @param   sb
           *          The output buffer.
           * @param   walias
           *          The word table alias.
           * @param   andTerm
           *          The list of tokens in the AND term of the CNF.
           * @param   caseSensitive
           *          Flag indicating that field is case-sensitive.
           */
          private void onClause(StringBuilder sb, String walias, List<Term> andTerm, boolean caseSensitive)
          {
             // NB: uppercase at the database side
             String placeHolder = caseSensitive ? " ?" : " UPPER(?)";
             Iterator<Term> tokens = andTerm.iterator();
             Term token = tokens.next(); // always exists

             sb.append(walias).append(".word ").append(op(token, args)).append(placeHolder);
             while (tokens.hasNext())
             {
                token = tokens.next(); // always exists
                sb.append(" or ").append(walias).append(".word ").append(op(token, args)).append(placeHolder);
             }
          }

          /**
           * Generate a 'weight' column for a CONTAINS UDF call replacement query.
           *
           * @param   weights
           *          The holder for ORDER BY components (used for implicit sorting).
           * @param   sb
           *          The output buffer.
           * @param   aggregate
           *          The aggregate wrapper.
           * @param   walias
           *          The word table alias.
           * @param   andTerm
           *          The list of tokens in the AND term of the CNF.
           * @param   caseSensitive
           *          Flag indicating that field is case-sensitive.
           */
          private void weights(StringBuilder weights,
                               StringBuilder sb,
                               BiConsumer<StringBuilder, Runnable> aggregate,
                               String walias, List<Term> andTerm,
                               boolean caseSensitive)
          {
             // NB: uppercase at the database side
             String placeHolder = caseSensitive ? " ?" : " UPPER(?)";
             for (Term t : andTerm)
             {
                String colAlias = walias + t.getOrder();
                aggregate.accept(sb,
                                 () -> sb.append(walias).append(".word ").append(op(t, args)).append(placeHolder));
                sb.append(" as ").append(colAlias).append(", ");
                weights.append(cteName).append(".").append(colAlias).append(" desc,");
             }
          }
       }

       /**
        * An immutable structure which contains weight column data.
        */
       private static class Weight
       {
          /** The word table alias. */
          public final String walias;

          /** The weight column alias. */
          public final String calias;

          /** The weight term. */
          public final Term term;

          /**
           * Constructor.
           *
           * @param   walias
           *          The word table alias.
           * @param   calias
           *          The weight column alias.
           * @param   term
           *          The weight term.
           */
          public Weight(String walias, String calias, Term term)
          {
             this.walias = walias;
             this.calias = calias;
             this.term = term;
          }
       }

       /**
        * Test program.
        *
        * @param   args
        *          The command line arguments.
        */
       public static void main(String[] args)
       {
          FqlToSqlConverter fql2sql = getInstance(new P2JH2Dialect(), new Database("fwd"));
          String sql = fql2sql.toSQL(
                "UPDATE com.goldencode.testcases.dmo.fwd.Book SET bookId=1, bookTitle='X'",
                0, 0, new ArrayList<>(), null, false);
          System.out.println(sql);
       }
    }