fix

2 files changed, 694 insertions, 159 deletions

diff --git a/lib/pure/parsesql.nim b/lib/pure/parsesql.nim
index 6891e2ff7..b53f46f82 100644
--- a/lib/pure/parsesql.nim
+++ b/lib/pure/parsesql.nim
@@ -462,27 +462,27 @@ proc errorStr(L: SqlLexer, msg: string): string =
 
 # ----------------------------- parser ----------------------------------------
 
-# Operator/Element	Associativity	Description
-# .	                left    	table/column name separator
-# ::            	left	        PostgreSQL-style typecast
-# [ ]	                left    	array element selection
-# -	                right	        unary minus
-# ^                     left	        exponentiation
-# * / %	                left	        multiplication, division, modulo
-# + -	                left	        addition, subtraction
-# IS	 	IS TRUE, IS FALSE, IS UNKNOWN, IS NULL
-# ISNULL	 	test for null
-# NOTNULL	 	test for not null
-# (any other)	        left    	all other native and user-defined oprs
-# IN	          	set membership
-# BETWEEN	 	range containment
-# OVERLAPS	 	time interval overlap
-# LIKE ILIKE SIMILAR	 	string pattern matching
-# < >	 	less than, greater than
-# =	                right	        equality, assignment
-# NOT	                right	        logical negation
-# AND	                left	        logical conjunction
-# OR              	left	        logical disjunction
+# Operator/Element   Associativity  Description
+# .                  left           table/column name separator
+# ::                 left           PostgreSQL-style typecast
+# [ ]                left           array element selection
+# -                  right          unary minus
+# ^                  left           exponentiation
+# * / %              left           multiplication, division, modulo
+# + -                left           addition, subtraction
+# IS                                IS TRUE, IS FALSE, IS UNKNOWN, IS NULL
+# ISNULL                            test for null
+# NOTNULL                           test for not null
+# (any other)        left           all other native and user-defined oprs
+# IN                                set membership
+# BETWEEN                           range containment
+# OVERLAPS                          time interval overlap
+# LIKE ILIKE SIMILAR                string pattern matching
+# < >                               less than, greater than
+# =                  right          equality, assignment
+# NOT                right          logical negation
+# AND                left           logical conjunction
+# OR                 left           logical disjunction
 
 type
   SqlNodeKind* = enum ## kind of SQL abstract syntax tree
@@ -518,11 +518,15 @@ type
     nkSelect,
     nkSelectDistinct,
     nkSelectColumns,
+    nkSelectPair,
     nkAsgn,
     nkFrom,
+    nkFromItemPair,
     nkGroup,
+    nkLimit,
     nkHaving,
     nkOrder,
+    nkJoin,
     nkDesc,
     nkUnion,
     nkIntersect,
@@ -670,6 +674,7 @@ proc getPrecedence(p: SqlParser): int =
     result = - 1
 
 proc parseExpr(p: var SqlParser): SqlNode
+proc parseSelect(p: var SqlParser): SqlNode
 
 proc identOrLiteral(p: var SqlParser): SqlNode =
   case p.tok.kind
@@ -921,6 +926,19 @@ proc parseWhere(p: var SqlParser): SqlNode =
   result = newNode(nkWhere)
   result.add(parseExpr(p))
 
+proc parseFromItem(p: var SqlParser): SqlNode =
+  result = newNode(nkFromItemPair)
+  if p.tok.kind == tkParLe:
+    getTok(p)
+    var select = parseSelect(p)
+    result.add(select)
+    eat(p, tkParRi)
+  else:
+    result.add(parseExpr(p))
+  if isKeyw(p, "as"):
+    getTok(p)
+    result.add(parseExpr(p))
+
 proc parseIndexDef(p: var SqlParser): SqlNode =
   result = parseIfNotExists(p, nkCreateIndex)
   if isKeyw(p, "primary"):
@@ -1019,7 +1037,12 @@ proc parseSelect(p: var SqlParser): SqlNode =
       a.add(newNode(nkIdent, "*"))
       getTok(p)
     else:
-      a.add(parseExpr(p))
+      var pair = newNode(nkSelectPair)
+      pair.add(parseExpr(p))
+      a.add(pair)
+      if isKeyw(p, "as"):
+        getTok(p)
+        pair.add(parseExpr(p))
     if p.tok.kind != tkComma: break
     getTok(p)
   result.add(a)
