Re: [sqlite] Problem with SELECT by rowid after INSERT

[James K. Lowden]

On Fri, 12 Apr 2019 11:40:13 -0400
Jim Dossey  wrote:

> CREATE TABLE "sessiond" (
> "journal" VARCHAR(4) DEFAULT '' NOT NULL,
> "session" VARCHAR(16) DEFAULT '' NOT NULL,
> "pid" INTEGER DEFAULT 0 NOT NULL,
> rowid INTEGER PRIMARY KEY
> );

Although it has nothing to do with the problem you posed, at some
point you might want to reconsider this design.  

A row in this table is allowed to exist even if contains no
information.  The journal and session can be empty strings, and the pid
0 (invalid).  That, in fact, is the default.  

I assume the rowid is meaningless, just a way to refer to the row from
another table.  

The table has the hallmark of an application design that inserts "empty
records" and updates them.  That's a whole other ball of wax, and I
don't want to critique an assumption.  But it's easy to see how that
kind of design is inefficient and defeats features provided by the
DBMS. 

The DEFAULT '' NOT NULL construct is particularly pernicious, because
NULL is useful in ways a zero-length string is not.  NULL can always
be converted to something else with coalesce, and is usually obvious
when interrogating the database interactively.  The same cannot be said
of zero-length strings.  

Instead of allowing any number of meaningless duplicate rows, why not
let the DBMS do a little work for you?  For example: 

CREATE TABLE sessiond (
journal VARCHAR(4) NOT NULL,
session VARCHAR(16) NOT NULL,
pid INTEGER NOT NULL check (pid > 0), 
primary key( pid, journal, session )
);

