select

Reads rows. The result arrives while the data brokers produce it: the first rows are displayed before the last ones are computed.

Syntax

select COLUMNS from TABLE [alias]
  [ [inner | left | right] join TABLE2 alias2 on alias.COL = alias2.COL ... ]
  [ where CONDITION ]
  [ order by COLUMNS ]
  [ limit N ];

You need the SELECT privilege on every table, and the table must be deployed. If a partition of the table has no available server, the select is refused: Error: One partition of this table is unavailable.

How it runs

SynSQL sends your statement to the data brokers that hold the partitions. Each one executes it on its own data with MariaDB, and the rows come back through the broker that you are connected to. A broker with many partitions asks other brokers of the cluster to help.

Because every partition runs the statement on its own:

• a function such as count() or avg() is computed per partition. You receive one row for each partition and add them up in your program. On gaia_source, select count(*) returns 600 rows.
• order by returns the rows in order.
• limit N stops after N rows.
• a join only finds the pairs that sit in the same partition. Joined tables must have the same hash key and the same number of partitions; deploy one close to the other to be sure.

Narrow tables

• Up to 16 tables in a join. inner, left and right joins, with on alias.col = alias.col.
• order by, limit, where with any condition that MariaDB accepts on one partition.

select gs.source_id, gs.ra, ap.teff_esphs
from gaia_source gs
inner join astrophysical_parameters ap on gs.source_id = ap.source_id
where gs.ra > 13.2 and gs.ra < 15.2;

This join, on tables of 1.8 and 1.5 billion rows, returns its 966 rows in less than a second on the demo cluster.

Wide tables

• The select must list all the columns of the hash key.
• A wide table cannot be joined, and group by and order by are refused.
• limit and where are supported. A condition with or on columns that live in distant chunks assembles every candidate row, which is slow.

select id, gene_00000 from single_cell where gene_00000 < 100;

NULL

A NULL column is displayed as (NULL) | by the command line client. An empty string and a NULL cannot be told apart in the result of an ordinary table.