%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
%% Query
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
\newpage
\subsection{Query}
\label{dm:query}

CMIS provides a type-based query service for discovering objects that match specified criteria, by defining a read-only projection of the CMIS data model into a relational view. 

Through this relational view, queries may be performed via a simplified SQL SELECT statement. This query language is based on a subset of the SQL-92 grammar (ISO/IEC 9075: 1992 – Database Language SQL), with a few extensions to enhance its filtering capability for the CMIS data model, such as existential quantification for multi-valued property, full-text search, and folder membership. Other statements of the SQL language are not adopted by CMIS. The semantics of this query language is defined by the SQL-92 standard, plus the extensions, in conjunction with the model mapping defined by CMIS's relational view.

\cmisgraphics{cmisquery}{width=1\textwidth}{CMIS Query}
%\begin{figure}[htb]
%\centering
%\includegraphics[width=1.0\textwidth]{cmisquery.pdf}
%\caption{CMIS Query}
%\end{figure}


\subsubsection{Relational View Projection of the CMIS Data Model}

The relational view of a CMIS repository consists of a collection of virtual tables that are defined on top of the CMIS data model.  This relational view is used for query purposes only. 

In this relational view a \emph{virtual table} is implicitly defined for each queryable object-type defined in the repository. (Non-queryable object-types are NOT exposed through this relational view.)

In each virtual table, a virtual column is implicitly defined for each property defined in the object-type definition AND for all properties defined on ANY ancestor-type of the object-type but NOT defined in the object-type definition. Virtual columns for properties defined on ancestor-types of the object-type but NOT defined in the object-Type definition MUST contain the SQL NULL value. Virtual columns for properties whose value is "not set" MUST contain the SQL NULL value.  

An object-type's \emph{queryName} attribute is used as the table name for the corresponding virtual table, and a property's \emph{queryName} attribute is used as the column name for the corresponding table column.  Please see the restrictions on queryName in section \ref{dm:querynamelimitations}~\nameref{dm:querynamelimitations}.


The virtual column for a multi-valued property MUST contain a single list value that includes all values of the property.  

\errataadd{
Please note that the following text and the BNF grammar are inconsistent. The text allows the use of secondary object types in FROM clauses and the BNF grammar does not. Because the BNF grammar is the formal description of the query language, it takes precedence over the text. That is, secondary object types MUST NOT be used in FROM clauses.

The next version of the CMIS specification will correct the BNF grammar and allow the use of secondary object types in FROM clauses.

A CMIS feature extension has been defined by the CMIS Technical Committee that allows support of secondary object types in FROM clauses also for CMIS 1.1 repositories. 
}

\paragraph{Object-Type Hierarchy in the Relational View Projection}

The relational view projection of the CMIS Data Model ensures that the virtual table for a particular object-type is a complete super-set of the virtual table for any and all of its ancestor types.

Additionally, an object-type definition's \texttt{includedInSupertypeQuery} specifies whether objects of that object-type MUST be included in the virtual table for any of its ancestor types. If the \texttt{includedInSupertypeQuery} attribute of the object-type is FALSE, then objects of that object-type MUST NOT be included in the virtual table for any of its ancestor types. 

In each virtual table, a virtual column is implicitly defined for each property defined in the object-type definition. In addition, a virtual column is also implicitly defined for each property defined on ANY ancestor-type of this object-type but NOT defined in this object-type definition. In addition, the virtual table for a secondary object type has one more virtual column for the \texttt{cmis:objectId} property defined by each object's primary type. If a secondary object type does not define any property, then its virtual table will have \texttt{cmis:objectId} as the only column, identifying the objects to which the secondary type has been applied. Virtual columns for properties defined on ancestor-types of the object-type but NOT defined (inherited) in the object-type definition MUST contain the SQL NULL value. Virtual columns for properties whose value is "not set" MUST contain the SQL NULL value. The rows of a virtual table corresponding to a queryable primary type represent the objects of that type. The rows of a virtual table corresponding to a queryable secondary type represent objects of various primary types (which may or may not be queryable) that the secondary type is applied to. To query on both an object's primary type properties and its secondary type properties, a SQL JOIN of the respective tables on the \texttt{cmis:objectId} column may be performed. Explicit JOIN support, as defined in \ref{dm:optionalcapabilities}~\nameref{dm:optionalcapabilities}, is not required for a repository to provide join between a primary type and secondary type tables based on \texttt{cmis:objectId}.