@@ -1027,7 +1050,7 @@ proc parseSelect(p: var SqlParser): SqlNode =
     var f = newNode(nkFrom)
     while true:
       getTok(p)
-      f.add(parseExpr(p))
+      f.add(parseFromItem(p))
       if p.tok.kind != tkComma: break
     result.add(f)
   if isKeyw(p, "where"):
@@ -1041,6 +1064,11 @@ proc parseSelect(p: var SqlParser): SqlNode =
       if p.tok.kind != tkComma: break
       getTok(p)
     result.add(g)
+  if isKeyw(p, "limit"):
+    getTok(p)
+    var l = newNode(nkLimit)
+    l.add(parseExpr(p))
+    result.add(l)
   if isKeyw(p, "having"):
     var h = newNode(nkHaving)
     while true:
@@ -1073,6 +1101,18 @@ proc parseSelect(p: var SqlParser): SqlNode =
       if p.tok.kind != tkComma: break
       getTok(p)
     result.add(n)
+  if isKeyw(p, "join") or isKeyw(p, "inner") or isKeyw(p, "outer") or isKeyw(p, "cross"):
+    var join = newNode(nkJoin)
+    result.add(join)
+    if isKeyw(p, "join"):
+      join.add(newNode(nkIdent, ""))
+      getTok(p)
+    else:
+      join.add(parseExpr(p))
+      eat(p, "join")
+    join.add(parseFromItem(p))
+    eat(p, "on")
+    join.add(parseExpr(p))
 
 proc parseStmt(p: var SqlParser; parent: SqlNode) =
   if isKeyw(p, "create"):
@@ -1104,7 +1144,7 @@ proc parseStmt(p: var SqlParser; parent: SqlNode) =
   elif isKeyw(p, "begin"):
     getTok(p)
   else:
-    sqlError(p, "CREATE expected")
+    sqlError(p, "SELECT, CREATE, UPDATE or DELETE expected")
 
 proc open(p: var SqlParser, input: Stream, filename: string) =
   ## opens the parser `p` and assigns the input stream `input` to it.
@@ -1120,6 +1160,8 @@ proc parse(p: var SqlParser): SqlNode =
   result = newNode(nkStmtList)
   while p.tok.kind != tkEof:
     parseStmt(p, result)
+    if p.tok.kind == tkEof:
+      break
     eat(p, tkSemicolon)
   if result.len == 1:
     result = result.sons[0]
@@ -1139,19 +1181,69 @@ proc parseSQL*(input: Stream, filename: string): SqlNode =
   finally:
     close(p)
 
-proc ra(n: SqlNode, s: var string, indent: int)
+proc parseSQL*(input: string, filename=""): SqlNode =
+  ## parses the SQL from `input` into an AST and returns the AST.
+  ## `filename` is only used for error messages.
+  ## Syntax errors raise an `EInvalidSql` exception.
+  parseSQL(newStringStream(input), "")
 
-proc rs(n: SqlNode, s: var string, indent: int,
-        prefix = "(", suffix = ")",
-        sep = ", ") =
+
+type
+  SqlWriter = object
+    indent: int
+    upperCase: bool
+    buffer: string
+
+proc add(s: var SqlWriter, thing: string) =
+  s.buffer.add(thing)
+
+proc add(s: var SqlWriter, thing: char) =
+  s.buffer.add(thing)
+
+proc addKeyw(s: var SqlWriter, thing: string) =
+  if s.buffer.len > 0 and s.buffer[^1] notin " ,\L(":
+    s.buffer.add(" ")
+  if s.upperCase:
+    s.buffer.add(thing.toUpper())
+  else:
+    s.buffer.add(thing)
+  s.buffer.add(" ")
+
+proc rm(s: var SqlWriter, chars = " \L,") =
+  while s.buffer[^1] in chars:
+    s.buffer = s.buffer[0..^2]
+
+proc newLine(s: var SqlWriter) =
+  s.rm(" \L")
+  s.buffer.add("\L")
+  for i in 0..<s.indent:
+    s.buffer.add("  ")
+
+template inner(s: var SqlWriter, body: untyped) =
+  inc s.indent
+  s.newLine()
+  body
+  dec s.indent
+
+template innerKeyw(s: var SqlWriter, keyw: string, body: untyped) =
+  s.newLine()
+  s.addKeyw(keyw)
+  inc s.indent
+  s.newLine()
+  body
+  dec s.indent
+
+proc ra(n: SqlNode, s: var SqlWriter)
+
+proc rs(n: SqlNode, s: var SqlWriter, prefix = "(", suffix = ")", sep = ", ") =
   if n.len > 0:
     s.add(prefix)
     for i in 0 .. n.len-1:
       if i > 0: s.add(sep)
