{"action":"opened","issue":{"url":"https://api.github.com/repos/facebook/presto/issues/4338","labels_url":"https://api.github.com/repos/facebook/presto/issues/4338/labels{/name}","comments_url":"https://api.github.com/repos/facebook/presto/issues/4338/comments","events_url":"https://api.github.com/repos/facebook/presto/issues/4338/events","html_url":"https://github.com/facebook/presto/issues/4338","id":126518507,"number":4338,"title":"Support for prepared statements","user":{"login":"rschlussel","id":10963789,"avatar_url":"https://avatars.githubusercontent.com/u/10963789?v=3","gravatar_id":"","url":"https://api.github.com/users/rschlussel","html_url":"https://github.com/rschlussel","followers_url":"https://api.github.com/users/rschlussel/followers","following_url":"https://api.github.com/users/rschlussel/following{/other_user}","gists_url":"https://api.github.com/users/rschlussel/gists{/gist_id}","starred_url":"https://api.github.com/users/rschlussel/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/rschlussel/subscriptions","organizations_url":"https://api.github.com/users/rschlussel/orgs","repos_url":"https://api.github.com/users/rschlussel/repos","events_url":"https://api.github.com/users/rschlussel/events{/privacy}","received_events_url":"https://api.github.com/users/rschlussel/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"milestone":null,"comments":0,"created_at":"2016-01-13T21:24:08Z","updated_at":"2016-01-13T21:24:08Z","closed_at":null,"body":"# Overview\r\nPrepared statements are a way to save query plans to reuse for other queries in the session.  They are a part of the sql standard and are used both through ODBC/JDBC drivers and in embedded sql for convenience and to save time in parsing and planning when a user wishes to run the same or similar queries many times in a row. \r\n\r\nWe'd like to add support for prepared statements in Presto.  See the plan below and let me know if you have any comments or suggestions.  Thanks!\r\n\r\n# PREPARE\r\nPrepares a plan for a query and saves it for use later in the same session.  The query can have parameters in it to replace literals that the user will fill in when executing the query\r\n\r\n## Syntax\r\nPREPARE \\<name\\> FROM \\<statement\\>  \r\nwhere parameters are represented by question marks \r\ne.g. \r\nPREPARE my-select FROM select n_name from nation where n_nationkey < ? AND n_regionkey = ?\r\n\r\n## Implementation\r\n* Add syntax to Parser.  \r\n* Add Parameter Expression Type\r\n     *  a parameter should have a monotonically increasing id per query (parameters for each query labeled 0,1,2) to ensure that on execution you know which parameters go where, even if the query has been rewritten\r\n     *  parameter should also have a datatype.\r\n* Add a map within the session that maps statement names to plans.  Potentially the map should also include input parameters and output column types for the prepared statement.\r\n*  In the plan, add a prepare PlanNode  that sits at  the top of the query plan.  \r\nPlan and optimize as usual.\r\nThen add the plan (nodes below prepare node) to the prepared statement map\r\n\r\n# DEALLOCATE\r\nRemoves a plan from your prepared plans.\r\n## Syntax\r\nDEALLOCATE \\<name\\>\r\ne.g.\r\nDEALLOCATE my-select;\r\n\r\n## Implementation\r\nAdd syntax to parser.\r\nRemove the thing with name \\<name\\> from your prepared plans map. \r\n\r\n# EXECUTE\r\nExecutes the already prepared statement of name \\<name\\>  replacing any parameters with the included args.\r\n\r\n## Syntax\r\nEXECUTE \\<name\\> [USING \\<arg1\\>[, \\<arg2\\>,…]]\r\ne.g.\r\nEXECUTE my-select USING 12, 3;\r\nto execute the query\r\nSELECT n_name from nation where n_nationkey < 12 and n_regionkey = 3;\r\n\r\n## Implementation\r\n* Add syntax to parser.\r\n* The parameters should be included in the analysis information.\r\n* Grab the plan with name \\<name\\> from the prepared plans map.  If we have information about the types of parameters expected, can do that type checking here.  Otherwise do it during the expression rewrite.\r\n* Add an expression rewrite rule to replace all instances of Parameter with literals corresponding to the appropriate parameter.  \r\n* If there were any other optimizations that couldn't be run during prepare because they needed actual values, run those now. \r\n* Add verifier that you’ve gotten rid of all the Parameter expressions in your plan.\r\n\r\n# DESCRIBE OUTPUT\r\nGet the output metadata for a prepared statement.  BI tools such as tableau use prepared statements as a way to get output metadata for a query without having to run them.  \r\n\r\n## Syntax\r\nThere is sql standard syntax for getting metadata for the output of a prepared statement, which is as follows:\r\nDESCRIBE [OUTPUT] \\<statement-name\\> using \\<sql descriptor\\>\r\n\r\nBut Presto doesn’t have sql descriptors and we anyway just want to return the data in the usual way. Another issue with this syntax is that DESCRIBE without OUTPUT conflicts with DESCRIBE \\<table-name\\> for Presto.\r\n\r\nTherefore, I propose we leave out that portion and use the following slightly modified syntax:\r\nDESCRIBE OUTPUT \\<statement-name\\>\r\n\r\ne.g.\r\nDESCRIBE OUTPUT my-select\r\nwould give you something like\r\nn_name: varchar\r\n\r\n## Implementation\r\nAdd syntax to Parser\r\nGet the plan from the prepared statement map.  Return output columns/types\r\n\r\n# DESCRIBE INPUT\r\nGet the input parameters required for a prepared statement.  This is not as important, but could potentially be useful.  \r\n\r\n## Syntax\r\nANSI compliant syntax would again use sql descriptors, but we would leave that out and write\r\nDESCRIBE INPUT \\<statement-name\\> \r\ne.g. \r\nDESCRIBE INPUT my-select\r\nwould give you\r\nbigint, bigint\r\n\r\n## Implementation\r\nThe easiest way to implement this would be to store the information in the prepared statements map and get it from there."}}