\cmisgraphics{virtualtable}{width=1\textwidth}{Virtual Tables}

\cmisgraphics{searchscope}{width=1\textwidth}{Query Search Scope}
%\begin{figure}[htb]
%\centering
%\includegraphics[width=1.0\textwidth]{searchscope.pdf}
%\caption{Query Search Scope}
%\end{figure}


\paragraph{Content Streams}

Content streams are NOT exposed through this relational view.


\paragraph{Result Set}

When a query is submitted, a set of pseudo CMIS objects will be returned.  These pseudo objects are comprised of the properties specified in the select clause of the query statement.

For each property in each object in the result set, the repository MUST include the property definition id as well as either the query name (if no alias is used) or the alias in place of the query name (if an alias is used). 

If the select clause of the query statement contains properties from a single type reference then the repository MAY represent these pseudo-objects with additional object information. 


\subsubsection{Query Language Definition}

This query languages is based on a subset of the SQL-92 grammar. CMIS-specific language extensions to SQL-92 are called out explicitly. 

The basic structure of a CMIS query is a SQL statement that MUST include the following clauses:
\begin{description}
	\item[SELECT {[}virtual columns list{]}] This clause identifies the set of virtual columns that will be included in the query results for each row and optionally their aliases.
	\item[FROM {[}virtual table names{]}] This clause identifies which virtual table(s) the query will run against. Aliases for the object-types are allowed in the BNF grammar.
\end{description}

Additionally, a CMIS query MAY include the following clauses: 
\begin{description}
	\item[WHERE {[}conditions{]}] This clause identifies the constraints that rows MUST satisfy to be considered a result for the query.
	\item[ORDER BY {[}sort specification{]}] This clause identifies the order in which the result rows MUST be sorted in the result row set.
\end{description}


\paragraph{BNF Grammar}

This BNF grammar is a "subset" of the SQL-92 grammar (ISO/IEC 9075: 1992 – Database Language SQL), except for some production alternatives. Specifically, except for these extensions, the following production rules are derived from the SQL-92 grammar. The non-terminals used in this grammar are also borrowed from the SQL-92 grammar without altering their semantics. Accordingly, the non-terminal <column name> is used for single-valued properties only so that the semantics of SQL can be preserved and borrowed. This approach not only facilitates comparison of the two query languages, and simplifies the translation of a CMIS query to a SQL query for a RDBMS-based implementation, but also allows future expansion of this query language to cover a larger subset of SQL with minimum conflict. The CMIS extensions are introduced primarily to support multi-valued properties and full-text search, and to test folder membership. Multi-valued properties are handled separately from single-valued properties, using separate non-terminals and separate production rules to prevent the extensions from corrupting SQL-92 semantics.\\\\

\lstset{language=CMISQuery}
\begin{lstlisting}
<CMIS 1.1 query statement> ::= <simple table> [ <order by clause> ]
<simple table>   ::= SELECT <select list> <from clause> [ <where clause> ]
<select list>    ::= "*" | <select sublist> [ { "," <select sublist> }... ]
<select sublist> ::= <qualifier> ".*" 
	| <value expression> [ [ AS ] <column name> ]
	| <multi-valued-column reference> [ [ AS ] <column name> ]
<value expression> ::= <column reference> | <numeric value function>
<column reference> ::= [ <qualifier> "." ] <column name> 
	| [ <qualifier> "." ] <secondary type table name> "." <secondary type column name>
<multi-valued-column reference> ::= [ <qualifier> "." ] <multi-valued-column name> 
	| [ <qualifier> "." ] <secondary type table name> "." <secondary type multi-valued-column name>
