Re: [sqlite] A possible double bug?

I can’t reproduce the problem, http://sqlite.org/datatype3.html#type_affinity “When text data is inserted into a NUMERIC column, the storage class of the text is converted to INTEGER or REAL (in order of preference) if such conversion is lossless and reversible” So after “create table test

Re: [sqlite] pragma integrity_check doesn't check constraints without indices

On 17 Oct 2016, at 8:17am, Torsten Landschoff wrote: > So much about my attempt to report a bug. If you don't want to believe my > report, then don't. Sorry, just to make it clear, I'm just a fellow-user of SQLite. I'm not on the development team. And I totally

Re: [sqlite] Any performance penalty for SELECTing more columns? (C API)

Try using the sqlite shell program and the explain feature. You will see that each column fetched requires an additional opcode to do the fetching and an additional register to hold the result, all of this on top of requiring SQLite to decode all of these fields without you ever intending to

Re: [sqlite] Possible Bug in VTable handling in SQLite 3.14.2

Dan, no, they don't, which is why I included "possible". It is probably not seen elsewhere. We are using SQLite's virtual table feature to implement a query front end for several diverse data storage methods in an OLTP application, with the two writeable stores being memory sections and CTree

[sqlite] pragma integrity_check doesn't check constraints without indices

On 14 Oct 2016, Simon Slavin wrote: > The problem is that SQLite doesn't expect you to make manual changes to > sqlite_master. It doesn't reread the schema to execute every command. That is factually correct as evidenced by this example: === snip

Re: [sqlite] A possible double bug?

On 10/17/2016 07:12 AM, Quan Yong Zhai wrote: I can’t reproduce the problem, As it has already been pointed out, this is normal for floating point arithmetic. In your machine, implementation, and SQLite installation the two value representations may be identical, while in others it may not

Re: [sqlite] pragma integrity_check doesn't check constraints without indices

On 2016/10/17 10:40 PM, Ben Newberg wrote: First off, my apologies for hijacking this thread. But I've seen some strange things when messing with pragma writable_schema. It appears all bets are off? example: create a table of columns (x, y, z), and fill it with values. then, modify

Re: [sqlite] pragma integrity_check doesn't check constraints without indices

To be clear, my question about all bets being off sounded more rhetorical in my head than it came out. I'm perfectly content with the outcome, just wanted to share an example of something I accidently learned the hard way when messing with schemas. And thanks to you all I know more about why it

