Redshift Scientific Notation Triggers Incorrect Multiple Queries Error

[kevinpalm]

Since updating from RPostgres 1.3.3 to 1.4.3, I'm getting this error on one of my Redshift queries when I don't expect: Error: Multiple queries must use the same column types.

It seems to be related to using scientific notation in a very specific circumstance around filling NULLs after a window function.

# open connection
conn <- RPostgres::dbConnect(RPostgres::Redshift()
                             , host =
                             , port = 
                             , user = 
                             , password = 
                             , dbname = )

query_BROKEN <- '
select coalesce(lead(num) over (order by num), 1e10)
from (
  select 1 as num
  union all
  select 2 as num
  union all
  select 3 as num
)
'

query_WORKS <- '
select coalesce(lead(num) over (order by num), 10000000)
from (
  select 1 as num
  union all
  select 2 as num
  union all
  select 3 as num
)
'

print(RPostgres::dbGetQuery(conn, query_WORKS))
# coalesce
# 1        2
# 2        3
# 3 10000000

print(RPostgres::dbGetQuery(conn, query_BROKEN))
# Error: Multiple queries must use the same column types.

RPostgres::dbDisconnect(conn)

[krlmlr]

Thanks, this is weird. I need to take a closer look.

[krlmlr]

I appreciate your patience with this.

Are you by any chance using dbplyr to generate this query? Does the example that you posted work with RPostgres 1.3.3, or does a change in dbQuoteLiteral() affect how the query is generated?