-      ra(n.sons[i], s, indent)
+      ra(n.sons[i], s)
     s.add(suffix)
 
-proc ra(n: SqlNode, s: var string, indent: int) =
+proc ra(n: SqlNode, s: var SqlWriter) =
   if n == nil: return
   case n.kind
   of nkNone: discard
@@ -1169,217 +1261,222 @@ proc ra(n: SqlNode, s: var string, indent: int) =
   of nkIntegerLit, nkNumericLit:
     s.add(n.strVal)
   of nkPrimaryKey:
-    s.add(" primary key")
-    rs(n, s, indent)
+    s.addKeyw("primary key")
+    rs(n, s)
   of nkForeignKey:
-    s.add(" foreign key")
-    rs(n, s, indent)
+    s.addKeyw("foreign key")
+    rs(n, s)
   of nkNotNull:
-    s.add(" not null")
+    s.addKeyw("not null")
   of nkNull:
-    s.add(" null")
+    s.addKeyw("null")
   of nkDot:
-    ra(n.sons[0], s, indent)
+    ra(n.sons[0], s)
     s.add(".")
-    ra(n.sons[1], s, indent)
+    ra(n.sons[1], s)
   of nkDotDot:
-    ra(n.sons[0], s, indent)
+    ra(n.sons[0], s)
     s.add(". .")
-    ra(n.sons[1], s, indent)
+    ra(n.sons[1], s)
   of nkPrefix:
     s.add('(')
-    ra(n.sons[0], s, indent)
+    ra(n.sons[0], s)
     s.add(' ')
-    ra(n.sons[1], s, indent)
+    ra(n.sons[1], s)
     s.add(')')
   of nkInfix:
     s.add('(')
-    ra(n.sons[1], s, indent)
+    ra(n.sons[1], s)
     s.add(' ')
-    ra(n.sons[0], s, indent)
+    ra(n.sons[0], s)
     s.add(' ')
-    ra(n.sons[2], s, indent)
+    ra(n.sons[2], s)
     s.add(')')
   of nkCall, nkColumnReference:
-    ra(n.sons[0], s, indent)
+    ra(n.sons[0], s)
     s.add('(')
     for i in 1..n.len-1:
       if i > 1: s.add(", ")
-      ra(n.sons[i], s, indent)
+      ra(n.sons[i], s)
     s.add(')')
   of nkReferences:
-    s.add(" references ")
-    ra(n.sons[0], s, indent)
+    s.addKeyw("references")
+    ra(n.sons[0], s)
   of nkDefault:
-    s.add(" default ")
-    ra(n.sons[0], s, indent)
+    s.addKeyw("default")
+    ra(n.sons[0], s)
   of nkCheck:
-    s.add(" check ")
-    ra(n.sons[0], s, indent)
+    s.addKeyw("check")
+    ra(n.sons[0], s)
   of nkConstraint:
-    s.add(" constraint ")
-    ra(n.sons[0], s, indent)
-    s.add(" check ")
-    ra(n.sons[1], s, indent)
+    s.addKeyw("constraint")
+    ra(n.sons[0], s)
+    s.addKeyw("check")
+    ra(n.sons[1], s)
   of nkUnique:
-    s.add(" unique")
-    rs(n, s, indent)
+    s.addKeyw("unique")
+    rs(n, s)
   of nkIdentity:
-    s.add(" identity")
+    s.addKeyw("identity")
   of nkColumnDef:
     s.add("\n  ")
-    rs(n, s, indent, "", "", " ")
+    rs(n, s, "", "", " ")
   of nkStmtList:
     for i in 0..n.len-1:
-      ra(n.sons[i], s, indent)
+      ra(n.sons[i], s)
       s.add("\n")
   of nkInsert:
     assert n.len == 3
-    s.add("insert into ")
-    ra(n.sons[0], s, indent)
-    ra(n.sons[1], s, indent)
+    s.addKeyw("insert into")
+    ra(n.sons[0], s)
+    ra(n.sons[1], s)
     if n.sons[2].kind == nkDefault:
-      s.add("default values")
+      s.addKeyw("default values")
     else:
