title: When does dplyr's mutate function work with a database?
body: <p>I try to use <code>dplyr</code>'s <code>mutate()</code> function on a SQL like database. I don't understand why it work sometimes and doesn't other times. </p>
  
  <p>Here is one example with the stantard example from <a href="https://cran.rstudio.com/web/packages/dplyr/vignettes/databases.html" rel="nofollow noreferrer">https://cran.rstudio.com/web/packages/dplyr/vignettes/databases.html</a></p>
  
  <h2>loading the data into a database</h2>
  
  <pre><code>&gt; library(nycflights13)
  &gt; library(dplyr)
  &gt; db_sqlite &lt;- src_sqlite("data/my_db.sqlite3", create = TRUE)
  &gt; flights_sqlite &lt;- copy_to(db_sqlite, flights, temporary = FALSE, indexes = list(
  +   c("year", "month", "day"), "carrier", "tailnum"))
  </code></pre>
  
  <h2>Creating a new numeric variable with success</h2>
  
  <p>If I try to create a new numeric variable such as <code>year2</code>, everything works</p>
  
  <pre><code>&gt; tbl(db_sqlite, "flights") %&gt;% mutate(year2 = year + 10)
  Source:   query [?? x 20]
  Database: sqlite 3.8.6 [data/my_db.sqlite3]
  
      year month   day dep_time sched_dep_time dep_delay arr_time sched_arr_time arr_delay carrier flight
     &lt;int&gt; &lt;int&gt; &lt;int&gt;    &lt;int&gt;          &lt;int&gt;     &lt;dbl&gt;    &lt;int&gt;          &lt;int&gt;     &lt;dbl&gt;   &lt;chr&gt;  &lt;int&gt;
  1   2013     1     1      517            515         2      830            819        11      UA   1545
  2   2013     1     1      533            529         4      850            830        20      UA   1714
  3   2013     1     1      542            540         2      923            850        33      AA   1141
  4   2013     1     1      544            545        -1     1004           1022       -18      B6    725
  5   2013     1     1      554            600        -6      812            837       -25      DL    461
  6   2013     1     1      554            558        -4      740            728        12      UA   1696
  7   2013     1     1      555            600        -5      913            854        19      B6    507
  8   2013     1     1      557            600        -3      709            723       -14      EV   5708
  9   2013     1     1      557            600        -3      838            846        -8      B6     79
  10  2013     1     1      558            600        -2      753            745         8      AA    301
  # ... with more rows, and 9 more variables: tailnum &lt;chr&gt;, origin &lt;chr&gt;, dest &lt;chr&gt;, air_time &lt;dbl&gt;,
  #   distance &lt;dbl&gt;, hour &lt;dbl&gt;, minute &lt;dbl&gt;, time_hour &lt;dbl&gt;, year2 &lt;dbl&gt;
  </code></pre>
  
  <h1>But failing to create a new character variable using <code>paste0()</code></h1>
  
  <p>If I try to create a new variable using the base function <code>paste0()</code>, I've got an error. </p>
  
  <pre><code>&gt; tbl(db_sqlite, "flights") %&gt;% mutate(date = paste0(year, month, day))
  Source:   query [?? x 20]
  Database: sqlite 3.8.6 [data/my_db.sqlite3]
  
  Error in sqliteSendQuery(conn, statement) : 
    error in statement: no such function: PASTE0
  </code></pre>
  
  <p>Session infos are here : </p>
  
  <pre><code>&gt; devtools::session_info()
  Session info ------------------------------------------------------------------------------------------------
   setting  value                       
   version  R version 3.3.2 (2016-10-31)
   system   x86_64, linux-gnu           
   ui       RStudio (1.0.35)            
   language (EN)                        
   collate  C                           
   tz       &lt;NA&gt;                        
   date     2016-12-12                  
  
  Packages ----------------------------------------------------------------------------------------------------
   package      * version    date       source                          
   DBI            0.5-1      2016-09-10 cran (@0.5-1)                   
   R6             2.2.0      2016-10-05 cran (@2.2.0)                   
   RSQLite        1.0.0      2014-10-25 CRAN (R 3.2.2)                  
   Rcpp           0.12.8     2016-11-17 cran (@0.12.8)                  
   assertthat     0.1        2013-12-06 CRAN (R 3.2.2)                  
   devtools       1.12.0     2016-06-24 CRAN (R 3.3.1)                  
   digest         0.6.10     2016-08-02 CRAN (R 3.2.3)                  
   dplyr        * 0.5.0.9000 2016-12-12 Github (hadley/dplyr@c846cb3)   
   lazyeval       0.2.0.9000 2016-10-14 Github (hadley/lazyeval@c155c3d)
   magrittr       1.5        2014-11-22 CRAN (R 3.2.3)                  
   memoise        1.0.0      2016-01-29 CRAN (R 3.2.3)                  
   nycflights13 * 0.2.0      2016-04-30 CRAN (R 3.3.2)                  
   tibble         1.2        2016-08-26 CRAN (R 3.2.3)                  
   withr          1.0.2      2016-06-20 CRAN (R 3.2.3)               
  </code></pre>
  
  <p>What kind of operations work using <code>mutate()</code> on database ? </p>
  
  <p>Is there any solution to use functions such as <code>paste0()</code> on database without collecting data into memory ?</p>