0

I have some R test code that I use to connect to a MonetDB instance.

I notice that for each connection that I create through tbl(conn, "some_table") I somehow always end up with a 'primer' query of the form

SELECT *
FROM "some_table" AS "zzz1"
WHERE (0 = 1)

So if I run the code

conn <- dbConnect(MonetDB.R(), host="localhost", dbname="testdb", user="monetdb", password="monetdb")
foo <- tbl(conn, "foo")
foo %>% filter(bar %like%  '%baz%') %>% collect()

I end up with two queries in the output

QQ: 'SELECT *
FROM "foo" AS "zzz1"
WHERE (0 = 1)'
QQ: Query result for query 0 with 0 rows and 34 cols, 0 rows.
QQ: 'SELECT *
FROM "foo")
WHERE ("bar" LIKE '%baz%')'
QQ: Query result for query 1 with 20 rows and 2 cols, 20 rows.

I do not understand where this additional query comes from. As far as I know DBI should not perform any dummy query by default.

Yunus King
  • 1,141
  • 1
  • 11
  • 23

1 Answers1

2

dplyr auto-generates and runs this query to get the columns for the table. This is needed to figure out whether bar in filter exists in the table for example.

Hannes Mühleisen
  • 2,542
  • 11
  • 13
  • Ok, I guessed something like that. Problem is that MonetDB does not recognize that `WHERE 0 = 1` or even `WHERE FALSE` is an anti-tautology in its MAL plan generation. I think this warrants a new MAL optimizer that checks for and shortcuts these anti-tautologies. – Yunus King Jan 15 '19 at 10:27
  • Why is this a problem? Does it take long to process? – Hannes Mühleisen Jan 15 '19 at 12:22
  • Yup on a big table it does a full scan. I made a MonetDB bug report for it: https://www.monetdb.org/bugzilla/show_bug.cgi?id=6677 – Yunus King Jan 15 '19 at 12:55
  • Indeed then this could/should be detected. DuckDB does. I also suspect that the sequential pipeline will not have the same problem in MonetDB. – Hannes Mühleisen Jan 15 '19 at 15:20
  • It actually does: check the reproduction steps in the bug report. – Yunus King Jan 17 '19 at 13:38