<numeric value function> ::= SCORE()
<qualifier>   ::= <table name> | <correlation name>
<from clause> ::= FROM <table reference>
<table reference> ::= <table name> [ [ AS ] <correlation name> ] | <joined table>
<joined table> ::= "(" <joined table> ")"
	| <table reference> [ <join type> ] JOIN <table reference> <join specification>
<join type> ::= INNER | LEFT [ OUTER ]
<join specification> ::= ON <column reference> "=" <column reference>  
<where clause>   ::= WHERE <search condition>
<search condition> ::= <boolean term> | <search condition> OR <boolean term>
<boolean term>   ::= <boolean factor> | <boolean term> AND <boolean factor>
<boolean factor> ::= [ NOT ] <boolean test>
<boolean test>   ::= <predicate> | "(" <search condition> ")"
<predicate> ::= <comparison predicate> | <in predicate> | <like predicate>
	| <null predicate> | <quantified comparison predicate> | <quantified in predicate>
	| <text search predicate> | <folder predicate>
<comparison predicate> ::= <value expression> <comp op> <literal>
<comp op> ::= "=" | "<>" | "<" | ">" | "<=" | ">="
<literal> ::= <signed numeric literal> | <character string literal>
	| <datetime literal> | <boolean literal>
<in predicate>   ::= <column reference> [ NOT ] IN "(" <in value list> ")"
<in value list>  ::= <literal> [{ "," <literal> }...]
<like predicate> ::= <column reference> [ NOT ] LIKE <character string literal>
<null predicate> ::= { <column reference>
	| <multi-valued-column reference> } IS [ NOT ] NULL
<quantified comparison predicate> ::=
	<literal> "=" ANY <multi-valued-column reference>
<quantified in predicate> ::= 
	ANY <multi-valued-column reference> [ NOT ] IN "(" <in value list> ")"
<text search predicate> ::= 
	CONTAINS "(" [ <qualifier>  "," ] <quote> <text search expression> <quote> ")" 
<folder predicate> ::= { IN_FOLDER | IN_TREE } "(" [ <qualifier> "," ] <folder id> ")"
<order by clause> ::= ORDER BY <sort specification> [ { "," <sort specification> }... ]
<sort specification> ::= <column reference> [ ASC | DESC ]
<correlation name> ::= <identifier>
<table name>  ::= <identifier> !! This MUST be the name of a primary object-type.
<secondary type table name> ::= <identifier>	!! This MUST be the name of a secondary object-type.
<column name> ::= <identifier> !! This MUST be the name of a single-valued property, or an alias for a scalar output value.
<secondary type column name> ::= <identifier> !! This MUST be the name of a single-valued property for a scalar output value of a secondary type.
<multi-valued-column name> ::= <identifier>	!! This MUST be the name of a multi-valued property.
<secondary type multi-valued-column name> ::= <identifier> !! This MUST be the name of a multi-valued property of a secondary type.
<folder id> ::= <character string literal>		!! This MUST be the object identity of a folder object.
<identifier> ::=			!! As defined by queryName attribute.
<signed numeric literal>   ::=	!! As defined by SQL-92 grammar.
<character string literal> ::= 	!! As defined by SQL-92 grammar. (i.e. enclosed in single-quotes)

!! This is an independent sub-grammar for full-text search criteria.
!! It is isolatable from the query statement grammar.  (See Escaping) 
<text search expression> ::= <conjunct> [ {<space> OR <space> <conjunct>} ... ]
<conjunct> ::= <term> [ {<space> <term>} ... ]
<term>   ::= ['-'] <simple term>
<simple term> ::= <word> | <phrase>
<word> ::= <word element> {<word element>} 
<phrase> ::= <double quote> <word> {<space> <word>} <double quote> 
<quote symbol> ::= <quote><quote> | <backslash><quote> 
<word element> ::= <char> - <space char> - <backslash char> - <quote> - <double quote> 
  | <quote symbol>
