result_create(conn@ptr, statement) : Result too large

Question

t1_DA <- sqldf("select decile, 
               count(decile) as count, avg(pred_spent) as avg_pred_spent,   
               avg(exp(total_spent)) as avg_total_spent,
               avg(log(pred_spent)) as ln_avg_pred_spent,   
               avg(total_spent) as ln_avg_total_spent
               from t1
               group by decile
               order by decile desc")

I am doing linear regression on a file and when I run this part of the code I am getting following error

Error in result_create(conn@ptr, statement) : Result too large

Is there any way to overcome this error?

By default, sqldf is based on SQLite's SQL dialect. Check if it supports functions like `exp` and `log`. — Parfait, Dec 16 '18 at 18:54
That is certainly not large. Inline with @Parfait's comment: https://stackoverflow.com/a/14552021/3358272. Neither of `exp` and `log` are listed on https://www.sqlite.org/lang_corefunc.html. — r2evans, Dec 16 '18 at 18:55
Indeed it is not available for sqldf, the code works fine when i run it without exp and log. I will use that instead. Thanks for your help guys.@r2evans @Parfait — Random user, Dec 16 '18 at 19:29

score 1 · Accepted Answer · answered Dec 16 '18 at 20:06

As mentioned, by default sqldf uses the SQLite dialect which does not support extensive mathematical and statistical functions like exp and log. Admittedly, a better raised message can help users debug rather than Result too large (maybe a git issue for author, @ggrothendieck?).

However, in order to integrate these outputs into your aggregate query, consider creating those columns before running in sqldf. Use either transform or within for easy new column assignment without constant reference to data frame using the $ assignment approach.

t1 <- transform(t1, exp_total_spent = exp(total_spent),
                    log_pred_spent = log10(log_pred_spent)
                )
# ALTERNATIVE
t1 <- within(t1, {exp_total_spent <- exp(total_spent)
                  log_pred_spent <- log10(log_pred_spent)
             })

t1_DA <- sqldf("select decile, 
                       count(decile) as count, 
                       avg(pred_spent) as avg_pred_spent,   
                       avg(exp_total_spent) as avg_total_spent,
                       avg(log_pred_spent) as ln_avg_pred_spent,   
                       avg(total_spent) as ln_avg_total_spent
                from t1
                group by decile
                order by decile desc")

I will try this. Much appreciated.Thanks a lot! @Parfait – Random user Dec 18 '18 at 12:02 — Random user, Dec 18 '18 at 12:02

result_create(conn@ptr, statement) : Result too large

1 Answers1