Re: [sqlite] Consider adding aggregate function "string_agg" as synonym of "group_concat"

On Sun, Sep 2, 2018 at 12:29 AM Simon Slavin wrote: > On 1 Sep 2018, at 11:03pm, Zsbán Ambrus wrote: > > Would you consider adding an aggregate function "string_agg" which is > > a synonym for the "group_concat" function but takes only two > > argume

[sqlite] Consider adding aggregate function "string_agg" as synonym of "group_concat"

quot; function. This would be very easy to implement from the SQL core, which already implements group_concat, and more difficult to implement as a user extension that only uses the SQLite API. SQLite already has a precedent for having a synonym for a function provided by the core: "ifnull" is a sy

Re: [sqlite] "Responsive" website revamp at www.sqlite.org

On Mon, Sep 5, 2016 at 10:55 PM, Richard Hipp wrote: > Most of the world views the internet on their phone now, I am told, > and websites are suppose to be "responsive", Most of the changes work well as far as I've seen, but I have one problem. The page

Re: [sqlite] A problem with 'pragma table_info(...)'

On Wed, Sep 21, 2016 at 9:57 AM, Stepan Zakharov wrote: > We are using VFS however, may be that can be ill-implemented somehow so it > makes PRAGMA not to work.. It can. And I think you're the third person on the mailing list to fall into that trap. From an earlier mail

Re: [sqlite] Problem with rename table

On Tue, Sep 6, 2016 at 3:37 PM, Radovan Antloga wrote: > I can't find a solution how to fix my database after > I have renamed table DOKUMENTI to DOKUMENTI2. > Table DOKUMENTI had trigger dokumenti_trigger1 > and after renaming table I cant execute any sql. I forgot > to

Re: [sqlite] Number of SQLITE_OMIT_ defines not working correctly

On Sat, Jul 16, 2016 at 6:53 PM, Olivier Mascia wrote: > I think you might have skipped this: > https://www.sqlite.org/compile.html#omitfeatures > >> Important Note: The SQLITE_OMIT_* options may not work with the >> amalgamation. SQLITE_OMIT_* compile-time options usually

Re: [sqlite] SQLite Logo

On Fri, Jun 24, 2016 at 2:03 PM, R.A. Nagy wrote: > I am putting together a commercial training for SQLite. I would like to use > the SQLite logo - as seen on the website - on the cover of the materials. SQLite has a logo... wow. I didn't notice that before. I just

Re: [sqlite] unable to set 'PRAGMA journal_mode = off' for vfs

On Tue, Jul 5, 2016 at 4:46 PM, Dobrean, Adrian wrote: > I am unable to turn off journal for vfs (I am using SQLite version 3.8.7.2). > I am not getting any error and the journal mode does not get set (i.e. to > PAGER_JOURNALMODE_OFF). > Any idea what am I doing wrong?

[sqlite] The Session Extension (future SQLite extension)

As for the session extension "https://www.sqlite.org/draft/sessionintro.html;, what I'd like to ask is when this is more useful than the RBU extension "http://sqlite.org/rbu.html; ? The two seem to serve a similar purpose. Ambrus

[sqlite] Work toward making the schema parsing logic simplier

On 1/15/16, Domingo Alvarez Duarte wrote: > Now that you are refactoring on the schema parsing could be nice if somehow > sqlite3 expose the schema/sql parser for developers. Note that sqlite3 already exposes most of the schema in a form readable to an application. The SQLITE_MASTER and

[sqlite] Index on computed value?

On Wed, Dec 16, 2015 at 9:17 AM, Deon Brewis wrote: > Is it possible to have an index on a computer value? > > E.g. I have a 40 byte value in one of my columns. I only want an index over > the first 4 bytes of it. > > However, I don't really want to repeat those 4 bytes inside another column on

[sqlite] SQLite crashes

On the SQLITE_OMIT_LOOKASIDE macro. On Fri, Nov 27, 2015 at 2:45 PM, Richard Hipp wrote: > The lookaside memory allocator is a fast memory pool used by > individual database connections for short-lived memory allocations. > It makes SQLite run faster, but by bypassing the system >

[sqlite] Minor errors in the documentation, building sqlite

On 11/9/15, Richard Hipp wrote: > It is traditional, in a permuted index, to include the complete > unedited title of each document in the index, even if that title > begins with a stop-word. You'll notice that documents that have > "SQLite" as an interior word are not listed among the "SQLite"

[sqlite] Minor errors in the documentation, building sqlite