<space>  ::= <space char> [ {<space char>} ... ]
<space char> ::= ' '
<backslash char> ::= <backslash><backslash>
<char>   ::= !! Any character 
<datetime literal> ::= TIMESTAMP <quote> <datetime string> <quote> 
<datetime string>  ::= YYYY-MM-DDThh:mm:ss.sss{Z | +hh:mm | -hh:mm}
<boolean literal>  ::= TRUE | FALSE | true | false
<quote> ::= "'"	!! Single-quote only, consistent with SQL-92 string literal
<double quote> ::= " !! U+0022 
<backslash> ::= \ !! U+005C 
\end{lstlisting}


\paragraph{SELECT Clause}

The SELECT clause MUST contain exactly one of the following:
\begin{itemize}
	\item A comma separated list of one or more column names. If an explicit column list is provided: A repository MUST include in its result row set all of the columns specified in the SELECT clause.
	\item * : If this token is specified, then the repository MUST return columns for ALL single-valued properties defined in the Object-Types whose virtual tables are listed in the FROM clause, and SHOULD also return all multi-valued properties. 
\end{itemize}

All column names MUST be valid "queryName" values for properties whose virtual tables are listed in the FROM clause. For each "queryName" an alias MAY be defined by adding the string " AS " and the name of the alias to the query name. Alias names MUST comply with the rules for query names. (See section \ref{dm:querynamelimitations}~\nameref{dm:querynamelimitations}.)


\paragraph{FROM Clause}

The FROM clause identifies which virtual table(s) the query will be run against, as described in the previous section. 

The FROM clause MUST contain only the "queryNames" of object-types whose queryable attribute value is TRUE. For each "queryName" an alias MAY be defined by adding the string " AS " and the name of the alias to the query name. Alias names MUST comply with the rules for query names. (See section \ref{dm:querynamelimitations}~\nameref{dm:querynamelimitations}.)


\subparagraph{Join Support}

CMIS repositories MAY support the use of SQL JOIN queries, and MUST indicate their support level using the optional capability attribute \texttt{capabilityJoin}.
\begin{itemize}
	\item If the repository's value for the \texttt{capabilityJoin} attribute is \texttt{none}, then no JOIN clauses can be used in queries.
	\item If the repository's value for the \texttt{capabilityJoin} attribute is \texttt{inneronly}, then only inner JOIN clauses can be used in queries.
	\item If the repository's value for the \texttt{capabilityJoin} attribute is \texttt{innerandouter}, then inner and/or outer JOIN clauses can be used in queries.
\end{itemize}

Only explicit joins using the "JOIN" keyword is supported. Queries MUST NOT include implicit joins as part of the WHERE clause of a CMIS query. 

CMIS queries MUST only support join operations using the "equality" predicate on single-valued properties. 


\paragraph{WHERE Clause}

This clause identifies the constraints that rows MUST satisfy to be considered a result for the query.

All column names MUST be valid "queryName" or their aliased values for properties that are defined as "queryable" in the object-type(s) whose virtual tables are listed in the FROM clause. 

Properties are defined to not support a "null" value, therefore the <null predicate> MUST be interpreted as testing the not set or set state of the specified property.


\subparagraph{Comparisons permitted in the WHERE clause}

SQL's simple comparison predicate, IN predicate, and LIKE predicate are supported, for single-valued properties only (so that SQL's semantics is preserved). Boolean conjunction (AND), disjunction (OR), and negation (NOT) of predicates are also supported.

Repositories SHOULD support the comparisons for the property types as described in the list below.  Repositories MAY support additional comparisons and operators.  Any additional operators not specified are repository-specific:\\

\begin{tabular}{|p{1in}|p{2in}|p{2in}|}
\hline
\textbf{Property Type} & \textbf{Operators supported on Type} & \textbf{Supported type of Literal in comparison}\\
\hline
String & =, <>, [NOT] LIKE & String \\
\hline
String (IN) & [NOT] IN & List of Strings\\
\hline
Decimal & =, <>, <, <=, >, >=  & Decimal\\
\hline
Decimal (IN) & [NOT] IN & List of Decimal\\
 \hline
