Sitemap

MkSQL's Dialect of SQL

Contents:

• Introduction
• Queries
  • Conditions
  • The FROM clause
  • Using the advanced IN and FROM features
• DML Statements
• DDL Statements
• Metakit Extensions

Introduction

MkSQL implements a fairly full subset of SQL 92 grammar. It does have it's peculiarities.

• No concept of NULL.
• SQL keywords must be all lowercase or all uppercase. Mixed case is not recognized.
• The DISTINCT keyword is not recognized (I consider this a shortcoming).
• The ALL, SOME and ANY keywords are not recognized (I do not consider these shortcomings worth bothering about).
• The CAST and CASE keywords are not recognized (ditto).
• The CREATE statement is fairly idiomatic (see below).
• You cannot use double quotes to make an illegal identifier into a column name.
• There is no concept of owner, so dba.table will not parse correctly.

Queries

subquery ::= selectstmt [ ORDER BY orderbylist ]
subquery ::= selectstmt [ { UNION | INTERSECT | EXCEPT } selectstmt ... ]

Note that you can't use an ORDER BY clause on a UNION, INTERSECT or EXCEPT statement. Note that you can construct statements like "select ... INTERSECT ( select ... UNION select ... )".

selectstmt ::= SELECT selectlist FROM tablelist [ WHERE condition ] [ GROUP BY columnlist [ HAVING condition ] ]

A few things worth noting about columns:

• Nearly any valid expression (including literals) can be used in a selectlist.
• 0b1 IF(cond,trueresult,falseresult) can be used in selectlists and conditions.
• Aliases are also allowed in a selectlist: sum(columnA) as sum.
• When an orderbylist refers back to the selectlist, it may do so by column number (starting from 1), by alias (if it has one), or by the exact verbatim expression used in the selectlist if it there is no alias. Where the expression is more than a simple column reference, an alias is preferred.
• When a GROUP BY or a HAVING refers back to the selectlist, it must do so by the alias (if there is one), or the exact verbatim expression used in the selectlist.

The last two restrictions are because if a result set column has no alias (and is more than a simple column reference), the text used to define the expression becomes the alias.

Conditions

MkSQL implements all the conditions you would expect, and a few you may not. The IN clause is fairly advanced:

          SELECT ... WHERE ('joe', 12) IN 
                ( SELECT name, shoesize FROM customers )
          SELECT ... WHERE (name, shoesize) IN 
                ( ('joe', 12), ('charlie', 13), ('wilma', 14) )
      

More generally, we have:
incondition ::= expr IN ( { exprlist | subquery } )
where expr includes things very much like Python tuples (and exprlist is a bunch of expressions, including the possibility of tuplish things). Like Python tuples, the tuple of length one must be spelled (expr,), but that should rarely be necessary.

The FROM clause

MkSQL implements the most important of the JOIN types: the CROSS JOIN, the NATURAL JOIN, LEFT and RIGHT OUTER JOINs, JOIN ON and JOIN USING. (This list lacks the FULL OUTER JOIN and the UNION JOIN - both of which are very strange beasts indeed!).

The CROSS JOIN is the product of two tables. The following statements are equivalent:

         SELECT ... FROM a CROSS JOIN b ...
         SELECT ... FROM a, b ... 
      

The NATURAL JOIN is the INNER JOIN of two tables on common column names. So if frequents and serves have bar as the only common column name, these two statements are equivalent (and the first will be faster):

         SELECT ... FROM frequents JOIN serves WHERE ...
         SELECT ... FROM frequents f, serves s WHERE f.bar = s.bar AND ... 
      

Further, these are equivalent to the following JOIN ON (where the columns to use in the join can be specified):

         SELECT ... FROM frequents JOIN serves USING (bar,) WHERE ...
      

JOIN ON is just a way of moving a condition out of the WHERE clause. These are equivalent:

         SELECT ... FROM a JOIN b ON condition WHERE ...
         SELECT ... FROM a, b ON condition AND ... 
      

NATURAL JOINs and JOIN ONs may also specify LEFT or RIGHT. All rows from the appropriate table will be included, whether or not they have a match with the other table. Where normal SQL would fill the unavailable columns with NULLs, MkSQL fills them with default values.

Using the advanced IN and FROM features

These features can be very valuable. For example, the following query:

          select * from frequents as f, serves as s 
            where f.bar = s.bar 
            and not exists 
              ( select l.drinker, l.beer from likes l 
                  where l.drinker=f.drinker and s.beer=l.beer
              )
      

          select * from frequents join serves 
            where (drinker, beer) not in (select drinker, beer from likes)
      

The second query will be much faster, since the subquery is not correlated. The subquery will only be executed once (while in the "classic" spelling, it will be executed for every row in the outer query).

DML Statements

The UPDATE statement allows (per the SQL standard) SET col=subquery. I have never tested this, and wouldn't be surprised if it didn't work properly. I would never use this in SQL, because it's too fragile (the subquery must return exactly one row; any other result is, in effect, a runtime syntax error - yech).

The INSERT statement allows subqueries:

          INSERT INTO boogle (a, b) SELECT snarf, gargle FROM snatch
      

The DELETE statement should hold no surprises.

DDL Statements

Of the DDL statements, only CREATE TABLE is recognized.

createstmt ::= CREATE TABLE tablenm ( defitem, ... )
defitem ::= { coldef | tblconstraint }
coldef ::= name type [size] [ colconstraint ]

The optional size parameter on a column definition (which uses literal [...]) is ignored - MetaKit doesn't care.

colconstraint ::= { UNIQUE | PRIMARY KEY | FOREIGN KEY REFERENCES table (column) | CHECK "code" }

A CHECK constraint is specified as source (a lambda, taking one arg - the value being checked). The double quote was chosen as delimiter because there should be no need to use double quotes within the Python code. If you try (even escaping the double quote), it won't work.

tblconstaint ::= { UNIQUE (column, ...) | PRIMARY KEY (column, ...) | FOREIGN KEY (column, ...) REFERENCES table (column, ...) }

In both column and table constraints, PRIMARY KEY is treated exactly like UNIQUE. This may change in a future version and be used to give the underlying view a native order, but not yet.

Metakit Extensions

[0b1] If you are querying an existing Metakit database, MkSQL can now access subviews. Note that you cannot create or update them in any way (only SELECT statements understand anything about subviews).

SELECT .... FROM FLATTEN(view.subview) will do a Metakit view.flatten(subview) (think of the opposite of GROUP BY) to make the structured view into a flat view.

In addition, a subquery's FROM may reference a subview attribute (either as subviewname or view.subview) of a view referenced by the outer query.