Re: [sqlite] ROWID bug in SELECT with JOIN?

[BareFeet]

On 26/09/2009, at 7:35 AM, sub sk79 wrote:

> Select with two tables joined has no _rowid_ column in select columns.
> I think this is a bug - just a quick check with the community before
> filing a bug report.
>
> select * from names, friends where first_name = fname and _rowid_ < 2;
> SQL error: no such column: _rowid_

select *

by default does not include the rowid column. You need to request it  
specifically, like this:

select rowid, *

If you are joining tables in your select, you need to specify which  
rowid you want in the output, such as:

select names.rowid as rowid, * from names, friends...

Beware that this can return multiple rows with the same rowid, if a  
name has more than one friend.

Tom
BareFeet

  --
Comparison of SQLite GUI applications:
http://www.tandb.com.au/sqlite/compare/?ml

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

Re: [sqlite] ROWID bug in SELECT with JOIN?

[Igor Tandetnik]

sub sk79  wrote:
> select *  from (select * from names, friends where first_name = fname)
> where _rowid_ < 2;
>
> Above query works - which _rowid_ is this accessing?

Well, define "works". In my experiments, _rowid_ is NULL for all rows in 
the subselect:

select typeof(_rowid_) from (select * from names, friends where 
first_name = fname);

But the fact that this statement compiles at all looks like a bug to me 
(though a very minor and unimportant one).


Now, why do you care about these corner cases? Do you have a practical 
problem you are trying to solve?

Igor Tandetnik 



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

Re: [sqlite] ROWID bug in SELECT with JOIN?

[sub sk79]

select *  from (select * from names, friends where first_name = fname)
 where _rowid_ < 2;

Above query works - which _rowid_ is this accessing?
Since the query works I get an impression the rowid is attached with the
result-set returned by a select?

-sk


On Fri, Sep 25, 2009 at 6:07 PM, Igor Tandetnik  wrote:
> sub sk79  wrote:
>> Select with two tables joined has no _rowid_ column in select columns.
>> I think this is a bug - just a quick check with the community before
>> filing a bug report.
>>
>> select * from names, friends where first_name = fname and _rowid_ < 2;
>> SQL error: no such column: _rowid_
>
> So, which table's _rowid_ is this statement supposed to access? The
> statement is wrong and shouldn't run. The only issue here is that the
> error message is somewhat misleading - it could probably say something
> like "ambiguous column name".
>
> Igor Tandetnik
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] ROWID bug in SELECT with JOIN?

[Igor Tandetnik]

sub sk79  wrote:
> Select with two tables joined has no _rowid_ column in select columns.
> I think this is a bug - just a quick check with the community before
> filing a bug report.
>
> select * from names, friends where first_name = fname and _rowid_ < 2;
> SQL error: no such column: _rowid_

So, which table's _rowid_ is this statement supposed to access? The 
statement is wrong and shouldn't run. The only issue here is that the 
error message is somewhat misleading - it could probably say something 
like "ambiguous column name".

Igor Tandetnik 



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