Re: [sqlite] Bug? sqlite3_column_name returns name of PK for rowid

[Martin Kleusberg]

Thank you all for your replies! Good to hear that this actually is sort of a 
sane behaviour. I think I wasn't fully aware of the fact that the primary key 
and the rowid column actually are *exactly* the same - it does make sense that 
way though.

Thankfully I'm not relying on the column names but was working on a SQLite GUI 
and wondered why I got the same column name twice after executing that SELECT 
statement. It is good to know, however, better not to start relying on the 
column names at all; so this is definitely a valuable piece of advice I got 
here :)

 Martin



On Thursday 03 July 2014 23:22:46 Martin Kleusberg wrote:
> Hi everybody,
> 
> I've encountered some odd behaviour when using the sqlite3_column_name
> function. Here's my attempt to build a minimal example.
> 
> Part 1: The database. Note that the table is not a 'without rowid' table and
> has a primary key:
> 
> $ sqlite3 test.db
> create table test(id integer primary key, bla integer);
> insert into test(bla) values(5);
> .quit
> 
> Part 2: A C program using the sqlite3_column_name function to determine the
> column names of the result set of a statement:
> 
> #include 
> #include "sqlite3.h"
> 
> int main()
> {
>   sqlite3* db;
>   if(sqlite3_open_v2("test.db", , SQLITE_OPEN_READONLY, NULL))
>   return 1;
> 
>   sqlite3_stmt* stmt;
>   int status = sqlite3_prepare_v2(db,
>   "SELECT rowid,* 
> FROM test",
>   -1,
>   ,
>   NULL);
>   if(status == SQLITE_OK)
>   {
>   status = sqlite3_step(stmt);
>   int columns = sqlite3_data_count(stmt);
>   int i;
>   for(i=0;i   printf("column #%d: %s\n", i+1, 
> sqlite3_column_name(stmt, i));
>   } else {
>   return 2;
>   }
>   sqlite3_finalize(stmt);
> 
>   sqlite3_close(db);
>   return 0;
> }
> 
> The output of this program is:
> column #1: id
> column #2: id
> column #3: bla
> 
> However, I'd have expected the following:
> column #1: rowid
> column #2: id
> column #3: bla
> 
> I've tested this using the latest version of SQLite, i.e. 3.8.5, and did a
> (admittedly very quick) search but couldn't find anything.
> 
> If there's any required information I didn't provide or any sane explanation
> please let me know :)
> 
> Cheers
> Martin
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug? sqlite3_column_name returns name of PK for rowid

[Tim Streater]

On 04 Jul 2014 at 15:35, Simon Slavin  wrote: 

> On 4 Jul 2014, at 12:28pm, Tim Streater  wrote:
>
>> On 04 Jul 2014 at 11:43, Simon Slavin  wrote:
>>
>>> On 3 Jul 2014, at 10:22pm, Martin Kleusberg  wrote:
>>>
 I've encountered some odd behaviour when using the sqlite3_column_name
 function.
>>>
>>> Sorry, but column names are guaranteed only if you use an 'AS' clause in
>>> your SELECT command.  For every other situation, there's no telling what
>>> you'll get. [snip]
>>
>> Hum. Does this apply using PHP to interface to SQLite as follows:
>>
>>  $res = $dbh->query ('SELECT fred FROM MyTable');
>>  $reg = $res->fetchArray (SQLITE3_ASSOC);
>>  $myvar = $reg['fred'];
>
> I'm afraid so.  You should bear this in mind for any future programming. 
> Though as Hick noted, if something works in a particular way now, it will
> probably continue to work for the future.  So you may not need to rush around
> converting all your old code.
>
> Using ->fetchArray() can definitely be a problem with "SELECT * ...".  And
> it's also a problem if you convert SQL rows to objects and want to refer to a
> SQL column value as an object property (sometimes called 'variables' in PHP).

Well so far it works just fine in the simple case. I'd have been a mite unhappy 
if it didn't, given that my code snippet above is what the PHP docs imply is 
what one does. For COUNT (*) I use AS, and also when there are ATTACHed 
databases in the picture. But then I'd expect to in these cases.



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

Re: [sqlite] Bug? sqlite3_column_name returns name of PK for rowid

