@filter generates errors

[pstaabp]

I just stumbled on this package and am intrigued since I'm using a lot of sqlite databases. I'm a bit confused in that if I do the example from the documentation:

database = Database(joinpath(pathof(QuerySQLite) |> dirname |> dirname, "test", "Chinook_Sqlite.sqlite"))

It seems then I can query things on a table. So the following should work:

database.Track |> @filter(_.AlbumId == 1)

However, I get the error:

SQLite.SQLiteException("near \"FROM\": syntax error")

which sounds like the internal SQL command isn't generated correctly. However, if I first convert the results to a DataFrame, then things work. That is,

database.Track |> DataFrame |> @filter(_.AlbumId == 1)

has the result with 10 rows in a table.

What I understand is this isn't really the purpose of this package, because first creating the DataFrame puts the entire Track table in memory and then filters the table. Instead, I though the @filter would be translated to SQL code and then called which should be faster with less memory.

[pstaabp]

Looking at this more in depth, it seems to be the @filter macro instead. Many of the others seem to work find.

[bramtayl]

Thanks for opening the issue! This is a bug. You can see the issue here:

julia> database.Track |> @filter(_.AlbumId == 1) |> get_sql
FROM (Track) WHERE AlbumId = 1

There should be another SELECT (*) at the start.

[bramtayl]

Should be fixed by https://github.com/queryverse/QuerySQLite.jl/commit/b6cd219650015af705d6c759c24b7e2213189912

@davidanthoff I meant to submit it as a PR but accidentally pushed to master sorry

[bramtayl]

@pstaabp let me know if you have any more issues with this.

[pstaabp]

This works now, however the display is odd. If you enter:

database.Track |> @filter(_.AlbumId == 1)

the result is

TrackId | Name | AlbumId | MediaTypeId | GenreId | Composer | Milliseconds | Bytes | UnitPrice
-- | -- | -- | -- | -- | -- | -- | -- | --

which is just the headers. However, if you send to a DataFrame, then the result is as expected.

Maybe this is the way that structs of the type QuerySQLite.SourceCode{SQLite.DB} are displayed. Seems like it should indicated that the results are not empty though.

[bramtayl]

That's not what displays for me; I get

10x9 SQLite query result
TrackId │ Name                                      │ AlbumId │ MediaTypeId
────────┼───────────────────────────────────────────┼─────────┼────────────
1       │ "For Those About To Rock (We Salute You)" │ 1       │ 1          
6       │ "Put The Finger On You"                   │ 1       │ 1          
7       │ "Let's Get It Up"                         │ 1       │ 1          
8       │ "Inject The Venom"                        │ 1       │ 1          
9       │ "Snowballed"                              │ 1       │ 1          
10      │ "Evil Walks"                              │ 1       │ 1          
11      │ "C.O.D."                                  │ 1       │ 1          
12      │ "Breaking The Rules"                      │ 1       │ 1          
13      │ "Night Of The Long Knives"                │ 1       │ 1          
14      │ "Spellbound"                              │ 1       │ 1          
... with 5 more columns: GenreId, Composer, Milliseconds, Byte…

[bramtayl]

What editor are you viewing the table in? I'm still using Juno in Atom

[pstaabp]

This was in a jupyter lab notebook on safari.

On Mon, Mar 30, 2020 at 12:51 PM bramtayl notifications@github.com wrote:

What editor are you viewing the table in? I'm still using Juno in Atom

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/queryverse/QuerySQLite.jl/issues/27#issuecomment-606115611, or unsubscribe https://github.com/notifications/unsubscribe-auth/AAGWV2WVTMKWHUJ2WGOIYZ3RKDEXPANCNFSM4LVBG57A .

[bramtayl]

Hmm, seems to me that this is Queryverse interacting badly with jupyter. Not sure though. In any case, the show methods are inherited from the Queryverse, so I don't think its an issue here.