[sqlite] SQLite behaviour when modifying sqlite_master (was: Re: pragma integrity_check doesn't check constraints without indices)

Hi Ben, On 10/17/2016 10:40 PM, Ben Newberg wrote: > First off, my apologies for hijacking this thread. :-( I just wanted to report a bug... > But I've seen some strange things when messing with pragma writable_schema. > It appears all bets are off? > > example: create a table of columns (x,

Re: [sqlite] pragma integrity_check doesn't check constraints without indices

Reads about right. I'm writing the below step-by-step thing as much to test my own knowledge of what's going on as to help, so knowledgeable folks please correct me where I mess up. The short version is: don't mess with writable schema unless you have no other choice. (Which you probably do)

Re: [sqlite] pragma integrity_check doesn't check constraints without indices

On Mon, 17 Oct 2016 15:40:44 -0500, Ben Newberg wrote: > But I've seen some strange things when messing with pragma writable_schema. > It appears all bets are off? Yes. that's why there's a warning: "Warning: misuse of this pragma can easily result in a corrupt database

Re: [sqlite] A possible double bug?

I’d say the mistake here is converting a double to a string before inserting it into the database. Any time you convert between floating point and decimal (or vice versa) you can lose accuracy, and are not guaranteed round-trip fidelity. (0.1, 0.01, 0.001, etc. do not have finite-length exact

Re: [sqlite] A possible double bug?

"The database file format is cross-platform - you can freely copy a database between 32-bit and 64-bit systems or between big-endian and little-endian architectures. Quote: "In your machine, implementation, and SQLite installation the two value representations may be identical, while in others

Re: [sqlite] A possible double bug?

On 10/17/2016 08:29 AM, Quan Yong Zhai wrote: "The database file format is cross-platform - you can freely copy a database between 32-bit and 64-bit systems or between big-endian and little-endian architectures. Quote: "In your machine, implementation, and SQLite installation the two value

Re: [sqlite] Pivot & concat SELECT?

On 2016/10/17 3:54 PM, Daniel Polski wrote: Let's say I have a table like this: CREATE TABLE table1( idINT, unitINT, bit_positionINT, valBOOL ); INSERT INTO table1 VALUES(1,1, 0, 1); INSERT INTO table1 VALUES(2,1, 1, 1);

Re: [sqlite] Pivot & concat SELECT?

select unit, sum(1 << bit_position) from table1 where val group by unit; To make it more complex.. Is it possible to select into "different bytes" depending on bit_position? (For example that bit_position 0-7 represent byte 1, bit_position 8-15 represent another) To get a second "byte" I

Re: [sqlite] Pivot & concat SELECT?

On 17 Oct 2016, at 3:47pm, Daniel Polski wrote: > To make it more complex.. Is it possible to select into "different bytes" > depending on bit_position? (For example that bit_position 0-7 represent byte > 1, bit_position 8-15 represent another) You can use the 'CASE'

Re: [sqlite] Pivot & concat SELECT?

On 2016/10/17 4:47 PM, Daniel Polski wrote: select unit, sum(1 << bit_position) from table1 where val group by unit; To make it more complex.. Is it possible to select into "different bytes" depending on bit_position? (For example that bit_position 0-7 represent byte 1, bit_position

Re: [sqlite] Inserting a new record (anywhere) into a table of ordered records that have an integer auto-increment primary key

My thinking on this is use a trigger or two. So if you have fields Date and Tiebreaker (as mentioned in the other replies) ... Date text, Tiebreaker int, ... Have it so if you insert it with a given Tiebreaker value it increments things after that, or if you give it null it puts it at the end

Re: [sqlite] Pivot & concat SELECT?

To add to the inefficiency :^)... select unit, (sum(1 << bit_position) & 0xff) AS byte1, ((sum(1 << bit_position) >> 8) & 0xff) AS byte2 from table1 where val group by unit; -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of

Re: [sqlite] Pivot & concat SELECT?

Den 2016-10-17 kl. 16:03, skrev Igor Tandetnik: select unit, sum(1 << bit_position) from table1 where val group by unit; Wow!! Thanks alot! /Daniel ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org

Re: [sqlite] Pivot & concat SELECT?

Den 2016-10-17 kl. 16:40, skrev R Smith: Luckily all data needed is in the table, and Igor's method will work just dandy in your case. Why not store the flags in full rather than per line though? You can easily manipulate the values by using standard bit-masking and boolean bit-wise

Re: [sqlite] Pivot & concat SELECT?

Correction - Needs another bracket to read: select unit, (bit_position / 8) AS byteNo, (sum(1 << (bit_position % 8))) AS byteVal from table1 where val group by unit, (bit_position / 8); On 2016/10/17 5:04 PM, R Smith wrote: select unit, (bit_position / 8) AS byteNo, (sum(1 <<

Re: [sqlite] Pivot & concat SELECT?

On 10/17/2016 9:54 AM, Daniel Polski wrote: Let's say I have a table like this: CREATE TABLE table1( idINT, unitINT, bit_positionINT, valBOOL ); INSERT INTO table1 VALUES(1,1, 0, 1); INSERT INTO table1 VALUES(2,1, 1, 1);

Re: [sqlite] Pivot & concat SELECT?

On 17 Oct 2016, at 3:03pm, Igor Tandetnik wrote: > select unit, sum(1 << bit_position) from table1 where val group by unit; Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org

Re: [sqlite] Pivot & concat SELECT?

On 17 Oct 2016, at 3:03pm, Igor Tandetnik wrote: > select unit, sum(1 << bit_position) from table1 where val group by unit; Beat me to it. I was going to say select unit, sum(val << bit_position) as byte from table1 group by unit; But yours is slightly more efficient not

[sqlite] Pivot & concat SELECT?

Let's say I have a table like this: CREATE TABLE table1( idINT, unitINT, bit_positionINT, valBOOL ); INSERT INTO table1 VALUES(1,1, 0, 1); INSERT INTO table1 VALUES(2,1, 1, 1); INSERT INTO table1 VALUES(3,1, 4, 1); INSERT INTO

Re: [sqlite] A possible double bug?

Discussions on floating point aside, I'm likewise getting results that are equal when trying it. So I'm curious as to the original poster's SQLite version, platform, language they're coding in, etc. When you run "select foo, typeof(foo) from test;" are you getting two results of (62.027393,

Re: [sqlite] A possible double bug?

> On Oct 17, 2016, at 2:12 AM, Quan Yong Zhai wrote: > > Or after prepare “ INSERT INTO test VALUES(?) “ > Bind_text “62.027393” > Bind_double 62.027393 > > In all the four situation, the value insert into foo field is binary > identical, it’s a 8-bytes REAL value.

Re: [sqlite] pragma integrity_check doesn't check constraints without indices

First off, my apologies for hijacking this thread. But I've seen some strange things when messing with pragma writable_schema. It appears all bets are off? example: create a table of columns (x, y, z), and fill it with values. then, modify sqlite_master to take out column z. let's say later on

Re: [sqlite] Pivot & concat SELECT?

On 10/17/2016 10:32 AM, Daniel Polski wrote: Den 2016-10-17 kl. 16:03, skrev Igor Tandetnik: select unit, sum(1 << bit_position) from table1 where val group by unit; Wow!! Thanks alot! Alot accepts expressions of gratitude but wonders why they are being addressed to it: