SingleStore compared to YugabyteDB

As a Developer Advocate at Yugabyte, I've got some questions about how we compare with SingleStore. There are many simple things: YugabyteDB is Open Source, PostgreSQL feature-compatible, and is a distributed SQL database. To be sure that SingleStore is not in the Distributed SQL category, I wanted to try a simple SQL schema with the most basic SQL features, the old EMP/DEPT mentioned from the first papers about SQL, and which became the legendary SCOTT schema in Oracle. I you want to have a look at it, you can create a YugabyteDB Managed free cluster and the tutorial will help you create a modern version of it and play with advanced queries.

As I've encountered some errors that I find misleading, I'm writing this blog post to help on similar situation, but you may save time by looking at the list of SQL features that are not supported in SingleStore.

Create SingleStore trial

I followed the online instructions to start memsql (the internal name of SingleStore) with the trial license provided:

docker run -i --init \
    --name memsql-ciab \
    -e LICENSE_KEY="TheLicenceProvidedForTrial0AAAAAAAAAAAEAAAAAAAAACgwNAIYMiZ83R94l1DqqMjt4gqo48F2p4Mm6gAXAhhfuPPKuSmfu3srHlVhoZ2Kspm2o1gz5Y0AAA==" \
    -e ROOT_PASSWORD="admin_p455w0rd" \
    -p 3306:3306 -p 8080:8080 \
    memsql/cluster-in-a-box

docker start memsql-ciab

The website is really user friendly to generate the command line above. Developers are used to Open Source and having to enter a license key usually makes it less friendly. Generating the docker command line with the trial license is a good idea.

I connect to it and run the creation of the DEPT table:

CREATE TABLE dept (
  deptno integer NOT NULL,
  dname text,
  loc text,
  description text,
  CONSTRAINT pk_dept PRIMARY KEY (deptno asc)
);

I'm lucky, this syntax is compatible with MySQL which can be very different from the SQL standard in many cases.

Then, I want to create the EMP table:

CREATE TABLE emp (
  empno integer generated by default as identity (start with 10000) NOT NULL,
  ename text NOT NULL,
  job text,
  mgr integer,
  hiredate date,
  sal integer,
  comm integer,
  deptno integer NOT NULL,
  email text,
  other_info json,
  CONSTRAINT pk_emp PRIMARY KEY (empno),
  CONSTRAINT emp_email_uk UNIQUE (email),
  CONSTRAINT fk_deptno FOREIGN KEY (deptno) REFERENCES dept(deptno),
  CONSTRAINT fk_mgr FOREIGN KEY (mgr) REFERENCES emp(empno)
);

This fails with:

ERROR 1064 ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'generated by default as identity (start with 10000) NOT NULL, ename text NOT ' at line 1

I replace the standard SQL generated by default as identity with auto_increment which MySQL users are used to

CREATE TABLE emp (
  empno integer  NOT NULL auto_increment,
  ename text NOT NULL,
  job text,
  mgr integer,
  hiredate date,
  sal integer,
  comm integer,
  deptno integer NOT NULL,
  email text,
  other_info json,
  CONSTRAINT pk_emp PRIMARY KEY (empno),
  CONSTRAINT emp_email_uk UNIQUE (email),
  CONSTRAINT fk_deptno FOREIGN KEY (deptno) REFERENCES dept(deptno),
  CONSTRAINT fk_mgr FOREIGN KEY (mgr) REFERENCES emp(empno)
);

This fails with:

ERROR 1706 ER_MEMSQL_FEATURE_LOCKDOWN: Feature 'FOREIGN KEY on COLUMNAR table' is not supported by SingleStore.

Ok, let's re-create the table as ROWSTORE tables, which makes sense for OLTP (which is what I'm testing here):

DROP TABLE dept;
CREATE ROWSTORE TABLE dept (
  deptno integer NOT NULL,
  dname text,
  loc text,
  description text,
  CONSTRAINT pk_dept PRIMARY KEY (deptno asc)
);
CREATE ROWSTORE TABLE emp (
  empno integer  NOT NULL auto_increment,
  ename text NOT NULL,
  job text,
  mgr integer,
  hiredate date,
  sal integer,
  comm integer,
  deptno integer NOT NULL,
  email text,
  other_info json,
  CONSTRAINT pk_emp PRIMARY KEY (empno),
  CONSTRAINT emp_email_uk UNIQUE (email),
  CONSTRAINT fk_deptno FOREIGN KEY (deptno) REFERENCES dept(deptno),
  CONSTRAINT fk_mgr FOREIGN KEY (mgr) REFERENCES emp(empno)
);