-      s.add("\n")
-      ra(n.sons[2], s, indent)
+      s.newLine()
+      ra(n.sons[2], s)
     s.add(';')
   of nkUpdate:
-    s.add("update ")
-    ra(n.sons[0], s, indent)
-    s.add(" set ")
+    s.addKeyw("update")
+    ra(n.sons[0], s)
+    s.addKeyw("set")
     var L = n.len
     for i in 1 .. L-2:
       if i > 1: s.add(", ")
       var it = n.sons[i]
       assert it.kind == nkAsgn
-      ra(it, s, indent)
-    ra(n.sons[L-1], s, indent)
+      ra(it, s)
+    ra(n.sons[L-1], s)
     s.add(';')
   of nkDelete:
-    s.add("delete from ")
-    ra(n.sons[0], s, indent)
-    ra(n.sons[1], s, indent)
+    s.addKeyw("delete from")
+    ra(n.sons[0], s)
+    ra(n.sons[1], s)
     s.add(';')
   of nkSelect, nkSelectDistinct:
-    s.add("select ")
+    s.addKeyw("select")
     if n.kind == nkSelectDistinct:
-      s.add("distinct ")
-    rs(n.sons[0], s, indent, "", "", ", ")
-    for i in 1 .. n.len-1: ra(n.sons[i], s, indent)
+      s.addKeyw("distinct")
+    s.inner:
+      for son in n.sons[0].sons:
+        ra(son, s)
+        s.add(',')
+        s.newLine()
+      s.rm()
+    for i in 1 .. n.len-1:
+      ra(n.sons[i], s)
     s.add(';')
   of nkSelectColumns:
     assert(false)
+  of nkSelectPair:
+    ra(n.sons[0], s)
+    if n.sons.len == 2:
+      s.addKeyw("as")
+      ra(n.sons[1], s)
+  of nkFromItemPair:
+    if n.sons[0].kind == nkIdent:
+      ra(n.sons[0], s)
+    else:
+      assert n.sons[0].kind == nkSelect
+      s.add("(")
+      s.inner:
+        ra(n.sons[0], s)
+      s.rm("; \L")
+      s.newLine()
+      s.add(")")
+    if n.sons.len == 2:
+      s.addKeyw("as")
+      ra(n.sons[1], s)
   of nkAsgn:
-    ra(n.sons[0], s, indent)
+    ra(n.sons[0], s)
     s.add(" = ")
-    ra(n.sons[1], s, indent)
+    ra(n.sons[1], s)
   of nkFrom:
-    s.add("\nfrom ")
-    rs(n, s, indent, "", "", ", ")
+    s.innerKeyw("from"):
+      rs(n, s, "", "", ", ")
   of nkGroup:
-    s.add("\ngroup by")
-    rs(n, s, indent, "", "", ", ")
+    s.innerKeyw("group by"):
+      rs(n, s, "", "", ", ")
+  of nkLimit:
+    s.innerKeyw("limit"):
+      rs(n, s, "", "", ", ")
   of nkHaving:
-    s.add("\nhaving")
-    rs(n, s, indent, "", "", ", ")
+    s.innerKeyw("having"):
+      rs(n, s, "", "", ", ")
   of nkOrder:
-    s.add("\norder by ")
-    rs(n, s, indent, "", "", ", ")
+    s.addKeyw("order by")
+    rs(n, s, "", "", ", ")
+  of nkJoin:
+    var joinType = n.sons[0].strVal
+    if joinType == "":
+      joinType = "join"
+    else:
+      joinType &= " " & "join"
+    s.innerKeyw(joinType):
+      ra(n.sons[1], s)
+    s.innerKeyw("on"):
+      ra(n.sons[2], s)
   of nkDesc:
-    ra(n.sons[0], s, indent)
-    s.add(" desc")
+    ra(n.sons[0], s)
+    s.addKeyw("desc")
   of nkUnion:
-    s.add(" union")
+    s.addKeyw("union")
   of nkIntersect:
-    s.add(" intersect")
+    s.addKeyw("intersect")
   of nkExcept:
-    s.add(" except")
+    s.addKeyw("except")
   of nkColumnList:
-    rs(n, s, indent)
+    rs(n, s)
   of nkValueList:
-    s.add("values ")
-    rs(n, s, indent)
+    s.addKeyw("values")
+    rs(n, s)
   of nkWhere:
-    s.add("\nwhere ")
-    ra(n.sons[0], s, indent)
+    s.newLine()
+    s.addKeyw("where")
+    s.inner:
+      ra(n.sons[0], s)
   of nkCreateTable, nkCreateTableIfNotExists:
-    s.add("create table ")
+    s.addKeyw("create table")
     if n.kind == nkCreateTableIfNotExists:
-      s.add("if not exists ")
-    ra(n.sons[0], s, indent)
+      s.addKeyw("if not exists")
+    ra(n.sons[0], s)
     s.add('(')
     for i in 1..n.len-1:
-      if i > 1: s.add(", ")
-      ra(n.sons[i], s, indent)
+      if i > 1: s.add(",")
+      ra(n.sons[i], s)
     s.add(");")
   of nkCreateType, nkCreateTypeIfNotExists:
-    s.add("create type ")
+    s.addKeyw("create type")
     if n.kind == nkCreateTypeIfNotExists:
-      s.add("if not exists ")
-    ra(n.sons[0], s, indent)
-    s.add(" as ")
-    ra(n.sons[1], s, indent)
+      s.addKeyw("if not exists")
+    ra(n.sons[0], s)
+    s.addKeyw("as")
+    ra(n.sons[1], s)
     s.add(';')
   of nkCreateIndex, nkCreateIndexIfNotExists:
-    s.add("create index ")
+    s.addKeyw("create index")
     if n.kind == nkCreateIndexIfNotExists:
-      s.add("if not exists ")
-    ra(n.sons[0], s, indent)
-    s.add(" on ")
-    ra(n.sons[1], s, indent)
+      s.addKeyw("if not exists")
+    ra(n.sons[0], s)
+    s.addKeyw("on")
+    ra(n.sons[1], s)
     s.add('(')
     for i in 2..n.len-1:
       if i > 2: s.add(", ")
-      ra(n.sons[i], s, indent)
+      ra(n.sons[i], s)
     s.add(");")
   of nkEnumDef:
-    s.add("enum ")
-    rs(n, s, indent)
+    s.addKeyw("enum")
+    rs(n, s)
 
-# What I want:
-#
-#select(columns = [T1.all, T2.name],
-#       fromm = [T1, T2],
-#       where = T1.name ==. T2.name,
-#       orderby = [name]):
-#
-#for row in dbQuery(db, """select x, y, z
-#                          from a, b
-#                          where a.name = b.name"""):
-#
-
-#select x, y, z:
-#  fromm: Table1, Table2
-#  where: x.name == y.name
-#db.select(fromm = [t1, t2], where = t1.name == t2.name):
-#for x, y, z in db.select(fromm = a, b where = a.name == b.name):
-#  writeLine x, y, z
-
-proc renderSQL*(n: SqlNode): string =
+proc renderSQL*(n: SqlNode, upperCase=false): string =
   ## Converts an SQL abstract syntax tree to its string representation.
-  result = ""
-  ra(n, result, 0)
+  var s: SqlWriter
+  s.buffer = ""
+  s.upperCase = upperCase
+  ra(n, s)
+  return s.buffer
 
 proc `$`*(n: SqlNode): string =
   ## an alias for `renderSQL`.
   renderSQL(n)