Integer & =, <>, <, <=, >, >=  & Integer\\
\hline
Integer (IN) & [NOT] IN & List of Integer\\
\hline
Boolean &  =  & Boolean literal\\
\hline
DateTime & =, <>, <\footnotemark[1], <=\footnotemark[1], >\footnotemark[1], >=\footnotemark[1]& DateTime literal\\
\hline
DateTime (IN) & [NOT] IN & List of DateTime literals\\
\hline
ID & =, <>  & String\\
\hline
ID (IN) & [NOT] IN & List of strings\\
\hline
URI & =, <>, [NOT] LIKE  & String\\
\hline
URI (IN) & [NOT] IN & List of strings\\
\hline
\end{tabular}\\\\
\footnotetext[1]{Comparison is based on chronological before or after date}

Operations on the \texttt{SCORE()} output MUST be treated the same as decimal operations.  

When using properties in a join statement, comparison MUST be allowed on properties of the same types as defined by the table above.  Repositories MAY extend this behavior.

The ANY operation argument MUST be one of the properties found in the table above which supports equality operations.

\errataadd{
The collation rules used to evaluate comparison predicates for String properties are repository specific.
For example, a predicate of the form

\texttt{... where cmis:name LIKE 'a\%' ...}

MUST match a cmis:name starting with 'a' and MAY match a cmis:name starting with 'A'.
}

\subparagraph{Multi-valued property support (SQL-92 Extension)}

The CMIS query language includes several new non-terminals to expose semantics for querying multi-valued properties, in a way that does not alter the semantics of existing SQL-92 production rules. 

\newpage
\subparagraph{Multi-valued column references}

\textbf{BNF grammar structure:} <multi-valued-column reference>, <multi-valued-column name>\\

These are non-terminals defined for multi-valued properties whereas SQL-92's <column reference> and <column name> are retained for single-valued properties only. This is to preserve the single-value semantics of a regular "column" in the SQL-92 grammar.


%%%
\subparagraph*{Quantified comparison predicate}

The SQL-92 production rule for <quantified comparison predicate> is extended to accept a multi-valued property in place of a <table subquery>.   This operation is restricted to equality tests only.

<Table subquery> is not supported in CMIS-SQL.

The SQL-92 <quantifier> is restricted to ANY only.

The SQL-92 <row value constructor> is restricted to a literal only.\\

\begin{cmisdescription}{Example}
\begin{lstlisting}[language=SQL]
SELECT Y.CLAIM_NUM, X.PROPERTY_ADDRESS, Y.DAMAGE_ESTIMATES, Z.BAND
FROM   ( POLICY AS X JOIN CLAIMS AS Y ON X.POLICY_NUM = Y.POLICY_NUM )
       JOIN RISK AS Z ON X.cmis:objectId = Z.cmis:objectId
WHERE  ( 100000 = ANY Y.DAMAGE_ESTIMATES ) AND Z.BAND > 3
\end{lstlisting}
(Note: DAMAGE\_ESTIMATES is a multi-valued Integer property and RISK is a secondary type.)
\end{cmisdescription}


%%%
\subparagraph*{IN/ANY Predicate}

CMIS-SQL exposes a new IN predicate defined for a multi-valued property. It is modeled after the SQL-92 IN predicate, but since the entire predicate is different semantically, it has its own production rule in the BNF grammar. 

The quantifier is restricted to ANY. The predicate MUST be evaluated to TRUE if at least one of the property's values is (or, is not, if NOT is specified) among the given list of literal values. Otherwise the predicate is evaluated to FALSE.

The ANY operation argument MUST be one of the properties found in the comparison list above which supports IN operations.\\

\begin{cmisdescription}{Example 1}
\begin{lstlisting}[language=SQL]
SELECT *
FROM	 CAR_REVIEW
WHERE	 (MAKE = 'buick') OR
       (ANY FEATURES IN ('NAVIGATION SYSTEM', 'SATELLITE RADIO', 'MP3')) 
