Re: [sqlite] Query Help
Hello, Thanks for the help. On Sun, Mar 10, 2013 at 10:36 PM, Igor Tandetnik wrote: > On 3/10/2013 11:06 AM, Navaneeth.K.N wrote: >> >> select distinct(lower(pattern)) as pattern, id from symbols where >> value1 = ?1 or value2 = ?1 group by pattern >> >> This returns >> >> "cchu", "20907" >> "chchu", "20879" >> "chu", "20935" >> >> This is distinct set of patterns, but I am not getting the list >> ordered by id. Even if I add a "order by id" to the above query, it >> sorts only the above set. But what I need is to get in the following >> order. >> >> >> "chu", "20851" >> "chchu", "20879" >> "cchu", "20907" > > > Why do you expect 'chu' to be accompanied by an ID of 20851, and not 20935? > These seem to be equally valid choices? More than the id, I care about order. When I use my first query, "chu" comes at the end. But since it has a lower id, it should be first. This order defined how my application behaves. Doing min(id) did the trick. Thanks for the help. Thanks to James also for the additional information. > > If you want, say, the smallest of the two, just say so: > > select lower(pattern) as pattern, min(id) as minid > > from symbols where value1 = ?1 or value2 = ?1 > group by pattern order by minid; > > -- > Igor Tandetnik > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Thanks Navaneeth ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] cross compiling sqlite
HI, Thank you for the reply. i have cross-compiled tcl and sqlite for ARM , but i am facing some "result=disk I/O error" issue which i have posted as a new thread . Thank you Brijesh -- View this message in context: http://sqlite.1065341.n5.nabble.com/cross-compiling-sqlite-tp67478p67615.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] TCL Test failures on ARM
yes there were 142 in each of those tests, that is, transient.1... to transient.142... in each of the sets i mentioned above -- View this message in context: http://sqlite.1065341.n5.nabble.com/TCL-Test-failures-on-ARM-tp67612p67614.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] TCL Test failures on ARM
On 03/11/2013 10:52 AM, bkk wrote:
Hi,
When i executed the TCL: tests on my ARM board (nitrogen 6X) the below tests
are
failing with the message
Expected: [0 ok]
Got: [1 {nfail=1 rc=1 result=disk I/O error}]
sysfault-2.1-vfsfault-transient
memsubsys1.sysfault-2.1-vfsfault-transient
memsubsys2.sysfault-2.1-vfsfault-transient
no_mutex_try.sysfault-2.1-vfsfault-transient
journaltest.sysfault-2.1-vfsfault-transient
inmemory_journal.sysfault-2.1-vfsfault-transient
What could be the reason for these failures? and how can i proceed further
with this ?
The tests inject errors into various system calls (open, ftruncate,
close, read - you can see the list in the test file). The errors are
injected such that they set errno to EINTR. In this case SQLite is
supposed to retry the system call. But in your case it looks like it
is returning an error to the user instead.
Are the test case names above truncated? Do you mean that all tests
that match the pattern "sysfault-2.1-vfsfault-transient*" etc. failed?
Dan.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] TCL Test failures on ARM
Hi,
When i executed the TCL: tests on my ARM board (nitrogen 6X) the below tests
are
failing with the message
Expected: [0 ok]
Got: [1 {nfail=1 rc=1 result=disk I/O error}]
sysfault-2.1-vfsfault-transient
memsubsys1.sysfault-2.1-vfsfault-transient
memsubsys2.sysfault-2.1-vfsfault-transient
no_mutex_try.sysfault-2.1-vfsfault-transient
journaltest.sysfault-2.1-vfsfault-transient
inmemory_journal.sysfault-2.1-vfsfault-transient
What could be the reason for these failures? and how can i proceed further
with this ?
Thank You
Brijesh
--
View this message in context:
http://sqlite.1065341.n5.nabble.com/TCL-Test-failures-on-ARM-tp67612.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug on real operations
On Fri, 8 Mar 2013 15:06:31 -0300 Israel Lins Albuquerque wrote: > The problem is not comparisons the problem is when I do something > like this: CREATE TABLE tb (a REAL); > INSERT INTO tb (a) VALUES(0); > UPDATE tb SET a = a + 5.45; > UPDATE tb SET a = a + 16.9; > > SELECT a FROM tb; > > Gives visually right answer: 22.35 > But putting on a double variable gives me 22.3499948593433 (something > like that) and when declaring double a = 22.35 => gdb give me > 22.34999 You wan to read up on the IEEE 754 floating point standard. I could recommend http://randomascii.wordpress.com/2013/02/07/float-precision-revisited-nine-digit-float-portability/. As others told you, 22.35 does not have an exact base 2 representation. It might help to know what numbers *can* be represented in near that number. I wrote a little program to illustrate. The library on my computer converts "22.35" to an 8-byte double whose bit pattern is 0x4036599a. Breaking it into its components, that turns out to mean 2^4 * (1.0 + 2^-52 * 0x6599a) The last 52 bits of those 64 are the fractional part, the mantissa. We know the last 4 of those 52 are 0x0a, or 10 decimal, or 0110 binary. What number do we get if we add/subtract one from that, making the final digits 0x09 or 0x0b? $ for N in 40365999 4036599a 4036599b; do printf "$N:\t"; ./genfp -i $N; done 40365999: 22.348 4036599a: 22.351 4036599b: 22.355 > CREATE TABLE tb (a REAL); > INSERT INTO tb (a) VALUES(0); > UPDATE tb SET a = a + 5.45; > UPDATE tb SET a = a + 16.9; ... > But putting on a double variable gives me 22.3499948593433 (something > like that) Be careful with that: your column is REAL, which is single-precision, only 4 bytes, of which only 23 are the mantissa, giving a decimal precision of only about 7 digits. When you add two REALs, you can't depend on more than 6 digits because of rounding error. In your example, 5.45 and 16.9 both have to be rounded in base 2, which explains why their sum is wrong even when rounded to 7 digits (22.34999). HTH. --jkl ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] hide sqlite database
On 10 Mar 2013, at 7:53pm, Clemens Ladisch wrote: > dd wrote: >> I have backup of existing database. I just want to hide this backup >> database. > > If the backup file is hidden, you won't be able to restore from it. > I doubt that that is your goal. > > What is your actual purpose for hiding this file? Possibly more clearly: what are you hiding it from ? People casually looking around in your file GUI ? All other applications under all other circumstances ? All applications including your restore one unless you intentionally make the file visible first ? Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] hide sqlite database
dd wrote: > I have backup of existing database. I just want to hide this backup > database. If the backup file is hidden, you won't be able to restore from it. I doubt that that is your goal. What is your actual purpose for hiding this file? Regards, Clemens ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Query Help
On Sun, 10 Mar 2013 20:36:47 +0530 "Navaneeth.K.N" wrote: > select distinct(lower(pattern)) as pattern, id from symbols where > value1 = ?1 or value2 = ?1 group by pattern Igor's answer is correct. I just want to point out what looks like a misunderstanding on your part regarding "distinct" because it may help you to understand Igor's answer better. You wrote select distinct(lower(pattern)) as though "distinct" were a function. It's not. The "distinct" keyword modifies the query such that distinct *rows* are returned. The whole row, that is, not a particular column. > "chu", "20851" > "chchu", "20879" > "cchu", "20907" > "chu", "20935" These are already distinct rows. The two you're thinking about "chu", "20851" and "chu", "20935" are distinct because they differ in their "id" column. That leaves you with a small problem: you want a distinct set of patterns, but for each pattern you need an id, and some patterns have more than one id. Whenever you want "one of Y" or "something about Y" for every X, you need a GROUP BY clause. Your X is "pattern" and your Y is "id". But which id? Igor suggests you take the minimum one, select lower(pattern) as pattern, min(id) as minid ... group by pattern which is certainly a fine choice if you don't care. HTH. --jkl ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Query Help
On 3/10/2013 11:06 AM, Navaneeth.K.N wrote: select distinct(lower(pattern)) as pattern, id from symbols where value1 = ?1 or value2 = ?1 group by pattern This returns "cchu", "20907" "chchu", "20879" "chu", "20935" This is distinct set of patterns, but I am not getting the list ordered by id. Even if I add a "order by id" to the above query, it sorts only the above set. But what I need is to get in the following order. "chu", "20851" "chchu", "20879" "cchu", "20907" Why do you expect 'chu' to be accompanied by an ID of 20851, and not 20935? These seem to be equally valid choices? If you want, say, the smallest of the two, just say so: select lower(pattern) as pattern, min(id) as minid from symbols where value1 = ?1 or value2 = ?1 group by pattern order by minid; -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] hide sqlite database
On 10 Mar 2013, at 7:52am, dd wrote: > I have backup of existing database. I just want to hide this backup > database. Hiding a file is a function of your operating system, not SQLite. Get some experience with hiding a simple text file. Once you have that working, just do the same thing with hiding the SQLite database file. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Query Help
Hi Guys, I have a table named "symbols". I am writing the below query. select lower(pattern), id from symbols where value1 = ?1 or value2 = ?1 This returned the following results. "chu", "20851" "chchu", "20879" "cchu", "20907" "chu", "20935" >From this, I need only distinct patterns. So I tried this query. select distinct(lower(pattern)) as pattern, id from symbols where value1 = ?1 or value2 = ?1 group by pattern This returns "cchu", "20907" "chchu", "20879" "chu", "20935" This is distinct set of patterns, but I am not getting the list ordered by id. Even if I add a "order by id" to the above query, it sorts only the above set. But what I need is to get in the following order. "chu", "20851" "chchu", "20879" "cchu", "20907" This is ordered by id and only distinct patterns. I am not able to come up with a query which does the above. Any help would be great. -- Thanks Navaneeth ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