If the rowid actually helps, sure use it.  If it's  better as the
primary key, that's fine; it's an arbitrary choice, logically
speaking.  But the {pid, journal, session} tuple should be unique
regardless.  If not, what to 2 identical rows represent, and how are
they different?  (I don't need to know, but you do.)  

When you need to track two indistigushable events, the solution isn't
to allow two undifferentiated rows with an arbitrary discriminator.
The solution is to add a "quantity" column, and count them.  It's easy
to delete the row when quantity reaches zero, or cull zero-quantity
rows on selection.  

For extra credit, add CHECK constraints to enforce the lengths of
journal and session.  

With a table like that, you can prevent applications from inserting
nonsense into the database.  I've used that strategy to good effect.
Once the developers have agreed they don't want garbage in the
database, it's pretty rare, when an insert fails, for the programmer to
say, "yeah, I meant to do that".  Nearly always, it's a bug.  

--jkl







___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Problem with SELECT by rowid after INSERT

[Keith Medcalf]

On Friday, 12 April, 2019 14:48, Jim Dossey  wrote:

>On Apr 12, 2019, at 3:27 PM, Keith Medcalf  wrote:

>>> To be a little more specific, the problem happens when I try to do
>>> sqlite3_bind_int() on the prepared statement using the new rowid.
>It
>>> doesn't use the rowid it uses NULL.
>>>
>>> The prepared statement is "SELECT * FROM sessiond WHERE rowid=?;"
>>> Then I call sqlite3_bind_int(ppStmt, 1, rowid) and the resulting
>>> SELECT command is
>>> SELECT * FROM "sessiond" WHERE "rowid"=NULL;
>>> Which is obtained by calling sqlite3_expanded_sql().
>>
>> This does not make sense.  It indicates that you did not actually
>bind a value to the parameter in question
>>
>
>It may not make sense, but that is what happened.  I tried it
>repeatedly with different values for rowid.  In every case, if the
>rowid did not exist in the table, sqlite3_bind_int() would insert
>NULL in place of the '?' Instead of the rowid.  The rowid's I was
>using were in the range of 10 to 25, so there were no extreme values.

Interesting because it works for me.  Everytime.  And there does not even need 
to be any records in the table at all (you just need a table definition that 
allows the prepare to succeed):


#include 
#include 
#include 
#include 

int main(int argc, char **argv)
{
sqlite3 *db;
sqlite3_stmt * stmt;
char *exp;

int rc;

rc = sqlite3_open("test.db", );
if (rc != SQLITE_OK) return 1;
rc = sqlite3_prepare_v2(db, "select * from t where rowid=?;", -1, , 0);
if (rc != SQLITE_OK) return 1;
rc = sqlite3_bind_int(stmt, 1, atoi(argv[1]));
if (rc != SQLITE_OK) return 1;
exp = sqlite3_expanded_sql(stmt);
printf("%s\n", exp);
return 0;
}

>test 1
select * from t where rowid=1;

>test 42
select * from t where rowid=42;

>test 57
select * from t where rowid=57;

>test 0
select * from t where rowid=0;

>test -5
select * from t where rowid=-5;

The only way I can get "where rowid=NULL" is if I do not bind anything at all 
to parameter 1 ...

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.




___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Problem with SELECT by rowid after INSERT

[Jim Dossey]

On Apr 12, 2019, at 3:27 PM, Keith Medcalf  wrote:
> 
> 
>> To be a little more specific, the problem happens when I try to do
>> sqlite3_bind_int() on the prepared statement using the new rowid.  It
>> doesn't use the rowid it uses NULL.
>> 
>> The prepared statement is "SELECT * FROM sessiond WHERE rowid=?;"
>> Then I call sqlite3_bind_int(ppStmt, 1, rowid) and the resulting
>> SELECT command is
>> SELECT * FROM "sessiond" WHERE "rowid"=NULL;
>> Which is obtained by calling sqlite3_expanded_sql().
> 
> This does not make sense.  It indicates that you did not actually bind a 
> value to the parameter in question
> 

It may not make sense, but that is what happened.  I tried it repeatedly with 
different values for rowid.  In every case, if the rowid did not exist in the 
table, sqlite3_bind_int() would insert NULL in place of the '?' Instead of the 
rowid.  The rowid's I was using were in the range of 10 to 25, so there were no 
extreme values.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Problem with SELECT by rowid after INSERT

[Jim Dossey]

That was it.  There was another SELECT going on that had not been finalized.

Thank you Richard, Simon, and Graham for you help on this.

> On Apr 12, 2019, at 2:36 PM, Richard Hipp  wrote:
> 
> On 4/12/19, Jim Dossey  wrote:
>> I'm doing the INSERT first, without a BEGIN ... COMMIT transaction, then I'm
>> doing the SELECT.  Shouldn't the INSERT do it's own COMMIT which should make
>> the new row visible to the SELECT?   Should I add a BEGIN ... COMMIT around
>> the INSERT?
> 
> Perhaps the SELECT is running inside of a transaction that was started
> before you did the INSERT.  For example, perhaps you didn't
> sqlite3_finalize() the previous SELECT, which caused it to hold the
> read transaction open.
> 
> -- 
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Problem with SELECT by rowid after INSERT

[Keith Medcalf]

On Friday, 12 April, 2019 09:40, Jim Dossey  wrote"

This does not really make a lot of sense at all for the following reasons:


>I have a table define like this:

>CREATE TABLE "sessiond" (
>"journal" VARCHAR(4) DEFAULT '' NOT NULL,
>"session" VARCHAR(16) DEFAULT '' NOT NULL,
>"pid" INTEGER DEFAULT 0 NOT NULL,
>rowid INTEGER PRIMARY KEY
>);

>In my application I open 2 connections to this table, one for reading
>and one for writing.  I do that because sometimes we do a SELECT, and
>while reading through those rows we'll UPDATE them.  With only 1
>connection the SELECT will block the UPDATE.

This (the above) is incorrect.  The single connection will be upgraded to a 
writer when the update is step'ed on that connection.  It will not block.  
Whether transmogification of the database underneath an "in progress" read is a 
good idea or not is a completely separate issue.

>I also have WAL mode turned on.

>The problem is when I do an INSERT and then try to SELECT that record
>by rowid it doesn't find it.  The INSERT is done on the write
>connection and the SELECT is done on the read connection.  However,
>if I open a new connection and do the SELECT, the new row is there.

What is the transaction state of the "read connection"?  Did you remember to 
RESET all prior statements that were "reading" from that connection?

WAL mode creates transactions so that readers are in a mode called 
REPEATABLE-READ.  They will not see any changes COMMIT'ed by other connections 
until all statements are completed and reset and the read transaction 
commit'ed.  This is so that a crapload of overlapping statements reading data 
through the same connection will always see THE SAME IDENTICAL AND UNCHANGING 
VIEW of the database until they are all RESET and FINALIZED (for implicit 
transactions, for explicit transactions you have to end the transaction as 
well, since it is the "opening" of the transaction that determines the 
REPEATABLE-READ state).

>To be a little more specific, the problem happens when I try to do
>sqlite3_bind_int() on the prepared statement using the new rowid.  It
>doesn't use the rowid it uses NULL.
>
>The prepared statement is "SELECT * FROM sessiond WHERE rowid=?;"
>Then I call sqlite3_bind_int(ppStmt, 1, rowid) and the resulting
>SELECT command is
>SELECT * FROM "sessiond" WHERE "rowid"=NULL;
>Which is obtained by calling sqlite3_expanded_sql().

This does not make sense.  It indicates that you did not actually bind a value 
to the parameter in question

>If I use an older rowid in the SELECT, it works just fine.  It just
>doesn't work when using the rowid that was just created.

Define "older rowid".  a rowid is just a number.  Therefore is impossible for a 
rowid to be new or old as all numbers have been in use for thousands of years.


---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.




___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Problem with SELECT by rowid after INSERT

[Graham Holden]

Friday, April 12, 2019, 7:23:31 PM, Jim Dossey  wrote:

> I'm doing the INSERT first, without a BEGIN ... COMMIT transaction,
> then I'm doing the SELECT.  Shouldn't the INSERT do it's own COMMIT
> which should make the new row visible to the SELECT?   Should I add
> a BEGIN ... COMMIT around the INSERT?

Have you "finished" executing the SELECT statement? If you're not
using explicit BEGIN...COMMIT, then each statement runs in its own
transaction. However, the implicit transaction started with a SELECT
does not complete until either sqlite3_step() returns SQLITE_DONE or
you explicitly call sqlite3_finalize(). As I understand it, if, for
example, you only expect one row from your SELECT statement and execute
sqlite3_step() once to retrieve that row, then the SELECT statement
(and the implicit transaction it is running in) is still active. As
such, any NEW SELECT statements you prepare/step will still be in the
same transaction and so wouldn't see the result of any INSERTs.



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Problem with SELECT by rowid after INSERT

[Richard Hipp]

On 4/12/19, Jim Dossey  wrote:
> I'm doing the INSERT first, without a BEGIN ... COMMIT transaction, then I'm
> doing the SELECT.  Shouldn't the INSERT do it's own COMMIT which should make
> the new row visible to the SELECT?   Should I add a BEGIN ... COMMIT around
> the INSERT?

Perhaps the SELECT is running inside of a transaction that was started
before you did the INSERT.  For example, perhaps you didn't
sqlite3_finalize() the previous SELECT, which caused it to hold the
read transaction open.

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Problem with SELECT by rowid after INSERT

[Jim Dossey]

I'm doing the INSERT first, without a BEGIN ... COMMIT transaction, then I'm 
doing the SELECT.  Shouldn't the INSERT do it's own COMMIT which should make 
the new row visible to the SELECT?   Should I add a BEGIN ... COMMIT around the 
INSERT?

The INSERT is done with sqlite3_exec().  Do I need to call any other functions 
after that to make the new row visible to other connections?

> On Apr 12, 2019, at 2:15 PM, Richard Hipp  wrote:
> 
> On 4/12/19, Jim Dossey  wrote:
>> 
>> The problem is when I do an INSERT and then try to SELECT that record by
>> rowid it doesn't find it.
> 
> Yes, because the SELECT is working inside a single transaction, but
> the INSERT is adding content in a separate, subsequent transaction
> which the SELECT never sees.  This is by design.
> 
> -- 
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Problem with SELECT by rowid after INSERT

[Richard Hipp]

On 4/12/19, Jim Dossey  wrote:
>
> The problem is when I do an INSERT and then try to SELECT that record by
> rowid it doesn't find it.

Yes, because the SELECT is working inside a single transaction, but
the INSERT is adding content in a separate, subsequent transaction
which the SELECT never sees.  This is by design.

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Problem with SELECT by rowid after INSERT

[Jim Dossey]

I do not have shared-cache mode enabled, or anything else that I can see, other 
than WAL mode.  I am not doing any transactions, so the INSERT and SELECT are 
self contained transactions.  This particular database file is opened using 
ATTACH rather than sqlite3_open(), and it is attached to the 2 database 
connections that I have open.

I did another test where I removed the '?' Parameter and the call to 
seqlite3_bind_int() and just created a SELECT statement with the rowid 
hard-coded in, like 'SELECT * FROM sessiond WHERE rowid=20;', and that also did 
not work.  So it isn't the sqlite3_bind_int() function that is causing the 
problem.

As as side note, apparently sqlite3_bind_int() inserts a NULL when doing 'WHERE 
rowid=?" If the rowid you are looking for doesn't exist in the table.  I tried 
several SELECTs (using the same code) with various rowid values, some existent 
and some not, and for the non existent rowid's sqlite3_bind_int() always 
inserts a NULL.

> On Apr 12, 2019, at 1:38 PM, Simon Slavin  wrote:
> 
> On 12 Apr 2019, at 6:32pm, Jim Dossey  wrote:
> 
>> It's just when I use 2 different connections that the second connection does 
>> not see the rowid that was just added. 
> 
> Okay, I see you're using WAL mode, and two connections.  Have you set up 
> Shared-Cache Mode or anything else that might 'merge' the two connections ?
> 
> Have you defined any transactions, or are both the INSERT and the SELECT 
> creating their own transactions ?
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Problem with SELECT by rowid after INSERT

[Simon Slavin]

On 12 Apr 2019, at 6:32pm, Jim Dossey  wrote:

> It's just when I use 2 different connections that the second connection does 
> not see the rowid that was just added. 

Okay, I see you're using WAL mode, and two connections.  Have you set up 
Shared-Cache Mode or anything else that might 'merge' the two connections ?

Have you defined any transactions, or are both the INSERT and the SELECT 
creating their own transactions ?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Problem with SELECT by rowid after INSERT

[Jim Dossey]

Yes, I've done extensive debugging to make sure the rowid is valid.

I even did a test where I did the INSERT and the SELECT on the same connection 
and that works okay.  It's just when I use 2 different connections that the 
second connection does not see the rowid that was just added.  I ran the CLI on 
the database file and did a SELECT there and I know that the record exists and 
the rowid that I'm searching for does exist.  But sqlite3_bind_int() inserts a 
NULL instead of the rowid.

> On Apr 12, 2019, at 1:26 PM, Simon Slavin  wrote:
> 
> On 12 Apr 2019, at 6:23pm, Jim Dossey  wrote:
> 
>> I did use sqlite3_last_insert_rowid() to get the last rowid. But I used 
>> sqlite3_expanded_sql to get the actual SQL statement that was processed to 
>> find out that sqlite3_bind_int() had inserted a NULL instead of the rowid I 
>> was looking for.
> 
> Sorry I misunderstood your post.  As a debugging test, have you 
> printed/logged the value received from sqlite3_last_insert_rowid() to make 
> sure it's the expected value ?
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Problem with SELECT by rowid after INSERT

[Simon Slavin]

On 12 Apr 2019, at 6:23pm, Jim Dossey  wrote:

> I did use sqlite3_last_insert_rowid() to get the last rowid. But I used 
> sqlite3_expanded_sql to get the actual SQL statement that was processed to 
> find out that sqlite3_bind_int() had inserted a NULL instead of the rowid I 
> was looking for.

Sorry I misunderstood your post.  As a debugging test, have you printed/logged 
the value received from sqlite3_last_insert_rowid() to make sure it's the 
expected value ?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Problem with SELECT by rowid after INSERT

[Jim Dossey]

I did use sqlite3_last_insert_rowid() to get the last rowid.  But I used 
sqlite3_expanded_sql to get the actual SQL statement that was processed to find 
out that sqlite3_bind_int() had inserted a NULL instead of the rowid I was 
looking for.

> On Apr 12, 2019, at 12:09 PM, Simon Slavin  wrote:
> 
> On 12 Apr 2019, at 4:40pm, Jim Dossey  wrote:
> 
>> Which is obtained by calling sqlite3_expanded_sql().
> 
> Was that a copy-paste error or do you actually want
> 
> 
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Problem with SELECT by rowid after INSERT

[Simon Slavin]

On 12 Apr 2019, at 4:40pm, Jim Dossey  wrote:

> Which is obtained by calling sqlite3_expanded_sql().

Was that a copy-paste error or do you actually want


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Problem with SELECT by rowid after INSERT

[Jim Dossey]

I have a table define like this:

CREATE TABLE "sessiond" (
"journal" VARCHAR(4) DEFAULT '' NOT NULL,
"session" VARCHAR(16) DEFAULT '' NOT NULL,
"pid" INTEGER DEFAULT 0 NOT NULL,
rowid INTEGER PRIMARY KEY
);

In my application I open 2 connections to this table, one for reading and one 
for writing.  I do that because sometimes we do a SELECT, and while reading 
through those rows we'll UPDATE them.  With only 1 connection the SELECT will 
block the UPDATE.  I also have WAL mode turned on.

The problem is when I do an INSERT and then try to SELECT that record by rowid 
it doesn't find it.  The INSERT is done on the write connection and the SELECT 
is done on the read connection.  However, if I open a new connection and do the 
SELECT, the new row is there.

To be a little more specific, the problem happens when I try to do 
sqlite3_bind_int() on the prepared statement using the new rowid.  It doesn't 
use the rowid it uses NULL.

The prepared statement is "SELECT * FROM sessiond WHERE rowid=?;"
Then I call sqlite3_bind_int(ppStmt, 1, rowid) and the resulting SELECT command 
is
SELECT * FROM "sessiond" WHERE "rowid"=NULL;
Which is obtained by calling sqlite3_expanded_sql().

If I use an older rowid in the SELECT, it works just fine.  It just doesn't 
work when using the rowid that was just created.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Problem with select

[Black, Michael (IS)]

You missed the prepare for your select_sql:



 sqlite3_stmt *select_stmt = NULL;
 sqlite3_prepare_v2(db,select_sql,strlen(select_sql),_stmt,NULL);

Add that one line and you get:



Successfully bound string for insert: 'zweiter Anlauf/Versuch'
Successfully bound real for insert: 22
INSERT completed

Found row
Column eventtype(0): 'zweiter Anlauf/Versuch'
Column counter(1): '22'

SELECT successfully completed
[mblack@melb0113 sqlite]$



If you close and re-open the database last_row_id won't know anything.  That 
only lasts for your session.





Michael D. Black

Senior Scientist

Advanced Analytics Directorate

Advanced GEOINT Solutions Operating Unit

Northrop Grumman Information Systems


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of deltagam...@gmx.net [deltagam...@gmx.net]
Sent: Wednesday, June 20, 2012 8:03 AM
To: sqlite-users
Subject: EXT :[sqlite] Problem with select

Hello,

I have a problem with the selection of the row, errorcode 21 s

Indeed I would like to retrieve all rows, but as far as I understand it,
this construction will retrieve only the last row/insert.

What happens if  the db is closed after the insert and reopened then for
the select-statement ?



===

#include 
#include 
#include "sqlite3.h"

#include 
#include 
#include 
#include 

using namespace std;


int main() {



 string filename = "errorlog.txt";
 std::ofstream outfile(filename);

 char eventtype[]="zweiter Anlauf/Versuch";
 int zaehler = strlen(eventtype);


 int rc;
 char *exec_errmsg;

 const char dbname[] = "ef.db";
 sqlite3 *db = NULL;

 rc = sqlite3_open(dbname, );
 if(SQLITE_OK != rc) {
 outfile << "Can't open database "<< dbname << " (" << rc << "):
" << sqlite3_errmsg(db) << std::endl;
 //fprintf(stderr, "Can't open database %s (%i): %s\n", dbname,
rc, sqlite3_errmsg(db));
 sqlite3_close(db);
 exit(1);
 }

/maybe discard this part
///
 const char create_sql[] = "CREATE TABLE eventlog ("
 "id INTEGER PRIMARY KEY,"
 "eventdate DATETIME default current_timestamp,"
 "eventtype TEXT,"
 "counter INTEGER"
 ")";

 // Use exec to run simple statements that can only fail/succeed
 rc = sqlite3_exec(db, create_sql, NULL, NULL, _errmsg);
 if(SQLITE_OK != rc) {
 outfile << "Error creating table (" << rc << "): " <<
sqlite3_errmsg(db) << std::endl;
 //fprintf(stderr, "Error creating table (%i): %s\n", rc,
exec_errmsg);
 sqlite3_free(exec_errmsg);
 //sqlite3_close(db);
 //exit(1);
 }