Now another error still prevents the referential integrity:

ERROR 1706 ER_MEMSQL_FEATURE_LOCKDOWN: Feature 'FOREIGN (non-SHARD) key to a sharded table' is not supported by SingleStore.

So the referenced table cannot be sharded. No problem, this is a reference table anyway, let's create it as REFERENCE:

DROP TABLE dept;
CREATE ROWSTORE REFERENCE TABLE dept (
  deptno integer NOT NULL,
  dname text,
  loc text,
  description text,
  CONSTRAINT pk_dept PRIMARY KEY (deptno asc)
);
CREATE ROWSTORE TABLE emp (
  empno integer  NOT NULL auto_increment,
  ename text NOT NULL,
  job text,
  mgr integer,
  hiredate date,
  sal integer,
  comm integer,
  deptno integer NOT NULL,
  email text,
  other_info json,
  CONSTRAINT pk_emp PRIMARY KEY (empno),
  CONSTRAINT emp_email_uk UNIQUE (email),
  CONSTRAINT fk_deptno FOREIGN KEY (deptno) REFERENCES dept(deptno),
  CONSTRAINT fk_mgr FOREIGN KEY (mgr) REFERENCES emp(empno)
);

But it fails again:

ERROR 1706 ER_MEMSQL_FEATURE_LOCKDOWN: Feature 'FOREIGN (non-SHARD) key to a sharded table' is not supported by SingleStore.

You may think that it is because of the self-reference fk_mgr FOREIGN KEY (mgr) REFERENCES emp(empno) but I've got the same error when removing it. Let's remove both foreign keys:

CREATE ROWSTORE TABLE emp (
  empno integer  NOT NULL auto_increment,
  ename text NOT NULL,
  job text,
  mgr integer,
  hiredate date,
  sal integer,
  comm integer,
  deptno integer NOT NULL,
  email text,
  other_info json,
  CONSTRAINT pk_emp PRIMARY KEY (empno),
  CONSTRAINT emp_email_uk UNIQUE (email)-- ,
  -- CONSTRAINT fk_deptno FOREIGN KEY (deptno) REFERENCES dept(deptno),
  -- CONSTRAINT fk_mgr FOREIGN KEY (mgr) REFERENCES emp(empno)
);

(note that the space after -- is mandatory in MySQL)

This fails on another constraint:

ERROR 1895 ER_MEMSQL_UNIQUE_KEY_IMPLICIT_SHARD_KEY: The unique key named: 'emp_email_uk' must contain all columns specified in the primary key when no shard key is declared

The global unique constraint is not supported either. This is a fundamental SQL feature. In real life, most tables will have one or many unique natural keys in addition to a generated primary key. It is critical to enforce the uniqueness because this is the only way to prevent duplicates. This is critical in cloud-native highly available applications where, in case of failure, the application doesn't know if the network failure happened before or after the commit. The primary key is often generated by the application. Only a natural key can prevent double insertion.

Well, too many limits, I'm able to run my simple CREATE only when both tables are REFERENCE:

DROP TABLE dept;
CREATE ROWSTORE REFERENCE TABLE dept (
  deptno integer NOT NULL,
  dname text,
  loc text,
  description text,
  CONSTRAINT pk_dept PRIMARY KEY (deptno asc)
);
CREATE ROWSTORE REFERENCE TABLE emp (
  empno integer  NOT NULL auto_increment,
  ename text NOT NULL,
  job text,
  mgr integer,
  hiredate date,
  sal integer,
  comm integer,
  deptno integer NOT NULL,
  email text,
  other_info json,
  CONSTRAINT pk_emp PRIMARY KEY (empno),
  CONSTRAINT emp_email_uk UNIQUE (email),
  CONSTRAINT fk_deptno FOREIGN KEY (deptno) REFERENCES dept(deptno),
  CONSTRAINT fk_mgr FOREIGN KEY (mgr) REFERENCES emp(empno)
);

