Why sqlite show qualified column names when selecting from views ?

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

Why sqlite show qualified column names when selecting from views ?

Domingo Alvarez Duarte-2
Hello !  

When querying views sqlite shows qualified column names if they are specified
individually.  

Is this the expected result or a bug ?  

This behavior breaks some of my code that uses column names for other
purposes.  

Cheers !  

====output of "test-view-alias"  

SQL: SELECT a.* FROM tbl AS a;
Column 0: id
Column 1: name
SQL: SELECT a.id, a.name FROM tbl AS a;
Column 0: id
Column 1: name
SQL: SELECT a.* FROM tbl_view AS a;
Column 0: id
Column 1: name
SQL: SELECT a.id, a.name FROM tbl_view AS a;
Column 0: a.id   <<<<<<<<<<<<<<<<<<<<< only with individual fields
Column 1: a.name <<<<<<<<<<<<<<<<<<<
====  

==== test-view-alias.c  

#include <stdio.h>
#include "sqlite3.h"

void showStmtColNames(sqlite3 *db, const char *szSQL)
{
    sqlite3_stmt* stmt;
    printf("SQL: %s\n", szSQL);
    int rc = sqlite3_prepare_v2(db, szSQL, -1, &stmt, 0);
    int i, col_count = sqlite3_column_count(stmt);
    for(i=0; i < col_count; ++i)
    {
        printf("Column %d: %s\n", i, sqlite3_column_name(stmt, i));
    }
    sqlite3_finalize(stmt);
}

