SELECT id FROM artists WHERE id > 100

versus

SELECT id FROM artists WHERE (id > 100)

Notice that the first image shows where_clause on the edge, while the second shows where_clause[0]. Not sure if there is ever a use-case for multiple WHERE's

e.g.

SELECT a FROM b WHERE a NOT IN (1)

cc @colinricardo , should just require adding a single word. If all goes well will deploy by EOD tomorrow.

From the table_ref rule, antlr thinks that INNER is the table alias (e.g. a INNER). I think the simplest way to fix this is to add a new matching rule to table_ref that matches a non-aliased table followed by a join.

https://github.com/datacamp/antlr-plsql/blob/master/antlr_plsql/plsql.g4#L955

from datacamp/sqlwhat#44

edit:
UNION is treated as compound statement, so for two select statements, a trailing ORDER BY is the outermost piece. It's probably okay for now, but the ORDER BY will incorrectly be part of the final SELECT statement. Example below.

SELECT a FROM b
UNION
SELECT x FROM y
ORDER BY c

the postgres ast is

  [{"SelectStmt"=>
     {"sortClause"=>
       [{"SortBy"=>
          {"node"=>
            {"ColumnRef"=>
              {"fields"=>[{"String"=>{"str"=>"c"}}], "location"=>47}},
           "sortby_dir"=>0,
           "sortby_nulls"=>0,
           "location"=>-1}}],
      "op"=>1,
      "larg"=>
       {"SelectStmt"=>
         {"targetList"=>
           [{"ResTarget"=>
              {"val"=>
                {"ColumnRef"=>
                  {"fields"=>[{"String"=>{"str"=>"a"}}], "location"=>7}},
               "location"=>7}}],
          "fromClause"=>
           [{"RangeVar"=>
              {"relname"=>"b",
               "inhOpt"=>2,
               "relpersistence"=>"p",
               "location"=>14}}],
          "op"=>0}},
      "rarg"=>
       {"SelectStmt"=>
         {"targetList"=>
           [{"ResTarget"=>
              {"val"=>
                {"ColumnRef"=>
                  {"fields"=>[{"String"=>{"str"=>"x"}}], "location"=>29}},
               "location"=>29}}],
          "fromClause"=>
           [{"RangeVar"=>
              {"relname"=>"y",
               "inhOpt"=>2,
               "relpersistence"=>"p",
               "location"=>36}}],
          "op"=>0}}}}],

Currently code with CASE..WHEN or subqueries in them seem to produce Unshaped nodes.

Case When Example

SELECT name, continent, code, surface_area,
    CASE WHEN surface_area > 2000000 THEN 'large'
       WHEN surface_area > 350000 THEN 'medium'
       ELSE 'small' END AS geosize_group
FROM countries;

Subquery Example

SELECT local_name, subquery.lang_num
FROM countries,
  (SELECT code, COUNT(*) AS lang_num
   FROM languages
   GROUP BY code) subquery
WHERE countries.code = subquery.code;

cc @ismayc

See this example on ast-viewer.datacamp.com.

A bunch of unshaped nodes.

cc @colinricardo

SELECT date FROM y chokes the parser, but SELECT date1 FROM y doesn't.

• Clearer steps on how to build and develop
• Instructions on how to run the ast-viewer locally

Look at this example. The alias of the inner join is parsed properly, but the tree representation turns it into an Unshaped node while it should be an AliasExpr.

@machow The plsql.g4 file doesn't need changing for this, I think, but the ast.py file does. Is this correct? Do you have time to pair on this at some point?

Yesterday, I updated antlr-plsql to create a Speaker instance from antlr_plsql/speaker.yml. However, because I didn't tell the package to contain the speaker.yml file, it did not include it in the PyPI release. This caused the library to raise an error on import.

Unfortunately, because the unit tests passed on every step leading up to release, and the exercise validator isn't testing courses-intro-sql yet, I didn't notice until people were testing course exercises.

It might not be worth the extra effort, but before deploying I can pin to courses-intro-sql to make sure the exercises pass.

Moved from datacamp/sqlwhat#45

For example, standard_function often allows an argument to be concatenation (which is now just another name for binary_expression). E.g.

COUNT '(' ( '*' | (DISTINCT | UNIQUE | ALL)? concatenation) ')' over_clause?

However, the official postgres parser allows any expression, rather than just concatentation. E.g.

SELECT COUNT( a AND b) FROM x

SELECT a INTO b FROM c currently generates this AST

cc @ismayc