search

cube2222 / octosql

OctoSQL is a query tool that allows you to join, analyse and transform data from multiple databases and file formats using SQL.
Mozilla Public License 2.0
4.76k stars 200 forks source link

mysql Relation = ‘in’ #230

Open will2love opened 4 years ago

will2love commented 4 years ago

origin code is this: //create a query with placeholders to prepare a statement from a physical formula query := formulaToSQL(filter, aliases) query = fmt.Sprintf("SELECT * FROM %s %s WHERE %s", tableName, alias, query)

when I use mysql sql: select m.test_id from mysql_test m where m.test_id in ('aaa') print the fileter: &{Value:true}

and then execute function formulaToSQL() get the sql SELECT * FROM test_config m WHERE TRUE

why in operation do not get corrent sql

cube2222 commented 4 years ago

"In" predicates are not getting pushed down to SQL databases currently.

will2love commented 4 years ago

But why I test mysql use 'in' predicat: select m.test_id from mysql_test m where m.test_id in ('aaa')

it can get successful result .

this is why?

Can you explain how it works when 'in' predicates ? thks

cube2222 commented 4 years ago

Yes, OctoSQL first planned query will actually be a whole database scan, followed by filtering in-memory. However, whenever we can, we push down predicates from in-memory filters to the underlying databases.

If you have a predicate like '=' then it will be pushed down, and octosql will only receive the records from the database which satisfy the predicate.

For a predicate like 'in' which is not yet implemented, OctoSQL will need to do a whole-table scan, and filter everything in memory to only the wanted records.