SQL Select

The SQL SELECT statement is used to display the contents of the specified columns in a table (known as “projection”). eXtremeSQL supports almost all standard SQL constructions. (Please see the wikipedia page for a detailed description of the select statement syntax.)

Some important eXtremeSQL features and SQL extensions are described in the sections below.

Order By

The order by clause causes the result set to be sorted by the specified columns. For example, consider the following table and index definition initialized with 6 initial rows:

 
    create table orders(id integer not null, tm integer not null);
    create index idx on orders(id,tm);
    insert into orders values (2,  200);
    insert into orders values (2,  220);
    insert into orders values (2,  210);
    insert into orders values (1,  100);
    insert into orders values (1,  120);
    insert into orders values (1,  110);
     

A simple select displays the rows in the order of insertion:

 
    select * from orders;
    id      tm
    ---------------------
    2       200
    2       220
    2       210
    1       100
    1       120
    1       110
     

Now, using the order by clause, we can sort the result set in ascending order by column id and tm as follows:

     
    select * from orders order by id, tm;
    id      tm
    -------------------------------------
    1       100
    1       110
    1       120
    2       200
    2       210
    2       220
     

Or sort the result set rows in descending order as follows:

     
    select * from orders order by id desc, tm desc;
    id      tm
    -----------------------------------------------
    2       220
    2       210
    2       200
    1       120
    1       110
    1       100
     

Distinct Order

A useful eXtremeSQL extension is the possibility to add the distinct keyword in the order by clause. This allows restricting the result set to a single record from an ordered subset. For example, using the distinct order feature, we can extract the first row from each "ordered subset" defined by the values of column id as follows:

     
    select * from orders order by id distinct,  tm;
    id    tm
    ----------------------------------------------
    1    100
    2    200
 

Or we can extract the last row from the "ordered subset" by specifying the descending order as follows:

 
    select * from orders order by id desc distinct, tm desc;
    id    tm
    --------------------------------------------------------
    2    220
    1    120
     

 

For Update

The for update clause in a SQL select statement is used to indicate that the result set (cursor) is going to be used subsequently for updating data values. Note that the eXtremeSQL implementation of the for update clause differs slightly from the standard. Whereas the standard specifies “For Update On” where the “On” preposition requires specification of a list of columns that will be subject to updates, eXtremeSQL does not require the “On list” – all columns are modifiable. In eXtremeSQL, the for update clause instructs the runtime to avoid issuing a transaction upgrade (from READ_ONLY to READ_WRITE), but rather to open a READ_WRITE transaction from the outset.

 

The Ignore Column Operator for Sequences

The exclamation point ("!") can be used as a shortcut for the sequence function seq_ignore() to indicate that a calculated column is to be ignored in the result set output. For example consider the following table with one ordered and two unordered sequence columns:

     
    create table Quotes(sym string primary key, 
        day sequence(int asc), 
        open sequence(int), 
        close sequence(int));
    insert into Quotes values('AAA', 
        [20170501, 20170502, 20170503, 20170504, 20170505, 20170509, 20170510], 
        [101, 102, 103, 104, 105, 109, 110], 
        [111, 112, 113, 114, 115, 119, 120]);
    insert into Quotes values('BBB', 
        [20170501, 20170502, 20170503, 20170504, 20170505, 20170509, 20170510], 
        [201, 202, 203, 204, 205, 209, 210], 
        [211, 212, 213, 214, 215, 219, 220]);
    insert into Quotes values('CCC', 
        [20170501, 20170502, 20170503, 20170504, 20170505, 20170509, 20170510], 
        [301, 302, 303, 304, 305, 309, 310], 
        [311, 312, 313, 314, 315, 319, 320]);
         
    select !seq_search(day, 20170501, 20170503) as daterange, 
        open@daterange as "open", close@daterange as "close"
    from Quotes;
     
    daterange open    close
    ------------------------------------------------------------------------------
    {?}     {101, 102, 103} {111, 112, 113}
    {?}     {201, 202, 203} {211, 212, 213}
    {?}     {301, 302, 303} {311, 312, 313}
 
    Selected records: 3
     

In this query we select an interval with function seq_search() and, as we don't need all timestamps in the interval in the query result, we mark the column as ignored with the! operator. Ignored column may be used in other calculated columns but will not appear in the actual statement output.

SELECT ... AS

The AS command is used to rename a column with a column alias.

    XSQL>create table T (t text, i int);
    XSQL>insert into T values ('john', 1);
    XSQL>insert into T values ('mike', 2);
    XSQL>select t as name from T;
    name
    ------------------------------------------------------------------------------
    john
    mike

    Selected records: 2

A column alias only exists for the duration of the query. Standard SQL does not permit references to column aliases in WHERE clauses because the WHERE clause is evaluated before the SELECT clause: SELECT is always the last step in the execution path. As a result, the alias is not yet defined when WHERE is processed.

    XSQL>select t as name from T where name='john';
    ERROR: Compiler error in line 1 at position 22: Failed to locate column 'name'
    name from T where name='john'
                      ^

An "inline view" can be used as a workaround - a subquery with an alias that can be treated like a table. This effectively allows the SELECT to be evaluated first, as shown in the example below.

    XSQL>select * from (select t as name from T) where name='john';
    name
    ------------------------------------------------------------------------------
    john

    Selected records: 1

ANN Indexes and the NEAR Operator

SQL supports approximate nearest neighbor (ANN) queries over vector arrays using the NEAR operator:

    select id, name
    from embeddings
    where embedding near [0.86, 0.88, 0.89]
    limit 3;

The optimizer expects an HNSW or Vamana index for ANN NEAR queries over vector arrays. The LIMIT clause is typically used to request the desired number of neighbors.