/maybe discard this part
///


 const char insert_sql[] = "INSERT INTO eventlog (eventtype,
counter) VALUES (?,?)";
 sqlite3_stmt *insert_stmt = NULL;



 rc = sqlite3_prepare_v2(db, insert_sql, -1, _stmt, NULL);
 if(SQLITE_OK != rc) {
 outfile << "Can't prepare insert statment " << insert_sql << "
(" << rc << "): " << sqlite3_errmsg(db) << std::endl;
 //fprintf(stderr, "Can't prepare insert statment %s (%i):
%s\n", insert_sql, rc, sqlite3_errmsg(db));
 sqlite3_close(db);
 exit(1);
 }



 rc = sqlite3_bind_text(insert_stmt, 1, eventtype,
strlen(eventtype), NULL);
 if(SQLITE_OK != rc) {
 outfile << "Error binding value in insert (%i): %s\n", rc,
sqlite3_errmsg(db);
 //fprintf(stderr, "Error binding value in insert (%i): %s\n",
rc, sqlite3_errmsg(db));
 sqlite3_close(db);
 exit(1);
 } else {
 printf("Successfully bound string for insert: '%s'\n", eventtype);
 }

 rc = sqlite3_bind_int(insert_stmt, 2, zaehler);
 if(SQLITE_OK != rc) {
 outfile << "Error binding value in insert (%i): %s\n", rc,
sqlite3_errmsg(db);
 //fprintf(stderr, "Error binding value in insert (%i): %s\n",
rc, sqlite3_errmsg(db));
 sqlite3_close(db);
 exit(1);
 } else {
 printf("Successfully bound real for insert: %d\n", zaehler);
 }


 rc = sqlite3_step(insert_stmt);
 if(SQLITE_DONE != rc) {
 outfile << "insert statement didn't return DONE (%i): %s\n",
rc, sqlite3_errmsg(db);
 //fprintf(stderr, "insert statement didn't return DONE (%i):
%s\n", rc, sqlite3_errmsg(db));
 } else {
 printf("INSERT

Re: [sqlite] Problem with select

[Simon Slavin]

On 20 Jun 2012, at 2:03pm, deltagam...@gmx.net wrote:

> I have a problem with the selection of the row, errorcode 21 s

Look up the error code in the list, or make SQLite print out the text message 
which does with it.

On this page



you see that error code 21 means that you have called the wrong SQLite 
functions in the wrong order.  Looking at your code, I see that you are doing 
some binding and stepping for the SELECT command, but I do not see you have 
called _prepare() for it.  So perhaps you should take a look at this page



and make sure that you always follow all those five steps for every statement.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Problem with select

[Marcus Grimm]

On 20.06.2012 15:03, deltagam...@gmx.net wrote:

Hello,

I have a problem with the selection of the row, errorcode 21 s


You are missing a sqlite3_prepare_v2 for your select.



Indeed I would like to retrieve all rows, but as far as I understand it, this 
construction will retrieve only the last
row/insert.


Well.. if it would work then yes because you ask for a specific ID, don't you ?



What happens if the db is closed after the insert and reopened then for the 
select-statement ?



What do you expect what will happen ?





===

#include 
#include 
#include "sqlite3.h"

#include 
#include 
#include 
#include 

using namespace std;


int main() {



string filename = "errorlog.txt";
std::ofstream outfile(filename);

char eventtype[]="zweiter Anlauf/Versuch";
int zaehler = strlen(eventtype);


int rc;
char *exec_errmsg;

const char dbname[] = "ef.db";
sqlite3 *db = NULL;

rc = sqlite3_open(dbname, );
if(SQLITE_OK != rc) {
outfile << "Can't open database "<< dbname << " (" << rc << "): " << 
sqlite3_errmsg(db) << std::endl;
//fprintf(stderr, "Can't open database %s (%i): %s\n", dbname, rc, 
sqlite3_errmsg(db));
sqlite3_close(db);
exit(1);
}

/ maybe discard this part 
///
const char create_sql[] = "CREATE TABLE eventlog ("
"id INTEGER PRIMARY KEY,"
"eventdate DATETIME default current_timestamp,"
"eventtype TEXT,"
"counter INTEGER"
")";

// Use exec to run simple statements that can only fail/succeed
rc = sqlite3_exec(db, create_sql, NULL, NULL, _errmsg);
if(SQLITE_OK != rc) {
outfile << "Error creating table (" << rc << "): " << sqlite3_errmsg(db) << 
std::endl;
//fprintf(stderr, "Error creating table (%i): %s\n", rc, exec_errmsg);
sqlite3_free(exec_errmsg);
//sqlite3_close(db);
//exit(1);
}

/ maybe discard this part 
///


const char insert_sql[] = "INSERT INTO eventlog (eventtype, counter) VALUES 
(?,?)";
sqlite3_stmt *insert_stmt = NULL;



rc = sqlite3_prepare_v2(db, insert_sql, -1, _stmt, NULL);
if(SQLITE_OK != rc) {
outfile << "Can't prepare insert statment " << insert_sql << " (" << rc << "): " << 
sqlite3_errmsg(db) << std::endl;
//fprintf(stderr, "Can't prepare insert statment %s (%i): %s\n", insert_sql, 
rc, sqlite3_errmsg(db));
sqlite3_close(db);
exit(1);
}



rc = sqlite3_bind_text(insert_stmt, 1, eventtype, strlen(eventtype), NULL);
if(SQLITE_OK != rc) {
outfile << "Error binding value in insert (%i): %s\n", rc, sqlite3_errmsg(db);
//fprintf(stderr, "Error binding value in insert (%i): %s\n", rc, 
sqlite3_errmsg(db));
sqlite3_close(db);
exit(1);
} else {
printf("Successfully bound string for insert: '%s'\n", eventtype);
}