\end{lstlisting}
(Note: FEATURES is a multi-valued String property.)
\end{cmisdescription}

\begin{cmisdescription}{Example 2}
\begin{lstlisting}[language=SQL]
SELECT d.cmis:objectId, d.cmis:name, a.SPECIES
FROM   cmis:document AS d JOIN ANIMAL AS a ON d.cmis:objectId = a.cmis:objectId
WHERE  ANY a.SPECIES IN ('dog', 'cat')
\end{lstlisting}
(Note: ANIMAL is a secondary type and ANIMAL.SPECIES is a multi-valued String property.)
\end{cmisdescription}


%%%
\subparagraph{CONTAINS() predicate function (CMIS-SQL Extension)}

\textbf{BNF grammar structure:} CONTAINS ( [ <qualifier> ,] ' <text search expression> ' )\\

\begin{cmisdescription}{Usage}
This is a predicate function that encapsulates the full-text search capability that MAY be provided by a repository. See the optional capability attribute \texttt{capabilityQuery}.
\begin{itemize}
	\item If the repository's value for the \texttt{capabilityQuery} attribute is \texttt{fulltextonly}, then only queries that filter based on the full-text content of documents can be fulfilled. Specifically, only the CONTAINS() predicate function can be included in the WHERE clause.
	\item If the repository's value for the \texttt{capabilityQuery} attribute is \texttt{bothseparate}, then the repository can fulfill queries that filter EITHER on the full-text content of documents OR on their properties, but NOT if both types of filters are included in the same query.
	\item If the repository's value for the \texttt{capabilityQuery} attribute is \texttt{bothcombined}, then the repository can fulfill queries that filter on both the full-text content of documents and their properties in the same query.
\end{itemize}
\end{cmisdescription}