[Kees Nuyt]

On Thu, 03 Jul 2014 23:22:46 +0200, Martin Kleusberg
 wrote:

> I've encountered some odd behaviour when using the sqlite3_column_name 
> function. Here's my attempt to build a minimal example.
>
> Part 1: The database. Note that the table is not a 'without rowid' table and 
> has a primary key:
>
> $ sqlite3 test.db
> create table test(id integer primary key, bla integer);
> insert into test(bla) values(5);
> .quit
>
> Part 2: A C program using the sqlite3_column_name function
> to determine the column names of the result set of a statement:

[snip]

>  "SELECT rowid,* FROM test",

[snip]

> The output of this program is:
> column #1: id
> column #2: id
> column #3: bla
>
> However, I'd have expected the following:
> column #1: rowid
> column #2: id
> column #3: bla
>
> I've tested this using the latest version of SQLite, i.e. 3.8.5, and did a 
> (admittedly very quick) search but couldn't find anything.
>
> If there's any required information I didn't provide or any
> sane explanation please let me know :)

My explanation:
By stating id INTEGER PRIMARY KEY, you aliased ROWID to id.
In this case there is no separate ROWID column, id takes its place.

Whenever you use ROWID for this table, you will get the value of id,
in the internal schema stucture, only one name can be stored, and a
separate (implicit, not aliased) ROWID column will be hidden, yet
accessible.

Without INTEGER PRIMARY KEY, the ROWID is still there, but it is hidden.
Check it with PRAGMA table_info(test);
You will see the id column, never a ROWID column.

I think this behaviour is consistent with
http://sqlite.org/lang_createtable.html#rowid
, and the naming you observe reminds you of the role of id in the table.

-- 
Groet, Cordialement, Pozdrawiam, Regards,

Kees Nuyt

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

Re: [sqlite] Bug? sqlite3_column_name returns name of PK for rowid

[Simon Slavin]

On 4 Jul 2014, at 12:28pm, Tim Streater  wrote:

> On 04 Jul 2014 at 11:43, Simon Slavin  wrote: 
> 
>> On 3 Jul 2014, at 10:22pm, Martin Kleusberg  wrote:
>> 
>>> I've encountered some odd behaviour when using the sqlite3_column_name
>>> function.
>> 
>> Sorry, but column names are guaranteed only if you use an 'AS' clause in your
>> SELECT command.  For every other situation, there's no telling what you'll
>> get.[snip]
> 
> Hum. Does this apply using PHP to interface to SQLite as follows:
> 
>  $res = $dbh->query ('SELECT fred FROM MyTable');
>  $reg = $res->fetchArray (SQLITE3_ASSOC);
>  $myvar = $reg['fred'];

I'm afraid so.  You should bear this in mind for any future programming.  
Though as Hick noted, if something works in a particular way now, it will 
probably continue to work for the future.  So you may not need to rush around 
converting all your old code.

Using ->fetchArray() can definitely be a problem with "SELECT * ...".  And it's 
also a problem if you convert SQL rows to objects and want to refer to a SQL 
column value as an object property (sometimes called 'variables' in PHP).

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

Re: [sqlite] Bug? sqlite3_column_name returns name of PK for rowid

[RSmith]

On 2014/07/03 23:22, Martin Kleusberg wrote:

Hi everybody,

I've encountered some odd behaviour when using the sqlite3_column_name
function. Here's my attempt to build a minimal example.

Part 1: The database. Note that the table is not a 'without rowid' table and
has a primary key:


The output of this program is:
column #1: id
column #2: id
column #3: bla

However, I'd have expected the following:
column #1: rowid
column #2: id
column #3: bla


Other answers describe the notion of column name accuracy and is valid in the regard and moreover essentially why it ISN'T a bug at 
all, so I will just quickly elaborate on WHY SQLite is showing you what it does:


When you make a column in a table that is defined as "INTEGER PRIMARY KEY" it becomes immediately an alias for the rowid (it has 
other aliases even) and so there are not 2 physical columns or datasets, but just 1 and now that 1 is named: "id".