Hello. In this mail I'd like to point out some minor problems with the SQLite documentation. (By the way, let me thank you for adding named anchors to every heading in the docs so I can link to them easily. A lot of webpages don't do this.) 1. In Compilation Options For SQLite

[sqlite] PRAGMA integrity_check says row 2 missing from index

Hello. The PRAGMA integrity_check is telling me that "row 2 missing from index tbvk". This is reproducible with the following commands. Should I be getting this message? Or is it a bug? SQLite version 3.9.2 2015-11-02 18:31:45 Enter ".help" for usage hints. Connected to a transient in-memory

[sqlite] Non-transitive numeric equality

Dear SQLite, It seems that equality of numeric values isn't transitive, when both integers and reals are involved. Here's an example output from the shell, which shows that the numeric value in the 'c' row is equal to both the value in the 'b' and the 'd' rows, but the value in the 'b' row isn't

[sqlite] The problem of inserting data too slow with index

On 10/30/15, aa wrote: >But recently I meet a problem about inserting data into table.It is > too slow whit index. >The first I create a table like this: > CREATE TABLE mac_tb (mac BIGINT PRIMARY KEY? > If I insert into mac_tb with mac ordey by num desc or asc ,

[sqlite] SQLite version 3.8.12 enters testing

On Wed, Oct 7, 2015 at 9:42 PM, Richard Hipp wrote: > On 10/7/15, Zsb?n Ambrus wrote: > New documentation covering indexes on expressions has been added. > Please let me know if you think more is needed. Thanks, that's much better. The "http://sqlite.org/draft/expridx.html; documentation tells

[sqlite] SQLite version 3.8.12 enters testing

On Wed, Oct 7, 2015 at 4:42 PM, Richard Hipp wrote: > A preliminary change log for version 3.8.12 can be seen at > https://www.sqlite.org/draft/releaselog/3_8_12.html and preliminary > documentation can be seen at https://www.sqlite.org/draft/ > > If you have issues or concerns with the current

[sqlite] Parser Grammar for MAX,MIN,SUM,COUNT,AVG functions

On Mon, Sep 21, 2015 at 9:11 AM, Prakash Premkumar wrote: > I'm reading the sqlite parser grammar. > > I could not find the grammar rules which parses aggregate functions like > MAX,MIN,SUM,COUNT,TOTAL. > > Can you please tell me how the aggregate functions are parsed (the grammar > rule that

[sqlite] Escape pathname to URL suitable for ATTACH

Hello, sqlite3 mailing list. The ATTACH command and the sqlite3_open function can take an URI instead of a plain filename, if sqlite3 is so configured. This is documented at "http://sqlite.org/uri.html;. This is useful, because it lets you specify extra options for attaching that you couldn't

[sqlite] Proper way to abort

On Mon, Aug 24, 2015 at 6:22 PM, Scott Doctor wrote: > I have some queries that may take 5-15 seconds to complete. Sometimes the > situation changes shortly after starting the query where my program does not > need those results anymore and the program wants to abort and begin a > different query

[sqlite] pragma temp_store_directory is deprecated, what is the alternative?

On Mon, Jul 27, 2015 at 9:35 PM, Simon Slavin wrote: > On 27 Jul 2015, at 8:03pm, Zsb?n Ambrus wrote: > I tried this once a couple of years ago, and both platforms use whatever the > expected variable name was for that OS. In other words, a native programmer > to that OS would get whatever

[sqlite] pragma temp_store_directory is deprecated, what is the alternative?

On Mon, Jul 27, 2015 at 12:28 PM, Richard Hipp wrote: > On 7/27/15, Paolo Bolzoni wrote: >> I found the temp_store_directory, but it is deprecated. So I was wondering, >> what is the suggested alternative? > > Set the TEMP environment variable to the location of your temporary > storage area.

[sqlite] Likelihood() in Left Join affecting results

On Thu, Jun 18, 2015 at 4:34 PM, Richard Hipp wrote: > Thanks for the bug report. The problem is now fixed on trunk. > > On 6/18/15, Jean Chevalier wrote: >> The likelihood() function, which should help select a query plan but >> otherwise be logic-neutral, nevertheless seems to affect results

[sqlite] Limit size of write-ahead log file

On 5/19/15, Kees Nuyt wrote: > Did you consider > PRAGMA wal_autocheckpoint=N; and > PRAGMA database.wal_checkpoint(); ? > > http://www.sqlite.org/pragma.html#pragma_wal_autocheckpoint Thank you for your reply. Yes, but that will not always limit the size of the wal file. I believe

[sqlite] Re : index broken by insert

On 5/20/15, REGIANY Lucie wrote: > J ai cr?? une table > CREATE TABLE table 1 (idsas (5) PRIMARY KEY NOT NULL); I don't see how that would work. Sqlite complains about syntax error for that. If "table 1" is meant to be the name of the table, you have to double quote it, because it contains a

[sqlite] Limit size of write-ahead log file

The PRAGMA max_page_count statement lets me set a limit on the size the database is allowed to grow. This is useful to protect myself against accidental errors in my program, where I fill the file system with a huge database file, which could disrupt other processes that are trying to write the

[sqlite] Can I define collation-aware SQL functions?

On 5/15/15, Richard Hipp wrote: > No there is not. The APIs used to implement those built-in functions > are not exposed to the application-defined function interface. Understood. Thank you for the reply. -- Ambrus

[sqlite] Determine type of prepared statement via C Interface?

On Fri, May 8, 2015 at 8:20 PM, Simon Slavin wrote: > Suppose you have this statement > > DELETE FROM myTable WHERE id=600 > > and there are no rows with id=600. I presume that the function will return > FALSE but a literal reading of the description says that it will return TRUE. > If I'm

[sqlite] Problems with pragma journal_mode

On Wed, Apr 22, 2015 at 10:46 AM, Janke, Julian wrote: > I have tested some of the other pragmas and none worked. After discussing > with you now I come to the conclusion that the problem is caused more by my > local setup. I In that case, as you have a custom vfs, could you check if it's your

[sqlite] Hex literals not working?

On Thu, Apr 16, 2015 at 10:29 AM, Ketil Froyn wrote: > Hexadecimal integer literals follow the C-language notation of > "0x" or "0X" followed > by hexadecimal digits. For example, 0x1234 means the same as 4660... > > Am I doing sometihng wrong? Or is this a feature that is newer than >

[sqlite] Request: Metadata about C API constants and functions

On 4/10/15, Dominique Devienne wrote: > But that's build time. There is no way (i.e. pragma) to list registered SQL > functions at runtime. > > This is something that I've asked for before, and I'm having trouble > understanding why nobody cares. > > When you run an SQLite "shell", the host

Re: [sqlite] sqlite3 tool bug

On 1/16/15, Dave Dyer wrote: > I have a class of database for which using sqlite3 to create > a copy via the "pipe" method fails. Using an explicit intermediate > file seems to work ok. > > I can supply a sample database to anyone interested in investigating. Rather

Re: [sqlite] Getting SQLITE_IOERR_WRITE when running sqlite

On 1/2/15, Dan Kennedy wrote: > On 01/02/2015 04:44 PM, Waiba, Aswin wrote: >> we were getting SQLITE_IOERR_WRITE (778). > > It means a call to write(), pwrite(), fallocate() or similar has failed. > Because it ran out of disk space, or the media was removed or perhaps is >

[sqlite] Docs: typos in SQLite Result Codes

There are some typos on the documentation page for SQLite Result codes "http://sqlite.org/rescode.html;. Under the heading "(1038) SQLITE_CANTOPEN_CONVPATH", the paragraph talks about the error code "SQLITE_CANTOPEN_SEEK". That constant does not exist. The text probably means

Re: [sqlite] BUG: Aggregate functions in subqueries

On 9/14/14, Lea Verou wrote: > Per the 3.7.11 changelog [1], queries of the form SELECT max(x), y FROM > table return the value of y from the same row that contains the maximum x > value. However, this: Hello! I don't think this is a bug. The documentation for the SELECT

Re: [sqlite] Docs: ON CONFLICT clause versus CHECK constraints

On 8/15/14, Richard Hipp wrote: > http://www.sqlite.org/draft/lang_conflict.html Thanks for fixing it. -- Ambrus ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Docs: ON CONFLICT clause versus CHECK constraints

Hi, I'd like to report a small confusion in the documentation. The documentation page for the ON CONFLICT clause at "http://sqlite.org/draft/lang_conflict.html; describes at the beginning what kind of constraints this clause deals with: > The ON CONFLICT clause applies to UNIQUE and NOT NULL

Re: [sqlite] Docs: link "How To Compile SQLite" from main documentation list

On 8/5/14, Richard Hipp <d...@sqlite.org> wrote: > On Tue, Aug 5, 2014 at 3:51 PM, Zsbán Ambrus <amb...@math.bme.hu> wrote: >> please link "http://sqlite.org/howtocompile.html; >> from "http://sqlite.org/docs.html; . Thank you for a

[sqlite] Segmentation fault in command line using aggregate function in column default value

Dear sqlite3 maintainers, I've got a segmentation fault when trying to execute the following two statements in the sqlite 3.8.5 command line program: CREATE TABLE tab(amt DEFAULT (max(1))); INSERT INTO tab DEFAULT VALUES; I believe this is a bug, because the vanilla command line should not

[sqlite] Docs: link "How To Compile SQLite" from main documentation list

Dear sqlite maintainers! Besides my previous question "Where exactly are parameters accepted in an expression?" let me ask an easier request for improving the sqlite3 documentation on the homepage. The document "How To Compile SQLite" at "http://sqlite.org/howtocompile.html; is very important,

Re: [sqlite] Expected behaviour of COUNT with no GROUP BY?

On 8/4/14, Mark Lawrence wrote: > I try to remember to define GROUP BY values when using aggregate > functions (and I wish SQLite considered it an error otherwise) but I > forget once and the result surprised me. > > It is expected behaviour that a row is returned in this

[sqlite] Where exactly are parameters accepted in an expression?

Hello sqlite list! The following statement gives an error in sqlite 3.8.4.3. CREATE VIEW vp AS SELECT ? AS x; The error message is: Error: parameters are not allowed in views The parameter (placeholder) is definitely be the problem here, because this similar statement executes without

Re: [sqlite] Hexadecimal integer literals

Hello! Just like the others in this conversation, I also believe that you must not change the rules how strings are converted to integers by type affinity, or by type conversions of arithmetic operators. Thus, you must not add hexadecimal representation to conversions (nor hex floats or 'inf'

Re: [sqlite] Regarding SQLITE installation

On Thu, Apr 17, 2014 at 9:04 PM, Nitin Nimran wrote: > I want to install sqlite for - Linux (X86_64) and NetBSD 6.1 (X86_64) > > What should be appropriate options for executing configure script to > install for specified target. Please see

Re: [sqlite] Making string changes in a table

On 3/9/14, Simon Slavin wrote: > Check out REPLACE(): > > Technically speaking this might mess up if the string '/path/to/' occurs in > the middle of the string as well as at its beginning, For that reason, I think it would be better to use the substr function. Ambrus

Re: [sqlite] SQL quine using with

And here's a quine which simply concatenates six named strings a lot of times. SELECT ab||a||a||a||a||aa||b||a||b||a||bb||b|| a||aa||a||aa||aa||b||a||bb||a||bb||bb||b|| a||ab||a||aa||bb||b||a||ba||a||bb||aa||ba FROM(SELECTa,','b,'a'aa,'b'bb,'SELECT ab||a||a||a||a||aa||b||a||b||a||bb||b||

Re: [sqlite] Out of memory error for SELECT char();

On 3/8/14, Simon Slavin wrote: > SQLite version 3.7.13 2012-07-17 17:46:21 The char function was added in 3.7.16. > Could you please post your OS and the version of the SQLite shell tool > you're using ? I'm using Linux amd64, compiling with gcc 4.8.1. I've got the out

Re: [sqlite] SQL quine using with

Anyway, here's a different quine using the replace function. SELECT replace(s,char(33),)||s||'''s);'FROM(SELECT'SELECT replace(s,char(33),)||s||!!!s);!FROM(SELECT!'s); ___ sqlite-users mailing list sqlite-users@sqlite.org

[sqlite] Out of memory error for SELECT char();

In the sqlite3 console, the following very simple statement gives "Error: out of memory": SELECT char(); I think this is a bug. This query should need very little memory, so it should not give such an error. I believe it should return a single row with a single value of an empty string. I've

Re: [sqlite] SQL quine using with

I have a favourite general method to write a quine in any programming language. This involves a list of strings and a list of numeric indexes. The second list is used to subscript into the first list, and the found strings are then extracted. This is possible in sqlite3, but comes out

Re: [sqlite] RPAD/LPAD

On 3/7/14, RSmith wrote: > Add to this the fact that you can - through SQL's ability to add > user-defined functions (an almost unique ability among SQL engines) Is that really so? I thought most sql engines had that. You can define functions in at least postgreSQL:

Re: [sqlite] Syntax diagram missing in sqlite3 docs

On 2/20/14, Zsbán Ambrus <amb...@math.bme.hu> wrote: > The page "http://sqlite.org/lang_transaction.html; should show the > syntax diagrams for commit-stmt and rollback-stmt. It seems that these bugs are now fixed in the draft documentation for sqlite 3.8.4. Thank you, s

[sqlite] Syntax diagram missing in sqlite3 docs

Hi, The page "http://sqlite.org/lang_transaction.html; should show the syntax diagrams for commit-stmt and rollback-stmt. Similarly, the page "http://sqlite.org/lang_savepoint.html; should show the diagram for rollback-stmt. These missing diagrams show up correctly in

Re: [sqlite] Field not editable

On 2/13/14, O.D. vidal wrote: > I would like a field is not editable. What is the best solution? > > I want the name of the country can not be changed. You can put all the tables you want to be not changable to a separate database file which you attach as read-only. Use the

Re: [sqlite] Syntax diagram in draft docs for sqlite 3.8.3 confuses me

On 1/30/14, Richard Hipp wrote: > Fixed. Do you see any more problems? Sqlite 3.8.3 is now released, but I found one more problem today. The page "http://sqlite.org/lang_transaction.html; should show the syntax diagrams for commit-stmt and rollback-stmt. Similarly, the page

[sqlite] Order of rows in a VALUES select expression

The future release sqlite 3.8.3 adds VALUES statements as a shortcut form of SELECT statements. This shortcut simply creates a results set made of any number of rows, any number of (unnamed) columns in each, and all the values given by separate explicit expressions. My question is whether sqlite

Re: [sqlite] order of = in join

On 1/30/14, E. Timothy Uy wrote: > #1 - f.term = t.term > Query #1 takes 300 ms, and query #2 takes 30 ms. Can you show the schema for the tables? Is it possible that the two equals comparisons use different affinity or collation, which changes the semantics of your statement?

Re: [sqlite] Syntax diagram in draft docs for sqlite 3.8.3 confuses me

On 1/30/14, Richard Hipp wrote: > Thanks Kevin and Ambrus for the error reports. A revised copy of the > documentation is up at http://www.sqlite.org/draft/lang_select.html - > please let me know if you see any other problems. Great, but the diagram for table-or-subquery still

Re: [sqlite] Syntax diagram in draft docs for sqlite 3.8.3 confuses me

On 1/30/14, Richard Hipp wrote: > Documentation on SELECT statements at > http://www.sqlite.org/draft/lang_select.html has now been updated and > amplified. Thank you, that seems cleaner. However, there seems to be an error. From the diagram for select-stmt and also in the

[sqlite] Syntax diagram in draft docs for sqlite 3.8.3 confuses me

Hi! I'm writing to you about the syntax diagram that appears on "http://sqlite.org/draft/lang_select.html;, and is a draft for the next version of sqlite (3.8.3). I find this diagram confusing, and would rather prefer to have something similar to "http://sqlite.org/lang_select.html;, only of

Re: [sqlite] WAL/TCL Question

On 1/14/14, Tilsley, Jerry M. wrote: > I have a database that I am using with a couple processes that will modify > the database at near the same time. So I thought this would be the ideal > time to investigate the WAL mode. How do I enable this on the database, and

[sqlite] Does not detect invalid column name when subexpression optimized away

Why does the following select statement succeed, instead of giving a "no column error"? Is this a feature or a bug? sqlite> select nonsensename and 0; 0 sqlite> .version SQLite 3.8.1 2013-10-17 12:57:35 c78be6d786c19073b3a6730dfe3fb1be54f5657a The select statement is not a subquery and has no

Re: [sqlite] Failed test on aarch64

On Tue, Nov 26, 2013 at 5:01 PM, Richard Hipp wrote: > The check-in above changes the behavior of REAL-to-INTEGER casts where the > REAL value is larger than the largest possible integer. For example: > > SELECT CAST(9223372036854775808.0 to INTEGER); > > The change causes

Re: [sqlite] What takes the most time

On Wed, Nov 13, 2013 at 11:50 PM, David de Regt wrote: > If a single _step() call takes a long time to execute (a few minutes), is my > only option to just wait for it? Does SQLite not allow any kind of callback > mechanism for each _step() to indicate how many percentages

Re: [sqlite] Where does SQLite write out files?

On 11/7/13, L. Wood wrote: > What directories can SQLite possibly write files to? > > * I'm aware of the files that SQLite can write to the *same* directory as > that of the actual database file, Take particular attention to master journal files. As

Re: [sqlite] Handling move/rename of an SQLite database file

On Thu, Nov 7, 2013 at 4:31 PM, L. Wood wrote: > Users could rename/move a database file while my (Mac OS X) program has made > an SQLite connection to the file. I want to handle this properly. One thing you have to be particularly careful about is the extra files sqlite

Re: [sqlite] SQLite keeps on searching endlessly

On 10/19/13, Raheel Gupta wrote: > Does SQLite support multi column primary keys ? Yes. > Also wouldnt primary keys actually slow down further inserts. I have > queries to insert nearly 1 rows in one second. With larger database > multi column primary keys might slow