Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

SQLite Release 3.25.0 adds support for window functions

sqlite.org

Post reply on HN
Print view View original

111–116 of 116 posts

Re: SQLite Release 3.25.0 adds support for window functions

#111

Post by electroly »

bobwaycott wrote: post #104
yread wrote: post #101

Earlier quoted context omitted.

eh? if you want to know the number of rows in a table select count(*) is the idiomatic way

No. That’s the way one may see the first time they’re learning what a SELECT statement is, but it’s most certainly not what you’d ever do in anything near production code. The parent made this statement: > ... for instance there is no way for `SELECT COUNT(star) FROM table` to do anything but retrieve every field of every row and return them to the SQLite engine.” The only response here is, “Of course! That’s what yo…

I'm sorry, you've misunderstood. I'm fully aware that you can query it more efficiently, and in fact that's the point. If you're writing a virtual table provider, SQLite does not provide enough information about the actual user's query for you to know that you can do it more efficiently. There's no way to know that the user wants a count at all; the count function is not pushed down into the virtual table provider interface. SQLite only tells you that it needs you to retrieve every field in every row. You can't know that, in actuality, SQLite only wants to count the rows.

Re: SQLite Release 3.25.0 adds support for window functions

#112

Post by tzs »

simonw wrote: post #99
mercer wrote: post #92

Earlier quoted context omitted.

> I adore SQLite but I think the W3C made the right call there - web standards need more than one compatible implementation. could you elaborate on that? I get the impression that now, eight years later, we're much worse off. I don't really see the upside of not going for what is, AFAIK, a relatively vanilla implementation of SQL.

A web standard needs to have more than one implementation. If you allow one single implementation to define the standard, you get something like Flash. I don't have a particularly strong practical argument here: for me this is more of a fundamental principle for how the web should work.

Flash was a closed source plugin that was added to the browser as a binary blob. It could be updated by the user, and because it frequently had security bugs users were frequently urged to update. Those updates also often introduced new features, which content providers started taking advantage of once a significant number of people updated, putting pressure on the rest to update too.

SQLite is open source that would have been compiled into the browser by the browser vendor. A typical user would not have the means to update it separately from the rest of the browser.

So why couldn't W3C pick a particular release of SQLite, list a specific subset of its features, and declare that this, as described in the documentation for that specific version, is the standard?

Re: SQLite Release 3.25.0 adds support for window functions

#113

Post by bobwaycott »

electroly wrote: post #111
bobwaycott wrote: post #104

Earlier quoted context omitted.

No. That’s the way one may see the first time they’re learning what a SELECT statement is, but it’s most certainly not what you’d ever do in anything near production code. The parent made this statement: > ... for instance there is no way for `SELECT COUNT(star) FROM table` to do anything but retrieve every field of every row and return them to the SQLite engine.” The only response here is, “Of course! That’s what yo…

I'm sorry, you've misunderstood. I'm fully aware that you can query it more efficiently, and in fact that's the point. If you're writing a virtual table provider, SQLite does not provide enough information about the actual user's query for you to know that you can do it more efficiently. There's no way to know that the user wants a count at all ; the count function is not pushed down into the virtual table provider i…

Ah. My apologies. I did misunderstand what you were calling attention to.

Re: SQLite Release 3.25.0 adds support for window functions

#114

Post by westurner »

twoquestions wrote: post #43
nanimo wrote: post #5

https://www.windowfunctions.com is a good introduction to window functions. Besides that, the comprehensive testing and evaluation of SQLite never ceases to amaze me. I'm usually hesitant to call software development "engineering", but SQLite is definitely well-engineered.

This looks great, but I couldn't get through the first question on aggregate functions. Are there any SQL books/tutorials that go over things like this? A lot of material I've seen has been like the classic image of "How to draw an owl. First draw two circles, then draw the rest of the owl", where they tell you the super basic stuff, then assume you know everything.

Ibis uses windowing functions for aggregations if the database supports them. IDK when support for the new SQLite support will be implemented? http://docs.ibis-project.org/sql.html#window-functions

[EDIT]

I created an issue for this here: https://github.com/ibis-project/ibis/issues/1597

Re: SQLite Release 3.25.0 adds support for window functions

#115

Post by irishsultan »

SQLite wrote: post #79
molyss wrote: post #58

The query optimizer improvements are pretty cool too. Even though, I don't really understand that one : "The IN-early-out optimization: When doing a look-up on a multi-column index and an IN operator is used on a column other than the left-most column, then if no rows match against the first IN value, check to make sure there exist rows that match the columns to the right before continuing with the next IN value. ".…

Suppose your query is: SELECT * FROM tab WHERE key1=1 AND key2 IN (2,3,4,5); SQLite starts by doing a single b-tree lookup on the index on (1,2) - composed from the key1 field and the first possibility of the key2 field. If that works, then it proceeds to look up (1,3), (1,4), and (1,5). But if the (1,2) lookup fails, then it backs off and tries just (1, ) to see if that matches anything at all. If (1, ) finds any re…

This is what I expected the optimization to be, except I'm still not sure that I understand the wording of "that match the columns to the right", I'd expect that to be "that match the columns to the left", after all, you're checking the existence of (1,* ), not of (* ,2) or (* ,3).

Re: SQLite Release 3.25.0 adds support for window functions

#116

Post by blattimwind »

zbentley wrote: post #71
blattimwind wrote: post #62

Earlier quoted context omitted.

Say you record an event like "name of customer X has been changed to FooCo". Instead, record "name of customer X has been changed to FooCo from Foo & Sons Co ". If you want to undo it, just swap from/to (=invert it).

A common pattern to implement this is an audit table: https://dba.stackexchange.com/questions/15186/what-is-an-aud... ...which is itself a special case of an audit trail or log: https://en.wikipedia.org/wiki/Audit_trail

In my experience the usefulness of audit tables diminishes as the table becomes more complex (more columns; composite and document columns, like arrays, json, xml), because it becomes difficult to find what exactly changed. Another problem is related rows; audit tables aren't very good to capture the context of changes. You can work around this with more complex triggers, but it quickly becomes a lot easier to do this from the application and not in the database, capturing semantics and not just data changes.

The issue with any application-based approach is of course that it can't provide total access mediation.

Post reply on HN