SQLite returns what it has inside the table based on what you asked for, but there is no reason it should or would return the exact 
name of what you asked for, just the exact data, and since it now knows that column as "id" there is no reason why it should return 
it as anything else - unless of course, you added a specification for what you wish it to return using "AS", which changes things 
dramatically and now SQLite (or any SQL engine really) is bound by law to return it exactly "AS" requested.


The fact that it returns the real column name mostly is a convention in SQLite and most other Engines and one that can be trusted 
mostly, if it tests correct today, it should do the same tomorrow. But if ever you need a guaranteed specific name for a column 
returned, i.e. id the data destination depends on that name being exact, then you should always specify the AS heading.



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

Re: [sqlite] Bug? sqlite3_column_name returns name of PK for rowid

[Hick Gunter]

Of course it does. The good news is that SQLite tends to stick with whatever it 
comes up with first unless there is a significant change to the query.

-Ursprüngliche Nachricht-
Von: Tim Streater [mailto:t...@clothears.org.uk]
Gesendet: Freitag, 04. Juli 2014 13:28
An: General Discussion of SQLite Database
Betreff: Re: [sqlite] Bug? sqlite3_column_name returns name of PK for rowid

On 04 Jul 2014 at 11:43, Simon Slavin <slav...@bigfraud.org> wrote:

> On 3 Jul 2014, at 10:22pm, Martin Kleusberg <mkleusb...@gmail.com> wrote:
>
>> I've encountered some odd behaviour when using the
>> sqlite3_column_name function.
>
> Sorry, but column names are guaranteed only if you use an 'AS' clause
> in your SELECT command.  For every other situation, there's no telling
> what you'll get.  For instance
>
> SELECT fred FROM MyTable
>
> may return with a column name of 'fred' or 'main.fred'.
>
> If you're using column names in your programming, always do
>
> SELECT fred AS fred FROM MyTable

Hum. Does this apply using PHP to interface to SQLite as follows:

  $res = $dbh->query ('SELECT fred FROM MyTable');
  $reg = $res->fetchArray (SQLITE3_ASSOC);
  $myvar = $reg['fred'];

I hope not. That's a lot of queries to change.



--
Cheers  --  Tim


---
Gunter Hick
Software Engineer

Scientific Games International GmbH
Klitschgasse 2 – 4, A - 1130 Vienna,
Austria
FN 157284 a, HG Wien
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This e-mail is confidential and may well also be legally privileged. If you 
have received it in error, you are on notice as to its status and accordingly 
please notify us immediately by reply e-mail and then
delete this message from your system. Please do not copy it or use it for any 
purposes, or disclose its contents to any person as to do so could be a breach 
of confidence. Thank you for your cooperation.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug? sqlite3_column_name returns name of PK for rowid

[Tim Streater]

On 04 Jul 2014 at 11:43, Simon Slavin  wrote: 

> On 3 Jul 2014, at 10:22pm, Martin Kleusberg  wrote:
>
>> I've encountered some odd behaviour when using the sqlite3_column_name
>> function.
>
> Sorry, but column names are guaranteed only if you use an 'AS' clause in your
> SELECT command.  For every other situation, there's no telling what you'll
> get.  For instance
>
> SELECT fred FROM MyTable
>
> may return with a column name of 'fred' or 'main.fred'.
>
> If you're using column names in your programming, always do
>
> SELECT fred AS fred FROM MyTable

Hum. Does this apply using PHP to interface to SQLite as follows:

  $res = $dbh->query ('SELECT fred FROM MyTable');
  $reg = $res->fetchArray (SQLITE3_ASSOC);
  $myvar = $reg['fred'];

I hope not. That's a lot of queries to change.



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

Re: [sqlite] Bug? sqlite3_column_name returns name of PK for rowid

[Simon Slavin]

On 3 Jul 2014, at 10:22pm, Martin Kleusberg  wrote:

> I've encountered some odd behaviour when using the sqlite3_column_name 
> function.

Sorry, but column names are guaranteed only if you use an 'AS' clause in your 
SELECT command.  For every other situation, there's no telling what you'll get. 
 For instance

SELECT fred FROM MyTable

may return with a column name of 'fred' or 'main.fred'.

If you're using column names in your programming, always do

SELECT fred AS fred FROM MyTable

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