SQL++ Server Differences

Differences between Couchbase Server SQL++ and Couchbase Lite SQL++

Important

N1QL is Couchbase’s implementation of the developing SQL++ standard. As such the terms N1QL and SQL++ are used interchangeably in 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. This table compares Couchbase Server and Mobile instances:

Table 1. SQL++ Query Comparison

Feature	SQL++ for Couchbase Server	SQL++ for Mobile
Scopes and Collections	`SELECT * FROM travel-sample.inventory.airport`	`SELECT * FROM inventory.airport`
Scopes and Collections	`SELECT * FROM travel-sample.inventory.airport`	`SELECT * FROM inventory.airport`
`USE KEYS`	`SELECT fname, email FROM tutorial USE KEYS ["dave", "ian"];`	`SELECT fname, email FROM tutorial WHERE meta().id IN ("dave", "ian");`
`ON KEYS`	SELECT * 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;`
`ON KEY`	SELECT * 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;`
`NEST`	SELECT * 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 NEST`	`SELECT * 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
`ARRAY`	`ARRAY i FOR i IN [1, 2] END`	`(SELECT VALUE i FROM [1, 2] AS i)`
`ARRAY FIRST`	`FIRST v FOR v IN arr`	`arr[0]`
`LIMIT l OFFSET o`	Does not allow `OFFSET` without `LIMIT`	Allows `OFFSET` without `LIMIT`
`UNION`, `INTERSECT`, and `EXCEPT`	All three are supported (with `ALL` and `DISTINCT` variants)	Not supported
`OUTER JOIN`	Both `LEFT` and `RIGHT OUTER JOIN` supported	Only `LEFT OUTER JOIN` supported (and necessary for query expressability)
`<`, `<=`, `=`, etc. operators	Can compare either complex values or scalar values	Only scalar values may be compared
`ORDER BY`	Result sequencing is based on specific rules described in SQL++ (server) OrderBy 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 DISTINCT`	Supported	SELECT DISTINCT VALUE is supported when the returned values are scalars
`CREATE INDEX`	Supported	Not Supported
`INSERT`/`UPSERT`/`DELETE`	Supported	Not Supported

Boolean Logic Rules

SQL++ for Couchbase Server	SQL++ for Mobile
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’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	SQL++ for Mobile			SQL++ for Server
Operand a	b	a AND b	a OR b	b	a AND b	a OR b
`TRUE`	`TRUE`	`TRUE`	`TRUE`	-	-	-
	`FALSE`	`FALSE`	`TRUE`	-	-	-
	`NULL`	`FALSE`	`TRUE`	-	`NULL`	-
	`MISSING`	`MISSING`	`TRUE`	-	-	-
`FALSE`	`TRUE`	`FALSE`	`TRUE`	-	-	-
	`FALSE`	`FALSE`	`FALSE`	-	-	-
	`NULL`	`FALSE`	`FALSE`	-	-	`NULL`
	`MISSING`	`FALSE`	`MISSING`	-	-	-
`NULL`	`TRUE`	`FALSE`	`TRUE`	-	`NULL`	-
	`FALSE`	`FALSE`	`FALSE`	-	-	`NULL`
	`NULL`	`FALSE`	`FALSE`	-	`NULL`	`NULL`
	`MISSING`	`FALSE`	`MISSING`	-	`MISSING`	`NULL`
`MISSING`	`TRUE`	`MISSING`	`TRUE`	-	-	-
	`FALSE`	`FALSE`	`MISSING`	-	-	-
	`NULL`	`FALSE`	`MISSING`	-	`MISSING`	`NULL`
	`MISSING`	`MISSING`	`MISSING`	-	-	-

CRUD Operations

SQL++ for Mobile only supports Read or Query operations.

SQL++ for Server fully supports CRUD operation.

Functions

Division Operator

SQL++ for Server	SQL++ 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 Server	SQL++ 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).