sqlite3_column_name() issue with views

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

sqlite3_column_name() issue with views

Charles Leifer
I was surprised to find that, depending on whether I queried a table or a
view, sqlite3_column_name would return different values for the column
name. Specifically, when the table is aliased and the columns in the SELECT
clause are quoted, sqlite returns an unquoted, unaliased column name, e.g. "
t1.name" -> name. When querying a view, though, sqlite will return the
quoted, aliased column name, e.g. "t1.name" -> "t1.name". Does this qualify
as a bug, or is this intended behavior?

I observed this with sqlite versions 3.8.2, 3.7.13 and 3.7.9.

Here is a short python script exemplifying the behavior:

import sqlite3
conn = sqlite3.connect(':memory:')
cursor = conn.cursor()

cursor.execute('create table people (name);')
cursor.execute('insert into people values (?)', ('charlie',))

cursor.execute('select t1.name from people as t1')
print cursor.description
# (('name', None, None, None, None, None, None),)

cursor.execute('create view people_view as select * from people')
cursor.execute('select t1.name from people_view as t1')
print cursor.description
# (('t1.name', None, None, None, None, None, None),)

Thanks in advance,

Charlie
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: sqlite3_column_name() issue with views

Richard Hipp-3
On Sun, Feb 2, 2014 at 12:23 PM, Charles Leifer <[hidden email]> wrote:

> I was surprised to find that, depending on whether I queried a table or a
> view, sqlite3_column_name would return different values for the column
> name. Specifically, when the table is aliased and the columns in the SELECT
> clause are quoted, sqlite returns an unquoted, unaliased column name, e.g.
> "
> t1.name" -> name. When querying a view, though, sqlite will return the
> quoted, aliased column name, e.g. "t1.name" -> "t1.name". Does this
> qualify
> as a bug, or is this intended behavior?
>

There is no "intended" behavior.
http://www.sqlite.org/c3ref/mark/column_name.html?If+there+is+n*fied

If the result of sqlite3_column_name is important, please us an AS clause
to set it to exactly what you want.


>
> I observed this with sqlite versions 3.8.2, 3.7.13 and 3.7.9.
>
> Here is a short python script exemplifying the behavior:
>
> import sqlite3
> conn = sqlite3.connect(':memory:')
> cursor = conn.cursor()
>
> cursor.execute('create table people (name);')
> cursor.execute('insert into people values (?)', ('charlie',))
>
> cursor.execute('select t1.name from people as t1')
> print cursor.description
> # (('name', None, None, None, None, None, None),)
>
> cursor.execute('create view people_view as select * from people')
> cursor.execute('select t1.name from people_view as t1')
> print cursor.description
> # (('t1.name', None, None, None, None, None, None),)
>
> Thanks in advance,
>
> Charlie
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: sqlite3_column_name() issue with views

Stephan Beal-3
On Tue, Feb 4, 2014 at 2:52 AM, Richard Hipp <[hidden email]> wrote:

> There is no "intended" behavior.
> http://www.sqlite.org/c3ref/mark/column_name.html?If+there+is+n*fied


i assume the broken image at the top of that page qualifies as "unintended
behaviour" ;)

http://www.sqlite.org/c3ref/images/sqlite370_banner.gif

seems to be 404.

--
----- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: sqlite3_column_name() issue with views

Richard Hipp-3
On Tue, Feb 4, 2014 at 11:34 AM, Stephan Beal <[hidden email]> wrote:

> On Tue, Feb 4, 2014 at 2:52 AM, Richard Hipp <[hidden email]> wrote:
>
> > There is no "intended" behavior.
> > http://www.sqlite.org/c3ref/mark/column_name.html?If+there+is+n*fied
>
>
> i assume the broken image at the top of that page qualifies as "unintended
> behaviour" ;)
>
> http://www.sqlite.org/c3ref/images/sqlite370_banner.gif
>
> seems to be 404.
>
>
The http://www.sqlite.org/c3ref/mark URL is a CGI program that adds the
highlight to a single sentence in the documentation.  But in so doing, it
changes path to the document.  And the document contains a relative URL
reference to the image, so now the image can no longer be found.

I could change the SRC= for the image to be an absolute path.  But then the
HTML pages would not work for static browsing when unpacked into an
arbitrary directory on a users disk.

I guess the correct solution is to redesign the "mark" CGI program so that
it does not add an extra directory level in the URL.

--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users