WebsiteDiscordBlog

Supported SQL

🚧

Available in Alpha

Please note that Proof of SQL is currently in closed alpha and only available to a limited set of customers. While the documentation is available to the community, some of the features are limited to alpha participants. To join the Proof of SQL alpha, reach out to us on Discord.

Proof of SQL generates a zk-proof from SQL logic. However, it requires a new circuit for every new SQL function and data type. Below are details of currently supported SQL for Proof of SQL:

Data Types currently supported:

  • BIGINT
  • VARCHAR

SQL Syntax currently supported:

  • SELECT
  • WHERE
  • AND
  • OR
  • =
  • ORDER BY
  • LIMIT
  • GROUP BY (with sum, min, max, count)

Proof of SQL select grammar

SELECT { * | expression [ [ AS ] output_name ] [, ...] }
    { FROM [schema_name.]table_name } 
    [ WHERE condition ]
    [ GROUP BY column_name [, ...] ]
    [ ORDER BY output_name [ ASC | DESC ] [, ...] ]
    [ LIMIT { positive_count | ALL } ]
    [ OFFSET start ]

where `expression` can be one of:
    `column_name`
    `aggregate_function`

where `condition` can be one of:
    `column_name` = `literal`
    `literal` = `column_name`
    `column_name` { != | <> } `literal`
    `literal` { != | <> } `column_name`

    not (`condition`)
    `condition` and `condition`
    `condition` or `condition`

where `literal` can be one of:
    `BIGINT`
    `VARCHAR`

Supported aggregate functions

FunctionArgument Type(s)Return TypeDescription
count(*)BIGINTnumber of input rows
count(column_name)anyBIGINTnumber of input rows
sum(column_name)BIGINTBIGINTthe sum of column rows across all input values
min(column_name)BIGINTBIGINTthe minimum value of expression across all input values
max( column_name)BIGINTBIGINTthe maximum value of expression across all input values

Restrictions

  • In case no output_name is specified, the column_name is used as output_name.
  • order by clause must always appear after where and group by clauses.
  • Equality / Not equals between column names and literals must have the same type.
  • limit and offset clause must appear after order by clause.
  • offset can appear before limit clause.

Current limitations

  • Column names and output names cannot be the same as reserved keywords.
  • Aggregate functions can only be used in association with the group by expressions

Reserved words

all, asc, desc, as, and, from, not, or, select, where, order, by, limit, offset, group, min, max, count, sum

Examples

select a from sxt_tab where a = 3
select a from sxt_tab where a = 'abc'
select a as b, a as c from sxt_tab where a = 3
select a,  b from sxt_tab where c = 123
select * from sxt_tab where a = 3
select a from sxt_tab where b = +4
select a from sxt_tab where b <> +4
select a from sxt_tab where b = -4
select a from sxt_tab where (b = 3) and (c = -2)
select a from sxt_tab where (b = 3) or (c = -2)
select a from sxt_tab where (b = 3) or (not (c = -2))
select a from sxt_tab where not (((f = 45) or (c = -2)) and (b = 3))
select a from sxt_tab where a = -9223372036854775808
select a from sxt_tab where a = 9223372036854775807
select a as b_rename from sxt_tab where b = +4
select a from eth.sxt_tab where a = 3
select * from eth.sxt_tab
select * from sxt_tab where a = 3 order by b
select a, b from sxt_tab where a = 3 order by b desc, a asc
select salary as s, name as salary from sxt_tab where salary = 5 order by salary desc
select salary as s, name, salary as d from sxt_tab order by s
select a from sxt_tab limit 3
select a from sxt_tab offset 0
select a from sxt_tab offset 7
select a from sxt_tab offset -7
select a from sxt_tab limit 55 offset 3
select a from sxt_tab where a = -3 order by a desc limit 55 offset 3
select department from employees group by department
SELECT max(salary) from employees group by department
SELECT max(salary) as max_sal, department as d, count(department) from employees group by department, bonus
SELECT max(salary) as max_sal, department as d, count(department) from employees group by department, bonus order by max_sal
select count(bonus) b from sxt.employees group by department
select department, count(bonus), count(department) as dep from sxt.employees group by department
select count(*) from sxt.employees where salary = 4 group by department
SELECT department as d1, department as d2 from employees group by department
select department as d1, max(salary), department as d2, sum(bonus) as sum_bonus from employees group by department, bonus, department

Simple roadmap of items on the horizon

Coming Soon (easy adds):

  • CASE
  • !=
  • binary blobs
  • int128
  • Addition (+)
  • Multiplication (x)

In Design (harder adds):

  • JOIN
  • int256
  • Division (÷)

Updated 3 months ago