rc = sqlite3_bind_int(insert_stmt, 2, zaehler);
if(SQLITE_OK != rc) {
outfile << "Error binding value in insert (%i): %s\n", rc, sqlite3_errmsg(db);
//fprintf(stderr, "Error binding value in insert (%i): %s\n", rc, 
sqlite3_errmsg(db));
sqlite3_close(db);
exit(1);
} else {
printf("Successfully bound real for insert: %d\n", zaehler);
}


rc = sqlite3_step(insert_stmt);
if(SQLITE_DONE != rc) {
outfile << "insert statement didn't return DONE (%i): %s\n", rc, 
sqlite3_errmsg(db);
//fprintf(stderr, "insert statement didn't return DONE (%i): %s\n", rc, 
sqlite3_errmsg(db));
} else {
printf("INSERT completed\n\n");
}


// start selecting ///
const char select_sql[] = "SELECT eventtype, counter FROM eventlog WHERE id=?";
//const char insert_sql[] = "INSERT INTO eventlog (eventtype, counter) VALUES 
(?,?)";

sqlite3_stmt *select_stmt = NULL;

// Now attempt to get that row out
sqlite3_int64 id = sqlite3_last_insert_rowid(db);
sqlite3_bind_int64(select_stmt, 1, id);


// This is your standard pattern
while(SQLITE_ROW == (rc = sqlite3_step(select_stmt))) {
int col;
printf("Found row\n");
for(col=0; col

[sqlite] Problem with select

[deltagam...@gmx.net]

Hello,

I have a problem with the selection of the row, errorcode 21 s

Indeed I would like to retrieve all rows, but as far as I understand it, 
this construction will retrieve only the last row/insert.


What happens if  the db is closed after the insert and reopened then for 
the select-statement ?




===

#include 
#include 
#include "sqlite3.h"

#include 
#include 
#include 
#include 

using namespace std;


int main() {



string filename = "errorlog.txt";
std::ofstream outfile(filename);

char eventtype[]="zweiter Anlauf/Versuch";
int zaehler = strlen(eventtype);


int rc;
char *exec_errmsg;

const char dbname[] = "ef.db";
sqlite3 *db = NULL;

rc = sqlite3_open(dbname, );
if(SQLITE_OK != rc) {
outfile << "Can't open database "<< dbname << " (" << rc << "): 
" << sqlite3_errmsg(db) << std::endl;
//fprintf(stderr, "Can't open database %s (%i): %s\n", dbname, 
rc, sqlite3_errmsg(db));

sqlite3_close(db);
exit(1);
}

/maybe discard this part 
///

const char create_sql[] = "CREATE TABLE eventlog ("
"id INTEGER PRIMARY KEY,"
"eventdate DATETIME default current_timestamp,"
"eventtype TEXT,"
"counter INTEGER"
")";

// Use exec to run simple statements that can only fail/succeed
rc = sqlite3_exec(db, create_sql, NULL, NULL, _errmsg);
if(SQLITE_OK != rc) {
outfile << "Error creating table (" << rc << "): " << 
sqlite3_errmsg(db) << std::endl;
//fprintf(stderr, "Error creating table (%i): %s\n", rc, 
exec_errmsg);

sqlite3_free(exec_errmsg);
//sqlite3_close(db);
//exit(1);
}

/maybe discard this part 
///



const char insert_sql[] = "INSERT INTO eventlog (eventtype, 
counter) VALUES (?,?)";

sqlite3_stmt *insert_stmt = NULL;



rc = sqlite3_prepare_v2(db, insert_sql, -1, _stmt, NULL);
if(SQLITE_OK != rc) {
outfile << "Can't prepare insert statment " << insert_sql << " 
(" << rc << "): " << sqlite3_errmsg(db) << std::endl;
//fprintf(stderr, "Can't prepare insert statment %s (%i): 
%s\n", insert_sql, rc, sqlite3_errmsg(db));

sqlite3_close(db);
exit(1);
}



rc = sqlite3_bind_text(insert_stmt, 1, eventtype, 
strlen(eventtype), NULL);

if(SQLITE_OK != rc) {
outfile << "Error binding value in insert (%i): %s\n", rc, 
sqlite3_errmsg(db);
//fprintf(stderr, "Error binding value in insert (%i): %s\n", 
rc, sqlite3_errmsg(db));

sqlite3_close(db);
exit(1);
} else {
printf("Successfully bound string for insert: '%s'\n", eventtype);
}

rc = sqlite3_bind_int(insert_stmt, 2, zaehler);
if(SQLITE_OK != rc) {
outfile << "Error binding value in insert (%i): %s\n", rc, 
sqlite3_errmsg(db);
//fprintf(stderr, "Error binding value in insert (%i): %s\n", 
rc, sqlite3_errmsg(db));

sqlite3_close(db);
exit(1);
} else {
printf("Successfully bound real for insert: %d\n", zaehler);
}


rc = sqlite3_step(insert_stmt);
if(SQLITE_DONE != rc) {
outfile << "insert statement didn't return DONE (%i): %s\n", 
rc, sqlite3_errmsg(db);
//fprintf(stderr, "insert statement didn't return DONE (%i): 
%s\n", rc, sqlite3_errmsg(db));

} else {
printf("INSERT completed\n\n");
}


// start selecting ///
const char select_sql[] = "SELECT eventtype, counter FROM eventlog 
WHERE id=?";
//const char insert_sql[] = "INSERT INTO eventlog (eventtype, 
counter) VALUES (?,?)";


sqlite3_stmt *select_stmt = NULL;

// Now attempt to get that row out
sqlite3_int64 id = sqlite3_last_insert_rowid(db);
sqlite3_bind_int64(select_stmt, 1, id);


