Please use the form below to provide your feedback. Because your feedback is valuable to us, the information you submit in this form is recorded in our issue tracking system (JIRA), which is publicly available. You can track the status of your feedback using the ticket number displayed in the dialog once you submit the form.

SQL++ Differences

      +

      Description — Differences between Couchbase Server SQL++ and Couchbase Lite N1QL
      Related Content — Predictive Queries | Live Queries | Indexing

      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

      SQL++ 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

      ARRAY FIRST arr

      arr[0]

      LIMIT l OFFSET o

      Allows 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

      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).

      Related Content

      How to . . .
      Learn more . . .
      Dive Deeper . . .

      Mobile Forum | Blog | Tutorials