*This finally works. But... I'm not in a distributed database anymore, as no table is sharded. *

Insert

On my non-sharded table, I continue inserting the sample rows

INSERT INTO dept (deptno,  dname,        loc, description)
     VALUES    (10,     'ACCOUNTING', 'NEW YORK','preparation of financial statements, maintenance of general ledger, payment of bills, preparation of customer bills, payroll, and more.'),
            (20,     'RESEARCH',   'DALLAS','responsible for preparing the substance of a research report or security recommendation.'),
            (30,     'SALES',      'CHICAGO','division of a business that is responsible for selling products or services'),
            (40,     'OPERATIONS', 'BOSTON','administration of business practices to create the highest level of efficiency possible within an organization');

INSERT INTO emp (empno, ename,    job,        mgr,   hiredate,     sal, comm, deptno, email, other_info)
     VALUES   (7369, 'SMITH',  'CLERK',     7902, '1980-12-17',  800, NULL,   20,'[e-mail]', '{"skills":["accounting"]}'),
            (7499, 'ALLEN',  'SALESMAN',  7698, '1981-02-20', 1600,  300,   30,'[e-mail]', null),
            (7521, 'WARD',   'SALESMAN',  7698, '1981-02-22', 1250,  500,   30,'[e-mail]', null),
            (7566, 'JONES',  'MANAGER',   7839, '1981-04-02', 2975, NULL,   20,'[e-mail]', null),
            (7654, 'MARTIN', 'SALESMAN',  7698, '1981-09-28', 1250, 1400,   30,'[e-mail]', null),
            (7698, 'BLAKE',  'MANAGER',   7839, '1981-05-01', 2850, NULL,   30,'[e-mail]', null),
            (7782, 'CLARK',  'MANAGER',   7839, '1981-06-09', 2450, NULL,   10,'[e-mail]', '{"skills":["C","C++","SQL"]}'),
            (7788, 'SCOTT',  'ANALYST',   7566, '1982-12-09', 3000, NULL,   20,'[e-mail]', '{"cat":"tiger"}'),
            (7839, 'KING',   'PRESIDENT', NULL, '1981-11-17', 5000, NULL,   10,'[e-mail]', null),
            (7844, 'TURNER', 'SALESMAN',  7698, '1981-09-08', 1500,    0,   30,'[e-mail]', null),
            (7876, 'ADAMS',  'CLERK',     7788, '1983-01-12', 1100, NULL,   20,'[e-mail]', null),
            (7900, 'JAMES',  'CLERK',     7698, '1981-12-03',  950, NULL,   30,'[e-mail]', null),
            (7902, 'FORD',   'ANALYST',   7566, '1981-12-03', 3000, NULL,   20,'[e-mail]', '{"skills":["SQL","CQL"]}'),
            (7934, 'MILLER', 'CLERK',     7782, '1982-01-23', 1300, NULL,   10,'[e-mail]', null);

This works without any problem.

However, let's try to delete a department that holds employees:

> delete from dept where deptno=10;

Query OK, 1 row affected (372 ms)

> select * from dept;
+---------------------------------------------------------------------------------------------------------------------------+
| deptno                       | dname                        | loc                          | description                  |
+---------------------------------------------------------------------------------------------------------------------------+
| 20                           | RESEARCH                     | DALLAS                       | responsible for preparing ...|
| 30                           | SALES                        | CHICAGO                      | division of a business tha...|
| 40                           | OPERATIONS                   | BOSTON                       | administration of business...|
+---------------------------------------------------------------------------------------------------------------------------+

3 rows in set (110 ms)

> select deptno, count(*) from emp group by deptno order by 1;
+-------------------------------------------------------------+
| deptno                       | count(*)                     |
+-------------------------------------------------------------+
| 10                           | 3                            |
| 20                           | 5                            |
| 30                           | 6                            |
+-------------------------------------------------------------+

3 rows in set (75 ms)

With nothing sharded, I was able to create the foreign key, but it is useless as it protects nothing. I have now some employees in an inexistant department.

I've done this to verify that SingleStore is not a Distributed SQL database, as are the Spanner-based databases which distributes all SQL features, with all ACID properties in cross-shard transactions. But SingleStore has its use cases and is a major actor in the cloud native NewSQL databases, especially in analytics thanks to its default column store.