// This is your standard pattern
while(SQLITE_ROW == (rc = sqlite3_step(select_stmt))) {
int col;
printf("Found row\n");
for(col=0; col

[sqlite] Problem with SELECT in BCB

[Puma01]

I have a problem with SQLite in BCB 4.0. I wrote my own application -
I found very useful information on 
http://www.dreamincode.net/forums/index.php?showtopic=122300

On form (Form1) I have: Memo1 and Button3.

File Unit1.cpp:


//---
#include 
#pragma hdrstop
#include 
#include "Unit1.h"
//---
#pragma package(smart_init)
#pragma resource "*.dfm"
TForm1 *Form1;

// DLL handle
HANDLE DLLHandle = LoadLibrary("sqlite3.dll");

// SQLite class
Database::Database(char* filename)
{
sqlite3_open = (int (__cdecl *)(const char *, 
sqlite3**))GetProcAddress(DLLHandle,"sqlite3_open");

sqlite3_close = (int (__cdecl 
*)(sqlite3*))GetProcAddress(DLLHandle,"sqlite3_close");

sqlite3_prepare_v2 = (int (__cdecl *)(sqlite3*, const char*, int, 
sqlite3_stmt**, const char**))GetProcAddress(DLLHandle,"sqlite3_prepare_v2");

sqlite3_column_count = (int (__cdecl 
*)(sqlite3_stmt*))GetProcAddress(DLLHandle,"sqlite3_column_count");

sqlite3_step = (int (__cdecl 
*)(sqlite3_stmt*))GetProcAddress(DLLHandle,"sqlite3_step");

sqlite3_column_text = (const unsigned char (__cdecl *)(sqlite3_stmt*, 
int iCol))GetProcAddress(DLLHandle,"sqlite3_column_text");

sqlite3_finalize = (int (__cdecl 
*)(sqlite3_stmt*))GetProcAddress(DLLHandle,"sqlite3_finalize");

database = NULL;
open(filename);
}

Database::~Database()
{
}

bool Database::open(char* filename)
{
if(sqlite3_open(filename, ) == SQLITE_OK)
{
Form1->Memo1->Lines->Add("You have access to base.");
return true;
}

return false;   
}

vector Database::query(char* query)
{
sqlite3_stmt *statement;
vector results;

if(sqlite3_prepare_v2(database, query, -1, , 0) == SQLITE_OK)
{
int cols = sqlite3_column_count(statement);
int result = 0;
while(true)
{
result = sqlite3_step(statement);

if(result == SQLITE_ROW)
{
vector values;
for(int col = 0; col < cols; col++)
{

values.push_back((char*)sqlite3_column_text(statement, col));   // HERE IS 
ERROR !
}
results.push_back(values);
}
else
{
break;   
}
}

sqlite3_finalize(statement);
}
  
return results;
}

void Database::close()
{
sqlite3_close(database);
}

//---
__fastcall TForm1::TForm1(TComponent* Owner)
: TForm(Owner)
{

}
//---

void __fastcall TForm1::Button3Click(TObject *Sender)
{
Database *db;
db = new Database("Database.sqlite");
db->query("CREATE TABLE a (a INTEGER, b INTEGER);");
db->query("INSERT INTO a VALUES(1, 2);");
db->query("INSERT INTO a VALUES(5, 4);");
vector result = db->query("SELECT a, b FROM a;");
for(vector::iterator it = result.begin(); it < result.end(); 
++it)
{
vector row = *it;
cout << "Values: (A=" << row.at(0) << ", B=" << row.at(1) << ")" << 
endl;
}
db->close();
}
//---




and file Unit1.h




//---
#ifndef Unit1H
#define Unit1H
//---
#include 
#include 
#include 
#include 
#include "sqlite3.h"
#include 
#include 
//---
class TForm1 : public TForm
{
__published:// IDE-managed Components
TMemo *Memo1;
TButton *Button3;
void __fastcall Button3Click(TObject *Sender);
private:// User declarations
public:// User declarations
__fastcall TForm1(TComponent* Owner);
};
//---
extern PACKAGE TForm1 *Form1;
//---

class Database
{
public:
Database(char* filename);
~Database();

bool open(char* filename);
vector query(char* query);
void close();
sqlite3 *database;

private:
int (*sqlite3_open)(const char *, sqlite3**);
int (*sqlite3_prepare_v2)(sqlite3*, const char*, int, sqlite3_stmt**, 
const 

RE: [sqlite] Problem/Bug: "SELECT 5 / 2;" returns 2 ?

[Clark, Chris M]

> -Original Message-
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
> Sent: Thu 10/6/2005 10:14 AM
> Subject:  Re: [sqlite] Problem/Bug: "SELECT 5 / 2;" returns 2
> <[EMAIL PROTECTED]> wrote:
> > Richard, I like to ask you, just to make sure; has the `order by` 
> > "bug"/issue, as result from automatic float->int conversion, been 
> > considered by you and if yes, do you plan any changes?
> > 
>
>
> You have a simple workaround:  Just add 0.0 to anything
> that you definitely want to be floating point.
>
> I will continue to trying to figure out a reasonable method
> of addressing your concerns.

In other databases the behavior in sqlite is the expected behavior. E.g. IBM 
db2udb, Ingres, Microsoft SQL Server. Oracle is different because of the wierd 
NUMBER datatype which is used to store both ints and decimal - I don't know 
about any others.

In most databases the datatypes of the operands dictate the result type; which  
is the sqlite behavior.

Chris

Re: [sqlite] Problem/Bug: "SELECT 5 / 2;" returns 2 ?

[drh]

=?ISO-8859-1?Q?Ren=E9_Tegel?= <[EMAIL PROTECTED]> wrote:
> Hi,
> 
> Richard, I like to ask you, just to make sure; has the `order by` 
> "bug"/issue, as result from automatic float->int conversion, been 
> considered by you and if yes, do you plan any changes?
> 

I have made note of the issues raised. I do not have a
solution to the problem at this point. (The various solutions
proposed on this mailing list are all unacceptable for
various reasons.)

You have a simple workaround:  Just add 0.0 to anything
that you definitely want to be floating point.

I will continue to trying to figure out a reasonable method
of addressing your concerns.

--
D. Richard Hipp <[EMAIL PROTECTED]>

Re: [sqlite] Problem/Bug: "SELECT 5 / 2;" returns 2 ?

[René Tegel]

Hi,

Richard, I like to ask you, just to make sure; has the `order by` 
"bug"/issue, as result from automatic float->int conversion, been 
considered by you and if yes, do you plan any changes?


regards,

rene

Ralf Junker wrote:

Hello René Tegel,



May i add to that that 'order by' also seems involved, see below. This can lead to really 
unexpected errors... "order by 1.0 * b / c" as workaround solves it btw.



Thanks for pointing this out.

I am sure we will be able to come up with even more examples where the 
confusion of INTEGER / REAL numbers leads to unexpected errors.



Differating between floating point operator '/' and integer operator 'DIV' like 
some program languages do would lead to predictable output.



Surely one possible solutions, but it very likely does not conform to the SQL 
standard (but I haven't checked). On the other hand, the sqlite3 column 
affinity does not result in standard output either for the examples given in 
this thread.

Is there any chance that the problem will be addressed in the forseable future? 
Do we need to create a ticket to put it on the agenda?

Regards,

Ralf 

Re: [sqlite] Problem/Bug: "SELECT 5 / 2;" returns 2 ?

[Ralf Junker]

Hello René Tegel,

>May i add to that that 'order by' also seems involved, see below. This can 
>lead to really unexpected errors... "order by 1.0 * b / c" as workaround 
>solves it btw.

Thanks for pointing this out.

I am sure we will be able to come up with even more examples where the 
confusion of INTEGER / REAL numbers leads to unexpected errors.

>Differating between floating point operator '/' and integer operator 'DIV' 
>like some program languages do would lead to predictable output.

Surely one possible solutions, but it very likely does not conform to the SQL 
standard (but I haven't checked). On the other hand, the sqlite3 column 
affinity does not result in standard output either for the examples given in 
this thread.

Is there any chance that the problem will be addressed in the forseable future? 
Do we need to create a ticket to put it on the agenda?

Regards,

Ralf 

Re: [sqlite] Problem/Bug: "SELECT 5 / 2;" returns 2 ?

[René Tegel]

Hi,

May i add to that that 'order by' also seems involved, see below. This 
can lead to really unexpected errors... "order by 1.0 * b / c" as 
workaround solves it btw.
Differating between floating point operator '/' and integer operator 
'DIV' like some program languages do would lead to predictable output.


regards,

rene

===
#select *, b/c, 1.0 * b/c from test2 order by b/c
a   b   c   b/c 1.0 * b/c
1   3   4   0   0.75
2   5   6   0   0.833
5   6   7   0   0.857142857142857
6   6   8   0   0.75
7   7   8   0   0.875
3   7   6   1   1.17
4   6   6   1   1.0
8   8   8   1   1.0
9   9   8   1   1.125
10  10  8   1   1.25
11  9   8   1   1.125
12  8   8   1   1.0
13  11  8   1   1.375
14  13  8   1   1.625
15  12  8   1   1.5

#select *, b/c, 1.0 * b/c from test2 order by 1.0*b/c
a   b   c   b/c 1.0 * b/c
1   3   4   0   0.75
6   6   8   0   0.75
2   5   6   0   0.833
5   6   7   0   0.857142857142857
7   7   8   0   0.875
4   6   6   1   1.0
8   8   8   1   1.0
12  8   8   1   1.0
9   9   8   1   1.125
11  9   8   1   1.125
3   7   6   1   1.17
10  10  8   1   1.25
13  11  8   1   1.375
15  12  8   1   1.5
14  13  8   1   1.625




Ralf Junker wrote:

Hello DRH,


3. If the division of INTEGERs can not be stored as an INTEGER 
(i.e. if a % b != 0), the result should be returned as a REAL.




create table t1( a integer, b integer);
insert into t1 values(5,2);
update t1 set a=a/b;

If your rule above was in force, this would leave
T1.A holding 2.5, which is incompatible with the 
way other database engines work.



Well, understandable. But suppose that's exactly what one wants to do? How to 
achieve this using the current implementation of sqlite3?

On the other hand, I think that the following, currently implemented behaviour 
of sqlite3 is also incompatible with the way other database engines work. Where 
they return 2.5 for real type columns, sqlite3 does not:

  create table t1 (a real, b real);
  insert into t1 values (5,2);
  select a / b from t1;
  2

So the final question biols down to: How can I reliably guarantee a real type 
result for divisions on real typed columns even if they happen to contain 
integers? I cant't believe I always have to use a workaround like this:

  select 1.0 * a / b from t1; 


I am sure there must be a better possibility without the extra multiplication! 
Or is there not?

Regards,

Ralf  

RE: [sqlite] Problem/Bug: "SELECT 5 / 2;" returns 2 ?

[Jackson, Douglas H]

Perhaps not a solution, but a workaround:
Try coercing the data entering the table into
a value recognizable as a real:

Create trigger t_t1i after insert on t1
Begin
  Update t1 set a = 1.0 * a, b = 1.0 * b
  Where rowid = new.rowid;
End;

Create trigger t_t1u after update on t1
Begin
   Update t1 set a = 1.0 * a, b = 1.0 * b
   Where rowid = new.rowid;
End;

The table will then hold reals in all cases.

Expressions then work without change:
  Select a/b from t1;
  Update a set a = a / b;

Doug

-Original Message-
From: Ralf Junker [mailto:[EMAIL PROTECTED] 
Sent: Monday, October 03, 2005 7:12 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Problem/Bug: "SELECT 5 / 2;" returns 2 ?

Hello DRH,

>> 3. If the division of INTEGERs can not be stored as an INTEGER 
>>(i.e. if a % b != 0), the result should be returned as a REAL.
>> 
>
>create table t1( a integer, b integer);
>insert into t1 values(5,2);
>update t1 set a=a/b;
>
>If your rule above was in force, this would leave
>T1.A holding 2.5, which is incompatible with the 
>way other database engines work.

Well, understandable. But suppose that's exactly what one wants to do?
How to achieve this using the current implementation of sqlite3?

On the other hand, I think that the following, currently implemented
behaviour of sqlite3 is also incompatible with the way other database
engines work. Where they return 2.5 for real type columns, sqlite3 does
not:

  create table t1 (a real, b real);
  insert into t1 values (5,2);
  select a / b from t1;
  2

So the final question biols down to: How can I reliably guarantee a real
type result for divisions on real typed columns even if they happen to
contain integers? I cant't believe I always have to use a workaround
like this:

  select 1.0 * a / b from t1; 

I am sure there must be a better possibility without the extra
multiplication! Or is there not?

Regards,

Ralf  

Re: [sqlite] Problem/Bug: "SELECT 5 / 2;" returns 2 ?

[Ralf Junker]

Hello DRH,

>> 3. If the division of INTEGERs can not be stored as an INTEGER 
>>(i.e. if a % b != 0), the result should be returned as a REAL.
>> 
>
>create table t1( a integer, b integer);
>insert into t1 values(5,2);
>update t1 set a=a/b;
>
>If your rule above was in force, this would leave
>T1.A holding 2.5, which is incompatible with the 
>way other database engines work.

Well, understandable. But suppose that's exactly what one wants to do? How to 
achieve this using the current implementation of sqlite3?

On the other hand, I think that the following, currently implemented behaviour 
of sqlite3 is also incompatible with the way other database engines work. Where 
they return 2.5 for real type columns, sqlite3 does not:

  create table t1 (a real, b real);
  insert into t1 values (5,2);
  select a / b from t1;
  2

So the final question biols down to: How can I reliably guarantee a real type 
result for divisions on real typed columns even if they happen to contain 
integers? I cant't believe I always have to use a workaround like this:

  select 1.0 * a / b from t1; 

I am sure there must be a better possibility without the extra multiplication! 
Or is there not?

Regards,

Ralf  

RE: [sqlite] Problem/Bug: "SELECT 5 / 2;" returns 2 ?

[Fred Williams]

Anybody thought of:

t1.a = 5
t1.b = 2

select a / (b * 1.0);

I think that would return a real.

-Original Message-
From: Dennis Cote [mailto:[EMAIL PROTECTED]
Sent: Friday, September 30, 2005 11:10 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Problem/Bug: "SELECT 5 / 2;" returns 2 ?


Ralf Junker wrote:

>>This can be fixed by checking the column affinity for a value when it
is stored. If an integer value is being stored in a column with numeric
affinity, then store the value as a REAL value

...

Re: [sqlite] Problem/Bug: "SELECT 5 / 2;" returns 2 ?

[Dennis Cote]

Ralf Junker wrote:


This can be fixed by checking the column affinity for a value when it is 
stored. If an integer value is being stored in a column with numeric affinity, 
then store the value as a REAL value rather than as an INTEGER value. This will 
perform the same conversion that the other engines do, and hence produce the 
same result when the division operation is performed later.
   



Unfortunately, this is not true for SQLite3. If you execute the following SQL 
commands

 CREATE TABLE t (a REAL, b REAL);
 INSERT INTO t VALUES (5, 2);

both values are stored as INTEGER and not as REAL as one would expect by the 
column affinity. In fact, this behaviour is intentional and is stated in the 
help as a feature to minimize storage space for INTEGER numbers.

 

I'm sorry I took so long to get back to, I started this reply last 
night, but had to leave it unfinished until now.


To clarify what I said above (since it obviously wasn't clear).

The first thing to note is that SQLite applies a data type to individual 
values, and data type affinities to table columns. These are different 
things.


I meant that your division problem can be fixed in SQLite if SQLite is 
modified so that it checks the affinity of the column before storing the 
integer value. In SQLite a column declared as REAL has an affinity of 
NUMERIC (see http://www.sqlite.org/datatype3.html section 2.1). The 
problem is that currently columns with NUMERIC affinity can store values 
of any type, in particular it can store both INTEGER and REAL (see 
section 1 of the data types documentation). The required change would be 
to modify SQLite so that it would convert INTEGER values to REAL values 
when they are stored in a column with NUMERIC affinity.


This would make your division work as expected. The integer value 
supplied to the insert statement would be converted to real and stored 
as real in the table. When the select statement later retrieves the 
values to do the division, it will have real values and do floating 
point path to produce a real result.


It has some side effects that others may not like however. Storing a 
value of 5 into the table would return a value of 5.0 when selected, 
since SQLite (at least the newest versions) formats REAL values with a 
decimal point on output. It would also make the database files larger if 
they were storing integer values in a typeless column or one with a type 
that produced NUMERIC affinity (again see the data types doc for the 
rules).


If a user really wants to force values to be stored and returned as 
integers they would simply need to explicitly declare the columns to 
have an INT type, so they would be assigned an INTEGER affinity. Then no 
conversion would be done when the values are stored. To me this seems 
like an optimization for both speed and space that the user can trigger 
by explicitly specifying the column's data type.



To force storing numbers as REALs SQLite3 requires to use data binding or to 
rewrite the 2nd command above like this:

 INSERT INTO t VALUES (5.0, 2.0);

In other words, SQLite3 requires REALs to be explicitly written as such. Many users are certainly not aware of this requirement. They expect REALs to be stored as REALs just like other DB-engines do if a column is defined as such. However, SQLite3 behaves differently and this can 


* result in wrong calculations / different than intended.
* lead to data errors when importing SQL scripts from other DBMS.

On the other INTEGER side, SQLite3 again behaves differently:

 CREATE TABLE i (a INTEGER, b INTEGER);
 INSERT INTO i VALUES (5, 2);
 INSERT INTO i VALUES (5.0, 2.0);

Both INSERTs above store the numbers as INTEGERs, even those explicitly marked 
as REALs by adding the '.0' decimal.

 

This behavior is also documented on the data type page. If the REAL 
value can be represented exactly as an INTEGER, an INTEGER is stored in 
columns declared to be of type INT. If not, the REAL value is stored. 
This seems reasonable to me. It is in some ways the complement to the 
change I proposed above.


If you do this in a standard SQL engine it will silently convert the 5.0 
value to an exact value with implementation defined precision. Usually 
not a problem for 5.0, but I suspect that 5.1 will probably come out of 
the INTEGER column with the same value as 5.0 does. In SQLite, it will 
store the 5.1 value as a REAL value even though it is in a column with 
INTEGER affinity.



Another problem shows when calculations are not performed on colums with type 
affinity but just on plain numbers like in

 SELECT 5 / 2;

What exactly is intended? From the findings above, both numbers might be seen 
as INTEGERs, but if '5.0' is interpreted as an INTEGER above, '5' might just as 
well be a REAL.

Arguing from "common sense" I would prefer 5 / 2 to return 2.5 rather than 2. 
If the result cannot be represented with exact numeric, I would rather want it to be 
aproximate only. I can not overcome the feeling 

Re: [sqlite] Problem/Bug: "SELECT 5 / 2;" returns 2 ?

[Jay Sprenkle]

On 9/30/05, Ralf Junker <[EMAIL PROTECTED]> wrote:
>
> Quite right, but even with explicit typing there are cases where SQLite3
> can not be instructed to store REAL numbers as REALs:
>
> C:\>sqlite3 num.db3
> SQLite version 3.2.1
> Enter ".help" for instructions
> sqlite> CREATE TABLE i (a INTEGER, b INTEGER);
> sqlite> INSERT INTO i VALUES (5, 2);
> sqlite> SELECT a / b FROM i;
> 2
> sqlite> INSERT INTO i VALUES (5.0, 2.0);
> sqlite> SELECT a / b FROM i;
> 2
> 2
>
> Even though the 2nd insert explicitly types 5.0 and 2.0 as REALs, they are
> stored as INTEGERs internally and resuls in incorrect calculations (the very
> last number should be 2.5 and not 2).


In that case it did what it was instructed to do. The type was integer
so it converted.



Summming up: Observations show that SQLite3 behaves inconsistent when
> storing and/or calculating numbers as INTEGERs or REALs. The point I am
> personally worried about is not so much the storage side of things but the
> calculation errors resulting from it.


Since it behaves in predictable ways I don't believe inconsistency is
a big problem. Inconsistency is the only constant I've found in life!
It's only inconsistent on one operation, storing integers in a real
type. If you code with a rule of thumb to always insert
with something like "insert into t values( round(x) );" then you
will have no problems.


I would like to propose the resolve this inconsistency as follows:
>
>
That would make sqlite math inconsistent with math as performed
by computer languages. I don't see that as an improvement.


---
The Castles of Dereth Calendar: a tour of the art and architecture of
Asheron's Call
http://www.lulu.com/content/77264

Re: [sqlite] Problem/Bug: "SELECT 5 / 2;" returns 2 ?

[Gerry Snyder]

Ralf Junker wrote:



Summming up: Observations show that SQLite3 behaves inconsistent when storing 
and/or calculating numbers as INTEGERs or REALs. The point I am personally 
worried about is not so much the storage side of things but the calculation 
errors resulting from it.

I would like to propose the resolve this inconsistency as follows:



3. If the division of INTEGERs can not be stored as an INTEGER (i.e. if a % b 
!= 0), the result should be returned as a REAL.



But this would break much, much code that expects integers to be treated 
as integers. This can't be right. Implementation would require, for 
every integer calculation, conversion to REAL, performing the 
calculation, and checking whether the result is an integer (within some 
tolerence). This suggestion won't float.


Gerry
--
--
Gerry Snyder
American Iris Society Director, Symposium Chair
in warm, winterless Los Angeles -- USDA zone 9b, Sunset 18-19

Re: [sqlite] Problem/Bug: "SELECT 5 / 2;" returns 2 ?

[drh]

Ralf Junker <[EMAIL PROTECTED]> wrote:
> 
> 3. If the division of INTEGERs can not be stored as an INTEGER 
>(i.e. if a % b != 0), the result should be returned as a REAL.
> 

create table t1( a integer, b integer);
insert into t1 values(5,2);
update t1 set a=a/b;

If your rule above was in force, this would leave
T1.A holding 2.5, which is incompatible with the 
way other database engines work.
--
D. Richard Hipp <[EMAIL PROTECTED]>

Re: [sqlite] Problem/Bug: "SELECT 5 / 2;" returns 2 ?

[Ralf Junker]

Hello Jay Sprenkle,

>I guess if you're going to use sqlite you're going to have to
>force typing explicitly if you're doing math with the sql engine. 

Quite right, but even with explicit typing there are cases where SQLite3 can 
not be instructed to store REAL numbers as REALs:

  C:\>sqlite3 num.db3
  SQLite version 3.2.1
  Enter ".help" for instructions
  sqlite> CREATE TABLE i (a INTEGER, b INTEGER);
  sqlite> INSERT INTO i VALUES (5, 2);
  sqlite> SELECT a / b FROM i;
  2
  sqlite> INSERT INTO i VALUES (5.0, 2.0);
  sqlite> SELECT a / b FROM i;
  2
  2

Even though the 2nd insert explicitly types 5.0 and 2.0 as REALs, they are 
stored as INTEGERs internally and resuls in incorrect calculations (the very 
last number should be 2.5 and not 2).

Summming up: Observations show that SQLite3 behaves inconsistent when storing 
and/or calculating numbers as INTEGERs or REALs. The point I am personally 
worried about is not so much the storage side of things but the calculation 
errors resulting from it.

I would like to propose the resolve this inconsistency as follows:

1. If two REALs are divided, the result should be a REAL. This is the current 
behaviour of SQLite3.

2. If two INTEGERs are divided, the result should be INTEGER only if it can be 
represented as an INTEGER (i.e. if a % b = 0).

3. If the division of INTEGERs can not be stored as an INTEGER (i.e. if a % b 
!= 0), the result should be returned as a REAL.

I do understand that 3. implies a change in numeric type, but in oder to 
perform calculations with SQL the type change seems unavoidable, at least as 
long as SQLite3 implements only 2 of the 3 numeric types suggested in this 
thread.

Regards,

Ralf 

Re: [sqlite] Problem/Bug: "SELECT 5 / 2;" returns 2 ?

[Ralf Junker]

The following table sums up the division findings from other SQL engines:

   |SELECT 5 / 2 | SELECT 5.0 / 2 | SELECT 5 / 2.0 | SELECT 5.0 / 2.0
--
SQLite3| 2   | 2.5| 2.5| 2.5 
PostgreSQL | 2   | 2.5000 | 2.5000 |
SQL*Plus   | 2.5 |||
MySql  | 2.50|||

The implementation seems undecided, but I see that the "big players" Oracle 
SQL*Plus and MySql both do not strip off the decimal digits.

Thanks to all who run some tests and provided the results. Feel free to add 
other engines or fill in the gaps.

Regards,

Ralf 

Re: [sqlite] Problem/Bug: "SELECT 5 / 2;" returns 2 ?

[Clark Christensen]

SQL*Plus: Release 8.1.7.0.0 - Production on Thu Sep 29 16:03:55 2005
 
 (c) Copyright 2000 Oracle Corporation.  All rights reserved.
 
 
 Connected to:
 Oracle8i Enterprise Edition Release 8.1.6.0.0 - Production
 With the Partitioning option
 JServer Release 8.1.6.0.0 - Production
 
 SQL> select 5/2 from dual;
 
5/2
 --
2.5
 
 SQL>

- Original Message 
From: Dennis Jenkins <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Thursday, September 29, 2005 13:14:48
Subject: Re: [sqlite] Problem/Bug: "SELECT 5 / 2;" returns 2 ?


>Just out of curiosity: Maybe someone can report the result of 'SELECT 5 / 2;' 
>on MySql, SQLServer, and others?
>
>  
>
devl=# SELECT 5 / 2;
 ?column?
--
2
(1 row)

devl=# select 5.0 / 2;
  ?column?

 2.5000
(1 row)

devl=# select 5 / 2.0;
  ?column?

 2.5000
(1 row)

devl=# select version();
version

 PostgreSQL 8.0.3 on i386-portbld-freebsd5.4, compiled by GCC cc (GCC) 
3.4.2 [FreeBSD] 20040728
(1 row)

Re: [sqlite] Problem/Bug: "SELECT 5 / 2;" returns 2 ?

[Alexander J. Kozlovsky]

> Unfortunately, this is not true for SQLite3. If you execute the following SQL 
> commands
> 
>   CREATE TABLE t (a REAL, b REAL);
>   INSERT INTO t VALUES (5, 2);
>
> both values are stored as INTEGER and not as REAL as one would expect
> by the column affinity. In fact, this behaviour is intentional and is
> stated in the help as a feature to minimize storage space for INTEGER
> numbers.

I think, it can be fixed by introducing special internal data type:
REAL_STORED_AS_INTEGER

If table column have REAL type, but inserted value is INTEGER, then
inserted type becomes REAL_STORED_AS_INTEGER and binary representation
keep small. When column value is extracted from SQLite or used in
SQL expression, then value dynamically converted to REAL.

That is, the user of database never see values of this type directly.
Such values stored as INTEGER, but can be seen only as REAL.


Best regards,
 Alexandermailto:[EMAIL PROTECTED]

Re: [sqlite] Problem/Bug: "SELECT 5 / 2;" returns 2 ?

[Jay Sprenkle]

On 9/29/05, Ralf Junker <[EMAIL PROTECTED]> wrote:
>
> Unfortunately, this is not true for SQLite3. If you execute the following
> SQL commands
>
> CREATE TABLE t (a REAL, b REAL);
> INSERT INTO t VALUES (5, 2);
>
> both values are stored as INTEGER and not as REAL as one would expect by
> the column affinity. In fact, this behaviour is intentional and is stated in
> the help as a feature to minimize storage space for INTEGER numbers.
>

D:\temp\convention>sqlite3 num.db
SQLite version 3.0.8
Enter ".help" for instructions
sqlite> CREATE TABLE t (a REAL, b REAL);
sqlite> INSERT INTO t VALUES (5, 2);
sqlite> select a/b from t;
2
sqlite> insert into t values(5.0,2);
sqlite> select a/b from t;
2
2.5
sqlite>

I guess if you're going to use sqlite you're going to have to
force typing explicitly if you're doing math with the sql engine.

Re: [sqlite] Problem/Bug: "SELECT 5 / 2;" returns 2 ?

[Jay Sprenkle]

On 9/29/05, Ralf Junker <[EMAIL PROTECTED]> wrote:
>
> Another problem shows when calculations are not performed on colums with
> type affinity but just on plain numbers like in
>
> SELECT 5 / 2;
>
> What exactly is intended? From the findings above, both numbers might be
> seen as INTEGERs, but if '5.0' is interpreted as an INTEGER above, '5'
> might just as well be a REAL.
>

As per almost every interpreted language standard

5 is an integer.

5.0 is a float.

As you proved with your own example 5.0 is not interpreted as an integer.

Re: [sqlite] Problem/Bug: "SELECT 5 / 2;" returns 2 ?

[Lawrence Chitty]

Ralf Junker wrote:


In risk of asking the obvious, I wonder if the following division should be 
considered correct:

 | Query | Result Value | Result Type| OK?
---
1 | SELECT 5 / 2; | 2| SQLITE_INTEGER | No?
 

I am following the rest of this thread, but just wanted to add that the 
following may be useful as a workaround for your situation.


SELECT  ROUND(5) / 2
2.5

Regards

Lawrence

Re: [sqlite] Problem/Bug: "SELECT 5 / 2;" returns 2 ?

[Dennis Jenkins]

Just out of curiosity: Maybe someone can report the result of 'SELECT 5 / 2;' 
on MySql, SQLServer, and others?

 


devl=# SELECT 5 / 2;
?column?
--
   2
(1 row)

devl=# select 5.0 / 2;
 ?column?

2.5000
(1 row)

devl=# select 5 / 2.0;
 ?column?

2.5000
(1 row)

devl=# select version();
   version

PostgreSQL 8.0.3 on i386-portbld-freebsd5.4, compiled by GCC cc (GCC) 
3.4.2 [FreeBSD] 20040728

(1 row)

Re: [sqlite] Problem/Bug: "SELECT 5 / 2;" returns 2 ?

[Ralf Junker]

Hello Dennis & Jay,

thanks for your detailed answers. I do understand your arguments and they make 
good sense for typed DB engines, but for the case of SQLite3 I dare to differ.

>This can be fixed by checking the column affinity for a value when it is 
>stored. If an integer value is being stored in a column with numeric affinity, 
>then store the value as a REAL value rather than as an INTEGER value. This 
>will perform the same conversion that the other engines do, and hence produce 
>the same result when the division operation is performed later.

Unfortunately, this is not true for SQLite3. If you execute the following SQL 
commands

  CREATE TABLE t (a REAL, b REAL);
  INSERT INTO t VALUES (5, 2);

both values are stored as INTEGER and not as REAL as one would expect by the 
column affinity. In fact, this behaviour is intentional and is stated in the 
help as a feature to minimize storage space for INTEGER numbers.

To force storing numbers as REALs SQLite3 requires to use data binding or to 
rewrite the 2nd command above like this:

  INSERT INTO t VALUES (5.0, 2.0);

In other words, SQLite3 requires REALs to be explicitly written as such. Many 
users are certainly not aware of this requirement. They expect REALs to be 
stored as REALs just like other DB-engines do if a column is defined as such. 
However, SQLite3 behaves differently and this can 

 * result in wrong calculations / different than intended.
 * lead to data errors when importing SQL scripts from other DBMS.

On the other INTEGER side, SQLite3 again behaves differently:

  CREATE TABLE i (a INTEGER, b INTEGER);
  INSERT INTO i VALUES (5, 2);
  INSERT INTO i VALUES (5.0, 2.0);

Both INSERTs above store the numbers as INTEGERs, even those explicitly marked 
as REALs by adding the '.0' decimal.

Another problem shows when calculations are not performed on colums with type 
affinity but just on plain numbers like in

  SELECT 5 / 2;

What exactly is intended? From the findings above, both numbers might be seen 
as INTEGERs, but if '5.0' is interpreted as an INTEGER above, '5' might just as 
well be a REAL.

Arguing from "common sense" I would prefer 5 / 2 to return 2.5 rather than 2. 
If the result cannot be represented with exact numeric, I would rather want it 
to be aproximate only. I can not overcome the feeling that exact numeric is 
sometimes simply wrong, especially with the whole family of financial 
calculations.

Just out of curiosity: Maybe someone can report the result of 'SELECT 5 / 2;' 
on MySql, SQLServer, and others?

Regards,

Ralf 

Re: [sqlite] Problem/Bug: "SELECT 5 / 2;" returns 2 ?

[Dennis Cote]

Ralf Junker wrote:


In risk of asking the obvious, I wonder if the following division should be 
considered correct:

 | Query | Result Value | Result Type| OK?
---
1 | SELECT 5 / 2; | 2| SQLITE_INTEGER | No?
2 | SELECT 5.0 / 2;   | 2.5  | SQLITE_FLOAT   | Yes
3 | SELECT 5 / 2.0;   | 2.5  | SQLITE_FLOAT   | Yes
4 | SELECT 5.0 / 2.0; | 2.5  | SQLITE_FLOAT   | Yes

The query in question is Query 1. Is the returned integer result correct or 
should it not better return the 2.5 float value instead?

I understand that this probably boils down to integer arithmetics, but the 
decimals dropping can cause severe rounding errors if SQLite stores an integer 
number without further warning like in:

 CREATE TABLE t (a REAL, b REAL);
 INSERT INTO t VALUES (5, 2);

Then the query

 SELECT a / b FROM t;

returns wrong results, even though both colums are declared as REAL.

In my opinion, any division which can not be represented as an integer should 
return a float value.

What do you think?

Regards,

Ralf


 


Ralf,

The following is from section 6.26  of the 
SQL:1999 standard.


1) If the declared type of both operands of a dyadic arithmetic operator 
is exact numeric, then the
declared type of the result is exact numeric, with precision and scale 
determined as follows:

a) Let S1 and S2 be the scale of the first and second operands respectively.
b) The precision of the result of addition and subtraction is 
implementation-defined, and the

scale is the maximum of S1 and S2.
c) The precision of the result of multiplication is 
implementation-defined, and the scale is S1+

S2.
d) The precision and scale of the result of division is 
implementation-defined.
2) If the declared type of either operand of a dyadic arithmetic 
operator is approximate numeric,
then the declared type of the result is approximate numeric. The 
precision of the result is

implementation-defined.

As you can see, the result of exact (integer) division is also exact 
(integer) with implementation defined precision and scale.  The result 
of an expression containing approximate (floating point) values is 
approximate (floating point). So SQLite is conforming to the SQL standard.


The problem is that in SQLite, individual rows can store a column value 
using a data type that is different than the column was declared to 
hold. Expressions using these values will then be done using different 
math (i.e. integer vs float) for some rows.


This exposes a difference between SQLite and other database engines with 
strict data typing. I suspect that your example will behave differently 
on mySQL, Oracle, et. al. than it does on SQLite. With strict typing 
these engines will always store the values using the declared type and 
always use the type of math associated with the declared types. 
Effectively 5 and 2 are converted to 5.0 and 2.0 when they are stored 
into the row. So the same operation is performed for all rows. This 
isn't true for SQLite.


This can be fixed by checking the column affinity for a value when it is 
stored. If an integer value is being stored in a column with numeric 
affinity, then store the value as a REAL value rather than as an INTEGER 
value. This will perform the same conversion that the other engines do, 
and hence produce the same result when the division operation is 
performed later.


Note, this change will have the side effect of making database files 
slightly larger, and may slow down operations with untyped columns 
because they default to numeric affinity. All integer values stored in 
these columns will be converted to REAL values and all math will be done 
with floating point (which may require library calls for some embedded 
applications without an FPU). However all this can be avoided by simply 
declaring these column to be integer typed. This will set the affinity 
correctly, so no conversions are done. It probably makes sense to 
declare the type of columns explicitly for increased performance (much 
like the integer primary key declaration) as long as the untyped version 
still produces the correct results.


Dennis Cote

Re: [sqlite] Problem/Bug: "SELECT 5 / 2;" returns 2 ?

[Jay Sprenkle]

In the C language, which sqlite is written in, performs math this way:
If mixing types the operands are converted to the most precise type and the
operation
evaluated.

=> SELECT 5 / 2;
is: integer operation integer
the most precise type is integer, so it's strictly integer math.
evaluated as integer / integer = integer result

=> SELECT 5 / 2.0;
is: integer operation float
the most precise type is float
evaluated as float / float = float result

It's performing as I expected based on my knowledge of C.


On 9/29/05, Ralf Junker <[EMAIL PROTECTED]> wrote:
>
> In risk of asking the obvious, I wonder if the following division should
> be considered correct:
>
> | Query | Result Value | Result Type | OK?
> ---
> 1 | SELECT 5 / 2; | 2 | SQLITE_INTEGER | No?
> 2 | SELECT 5.0 / 2; | 2.5 | SQLITE_FLOAT | Yes
> 3 | SELECT 5 / 2.0; | 2.5 | SQLITE_FLOAT | Yes
> 4 | SELECT 5.0 / 2.0; | 2.5 | SQLITE_FLOAT | Yes
>
> The query in question is Query 1. Is the returned integer result correct
> or should it not better return the 2.5 float value instead?
>

[sqlite] problem with SELECT * when using multiple FROM tables

[Radek Hulán]

When working on a DB layer, and migrating a project from MySQL to SQLite, I've noticed 
that this is ok with MySQL:

CREATE TABLE b (
afield1 integer,
afield2 integer);

CREATE TABLE b (
bfield1 integer,
bfield2 integer);

$query = sqlite_query('SELECT * FROM a, b WHERE afield1=bfield1',$db);
and/or
$query = sqlite_query('SELECT * FROM a a, b b WHERE a.afield1=b.bfield1',$db);
$row = sqlite_fetch_array($query,ářčáářSQLITE_ASSOC);
echo $row['afield1];

But it is not ok with SQLite. It does not work. Only fully defined fields, no 
wildcards, work with SQLite, using PHP 5.0.2:

SELECT 
  a.afield1 as afield1,
  a.afield2 as afield2,
  b.afield1 as bfield1,
  b.afield2 as bfield2
FROM a a, b b
WHERE a.afield1=b.bfield1

Is this supposed to be a bug, or a "feature"?


Radek Hulan
  web: http://hulan.info/
  web: http://blogcms.com/