\begin{cmisdescription}{Inputs}
\begin{description}
	\item[<qualifier>] The value of this optional parameter MUST be the name of one of the virtual tables listed in the FROM clause for the query.
	\begin{itemize}
		\item If specified, then the predicate SHOULD only be applied to objects in the specified virtual table, but a repository MAY ignore the value of the parameter.  
		\item If not specified, applies to the single virtual table.  If the query is a join, a server SHOULD throw an exception if the qualifier is not specified.
	\end{itemize}
	\item[<text search expression>] The <text search expression> parameter MUST be a character string, specifying the full-text search criteria.
	\begin{itemize*}
		\item The Text Search Expression may be a set of terms or phrases with an optional '-' to signal negation.  A phrase is defined as a word or group of words. A group of words must be surrounded by double quotes to be considered a single phrase.
		\item Terms may contain wildcards. The wildcard '*' substitutes for zero or more characters. The wildcard '?' substitutes for exactly one character. The characters '\%' and '\_', which are wildcards in LIKE expressions are not considered wildcards in text serach terms.
		\item Terms separated by whitespace are AND'ed together.
		\item Terms separated by "OR" are OR'ed together.
		\item Implicit "AND" has higher precedence than "OR".
		\item Within a word or phrase, each (single-)quote must also be escaped by a preceding backslash '\textbackslash'. Using double single-quotes (") as a SQL-92 way to escape a literal single-quote (') character SHOULD BE supported as an allowable alternative to the double character '.
	\end{itemize*}

\end{description}
\end{cmisdescription}

\begin{cmisdescription}{Return value}
The predicate returns a Boolean value. 
\begin{itemize}
	\item The predicate MUST return TRUE if the object is considered by the repository as "relevant" with respect to the given <text search expression> parameter. 
	\item The predicate MUST return FALSE if the object is considered by the repository as not "relevant" with respect to the given <text search expression> parameter.
\end{itemize}
\end{cmisdescription}

\begin{cmisdescription}{Constraints}
\begin{itemize}
	\item At most one \texttt{CONTAINS()} function MUST be included in a single query statement.  The repository MUST throw an exception if more than one \texttt{CONTAINS()} function is found.
	\item The return value of the \texttt{CONTAINS()} function  MAY only be included conjunctively (ANDed) with the aggregate of all other predicates, if there is any, in the WHERE clause.
\end{itemize}
\end{cmisdescription}


%%%
\subparagraph{SCORE() predicate function}

\textbf{BNF grammar structure:}  SCORE ()\\

\begin{cmisdescription}{Usage}
This is a predicate function that encapsulates the full-text search capability that MAY be provided by a repository. (See previous section.)
\end{cmisdescription}

\begin{cmisdescription}{Inputs}
No inputs MUST be provided for this predicate function.
\end{cmisdescription}

\begin{cmisdescription}{Return value}
The \texttt{SCORE()} predicate function returns a decimal value in the interval [0,1].

\begin{itemize}
	\item A repository MUST return the value 0 if the object is considered by the repository as having absolutely no relevance with respect to the \texttt{CONTAINS()} function specified in the query. 
	\item A repository MUST return the value 1 if the object is considered by the repository as having absolutely complete relevance with respect to the \texttt{CONTAINS()} function specified in the query.
\end{itemize}
\end{cmisdescription}

\begin{cmisdescription}{Constraints}
\begin{itemize}
	\item The \texttt{SCORE()} function MUST only be used in queries that also include a \texttt{CONTAINS()} predicate function.
	\item The \texttt{SCORE()} function MUST only be used in the SELECT clause of a query. It MUST NOT be used in the WHERE clause or in the ORDER BY clause. 
	\item An alias column name defined for the \texttt{SCORE()} function call in the SELECT clause (i.e., \texttt{SELECT SCORE() AS column\_name ...}) may be used in the ORDER BY clause. 
	\item If \texttt{SCORE()} is included in the SELECT clause and an alias column name is not provided, then a query name of \texttt{SEARCH\_SCORE} is used for the query output, and the property definition id is repository-specific.
\end{itemize}
\end{cmisdescription}


%%%
\subparagraph{IN\_FOLDER() predicate function}

\textbf{BNF grammar structure:}  IN\_FOLDER( [ <qualifier>, ] <folder id> )\\

\begin{cmisdescription}{Usage}
This is a predicate function that tests whether or not a candidate object is a child-object of the folder object identified by the given <folder id>.
\end{cmisdescription}

\begin{cmisdescription}{Inputs}
\begin{description}
	\item[<qualifier>] The value of this optional parameter MUST be the name of one of the virtual tables listed in the FROM clause for the query.
	\begin{itemize}
		\item If specified, then the predicate SHOULD only be applied to objects in the specified virtual table, but a repository MAY ignore the value of the parameter.  
		\item If the query is a join, a server SHOULD throw an exception if the qualifier is not specified. 
	\end{itemize}
	\item[<folder id>] The value of this parameter MUST be the id of a folder object in the repository.
\end{description}
\end{cmisdescription}

\begin{cmisdescription}{Return value}
The predicate returns a Boolean value. 
\begin{itemize}
	\item The predicate function MUST return TRUE if the object is a child-object of the folder specified by <folder id>.
	\item The predicate function MUST return FALSE if the object is a NOT a child-object of the folder specified by <folder id>.
\end{itemize}
\end{cmisdescription}


%%%
\subparagraph{IN\_TREE() predicate function}

\textbf{BNF grammar structure:}   IN\_TREE( [ <qualifier>, ] <folder id> )\\

\begin{cmisdescription}{Usage}
This is a predicate function that tests whether or not a candidate object is a descendant-object of the folder object identified by the given <folder id>.
\end{cmisdescription}

\begin{cmisdescription}{Inputs}
\begin{description}
	\item[<qualifier>] The value of this optional parameter MUST be the name of one of the virtual tables listed in the FROM clause for the query.
	\begin{itemize} 
		\item If specified, then the predicate SHOULD only be applied to objects in the specified virtual table, but a repository MAY ignore the value of the parameter.  
		\item If the query is a join, a server SHOULD throw an exception if the qualifier is not specified. 
	\end{itemize}
	\item[<folder id>] The value of this parameter MUST be the id of a folder object in the repository.
\end{description}
\end{cmisdescription}

\begin{cmisdescription}{Return value}
The predicate returns a Boolean value. 
\begin{itemize}
	\item The predicate function MUST return TRUE if the object is a descendant-object of the folder specified by <folder id>.
	\item The predicate function MUST return FALSE if the object is a NOT a descendant-object of the folder specified by <folder id>.
\end{itemize}
\end{cmisdescription}


\paragraph{ORDER BY Clause}

This clause MUST contain a comma separated list of one or more column names. 

All column names referenced in this clause MUST be valid "queryName" or their aliased values for properties defined as orderable in the object-type(s) whose virtual tables are listed in the FROM clause. 

Only columns in the SELECT clause MAY be in the ORDER BY clause.

Collation rules for the ORDER BY clause are repository specific.


\subsubsection{Escaping}

Character escaping for character strings differs from SQL-92's escaping. A repository MUST support the escaping of certain literal characters in a character string, or in a text search expression, using a backslash character (\textbackslash) in the following manner.  For a <character string literal>, which MUST BE a string enclosed in single-quotes according to the SQL-92 grammar, any occurrence of the single-quote character (') and the escape character (\textbackslash) in the string MUST BE escaped. This applies to <folder id>, which is a <character string literal>. Furthermore, when a <character string literal> is used in a LIKE predicate, any occurrence of the percent character (\%) and the underscore character (\_) in the string as a literal MUST BE escaped also. Therefore, within a quoted string in a query:
\begin{itemize}
	\item The double character \textbackslash' represents a literal single-quote (') character.
	\item The double character \textbackslash\textbackslash{} represents a literal backslash (\textbackslash) character.
	\item Within a LIKE string, the double characters \textbackslash\% and \textbackslash\_ represent a literal percent (\%) character and a literal underscore (\_) character respectively.
	\item Within a CONTAINS text search expression, the double characters \textbackslash * and \textbackslash ? represent a literal asterisk (*) character and a literal question mark (?) character respectively.
	\item All other instances of a backslash (\textbackslash) character are errors.