int main(int argc, char *argv[])
{
    sqlite3 *db;
    const char dbname_szSQL[] = ":memory:";
    int rc = sqlite3_open(dbname_szSQL, &db);
    if(rc == SQLITE_OK)
    {
        char *errmsg;
        rc = sqlite3_exec(db, "CREATE TABLE tbl(id, name);", NULL,
NULL, &errmsg);
        rc = sqlite3_exec(db, "CREATE VIEW tbl_view AS SELECT a.id,
a.name FROM tbl AS a;", NULL, NULL, &errmsg);
        rc = sqlite3_exec(db, "INSERT INTO tbl(id, name) VALUES(1,
'dad');", NULL, NULL, &errmsg);
       
        showStmtColNames(db, "SELECT a.* FROM tbl AS a;");
        showStmtColNames(db, "SELECT a.id, a.name FROM tbl AS a;");
        showStmtColNames(db, "SELECT a.* FROM tbl_view AS a;");
        showStmtColNames(db, "SELECT a.id, a.name FROM tbl_view AS
a;"); //here only we get qualified names
       
        sqlite3_close(db);
    }
    return 0;
}  

====  

====script to compile "test-view-alias.c"  

MYINC=.

gcc -g -O2 \
    -DSQLITE_DEBUG=1 \
    -DSQLITE_ENABLE_EXPLAIN_COMMENTS=1 \
    -DTHREADSAFE=1 \
    -DSQLITE_DEFAULT_FILE_FORMAT=4 \
    -DSQLITE_DEFAULT_AUTOVACUUM=1 \
    -DSQLITE_DEFAULT_FOREIGN_KEYS=1 \
    -DSQLITE_ENABLE_COLUMN_METADATA=1 \
    -DSQLITE_ENABLE_FTS4=1 \
    -DSQLITE_ENABLE_FTS3_PARENTHESIS=1 \
    -DSQLITE_ENABLE_UNLOCK_NOTIFY=1 \
    -DSQLITE_ENABLE_RTREE=1 \
    -DSQLITE_ENABLE_STAT4=1 \
    -DSQLITE_OMIT_TCL_VARIABLE=1 \
    -DSQLITE_USE_URI=1 \
    -DSQLITE_SOUNDEX=1\
    -o test-view-alias test-view-alias.c -I $MYINC $MYINC/sqlite3.c
-lpthread -lm -ldl  

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

Re: Why sqlite show qualified column names when selecting from views ?

Simon Slavin-3

On 25 Aug 2015, at 6:13pm, sqlite-mail <[hidden email]> wrote:

> When querying views sqlite shows qualified column names if they are specified
> individually.

Sorry but this has been mentioned a few times here and won't be changed.  The SQL standard doesn't mention column names so SQL engines are free to do what they want.

In SQLite you can depend on column names only if you have specified them using 'AS'.  So I would expect, but haven't tested right now ...

> SQL: SELECT a.* FROM tbl_view AS a;
> Column 0: id
> Column 1: name
> SQL: SELECT a.id, a.name FROM tbl_view AS a;
> Column 0: a.id   <<<<<<<<<<<<<<<<<<<<< only with individual fields
> Column 1: a.name <<<<<<<<<<<<<<<<<<<


SQL: SELECT a.id AS id, a.name AS name FROM tbl_view AS a;
Column 0: id
Column 1: name

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

Re: Why sqlite show qualified column names when selecting from views ?

Domingo Alvarez Duarte-2
First of all thanks for reply !  

I can understand your point, what confuses me is that when we query a view
with "a.*" it doesn't qualify the names so I thought that was a mistake when
we use individual names otherwise I would expected qualified names there too.
 

Cheers !  

>  Tue Aug 25 2015 19:45:27 CEST from "Simon Slavin" <[hidden email]>
>Subject: Re: [sqlite] Why sqlite show qualified column names when selecting
>from views ?
>
>  On 25 Aug 2015, at 6:13pm, sqlite-mail <[hidden email]> wrote:
>
>  
>>When querying views sqlite shows qualified column names if they are
>>specified
>> individually.
>>

>  Sorry but this has been mentioned a few times here and won't be changed.
>The SQL standard doesn't mention column names so SQL engines are free to do
>what they want.
>
> In SQLite you can depend on column names only if you have specified them
>using 'AS'. So I would expect, but haven't tested right now ...
>
>  
>>SQL: SELECT a.* FROM tbl_view AS a;
>> Column 0: id
>> Column 1: name
>> SQL: SELECT a.id, a.name FROM tbl_view AS a;
>> Column 0: a.id <<<<<<<<<<<<<<<<<<<<< only with individual fields
>> Column 1: a.name <<<<<<<<<<<<<<<<<<<
>>
>>

>  SQL: SELECT a.id AS id, a.name AS name FROM tbl_view AS a;
> Column 0: id
> Column 1: name
>
> Simon.
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>  

 

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

Re: Why sqlite show qualified column names when selecting from views ?

Stephan Beal-3
The behaviour you're asking about is explicitly undefined in sqlite. Today
it might work like you are reporting and tomorrow it might do something
different. The ONLY way to guarantee specific column names is to do what
Simon suggested: always use "as". If you don't, the exact results are
undefined.

----- stephan
(Sent from a mobile device, possibly from bed. Please excuse brevity,
typos, and top-posting.)
On Aug 25, 2015 19:52, "sqlite-mail" <[hidden email]> wrote:

> First of all thanks for reply !
>
> I can understand your point, what confuses me is that when we query a view
> with "a.*" it doesn't qualify the names so I thought that was a mistake
> when
> we use individual names otherwise I would expected qualified names there
> too.
>
>
> Cheers !
> >  Tue Aug 25 2015 19:45:27 CEST from "Simon Slavin" <[hidden email]
> >
> >Subject: Re: [sqlite] Why sqlite show qualified column names when
> selecting
> >from views ?
> >
> >  On 25 Aug 2015, at 6:13pm, sqlite-mail <[hidden email]>
> wrote:
> >
> >
> >>When querying views sqlite shows qualified column names if they are
> >>specified
> >> individually.
> >>
>
> >  Sorry but this has been mentioned a few times here and won't be changed.
> >The SQL standard doesn't mention column names so SQL engines are free to
> do
> >what they want.
> >
> > In SQLite you can depend on column names only if you have specified them
> >using 'AS'. So I would expect, but haven't tested right now ...
> >
> >
> >>SQL: SELECT a.* FROM tbl_view AS a;
> >> Column 0: id
> >> Column 1: name
> >> SQL: SELECT a.id, a.name FROM tbl_view AS a;
> >> Column 0: a.id <<<<<<<<<<<<<<<<<<<<< only with individual fields
> >> Column 1: a.name <<<<<<<<<<<<<<<<<<<
> >>
> >>
>
> >  SQL: SELECT a.id AS id, a.name AS name FROM tbl_view AS a;
> > Column 0: id
> > Column 1: name
> >
> > Simon.
> > _______________________________________________
> > sqlite-users mailing list
> > [hidden email]
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> >
> >
>
>
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Why sqlite show qualified column names when selecting from views ?

Simon Slavin-3
In reply to this post by Domingo Alvarez Duarte-2

On 25 Aug 2015, at 6:52pm, sqlite-mail <[hidden email]> wrote:

> I can understand your point, what confuses me is that when we query a view
> with "a.*" it doesn't qualify the names so I thought that was a mistake when
> we use individual names otherwise I would expected qualified names there too.

I understand that this is not what one would expect.  Worse still, as the documentation says ...

<https://www.sqlite.org/c3ref/column_name.html>

"If there is no AS clause then the name of the column is unspecified and may change from one release of SQLite to the next."

If you expect your own code to know what columns are called use 'AS' for each column.

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

Re: Why sqlite show qualified column names when selecting from views ?

Richard Hipp-3
On 8/25/15, Simon Slavin <[hidden email]> wrote:
>
> "If there is no AS clause then the name of the column is unspecified and may
> change from one release of SQLite to the next."
>

Heed this warning!!!

And yet, there are countless tens of thousands of applications in
circulation that have ignored this warning and depend on the
(unsupported) behavior of whatever version of SQLite they were
originally developed on.  So the reality is that we are extremely
careful not to change the result column naming algorithms, for fear of
breaking billions of cellphone apps.

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

Re: Why sqlite show qualified column names when selecting from views ?

R Smith


On 2015-08-25 08:37 PM, Richard Hipp wrote:

> On 8/25/15, Simon Slavin <[hidden email]> wrote:
>> "If there is no AS clause then the name of the column is unspecified and may
>> change from one release of SQLite to the next."
>>
> Heed this warning!!!
>
> And yet, there are countless tens of thousands of applications in
> circulation that have ignored this warning and depend on the
> (unsupported) behavior of whatever version of SQLite they were
> originally developed on.  So the reality is that we are extremely
> careful not to change the result column naming algorithms, for fear of
> breaking billions of cellphone apps.

I vote to change it every release... Stimulate better habits!


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

Re: Why sqlite show qualified column names when selecting from views ?

Petite Abeille-2

> On Aug 25, 2015, at 8:53 PM, R.Smith <[hidden email]> wrote:
>
> I vote to change it every release... Stimulate better habits!

Seconded. Keep them on their toes!
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Why sqlite show qualified column names when selecting from views ?

Scott Robison-2
On Aug 25, 2015 1:02 PM, "Petite Abeille" <[hidden email]> wrote:
>
>
> > On Aug 25, 2015, at 8:53 PM, R.Smith <[hidden email]> wrote:
> >
> > I vote to change it every release... Stimulate better habits!
>
> Seconded. Keep them on their toes!
>

Or randomly generate names after every prepare! Or just leave them
anonymous. #mostlyjoking
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Why sqlite show qualified column names when selecting from views ?

Simon Slavin-3

On 25 Aug 2015, at 8:52pm, Scott Robison <[hidden email]> wrote:

> On Aug 25, 2015 1:02 PM, "Petite Abeille" <[hidden email]> wrote:
>
>> On Aug 25, 2015, at 8:53 PM, R.Smith <[hidden email]> wrote:
>>
>>> I vote to change it every release... Stimulate better habits!
>>
>> Seconded. Keep them on their toes!
>
> Or randomly generate names after every prepare! Or just leave them
> anonymous.

In the next version of SQLite3 the names of all columns will be 'columnname'.

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

Re: Why sqlite show qualified column names when selecting from views ?

Jean-Christophe Deschamps-3
At 22:28 25/08/2015, you wrote:

>In the next version of SQLite3 the names of all columns will be
>'columnname'

"Don't trust me" could be more apropriate.

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

Re: Why sqlite show qualified column names when selecting from views ?

Tim Streater-3
In reply to this post by Richard Hipp-3
On 25 Aug 2015 at 19:37, Richard Hipp <[hidden email]> wrote:

> On 25/8/15, Simon Slavin <[hidden email]> wrote:
>>
>> "If there is no AS clause then the name of the column is unspecified and may
>> change from one release of SQLite to the next."

> Heed this warning!!!
>
> And yet, there are countless tens of thousands of applications in
> circulation that have ignored this warning and depend on the
> (unsupported) behavior of whatever version of SQLite they were
> originally developed on.  So the reality is that we are extremely
> careful not to change the result column naming algorithms, for fear of
> breaking billions of cellphone apps.

So how does that work with:

  create table newtable as select * from oldtable;

Does that mean that, in principle, I could have any random set of column names for my new table?

Doing this properly is also going to mean that, for safety, I've got 306 select statements to fix up in my application. It's gonna be a bit dull if I have one where I select 20 named columns from a table and have to change such as:

  select absid, firstname, lastname, phone, ...

to:

  select absid as absid, firstname as firstname, lastname as lastname, phone as phone, ...

Recipe for typos there, ISTM. Grumble, grumble.

--
Cheers  --  Tim

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

Re: Why sqlite show qualified column names when selecting from views ?

Simon Slavin-3

On 25 Aug 2015, at 10:27pm, Tim Streater <[hidden email]> wrote:

> So how does that work with:
>
>  create table newtable as select * from oldtable;
>
> Does that mean that, in principle, I could have any random set of column names for my new table?

Future versions of SQLite can do anything.  In real life, for that specific command, for column names which are just letters and digits, you'll get what you expect.  However ...

If you have column names surrounded by double-quotes or square brackets, do you expect them to be part of the column names ?  The SQL specification is a little weird on the issue.

Also, suppose you did
        create table newtable as select * from myView

and one of the view columns was a calculation.  What do you expect the name of the corresponding column to be ?

> Doing this properly is also going to mean that, for safety, I've got 306 select statements to fix up in my application. It's gonna be a bit dull if I have one where I select 20 named columns from a table and have to change such as:
>
>  select absid, firstname, lastname, phone, ...
>
> to:
>
>  select absid as absid, firstname as firstname, lastname as lastname, phone as phone, ...

Alternatively you could use the columns which are in specific places.  For example, if lastname is the third column of the SELECT command, its values will always be the third column of the response.  Not only will this always work but it will be faster since it's faster to process the index 3 than to look up a string.

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

Re: Why sqlite show qualified column names when selecting from views ?

Richard Hipp-3
In reply to this post by Tim Streater-3
On 8/25/15, Tim Streater <[hidden email]> wrote:

> On 25 Aug 2015 at 19:37, Richard Hipp <[hidden email]> wrote:
>
>> On 25/8/15, Simon Slavin <[hidden email]> wrote:
>>>
>>> "If there is no AS clause then the name of the column is unspecified and
>>> may
>>> change from one release of SQLite to the next."
>
>> Heed this warning!!!
>>
>> And yet, there are countless tens of thousands of applications in
>> circulation that have ignored this warning and depend on the
>> (unsupported) behavior of whatever version of SQLite they were
>> originally developed on.  So the reality is that we are extremely
>> careful not to change the result column naming algorithms, for fear of
>> breaking billions of cellphone apps.
>
> So how does that work with:
>
>   create table newtable as select * from oldtable;
>
> Does that mean that, in principle, I could have any random set of column
> names for my new table?
>

Yeah.  That's a bummer, isn't it....

But in practice, our default column name chooser algorithm does what
you want here, and as I stated before, we are in no hurry to change
it.


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

Re: Why sqlite show qualified column names when selecting from views ?

Domingo Alvarez Duarte-2
In reply to this post by Tim Streater-3
I just tested this on postgresql and I always get unqualified column names as
I would expect.  

I remember this was a lot worst in previous sqlite versions and I needed to
play with two compile time/pragma flags and depending on that combination we
got some bizarre results.  

I understand that there is legacy code that should take in consideration when
changing sqlite behavior.  

But like it's already done on several other cases why not fix this issue and
wrap this fix with "#ifdef SQLITE_CLEAN_COLUMN_NAMES" that's not defined by
default so older code will remain working but new code can enable this and
use it ?  

   

Cheers !  

 

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

(256 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: Why sqlite show qualified column names when selecting from views ?

Simon Slavin-3

On 26 Aug 2015, at 10:17am, Domingo Alvarez Duarte <[hidden email]> wrote:

> But like it's already done on several other cases why not fix this issue and
> wrap this fix with "#ifdef SQLITE_CLEAN_COLUMN_NAMES" that's not defined by
> default so older code will remain working but new code can enable this and
> use it ?

With that definition set, suppose you execute the following two commands:

ATTACH DATABASE 'August' AS salesThisMonth;
SELECT clientName, salesThisMonth.salesTotal, salesTotal
        FROM sales
        JOIN salesThisMonth ON salesThisMonth.clientId = sales.clientId
        ORDER BY clientName;

What should the name be for the second column ?

Also, with

SELECT a, b, 100*a/b FROM myTable;

What should the name of the third column be ?

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

Re: Why sqlite show qualified column names when selecting from views ?

Domingo Alvarez Duarte-2
Hello !  

Three possibilities:  

1- Show a qualified name to disambiguate.  

2 - Show duplicated column names.  

3 - Show error message message about ambiguos column name.  

   

On the case mentioned apply rule 2, like postgresql do.  

Cheers !  

   

>  Wed Aug 26 2015 2:35:41 pm CEST CEST from "Simon Slavin"
><[hidden email]>  Subject: Re: [sqlite] Why sqlite show qualified
>column names when selecting from views ?
>
>  On 26 Aug 2015, at 10:17am, Domingo Alvarez Duarte
><[hidden email]> wrote:
>
>  
>>But like it's already done on several other cases why not fix this issue
>>and
>> wrap this fix with "#ifdef SQLITE_CLEAN_COLUMN_NAMES" that's not defined
>>by
>> default so older code will remain working but new code can enable this and
>> use it ?
>>

>  With that definition set, suppose you execute the following two commands:
>
> ATTACH DATABASE 'August' AS salesThisMonth;
> SELECT clientName, salesThisMonth.salesTotal, salesTotal
> FROM sales
> JOIN salesThisMonth ON salesThisMonth.clientId = sales.clientId
> ORDER BY clientName;
>
> What should the name be for the second column ?
>
> Also, with
>
> SELECT a, b, 100*a/b FROM myTable;
>
> What should the name of the third column be ?
>
> Simon.
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>  

 

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

Re: Why sqlite show qualified column names when selecting from views ?

Stephen Chrzanowski
I'm sure either #1 or #3 happens already.  I know I've been nagged about
ambiguous field names, but I also know that somewhere I've seen _1 or _2 be
appended to field names (That might be just the SQL IDE I use doing that).

I can't get on board with #2 because most wrappers only look at the first
value, or, would overwrite "SalesTotal" with whatever the last column has
the name SalesTotal.  The wrapper I use in Delphi uses both named field
arrays (IE: tbl.FieldByName['SalesTotal']) as well as field numbers (IE:
tbl.FieldByID[1]) but I try to use the field names since the order of the
field names COULD change.


On Wed, Aug 26, 2015 at 10:12 AM, Domingo Alvarez Duarte <
[hidden email]> wrote:

> Hello !
>
> Three possibilities:
>
> 1- Show a qualified name to disambiguate.
>
> 2 - Show duplicated column names.
>
> 3 - Show error message message about ambiguos column name.
>
>
>
> On the case mentioned apply rule 2, like postgresql do.
>
> Cheers !
>
>
> >  Wed Aug 26 2015 2:35:41 pm CEST CEST from "Simon Slavin"
> ><[hidden email]>  Subject: Re: [sqlite] Why sqlite show qualified
> >column names when selecting from views ?
> >
> >  On 26 Aug 2015, at 10:17am, Domingo Alvarez Duarte
> ><[hidden email]> wrote:
> >
> >
> >>But like it's already done on several other cases why not fix this issue
> >>and
> >> wrap this fix with "#ifdef SQLITE_CLEAN_COLUMN_NAMES" that's not defined
> >>by
> >> default so older code will remain working but new code can enable this
> and
> >> use it ?
> >>
>
> >  With that definition set, suppose you execute the following two
> commands:
> >
> > ATTACH DATABASE 'August' AS salesThisMonth;
> > SELECT clientName, salesThisMonth.salesTotal, salesTotal
> > FROM sales
> > JOIN salesThisMonth ON salesThisMonth.clientId = sales.clientId
> > ORDER BY clientName;
> >
> > What should the name be for the second column ?
> >
> > Also, with
> >
> > SELECT a, b, 100*a/b FROM myTable;
> >
> > What should the name of the third column be ?
> >
> > Simon.
> > _______________________________________________
> > sqlite-users mailing list
> > [hidden email]
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> >
> >
>
>
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Why sqlite show qualified column names when selecting from views ?

Domingo Alvarez Duarte-2
So in your case (delphi usage) how do you do when using postgresql ?  

Cheers !  

>  Wed Aug 26 2015 4:52:02 pm CEST CEST from "Stephen Chrzanowski"
><[hidden email]>  Subject: Re: [sqlite] Why sqlite show qualified column
>names when selecting from views ?
>
>  I'm sure either #1 or #3 happens already. I know I've been nagged about
> ambiguous field names, but I also know that somewhere I've seen _1 or _2 be
> appended to field names (That might be just the SQL IDE I use doing that).
>
> I can't get on board with #2 because most wrappers only look at the first
> value, or, would overwrite "SalesTotal" with whatever the last column has
> the name SalesTotal. The wrapper I use in Delphi uses both named field
> arrays (IE: tbl.FieldByName['SalesTotal']) as well as field numbers (IE:
> tbl.FieldByID[1]) but I try to use the field names since the order of the
> field names COULD change.
>
>
> On Wed, Aug 26, 2015 at 10:12 AM, Domingo Alvarez Duarte <
> [hidden email]> wrote:
>
>  
>>Hello !
>>
>> Three possibilities:
>>
>> 1- Show a qualified name to disambiguate.
>>
>> 2 - Show duplicated column names.
>>
>> 3 - Show error message message about ambiguos column name.
>>
>>
>>
>> On the case mentioned apply rule 2, like postgresql do.
>>
>> Cheers !
>>
>>
>>  
>>>Wed Aug 26 2015 2:35:41 pm CEST CEST from "Simon Slavin"
>>> <[hidden email]> Subject: Re: [sqlite] Why sqlite show qualified
>>> column names when selecting from views ?
>>>
>>> On 26 Aug 2015, at 10:17am, Domingo Alvarez Duarte
>>> <[hidden email]> wrote:
>>>
>>>
>>>  
>>>>But like it's already done on several other cases why not fix this issue
>>>> and
>>>> wrap this fix with "#ifdef SQLITE_CLEAN_COLUMN_NAMES" that's not defined
>>>> by
>>>> default so older code will remain working but new code can enable this

>>>  

>>  and
>>  
>>>  
>>>>use it ?
>>>>
>>>>

>>>  With that definition set, suppose you execute the following two

>>  commands:
>>
>>  
>>>ATTACH DATABASE 'August' AS salesThisMonth;
>>> SELECT clientName, salesThisMonth.salesTotal, salesTotal
>>> FROM sales
>>> JOIN salesThisMonth ON salesThisMonth.clientId = sales.clientId
>>> ORDER BY clientName;
>>>
>>> What should the name be for the second column ?
>>>
>>> Also, with
>>>
>>> SELECT a, b, 100*a/b FROM myTable;
>>>
>>> What should the name of the third column be ?
>>>
>>> Simon.
>>> _______________________________________________
>>> sqlite-users mailing list
>>> [hidden email]
>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>>
>>>
>>>
>>>
>>>
>>>
>>>

>>  _______________________________________________
>> sqlite-users mailing list
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>

>  _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>  

 

 
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users