[sqlite] Bug in SQLite 3.8.2
Hi, The bug affects 3.8.2 and 3.8.3.1, I haven't tested other versions. *Preconditions:* - 2 databases: A and B. - database A has table "test": CREATE TABLE test (id integer PRIMARY KEY, val text) WITHOUT ROWID - database B has table "test2": CREATE TABLE test2 (EID INTEGER, node1 INTEGER, node2 INTEGER) *To reproduce bug:* - open database A and attach database B: ATTACH 'database_b.db' AS 'attached'; - execute query: select test.*, t2.ROWID from attached.test2 t2, test SQLite says: *no such column: t2.ROWID* Weird thing is that when you switch test2 and test table positions with each other, the same query will work: select test.*, t2.ROWID from test, attached.test2 t2 *^^^ this works just fine.* Problem occurres only if following conditions are met: - table in local database is WITHOUT ROWID - table in attached database is a regular table with ROWID - query selects ROWID from the regular table - both tables must be mentioned in the FROM clause - the WITHOUT ROWID table must be mentioned as the second one Regards, -- Paweł Salawa pawelsal...@gmail.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Building sqlite 3.7.3 with Tcl binding
Hi, How do I compile sqlite 3.7.3 with Tcl bindings? I don't see any tcl-related options in configure script (the amalgamation distribution) and default compilation doesn't probide Tcl bindings. Thanks for help! Regards, -- Paweł Salawa pawelsal...@gmail.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Tcl bindins problem, once again
Hi,
I mentioned the problem before but it seems to be ignored for now. I'd
really appreciate some response, at least confirmation or denial that
I'm right about the problem.
Quoting from previous thread:
--- QUOTE ---
select a1.txt, a2.txt
from a a1
join a a2
using (ref)
where a1.lang = 'french'
and
a2.lang = 'english';
--- END OF QUOTE ---
Note, that the "SELECT" is not known to application - it's custom select
typed by end-user, so I don't deciede about "AS" aliases for columns.
--- QUOTE ---
We can either use:
db eval $query arr {
parray arr
}
But then we would have arr(*) = a.txt a.txt but only one array entry:
arr(a.txt).
Other way is to use:
set results [db eval $query]
foreach cellValue $results {
puts $cellValue
}
But then we lose informations about column names in results.
One of possible solutions would be to add some prefix or suffix to array
indexes.
Other one would be to provide column names in results of [db eval $query],
using for example flag, like: [db eval $query -withColumnNames] or
something
like that. The results would be: [list $columnList $tableDataList]
--- END OF QUOTE ---
Currently I implemented workaround with 2 calls mixed. First is "db eval
$query arr {...}" to collect list of result columns and second "set
results [db eval $query]" to get all values.
It works, but it causes additional query execution. It's a workaround,
not a solution.
Shouldn't it be fixed?
Regards,
Googie
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] No error on UPDATE setting duplicated value on UNIQUE column.
> Maybe you should remove the "ON CONFLICT IGNORE" clause at the end of the > uniqueness constraint... You're right, of course. Shame on me for missing it :( Shame on me! -- Paweł Salawa pawelsal...@gmail.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] No error on UPDATE setting duplicated value on UNIQUE column.
Hi, My SQLite is 3.7.2. I have a table like this: CREATE TABLE [newsd] ( [id] INTEGER PRIMARY KEY AUTOINCREMENT, [date] INTEGER NOT NULL, [title] TEXT NOT NULL, [yhfgdfhd] NONE, CONSTRAINT "fg" UNIQUE ([yhfgdfhd]) ON CONFLICT IGNORE ) so column [yhfgdfhd] is UNIQUE, and [title] is NOT NULL. Now follow the log of 'sqlite3' calls: $ sqlite3 "data.db" SQLite version 3.7.2 Enter ".help" for instructions sqlite> select * from newsd where ROWID = 16; 16|12.2.12||e sqlite> select * from newsd where ROWID = 21; 21|||x sqlite> UPDATE [newsd] SET [yhfgdfhd] = 'e' WHERE ROWID = 21; sqlite> select * from newsd where ROWID = 21; 21|||x sqlite> UPDATE [newsd] SET [title] = NULL WHERE ROWID = 21; SQL error: newsd.title may not be NULL So I'm indeed unable to set duplicate on UNIQUE column, but SQLite doesn't raise error, thus my application doesn't call ROLLBACK for that case. Last call at the end is to ensure that other constraint violation raises error correctly. Same behaviour takes place for tclsqlite extension. Regards, -- Paweł Salawa pawelsal...@gmail.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Tcl bindings are a little problematic.
Hi,
I have a feeling that thread "Tcl bindings are a little problematic." is
forgotten, but yet not concluded.
I want to catch your eye again on it. Please see response below.
Regards,
Pawel
From: Paweł Salawa <pawelsal...@...>
Subject: Re: Tcl bindings are a little problematic.
Newsgroups: gmane.comp.db.sqlite.general
Date: 2010-09-05 10:19:35 GMT (2 days, 19 hours and 47 minutes ago)
> The column names on queries that do not have AS clauses on the columns are
> undefined and are subject to change. To get specific column names, use AS:
>
> select a1.txt AS a1, a2.txt AS a2
>
> Then your arr() will contain entries a(*), a(a1), and a(a2).
True, but in case of custom SQL typed by end-user (here by user of
SQLiteStudio) both result columns can be named exactly same by the user.
For now it can be handled by double call (in case of SELECT), first using
arr() and second with results returned in list, but a disadventage is obvious.
Dnia czwartek, 2 września 2010, napisałeś:
> On Wed, Sep 1, 2010 at 6:11 PM, Paweł Salawa bitrock.com> wrote:
>
> > Hi,
> >
> > It's pretty hard to make a SELECT from same table, but using two different
> > aliases for the table and at the same time also getting column names in
> > results - using Tcl bindings.
> >
> > The SQL query would be:
> >
> > select a1.txt, a2.txt
> > from a a1
> > join a a2
> > using (ref)
> > where a1.lang = 'french'
> > and
> > a2.lang = 'english';
> >
> > We can either use:
> > db eval $query arr {
> > parray arr
> > }
> >
> > But then we would have arr(*) = a.txt a.txt
> > but only one entry: arr(a.txt) = something
> > which can fail (see thread on forum I mentioned below).
> >
>
> The column names on queries that do not have AS clauses on the columns are
> undefined and are subject to change. To get specific column names, use AS:
>
> select a1.txt AS a1, a2.txt AS a2
>
> Then your arr() will contain entries a(*), a(a1), and a(a2).
>
>
> >
> > Other way is to use:
> > set results [db eval $query]
> > foreach cellValue $results {
> > puts $cellValue
> > }
> >
> > But then we lose informations about column names in results.
> >
> > The problem was discussed here (escpecially first and last post in the
> > thread): http://forum.sqlitestudio.one.pl/viewtopic.php?f=4=3596
> >
> > One of possible solutions would be to add some prefix or suffix to array
> > indexes.
> >
> > Other one would be to provide column names in results of [db eval $query],
> > using for example flag, like: [db eval $query -withColumnNames] or
> > something
> > like that. The results would be: [list $columnList $tableDataList]
> >
> > I hope I explained it clearly enough. In case of any questions don't
> > hesistate
> > to ask.
> >
> > Regards,
> > --
> > Paweł Salawa
> > paul bitrock.com
> > ___
> > sqlite-users mailing list
> > sqlite-users sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
>
>
>
> --
> D. Richard Hipp
> drh sqlite.org
>
--
Paweł Salawa
paul bitrock.com
___
sqlite-users mailing list
sqlite-users sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
--
Paweł Salawa
pawelsal...@gmail.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Tcl bindings are a little problematic.
> The column names on queries that do not have AS clauses on the columns are
> undefined and are subject to change. To get specific column names, use AS:
>
> select a1.txt AS a1, a2.txt AS a2
>
> Then your arr() will contain entries a(*), a(a1), and a(a2).
True, but in case of custom SQL typed by end-user (here by user of
SQLiteStudio) both result columns can be named exactly same by the user.
For now it can be handled by double call (in case of SELECT), first using
arr() and second with results returned in list, but a disadventage is obvious.
Dnia czwartek, 2 września 2010, napisałeś:
> On Wed, Sep 1, 2010 at 6:11 PM, Paweł Salawa <p...@bitrock.com> wrote:
>
> > Hi,
> >
> > It's pretty hard to make a SELECT from same table, but using two different
> > aliases for the table and at the same time also getting column names in
> > results - using Tcl bindings.
> >
> > The SQL query would be:
> >
> > select a1.txt, a2.txt
> > from a a1
> > join a a2
> > using (ref)
> > where a1.lang = 'french'
> > and
> > a2.lang = 'english';
> >
> > We can either use:
> > db eval $query arr {
> > parray arr
> > }
> >
> > But then we would have arr(*) = a.txt a.txt
> > but only one entry: arr(a.txt) = something
> > which can fail (see thread on forum I mentioned below).
> >
>
> The column names on queries that do not have AS clauses on the columns are
> undefined and are subject to change. To get specific column names, use AS:
>
> select a1.txt AS a1, a2.txt AS a2
>
> Then your arr() will contain entries a(*), a(a1), and a(a2).
>
>
> >
> > Other way is to use:
> > set results [db eval $query]
> > foreach cellValue $results {
> > puts $cellValue
> > }
> >
> > But then we lose informations about column names in results.
> >
> > The problem was discussed here (escpecially first and last post in the
> > thread): http://forum.sqlitestudio.one.pl/viewtopic.php?f=4=3596
> >
> > One of possible solutions would be to add some prefix or suffix to array
> > indexes.
> >
> > Other one would be to provide column names in results of [db eval $query],
> > using for example flag, like: [db eval $query -withColumnNames] or
> > something
> > like that. The results would be: [list $columnList $tableDataList]
> >
> > I hope I explained it clearly enough. In case of any questions don't
> > hesistate
> > to ask.
> >
> > Regards,
> > --
> > Paweł Salawa
> > p...@bitrock.com
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
>
>
>
> --
> D. Richard Hipp
> d...@sqlite.org
>
--
Paweł Salawa
p...@bitrock.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Tcl bindings are a little problematic.
Hi,
It's pretty hard to make a SELECT from same table, but using two different
aliases for the table and at the same time also getting column names in
results - using Tcl bindings.
The SQL query would be:
select a1.txt, a2.txt
from a a1
join a a2
using (ref)
where a1.lang = 'french'
and
a2.lang = 'english';
We can either use:
db eval $query arr {
parray arr
}
But then we would have arr(*) = a.txt a.txt
but only one entry: arr(a.txt) = something
which can fail (see thread on forum I mentioned below).
Other way is to use:
set results [db eval $query]
foreach cellValue $results {
puts $cellValue
}
But then we lose informations about column names in results.
The problem was discussed here (escpecially first and last post in the
thread): http://forum.sqlitestudio.one.pl/viewtopic.php?f=4=3596
One of possible solutions would be to add some prefix or suffix to array
indexes.
Other one would be to provide column names in results of [db eval $query],
using for example flag, like: [db eval $query -withColumnNames] or something
like that. The results would be: [list $columnList $tableDataList]
I hope I explained it clearly enough. In case of any questions don't hesistate
to ask.
Regards,
--
Paweł Salawa
p...@bitrock.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
