{"action":"created","issue":{"url":"https://api.github.com/repos/cockroachdb/cockroach/issues/1670","labels_url":"https://api.github.com/repos/cockroachdb/cockroach/issues/1670/labels{/name}","comments_url":"https://api.github.com/repos/cockroachdb/cockroach/issues/1670/comments","events_url":"https://api.github.com/repos/cockroachdb/cockroach/issues/1670/events","html_url":"https://github.com/cockroachdb/cockroach/issues/1670","id":94800749,"number":1670,"title":"sql/parser: replace vitess grammar with postgres grammar","user":{"login":"petermattis","id":6201335,"avatar_url":"https://avatars.githubusercontent.com/u/6201335?v=3","gravatar_id":"","url":"https://api.github.com/users/petermattis","html_url":"https://github.com/petermattis","followers_url":"https://api.github.com/users/petermattis/followers","following_url":"https://api.github.com/users/petermattis/following{/other_user}","gists_url":"https://api.github.com/users/petermattis/gists{/gist_id}","starred_url":"https://api.github.com/users/petermattis/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/petermattis/subscriptions","organizations_url":"https://api.github.com/users/petermattis/orgs","repos_url":"https://api.github.com/users/petermattis/repos","events_url":"https://api.github.com/users/petermattis/events{/privacy}","received_events_url":"https://api.github.com/users/petermattis/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"milestone":null,"comments":1,"created_at":"2015-07-13T20:12:10Z","updated_at":"2015-07-13T21:40:49Z","closed_at":null,"body":"TL;DR? I think we should replace the vitess SQL grammar with a trimmed down postgres SQL grammar.\r\n\r\nWhile working on SQL expression evaluation I started running into the limitations of the vitess SQL parser: incorrect handling of booleans, forcing boolean expressions where value expressions are allowed, etc. The short summary is that vitess parses a subset of MySQL. Out of the box it didn't handle various DDL statements such as CREATE and ALTER. I've made a number of fixes to the grammar, but I believe this is a game of whack-a-mole and that it is time to switch to a better base.\r\n\r\nThe [Postgres SQL grammar](https://github.com/postgres/postgres/blob/master/src/backend/parser/gram.y) weighs in at a hefty 14,850 lines. Interestingly, this is fairly close to the size of the [MySQL grammar](http://osxr.org/mysql/source/sql/sql_yacc.yy). Strip out the C code (used for semantic actions) and the Postgres grammar drops down to 5,658 lines. The grammar includes a lot of Postgres extensions (such as the ability to [create new types](http://www.postgresql.org/docs/9.4/static/sql-createtype.html) and [operators](http://www.postgresql.org/docs/9.4/static/sql-createoperator.html)) which can also be trimmed to bring the grammar down to ~3000 lines of code.\r\n\r\nWhy Postgres vs MySQL? MySQL is certainly the more popular database, but Postgres attempts to follow the SQL standard. It is very unlikely that our brand of SQL will be 100% compatible with either Postgres or MySQL. We certainly don't want to implement all of the crazy Postgres extensions (see custom types and operators) not do we want to limit ourselves to their SQL (e.g. we almost certainly want interleaved indexes). That said, it seems preferable to start with a base that pays heed to the standards.\r\n\r\nThe Postgres grammar is mostly compatible with [our syntax tree](https://github.com/cockroachdb/cockroach/blob/master/sql/parser/ast.go), though we'll definitely need to extend the structures in certain places. We will also have to replace the tokenizer. But the vast majority of the work will be in writing new semantic actions for the grammar rules. The most unfortunate irritation of switching to the Postgres grammar is that the go yacc tool has a hardcoded limit of [2000 states](https://github.com/golang/go/blob/master/src/cmd/yacc/yacc.go#L64). Even after the trimming mentioned above the grammar contains 2200+ states. It's conceivable that further trimming will get this under 2000 states, but in the meantime the easiest solution is to fork the yacc tool and adjust the hardcoded values.\r\n\r\nI spent last week exploring the feasibility of the proposed switch, wrapping my head around the postgres grammar and tokenizer. I'm confident the switch is feasible in a short time frame and will save us lots of headaches down the road."},"comment":{"url":"https://api.github.com/repos/cockroachdb/cockroach/issues/comments/121068677","html_url":"https://github.com/cockroachdb/cockroach/issues/1670#issuecomment-121068677","issue_url":"https://api.github.com/repos/cockroachdb/cockroach/issues/1670","id":121068677,"user":{"login":"spencerkimball","id":6147149,"avatar_url":"https://avatars.githubusercontent.com/u/6147149?v=3","gravatar_id":"","url":"https://api.github.com/users/spencerkimball","html_url":"https://github.com/spencerkimball","followers_url":"https://api.github.com/users/spencerkimball/followers","following_url":"https://api.github.com/users/spencerkimball/following{/other_user}","gists_url":"https://api.github.com/users/spencerkimball/gists{/gist_id}","starred_url":"https://api.github.com/users/spencerkimball/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/spencerkimball/subscriptions","organizations_url":"https://api.github.com/users/spencerkimball/orgs","repos_url":"https://api.github.com/users/spencerkimball/repos","events_url":"https://api.github.com/users/spencerkimball/events{/privacy}","received_events_url":"https://api.github.com/users/spencerkimball/received_events","type":"User","site_admin":false},"created_at":"2015-07-13T21:40:49Z","updated_at":"2015-07-13T21:40:49Z","body":"I'm fine with this. Can you give a couple of examples of pointed\ndifferences between the two grammars? Are people going to be stumbling over\nthem?\n\nOn Mon, Jul 13, 2015 at 4:12 PM, Peter Mattis <notifications@github.com>\nwrote:\n\n> TL;DR? I think we should replace the vitess SQL grammar with a trimmed\n> down postgres SQL grammar.\n>\n> While working on SQL expression evaluation I started running into the\n> limitations of the vitess SQL parser: incorrect handling of booleans,\n> forcing boolean expressions where value expressions are allowed, etc. The\n> short summary is that vitess parses a subset of MySQL. Out of the box it\n> didn't handle various DDL statements such as CREATE and ALTER. I've made a\n> number of fixes to the grammar, but I believe this is a game of\n> whack-a-mole and that it is time to switch to a better base.\n>\n> The Postgres SQL grammar\n> <https://github.com/postgres/postgres/blob/master/src/backend/parser/gram.y>\n> weighs in at a hefty 14,850 lines. Interestingly, this is fairly close to\n> the size of the MySQL grammar\n> <http://osxr.org/mysql/source/sql/sql_yacc.yy>. Strip out the C code\n> (used for semantic actions) and the Postgres grammar drops down to 5,658\n> lines. The grammar includes a lot of Postgres extensions (such as the\n> ability to create new types\n> <http://www.postgresql.org/docs/9.4/static/sql-createtype.html> and\n> operators\n> <http://www.postgresql.org/docs/9.4/static/sql-createoperator.html>)\n> which can also be trimmed to bring the grammar down to ~3000 lines of code.\n>\n> Why Postgres vs MySQL? MySQL is certainly the more popular database, but\n> Postgres attempts to follow the SQL standard. It is very unlikely that our\n> brand of SQL will be 100% compatible with either Postgres or MySQL. We\n> certainly don't want to implement all of the crazy Postgres extensions (see\n> custom types and operators) not do we want to limit ourselves to their SQL\n> (e.g. we almost certainly want interleaved indexes). That said, it seems\n> preferable to start with a base that pays heed to the standards.\n>\n> The Postgres grammar is mostly compatible with our syntax tree\n> <https://github.com/cockroachdb/cockroach/blob/master/sql/parser/ast.go>,\n> though we'll definitely need to extend the structures in certain places. We\n> will also have to replace the tokenizer. But the vast majority of the work\n> will be in writing new semantic actions for the grammar rules. The most\n> unfortunate irritation of switching to the Postgres grammar is that the go\n> yacc tool has a hardcoded limit of 2000 states\n> <https://github.com/golang/go/blob/master/src/cmd/yacc/yacc.go#L64>. Even\n> after the trimming mentioned above the grammar contains 2200+ states. It's\n> conceivable that further trimming will get this under 2000 states, but in\n> the meantime the easiest solution is to fork the yacc tool and adjust the\n> hardcoded values.\n>\n> I spent last week exploring the feasibility of the proposed switch,\n> wrapping my head around the postgres grammar and tokenizer. I'm confident\n> the switch is feasible in a short time frame and will save us lots of\n> headaches down the road.\n>\n> —\n> Reply to this email directly or view it on GitHub\n> <https://github.com/cockroachdb/cockroach/issues/1670>.\n>\n"}}