Add support for fields with spaces in their names when using FROM clause

[jimmyg3g]

I inherited a bunch of SQL with spaces in field names. Does tidyquery support field names that have spaces like brand and model in this reprex:

library(tidyverse)
library(tidyquery)
mtcars_tibble <- mtcars %>% rownames_to_column(var = 'brand and model') %>%
    as_tibble()
sql <- "select mt.`brand and model`, mt.mpg, mt.cyl from mtcars_tibble as mt"
tidyquery::query(sql)
#> Warning in readChar(rc, nchars): truncating string with embedded nuls

#> Warning in readChar(rc, nchars): truncating string with embedded nuls

#> Warning in readChar(rc, nchars): truncating string with embedded nuls

#> Warning in readChar(rc, nchars): truncating string with embedded nuls

#> Warning in readChar(rc, nchars): truncating string with embedded nuls

#> Warning in readChar(rc, nchars): truncating string with embedded nuls

#> Warning in readChar(rc, nchars): truncating string with embedded nuls

#> Warning in readChar(rc, nchars): truncating string with embedded nuls

#> Warning in readChar(rc, nchars): truncating string with embedded nuls

#> Warning in readChar(rc, nchars): truncating string with embedded nuls

#> Warning in readChar(rc, nchars): truncating string with embedded nuls

#> Warning in readChar(rc, nchars): truncating string with embedded nuls

#> Warning in readChar(rc, nchars): truncating string with embedded nuls

#> Warning in readChar(rc, nchars): truncating string with embedded nuls

#> Warning in readChar(rc, nchars): truncating string with embedded nuls

#> Warning in readChar(rc, nchars): truncating string with embedded nuls

#> Warning in readChar(rc, nchars): truncating string with embedded nuls

#> Warning in readChar(rc, nchars): truncating string with embedded nuls

#> Warning in readChar(rc, nchars): truncating string with embedded nuls

#> Warning in readChar(rc, nchars): truncating string with embedded nuls
#> Warning in readChar(rc, 1L, useBytes = TRUE): truncating string with embedded
#> nuls

#> Warning in readChar(rc, 1L, useBytes = TRUE): truncating string with embedded
#> nuls
#> Warning in readChar(rc_in, 1L): truncating string with embedded nuls
#> Error in str2lang(unqualified_column_name): <text>:1:7: unexpected symbol
#> 1: brand and
#>           ^

^{Created on 2020-08-11 by the reprex package (v0.3.0.9001)}

[ianmcook]

Hi @jimmyg3g, thanks for reporting this!

This error was happening because of a problem in the queryparser package, which tidyquery depends on, and which I also maintain.

I believe I have now resolved this in the development version of queryparser on GitHub.

I have also made a change to queryparser that silences the numerous truncating string with embedded nuls warnings.

Please reinstall the development version of queryparser from GitHub and let me know if it works for you:

remotes::install_github("ianmcook/queryparser")

[jimmyg3g]

Yes, it works for me, thanks!

[jimmyg3g]

The third example on this reprex is throwing an error:

library(tidyverse)
library(tidyquery)
mtcars_tibble <- mtcars %>% rownames_to_column(var = 'brand and model') %>%
    as_tibble()

query('SELECT mt.`brand and model` FROM mtcars_tibble as mt LIMIT 1')
#> # A tibble: 1 x 1
#>   `brand and model`
#>   <chr>            
#> 1 Mazda RX4
query("SELECT SUBSTRING_INDEX(`brand and model`, ' ', 1) as brand from mtcars_tibble LIMIT 1")
#> # A tibble: 1 x 1
#>   brand
#>   <chr>
#> 1 Mazda
query("SELECT SUBSTRING_INDEX(mt.`brand and model`, ' ', 1) as brand from mtcars_tibble as mt LIMIT 1")
#> Error in str2lang(expr_out): <text>:1:20: unexpected symbol
#> 1: SUBSTRING_INDEX(mt.`brand and model`
#>                        ^

^{Created on 2020-08-18 by the reprex package (v0.3.0.9001)}

[ianmcook]

Oops, didn't catch that case. I just fixed it. The version of queryparser on GitHub should solve this for you.

[jimmyg3g]

That did the trick, thanks!