Nulls
For the latest version of Engine, the following data types were used in conjunction with various NULL statements and the results tabulated:
| Name | Data Type | Width | Cardinality |
|---|---|---|---|
| INT | INTEGER | 8 | 2 |
| INT2 | INTEGER | 8 | 7 |
| INT3 | INTEGER | 8 | 100,000 |
| STR1 | STRING | 20 | 2 |
| STR2 | STRING | 20 | 7 |
| STR3 | STRING | 20 | 100,000 |
| STR4 | STRING | 1 | 2 |
| STR5 | STRING | 1 | 7 |
| R1 | REAL | 18 | 2 |
| R2 | REAL | 18 | 7 |
| R3 | REAL | 18 | 100,000 |
| UN1 | UNICODE | 20 | 2 |
| UN2 | UNICODE | 20 | 7 |
| UN3 | UNICODE | 20 | 100,000 |
| UN4 | UNICODE | 1 | 2 |
| UN5 | UNICODE | 1 | 7 |
| Date1 | DATE | 11 | 2 |
| Date2 | DATE | 11 | 7 |
| Date3 | DATE | 11 | 10000 |
| T1 | TIME | 8 | 2 |
| T2 | TIME | 8 | 7 |
| T3 | TIME | 8 | 86401 |
| DT1 | DATETIME | 20 | 2 |
| DT2 | DATETIME | 20 | 7 |
| DT3 | DATETIME | 20 | 100000 |
Array Fields
Array fields return all records where the value stored for the array field is blank (i.e. an empty string).
SELECT * WHERE ARRAY_FIELD=NULL;
SELECT * WHERE ARRAY_FIELD IN ("A", "B", NULL);
will NOT return blank records.
Interpreting the results in the grids.
In the grids below, the behavior of different column types when used in various NSQL expressions and queries are detailed. Their behavior is indicated through keys. The following table explains the results that are shown in the grids.
| KEY | Description | Details | Example |
|---|---|---|---|
| N2 | NULL is not selected | NULL values are not returned by the query. |
Query will fail to return NULL records for records that contain NULL. |
| N3 | "NULL" is evaluated as zero | The "NULL" string is evaluated as if 0 had been specified. Note - this is true when any string value is specified as a value for a numeric field. To alter this behavior, set ALT_StrongTypeCheck = 1. This will then generate an error if a string is specified as a value for a numeric field. | SELECT * WHERE FIELD="NULL": Query will return all
records containing the value 0.
|
| N6 | "" is treated as 0. | An empty string is evaluated as if 0 had been specified. | SELECT * WHERE FIELD="": Query will return all records
containing the value 0.
|
| N7 | Expression is not supported | It is not possible to use some data types with the ISNULL function. | SELECT * WHERE ISNULL(FIELD)=0: Query will generate an
error, expression is not supported.
|
