This section shows the definitions of types specifying the value constraints for fields and query operations.
Here are shown the definitions of primitive types which cannot be represented by a combination of any other types.
Represents the values: TRUE or FALSE.
Represents a sequence of zero or more Unicode code points (characters) excluding the NULL character (U+0000). Refer to GridDB Features Reference for the upper limit size.
Represents integer values as follows:
Represents the IEEE754 floating point numbers. The following types are available depending on the precision.
Note. In principle, arithmetic precision conforms to the IEEE754 specifications; however, it might vary depending on the runtime environment.
Represents the combination of a date consisting of year, month and day, and a time consisting of hour, minute and second. Refer to the Annex Range of values for the display range.
Represents the spatial structure. Refer to GridDB Features Reference for the upper limit size. It does not support non-numeric numbers (NAN) and positive and negative infinity (INF, -INF) as the number of coordinates represented by each structure. In addition, it is capable of storing SRID (Spatial Reference System Identifier) as an integer value, but does not support coordinate range limit by the coordinate system represented by the SRID and the coordinate conversion by the conversion of SRID.
Represents binary data, such as image and sound. Refer to GridDB Features Reference for the upper limit size.
Here are shown the definitions of types which can be represented by a combination of primitive types.
Represent a sequence of values. The following types are available for array values. The length of an array indicates the number of array elements. Refer to GridDB Features Reference for the upper limit size. The element of array cannot be set to NULL.
TQL supports a query corresponding to the SQL SELECT statement which is required to select data to be fetched, deleted or updated. It does not support other than a selection query, such as manipulation of selected data, management of data structure and transaction processing.
All queries are expressed by the syntax below:
[EXPLAIN [ANALYZE]] SELECT (select expression) [FROM (Collection or TimeSeries name)] [WHERE (conditional expression)] ORDER BY (Column name) [ASC|DESC] [, (Column name) [ASC|DESC]]* [LIMIT (number) [OFFSET (number)]]
A SELECT statement is used to narrow down Rows in a Collection or TimeSeries specified in the FROM clause according to the conditional expression in the WHERE clause and process the result set according to the select expression specifying target Column(s), a calculation formula, etc.
If a target Collection or TimeSeries is already specified, you need to omit the FROM clause or specify the same name as the target in the FROM clause. You should note that the FROM clause is case-insensitive. If the WHERE clause is omitted, all the Rows of a target Collection or TimeSeries are selected.
You can place EXPLAIN or EXPLAIN ANALYZE before a SELECT statement to obtain execution plan information and analysis information on execution results in relation to the SELECT statement. See the later section for more information.
Unlike SQL, you cannot extract only specific Column(s) except for aggregation operations. Additionally, clauses corresponding to the following are not available.
ASCII characters in the keywords of the basic syntax and the names of functions, operators and enumeration constants described in the later sections can be written in lower case.
This section shows the definitions of the syntax of conditional expressions used in the WHERE clause and the operators and functions available in a conditional expression. When NULL is included in operator or function, it returns NULL unless otherwise noted.
A value can be either a constant literal or a field of a specified Column in a Row under operation.
A literal value can be either of the following:
To specify a field value held by a row under evaluation, either describe directly the corresponding column name or describe the column name enclosed within quotation marks “””. By enclosing within quotation marks, columns with the same name as key words such as “SELECT” and “WHERE” can also be handled. You should note that Column names are case-insensitive. Additionally, you cannot join a Column name and a Collection or TimeSeries name by “.” etc.
Operators are evaluated in the order below: Operators with equal precedence are evaluated in the left-to-right order in which they appear.
Round parentheses can be used to override the order of precedence and force some parts of an expression to be evaluated before others.
A combination of left and right operand types can be only Boolean-Boolean, String-String, numeric-numeric, or time-time. With regard to numeric types, if left and right operand types are different in precision, the type with a lower precision or a narrower representable value range will be converted to the one with a higher precision or a wider range. Magnitude comparison cannot be made between Boolean and Boolean types or String and String types.
These operators produce a Boolean value representing the result of comparison; namely, “Equal to,” “Greater than or equal to,” “Greater than,” “Less than or equal to,” “Less than” and “Not equal to,” respectively. However if either of operands is NULL, it returns NULL. In principle, arithmetic precision conforms to the IEEE754 specifications; however, it might vary depending on the runtime environment. GridDB assumes that NaN is equal to NaN and that NaN is greater than any other values.
These operators obtain the Boolean results of the comparison operations in which the value on the left side is equal to or not equal to NULL. The right side must be NULL.
These operators produce the result of negation, logical product, logical sum and exclusive logical sum, respectively. Operands of logical operations must be Boolean or NULL.
If an operand is TRUE, it returns FALSE. If an operand is FALSE, it returns TRUE. If an operand is NULL, it returns NULL.
If both operands are TRUE, it returns TRUE. If either of the operands is FALSE, it returns FALSE. Otherwise, it returns NULL.
If both operands are FALSE, it returns FALSE. If either of the operands is TRUE, it returns TRUE. Otherwise, it returns NULL.
If both operands are TRUE or FALSE, it returns FALSE. If either of the operands is NULL, it returns NULL. Otherwise, it returns TRUE.
In addition, a short evaluation (or minimum evaluation) is carried out in an AND or OR operation. That is, if the evaluation can be confirmed with the formula described first, the evaluation of the formula described later will not be conducted. For example,
WHERE A=1 AND B=1
In this case, if A is 1, then B is deemed to be equal to 1, and if A is not 1, then the evaluation of B=1 need not be carried out.
Returns the length of the specified string.
Returns a new string obtained by concatenating all given strings. If NULL is included in the input, NULL will be ignored and the rest will be concatenated.
Checks if the whole target string matches the specified pattern. The string matching is case-sensitive. It returns TRUE only if the matching is successful.
The following wildcards can be used for specifying a pattern.
For example, the expression below returns TRUE for “RDB” and “RDBMS” but FALSE for “ORDB” and “DBMS”.
column LIKE '_DB%'
Wildcards can be placed at any arbitrary position(s) within a pattern. FALSE is always returned if an empty string is specified as a pattern.
In order to search a wildcard character itself, specify an escape character in the ESCAPE clause. For example, the expression below returns TRUE for “10%” but FALSE for “10$%.”
column LIKE '10$%' ESCAPE '$'
The escape character must be a single character.
You can specify either a Column name or a string literal as “str,” “pattern” and “esc.”
Extracts parts of a string.
“start” is the position where to start extraction of characters. The first character is at index 1. “length” is the number of characters to extract (0 or positive number). If “length” is omitted, it extracts all the characters from the “start” position to the end of the string. If “start” or “length” is out of index, it returns an empty string. You should note that “start” must be a positive number and “length” must be 0 or a positive number.
Convert ASCII alphabetical characters within a string to upper-case and lower-case characters, respectively.
Perform arithmetic operations; addition, negation, subtraction, multiplication, division and remainder, respectively. You should note that the remainder operator does not support floating point numbers. In floating point division, if a number other than 0 is divided by 0, the result is INF, and if 0 is divided by 0, the result is NaN. The operations on INF or NaN conform to IEEE754.
Round a floating point number “num” off to the closest integer, down to the greatest integer that is less than “num,” and up to the least integer that is greater than or equal to “num,” respectively. The result is DOUBLE. They round a number away from 0, toward negative infinity, and toward positive infinity, respectively. Accordingly, if “num” is negative, the results are equal to -ROUND(abs(num)), -CEIL(abs(num)), and -FLOOR(abs(num)), respectively, where abs(num) is the absolute value of “num.”
The table below shows operation examples.
| Value | ROUND | FLOOR | CEILING |
|---|---|---|---|
| 1.34 | 1.0 | 1.0 | 2.0 |
| 3.67 | 4.0 | 3.0 | 4.0 |
| -0.23 | 0.0 | -1.0 | -0.0 |
| -3.89 | -4.0 | -4.0 | -3.0 |
If you specify +0, -0, an integer, NaN, INF, or -INF as a parameter, they return the same value as the specified value.
Returns the current date and time. It returns the constant result during a single query transaction.
Example) Searching for data with the date value of time-type column before the current time
SELECT * WHERE date < NOW()
Returns a TIMESTAMP result converted from a timestamp expression.
The following format based on the Western calendar is only supported as a timestamp expression.
Example) Searching for data with the date value of time-type column newer than the time “December 30, 2018 10:15:30 (UTC)”
SELECT * WHERE date > TIMESTAMP('2018-12-30T10:15:30.000Z')
| The following format based on the Western calendar is only supported as a timestamp expression. Time zone strings (Z | ±hh:mm | ±hhmm) are interpreted. |
YYYY-MM-DDThh:mm:ss.SSSZ
“.SSS” can be omitted. In the format above, alphabetical characters stand for decimal integers, as follows:
Refer to the Annex Range of values for the display range.
Returns the result of adding the specified number (“duration”) of designated intervals (first parameter) to the specified “timestamp” value. Specify a numeric value as “duration.” If a negative value is specified as “duration,” it returns the time earlier than the specified time. The current version uses the UTC timezone for calculation.
For the first argument time_unit, specify one of the following identifiers.
| YEAR | MONTH | DAY | HOUR | MINUTE | SECOND | MILLISECOND |
Example) Searching for data with the date value of time-type column one hour before the current time
SELECT * WHERE date < TIMESTAMPADD(HOUR, NOW(), -1)
Return the difference between two given timestamps for the designate interval (first parameter). The value returned is a numeric value. The current version uses the UTC timezone for calculation.
For the first argument time_unit, specify one of the following identifiers.
| YEAR | MONTH | DAY | HOUR | MINUTE | SECOND | MILLISECOND |
Example) Selecting tickets with three or more days of validity period among a list of tickets (tickets).
SELECT * WHERE TIMESTAMPDIFF(DAY, expired, issued) >= 3
Example) Search for data where the difference between time column end and start (=end-start) is less than 10 hours
SELECT * WHERE TIMESTAMPDIFF(HOUR, end, start) < 10
Convert to a TIMESTAMP corresponding to the num in milliseconds of the time 1970-01-01T00:00:00Z. An error occurs if num is a floating-point number. In addition, an error occurs if the conversion results cannot be expressed as time data e.g. negative values or extremely large values, etc. As a result, if a query using this function on a numerical column is issued, an error occurs if the conversion results contain values that cannot be expressed as time data in the column value. For example, an error occurs if the container contains a row with a num=-1 in the query below.
SELECT * WHERE TO_TIMESTAMP_MS(num) > TIMESTAMP('2011-01-01T00:00:00Z')
In such a situation, avoid such errors by using the TO_EPOCHMS function as shown below to evaluate only the value in which the converted numerical data serves as the range of the time series data.
SELECT * WHERE num < TO_EPOCH_MS(TIMESTAMP('9999-12-31T23:59:59.999Z'))
AND num >= 0
AND TO_TIMESTAMP_MS(num) > TIMESTAMP('2011-01-01T00:00:00Z')
For time series value specified in a timestamp, the time passed in milliseconds starting from the time 1970-01-01T00:00:00Z is converted to a LONG value. An error occurs if a value which is not a time series data is specified. This function is the inverted conversion of the TO_TIMESTAMP_MS function.
Example) Comparing column value num of elapsed time from 1970-01-01T00:00:00Z with the current time
SELECT * WHERE num > TO_EPOCH_MS(NOW())
Returns the length of the specified array.
Extracts an array element at the specified position. An array of length 1 or more must be specified. The parameter “n” is the number indicating the element position, starting from 0. If “n” is Floating point type or a negative value or longer than the length of “array,” or if the length of “array” is 0, an error is returned. Accordingly, if different lengths of arrays are contained in a Collection, and if a query specifying an array element as below cannot extract the specified array element, an error can be returned.
SELECT * FROM arrays WHERE ELEMENT(1, array) = 1
Rewrite such a query to prevent the ELEMENT function from being evaluated, as below:
SELECT * FROM arrays WHERE ARRAY_LENGTH(array)>= 1 AND ELEMENT(1, array) = 1
Spatial-Type data are widely used in the GIS field, such as OpenGIS. In TQL, it manages the two or three-dimensional spatial structure, and provides generating function and judgment function.
Generates a spatial-type data from a string of WKT representation.
The WKT is a standard for representing the spatial structure as a string. In TQL, it supports only the following structure.
However, it can not include the infinity or non-numeric numbers as a number that make up the coordinates. In addition, it can not give unsupported spatial structures.
In the case of the rectangle having a diagonal line connecting points (0, 0) and (10, 10) on the two-dimensional space will be expressed as follows.
POLYGON((0 0,10 0,10 10,0 10,0 0))
In addition, it can express the value that does not correspond with a particular spatial range called the empty geometry for each type of spatial structure data type. Express “EMPTY” instead of the coordinate values as the following example.
LINESTRING(EMPTY)
In addition, you can specify the SRID by describing the integer after “;”. In the following example, it indicates the rectangle is in the coordinate system of SRID:4326.
POLYGON((0 0,10 0,10 10,0 10,0 0);4326)
However, it does not correspond to coordinate range limit by the coordinate system represented by this SRID or the coordinate transformation by changing the SRID. If you do not specify a SRID, it is set to -1 as an invalid SRID.
Generate a rectangle having a diagonal line connecting points p1 and p2 on the two-dimensional space. If p1 is equal to p2 or any of the y-coordinate or x-coordinate of p1 and p2 is equal, the result will be an error because it can not form a rectangle.
Generate a rectangle having a diagonal line connecting points (x1, y1) and (x2, y2) on the two-dimensional space. If x1 is equal to x2 or y1 is equal to y2, the result will be an error because it can not form a rectangle.
Generate a rectangular parallelepiped having a diagonal line connecting points p1 and p2 in the three-dimensional space. If p1 is equal to p2 or any of the coordinate y, x and z of p1 and p2 is equal, the result will be an error because it can not form a rectangular parallelepiped.
Generate a rectangular parallelepiped having a diagonal line connecting points (x1, y1, z1) and (x2, y2, z2) in the three-dimensional space. If x1 is equal to x2, y1 is equal to y2 or z1 is equal to z2, the result will be an error because it can not form a rectangle parallelepiped.
Generate a plane from the normal vector v and point p0 in the three-dimensional space. If the length of v is zero, it generate an undefined plane. This undefined plane does not intersect with any kind of spatial structure.
Generate a cone from the angle deg of the axis and the bus, point p0 and vector v of the axis in the three-dimensional space. Units of angle deg is a degree. An error occurs, if the length of v is 0. Even if deg is not in the range from 0 to 90, it generates a cone with the remainder of dividing by 360 deg taking into account the reverse vector of the axis.
Generate a sphere from the point p0 and the radius r in the three-dimensional space. An error occurs, if r is zero or negative value.
Generate a cylinder from the point p0, vector v of the axis and the radius r in the three-dimensional space. An error occurs, if the length of v is zero. If r is 0, it will be a straight line. If r is a negative value, it will be the same as when specifying -r.
Generate a quadratic surface in the three-dimensional space represented by definition equation f(X) = <AX, X> + BX + c. It does not determine whether the definition formula is completed as a two-dimensional curved surface.
Determine whether the “Minimum Bounding Box” of the each spatial ranges intersect. It returns TRUE only if they intersect. “intersect” means “there is a common region to the two regions.”
Both g1 and g2 cannot be specified quadratic surface (QUADRATICSURFACE). In addition, for POLYHEDRALSURFACE, the result will be undefined if specified the shapes other than rectangular parallelepipeds, the combination of POLYGON which do not share a side each other and the shape which is not closed as a spatial structure.
It use only x and y coordinates excluding z coordinate as a decision object when one is two-dimensional spatial structure consisting of x and y coordinate and the other is three-dimensional spatial structure consisting of x, y and z coordinates. For example, the result of intersection determination of POINT (x0 y0) and LINESTRING (x1 y1 z1, x2 y2 z2) is true only if x1 <= x0 <= x2 and y1 <= y0 <= y2 are completed.
FALSE is always returned if either one or both is an empty geometry.
Circumscribed rectangular parallelepiped is defined according to the type of structure as follows.
Example) Selecting a Row such as spatial-type data on the column geom and the specified rectangular range intersect.
SELECT * WHERE ST_MBRIntersects(geom, ST_GeomFromText('POLYGON((0 0,10 0,10 10,0 10,0 0))'))
Determine whether the quadratic curved surface q and the circumscribed rectangular parallelepiped of spatial structure g other than two-dimensional curved intersect. It returns TRUE only if they intersect. It can not give other than quadratic curved surface to q and a quadratic curved surface to g. Also, it can not give a two-dimensional spatial structure to g. The condition of other determination of intersecting are the same as ST_MBRIntersects.
Returns the SRID of the spatial structure g. If g does not have a SRID, it returns an invalid SRID (-1).
This section defines the selection expressions to specify target Column(s), a calculation formula, etc. Only the Rows satisfying the conditions specified in the FROM and WHERE clauses are to be processed.
Selects all the Rows satisfying the specified condition(s). By designating a ORDER BY section (to be described later), the data can be sorted by column value.
Any one of operation functions described in the following sections is available for aggregation, selection and other operations.
If an overflow occurs in an internal operation, -INF or INF is returned for floating point operation, and the value “undefined” is returned for integer operation. And if NaN is given as an operand for floating-point operation, NaN is returned.
Here are described the aggregation operations which can be applied to a set of any Rows.
If no target field is found, the number of results returned is 0, except for the functions COUNT and SUM shown below. Otherwise, the number of results returned is always 1. If the value of the specified column is NULL, it will not get evaluated unless otherwise noted.
Returns the largest value in the specified Column. Only numerical or time series columns can be specified. The type of a returned value is the same as that of the specified Column.
Returns the smallest value in the specified Column. Only numerical or time series columns can be specified. The type of a returned value is the same as that of the specified Column.
Return the number of Rows satisfying a given condition(s). A Column cannot be specified. The type of a returned value is always LONG. If there is not a single row that can serve as a target, the value of the operation result is 0. It also evaluates rows containing a NULL value.
Return the sum of values in the specified Column. Only a numeric-type Column can be specified. The type of a returned value is LONG if the specified Column is of integer type, and DOUBLE if it is of floating-point type.
Returns the average value of the specified Column. Only a numeric-type Column can be specified. The type of a returned value is always DOUBLE.
Returns the variance of values in the specified Column. Only a numeric-type Column can be specified. The type of a returned value is always DOUBLE.
Returns the standard deviation of values in the specified Column. Only a numeric-type Column can be specified. The type of a returned value is always DOUBLE.
Here are described the aggregation operations which can be applied to a set of Rows in a TimeSeries.
In aggregation operation weighted by a time-type key, for each Row satisfying a condition, a weighted value is obtained by calculating half the time span between the adjacent Rows before and after the Row in terms of seconds. However, if a Row has only one adjacent Row, the time span from the adjacent Row is considered, and if no adjacent Rows exist, 1 (sec.) is used as a weighted value. If the value of the specified column is NULL, it will not get evaluated unless otherwise noted.
Returns the average weighted by a time-type key of values in the specified Column. The type of a returned value is always DOUBLE.
The weighted average is calculated by dividing the sum of products of sample values and their respective weighted values by the sum of weighted values. Only a numeric-type Column can be specified. The method for calculating a weighted value is as shown above.
Example) Obtaining the time-weighted average voltage at Point 103, plant1 in July 2011.
SELECT TIME_AVG(voltage103) FROM plant1
WHERE TIMESTAMP('2011-07-01T00:00:00Z') <= timestamp AND timestamp < TIMESTAMP('2011-08-01T00:00:00Z')
Here, you can see an example of the procedure for calculating a weighted average for the TimeSeries below.
| Key (seconds from 00:00:00 on July 1, 2011) | Column to be aggregated |
|---|---|
| 0 sec. | 4 |
| 10 sec. | 3 |
| 20 sec. | 2 |
| 40 sec. | 1 |
This TimeSeries is extended as shown in the table below; starting from the left, a midpoint time between adjacent Row values, a weighted value, and a product of a sample value (a Column value) and the weighted value are calculated.
| Key | Column to be aggregated | Midpoint (seconds) | Time span from (previous) midpoint | Time span from (following) midpoint | Weighted value | Product of sample and weighted values |
|---|---|---|---|---|---|---|
| 0 sec. | 4 | - | - | 5 (=5-0) | 5 | 20 (=4*5) |
| (Midpoint) | - | 5 sec.(=(0+10)/2) | - | - | - | - |
| 10 sec. | 3 | - | 5 (=10-5) | 5 (=15-10) | 10 (=5+5) | 30 (=3*10) |
| (Midpoint) | - | 15 sec.(=(10+20)/2) | - | - | - | - |
| 20 sec. | 2 | - | 5 (=20-15) | 10 (=30-20) | 15 (=5+10) | 30 (=2*15) |
| (Midpoint) | - | 30 sec.(=(20+40)/2) | - | - | - | - |
| 40 sec. | 1 | - | 10 (=40-30) | - | 10 | 10 (=1*10) |
Finally, all the products of sample values and their respective weighted values and all the weighted values are added up respectively, and then the quotient of both the sums is calculated. In the case of this TimeSeries, it is calculated as: (20+30+30+10)/(5+10+15+10) =90/40=2.25. This procedure is not necessarily the same as that for internal operations in GridDB.
The normal unweighted average is calculated as: (4+3+2+1)/4=10/4=2.5.
Selects a time-series Row whose timestamp is identical with or just after the specified timestamp.
Example) Obtaining the temperature at plant1 at the beginning of July 2011.
SELECT TIME_NEXT(*, TIMESTAMP('2011-07-01T00:00:00Z')) FROM plant1
Select a time-series Row whose timestamp is just after the specified timestamp.
Selects a time-series Row whose timestamp is identical with or just before the specified timestamp.
Selects a time-series Row whose timestamp is just before the specified timestamp.
Returns a specified Column value of the time-series Row whose timestamp is identical with the specified timestamp, or a value obtained by linearly interpolating specified Column values of adjacent Rows whose timestamps are just before and after the specified timestamp, respectively. NULL is set if NULL is found in either of the previous or the next timestamp of the specified column value. If no Row with the same timestamp nor no Row with an earlier or later timestamp is found, an intended Row is not generated and the number of Rows returned is 0. Only a numeric-type Column can be specified. The field values of the Row with the latest timestamp among those with the timestamp identical with or earlier than the specified timestamp are set on the specified Column and the fields other than a key.
Takes a sampling of Rows in a specific range from a given start time to a given end time.
Each sampling time point is defined by adding a sampling interval multiplied by a non-negative integer to the start time, excluding the time points later than the end time.
If there is a Row whose timestamp is identical with each sampling time point, the values of the Row are used. Otherwise, interpolated values are used. Row field values in the specified Column to be interpolated are obtained by linearly interpolating the values of the Rows just before and after a sampling time point. NULL is set if NULL is found in either of the previous or the next timestamp of the specified column value. For other fields, the values of the Row with the latest timestamp among those with earlier timestamps than a sampling time point are used as interpolated values. Columns to be linearly interpolated must be of numeric type. If “*” is specified instead of a specific Column name, the latter method of interpolation is applied to all field.
If there is no Rows to be referenced for interpolation at a specific sampling time point, a corresponding Row is not generated, and thus the number of results returned is reduced by the number of such time points. A shorter sampling interval increases the likelihood that identical Row field values will be used for the Columns with no need for linear interpolation even at different sampling time points. The sampling interval parameter “interval” must be a positive value.
Example) Obtaining hourly voltage information at Point 103, plant1 on July 1, 2011.
SELECT TIME_SAMPLING(
voltage103, TIMESTAMP('2011-07-01T00:00:00Z'), TIMESTAMP('2011-07-02T00:00:00Z'), 1, HOUR)
FROM plant1
In addition, regarding this sampling result, the ORDER BY section (to be described later) can be described and sorted in column sequence. Example) Determine the hourly voltage at Plant 1, Point 103 on July 1, 2011 and sort the data in voltage sequence.
SELECT TIME_SAMPLING(
voltage103, TIMESTAMP('2011-07-01T00:00:00Z'), TIMESTAMP('2011-07-02T00:00:00Z'), 1, HOUR)
FROM plant1 ORDER BY voltage103
Return any row group that has either the maximum or minimum specified column value. If the value of the specified column is NULL, it will not get evaluated unless otherwise noted.
Find the row group with the maximum specified column value. Only numerical or time series columns can be specified.
Find the row group with the minimum specified column value. Only numerical or time series columns can be specified.
The sorting sequence of the search results can be specified by the description in the ORDER BY section. The description in the ORDER BY section is defined as follows.
ORDER BY (Column name) [ASC|DESC] [, (Column name) [ASC|DESC]]*
However, an ‘*’ means that the previous component is repeated 0 or more times.
ASC means to sort in ascending order and DES means to sort in descending order. If the sorting sequence is not specified, it will remain as ascendant. If multiple sorting conditions are specified, the results will be sorted in order starting from the top-most condition. NULL is sorted as the max value.
Example) Conduct a search with a in descending order as the first sorting condition, b in ascending order as the second sorting condition, and c in ascending order as the third sorting condition.
SELECT * ORDER BY a DESC, b ASC, c
Unlike SQL, functions and formulas cannot be specified in the sorting conditions.
The number of search results found can be limited by the description stated in the LIMIT section. In addition, the start position for locating the search results can be specified by the OFFSET. The syntax of the LIMIT and OFFSET specifications are defined as follows.
LIMIT (number) [OFFSET (number)]
OFFSET is always used together with LIMIT. If omitted, the meaning is the same as OFFSET 0. Negative values and floating point decimals cannot be specified for the LIMIT and OFFSET figures.
Formulas and functions cannot be specified for the LIMIT and OFFSET figures.
You can obtain execution plan information and analysis information on execution results by adding EXPLAIN or EXPLAIN ANALYZE before a SELECT statement.
A obtained result set is composed of an array of entries with the same structure as a Row.
| Name | Type | Description |
|---|---|---|
| ID | INTEGER | An ID indicating the position of an entry in an array of entries. |
| DEPTH | INTEGER | Indicates a depth for representing the relation with other entries. If there is found an entry whose depth is smaller than that of a target entry by one, through checking entries one by one whose IDs are smaller than that of the target entry, it means that the target entry describes the content of the found entry in more detail. |
| TYPE | String type | Indicates classification of information indicated by an entry; namely, classification of analysis results, such as execution time, classification of components of a query plan, etc. |
| VALUE_TYPE | STRING | Indicates the type of a value assigned to the information indicated by an entry. The type of a value assigned to execution time or other analysis results, for example, is returned. Type names are the same as the primitive types defined in TQL. An empty string is set as VALUE if no value is assigned. |
| VALUE | String type | Indicates a character string representing a value assigned to the information indicated by an entry. An empty string is set as VALUE if no value is assigned. |
| STATEMENT | String type | Returns a part of a TQL statement corresponding to the information indicated by an entry. An empty string is set if no correspondence is found. |
EXPLAIN is placed before a SELECT statement to obtain execution plan information about the SELECT statement. The SELECT statement itself will not be executed.
Even a single identical query might return different results depending on the indexing settings or other conditions.
EXPLAIN ANALYZE is placed before a SELECT statement to execute the SELECT statement and obtain analysis information, such as execution time, as well as obtain execution plan information about the SELECT statement.
Describe the range of values such as the upper limit of the value, etc. Refer to GridDB Features Reference for the restriction values of the system.
The values that may be adopted by the basic datatypes below are as follows.
| Type | Representable values |
|---|---|
| Boolean (BOOL) | TRUE/FALSE |
| BYTE | -27 to 27-1 |
| SHORT | -215 to 215-1 |
| INTEGER | -231 to 231-1 |
| LONG | -263 to 263-1 |
| FLOAT | Conforming to IEEE754 |
| DOUBLE | Conforming to IEEE754 |
| TIMESTAMP | 1970/1/1 to 9999/12/31(UTC). Accuracy is in milliseconds. Leap seconds are not handled. |
Value that can be used to TQL operation in spatial (GEOMETRY) type is any arbitrary value returned by the ST_GeomFromText function. Among these values, the value that can be stored containers is excluding the QUADRATICSURFACE structure.
The range of values of objects mapped onto the basic types through API may be different from those of the above basic types. The value out of the described range cannot be registered into containers. But the value may be used in the other operations, such as constructing a search condition. For example, a java.util.Date object to be mapped onto TIMESTAMP by Java API can have a value before the year 1970 that cannot be stored in containers, and the value can be used as a RowKey condition of a RowKeyPredicate object or in a sampling query. However, in that case, it is possible that an error occurs when obtaining rows by the condition. For the representation range of the object itself to be mapped onto the above basic types, see the definition of the object type.