EVOLUTION-MANAGER
Edit File: reprex.html
<!DOCTYPE html> <html> <head> <meta charset="utf-8" /> <meta name="generator" content="pandoc" /> <meta http-equiv="X-UA-Compatible" content="IE=EDGE" /> <meta name="viewport" content="width=device-width, initial-scale=1" /> <title>Reprexes for dbplyr</title> <script>// Pandoc 2.9 adds attributes on both header and div. We remove the former (to // be compatible with the behavior of Pandoc < 2.8). document.addEventListener('DOMContentLoaded', function(e) { var hs = document.querySelectorAll("div.section[class*='level'] > :first-child"); var i, h, a; for (i = 0; i < hs.length; i++) { h = hs[i]; if (!/^h[1-6]$/i.test(h.tagName)) continue; // it should be a header h1-h6 a = h.attributes; while (a.length > 0) h.removeAttribute(a[0].name); } }); </script> <style type="text/css">code{white-space: pre;}</style> <style type="text/css" data-origin="pandoc"> pre > code.sourceCode { white-space: pre; position: relative; } pre > code.sourceCode > span { display: inline-block; line-height: 1.25; } pre > code.sourceCode > span:empty { height: 1.2em; } code.sourceCode > span { color: inherit; text-decoration: inherit; } div.sourceCode { margin: 1em 0; } pre.sourceCode { margin: 0; } @media screen { div.sourceCode { overflow: auto; } } @media print { pre > code.sourceCode { white-space: pre-wrap; } pre > code.sourceCode > span { text-indent: -5em; padding-left: 5em; } } pre.numberSource code { counter-reset: source-line 0; } pre.numberSource code > span { position: relative; left: -4em; counter-increment: source-line; } pre.numberSource code > span > a:first-child::before { content: counter(source-line); position: relative; left: -1em; text-align: right; vertical-align: baseline; border: none; display: inline-block; -webkit-touch-callout: none; -webkit-user-select: none; -khtml-user-select: none; -moz-user-select: none; -ms-user-select: none; user-select: none; padding: 0 4px; width: 4em; color: #aaaaaa; } pre.numberSource { margin-left: 3em; border-left: 1px solid #aaaaaa; padding-left: 4px; } div.sourceCode { } @media screen { pre > code.sourceCode > span > a:first-child::before { text-decoration: underline; } } code span.al { color: #ff0000; font-weight: bold; } /* Alert */ code span.an { color: #60a0b0; font-weight: bold; font-style: italic; } /* Annotation */ code span.at { color: #7d9029; } /* Attribute */ code span.bn { color: #40a070; } /* BaseN */ code span.bu { } /* BuiltIn */ code span.cf { color: #007020; font-weight: bold; } /* ControlFlow */ code span.ch { color: #4070a0; } /* Char */ code span.cn { color: #880000; } /* Constant */ code span.co { color: #60a0b0; font-style: italic; } /* Comment */ code span.cv { color: #60a0b0; font-weight: bold; font-style: italic; } /* CommentVar */ code span.do { color: #ba2121; font-style: italic; } /* Documentation */ code span.dt { color: #902000; } /* DataType */ code span.dv { color: #40a070; } /* DecVal */ code span.er { color: #ff0000; font-weight: bold; } /* Error */ code span.ex { } /* Extension */ code span.fl { color: #40a070; } /* Float */ code span.fu { color: #06287e; } /* Function */ code span.im { } /* Import */ code span.in { color: #60a0b0; font-weight: bold; font-style: italic; } /* Information */ code span.kw { color: #007020; font-weight: bold; } /* Keyword */ code span.op { color: #666666; } /* Operator */ code span.ot { color: #007020; } /* Other */ code span.pp { color: #bc7a00; } /* Preprocessor */ code span.sc { color: #4070a0; } /* SpecialChar */ code span.ss { color: #bb6688; } /* SpecialString */ code span.st { color: #4070a0; } /* String */ code span.va { color: #19177c; } /* Variable */ code span.vs { color: #4070a0; } /* VerbatimString */ code span.wa { color: #60a0b0; font-weight: bold; font-style: italic; } /* Warning */ </style> <script> // apply pandoc div.sourceCode style to pre.sourceCode instead (function() { var sheets = document.styleSheets; for (var i = 0; i < sheets.length; i++) { if (sheets[i].ownerNode.dataset["origin"] !== "pandoc") continue; try { var rules = sheets[i].cssRules; } catch (e) { continue; } for (var j = 0; j < rules.length; j++) { var rule = rules[j]; // check if there is a div.sourceCode rule if (rule.type !== rule.STYLE_RULE || rule.selectorText !== "div.sourceCode") continue; var style = rule.style.cssText; // check if color or background-color is set if (rule.style.color === '' && rule.style.backgroundColor === '') continue; // replace div.sourceCode by a pre.sourceCode rule sheets[i].deleteRule(j); sheets[i].insertRule('pre.sourceCode{' + style + '}', j); } } })(); </script> <style type="text/css">body { background-color: #fff; margin: 1em auto; max-width: 700px; overflow: visible; padding-left: 2em; padding-right: 2em; font-family: "Open Sans", "Helvetica Neue", Helvetica, Arial, sans-serif; font-size: 14px; line-height: 1.35; } #TOC { clear: both; margin: 0 0 10px 10px; padding: 4px; width: 400px; border: 1px solid #CCCCCC; border-radius: 5px; background-color: #f6f6f6; font-size: 13px; line-height: 1.3; } #TOC .toctitle { font-weight: bold; font-size: 15px; margin-left: 5px; } #TOC ul { padding-left: 40px; margin-left: -1.5em; margin-top: 5px; margin-bottom: 5px; } #TOC ul ul { margin-left: -2em; } #TOC li { line-height: 16px; } table { margin: 1em auto; border-width: 1px; border-color: #DDDDDD; border-style: outset; border-collapse: collapse; } table th { border-width: 2px; padding: 5px; border-style: inset; } table td { border-width: 1px; border-style: inset; line-height: 18px; padding: 5px 5px; } table, table th, table td { border-left-style: none; border-right-style: none; } table thead, table tr.even { background-color: #f7f7f7; } p { margin: 0.5em 0; } blockquote { background-color: #f6f6f6; padding: 0.25em 0.75em; } hr { border-style: solid; border: none; border-top: 1px solid #777; margin: 28px 0; } dl { margin-left: 0; } dl dd { margin-bottom: 13px; margin-left: 13px; } dl dt { font-weight: bold; } ul { margin-top: 0; } ul li { list-style: circle outside; } ul ul { margin-bottom: 0; } pre, code { background-color: #f7f7f7; border-radius: 3px; color: #333; white-space: pre-wrap; } pre { border-radius: 3px; margin: 5px 0px 10px 0px; padding: 10px; } pre:not([class]) { background-color: #f7f7f7; } code { font-family: Consolas, Monaco, 'Courier New', monospace; font-size: 85%; } p > code, li > code { padding: 2px 0px; } div.figure { text-align: center; } img { background-color: #FFFFFF; padding: 2px; border: 1px solid #DDDDDD; border-radius: 3px; border: 1px solid #CCCCCC; margin: 0 5px; } h1 { margin-top: 0; font-size: 35px; line-height: 40px; } h2 { border-bottom: 4px solid #f7f7f7; padding-top: 10px; padding-bottom: 2px; font-size: 145%; } h3 { border-bottom: 2px solid #f7f7f7; padding-top: 10px; font-size: 120%; } h4 { border-bottom: 1px solid #f7f7f7; margin-left: 8px; font-size: 105%; } h5, h6 { border-bottom: 1px solid #ccc; font-size: 105%; } a { color: #0033dd; text-decoration: none; } a:hover { color: #6666ff; } a:visited { color: #800080; } a:visited:hover { color: #BB00BB; } a[href^="http:"] { text-decoration: underline; } a[href^="https:"] { text-decoration: underline; } code > span.kw { color: #555; font-weight: bold; } code > span.dt { color: #902000; } code > span.dv { color: #40a070; } code > span.bn { color: #d14; } code > span.fl { color: #d14; } code > span.ch { color: #d14; } code > span.st { color: #d14; } code > span.co { color: #888888; font-style: italic; } code > span.ot { color: #007020; } code > span.al { color: #ff0000; font-weight: bold; } code > span.fu { color: #900; font-weight: bold; } code > span.er { color: #a61717; background-color: #e3d2d2; } </style> </head> <body> <h1 class="title toc-ignore">Reprexes for dbplyr</h1> <p>If you’re reporting a bug in dbplyr, it is much easier for me to help you if you can supply a <a href="https://reprex.tidyverse.org">reprex</a> that I can run on my computer. Creating reprexes for dbplyr is particularly challenging because you are probably using a database that you can’t share with me. Fortunately, in many cases you can still demonstrate the problem even if I don’t have the complete dataset, or even access to the database system that you’re using.</p> <p>This vignette outlines three approaches for creating reprexes that will work anywhere:</p> <ul> <li><p>Use <code>memdb_frame()</code>/<code>tbl_memdb()</code> to easily create datasets that live in an in-memory SQLite database.</p></li> <li><p>Use <code>lazy_frame()</code>/<code>tbl_lazy()</code> to simulate SQL generation of dplyr pipelines.</p></li> <li><p>Use <code>translate_sql()</code> to simulate SQL generation of columnar expression.</p></li> </ul> <div class="sourceCode" id="cb1"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb1-1"><a href="#cb1-1"></a><span class="kw">library</span>(dplyr)</span> <span id="cb1-2"><a href="#cb1-2"></a><span class="kw">library</span>(dbplyr)</span></code></pre></div> <div id="using-memdb_frame" class="section level2"> <h2>Using <code>memdb_frame()</code></h2> <p>The first place to start is with SQLite. SQLite is particularly appealing because it’s completely embedded instead an R package so doesn’t have any external dependencies. SQLite is designed to be small and simple, so it can’t demonstrate all problems, but it’s easy to try out and a great place to start.</p> <p>You can easily create a SQLite in-memory database table using <code>memdb_frame()</code>:</p> <div class="sourceCode" id="cb2"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb2-1"><a href="#cb2-1"></a>mf <-<span class="st"> </span><span class="kw">memdb_frame</span>(<span class="dt">g =</span> <span class="kw">c</span>(<span class="dv">1</span>, <span class="dv">1</span>, <span class="dv">2</span>, <span class="dv">2</span>, <span class="dv">2</span>), <span class="dt">x =</span> <span class="dv">1</span><span class="op">:</span><span class="dv">5</span>, <span class="dt">y =</span> <span class="dv">5</span><span class="op">:</span><span class="dv">1</span>)</span> <span id="cb2-2"><a href="#cb2-2"></a>mf</span> <span id="cb2-3"><a href="#cb2-3"></a><span class="co">#> # Source: table<dbplyr_001> [?? x 3]</span></span> <span id="cb2-4"><a href="#cb2-4"></a><span class="co">#> # Database: sqlite 3.30.1 [:memory:]</span></span> <span id="cb2-5"><a href="#cb2-5"></a><span class="co">#> g x y</span></span> <span id="cb2-6"><a href="#cb2-6"></a><span class="co">#> <dbl> <int> <int></span></span> <span id="cb2-7"><a href="#cb2-7"></a><span class="co">#> 1 1 1 5</span></span> <span id="cb2-8"><a href="#cb2-8"></a><span class="co">#> 2 1 2 4</span></span> <span id="cb2-9"><a href="#cb2-9"></a><span class="co">#> 3 2 3 3</span></span> <span id="cb2-10"><a href="#cb2-10"></a><span class="co">#> 4 2 4 2</span></span> <span id="cb2-11"><a href="#cb2-11"></a><span class="co">#> # … with more rows</span></span> <span id="cb2-12"><a href="#cb2-12"></a></span> <span id="cb2-13"><a href="#cb2-13"></a>mf <span class="op">%>%</span><span class="st"> </span></span> <span id="cb2-14"><a href="#cb2-14"></a><span class="st"> </span><span class="kw">group_by</span>(g) <span class="op">%>%</span><span class="st"> </span></span> <span id="cb2-15"><a href="#cb2-15"></a><span class="st"> </span><span class="kw">summarise_all</span>(mean, <span class="dt">na.rm =</span> <span class="ot">TRUE</span>)</span> <span id="cb2-16"><a href="#cb2-16"></a><span class="co">#> # Source: lazy query [?? x 3]</span></span> <span id="cb2-17"><a href="#cb2-17"></a><span class="co">#> # Database: sqlite 3.30.1 [:memory:]</span></span> <span id="cb2-18"><a href="#cb2-18"></a><span class="co">#> g x y</span></span> <span id="cb2-19"><a href="#cb2-19"></a><span class="co">#> <dbl> <dbl> <dbl></span></span> <span id="cb2-20"><a href="#cb2-20"></a><span class="co">#> 1 1 1.5 4.5</span></span> <span id="cb2-21"><a href="#cb2-21"></a><span class="co">#> 2 2 4 2</span></span></code></pre></div> <p>Reprexes are easiest to understand if you create very small custom data, but if you do want to use an existing data frame you can use <code>tbl_memdb()</code>:</p> <div class="sourceCode" id="cb3"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb3-1"><a href="#cb3-1"></a>mtcars_db <-<span class="st"> </span><span class="kw">tbl_memdb</span>(mtcars)</span> <span id="cb3-2"><a href="#cb3-2"></a>mtcars_db <span class="op">%>%</span><span class="st"> </span></span> <span id="cb3-3"><a href="#cb3-3"></a><span class="st"> </span><span class="kw">group_by</span>(cyl) <span class="op">%>%</span><span class="st"> </span></span> <span id="cb3-4"><a href="#cb3-4"></a><span class="st"> </span><span class="kw">summarise</span>(<span class="dt">n =</span> <span class="kw">n</span>()) <span class="op">%>%</span><span class="st"> </span></span> <span id="cb3-5"><a href="#cb3-5"></a><span class="st"> </span><span class="kw">show_query</span>()</span> <span id="cb3-6"><a href="#cb3-6"></a><span class="co">#> <SQL></span></span> <span id="cb3-7"><a href="#cb3-7"></a><span class="co">#> SELECT `cyl`, COUNT() AS `n`</span></span> <span id="cb3-8"><a href="#cb3-8"></a><span class="co">#> FROM `mtcars`</span></span> <span id="cb3-9"><a href="#cb3-9"></a><span class="co">#> GROUP BY `cyl`</span></span></code></pre></div> </div> <div id="translating-verbs" class="section level2"> <h2>Translating verbs</h2> <p>Many problems with dbplyr come down to incorrect SQL generation. Fortunately, it’s possible to generate SQL without a database using <code>lazy_frame()</code> and <code>tbl_lazy()</code>. Both take an <code>con</code> argument which takes a database “simulator” like <code>simulate_postgres()</code>, <code>simulate_sqlite()</code>, etc.</p> <div class="sourceCode" id="cb4"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb4-1"><a href="#cb4-1"></a>x <-<span class="st"> </span><span class="kw">c</span>(<span class="st">"abc"</span>, <span class="st">"def"</span>, <span class="st">"ghif"</span>)</span> <span id="cb4-2"><a href="#cb4-2"></a></span> <span id="cb4-3"><a href="#cb4-3"></a><span class="kw">lazy_frame</span>(<span class="dt">x =</span> x, <span class="dt">con =</span> <span class="kw">simulate_postgres</span>()) <span class="op">%>%</span><span class="st"> </span></span> <span id="cb4-4"><a href="#cb4-4"></a><span class="st"> </span><span class="kw">head</span>(<span class="dv">5</span>) <span class="op">%>%</span><span class="st"> </span></span> <span id="cb4-5"><a href="#cb4-5"></a><span class="st"> </span><span class="kw">show_query</span>()</span> <span id="cb4-6"><a href="#cb4-6"></a><span class="co">#> <SQL></span></span> <span id="cb4-7"><a href="#cb4-7"></a><span class="co">#> SELECT *</span></span> <span id="cb4-8"><a href="#cb4-8"></a><span class="co">#> FROM `df`</span></span> <span id="cb4-9"><a href="#cb4-9"></a><span class="co">#> LIMIT 5</span></span> <span id="cb4-10"><a href="#cb4-10"></a></span> <span id="cb4-11"><a href="#cb4-11"></a><span class="kw">lazy_frame</span>(<span class="dt">x =</span> x, <span class="dt">con =</span> <span class="kw">simulate_mssql</span>()) <span class="op">%>%</span><span class="st"> </span></span> <span id="cb4-12"><a href="#cb4-12"></a><span class="st"> </span><span class="kw">head</span>(<span class="dv">5</span>) <span class="op">%>%</span><span class="st"> </span></span> <span id="cb4-13"><a href="#cb4-13"></a><span class="st"> </span><span class="kw">show_query</span>()</span> <span id="cb4-14"><a href="#cb4-14"></a><span class="co">#> <SQL></span></span> <span id="cb4-15"><a href="#cb4-15"></a><span class="co">#> SELECT TOP(5) *</span></span> <span id="cb4-16"><a href="#cb4-16"></a><span class="co">#> FROM `df`</span></span></code></pre></div> <p>If you isolate the problem to incorrect SQL generation, it would be very helpful if you could also suggest more appropriate SQL.</p> </div> <div id="translating-individual-expressions" class="section level2"> <h2>Translating individual expressions</h2> <p>In some cases, you might be able to track the problem down to incorrect translation for a single column expression. In that case, you can make your reprex even simpler with <code>translate_sql()</code>:</p> <div class="sourceCode" id="cb5"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb5-1"><a href="#cb5-1"></a><span class="kw">translate_sql</span>(<span class="kw">substr</span>(x, <span class="dv">1</span>, <span class="dv">2</span>), <span class="dt">con =</span> <span class="kw">simulate_postgres</span>())</span> <span id="cb5-2"><a href="#cb5-2"></a><span class="co">#> <SQL> SUBSTR(`x`, 1, 2)</span></span> <span id="cb5-3"><a href="#cb5-3"></a><span class="kw">translate_sql</span>(<span class="kw">substr</span>(x, <span class="dv">1</span>, <span class="dv">2</span>), <span class="dt">con =</span> <span class="kw">simulate_sqlite</span>())</span> <span id="cb5-4"><a href="#cb5-4"></a><span class="co">#> <SQL> SUBSTR(`x`, 1, 2)</span></span></code></pre></div> </div> <!-- code folding --> <!-- dynamically load mathjax for compatibility with self-contained --> <script> (function () { var script = document.createElement("script"); script.type = "text/javascript"; script.src = "https://mathjax.rstudio.com/latest/MathJax.js?config=TeX-AMS-MML_HTMLorMML"; document.getElementsByTagName("head")[0].appendChild(script); })(); </script> </body> </html>