Skip to main content

SQL++ for Mobile and Server Differences

Description — Differences between SQL++ for Mobile and SQL++ for Server
Related Content — SQL++ for Mobile | Indexes
info

N1QL is Couchbase's implementation of the developing SQL++ standard. As such the terms N1QL and SQL++ are used interchangeably in all Couchbase documentation unless explicitly stated otherwise.

There are several minor but notable behavior differences between SQL++ for Mobile queries and SQL++ for Server, as shown in Table 1.

In some instances, if required, you can force SQL++ for Mobile to work in the same way as SQL++ for Server. These instances are noted in the content below.

Table 1. SQL++ Query Comparison
FeatureSQL++ for ServerSQL++ for Mobile
USE KEYSSELECT fname, email FROM tutorial USE KEYS ('dave', 'ian');SELECT fname, email FROM tutorial WHERE META().id IN ('dave', 'ian');
ON KEYSSELECT * FROM user u JOIN orders o ON KEYS ARRAY s.order_id FOR s IN u.order_history END;SELECT * FROM user u, u.order_history s JOIN orders o ON s.order_id = Meta(o).id;
USE KEYSELECT * FROM user u JOIN orders o ON KEY o.user_id FOR u;SELECT * FROM user u JOIN orders o ON META(u).id = o.user_id;
NESTSELECT * FROM user u NEST orders orders ON KEYS ARRAY s.order_id FOR s IN u.order_history END;NEST/UNNEST not supported
LEFT OUTER NESTSELECT * FROM user u LEFT OUTER NEST orders orders ON KEYS ARRAY s.order_id FOR s IN u.order_history END;NEST/UNNEST not supported
ARRAYARRAY i FOR i IN [1, 2] END(SELECT VALUE i FROM [1, 2] AS i)
ARRAY FIRSTARRAY FIRST arrarr[0]
LIMIT l OFFSET oAllows OFFSET without LIMITAllows OFFSET without LIMIT
UNION, INTERSECT, EXCEPTAll three are supported (with ALL and DISTINCT variants).Not supported
OUTER JOINBoth LEFT and RIGHT OUTER JOIN are supported.Only LEFT OUTER JOIN supported (and necessary for query expressability).
<, <=, =, etc. operatorsCan compare either complex values or scalar values.Only scalar values may be compared.
ORDER BYResult sequencing is based on specific rules described in SQL++ for Server ORDER BY clause.Result sequencing is based on the SQLite ordering described in SQLite select overview.

The ordering of Dictionary and Array objects is based on binary ordering.
SELECT DISTINGCTSupportedSELECT DISTINCT VALUE is supported when the returned values are scalars.
CREATE INDEXSupportedNot Supported
INSERT, UPSERT, DELETESupportedNot Supported

Boolean Logic Rules

SQL++ for Server

Couchbase Server operates in the same way as Couchbase Lite, except:

  • MISSING, NULL and FALSE are FALSE
  • Numbers 0 is FALSE
  • Empty strings, arrays, and objects are FALSE
  • All other values are TRUE

You can choose to use Couchbase Server's SQL++ rules by using the TOBOOLEAN(expr) function to convert a value to its boolean value.

SQL++ for Mobile

SQL++ for Mobile's boolean logic rules are based on SQLite's, so:

  • TRUE is TRUE, and FALSE is FALSE
  • Numbers 0 or 0.0 are FALSE
  • Arrays and dictionaries are FALSE
  • String and Blob are TRUE if the values are casted as a non-zero or FALSE if the values are casted as 0 or 0.0 — see: SQLITE's CAST and Boolean expressions for more details.
  • NULL is FALSE
  • MISSING is MISSING

Logical Operations

In SQL++ for Mobile logical operations will return one of three possible values; TRUE, FALSE, or MISSING.

Logical operations with the MISSING value could result in TRUE or FALSE if the result can be determined regardless of the missing value, otherwise the result will be MISSING.

In SQL++ for Mobile — unlike SQL++ for Server — NULL is implicitly converted to FALSE before evaluating logical operations. Table 2 summarizes the result of logical operations with different operand values and also shows where the Couchbase Server behavior differs.

Table 2. Logical Operations Comparison
Operand
a
Operand
b
SQL ++ for Mobile
a AND b
SQL ++ for Mobile
a OR b
SQL ++ for Server
a AND b
SQL ++ for Server
a OR b
TRUETRUETRUETRUE--
FALSEFALSETRUE--
NULLFALSETRUENULL-
MISSINGMISSINGTRUE--
FALSETRUEFALSETRUE--
FALSEFALSEFALSE--
NULLFALSEFALSE-NULL
MISSINGFALSEMISSING--
NULLTRUEFALSETRUENULL-
FALSEFALSEFALSE-NULL
NULLFALSEFALSENULLNULL
MISSINGFALSEMISSINGMISSINGNULL
MISSINGTRUEMISSINGTRUE--
FALSEFALSEMISSING--
NULLFALSEMISSINGMISSINGNULL
MISSINGMISSINGMISSING--

CRUD Operations

  • SQL++ for Mobile only supports Read or Query operations.
  • SQL++ for Server fully supports CRUD operation.

Functions

Division Operator

SQL ++ for ServerSQL++ for Mobile
SQL++ for Server always performs float division regardless of the types of the operands.

You can force this behavior in SQL++ for Mobile by using the DIV(x, y) function.
The operand types determine the division operation performed.

If both are integers, integer division is used.

If one is a floating number, then float division is used.

Round Function

SQL ++ for ServerSQL++ for Mobile
SQL++ for Server ROUND() uses the Rounding to Nearest Even convention (for example, ROUND(1.85) returns 1.8).

You can force this behavior in Couchbase Lite by using the ROUND_EVEN() function.
The ROUND() function returns a value to the given number of integer digits to the right of the decimal point (left if digits is negative).

Digits are 0 if not given.

Midpoint values are handled using the Rounding Away From Zero convention, which rounds them to the next number away from zero (for example, ROUND(1.85) returns 1.9).