\end{itemize}
Using double single-quotes ('') as a SQL-92 way to escape a literal single-quote (') character SHOULD BE supported as an allowable alternative to the double character \textbackslash'.

For a <text search expression>, a second-level character escaping is required so that the <text search expression> sub-grammar is isolatable from the query statement-level grammar. When a text search expression is composed for a query according to the <text search expression> sub-grammar, any occurrence of the following four characters in the expression as a literal character MUST BE escaped: double-quote ("), hyphen (-), single-quote ('), and the escape character (\textbackslash). Then, before this expression is enclosed in single-quotes and inserted into a CONTAINS() predicate, the query statement-level escaping rules described in the above MUST BE applied. This two-level character escaping allows a query statement parser, using statement-level escaping rules, to correctly extract a <text search expression> as a character string literal independent of the <text search expression> sub-grammar. This extracted <text search expression> can then be correctly interpreted by a full-text search parser independent of the query-statement grammar, using second-level escaping rules. Since the <text search expression> sub-grammar is isolated from the SQL-92 grammar, double single-quotes is not a valid way to escape a literal single-quote character for second-level character escaping.

An <identifier> in a query statement MUST conform to the SQL-92 identifier syntax, and MUST NOT require character escaping.\\

\textbf{Example:}\\
A query statement that contains a full-text search for the literal string "John'sPresentation-Version2" may be composed as:\\

\texttt{SELECT ... FROM ... WHERE ... CONTAINS('John\textbackslash\textbackslash\textbackslash'sPresentation\textbackslash\textbackslash-Version2') ...}\\

A query parser extracts from this statement the text search expression "John\textbackslash'sPresentation\textbackslash-Version2" as a character string literal, and passes it to a text-search parser, which interprets it as a single-word full-text search criteria: John'sPresentation-Version2.

