Re: [sqlite] General Amalgamation vs Your Own
On Saturday, 21 September, 2019 22:35, Stephen Chrzanowski : >Thanks for all this info. >No, I have not made a single change to the amalgamation that has been >provided by sqlite.org. The most I've done is read, line by line, >through the first meg or so of source code, as well as setup an >environment where I specifically can build sqlite3.dll for fun. >The thought was entirely for theoretical practices, and only came >up when I noticed a few threads suggesting that they're making >changes to the code (Be it to test theory or to enact live in >their libraries), then my thoughts wandered to their code vs future >SQLite code enhancements or bug fixes, and how getting the two >different sets of code to work with each other going forward. I used to have a repository that contained basically the distribution amalgamation/shell code on trunk and a branch that contained the changes I had made. This was complicated to maintain since the changes end up being somewhat extensive to implement simple things (such as moving extensions from the shell into the core, and adding new builtin extensions). Now I simply work on a clone of the distribution repository and have a private branch that has my changes incorporated in source and standard build tools directly. It is much easier to maintain and so far has not required anywhere near as many manual merge operations to resolve conflicts. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to install REGEXP support?
On Sep 19, 2019, at 7:21 PM, Peng Yu wrote: > > My question is `But it is not clear how to install it for sqlite3 installed by > homebrew.` That sounds more like a question to ask the Homebrew community; it isn’t really related to SQLite directly. SQLite is just making a system call to load a library by name. —Jens ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] General Amalgamation vs Your Own
Thanks for all this info. No, I have not made a single change to the amalgamation that has been provided by sqlite.org. The most I've done is read, line by line, through the first meg or so of source code, as well as setup an environment where I specifically can build sqlite3.dll for fun. The thought was entirely for theoretical practices, and only came up when I noticed a few threads suggesting that they're making changes to the code (Be it to test theory or to enact live in their libraries), then my thoughts wandered to their code vs future SQLite code enhancements or bug fixes, and how getting the two different sets of code to work with each other going forward. On Sat, Sep 21, 2019 at 10:41 PM Warren Young wrote: > On Sep 21, 2019, at 11:29 AM, Stephen Chrzanowski > wrote: > > > > How does one have their own code base for SQLite, with their own customer > > logic or functionality or whatever, then, have updates provided by the > > SQLite team implemented in when updates and such are provided? > > What kind of code are you talking about? > > With custom functions and loadable extensions, you don’t modify the SQLite > core at all: > > https://sqlite.org/c3ref/create_function.html > https://sqlite.org/loadext.html > > The latter allows a lot of flexibility, changing collation sequences, > adding VFSes, etc. > > If you’re modifying the SQLite core, then why? Maybe it isn’t actually > required that you do so, and we could show you a better way. Or, maybe > SQLite extensions could be, ah, *extended* to give you the API you’d need > to do this from the outside, without modifying the core. > > > I'm assuming Fossil would handle this kind of process with merging and > such? > > I would not recommend using private branches, even though drh offered it, > because it’s not a commonly-used feature of Fossil, so it’s not as > well-tested and well-supported as Fossil’s mainstream usage code paths. If > you ever run into trouble using private branches in Fossil, you’re more > likely to get “crickets” as a response than with similar trouble within the > well-used features of Fossil. > > Even when private branches are the right answer, they exist for a > different use case than what you’ve got going here: you have check-in > rights on the parent repo you cloned from, but you don’t want all local > check-ins to sync up to the parent repo. I don’t see any user with > check-ins on the SQLite code repo that looks like your name or your Gmail > user name, so I’m assuming you *don’t* have check-in rights on the SQLite > code repo. Therefore, private branches don’t really apply to your case. > > I’d suggest one of two other alternatives: > > 1. As with private branches, keep your SQLite changes in a clone of the > main SQLite repo, but set the “autosync” setting to “pullonly” and check > your local changes in on a normal branch. Since you have no check-in > ability on the main SQLite repo and autosync is set to not push local > changes, you’re set. > > The main thing you have to watch out for is that if you ever *do* get > check-in rights on the main SQLite repo, that you never use that same > forked repo with that account, since the first sync will push all of your > historical changes up to SQLite. > > All branches in Fossil are effectively private when you don’t have > check-in capability. All the autosync setting does is prevent Fossil from > trying things you know will always fail. > > > 2. Keep your local mods in a separate repository — possibly managed by a > non-Fossil [D]VCS — and periodically merge changes in from SQLite. This > arrangement is usually called a “vendor branch:” > > http://svnbook.red-bean.com/en/1.7/svn.advanced.vendorbr.html > > That document gives it in terms of Subversion, but it works in any decent > [D]VCS. > > When using two different [D]VCSes, you do the “merge” in part with copy > commands: > > $ cd ~/path/to/sqlite/checkout > $ fossil update > $ make -j11 sqlite3.c # optional > $ cd ~/path/to/private/repo > $ git checkout sqlite-vendor-branch > $ cp ~/path/to/sqlite/checkout/src/* 3rd-party/sqlite > $ git commit -a > $ git checkout master, merge, etc. > > You could then script that process locally, so that you give only a single > command, like “tools/update-sqlite”. > > > What is the workflow on making sure the amalgamation source doesn't > modify > > local changes? > > Vendor branches. You want to do that even with my option #1 above or with > drh’s offering of private branches. In the latter case, SQLite “trunk” is > the vendor branch, and your branch off of that is your project’s effective > “trunk”. > > Keep in mind that in Fossil, “trunk” isn’t all that special. It just > happens to be Fossil’s default when starting a new repo and it’s Fossil’s > best guess when you give it no other clues. Other than that, trunk is just > another branch in Fossil. > ___ > sqlite-users
Re: [sqlite] General Amalgamation vs Your Own
On Sep 21, 2019, at 11:29 AM, Stephen Chrzanowski wrote: > > How does one have their own code base for SQLite, with their own customer > logic or functionality or whatever, then, have updates provided by the > SQLite team implemented in when updates and such are provided? What kind of code are you talking about? With custom functions and loadable extensions, you don’t modify the SQLite core at all: https://sqlite.org/c3ref/create_function.html https://sqlite.org/loadext.html The latter allows a lot of flexibility, changing collation sequences, adding VFSes, etc. If you’re modifying the SQLite core, then why? Maybe it isn’t actually required that you do so, and we could show you a better way. Or, maybe SQLite extensions could be, ah, *extended* to give you the API you’d need to do this from the outside, without modifying the core. > I'm assuming Fossil would handle this kind of process with merging and such? I would not recommend using private branches, even though drh offered it, because it’s not a commonly-used feature of Fossil, so it’s not as well-tested and well-supported as Fossil’s mainstream usage code paths. If you ever run into trouble using private branches in Fossil, you’re more likely to get “crickets” as a response than with similar trouble within the well-used features of Fossil. Even when private branches are the right answer, they exist for a different use case than what you’ve got going here: you have check-in rights on the parent repo you cloned from, but you don’t want all local check-ins to sync up to the parent repo. I don’t see any user with check-ins on the SQLite code repo that looks like your name or your Gmail user name, so I’m assuming you *don’t* have check-in rights on the SQLite code repo. Therefore, private branches don’t really apply to your case. I’d suggest one of two other alternatives: 1. As with private branches, keep your SQLite changes in a clone of the main SQLite repo, but set the “autosync” setting to “pullonly” and check your local changes in on a normal branch. Since you have no check-in ability on the main SQLite repo and autosync is set to not push local changes, you’re set. The main thing you have to watch out for is that if you ever *do* get check-in rights on the main SQLite repo, that you never use that same forked repo with that account, since the first sync will push all of your historical changes up to SQLite. All branches in Fossil are effectively private when you don’t have check-in capability. All the autosync setting does is prevent Fossil from trying things you know will always fail. 2. Keep your local mods in a separate repository — possibly managed by a non-Fossil [D]VCS — and periodically merge changes in from SQLite. This arrangement is usually called a “vendor branch:” http://svnbook.red-bean.com/en/1.7/svn.advanced.vendorbr.html That document gives it in terms of Subversion, but it works in any decent [D]VCS. When using two different [D]VCSes, you do the “merge” in part with copy commands: $ cd ~/path/to/sqlite/checkout $ fossil update $ make -j11 sqlite3.c # optional $ cd ~/path/to/private/repo $ git checkout sqlite-vendor-branch $ cp ~/path/to/sqlite/checkout/src/* 3rd-party/sqlite $ git commit -a $ git checkout master, merge, etc. You could then script that process locally, so that you give only a single command, like “tools/update-sqlite”. > What is the workflow on making sure the amalgamation source doesn't modify > local changes? Vendor branches. You want to do that even with my option #1 above or with drh’s offering of private branches. In the latter case, SQLite “trunk” is the vendor branch, and your branch off of that is your project’s effective “trunk”. Keep in mind that in Fossil, “trunk” isn’t all that special. It just happens to be Fossil’s default when starting a new repo and it’s Fossil’s best guess when you give it no other clues. Other than that, trunk is just another branch in Fossil. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [EXTERNAL] Group-by and order-by-desc does not work as expected
Good to know that I was not to far off target then. But fixing issues in less than a day of reporting? On a Saturday? Who does that? I was planning to feel happy about solving this issue.. :) Fredrik On Sat, Sep 21, 2019 at 9:31 PM Dan Kennedy wrote: > > On 22/9/62 02:25, Fredrik Larsen wrote: > > Interesting, very similar change but not fully idenctial. In my patch, I > > created a sqlite3ExprListCompareIgnoreButUpdateSort, and used this > function > > from line 6239. This function ignores the sort part when comparing > > expressions, but will update the GroupBy sortOrder field if expressions > are > > found equal. I see dan modifies the sortFlag. > > That sounds equivalent to me. The sortOrder/sortFlag thing is probably > just because you patched the last release (3.29.0) or earlier. The field > has changed names since then. > > Dan. > > > > > Would be interesting to know > > what effect this difference has. My change works in my test, but not sure > > if it really works. > > > > Anyway, super-nice that this issue is fixed officially. No I don't have > to > > wonder if my fix is really correct, or will suddenly corrupt something :) > > > > Fredrik > > > > On Sat, Sep 21, 2019 at 8:49 PM Keith Medcalf > wrote: > > > >> See Dan's checkin on trunk for this issue. > >> > >> https://www.sqlite.org/src/info/20f7951bb238ddc0 > >> > >>> -Original Message- > >>> From: sqlite-users On > >>> Behalf Of Fredrik Larsen > >>> Sent: Saturday, 21 September, 2019 08:12 > >>> To: SQLite mailing list > >>> Subject: Re: [sqlite] [EXTERNAL] Group-by and order-by-desc does not > work > >>> as expected > >>> > >>> Your last sentence got me thinking. So I downloaded the source, > modified > >>> the ordering of the GROUP-BY expression to match ORDER-BY and it works! > >>> This will offcourse only work if the GROUP-BY and ORDER-BY matches > >>> generally expect for the direction. This fix only improves performance > >>> for > >>> relevant cases and keeps other cases unaffected. Not sure if I > introduced > >>> some subtle bugs with this modification, but my test-cases runs fine. > >>> > >>> Fredrik > >>> > >>> On Fri, Sep 20, 2019 at 6:57 PM Keith Medcalf > >>> wrote: > >>> > > We can observe GROUP BY works ASCending only as of now. Why it can't > >>> work > > DESCending to avoid ordering, that's a different question. > >From https://www.sqlite.org/lang_select.html we can observe that > > GROUP BY takes an expr on the RHS, while ORDER BY takes an expr > > followed by optional COLLATE and ASC/DESC terms. > The GROUP BY clause does not imply ordering. The fact that the output > >>> is > ordered is an implementation detail -- the grouping could be > >>> implemented by > a hash table, in which case the output would be ordered by hash value, > >>> for > instance. All that the expression in a GROUP BY does is determine the > groupings, and therefore the expression is limited to a comparison > compatible expression. For example, you can GROUP BY x COLLATE NOCASE > which implies that the groups are formed using case insensitive > >>> comparisons > of x. The ORDER BY clause determines the output ordering. > > You will note that if you do the following: > > create table x(x,y); > create index ix on x(x desc, y); > select x, someaggregate(y) from x group by x order by x desc; > > then ix will be used as a covering index (which is good) however the > >>> group > by x is treated as an ordering expression, not as simply a grouping > expression. > > In fact the code that implements the group by does indeed (perhaps > erroneously) treat the group by expression as implying order, since it > >>> will > traverse the covering index in reverse order so that the output from > >>> GROUP > BY is in ascending order, and add an extra sort to do the ORDER BY. > > That means the GROUP BY code generator is already capable of > traversing > the selected index in reverse order when necessary. It appears that > >>> the > optimizer however does not recognize that the "desc" attribute from > the > order by can be "pushed down" into the GROUP BY (which really is > >>> ordering > as an implementation detail) thus eliminating the ORDER BY processing > entirely. > > Note that you cannot specify that the GROUP BY is ordering -- it will > >>> not > accept the ASC or DESC keywords (which is correct), and this should > not > >>> be > changed, however, treating it as being ordering when it is not might > perhaps be a defect ... > > > > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > >>> ___ > >>> sqlite-users mailing list > >>>
Re: [sqlite] [EXTERNAL] Group-by and order-by-desc does not work as expected
On 22/9/62 02:25, Fredrik Larsen wrote: Interesting, very similar change but not fully idenctial. In my patch, I created a sqlite3ExprListCompareIgnoreButUpdateSort, and used this function from line 6239. This function ignores the sort part when comparing expressions, but will update the GroupBy sortOrder field if expressions are found equal. I see dan modifies the sortFlag. That sounds equivalent to me. The sortOrder/sortFlag thing is probably just because you patched the last release (3.29.0) or earlier. The field has changed names since then. Dan. Would be interesting to know what effect this difference has. My change works in my test, but not sure if it really works. Anyway, super-nice that this issue is fixed officially. No I don't have to wonder if my fix is really correct, or will suddenly corrupt something :) Fredrik On Sat, Sep 21, 2019 at 8:49 PM Keith Medcalf wrote: See Dan's checkin on trunk for this issue. https://www.sqlite.org/src/info/20f7951bb238ddc0 -Original Message- From: sqlite-users On Behalf Of Fredrik Larsen Sent: Saturday, 21 September, 2019 08:12 To: SQLite mailing list Subject: Re: [sqlite] [EXTERNAL] Group-by and order-by-desc does not work as expected Your last sentence got me thinking. So I downloaded the source, modified the ordering of the GROUP-BY expression to match ORDER-BY and it works! This will offcourse only work if the GROUP-BY and ORDER-BY matches generally expect for the direction. This fix only improves performance for relevant cases and keeps other cases unaffected. Not sure if I introduced some subtle bugs with this modification, but my test-cases runs fine. Fredrik On Fri, Sep 20, 2019 at 6:57 PM Keith Medcalf wrote: We can observe GROUP BY works ASCending only as of now. Why it can't work DESCending to avoid ordering, that's a different question. >From https://www.sqlite.org/lang_select.html we can observe that GROUP BY takes an expr on the RHS, while ORDER BY takes an expr followed by optional COLLATE and ASC/DESC terms. The GROUP BY clause does not imply ordering. The fact that the output is ordered is an implementation detail -- the grouping could be implemented by a hash table, in which case the output would be ordered by hash value, for instance. All that the expression in a GROUP BY does is determine the groupings, and therefore the expression is limited to a comparison compatible expression. For example, you can GROUP BY x COLLATE NOCASE which implies that the groups are formed using case insensitive comparisons of x. The ORDER BY clause determines the output ordering. You will note that if you do the following: create table x(x,y); create index ix on x(x desc, y); select x, someaggregate(y) from x group by x order by x desc; then ix will be used as a covering index (which is good) however the group by x is treated as an ordering expression, not as simply a grouping expression. In fact the code that implements the group by does indeed (perhaps erroneously) treat the group by expression as implying order, since it will traverse the covering index in reverse order so that the output from GROUP BY is in ascending order, and add an extra sort to do the ORDER BY. That means the GROUP BY code generator is already capable of traversing the selected index in reverse order when necessary. It appears that the optimizer however does not recognize that the "desc" attribute from the order by can be "pushed down" into the GROUP BY (which really is ordering as an implementation detail) thus eliminating the ORDER BY processing entirely. Note that you cannot specify that the GROUP BY is ordering -- it will not accept the ASC or DESC keywords (which is correct), and this should not be changed, however, treating it as being ordering when it is not might perhaps be a defect ... ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [EXTERNAL] Group-by and order-by-desc does not work as expected
Interesting, very similar change but not fully idenctial. In my patch, I created a sqlite3ExprListCompareIgnoreButUpdateSort, and used this function from line 6239. This function ignores the sort part when comparing expressions, but will update the GroupBy sortOrder field if expressions are found equal. I see dan modifies the sortFlag. Would be interesting to know what effect this difference has. My change works in my test, but not sure if it really works. Anyway, super-nice that this issue is fixed officially. No I don't have to wonder if my fix is really correct, or will suddenly corrupt something :) Fredrik On Sat, Sep 21, 2019 at 8:49 PM Keith Medcalf wrote: > > See Dan's checkin on trunk for this issue. > > https://www.sqlite.org/src/info/20f7951bb238ddc0 > > >-Original Message- > >From: sqlite-users On > >Behalf Of Fredrik Larsen > >Sent: Saturday, 21 September, 2019 08:12 > >To: SQLite mailing list > >Subject: Re: [sqlite] [EXTERNAL] Group-by and order-by-desc does not work > >as expected > > > >Your last sentence got me thinking. So I downloaded the source, modified > >the ordering of the GROUP-BY expression to match ORDER-BY and it works! > >This will offcourse only work if the GROUP-BY and ORDER-BY matches > >generally expect for the direction. This fix only improves performance > >for > >relevant cases and keeps other cases unaffected. Not sure if I introduced > >some subtle bugs with this modification, but my test-cases runs fine. > > > >Fredrik > > > >On Fri, Sep 20, 2019 at 6:57 PM Keith Medcalf > >wrote: > > > >> >We can observe GROUP BY works ASCending only as of now. Why it can't > >work > >> >DESCending to avoid ordering, that's a different question. > >> >From https://www.sqlite.org/lang_select.html we can observe that > >> >GROUP BY takes an expr on the RHS, while ORDER BY takes an expr > >> >followed by optional COLLATE and ASC/DESC terms. > >> > >> The GROUP BY clause does not imply ordering. The fact that the output > >is > >> ordered is an implementation detail -- the grouping could be > >implemented by > >> a hash table, in which case the output would be ordered by hash value, > >for > >> instance. All that the expression in a GROUP BY does is determine the > >> groupings, and therefore the expression is limited to a comparison > >> compatible expression. For example, you can GROUP BY x COLLATE NOCASE > >> which implies that the groups are formed using case insensitive > >comparisons > >> of x. The ORDER BY clause determines the output ordering. > >> > >> You will note that if you do the following: > >> > >> create table x(x,y); > >> create index ix on x(x desc, y); > >> select x, someaggregate(y) from x group by x order by x desc; > >> > >> then ix will be used as a covering index (which is good) however the > >group > >> by x is treated as an ordering expression, not as simply a grouping > >> expression. > >> > >> In fact the code that implements the group by does indeed (perhaps > >> erroneously) treat the group by expression as implying order, since it > >will > >> traverse the covering index in reverse order so that the output from > >GROUP > >> BY is in ascending order, and add an extra sort to do the ORDER BY. > >> > >> That means the GROUP BY code generator is already capable of traversing > >> the selected index in reverse order when necessary. It appears that > >the > >> optimizer however does not recognize that the "desc" attribute from the > >> order by can be "pushed down" into the GROUP BY (which really is > >ordering > >> as an implementation detail) thus eliminating the ORDER BY processing > >> entirely. > >> > >> Note that you cannot specify that the GROUP BY is ordering -- it will > >not > >> accept the ASC or DESC keywords (which is correct), and this should not > >be > >> changed, however, treating it as being ordering when it is not might > >> perhaps be a defect ... > >> > >> > >> > >> ___ > >> sqlite-users mailing list > >> sqlite-users@mailinglists.sqlite.org > >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > >> > >___ > >sqlite-users mailing list > >sqlite-users@mailinglists.sqlite.org > >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [EXTERNAL] Group-by and order-by-desc does not work as expected
See Dan's checkin on trunk for this issue. https://www.sqlite.org/src/info/20f7951bb238ddc0 >-Original Message- >From: sqlite-users On >Behalf Of Fredrik Larsen >Sent: Saturday, 21 September, 2019 08:12 >To: SQLite mailing list >Subject: Re: [sqlite] [EXTERNAL] Group-by and order-by-desc does not work >as expected > >Your last sentence got me thinking. So I downloaded the source, modified >the ordering of the GROUP-BY expression to match ORDER-BY and it works! >This will offcourse only work if the GROUP-BY and ORDER-BY matches >generally expect for the direction. This fix only improves performance >for >relevant cases and keeps other cases unaffected. Not sure if I introduced >some subtle bugs with this modification, but my test-cases runs fine. > >Fredrik > >On Fri, Sep 20, 2019 at 6:57 PM Keith Medcalf >wrote: > >> >We can observe GROUP BY works ASCending only as of now. Why it can't >work >> >DESCending to avoid ordering, that's a different question. >> >From https://www.sqlite.org/lang_select.html we can observe that >> >GROUP BY takes an expr on the RHS, while ORDER BY takes an expr >> >followed by optional COLLATE and ASC/DESC terms. >> >> The GROUP BY clause does not imply ordering. The fact that the output >is >> ordered is an implementation detail -- the grouping could be >implemented by >> a hash table, in which case the output would be ordered by hash value, >for >> instance. All that the expression in a GROUP BY does is determine the >> groupings, and therefore the expression is limited to a comparison >> compatible expression. For example, you can GROUP BY x COLLATE NOCASE >> which implies that the groups are formed using case insensitive >comparisons >> of x. The ORDER BY clause determines the output ordering. >> >> You will note that if you do the following: >> >> create table x(x,y); >> create index ix on x(x desc, y); >> select x, someaggregate(y) from x group by x order by x desc; >> >> then ix will be used as a covering index (which is good) however the >group >> by x is treated as an ordering expression, not as simply a grouping >> expression. >> >> In fact the code that implements the group by does indeed (perhaps >> erroneously) treat the group by expression as implying order, since it >will >> traverse the covering index in reverse order so that the output from >GROUP >> BY is in ascending order, and add an extra sort to do the ORDER BY. >> >> That means the GROUP BY code generator is already capable of traversing >> the selected index in reverse order when necessary. It appears that >the >> optimizer however does not recognize that the "desc" attribute from the >> order by can be "pushed down" into the GROUP BY (which really is >ordering >> as an implementation detail) thus eliminating the ORDER BY processing >> entirely. >> >> Note that you cannot specify that the GROUP BY is ordering -- it will >not >> accept the ASC or DESC keywords (which is correct), and this should not >be >> changed, however, treating it as being ordering when it is not might >> perhaps be a defect ... >> >> >> >> ___ >> sqlite-users mailing list >> sqlite-users@mailinglists.sqlite.org >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >> >___ >sqlite-users mailing list >sqlite-users@mailinglists.sqlite.org >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] General Amalgamation vs Your Own
On 9/21/19, Stephen Chrzanowski wrote: > > How does one have their own code base for SQLite, with their own customer > logic or functionality or whatever, then, have updates provided by the > SQLite team implemented in when updates and such are provided? > https://www.sqlite.org/privatebranch.html -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] General Amalgamation vs Your Own
With a post that someone made here, and the thought that I've been running in my head over the past while... How does one have their own code base for SQLite, with their own customer logic or functionality or whatever, then, have updates provided by the SQLite team implemented in when updates and such are provided? I'm assuming Fossil would handle this kind of process with merging and such? What is the workflow on making sure the amalgamation source doesn't modify local changes? ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [EXTERNAL] Group-by and order-by-desc does not work as expected
To clarify; GROUP-BY does not really have ordering, but in the SQLite implementation, GROUP-BY and ORDER-BY is very closely related as expected, and it is possible to set a GROUP-BY direction in code (it is default 0 -> ASC). So thats what I did. Also, some other modifications very required to stop SQLite from assuming to much about GROUP-BY queries. Fredrik On Sat, Sep 21, 2019 at 4:12 PM Fredrik Larsen wrote: > Your last sentence got me thinking. So I downloaded the source, modified > the ordering of the GROUP-BY expression to match ORDER-BY and it works! > This will offcourse only work if the GROUP-BY and ORDER-BY matches > generally expect for the direction. This fix only improves performance for > relevant cases and keeps other cases unaffected. Not sure if I introduced > some subtle bugs with this modification, but my test-cases runs fine. > > Fredrik > > On Fri, Sep 20, 2019 at 6:57 PM Keith Medcalf wrote: > >> >We can observe GROUP BY works ASCending only as of now. Why it can't work >> >DESCending to avoid ordering, that's a different question. >> >From https://www.sqlite.org/lang_select.html we can observe that >> >GROUP BY takes an expr on the RHS, while ORDER BY takes an expr >> >followed by optional COLLATE and ASC/DESC terms. >> >> The GROUP BY clause does not imply ordering. The fact that the output is >> ordered is an implementation detail -- the grouping could be implemented by >> a hash table, in which case the output would be ordered by hash value, for >> instance. All that the expression in a GROUP BY does is determine the >> groupings, and therefore the expression is limited to a comparison >> compatible expression. For example, you can GROUP BY x COLLATE NOCASE >> which implies that the groups are formed using case insensitive comparisons >> of x. The ORDER BY clause determines the output ordering. >> >> You will note that if you do the following: >> >> create table x(x,y); >> create index ix on x(x desc, y); >> select x, someaggregate(y) from x group by x order by x desc; >> >> then ix will be used as a covering index (which is good) however the >> group by x is treated as an ordering expression, not as simply a grouping >> expression. >> >> In fact the code that implements the group by does indeed (perhaps >> erroneously) treat the group by expression as implying order, since it will >> traverse the covering index in reverse order so that the output from GROUP >> BY is in ascending order, and add an extra sort to do the ORDER BY. >> >> That means the GROUP BY code generator is already capable of traversing >> the selected index in reverse order when necessary. It appears that the >> optimizer however does not recognize that the "desc" attribute from the >> order by can be "pushed down" into the GROUP BY (which really is ordering >> as an implementation detail) thus eliminating the ORDER BY processing >> entirely. >> >> Note that you cannot specify that the GROUP BY is ordering -- it will not >> accept the ASC or DESC keywords (which is correct), and this should not be >> changed, however, treating it as being ordering when it is not might >> perhaps be a defect ... >> >> >> >> ___ >> sqlite-users mailing list >> sqlite-users@mailinglists.sqlite.org >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >> > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [EXTERNAL] Group-by and order-by-desc does not work as expected
Your last sentence got me thinking. So I downloaded the source, modified the ordering of the GROUP-BY expression to match ORDER-BY and it works! This will offcourse only work if the GROUP-BY and ORDER-BY matches generally expect for the direction. This fix only improves performance for relevant cases and keeps other cases unaffected. Not sure if I introduced some subtle bugs with this modification, but my test-cases runs fine. Fredrik On Fri, Sep 20, 2019 at 6:57 PM Keith Medcalf wrote: > >We can observe GROUP BY works ASCending only as of now. Why it can't work > >DESCending to avoid ordering, that's a different question. > >From https://www.sqlite.org/lang_select.html we can observe that > >GROUP BY takes an expr on the RHS, while ORDER BY takes an expr > >followed by optional COLLATE and ASC/DESC terms. > > The GROUP BY clause does not imply ordering. The fact that the output is > ordered is an implementation detail -- the grouping could be implemented by > a hash table, in which case the output would be ordered by hash value, for > instance. All that the expression in a GROUP BY does is determine the > groupings, and therefore the expression is limited to a comparison > compatible expression. For example, you can GROUP BY x COLLATE NOCASE > which implies that the groups are formed using case insensitive comparisons > of x. The ORDER BY clause determines the output ordering. > > You will note that if you do the following: > > create table x(x,y); > create index ix on x(x desc, y); > select x, someaggregate(y) from x group by x order by x desc; > > then ix will be used as a covering index (which is good) however the group > by x is treated as an ordering expression, not as simply a grouping > expression. > > In fact the code that implements the group by does indeed (perhaps > erroneously) treat the group by expression as implying order, since it will > traverse the covering index in reverse order so that the output from GROUP > BY is in ascending order, and add an extra sort to do the ORDER BY. > > That means the GROUP BY code generator is already capable of traversing > the selected index in reverse order when necessary. It appears that the > optimizer however does not recognize that the "desc" attribute from the > order by can be "pushed down" into the GROUP BY (which really is ordering > as an implementation detail) thus eliminating the ORDER BY processing > entirely. > > Note that you cannot specify that the GROUP BY is ordering -- it will not > accept the ASC or DESC keywords (which is correct), and this should not be > changed, however, treating it as being ordering when it is not might > perhaps be a defect ... > > > > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
