Re: [sqlite] sqlite_column_table_name() and table alias name

[Jake Chen]

Hi J Decker,

`alias_name` can only be identifiers without qualifiers I believe. Hence 
`select 1 1` isn't possible.

Changing values to arrays to allow multi values on same column be able to 
co-exist is a workaround. It works perfect on orphan columns such as `select 1 
a, 2 a`. Though it cannot reflect the field metadata clearly enough. In your 
previous example, the value of name column is `name: ['orange', 'orange', 
fruit: 'orange', color: 'orange']`. I believe this isn't valid JSON. It's 
possible in JavaScript since Array is just special Object but it doesn't mean 
it's easy to process.

On the other hand, formatting the result as an array of objects by default is 
the right thing to do. I haven't got any intention to oppose that. What I 
suggested is, the results can be array of arrays or objects nested with table 
names. Postgres does that by `.query(sql, { rowMode: 'array' })`. MySQL does 
that by `.query(sql, { nestTables: true })`.

Oh, I think these discussions are a bit off topic. I sent this email mostly to 
request the `sqlite3_column_table_alias_name()` API. I've got the original 
patch working 
 in my 
fork of node-sqlite3. 

> On 16 Jan 2018, at 10:11 AM, J Decker  wrote:
> 
> On Mon, Jan 15, 2018 at 5:34 PM, Jake Chen  wrote:
> 
>> The example result is a demonstration of results nested with table alias
>> right? If that's the case I think the `select 1, 1` part should result in:
>> 
>> [ { '': { '1': 1 } } ]
>> 
>> There's no need to return both. If the results should not be nested, here
>> the result I'd expect:
>> 
>> [ { '1': 1 } ]
>> 
> 
> That is `select 1`
> yes, if they happen to be the same value you can collapse them.  But if you
> end up with 1 columns with the same alias (a few things I tried were
> prevented by having to specify the query a certain way).
> 
> (although I don't think aliases work as numbers either)
> select 1 1, 2 1
> [ { 1: [ 1, 2 ] } ]
> 
> select 1 a, 2 a
> [ { a: [ 1, 2 ] } ]
> 
> (and there's no table named to specify keyed values in that result.)
> 
> 
> 
>> 
>> With corresponding fields:
>> 
>> [ { name: '1', table: '', orgTable: '' } ]
>> 
>>> unless something like `as tabname.newcol` can override it?
>> 
>> I believe alias_name can't have qualifiers.
>> 
>> Regarding the `name` part (same case as the `1` column I guess), this is
>> unnecessary ambiguity. Either returning rows in pure arrays or nest the
>> rows with table names, the ambiguity is gone. In pure arrays:
>> 
>> [ [ 1, 1, 1, 'apple', 1, 'red', 1, 1 ] ]
>> 
>> With corresponding fields:
>> 
>> [ { name: '1', table: '' },
>>  { name: '1', table: '' },
>>  { name: 'fruit_id', table: 'fruit' },
>>  { name: 'name', table: 'fruit' },
>>  { name: 'color_id', table: 'color' },
>>  { name: 'name', table: 'color' },
>>  { name: 'fruid_id', table: 'fruit_color' },
>>  { name: 'color_id', table: 'fruit_color' } }
>> 
>> Or if the result is nested by tables:
>> 
>> [ { '': { '1': 1 } },
>>  { fruit: { fruit_id: 1, name: 'apple' } },
>>  { color: { color_id: 1, name: 'red' } },
>>  { fruit_color: { fruit_id: 1, color_id: 1 } } ]
>> 
>> 
> This is what it would be, other than '' would be '1' because that IS the
> column name there... it just happens that two values have the same alias
> name (bad query writing;  )
> 
> When I was implementing test case I couldn't do 'select 1 as foo.id' of
> course then I'd want to be able to alias the database name it's in too
> 
> I'm not a fan of arrays; that the rows are returned is enough :)
> If you never have to column names the same all values would be flat.
> I took the overlap as an array from `mssql` node package(s).
> 
> And then just decided to throw in all of the options so you can reference
> it in many ways... testing if( record.field.length ) as required; but if
> you can use USING() you only get 1 column back in a * query (those will be
> the same value sorted by sqlite nicely)
> 
> 
> 
> 
> 
> 
>> That is what I've implemented in the PR I sent to node-sqlite3.
>> 
>>> On 16 Jan 2018, at 3:59 AM, J Decker  wrote:
>>> 
>>> One more example for the road...
>>> 
>>> create table fruit ( fruit_id,name)
>>> create table color ( color_id,name)
>>> create table fruit_color ( fruit_id,color_id )
>>> 
>>> insert into fruit (fruit_id,name) values (1,'apple'),(2,'orange'),(3,'b
>>> anana')"
>>> insert into color (color_id,name) values (1,'red'),(2,'orange'),(3,'yel
>>> low')"
>>> insert into fruit_color (fruit_id,color_id) values (1,1),(2,2),(3,3)"
>>> 
>>> select 1,1,fruit.*,color.* from fruit join fruit_color on
>>> fruit_color.fruit_id=fruit.fruit_id join color on
>>> fruit_color.color_id=color.color_id
>>> 
>>> (If the table has columns that overlap)
>>> 
>>> [ { '1': [ 1, 1 ],
>>>   fruit: { fruit_id: 1 },
>>>   color: { color_id: 1 },
>>>   fruit_id: 1,
>>>   name: [ 'apple', 'red', fruit: 'apple', color: 'red' ],
>>>   

Re: [sqlite] sqlite_column_table_name() and table alias name

[J Decker]

On Mon, Jan 15, 2018 at 5:34 PM, Jake Chen  wrote:

> The example result is a demonstration of results nested with table alias
> right? If that's the case I think the `select 1, 1` part should result in:
>
> [ { '': { '1': 1 } } ]
>
> There's no need to return both. If the results should not be nested, here
> the result I'd expect:
>
> [ { '1': 1 } ]
>

That is `select 1`
yes, if they happen to be the same value you can collapse them.  But if you
end up with 1 columns with the same alias (a few things I tried were
prevented by having to specify the query a certain way).

(although I don't think aliases work as numbers either)
select 1 1, 2 1
 [ { 1: [ 1, 2 ] } ]

select 1 a, 2 a
 [ { a: [ 1, 2 ] } ]

(and there's no table named to specify keyed values in that result.)



>
> With corresponding fields:
>
> [ { name: '1', table: '', orgTable: '' } ]
>
> > unless something like `as tabname.newcol` can override it?
>
> I believe alias_name can't have qualifiers.
>
> Regarding the `name` part (same case as the `1` column I guess), this is
> unnecessary ambiguity. Either returning rows in pure arrays or nest the
> rows with table names, the ambiguity is gone. In pure arrays:
>
> [ [ 1, 1, 1, 'apple', 1, 'red', 1, 1 ] ]
>
> With corresponding fields:
>
> [ { name: '1', table: '' },
>   { name: '1', table: '' },
>   { name: 'fruit_id', table: 'fruit' },
>   { name: 'name', table: 'fruit' },
>   { name: 'color_id', table: 'color' },
>   { name: 'name', table: 'color' },
>   { name: 'fruid_id', table: 'fruit_color' },
>   { name: 'color_id', table: 'fruit_color' } }
>
> Or if the result is nested by tables:
>
> [ { '': { '1': 1 } },
>   { fruit: { fruit_id: 1, name: 'apple' } },
>   { color: { color_id: 1, name: 'red' } },
>   { fruit_color: { fruit_id: 1, color_id: 1 } } ]
>
>
This is what it would be, other than '' would be '1' because that IS the
column name there... it just happens that two values have the same alias
name (bad query writing;  )

When I was implementing test case I couldn't do 'select 1 as foo.id' of
course then I'd want to be able to alias the database name it's in too

I'm not a fan of arrays; that the rows are returned is enough :)
If you never have to column names the same all values would be flat.
I took the overlap as an array from `mssql` node package(s).

And then just decided to throw in all of the options so you can reference
it in many ways... testing if( record.field.length ) as required; but if
you can use USING() you only get 1 column back in a * query (those will be
the same value sorted by sqlite nicely)






> That is what I've implemented in the PR I sent to node-sqlite3.
>
> > On 16 Jan 2018, at 3:59 AM, J Decker  wrote:
> >
> > One more example for the road...
> >
> > create table fruit ( fruit_id,name)
> > create table color ( color_id,name)
> > create table fruit_color ( fruit_id,color_id )
> >
> > insert into fruit (fruit_id,name) values (1,'apple'),(2,'orange'),(3,'b
> > anana')"
> > insert into color (color_id,name) values (1,'red'),(2,'orange'),(3,'yel
> > low')"
> > insert into fruit_color (fruit_id,color_id) values (1,1),(2,2),(3,3)"
> >
> > select 1,1,fruit.*,color.* from fruit join fruit_color on
> > fruit_color.fruit_id=fruit.fruit_id join color on
> > fruit_color.color_id=color.color_id
> >
> > (If the table has columns that overlap)
> >
> > [ { '1': [ 1, 1 ],
> >fruit: { fruit_id: 1 },
> >color: { color_id: 1 },
> >fruit_id: 1,
> >name: [ 'apple', 'red', fruit: 'apple', color: 'red' ],
> >color_id: 1 },
> >  { '1': [ 1, 1 ],
> >fruit: { fruit_id: 2 },
> >color: { color_id: 2 },
> >fruit_id: 2,
> >name: [ 'orange', 'orange', fruit: 'orange', color: 'orange' ],
> >color_id: 2 },
> >  { '1': [ 1, 1 ],
> >fruit: { fruit_id: 3 },
> >color: { color_id: 3 },
> >fruit_id: 3,
> >name: [ 'banana', 'yellow', fruit: 'banana', color: 'yellow' ],
> >color_id: 3 } ]
> >
> > can be accessed with
> > result[n].fruit.name
> > result[n].name.fruit
> > result[n].name[0]
> >
> > constants and expressions get put into the base object (unless something
> > like `as tabname.newcol` can override it?  If a column name overlaps a
> > table name, it can orphan the table object and replace with the
> row
> > value. throws an exception and gives up. (IE
> > `select fruit.name as c,fruit.*,c.* from fruit join fruit_color
> > USING(fruit_id) join color as c USING(color_id)` ).
> >
> > wouldn't make that the production version of data to rely on... and
> > wouldn't want to query 1M records with triplicated values :)
> >
> >
> > On Mon, Jan 15, 2018 at 7:07 AM, J Decker  wrote:
> >
> >>
> >>
> >> On Mon, Jan 15, 2018 at 7:05 AM, J Decker  wrote:
> >>
> >>>
> >>>
> >>> On Mon, Jan 15, 2018 at 5:14 AM, Clemens Ladisch 
> >>> wrote:
> >>>
>  J Decker wrote:
> > What is the expected output?
> 
>  And just out of curiosity: what 

Re: [sqlite] sqlite_column_table_name() and table alias name

[Jake Chen]

Hi Clemens, 

You're right. I didn't look into your SQL carefully enough. As a reference, in 
MySQL the table of id would be the first table it belongs to. `articles` it is. 

Jake

> On 15 Jan 2018, at 11:03 PM, Clemens Ladisch  wrote:
> 
> Jake Chen wrote:
>>> On 15 Jan 2018, at 9:14 PM, Clemens Ladisch  wrote:
>>> SELECT id FROM articles JOIN tags USING (id);
>> 
>> `id` is ambiguous here.
> 
> It's not, because of the USING clause.
> 
> 
> Regards,
> Clemens
> ___
> 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] sqlite_column_table_name() and table alias name

[Jake Chen]

The example result is a demonstration of results nested with table alias right? 
If that's the case I think the `select 1, 1` part should result in:

[ { '': { '1': 1 } } ] 

There's no need to return both. If the results should not be nested, here the 
result I'd expect:

[ { '1': 1 } ]

With corresponding fields:

[ { name: '1', table: '', orgTable: '' } ]

> unless something like `as tabname.newcol` can override it?

I believe alias_name can't have qualifiers. 

Regarding the `name` part (same case as the `1` column I guess), this is 
unnecessary ambiguity. Either returning rows in pure arrays or nest the rows 
with table names, the ambiguity is gone. In pure arrays:

[ [ 1, 1, 1, 'apple', 1, 'red', 1, 1 ] ]

With corresponding fields:

[ { name: '1', table: '' },
  { name: '1', table: '' },
  { name: 'fruit_id', table: 'fruit' },
  { name: 'name', table: 'fruit' },
  { name: 'color_id', table: 'color' },
  { name: 'name', table: 'color' },
  { name: 'fruid_id', table: 'fruit_color' },
  { name: 'color_id', table: 'fruit_color' } }

Or if the result is nested by tables:

[ { '': { '1': 1 } },
  { fruit: { fruit_id: 1, name: 'apple' } },
  { color: { color_id: 1, name: 'red' } },
  { fruit_color: { fruit_id: 1, color_id: 1 } } ]

That is what I've implemented in the PR I sent to node-sqlite3. 

> On 16 Jan 2018, at 3:59 AM, J Decker  wrote:
> 
> One more example for the road...
> 
> create table fruit ( fruit_id,name)
> create table color ( color_id,name)
> create table fruit_color ( fruit_id,color_id )
> 
> insert into fruit (fruit_id,name) values (1,'apple'),(2,'orange'),(3,'b
> anana')"
> insert into color (color_id,name) values (1,'red'),(2,'orange'),(3,'yel
> low')"
> insert into fruit_color (fruit_id,color_id) values (1,1),(2,2),(3,3)"
> 
> select 1,1,fruit.*,color.* from fruit join fruit_color on
> fruit_color.fruit_id=fruit.fruit_id join color on
> fruit_color.color_id=color.color_id
> 
> (If the table has columns that overlap)
> 
> [ { '1': [ 1, 1 ],
>fruit: { fruit_id: 1 },
>color: { color_id: 1 },
>fruit_id: 1,
>name: [ 'apple', 'red', fruit: 'apple', color: 'red' ],
>color_id: 1 },
>  { '1': [ 1, 1 ],
>fruit: { fruit_id: 2 },
>color: { color_id: 2 },
>fruit_id: 2,
>name: [ 'orange', 'orange', fruit: 'orange', color: 'orange' ],
>color_id: 2 },
>  { '1': [ 1, 1 ],
>fruit: { fruit_id: 3 },
>color: { color_id: 3 },
>fruit_id: 3,
>name: [ 'banana', 'yellow', fruit: 'banana', color: 'yellow' ],
>color_id: 3 } ]
> 
> can be accessed with
> result[n].fruit.name
> result[n].name.fruit
> result[n].name[0]
> 
> constants and expressions get put into the base object (unless something
> like `as tabname.newcol` can override it?  If a column name overlaps a
> table name, it can orphan the table object and replace with the row
> value. throws an exception and gives up. (IE
> `select fruit.name as c,fruit.*,c.* from fruit join fruit_color
> USING(fruit_id) join color as c USING(color_id)` ).
> 
> wouldn't make that the production version of data to rely on... and
> wouldn't want to query 1M records with triplicated values :)
> 
> 
> On Mon, Jan 15, 2018 at 7:07 AM, J Decker  wrote:
> 
>> 
>> 
>> On Mon, Jan 15, 2018 at 7:05 AM, J Decker  wrote:
>> 
>>> 
>>> 
>>> On Mon, Jan 15, 2018 at 5:14 AM, Clemens Ladisch 
>>> wrote:
>>> 
 J Decker wrote:
> What is the expected output?
 
 And just out of curiosity: what should the table name be for these
 columns?
 
  SELECT articles.gmt_deleted+tags.type, 42, id FROM articles JOIN tags
 USING (id);
 
 
>>> table name wouldn't matter.
>>> 
>>> create table articles ( id, content,gmt_deleted ) " );
>>> create table tags ( id,type, text ) " );
>>> insert into articles (id,content,gmt_deleted) values ( 1, 'hello
>>> world','X')" );
>>> insert into tags (id,type,text) values ( 1, 'text', 'MOTD')" );
>>> SELECT articles.gmt_deleted+tags.type, 42, articles.id FROM articles
>>> JOIN tags on articles.id=tags.id;" ) );
>>> [ { '42': 42, 'articles.gmt_deleted+tags.type': 0, id: 1 } ]
>>> 
>>> 
>> SELECT * FROM articles JOIN tags USING(id)
>> [ { id: 1,
>>content: 'hello world',
>>gmt_deleted: 'X',
>>type: 'text',
>>text: 'MOTD' } ]
>> 
>>> 
 Regards,
 Clemens
 ___
 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

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

Re: [sqlite] sqlite_column_table_name() and table alias name

[J Decker]

One more example for the road...

create table fruit ( fruit_id,name)
create table color ( color_id,name)
create table fruit_color ( fruit_id,color_id )

insert into fruit (fruit_id,name) values (1,'apple'),(2,'orange'),(3,'b
anana')"
insert into color (color_id,name) values (1,'red'),(2,'orange'),(3,'yel
low')"
insert into fruit_color (fruit_id,color_id) values (1,1),(2,2),(3,3)"

select 1,1,fruit.*,color.* from fruit join fruit_color on
fruit_color.fruit_id=fruit.fruit_id join color on
fruit_color.color_id=color.color_id

(If the table has columns that overlap)

[ { '1': [ 1, 1 ],
fruit: { fruit_id: 1 },
color: { color_id: 1 },
fruit_id: 1,
name: [ 'apple', 'red', fruit: 'apple', color: 'red' ],
color_id: 1 },
  { '1': [ 1, 1 ],
fruit: { fruit_id: 2 },
color: { color_id: 2 },
fruit_id: 2,
name: [ 'orange', 'orange', fruit: 'orange', color: 'orange' ],
color_id: 2 },
  { '1': [ 1, 1 ],
fruit: { fruit_id: 3 },
color: { color_id: 3 },
fruit_id: 3,
name: [ 'banana', 'yellow', fruit: 'banana', color: 'yellow' ],
color_id: 3 } ]

can be accessed with
result[n].fruit.name
result[n].name.fruit
result[n].name[0]

constants and expressions get put into the base object (unless something
like `as tabname.newcol` can override it?  If a column name overlaps a
table name, it can orphan the table object and replace with the row
value. throws an exception and gives up. (IE
`select fruit.name as c,fruit.*,c.* from fruit join fruit_color
USING(fruit_id) join color as c USING(color_id)` ).

wouldn't make that the production version of data to rely on... and
wouldn't want to query 1M records with triplicated values :)


On Mon, Jan 15, 2018 at 7:07 AM, J Decker  wrote:

>
>
> On Mon, Jan 15, 2018 at 7:05 AM, J Decker  wrote:
>
>>
>>
>> On Mon, Jan 15, 2018 at 5:14 AM, Clemens Ladisch 
>> wrote:
>>
>>> J Decker wrote:
>>> > What is the expected output?
>>>
>>> And just out of curiosity: what should the table name be for these
>>> columns?
>>>
>>>   SELECT articles.gmt_deleted+tags.type, 42, id FROM articles JOIN tags
>>> USING (id);
>>>
>>>
>> table name wouldn't matter.
>>
>> create table articles ( id, content,gmt_deleted ) " );
>> create table tags ( id,type, text ) " );
>> insert into articles (id,content,gmt_deleted) values ( 1, 'hello
>> world','X')" );
>> insert into tags (id,type,text) values ( 1, 'text', 'MOTD')" );
>> SELECT articles.gmt_deleted+tags.type, 42, articles.id FROM articles
>> JOIN tags on articles.id=tags.id;" ) );
>> [ { '42': 42, 'articles.gmt_deleted+tags.type': 0, id: 1 } ]
>>
>>
> SELECT * FROM articles JOIN tags USING(id)
> [ { id: 1,
> content: 'hello world',
> gmt_deleted: 'X',
> type: 'text',
> text: 'MOTD' } ]
>
>>
>>> Regards,
>>> Clemens
>>> ___
>>> 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] sqlite_column_table_name() and table alias name

[J Decker]

On Mon, Jan 15, 2018 at 7:05 AM, J Decker  wrote:

>
>
> On Mon, Jan 15, 2018 at 5:14 AM, Clemens Ladisch 
> wrote:
>
>> J Decker wrote:
>> > What is the expected output?
>>
>> And just out of curiosity: what should the table name be for these
>> columns?
>>
>>   SELECT articles.gmt_deleted+tags.type, 42, id FROM articles JOIN tags
>> USING (id);
>>
>>
> table name wouldn't matter.
>
> create table articles ( id, content,gmt_deleted ) " );
> create table tags ( id,type, text ) " );
> insert into articles (id,content,gmt_deleted) values ( 1, 'hello
> world','X')" );
> insert into tags (id,type,text) values ( 1, 'text', 'MOTD')" );
> SELECT articles.gmt_deleted+tags.type, 42, articles.id FROM articles JOIN
> tags on articles.id=tags.id;" ) );
> [ { '42': 42, 'articles.gmt_deleted+tags.type': 0, id: 1 } ]
>
>
SELECT * FROM articles JOIN tags USING(id)
[ { id: 1,
content: 'hello world',
gmt_deleted: 'X',
type: 'text',
text: 'MOTD' } ]

>
>> Regards,
>> Clemens
>> ___
>> 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] sqlite_column_table_name() and table alias name

[J Decker]

On Mon, Jan 15, 2018 at 5:14 AM, Clemens Ladisch  wrote:

> J Decker wrote:
> > What is the expected output?
>
> And just out of curiosity: what should the table name be for these columns?
>
>   SELECT articles.gmt_deleted+tags.type, 42, id FROM articles JOIN tags
> USING (id);
>
>
table name wouldn't matter.

create table articles ( id, content,gmt_deleted ) " );
create table tags ( id,type, text ) " );
insert into articles (id,content,gmt_deleted) values ( 1, 'hello
world','X')" );
insert into tags (id,type,text) values ( 1, 'text', 'MOTD')" );
SELECT articles.gmt_deleted+tags.type, 42, articles.id FROM articles JOIN
tags on articles.id=tags.id;" ) );
[ { '42': 42, 'articles.gmt_deleted+tags.type': 0, id: 1 } ]



>
> Regards,
> Clemens
> ___
> 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] sqlite_column_table_name() and table alias name

[Clemens Ladisch]

Jake Chen wrote:
>> On 15 Jan 2018, at 9:14 PM, Clemens Ladisch  wrote:
>>  SELECT id FROM articles JOIN tags USING (id);
>
> `id` is ambiguous here.

It's not, because of the USING clause.


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

Re: [sqlite] sqlite_column_table_name() and table alias name

[Jake Chen]

`articles.gmt_delete+tags.type` is an select expression, both the original 
column name and table name should be empty.
42 is a literal, both the original column name and table name should be empty 
too.
`id` is ambiguous here. Proper SQL might require it be prefixed with qualifier 
(either `articles` or `tags`). Then both table name and table alias name are 
known.

> On 15 Jan 2018, at 9:14 PM, Clemens Ladisch  wrote:
> 
> J Decker wrote:
>> What is the expected output?
> 
> And just out of curiosity: what should the table name be for these columns?
> 
>  SELECT articles.gmt_deleted+tags.type, 42, id FROM articles JOIN tags USING 
> (id);
> 
> 
> Regards,
> Clemens
> ___
> 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] sqlite_column_table_name() and table alias name

[Clemens Ladisch]

J Decker wrote:
> What is the expected output?

And just out of curiosity: what should the table name be for these columns?

  SELECT articles.gmt_deleted+tags.type, 42, id FROM articles JOIN tags USING 
(id);


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

Re: [sqlite] sqlite_column_table_name() and table alias name (J Decker)

[Jake Chen]

Hi J Decker,

I've got the amalgamation patch working now. However it seems the alias of 
subquery isn't processed correctly. 

The full SQL:
SELECT "posts".*, "tagMaps".*, "topics".* FROM (SELECT * FROM "articles" 
WHERE "gmt_deleted" IS NULL) AS "posts" LEFT JOIN "tag_maps" AS "tagMaps" ON 
"posts"."id" = "tagMaps"."target_id" AND "tagMaps"."target_type" = 0 LEFT JOIN 
"tags" AS "topics" ON "tagMaps"."tag_id" = "topics"."id" AND "topics"."type" = 1

The columns of articles have both their table and table alias point to 
`articles`. I believe the alias should be `posts`. 

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

Re: [sqlite] sqlite_column_table_name() and table alias name

[Simon Slavin]

On 15 Jan 2018, at 12:03pm, Jake Chen  wrote:

> Sorry for not able to reply to your email directly. I opted to receive 
> subscriptions in daily digest. It seems there's no way to revert this option.

As you’ve worked out, you have to unsubscribe and resubscribe.

However, please do not reply directly to a poster unless you’ve moving to a 
personal matter.  There are advantages to having other eyes see your 
discussion.  Especially when those other eyes are those of the development team.

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

Re: [sqlite] sqlite_column_table_name() and table alias name

[Jake Chen]

Hi Decker,

The last one is the one expected. If queried with `nestTables` positive, the 
result should be something like:

[ { foo: { id: 1, parent_id: 3 }, bar: { id: 3, parent_id: null } },
  { foo: { id: 2, parent_id: 4 }, bar: { id: 4, parent_id: null } } ]

Regarding your patch, is that patch official? I've noticed the comment saying 
sqlite3_column_table_alias_name would be available after 3.22.0. It would be 
helpful since I've been trying to apply the patch to amalgamation but failed to 
get table alias yet :-(

Sorry for not able to reply to your email directly. I opted to receive 
subscriptions in daily digest. It seems there's no way to revert this option.

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

Re: [sqlite] sqlite_column_table_name() and table alias name

[J Decker]

@Jake though;
What is the expected output?
 { id: { foo: 1, bar:3 }, parent_id : { foo: 4, bar: 1} }

or
 { id: [1,3], parent_id : [4,1] }
or
 { id: [ 0:1, 1:3, foo: 1, bar:3 ], parent_id : [ 0:4, 1::1, foo: 4, bar:
1] }

or
 { foo : { id : 1, parent_id:3 }, bar : { id: 1, parent_id:3 } } ?


On Mon, Jan 15, 2018 at 1:59 AM, J Decker  wrote:

> Maybe this patch (the same, but against original source instead of
> amalgamation) against current fossil head
>
>
Hmm updated tests too; but bork capi3.test; I don't know how to sperate .22
and .32 to differentiate the expected results 


> https://drive.google.com/open?id=1c24qvtvS57ASJF5RfZxLJSsgI2FhOVk1
>
>
> On Sun, Jan 14, 2018 at 11:17 PM, Jake Chen  wrote:
>
>> Hi Peter,
>>
>> Currently the query result returned by node-sqlite3 has the rows
>> formatted into objects all ready. Given SQL `SELECT foo.*, bar.* FROM foo
>> JOIN bar`, the columns of foo and bar collide with each other. I've already
>> sent a PR https://github.com/mapbox/node-sqlite3/pull/932 <
>> https://github.com/mapbox/node-sqlite3/pull/932> to node-sqlite3 to
>> allow a different result structure that keeps the rows untouched and
>> returns fields along with rows.
>>
>> However, when the SQL contains table aliases, such as `SELECT foo.*,
>> bar.* FROM egg AS foo JOIN egg AS bar ON foo.id  =
>> bar.parent_id`, there's no way to return the fields correctly since
>> `sqlite_column_table_name()` returns the actual table name rather than the
>> alias. The link I attached in previous post is a discussion that took place
>> 4 years ago. OP suggests a new function called
>> `sqlite_column_table_alias_name()` to return the table alias instead of
>> actual name. A patch is attached to implement this function. The major
>> change is in `columnTypeImpl` of `src/select.c`:
>>
>> @@ -105364,6 +105386,7 @@
>>   if( jnSrc ){
>> pTab = pTabList->a[j].pTab;
>> pS = pTabList->a[j].pSelect;
>> +  zTableAlias =
>> pTabList->a[j].zAlias?pTabList->a[j].zAlias:pTabList->a[j].zName;
>>   }else{
>> pNC = pNC->pNext;
>>   }
>>
>> Source: http://mailinglists.sqlite.org/cgi-bin/mailman/private/sqlit
>> e-users/2014-November/056388.html > g/cgi-bin/mailman/private/sqlite-users/2014-November/056388.html>
>>
>> Jake
>> ___
>> 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] sqlite_column_table_name() and table alias name

[J Decker]

Maybe this patch (the same, but against original source instead of
amalgamation) against current fossil head

https://drive.google.com/open?id=1c24qvtvS57ASJF5RfZxLJSsgI2FhOVk1


On Sun, Jan 14, 2018 at 11:17 PM, Jake Chen  wrote:

> Hi Peter,
>
> Currently the query result returned by node-sqlite3 has the rows formatted
> into objects all ready. Given SQL `SELECT foo.*, bar.* FROM foo JOIN bar`,
> the columns of foo and bar collide with each other. I've already sent a PR
> https://github.com/mapbox/node-sqlite3/pull/932 <
> https://github.com/mapbox/node-sqlite3/pull/932> to node-sqlite3 to allow
> a different result structure that keeps the rows untouched and returns
> fields along with rows.
>
> However, when the SQL contains table aliases, such as `SELECT foo.*, bar.*
> FROM egg AS foo JOIN egg AS bar ON foo.id  =
> bar.parent_id`, there's no way to return the fields correctly since
> `sqlite_column_table_name()` returns the actual table name rather than the
> alias. The link I attached in previous post is a discussion that took place
> 4 years ago. OP suggests a new function called 
> `sqlite_column_table_alias_name()`
> to return the table alias instead of actual name. A patch is attached to
> implement this function. The major change is in `columnTypeImpl` of
> `src/select.c`:
>
> @@ -105364,6 +105386,7 @@
>   if( jnSrc ){
> pTab = pTabList->a[j].pTab;
> pS = pTabList->a[j].pSelect;
> +  zTableAlias =
> pTabList->a[j].zAlias?pTabList->a[j].zAlias:pTabList->a[j].zName;
>   }else{
> pNC = pNC->pNext;
>   }
>
> Source: http://mailinglists.sqlite.org/cgi-bin/mailman/private/
> sqlite-users/2014-November/056388.html  org/cgi-bin/mailman/private/sqlite-users/2014-November/056388.html>
>
> Jake
> ___
> 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] sqlite_column_table_name() and table alias name

[Jake Chen]

Hi Peter,

Currently the query result returned by node-sqlite3 has the rows formatted into 
objects all ready. Given SQL `SELECT foo.*, bar.* FROM foo JOIN bar`, the 
columns of foo and bar collide with each other. I've already sent a PR 
https://github.com/mapbox/node-sqlite3/pull/932 
 to node-sqlite3 to allow a 
different result structure that keeps the rows untouched and returns fields 
along with rows. 

However, when the SQL contains table aliases, such as `SELECT foo.*, bar.* FROM 
egg AS foo JOIN egg AS bar ON foo.id  = bar.parent_id`, there's 
no way to return the fields correctly since `sqlite_column_table_name()` 
returns the actual table name rather than the alias. The link I attached in 
previous post is a discussion that took place 4 years ago. OP suggests a new 
function called `sqlite_column_table_alias_name()` to return the table alias 
instead of actual name. A patch is attached to implement this function. The 
major change is in `columnTypeImpl` of `src/select.c`:

@@ -105364,6 +105386,7 @@
  if( jnSrc ){
pTab = pTabList->a[j].pTab;
pS = pTabList->a[j].pSelect;
+  zTableAlias = 
pTabList->a[j].zAlias?pTabList->a[j].zAlias:pTabList->a[j].zName;
  }else{
pNC = pNC->pNext;
  }

Source: 
http://mailinglists.sqlite.org/cgi-bin/mailman/private/sqlite-users/2014-November/056388.html
 

 

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

Re: [sqlite] sqlite_column_table_name() and table alias name

[petern]

Jake.  Maybe somebody else can see the patch.  There's a login screen at
the linked page.
Others are using Node.js with the current version of SQLite.  Are you sure
that patch is needed?
If you can briefly explain what's not working I'm sure somebody else will
reply.
Peter


On Sun, Jan 14, 2018 at 9:17 PM, Jake Chen  wrote:

> Hi,
>
> My apologies if it's impolite to reply to such an ancient thread.
>
> I've encountered the same problem while implementing an object-relational
> mapping library for Node.js. Is there any chance that OP's patch gets
> merged into master?
>
> If this email weren't able to get related to the original post, here's the
> link http://mailinglists.sqlite.org/cgi-bin/mailman/private/
> sqlite-users/2014-November/056379.html  org/cgi-bin/mailman/private/sqlite-users/2014-November/056379.html>
>
> Jake Chen
> ___
> 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] sqlite_column_table_name() and table alias name

[Jake Chen]

Hi,

My apologies if it's impolite to reply to such an ancient thread.

I've encountered the same problem while implementing an object-relational 
mapping library for Node.js. Is there any chance that OP's patch gets merged 
into master?

If this email weren't able to get related to the original post, here's the link 
http://mailinglists.sqlite.org/cgi-bin/mailman/private/sqlite-users/2014-November/056379.html
 


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

Re: [sqlite] sqlite_column_table_name() and table alias name

[Jose F. Gimenez]

Hi,

any chance that this or a similar sqlite3_column_table_alias_name() were 
added to SQLite?


TIA,
Jose F. Gimenez
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite_column_table_name() and table alias name

[Jose F. Gimenez]

Simon,

BTW, by using "pragma full_column_names=1" and "pragma 
short_column_names=0", the sentence:


SELECT * FROM mytable AS myalias

returns the column names as myalias.col1, myalias.col2, etc., which is 
perfectly correct. But I'm aware that those pragma are deprecated. And 
in fact, if you use them, other errors arise.



Regards,
Jose F. Gimenez
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite_column_table_name() and table alias name

[Jose F. Gimenez]

Simon,

If you develop bussiness software, where you know exactly the querys because 
you are writing them, then there is no problem at all. But other kind of 
software, like a database manager, need all available metadata. And more 
metadata info is better, for sure. And this is my case now.

Yep.  That's what



is for.


No, sorry. It's not. That pragma shows information about a given table, 
and that it's ok. But it's not what I'm talking about. Please, reread my 
first post. A simple sentence like:


SELECT * FROM mytable AS myalias

causes that sqlite3_column_table_name() returns "mytable" for every 
column. And what I'm asking for is a function that returns "myalias" in 
that case. No less no more. In my previous message I attached a patch 
that adds the function sqlite3_column_table_alias_name(), which does the 
job.


IMHO, sqlite3_column_table_name() should returns the table alias, and 
there should be another function sqlite3_columns_orgtable_name() which 
returns the original table name. But now, it's not desirable to break 
backwards compatibility.


Regards,
Jose F. Gimenez
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite_column_table_name() and table alias name

[Simon Slavin]

On 2 Nov 2014, at 6:28pm, Jose F. Gimenez  wrote:

> If you develop bussiness software, where you know exactly the querys because 
> you are writing them, then there is no problem at all. But other kind of 
> software, like a database manager, need all available metadata. And more 
> metadata info is better, for sure. And this is my case now.

Yep.  That's what



is for.

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

Re: [sqlite] sqlite_column_table_name() and table alias name

[Jose F. Gimenez]

Simon,


This is from the documentation of SQLite:



"The name of a result column is the value of the "AS" clause for that column, if 
there is an AS clause. 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 do not use AS, then you have no idea what the names of your columns are. 
 You cannot even rely on each column having a different name.  You might 
upgrade to a slightly higher version of SQLite because of a bug, and get 
completely different names for the columns in your result.

If you want to have any idea about your column names at all, assign each one using an 
"AS" clause.

If you want to pass the results of "SELECT * FROM ..." to something else and don't know 
the names of your columns, don't do a "SELECT * FROM ..." in the first place.  Use



to find the names of the columns which exist, and create your SELECT from that.


If you develop bussiness software, where you know exactly the querys 
because you are writing them, then there is no problem at all. But other 
kind of software, like a database manager, need all available metadata. 
And more metadata info is better, for sure. And this is my case now.


If metadata were not useful, why exist functions as 
sqlite3_column_name(), sqlite3_column_origin_name() or 
sqlite3_column_table_name(), among many others? I know there is a 
compilation switch to enable metadata (SQLITE_ENABLE_COLUMN_METADATA), 
so, what's the problem to add such useful information that I'm asking for?


Well, at last I've tried to do myself, and I belive I've got it  ;-)

I attach a patch (aplied over sqlite-amalgamation-3080701.zip), which 
adds the functions sqlite3_column_table_alias_name() and 
sqlite3_column_table_alias_name16(). Those functions return the table 
alias for the related column, or the original table name if there is no 
alias for it. It's rather small patch, althougt I've not added those 
functions to the load_extension system, in order to not grow it and make 
it more readable. But if needed, I can make a full patch. The key change 
is this:


@@ -105364,6 +105386,7 @@
 if( jnSrc ){
   pTab = pTabList->a[j].pTab;
   pS = pTabList->a[j].pSelect;
+  zTableAlias = 
pTabList->a[j].zAlias?pTabList->a[j].zAlias:pTabList->a[j].zName;

 }else{
   pNC = pNC->pNext;
 }

The rest of changes are to accomodate that info into the actual code.

Please, I'll be very happy if it's evaluated and incorporated to sqlite 
code (if approved, of course).



Best regards,
Jose F. Gimenez

Index: sqlite3.c
===
--- sqlite3.c   (revisión: 28)
+++ sqlite3.c   (copia de trabajo)
@@ -3747,6 +3747,8 @@
 SQLITE_API const void *sqlite3_column_table_name16(sqlite3_stmt*,int);
 SQLITE_API const char *sqlite3_column_origin_name(sqlite3_stmt*,int);
 SQLITE_API const void *sqlite3_column_origin_name16(sqlite3_stmt*,int);
+SQLITE_API const char *sqlite3_column_table_alias_name(sqlite3_stmt*,int);
+SQLITE_API const void *sqlite3_column_table_alias_name16(sqlite3_stmt*,int);
 
 /*
 ** CAPI3REF: Declared Datatype Of A Query Result
@@ -9326,8 +9328,9 @@
 #define COLNAME_DATABASE 2
 #define COLNAME_TABLE3
 #define COLNAME_COLUMN   4
+#define COLNAME_TABALIAS 5
 #ifdef SQLITE_ENABLE_COLUMN_METADATA
-# define COLNAME_N5  /* Number of COLNAME_xxx symbols */
+# define COLNAME_N6  /* Number of COLNAME_xxx symbols */
 #else
 # ifdef SQLITE_OMIT_DECLTYPE
 #   define COLNAME_N  1  /* Store only the name */
@@ -68230,9 +68233,10 @@
 **2  The name of the database that the column derives from
 **3  The name of the table that the column derives from
 **4  The name of the table column that the result column derives from
+**5  The name of the table for the column as its refered in the 
sentence (maybe an alias)
 **
 ** If the result is not a simple column reference (if it is an expression
-** or a constant) then useTypes 2, 3, and 4 return NULL.
+** or a constant) then useTypes 2, 3, 4 and 5 return NULL.
 */
 static const void *columnName(
   sqlite3_stmt *pStmt,
@@ -68355,6 +68359,22 @@
 #endif /* SQLITE_OMIT_UTF16 */
 #endif /* SQLITE_ENABLE_COLUMN_METADATA */
 
+/*
+** Return the name of the table for the column (maybe an alias).
+** NULL is returned if the result column is an expression or constant or
+** anything else which is not an unambiguous reference to a database column.
+*/
+SQLITE_API const char *sqlite3_column_table_alias_name(sqlite3_stmt *pStmt, 
int N){
+  return columnName(
+  pStmt, N, (const void*(*)(Mem*))sqlite3_value_text, COLNAME_TABALIAS);
+}
+#ifndef SQLITE_OMIT_UTF16
+SQLITE_API const void *sqlite3_column_table_alias_name16(sqlite3_stmt *pStmt, 
int N){

Re: [sqlite] sqlite_column_table_name() and table alias name

[Simon Slavin]

On 1 Nov 2014, at 12:29pm, Jose F. Gimenez  wrote:

> But if I need the column's fullname, I get:
> 
> messages.subject, people.name, people.name
> 
> And yes, I know that I can specify an alias for those columns which could be 
> ambiguous. That is "sender.name AS sender_name" and "receipt.name as 
> receipt_name", but what about a query like "SELECT messages.*, sender.*, 
> receipt.* ..." which will be processed later by a reporting system that knows 
> nothing about original columns? In this case, it's absolutely needed to 
> distingish between  and ,  and 
> , and so on.

This is from the documentation of SQLite:



"The name of a result column is the value of the "AS" clause for that column, 
if there is an AS clause. 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 do not use AS, then you have no idea what the names of your columns are. 
 You cannot even rely on each column having a different name.  You might 
upgrade to a slightly higher version of SQLite because of a bug, and get 
completely different names for the columns in your result.

If you want to have any idea about your column names at all, assign each one 
using an "AS" clause.

If you want to pass the results of "SELECT * FROM ..." to something else and 
don't know the names of your columns, don't do a "SELECT * FROM ..." in the 
first place.  Use



to find the names of the columns which exist, and create your SELECT from that.

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

Re: [sqlite] sqlite_column_table_name() and table alias name

[Jose F. Gimenez]

Simon,

thanks for replying.


Specify which names you want SQLite to use:

SELECT a, b, table2.c AS table2, alias.c AS alias
FROM table1
LEFT JOIN table2 ON ...
LEFT JOIN table2 AS alias ON ...

However, I strongly advise against having one string which is both a table name and a column 
name ("table2" in your example).  I cannot right now think of a problem this will 
trigger but I suspect you're just setting yourself up for later confusion.  Perhaps use 
something like .


Well, I know I can specify every column in the query, but that's not the 
question. Perhaps I didn't explain correctly. Sorry for my bad english.


The question is that there is a query where there are two JOINs over the 
same table. Let's use a more real example: an internal message system. 
This could be a sample query:


SELECT subject, sender.name, receipt.name
FROM messages
LEFT JOIN people AS sender ON messages.idsender=people.id
LEFT JOIN people AS receipt ON messages.idreceipt=people.id

That retrieves a list for messages, with columns: , name>, . But if I need the column's fullname, I get:


messages.subject, people.name, people.name

And yes, I know that I can specify an alias for those columns which 
could be ambiguous. That is "sender.name AS sender_name" and 
"receipt.name as receipt_name", but what about a query like "SELECT 
messages.*, sender.*, receipt.* ..." which will be processed later by a 
reporting system that knows nothing about original columns? In this 
case, it's absolutely needed to distingish between  and 
,  and , and so on.


So, the question is how to get the alias table name for every column in 
the query. Of course, SQLite retrieves correctly all data for the query 
(I mean that retrieved data has columns from sender and receipt), but 
seems that it's not possible to get the alias table name. BTW, in my 
work I use both SQLite and MySQL/MariaDB, and MySQL/MariaDB allows to 
get that information (there are  and  fields in 
MYSQL_FIELD struct). I only have problems with SQLite.


TIA,
Jose F. Gimenez
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite_column_table_name() and table alias name

[Simon Slavin]

> On 31 Oct 2014, at 12:30pm, Jose F. Gimenez  wrote:
> 
> SELECT a, b, table2.c, alias.c
> FROM table1
> LEFT JOIN table2 ON ...
> LEFT JOIN table2 AS alias ON ...
> 
> the API sqlite_column_table_name() applied to both columns 3 and 4 returns 
> . I know that  belongs to  in both cases, but is there any 
> way to get  for column 3 and  for column 4?

Specify which names you want SQLite to use:

SELECT a, b, table2.c AS table2, alias.c AS alias
FROM table1
LEFT JOIN table2 ON ...
LEFT JOIN table2 AS alias ON ...

However, I strongly advise against having one string which is both a table name 
and a column name ("table2" in your example).  I cannot right now think of a 
problem this will trigger but I suspect you're just setting yourself up for 
later confusion.  Perhaps use something like .

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

[sqlite] sqlite_column_table_name() and table alias name

[Jose F. Gimenez]

Hi,

in this kind of statements:

SELECT a, b, table2.c, alias.c
FROM table1
LEFT JOIN table2 ON ...
LEFT JOIN table2 AS alias ON ...

the API sqlite_column_table_name() applied to both columns 3 and 4 
returns . I know that  belongs to  in both cases, but 
is there any way to get  for column 3 and  for column 4?


TIA,
Jose F. Gimenez
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users