-
-when not defined(testing) and isMainModule:
-  echo(renderSQL(parseSQL(newStringStream("""
-      CREATE TYPE happiness AS ENUM ('happy', 'very happy', 'ecstatic');
-      CREATE TABLE holidays (
-         num_weeks int,
-         happiness happiness
-      );
-      CREATE INDEX table1_attr1 ON table1(attr1);
-
-      SELECT * FROM myTab WHERE col1 = 'happy';
-  """), "stdin")))
-
-# CREATE TYPE happiness AS ENUM ('happy', 'very happy', 'ecstatic');
-# CREATE TABLE holidays (
-#    num_weeks int,
-#    happiness happiness
-# );
-# CREATE INDEX table1_attr1 ON table1(attr1)
diff --git a/tests/stdlib/tparsesql.nim b/tests/stdlib/tparsesql.nim
new file mode 100644
index 000000000..e73a1d7ee
--- /dev/null
+++ b/tests/stdlib/tparsesql.nim
@@ -0,0 +1,438 @@
+import unittest
+
+import sequtils
+import strutils
+import parsesql
+
+proc fold(str: string): string =
+  var
+    lines = str.split("\L")
+    minCount = 1000
+  while lines.len > 0 and lines[0].strip().len == 0:
+    lines.delete(0, 0)
+  while lines.len > 0 and lines[lines.len-1].strip().len == 0:
+    lines.delete(lines.len, lines.len)
+  for line in lines:
+    var count = 0
+    while line[count] == ' ':
+      inc count
+    if minCount > count:
+      minCount = count
+  for i, line in lines:
+    lines[i] = line[minCount..^1]
+  return lines.join("\L")
+
+proc parseCheck(have: string, need: string) =
+  var
+    sql = parseSQL(have)
+    sqlHave = renderSQL(sql, true).strip()
+    sqlNeed = need.fold().strip()
+  var
+    haveLines = sqlHave.split("\L")
+    needLines = sqlNeed.split("\L")
+  for i in 0..<haveLines.len:
+    if haveLines[i] != needLines[i]:
+      echo ""
+      echo " --- have --- "
+      echo sqlHave
+      #echo repr(sql)
+      echo " --- need --- "
+      echo sqlNeed
+      echo " --- lines --- "
+      echo repr haveLines[i]
+      echo repr needLines[i]
+      echo "line: ", i
+      raise newException(Exception, "Two don't equal.")
+
+
+suite "sql":
+
+  test "basic":
+    parseCheck "SELECT foo FROM table;", """
+      SELECT
+        foo
+      FROM
+        table;
+    """
+
+  test "dont require ; at the end":
+    parseCheck "SELECT foo FROM table", """
+      SELECT
+        foo
+      FROM
+        table;
+    """
+
+  test "limit":
+    parseCheck "SELECT foo FROM table limit 10", """
+      SELECT
+        foo
+      FROM
+        table
+      LIMIT
+        10;
+    """
+
+  test "fields":
+    parseCheck "SELECT foo, bar, baz FROM table limit 10", """
+      SELECT
+        foo,
+        bar,
+        baz
+      FROM
+        table
+      LIMIT
+        10;
+    """
+
+  test "as_field":
+    parseCheck "SELECT foo AS bar FROM table", """
+      SELECT
+        foo AS bar
+      FROM
+        table;
+    """
+
+    parseCheck "SELECT foo AS foo_prime, bar AS bar_prime, baz AS baz_prime FROM table", """
+      SELECT
+        foo AS foo_prime,
+        bar AS bar_prime,
+        baz AS baz_prime
+      FROM
+        table;
+    """
+
+  test "select *":
+    parseCheck "SELECT * FROM table", """
+      SELECT
+        *
+      FROM
+        table;
+    """
+    # TODO: COUNT(*)
+    #parseCheck "SELECT COUNT(*) FROM table", """
+    #  SELECT *
+    #  FROM table;
+    #"""
+
+  test "where":
+    parseCheck """
+      SELECT * FROM table
+      WHERE a = b and c = d
+    """, """
+      SELECT
+        *
+      FROM
+        table
+      WHERE
+        ((a = b) and (c = d));
+    """
+
+    parseCheck """
+      SELECT * FROM table
+      WHERE not b
+    """, """
+      SELECT
+        *
+      FROM
+        table
+      WHERE
+        (not b);
+    """
+
+    parseCheck """
+      SELECT
+        *
+      FROM
+        table
+      WHERE
+        a and not b
+    """, """
+      SELECT
+        *
+      FROM
+        table
+      WHERE
+        (a and (not b));
+    """
+
+    parseCheck """
+      SELECT *
+      FROM table
+      WHERE not a and not b
+    """, """
+      SELECT
+        *
+      FROM
+        table
+      WHERE
+        ((not a) and (not b));
+    """
+
+    parseCheck """
+      SELECT * FROM table
+      WHERE a = b and c = d or n is null and not b + 1 = 3
+    """, """
+      SELECT
+        *
+      FROM
+        table
+      WHERE
+        (((a = b) and (c = d)) or ((n is null) and (((not b) + 1) = 3)));
+    """
+
+  test "having":
+    parseCheck """
+      SELECT * FROM table
+      HAVING a = b and c = d
+    """, """
+      SELECT
+        *
+      FROM
+        table
+      HAVING
+        ((a = b) and (c = d));
+    """
+
+  test "group by":
+    parseCheck """
+      SELECT a, b FROM table
+      GROUP BY a
+    """, """
+      SELECT
+        a,
+        b
+      FROM
+        table
+      GROUP BY
+        a;
+    """
+
+    parseCheck """
+      SELECT a, b FROM table
+      GROUP BY 1, 2
+    """, """
+      SELECT
+        a,
+        b
+      FROM
+        table
+      GROUP BY
+        1, 2;
+    """
+
+  test "as table":
+    parseCheck """
+      SELECT t.a FROM t as t
+    """, """
+      SELECT
+        t.a
+      FROM
+        t AS t;
+    """
+
+  test "subselects":
+    parseCheck """
+      SELECT a, b FROM (
+        SELECT * FROM t
+      )
+    """, """
+      SELECT
+        a,
+        b
+      FROM
+        (
+          SELECT
+            *
+          FROM
+            t
+        );
+    """
+
+    parseCheck """
+      SELECT a, b FROM (
+        SELECT * FROM t
+      ) as foo
+    """, """
+      SELECT
+        a,
+        b
+      FROM
+        (
+          SELECT
+            *
+          FROM
+            t
+        ) AS foo;
+    """
+
+    parseCheck """
+      SELECT a, b FROM (
+        SELECT * FROM (
+          SELECT * FROM (
+            SELECT * FROM (
+              SELECT * FROM inner as inner1
+            ) as inner2
+          ) as inner3
+        ) as inner4
+      ) as inner5
+    """, """
+      SELECT
+        a,
+        b
+      FROM
+        (
+          SELECT
+            *
+          FROM
+            (
+              SELECT
+                *
+              FROM
+                (
+                  SELECT
+                    *
+                  FROM
+                    (
+                      SELECT
+                        *
+                      FROM
+                        inner AS inner1
+                    ) AS inner2
+                ) AS inner3
+            ) AS inner4
+        ) AS inner5;
+    """
+
+    parseCheck """
+      SELECT a, b FROM
+        (SELECT * FROM a),
+        (SELECT * FROM b),
+        (SELECT * FROM c)
+    ""","""
+      SELECT
+        a,
+        b
+      FROM
+        (
+          SELECT
+            *
+          FROM
+            a
+        ), (
+          SELECT
+            *
+          FROM
+            b
+        ), (
+          SELECT
+            *
+          FROM
+            c
+        );
+    """
+
+  test "original test":
+    parseCheck """
+      CREATE TYPE happiness AS ENUM ('happy', 'very happy', 'ecstatic');
+      CREATE TABLE holidays (
+         num_weeks int,
+         happiness happiness
+      );
+      CREATE INDEX table1_attr1 ON table1(attr1);
+
+      SELECT * FROM myTab WHERE col1 = 'happy';
+    """, """
+      CREATE TYPE happiness AS ENUM ('happy', 'very happy', 'ecstatic');
+      CREATE TABLE holidays(
+        num_weeks int,
+        happiness happiness);
+      CREATE INDEX table1_attr1 ON table1(attr1);
+      SELECT
+        *
+      FROM
+        myTab
+      WHERE
+        (col1 = 'happy');
+    """
+
+  test "joins":
+    parseCheck """
+      SELECT id FROM a
+      JOIN b
+      ON a.id == b.id
+    """, """
+      SELECT
+        id
+      FROM
+        a
+      JOIN
+        b
+      ON
+        (a.id == b.id);
+    """
+
+    parseCheck """
+      SELECT id FROM a
+      JOIN (SELECT id from c) as b
+      ON a.id == b.id
+    """, """
+      SELECT
+        id
+      FROM
+        a
+      JOIN
+        (
+          SELECT
+            id
+          FROM
+            c
+        ) AS b
+      ON
+        (a.id == b.id);
+    """
+
+    parseCheck """
+      SELECT id FROM a
+      INNER JOIN b
+      ON a.id == b.id
+    """, """
+      SELECT
+        id
+      FROM
+        a
+      INNER JOIN
+        b
+      ON
+        (a.id == b.id);
+    """
+
+    parseCheck """
+      SELECT id FROM a
+      OUTER JOIN b
+      ON a.id == b.id
+    """, """
+      SELECT
+        id
+      FROM
+        a
+      OUTER JOIN
+        b
+      ON
+        (a.id == b.id);
+    """
+
+    parseCheck """
+      SELECT id FROM a
+      CROSS JOIN b
+      ON a.id == b.id
+    """, """
+      SELECT
+        id
+      FROM
+        a
+      CROSS JOIN
+        b
+      ON
+        (a.id == b.id);
+    """