Re: [sqlite] Bug in SQLite FLOAT values

On 2015/02/07 15:47, Abdul Aziz wrote: Thanks for replybut I am now using VARCHARS, then how this is working? not generating any errors? Please elaborate, my query to create DB is: mSQLiteDatabase.execSQL("CREATE TABLE " + tableName + " ( " + EVENT_TIME + " INTEGER, " + SYSTEM_TIME + "

Re: [sqlite] why does the recursive example sort alphabetically

On 2015/02/04 15:45, Mark Summerfield wrote: Hi, In the documentation on page http://www.sqlite.org/lang_with.html there is an example of a recursive query if you scroll down to the heading "Controlling Depth-First Versus Breadth-First Search Of a Tree Using ORDER BY". The second example

Re: [sqlite] Possible to get table size (in bytes)?

On 2015/02/04 20:26, Rael Bauer wrote: Hi, Is it possible to get the information of how many bytes a table is taking up in the database? Not with API calls, but it is possible by running the SQLiteAnalyzer utility afvailable from the same download pages as SQLite3 CLI etc.

Re: [sqlite] Looking for SQLite schema doc generator (in HTML) for tables, fields, etc

On 2015/02/03 17:34, Gerald Bauer wrote: Hello, I'm looking for a little tool that reads in an SQLite schema (e.g. beer.db, football.db, etc.) and outputs (generates) documentation for tables, fields etc. as a single HTML page or as HTML pages.Any insight appreciated? Actually I

Re: [sqlite] Encoding question

On 2015/02/02 19:37, Peter Haworth wrote: On Mon, Feb 2, 2015 at 9:00 AM, <sqlite-users-requ...@sqlite.org> wrote: From: RSmith <rsm...@rsweb.co.za> To: sqlite-users@sqlite.org Subject: Re: [sqlite] Encoding question Message-ID: <54cebb71.8060...@rsweb.co.za> Content-Type: t

Re: [sqlite] Encoding question

On 2015/02/02 01:12, Peter Haworth wrote: I'm new to the unicode world so this question may not make sense. The "PRAGMA encoding" statement tells me the encoding of a database. Can I rely on all data in the database having that encoding? For example, if the encoding is UTF8 and a row is

Re: [sqlite] "database disk image is malformed" error occurs more

On 2015/01/30 14:45, Mario M. Westphal wrote: - The databases in question are stored on a location hard disk or SSD. - If a user stores his database on a NAS box or Windows server, it is accessed directly, via standard Windows file system routines. - From what I can tell, network-based

Re: [sqlite] Truncation of floating point numbers in SQLite?

On 2015/01/30 05:49, Donald Shepherd wrote: Trying to retrieve a stored qNaN or sNaN returns a column type of NULL and a value of 0. Thank you for letting us know. Well I suppose that's SQLite's method to answer with errors of the sort, returning NULL (as is the case with div0 for

Re: [sqlite] Truncation of floating point numbers in SQLite?

On 2015/01/29 05:05, James K. Lowden wrote: There's no reason to think, if the data are provided in binary form, that they won't be returned in the identical form absent an explicit conversion. If that's not so, I'd sure like to know why. I'm faintly surprised NaNs can't be stored, too. Why

Re: [sqlite] Truncation of floating point numbers in SQLite?

On 2015/01/29 01:00, Donald Shepherd wrote: I can say there's no string round-trips with fairly high confidence and I am using _bind_double and _column_double. I can also confirm NaNs are a special case, as I've had to write code to store those as a BLOB. So you have a case where you have a

Re: [sqlite] "database disk image is malformed" error occurs more

On 2015/01/28 20:06, Mario M. Westphal wrote: 1. I don’t have the damaged databases here so I cannot run the diagnosis myself. The databases are usually too large to upload or transfer. 2. The SQLite version I currently use is 3.8.8.1 (complied using the Amalgation and Visual Studio 2012).

Re: [sqlite] Invalid column prefix returned in SELECT with joined subquery

On 2015/01/26 14:00, Tim Streater wrote: On 26 Jan 2015 at 07:33, Hick Gunter wrote: It is never a good idea to rely on automatically assigned column names. If you want reproducible, predictable, release independant column names then please assign them with the AS clause.

Re: [sqlite] Invalid column prefix returned in SELECT with joined subquery

On 2015/01/26 04:04, James K. Lowden wrote: On Sun, 25 Jan 2015 23:18:05 +0200 RSmith <rsm...@rsweb.co.za> wrote: There is no documentation in either SQLite or the SQL standard that would lead anyone to believe that behavior is expected - in fact it is very clear about the returned

Re: [sqlite] Invalid column prefix returned in SELECT with joined subquery

On 2015/01/25 15:16, Marcus Bergner wrote: Hi, Using the latest amalgamation build sqlite-autoconf-3080801 I'm seeing the following inconsistent behaviour: $ ./sqlite3 sqlite> .headers on sqlite> pragma short_column_names; 1 sqlite> pragma full_column_names; 0 sqlite> create table tbl1 (id1

Re: [sqlite] Having problems with Entity Framework code first db creation

On 2015/01/23 16:51, Walter Williams wrote: I'm trying to use a code first model ///... (snipped) then in the signature... "Do, or do not. There is no try." Thank you for the chuckle. As to the actual question, when you say "when I try

Re: [sqlite] ORDER BY and LIMIT regression

Might this not be a "reverse_unordered_selects" pragma or compile option going wrong, or at least the code making it work getting somehow hooked in the new versions for this query? I have seen similar things when using that pragma (but of course that was intended). Just a thought... On

Re: [sqlite] Malformatted output by sqlite3

On 2015/01/19 12:52, Stephan Buchert wrote: (Prompt erased for easier paste and copy): CREATE TABLE satpos(msec INTEGER, latitude REAL, longitude REAL); INSERT INTO satpos VALUES (86386217,-0.0318895369716216,-167.689719869132); INSERT INTO satpos VALUES

Re: [sqlite] Shell tool improvement request

On 2015/01/16 18:33, Simon Slavin wrote: (following description simplified) I have a text file I wanted to .import into a table. The text file has two columns separated by a tab: a word and a number. It starts off like this: ! 32874624 " 239874242 # 98235252 $ 438743824

Re: [sqlite] Huge WAL log

On 2015/01/16 11:33, Jan Slodicka wrote: The code schema is as follows: foreach table { BEGIN INSERT INTO table VALUES() INSERT INTO table VALUES() ... COMMIT } Large column values are supplied as parameters, the rest (vast majority) is passed through SQL

Re: [sqlite] Inserting/removing large number of rows with index

On 2015/01/15 23:18, Baruch Burstein wrote: Hi, If I have a table with an index, and INSERT or DELETE a large number of rows in one statement, does sqlite stop to update the index for each record, or is it smart enough to update the index just once for all the changed records? In a B-Tree

Re: [sqlite] SQLite 3.8.8 expected in January

On 2015/01/10 15:50, Richard Hipp wrote: Yes, it was a compile-time omission. I have uploaded a new DLL that includes the loadable extension interface. Thank you - it works perfectly for all entries. All other tests worked well too, so no new problems to report from this side.

Re: [sqlite] SQLite 3.8.8 expected in January

The pre-compiled and supplied DLL (sqlite3.dll) seem to be missing an entry point for "sqlite3_enable_load_extension" - I do not see any mention in the update text about altering or removing this feature so I am assuming this might be a compile-time omission? On 2015/01/09 19:23, Richard Hipp

Re: [sqlite] Error while executing query: no such column: t1.*B.Switch-Tower-Sector

On 2015/01/09 01:12, MikeSnow wrote: I was wondering if anyone could let me know where I am going wrong. I am getting the error... "Error while executing query: no such column: t1.*B.Switch-Tower-Sector" but, the column, t1.[*B.Switch-Tower-Sector], does exist. I get results when I do

Re: [sqlite] Sqlite problem possible bug

On 2015/01/07 12:13, The Responsa Project wrote: To Whom it amy concern I am trying to use SQLITE and the like statement with wildcards and hebrew when I put in an english string it works correctly, such as Select * from dbname where colname like '%123%' I will get all the entries from

Re: [sqlite] Can I user sqlite on wp8 native project?

On 2015/01/06 11:24, Bite Forest wrote: I’m developing game with cocos2d-x. But compile sqlite.c in vs, there’r lots of error. Which version of sqlite can I compile through vs in c++ code? SQLite source is in C so it won't compile in C++, but you can just statically link the .obj for it (in

Re: [sqlite] VACUUM requires 6.7 times space ?

On 2015/01/05 13:32, Dan Kennedy wrote: On 01/05/2015 06:22 PM, Simon Slavin wrote: I have a database file which is 120GB in size. It consists of two huge tables and an index. //... Probably running out of space wherever temp files are created. I haven't done this, but I seem to remember

Re: [sqlite] COMMIT nested in SELECT returns unexpected results

On 2014/12/24 12:50, Jim Carroll wrote: I understand that performing a SELECT and nested COMMIT on the same table is not supported in sqlite, but I would have expected a COMMIT on a separate table would not be a problem. Some test code in python however reveals that performing the COMMIT

Re: [sqlite] Parentheses in column name

On 2014/12/22 22:19, Federico Jurio wrote: Hi guys, i'm trying to make a simple query using sqlite through gdal library ( http://www.gdal.org/ogr_sql.html ) I have a simple table with two columns: ID and (asd*) (both columns have integer values) I want the minimun value of this columns My

Re: [sqlite] Abnormal memory usage when removing rows within nested transaction from a table, referenced by another table via FK,

On 2014/12/18 12:03, Dan Kennedy wrote: On 12/18/2014 04:16 PM, Paul wrote: I understand. I guess, I'll have to stick to UPDATE <-> INSERT. Thank you for taking your time. Just out of curiosity, I want to ask one more question. How can FK constraint fail if I am removing (replacing) row from

Re: [sqlite] Feature Request - RowCount

On 2014/12/13 21:46, James K. Lowden wrote: So the number of tools with feature X is no measure of the value of X. (Notable example: the tool should keep every query and result in a time-sequenced transcript log, so that prior results can be re-examined and prior queries modified. Most tools

Re: [sqlite] Feature Request - RowCount

On 2014/12/13 14:38, Richard Hipp wrote: The "SELECT count(*) FROM table" query already has a special optimization in the b-tree layer to make it go faster. You can see this by comparing the times of these queries: SELECT count(*) FROM table; SELECT count(*) FROM table WHERE 1;

[sqlite] Feature Request - RowCount

To the SQLite devs: After recent discussion about the row-count issue w.r.t. Nulls in primary keys etc. I have been somewhat wrestling with how to improve this from a user perspective. To explain: Most DB Admin tools out there displays the number of rows in a table when you select it or open

Re: [sqlite] How to speed up database open

On 2014/12/11 17:58, Paul wrote: On 2014/12/11 13:51, Paul wrote: I have yet to try and test if dropping stat tables worth the effort. Some databases in fact can grow pretty big, up to few hundred of megabytes// In that case maybe keep the Stat1 tables and there is also the option of

Re: [sqlite] Counting rows

On 2014/12/11 17:19, Simon Slavin wrote: In my table which had about 300 million (sic.) rows I did this SELECT count(*) FROM myTable; to count the number of rows. After half an hour it was still processing and I had to kill it. I know that the internal structure of a table means that this

Re: [sqlite] How to speed up database open

On 2014/12/11 13:51, Paul wrote: In my specific case I need to open database as fast as possible. Usual working cycle: open -> select small data set -> close. It is irrelevant how much time it takes to open database when data is being added or updated, since it happens not too often. /Snipped

Re: [sqlite] replace many rows with one

On 2014/12/10 13:39, Simon Slavin wrote: Dear folks, A little SQL question for you. The database file concerned is purely for data manipulation at the moment. I can do anything I like to it, even at the schema level, without inconveniencing anyone. I have a TABLE with about 300 million

Re: [sqlite] seeking advice

On 2014/12/09 22:41, Rene Zaumseil wrote: Hi there, I have to store and retrieve up to 2000 parameters. The parameters can have real and integer values. The max. change rate is 100ms and the max. duration is up to some hours. The simple solution would be to use plain binary files. It's fast

Re: [sqlite] Artificially slow VACUUM by injecting a sleep() somewhere?

On 2014/12/09 03:36, David Barrett wrote: Hi all, great questions: *Re: Why VACUUM.* We vacuum weekly. This particular database is a "rolling journal" -- we are constantly adding new rows to the end of the table, and every week we truncate off the head of the journal to only keep 3M rows at

Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail

On 2014/12/08 15:58, Gwendal Roué wrote: I'm new to this mailing list, and I won't try to push my opinion, which is : yes this is a bug, and this bug could be fixed without introducing any regression (since fixing it would cause failing code to suddenly run, and this has never been a

Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail

On 2014/12/08 11:55, Gwendal Roué wrote: Hi, Unique indexes make some valid update queries fail. Please find below the SQL queries that lead to the unexpected error: -- The `books` and `pages` tables implement a book with several pages. -- Page ordering is implemented via the `position`

Re: [sqlite] "database is locked" for SQLITE_BUSY

On 2014/12/03 13:00, Jonathan Moules wrote: Hi, Just a quick request/suggestion. Currently SQLITE_BUSY events return an error of "Database is locked". Is it possible to change this to "Database is busy" or something similar? I ask because when someone then goes googling for "SQLite database

Re: [sqlite] [sqlite-dev] Implementing per-value custom types

On 2014/11/26 15:58, Darko Volaric wrote: I'm not looking for confirmation of ideas, on the contrary, people seem to want to push their own ideas about a database should be used and how I'm not using it correctly, when that is irrlevent to the issue I'm discussing. Maybe more focus on the

Re: [sqlite] [sqlite-dev] Implementing per-value custom types

Hi Darko, Firstly, kindly keep this to the sqlite-users forum and not on the dev forum (the devs read this too, the difference being simply that this one exists to help you, the other one is to discuss development stuff, not to help anyone). Secondly, you are confusing two things. You are

Re: [sqlite] Serializing an object's vector or array using sqlite3 in c++

On 2014/11/21 08:09, Thane Michael wrote: Many of the answers I came across online implied that it wouldn't be as straightforward as serializing ints and strings. It would be equally difficult in any RDBMS, the difficulty does not lie with the Database, it lies with your objects. There are

Re: [sqlite] Insert Hindi Language

On 2014/11/18 15:12, Richard Hipp wrote: CREATE TABLE example(x TEXT); INSERT INTO example(x) VALUES('हैलो, विश्व'); SELECT * FROM example; The point being that Hindi isn't special, the presented characters are Unicode just like all the others - as long as your DB text encoding is set to

Re: [sqlite] Discrepancy with indexing and WHERE clause with AND/OR

On 2014/11/17 14:48, RP McMurphy wrote: Upon further analysis it appears that the data "shape" is different in different periods within the table. That is, some sections have the inverse shape to other sections. So it looked like query times would change over time but actually they are changing

Re: [sqlite] Discrepancy with indexing and WHERE clause with AND/OR

On 2014/11/17 14:48, RP McMurphy wrote: PS: Some administravia; Does anyone know of a way to reduce the posting delay for this list? Is it always like this? Or is it some problem with the gmane site in general? Join the mailing list directly (you can unsubscribe once you got what you were

Re: [sqlite] Verifying a file is valid SQLite3

Nvm - found it - thanks. It seems the SQlite search engine is better than the Google one - Page ref (in case anyone else is interested): https://www.sqlite.org/fileformat2.html#database_header Specifically point 1.2.1 On 2014/11/16 15:03, RSmith wrote: Could someone kindly tell me the byte

[sqlite] Verifying a file is valid SQLite3

Could someone kindly tell me the byte-pattern, offset and length into an SQLite3 file that might suffice to verify that it is indeed an SQLite3 file. I am not very concerned with false positives (as nobody can control all the files in the World), but rather interested in absolute negatives,

Re: [sqlite] Discrepancy with indexing and WHERE clause with AND/OR

On 2014/11/14 16:32, RP McMurphy wrote: On Tue, 11/11/14, RP McMurphy wrote: > If you can provide any examples where ANALYZE makes a query slower, I suspect the developer team would like > to see them. > After we

Re: [sqlite] [SQLite] Support for 23,10 Precision number format

On 2014/11/13 19:06, Simon Slavin wrote: On Thu, Nov 13, 2014 at 3:38 PM, Simon Slavin wrote: 100,000,000,000,000,000,000 But he's using the field to store an amount of money in. So why ask for anything with ten places after the decimal point ? No genuine currency

Re: [sqlite] [SQLite] Support for 23,10 Precision number format

On 2014/11/13 15:01, Dinesh Navsupe wrote: Hi, My need is 23 decimal digits of precision. We work on complex payout calculation engine where in formula outputs are quite large numbers and clients do not want to round off. I do not think that re-stating your need suffices as a good enough

Re: [sqlite] Locking errors on network

On 2014/11/10 20:22, Mike McWhinney wrote: So SQLite shouldn't be used at all on a network? Aren't there any other provisions to handled the locking errors if/when they occur? It is not about SQLite, it is about the Networking systems lying about whether a file is locked or not. No RDBMS

Re: [sqlite] What is the best alternative to this RIGHT OUTER JOIN ?

On 2014/11/09 14:11, Tristan Van Berkom wrote: A good SQL rule of thumb: if you can think of a way, so can the DBMS. "... no opportunity to make a good guess" is not true. In some sense, SQLite has had 10 years to make a good guess, and often does. A nested select need not be materialized as a

Re: [sqlite] bug: "no such column" with UNION + subquery + GROUP BY + ORDER BY + COLLATE

On 2014/11/08 14:21, Clemens Ladisch wrote: Hi, the following query fails to parse, although it should be valid: sqlite> select 1 union select 1 from (select 1 as x) group by x order by 1 collate binary; Error: no such column: x Will call the above Version A. I don't see how that

Re: [sqlite] LEFT joins affect query plan for semantically equal queries

On 2014/11/05 15:26, David Woodhouse wrote: On Wed, 2014-11-05 at 15:13 +0200, RSmith wrote: I don't think it's anything to do with the table data being special, is it? Isn't it generically true that for any LEFT JOIN of a,b WHERE b.anything IS NOT NULL, the results are going to be equal

Re: [sqlite] LEFT joins affect query plan for semantically equal queries

On 2014/11/05 14:13, vita...@yourcmc.ru wrote: Hi! After playing a little with SQLite as a DBMS for Bugzilla, I've discovered that LEFT/INNER join affects query plan in a bad way even for semantically equal queries: SELECT * FROM bugs b INNER JOIN profiles p ON p.userid=b.assigned_to WHERE

Re: [sqlite] x64 vs x32 DLL

On 2014/11/04 21:34, jose isaias cabrera wrote: Greetings! I have an application that is written for x32 machines. However, we now have a few machines that are x64 and all is well when we are using the precompiled x32 DLLs provided by http://www.sqlite.org/download.html. Changing the x32

Re: [sqlite] Search query alternatives.

On 2014/10/16 00:05, Michael Falconer wrote: Hi all, first off I must start with an apology. I know I'm sort of doing the wrong//... No need to apologise, this flies quite close to the central theme. Whether you are using SQLite or any other SQL RDBMS, this is horrible DB design and it is

Re: [sqlite] Make a database read-only?

On 2014/10/14 13:48, Ross Altman wrote: Hi Martin, Thank you, I'll definitely look into that. It's unfortunate that there isn't a simpler way to do this... oh well. Let me bud in here since I encounter this question a lot in other matters. There typically are three reasons one would like to

Re: [sqlite] line break, or similar, in query results

On 2014/10/14 13:09, Clemens Ladisch wrote: Paul Sanderson wrote: SELECT CASE WHEN previd = 0 THEN '--' || char(10) END, * FROM (SELECT ...); This solution from Clemens will work perfectly, and depending on the kind of OS you use and output method it might even work to add

Re: [sqlite] [SQL Query] Top 10 of 5 different groups sorted by length

ReportJobLengths R WHERE GroupName like 'GRP12%' LIMIT 10 UNION ALL SELECT * FROM ReportJobLengths R WHERE GroupName like 'GRP15%' LIMIT 10 UNION ALL SELECT * FROM ReportJobLengths R WHERE GroupName like 'GRP20%' LIMIT 10; etc. On 2014/10/14 09:22, RSmith wrote: On 2014/10/13 23:21, pihu...@free.fr

Re: [sqlite] [SQL Query] Top 10 of 5 different groups sorted by length

On 2014/10/13 23:21, pihu...@free.fr wrote: Hello! I'm trying to find a way to reduce the length of the following query using SQLite: select * from (select GroupName, JobName, Start, End, Status, (strftime('%s', End) - strftime('%s', Start)) as Length from ReportJobs where PlanDate =

Re: [sqlite] decoding a bitmask

On 2014/10/13 15:39, Paul Sanderson wrote: Thanks all Clemens - I went initially for your solution as it fitsbetter with some other work i have done My actual code is as folows (CASE visits.transition & 0xFF00 WHEN 0x0080 THEN 'Blocked' ELSE '' END || CASE

Re: [sqlite] decoding a bitmask

On 2014/10/13 16:51, Mark Lawrence wrote: On Mon Oct 13, 2014 at 02:39:40PM +0100, Paul Sanderson wrote: The query is on a visits table from a google chrome history database. The query seems to work OK if a single bit is set, but fails (a blank string is returned) when multiple bits are set.

Re: [sqlite] decoding a bitmask

On 2014/10/13 13:52, Paul Sanderson wrote: I have a table with an integer value which is a bitmask. one or more of the bits can be set and each bit has a corresponding meaning. so using the windows file attribute as an example we have 0c01 readonly 0x02 hidden 0x04 system 0x10 directory 0x20

Re: [sqlite] Stored Procedures

On 2014/10/09 19:04, Omprakash Kolluri wrote: Hi, I am new to SQLite. I am working on an app that I am developing and plan to use SQLite as an embedded database. My Question - Does SQLite support stored procedures similar to those in MS SQL Server etc. Any suggestions OR pointers to

Re: [sqlite] Single large table vs. several smaller tables

There is no point to grouping similar fields in different tables, though I would advise grouping them together in the main table as a simple case for clarity, but it has no other benefit. As long as the data is in 1NF, one table is fine. The main reason (and I think only motivation) to have data

Re: [sqlite] Is there equivalent to MySQL IF() function?

On 2014/10/07 13:20, Tony Papadimitriou wrote: Well, it is exactly because I understand the difference between a boolean expression and a non-boolean expression, along with a bit misleading documentation, that I got confused. It is usually those who are used to only the C-like treatment of a

Re: [sqlite] Is there equivalent to MySQL IF() function?

On 2014/10/07 12:13, Tony Papadimitriou wrote: Thanks. It seems quite a bit more verbose than the IF() function, but it works, so I can't complain. As an aside - It's not only a little more verbose, it also happens to be the way prescribed by the SQL standard and to my knowledge MySQL,

Re: [sqlite] Is there equivalent to MySQL IF() function?

On 2014/10/07 12:42, Tony Papadimitriou wrote: You're right, ... but in that page it says: The only difference between the following two CASE expressions is that the x expression is evaluated exactly once in the first example but might be evaluated multiple times in the second: CASE x

Re: [sqlite] Is there equivalent to MySQL IF() function?

SELECT CASE WHEN (AGE<3) THEN 'Baby' WHEN (AGE BETWEEN 4 AND 18) THEN 'Child' ELSE 'Adult' END On 2014/10/07 11:15, Tony Papadimitriou wrote: Hi all, Is there any an equivalent function to the MySQL IF(condition,true_expr,false_expr) function? For example, SELECT AGE,IF(AGE <

Re: [sqlite] Is there a simple command line data editor for sqlite3?

Did you have a specific OS in mind? Linux, MacOS and Windows all have a myriad of SQLite editors. A simple google would no doubt reveal a lot, but if you say which OS, I'm sure on here we can point out some good ones. On 2014/09/30 19:23, c...@isbd.net wrote: Is there such a thing? Of

Re: [sqlite] Will collate binary supports like query

On 2014/09/30 09:03, dd wrote: I just got below pragma: pragma case_sensitive_like = true As an aside... The beauty of it being a Pragma is that you are not stuck with a single method - you can switch it on and off at a whim... Maybe even provide a GUI interface to switch modes.

Re: [sqlite] Division accuracy

On 2014/09/25 19:32, Clemens Ladisch wrote: RSmith wrote: the time of finishing does not determine position alone, there are bonuses and penalties which doesn't stack up to integer values, but is measurable [...] Without going into too much detail about how bonuses and penalties

[sqlite] Fwd: Re: sqlite generate strange field name in union...

On 2014/09/25 05:04, 麦田观望者 wrote: Hi, RSmith‍: I can't find a method to reply you message,so i send it to you mailbox directly, sorry for disturber. you say: >It is just whatever the Query producer feels comfortable writing in the >header to identify the column‍ maybe you are right on the

Re: [sqlite] Division accuracy

On 2014/09/25 15:43, James K. Lowden wrote: On Thu, 25 Sep 2014 10:36:31 +0200 Clemens Ladisch wrote: Yes, and yes, absolutely. In that case the order is established by the user, and can be captured by the application as integers, and stored in the database. The problem

Re: [sqlite] sqlite generate strange field name in union

On 2014/09/24 18:48, 麦田观望者 wrote: if we have to tables: create table t1(f1 integer,f2 integer); create table t2(f1 integer,f2 integer);‍ the fellowing sql generate a result set with a strange field name: select t1.f1 from t1 union select t2.f1 from t2 ‍ we expect a column named "f1" but we

Re: [sqlite] Division accuracy

Thanks Alessandro, this will work, it's just... ugly... and for other reasons I'd prefer the sort order to be numeric. If however it doesn't work out, this might just be the best solution, ugly or not. :) Thanks! Ryan On 2014/09/24 21:33, Alessandro Marzocchi wrote: Which language are you

Re: [sqlite] Division accuracy

On 2014/09/24 22:24, Clemens Ladisch wrote: RSmith wrote: Clemens I'm liking the link list but did not go with it due to an expensive insert function Yes, you have to update two references (with prev/next), but how is that worse than the update of all SortOrder values? Well the insert runs

Re: [sqlite] Division accuracy

where "Next"="ID" kind of thing and use that as the ordering - not hard but will have to see how expensive it is. Either way, thanks for the help! On 2014/09/24 20:15, Clemens Ladisch wrote: RSmith wrote: I have one program that inserts values to a table and determin

[sqlite] Division accuracy

I'm trying to find what the limit is for dividing in terms of accuracy. Basically I have one program that inserts values to a table and determine sort order using one standard trick that has a REAL column named "SortOrder" which gets the value Highest_previous_value+1 if an insert happens with

Re: [sqlite] Non-optimal query plan

On 2014/09/24 16:52, Rob Golsteijn wrote: Hi List, I was looking at the query plan of a rather simple query, but I don't understand why sqlite would choose this query plan. ...I was surprised that sqlite came up with the inferior query plan... Note: After an "analyze aaa" (on a decently

Re: [sqlite] Results of Joins in sqlite

On 2014/09/24 15:06, Prakash Premkumar wrote: Thanks a lot , Simon and Hick, What I am looking for is , instead of iterating through the result which sqlite provides and then form the respective objects and setting pointers, is it possible to hack sqlite to fill in the objects this way. I

Re: [sqlite] Atomic database structure initialization

On 2014/09/22 15:03, Paul wrote: I suspect that no, not all accesses to the database file are done using transactions. What about read-only databases? Moreover, what about read-only medium? A transaction does not necessarily imply a write, only if there is an update of actual data, which

Re: [sqlite] Performance regression between 3.7.17 and 3.8.0.2?

On 2014/09/21 15:39, Merike wrote: Now I could very well be wrong about that as you say in your other reply that "It might simply be that Analyze did not get your QP to react on that size DB as it did for us". You seem to be saying that analyze behaves differently depending on database size...

Re: [sqlite] An order by problem, maybe a bug?

On 2014/09/21 17:18, James K. Lowden wrote: ...to get web payment forms to allow, for the love of God, spaces in credit card numbers. --jkl Now there's a worthy cause. Ditto for phone numbers (though they mostly are more lenient today). Also to allow hashes and dashes in the address field.

Re: [sqlite] An order by problem, maybe a bug?

On 2014/09/20 23:23, Simon Slavin wrote: ...calls themself Tarquin Fin-tim-lin-bin-whin-bim-lim-bus-stop-F'tang-F'tang-Olé-Biscuitbarrel Oh you know him? We go way back... old Tim Biscuits we used to call him. It was fun watching the undertakers figure out how to get all that on his

Re: [sqlite] Performance regression between 3.7.17 and 3.8.0.2?

On 2014/09/21 14:12, RSmith wrote: On 2014/09/20 23:08, Richard Hipp wrote: On Sat, Sep 20, 2014 at 12:45 PM, Merike <gas...@smail.ee> wrote: A question: is the query being fast again after analyze call indicative of the bug being fixed? Because I tried it on my original databa

Re: [sqlite] Performance regression between 3.7.17 and 3.8.0.2?

On 2014/09/20 23:08, Richard Hipp wrote: On Sat, Sep 20, 2014 at 12:45 PM, Merike wrote: A question: is the query being fast again after analyze call indicative of the bug being fixed? Because I tried it on my original database too and there I don't see a speedup after

Re: [sqlite] I'm trying to figure out how to ...

On 2014/09/18 05:38, Mark Halegua wrote: I'm racking my brain trying to figure out how to get directly to the last item in a (potentially) sorted or ordered table. At least oe of the tables will be ordered by a name and a date, so uising the rtowid won't work. Also, how to traverse a table

Re: [sqlite] FW: I'm trying to figure out how to ...

On 2014/09/18 08:18, Keith Medcalf wrote: ...long article... Thanks Keith for taking the time, I imagine this is a topic you are passionate about and I think it should be published somewhere as an article because it will save lives of beginner DBA/Programmers for millenia to come - I have

Re: [sqlite] Create join and add unique column

On 2014/09/16 20:00, James K. Lowden wrote: Or not use a temporary table. http://www.schemamania.org/sql/#rank.rows Now easier with CTE. In theory it could be *faster* than a temporary table, because the insertion I/O is avoided. But only testing will tell. --jkl Hi James, I'm

Re: [sqlite] Create join and add unique column

On 2014/09/16 15:32, Paul Sanderson wrote: select _rowid_, * from tab3 does the trick - thanks all Indeed, and if you are pedantic or do not work in a table with rowids, the solution is to explicitly give the table definition then fill it, some variation on this: CREATE TEMPORARY TABLE

Re: [sqlite] Divide by 0 not giving error

On 2014/09/15 22:13, Simon Slavin wrote: On 15 Sep 2014, at 8:33pm, Dave Wellman wrote: Simon, I'm really surprised at that. Effectively what this means is that the answer that Sqlite returns may or may not be the correct result. What ? No. It's correct. The

Re: [sqlite] Divide by 0 not giving error

On 2014/09/15 20:50, Dave Wellman wrote: Hi all, I've found that an sql request that I expected to fail, but it didn't. On the face of it that is good news but there is a potential downside. I wonder if my expectation is wrong or if this is a bug which so far hasn't been caught. The

Re: [sqlite] Can't figure out how to report a bug

This is the right way to report a bug, and as soon as you encounter a bug, you should report it here. As for the current query, this is not a bug, it's a VS13 compiler peculiarity which they feel pertinent to report on, but which does not affect the ability of SQLite to produce the correct

Re: [sqlite] Problems uploading CSV into sqlite3 DB

Humour us and paste the first 4 lines or so of your CSV text here On 2014/09/05 04:17, Carlos A. Gorricho (HGSAS) wrote: So, any ideas on how to solve this issue will be more than welcome. I have tried several shortcuts...none works. Latest was to install an Ubuntu 14.04 Virtual Machine

Re: [sqlite] Not SQLite related, but, SQLite Mailing List related

On 2014/08/11 19:19, Stephen Chrzanowski wrote: Ok, this IS NOT about SQLite itself in ANY regard, but specifically about this particular mailing list and how GMail is handling itself. When I joined this mailing list years ago, I put anything that goes through here into its own label via the

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

On 2014/08/04 18:27, Mark Lawrence wrote: On Mon Aug 04, 2014 at 06:04:53PM +0200, RSmith wrote: Yes it does, thanks. I guess I still find the combination of COUNT without a GROUP BY to be unintuitive, but at least I know why now. As far as I know, there is no requirement for a group

  1   2   3   4   >