MarkLogic Server 11.0 Product Documentation

SQL Data Modeling Guide — Chapter 7

Supported SQL Statements, Functions and Types

This section describes the SQL statements and functions supported in MarkLogic. The topics are:

• Supported Statements
• Supported Functions
• Supported Types

System Tables

Data dictionaries consists of a series of tables that are created in the SYS schema. These system tables are listed in the table below.

To see the full contents of a system table, do a select *. For example:

select * from sys_tables

System Columns __content and __docid

Each view has two system columns:

The __docid and __content system columns are preceded by two underscores.

For example: The following returns the fragment ID for each document that matches the employees view:

select __docid from employees

The following returns the contents of each document that matches the employees view:

select __content from employees

Calling Built-in Functions from SQL

You can call MarkLogic built-in functions from inside a SELECT statement, as long as the parameter types match the column types. You cannot call aggregate functions from SQL.

The following are some examples of the use of MarkLogic functions in SQl statements:

Provide the version of MarkLogic Server and hardware information:

select xdmp_version(), xdmp_platform(), xdmp_architecture()

Trace the performance of a query:

select xdmp_elapsed_time, t1.this, t2.that from t1, t2
    where t1.key=t2.ref group by t1.this

Do some trigonometry:

select math_cos(EmployeeID) from employees

Do some geospatial:

select cts_distance(town.center, building.location) from town, building

Return the first five values of the FirstName column, starting with the third character:

select fn_substring(FirstName,3) from employees limit 5 

ORDER BY Keyword

MarkLogic supports the ORDER BY keyword supported in SQL standard 2003, rather than sql92.

When you include an ORDER BY in SQL queries, such as ORDER BY column_A desc nulls first, you are also able to specify where to put the nulls, either at the beginning (NULLS FIRST) or at the bottom (NULLS LAST). If you don't specify the nulls ordering, the default behavior is NULLS LAST, putting all the nulls at the bottom, which is often the most efficient option.

If you want the default behavior to place nulls as the smallest value (the default before 9.0-9) enable the trace event:

Optic Nulls Smallest On

GROUPING SETS Keyword

MarkLogic supports the GROUPING SETS keyword supported in SQL99.

GROUP BY GROUPING SETS allows the calculation of multiple group bys in a single pass, by allowing you to specify multiple sets of grouping columns. For example:

SELECT    A.id,B.name,COUNT(*)
FROM     Table_1 AS A, Table_2 as B
WHERE    A.number = B.number
GROUP BY GROUPING SETS (A.id,(A.id,B.name));

In this example, the GROUP BY clause determines the first requirement - groups of IDs - by grouping the A.ID values from the TABLE_1 Table. It then determines the second requirement - number of IDs by ID and NAME - by grouping the A.ID values from TABLE_1 with the B.NAME values from TABLE_2.

A <grouping specification> of () (called grand total in the SQL Standard) is equivalent to grouping the entire result Table.

CUBE Keyword

The CUBE keyword is a simpler way to specify particular sets of grouping columns.

Here are two examples comparing the CUBE and GROUPING SETS keywords:

Using the CUBE keyword:

select A, B, C, count(*) from Table
group by cube(A, B, C)

Using the GROUPING SETS keyword:

select A, B, C, count(*) from Table
group by grouping sets(

  (A, B, C),
  (A, B),
  (A, C),
  (B, C),
  (A),
  (B),
  (C),
  ()
 )

ROLLUP Keyword

The ROLLUP keyword is a simpler way to specify particular sets of grouping columns. Here are two examples comparing the ROLLUP and GROUPING SETS keywords:

Using the ROLLUP keyword:

select A, B, C, count(*) from Table
group by rollup(A, B, C)

Using the GROUPING SETS keyword:

select A, B, C, count(*) from Table
group by grouping sets(
  (A, B, C),
  (A, B),
  (A),
  ()
)

GROUPING() Function

The grouping() function is an aggregate function that accepts a single column as a parameter and returns 1 if the column is aggregated (not a grouping column), and 0 otherwise. This aggregate function can be used as a reliable way to determine which grouping set the row represents.

MATCH Operator

The MATCH operates differently on range views and template views. You can MATCH column names when using range views, but not template views. You can MATCH on tables created by both range and template views.

When the MATCH operator is used with range views, column names are bound to their corresponding index references and searchable fields are bound to their field names. When the MATCH operator is applied to individual columns, all names are unbound, as it doesn't make sense to constrain searches against one index to the values of another. These queries are executed in unfiltered mode.

The search expression following the MATCH operator must be contained inside single quotes.

Field names, like view and schema names, are treated as case-insensitive for the purposes of duplicate detection and lookup.

SELECT * FROM employees WHERE employees MATCH 'Manager'

SELECT * FROM employees WHERE employees MATCH 'J*'

SELECT employeeid, firstname, lastname, position FROM employees 
    WHERE employees MATCH 'Steve OR John OR Goodall'

SELECT employeeid, firstname, lastname, position FROM employees 
    WHERE employees MATCH 'Steve AND Manager'

SELECT * from employees WHERE firstname MATCH 'John OR Jane' 
    AND lastname MATCH 'Lead'

SELECT * FROM employees WHERE employees MATCH 'position:Manager'

SELECT firstname, lastname FROM employees WHERE employees 
    MATCH 'employeeid LE 3'

SELECT employeeid, firstname, lastname, position FROM employees 
    WHERE firstname MATCH 'Steve OR John OR Goodall'

SELECT * FROM employees WHERE employees MATCH 'firstname:J*'

SET/SHOW Statements

The MarkLogic ODBC driver supports Postgres SET and SHOW run-time configuration parameters, as well as some parameters that are specific to MarkLogic Server. These parameters only work when accessing MarkLogic through an ODBC driver, as is the case with mlsql. They do not work when accessing MarkLogic through xdmp:sql or the Query Console.

For details on the Postgres parameters, see:

• http://www.postgresql.org/docs/9.1/static/sql-set.html
• http://www.postgresql.org/docs/9.1/static/sql-show.html

All SET parameters are good for the duration of the SQL session in which they are set. Some parameters are read-only and can only be specified by the SHOW statement. These are described in Read-only SHOW Parameters.

All SET string values must be specified in single quotes (SET parameter 'value').

SET timezone 'UTC'

SET statement_timeout 5000

SET lc_messages 'en_US'

SET lc_collate 'en_US.utf8'

SET lc_collation 'http://marklogic.com/collation/en_US/S1/MO'

set lc_numeric 'de_DE'

set lc_time 'en_US.UTF-8'

SET DateType 'ISO'

SET extra_float_digits 2

SET client_encoding 'UTF8'

SET NAMES 'UTF8'

SET coordinate_system 'wgs84/double'

SET search_path 'main'

SET mls_default_xquery '1.0-ml'

SET mls_redundant_check 1; 
SELECT title, year FROM songs WHERE year=1991;

Read-only SHOW Parameters

The following parameters can be obtained via the SHOW statement but they are read-only and cannot be set via the SET statement.

Best Practices and Performance Considerations

MarkLogic SQL does not have a default/implicit limit for the rows returned. Queries that return large result sets, such as tens of thousands of rows, may perform poorly. Should you experience performance problems it is a best practice to page the results using the LIMIT statement.