Request to get alias of table

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

Request to get alias of table

J Decker
I would really like to get the table alias specified in the query, with an
unpatched version of Sqlite.


So here's a script...

------
.headers on

create table addresses( id, name );
create table users( id, address_id, name );
create table pets(id, user_id, name );

insert into addresses(id,name) values ( 1, "there" );
insert into users(id,address_id,name) values ( 1, 1, "bob" );
insert into pets(id,user_id,name) values ( 1,1, "odif" );

select * from users user join addresses address on address.id=user.address_id
join pets pet on pet.user_id=user.id;

------
This is the output, which you can see in the one case for suer multiple
values come back that are 'id'

id|address_id|name|id|name|id|user_id|name
1|1|bob|1|there|1|1|odif

Sqlite command line tool doesn't really provide all of the information
available though...

---
This is the returns of
sqlite3_column_table_name
sqlite3_column_origin_name
, and the function that doesn't exist yet : sqlite3_column_table_alias

data : 1,1,bob,1,there,1,1,odif
Column 0 : table_name:users   origin_name:id   table_alias:user
Column 1 : table_name:users   origin_name:address_id   table_alias:user
Column 2 : table_name:users   origin_name:name   table_alias:user
Column 3 : table_name:addresses   origin_name:id   table_alias:address
Column 4 : table_name:addresses   origin_name:name   table_alias:address
Column 5 : table_name:pets   origin_name:id   table_alias:pet
Column 6 : table_name:pets   origin_name:user_id   table_alias:pet
Column 7 : table_name:pets   origin_name:name   table_alias:pet


Desired output:
[
  {
    user: { id: 1, address_id: 1, name: 'bob' },
    address: { id: 1, name: 'there' },
    pet: { id: 1, user_id: 1, name: 'odif' },
    id: [ 1, 1, 1, user: 1, address: 1, pet: 1 ],
    name: [
      'bob',
      'there',
      'odif',
      user: 'bob',
      address: 'there',
      pet: 'odif'
    ]
  }
]

// in the resulting object returned to JS, I keep column names that are
duplicated as both an array...
id[0], id[1], id[2]  but also related to the table (alias) they come from.
id.name, id.pets, id.address... but conversely by table (alias) name...
name.id, address.id, pets.id

-----------

I would really like to get the table alias specified in the query, with an
unpatched version of Sqlite.
_______________________________________________
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: Request to get alias of table

J Decker
I was trying a more complex query where the table name isn't a simple table
name...

select * from ( select * from addresses join user on user.address_id=
addresses.id ) address;

but that gives like really strange column names...

# sqlite3 output
id|name|id:1|address_id|name:1
1|there|1|1|bob
# my output
[ { id: 1, name: 'there', 'id:1': 1, address_id: 1, 'name:1': 'bob' } ]


so if I were to try an do like   select * from ( select * from addresses
join user on user.address_id=addresses.id ) address join pet on pet.user_id=
address.id:1;

is a syntax error.... ok...


select * from ( select count(*)count,* from addresses join users on
users.address_id=addresses.id ) address join pets pet on
pet.user_id=address.'id:1';

-- I guess that doesn't really make a 'table' to pull from....

1,1,there,1,1,bob,1,1,odif
Column 0 : table_name:(null)   origin_name:(null)   table_alias:(null)
Column 1 : table_name:addresses   origin_name:id   table_alias:addresses
Column 2 : table_name:addresses   origin_name:name   table_alias:addresses
Column 3 : table_name:users   origin_name:id   table_alias:users
Column 4 : table_name:users   origin_name:address_id   table_alias:users
Column 5 : table_name:users   origin_name:name   table_alias:users
Column 6 : table_name:pets   origin_name:id   table_alias:pet
Column 7 : table_name:pets   origin_name:user_id   table_alias:pet
Column 8 : table_name:pets   origin_name:name   table_alias:pet

[
  {
    addresses: { id: 1, name: 'there' },
    users: { 'id:1': 1, address_id: 1, 'name:1': 'bob' },
    pet: { id: 1, user_id: 1, name: 'odif' },
    count: 1,
    id: [ 1, 1, addresses: 1, pet: 1 ],
    name: [ 'there', 'odif', addresses: 'there', pet: 'odif' ]
  }
]

On Thu, Aug 15, 2019 at 11:51 AM J Decker <[hidden email]> wrote:

> I would really like to get the table alias specified in the query, with an
> unpatched version of Sqlite.
>
>
> So here's a script...
>
> ------
> .headers on
>
> create table addresses( id, name );
> create table users( id, address_id, name );
> create table pets(id, user_id, name );
>
> insert into addresses(id,name) values ( 1, "there" );
> insert into users(id,address_id,name) values ( 1, 1, "bob" );
> insert into pets(id,user_id,name) values ( 1,1, "odif" );
>
> select * from users user join addresses address on address.id=user.address_id
> join pets pet on pet.user_id=user.id;
>
> ------
> This is the output, which you can see in the one case for suer multiple
> values come back that are 'id'
>
> id|address_id|name|id|name|id|user_id|name
> 1|1|bob|1|there|1|1|odif
>
> Sqlite command line tool doesn't really provide all of the information
> available though...
>
> ---
> This is the returns of
> sqlite3_column_table_name
> sqlite3_column_origin_name
> , and the function that doesn't exist yet : sqlite3_column_table_alias
>
> data : 1,1,bob,1,there,1,1,odif
> Column 0 : table_name:users   origin_name:id   table_alias:user
> Column 1 : table_name:users   origin_name:address_id   table_alias:user
> Column 2 : table_name:users   origin_name:name   table_alias:user
> Column 3 : table_name:addresses   origin_name:id   table_alias:address
> Column 4 : table_name:addresses   origin_name:name   table_alias:address
> Column 5 : table_name:pets   origin_name:id   table_alias:pet
> Column 6 : table_name:pets   origin_name:user_id   table_alias:pet
> Column 7 : table_name:pets   origin_name:name   table_alias:pet
>
>
> Desired output:
> [
>   {
>     user: { id: 1, address_id: 1, name: 'bob' },
>     address: { id: 1, name: 'there' },
>     pet: { id: 1, user_id: 1, name: 'odif' },
>     id: [ 1, 1, 1, user: 1, address: 1, pet: 1 ],
>     name: [
>       'bob',
>       'there',
>       'odif',
>       user: 'bob',
>       address: 'there',
>       pet: 'odif'
>     ]
>   }
> ]
>
> // in the resulting object returned to JS, I keep column names that are
> duplicated as both an array...
> id[0], id[1], id[2]  but also related to the table (alias) they come
> from.  id.name, id.pets, id.address... but conversely by table (alias)
> name... name.id, address.id, pets.id
>
> -----------
>
> I would really like to get the table alias specified in the query, with an
> unpatched version of Sqlite.
>
>
_______________________________________________
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: Request to get alias of table

Keith Medcalf
In reply to this post by J Decker
After fixing the errors in the script (strings are quoted with single quotes, not double quotes):

sqlite> .headers on
sqlite> create table addresses( id, name );
sqlite> create table users( id, address_id, name );
sqlite> create table pets(id, user_id, name );
sqlite>
sqlite> insert into addresses(id,name) values ( 1, 'there' );
sqlite> insert into users(id,address_id,name) values ( 1, 1, 'bob' );
sqlite> insert into pets(id,user_id,name) values ( 1,1, 'odif' );
sqlite>
sqlite> select * from users user join addresses address on address.id=user.address_id
   ...> join pets pet on pet.user_id=user.id;
id|address_id|name|id|name|id|user_id|name
1|1|bob|1|there|1|1|odif
sqlite> pragma full_column_names=1;
sqlite> pragma short_column_names=0;
sqlite> select * from users user join addresses address on address.id=user.address_id
   ...> join pets pet on pet.user_id=user.id;
user.id|user.address_id|user.name|address.id|address.name|pet.id|pet.user_id|pet.name
1|1|bob|1|there|1|1|odif
sqlite>

Note that the short_column_names and full_column_names pragma's are deprecated even though highly useful.  You have to turn off short_column_names (the default) in order for full_column_names to have any effect.

Note that you SHOULD be using AS to name your columns, if you care about column names, and not relying on implementation details.

--
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.




_______________________________________________
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: Request to get alias of table

J Decker
On Thu, Aug 15, 2019 at 12:24 PM Keith Medcalf <[hidden email]> wrote:

> After fixing the errors in the script (strings are quoted with single
> quotes, not double quotes):
>
sqlite> .headers on

> sqlite> create table addresses( id, name );
> sqlite> create table users( id, address_id, name );
> sqlite> create table pets(id, user_id, name );
> sqlite>
> sqlite> insert into addresses(id,name) values ( 1, 'there' );
> sqlite> insert into users(id,address_id,name) values ( 1, 1, 'bob' );
> sqlite> insert into pets(id,user_id,name) values ( 1,1, 'odif' );
> sqlite>
> sqlite> select * from users user join addresses address on address.id
> =user.address_id
>    ...> join pets pet on pet.user_id=user.id;
> id|address_id|name|id|name|id|user_id|name
> 1|1|bob|1|there|1|1|odif
> sqlite> pragma full_column_names=1;
> sqlite> pragma short_column_names=0;
> sqlite> select * from users user join addresses address on address.id
> =user.address_id
>    ...> join pets pet on pet.user_id=user.id;
> user.id|user.address_id|user.name|address.id|address.name|pet.id
> |pet.user_id|pet.name
> 1|1|bob|1|there|1|1|odif
> sqlite>
>
> Note that the short_column_names and full_column_names pragma's are
> deprecated even though highly useful.  You have to turn off
> short_column_names (the default) in order for full_column_names to have any
> effect.
>
> Note that you SHOULD be using AS to name your columns, if you care about
> column names, and not relying on implementation details.
>
>

sqlite> select * from ( select count(*)count,* from addresses join users on
users.address_id=addresses.id ) address ;
address.count|address.addresses.id|address.addresses.name|address.users.id
|address.users.address_id|address.users.name
1|1|there|1|1|bob

sqlite> select * from ( select count(*)count,* from addresses join users on
users.address_id=addresses.id ) address join pets pet on pet.user_id=
address.users.id;
Error: no such column: address.users.id

How do I fix that query to work then?


Hmm, I don't really like this as the output though... I'd rather have the
individual names without parsing...

select * from users user join addresses address on address.id=user.address_id
join pets pet on pet.user_id=user.id;
[
  {
    'user.id': 1,
    'user.address_id': 1,
    'user.name': 'bob',
    'address.id': 1,
    'address.name': 'there',
    'pet.id': 1,
    'pet.user_id': 1,
    'pet.name': 'odif'
  }
]


select * from ( select * from addresses join users on users.address_id=
addresses.id ) address
[
  {
    'address.addresses.id': 1,
    'address.addresses.name': 'there',
    'address.users.id': 1,
    'address.users.address_id': 1,
    'address.users.name': 'bob'
  }
]



Intersting, origin_name is still the short name....

Column 0 : table_name:users   origin_name:id   table_alias:user
Column 1 : table_name:users   origin_name:address_id   table_alias:user
Column 2 : table_name:users   origin_name:name   table_alias:user
Column 3 : table_name:addresses   origin_name:id   table_alias:address
Column 4 : table_name:addresses   origin_name:name   table_alias:address
Column 5 : table_name:pets   origin_name:id   table_alias:pet
Column 6 : table_name:pets   origin_name:user_id   table_alias:pet
Column 7 : table_name:pets   origin_name:name   table_alias:pet





--

> The fact that there's a Highway to Hell but only a Stairway to Heaven says
> a lot about anticipated traffic volume.
>
>
>
>
> _______________________________________________
> 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: Request to get alias of table

J Decker
On Thu, Aug 15, 2019 at 12:33 PM J Decker <[hidden email]> wrote:

>
>
> On Thu, Aug 15, 2019 at 12:24 PM Keith Medcalf <[hidden email]>
> wrote:
>
>> After fixing the errors in the script (strings are quoted with single
>> quotes, not double quotes):
>>
> .headers on
> pragma full_column_names=1;
> pragma short_column_names=0;
> create table addresses( id, name );
> create table users( id, address_id, name );
> create table pets(id, user_id, name );
>
> insert into addresses(id,name) values ( 1, 'there' );
> insert into users(id,address_id,name) values ( 1, 1, 'bob' );
> insert into pets(id,user_id,name) values ( 1,1, 'odif' );
>
> select * from users user join addresses address on address.id=user.address_id
> join pets pet on pet.user_id=user.id;
>
>> id|address_id|name|id|name|id|user_id|name
>> 1|1|bob|1|there|1|1|odif
>> sqlite> pragma full_column_names=1;
>> sqlite> pragma short_column_names=0;
>> sqlite> select * from users user join addresses address on address.id
>> =user.address_id
>>    ...> join pets pet on pet.user_id=user.id;
>> user.id|user.address_id|user.name|address.id|address.name|pet.id
>> |pet.user_id|pet.name
>> 1|1|bob|1|there|1|1|odif
>> sqlite>
>>
>> Note that the short_column_names and full_column_names pragma's are
>> deprecated even though highly useful.  You have to turn off
>> short_column_names (the default) in order for full_column_names to have any
>> effect.
>>
>> Note that you SHOULD be using AS to name your columns, if you care about
>> column names, and not relying on implementation details.
>>
>> Even if I do use AS, queries could be composed of expressions from other
queries, and eventually what someone makde 'unique' will be the same like
'count(*) AS count' , count, count, count....



select count(*)count,* from ( select count(*)count,* from addresses join
users on users.address_id=addresses.id ) address join pets pet on
pet.user_id=address.'id:1';
1,1,1,there,1,1,bob,1,1,odif
Column 0 : table_name:(null)   origin_name:(null)   table_alias:(null)
-- I would have expected 'table_name' to be (select
count(*)count,*,fromaddresses.....) and alias to be 'address'
-- I would have expected origin_name to be 'count(*)' and column_name to be
'count' (which it is)

Column 1 : table_name:(null)   origin_name:(null)   table_alias:(null)
-- I would have expected 'table_name' to be addresses and alias to be
'addresses' /* no alias, I'm not re-testing */
-- I would have expected origin_name to be 'count(*)' and column_name to be
'count' (which it is)

Column 2 : table_name:addresses   origin_name:id   table_alias:addresses
Column 3 : table_name:addresses   origin_name:name   table_alias:addresses
Column 4 : table_name:users   origin_name:id   table_alias:users
Column 5 : table_name:users   origin_name:address_id   table_alias:users
Column 6 : table_name:users   origin_name:name   table_alias:users
Column 7 : table_name:pets   origin_name:id   table_alias:pet
Column 8 : table_name:pets   origin_name:user_id   table_alias:pet
Column 9 : table_name:pets   origin_name:name   table_alias:pet

[
  {
    addresses: { id: 1, name: 'there' },
    users: { 'id:1': 1, address_id: 1, 'name:1': 'bob' },
    pet: { id: 1, user_id: 1, name: 'odif' },
/* I Have an error apparently (or feature), that if there isn't a
table/alias name, I don't split it...
  I should at least promote it to an array... */
    count: 1,
    id: [ 1, 1, addresses: 1, pet: 1 ],
    name: [ 'there', 'odif', addresses: 'there', pet: 'odif' ]
  }
]


>
>
> --
>> The fact that there's a Highway to Hell but only a Stairway to Heaven
>> says a lot about anticipated traffic volume.
>>
>>
>>
>>
>> _______________________________________________
>> 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: Request to get alias of table

J Decker
In reply to this post by J Decker
>
>
>
> sqlite> select * from ( select count(*)count,* from addresses join users
> on users.address_id=addresses.id ) address join pets pet on pet.user_id=
> address.users.id;
> Error: no such column: address.users.id
>
>
>> console.log( db.do( "select count(*)count,* from ( select count(*)count,*
from addresses join users on users.address_id=addresses.id ) address join
pets pet on pet.user_id='address.user.id';" ) );


13:34:27.325|212800001160~sql_module.cc(571):Do Command[04F1ECF4:test.db]:
select count(*)count,* from ( select count(*)count,* from addresses join
users on users.address_id=addres
ses.id ) address join pets pet on pet.user_id='address.user.id';
13:34:27.326|212800001160~sack.cc(97503):Sqlite3 Err: (284) automatic index
on users(address_id)
13:34:27.326|212800001160~sack.cc(104891):0
13:34:27.326|212800001160~sack.cc(101458):Column 0 : table_name:(null)
origin_name:(null)   table_alias:(null)
13:34:27.327|212800001160~sack.cc(101458):Column 1 : table_name:(null)
origin_name:(null)   table_alias:(null)
13:34:27.327|212800001160~sack.cc(101458):Column 2 : table_name:addresses
origin_name:id   table_alias:addresses
13:34:27.327|212800001160~sack.cc(101458):Column 3 : table_name:addresses
origin_name:name   table_alias:addresses
13:34:27.327|212800001160~sack.cc(101458):Column 4 : table_name:users
origin_name:id   table_alias:users
13:34:27.327|212800001160~sack.cc(101458):Column 5 : table_name:users
origin_name:address_id   table_alias:users
13:34:27.328|212800001160~sack.cc(101458):Column 6 : table_name:users
origin_name:name   table_alias:users
13:34:27.328|212800001160~sack.cc(101458):Column 7 : table_name:pets
origin_name:id   table_alias:pet
13:34:27.328|212800001160~sack.cc(101458):Column 8 : table_name:pets
origin_name:user_id   table_alias:pet
13:34:27.328|212800001160~sack.cc(101458):Column 9 : table_name:pets
origin_name:name   table_alias:pet
13:34:27.328|212800001160~sack.cc(104418):no data
[
  {
    count: 0,
    'address.count': null,
    'address.addresses.id': null,
    'address.addresses.name': null,
    'address.users.id': null,
    'address.users.address_id': null,
    'address.users.name': null,
    'pet.id': null,
    'pet.user_id': null,
    'pet.name': null
  }
]

Hmm... I don't get any values that way, I wonder what happens?

I suppose parsing that might be an option; not sure I can guarantee
databases will always pragma longname....
_______________________________________________
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: Request to get alias of table

Keith Medcalf
In reply to this post by Keith Medcalf

Using AS to name columns means NOT using *.  One only uses * when one does not care about column names (or the ordering of the result columns).

select address.id      AS address_id,
       address.name    AS address_name,
       user.id         AS user_id,
       user.address_id AS user_address_id,
       user.name       AS user_name,
       pet.id          AS pet_id,
       pet.user_id     AS pet_user_id,
       pet.name        AS pet_name
  from users AS user
  join addresses AS address
    on address.id == user.address_id
  join pets AS pet
    on pet.user_id == user.id;

demonstrates how to specify column names.

import apsw
db = apsw.Connection('')
db.execute('''
create table addresses( id, name );
create table users( id, address_id, name );
create table pets(id, user_id, name );
insert into addresses(id,name) values ( 1, 'there' );
insert into users(id,address_id,name) values ( 1, 1, 'bob' );
insert into pets(id,user_id,name) values ( 1,1, 'odif' );
''')
cr1 = db.execute('''
select *
  from users user
  join addresses address
    on address.id=user.address_id
  join pets pet
    on pet.user_id=user.id;')
cr2 = db.execute('''
select address.id      AS address_id,
       address.name    AS address_name,
       user.id         AS user_id,
       user.address_id AS user_address_id,
       user.name       AS user_name,
       pet.id          AS pet_id,
       pet.user_id     AS pet_user_id,
       pet.name        AS pet_name
  from users AS user
  join addresses AS address
    on address.id == user.address_id
  join pets AS pet
    on pet.user_id == user.id;
''')
cr1.fetchone()
Row(id=1, address_id=1, name=u'bob', id_1=1, name_1=u'there', id_1=1, user_id=1, name_1=u'odif')
cr2.fetchone()
Row(address_id=1, address_name=u'there', user_id=1, user_address_id=1, user_name=u'bob', pet_id=1, pet_user_id=1, pet_name=u'odif')

*Note that I have modified apsw from the default by having execute() be a method of the connection, and having data returned as a Row object, via a python wrapper.  It used to be able to handle the full_column_names but I got rid of that since it required modifying the apsw code itself to return additional column metadata.

--
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.



_______________________________________________
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: Request to get alias of table

J Decker
On Thu, Aug 15, 2019 at 2:53 PM Keith Medcalf <[hidden email]> wrote:

>
> Using AS to name columns means NOT using *.  One only uses * when one does
> not care about column names (or the ordering of the result columns).
>

Sounds like a personal gospel.

This is closer to the test case....
(yes it doesn't make a lot of sense without the additional wheres and other
context, but the structure is closer to what I'd have)

 select count(*)count,* from ( select count(*)count,* from addresses join
users on users.address_id=addresses.id ) address join pets pet on
pet.user_id='address.user.id';

from a test driven development viewpoint, I want to see what's available
Then I can later refine the query.

Second though, Providing the ability to get table alias I can make queries
that reference different tables in reality but appear to have the same
results as other queries.

The query itself does a pretty good job of defining the structure of the
data itself even with *.





>
> --
> The fact that there's a Highway to Hell but only a Stairway to Heaven says
> a lot about anticipated traffic volume.
>
>
>
> _______________________________________________
> 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: Request to get alias of table

Keith Medcalf
In reply to this post by Keith Medcalf

Aha!  Found and fixed some bugs in my apsw wrapper for stuff that I do not do often (such as using * to retrieve duplicate column names as I typically always use AS to name columns explicitly, so the Row was being built with duplicate names).  Also, removed the code that disabled the long_column_names support as it does not actually require changes to the APSW base, those only being required if I want to fetch the underlying column metadata).

from __future__ import absolute_import, division, print_function, unicode_literals
import newapsw
import apsw
db = apsw.Connection('')
db.execute('''
create table addresses( id, name );
create table users( id, address_id, name );
create table pets(id, user_id, name );
insert into addresses(id,name) values ( 1, 'there' );
insert into users(id,address_id,name) values ( 1, 1, 'bob' );
insert into pets(id,user_id,name) values ( 1,1, 'odif' );
''')
cr1 = db.execute('''
select *
  from users user
  join addresses address
    on address.id=user.address_id
  join pets pet
    on pet.user_id=user.id;
''')
print(1, cr1.fetchone())
cr2 = db.execute('''
select address.id      AS address_id,
       address.name    AS address_name,
       user.id         AS user_id,
       user.address_id AS user_address_id,
       user.name       AS user_name,
       pet.id          AS pet_id,
       pet.user_id     AS pet_user_id,
       pet.name        AS pet_name
  from users AS user
  join addresses AS address
    on address.id == user.address_id
  join pets AS pet
    on pet.user_id == user.id;
''')
print(2, cr2.fetchone())
db.execute('''
pragma full_column_names=1;
pragma short_column_names=0;
''')
cr3 = db.execute('''
select *
  from users user
  join addresses address
    on address.id=user.address_id
  join pets pet
    on pet.user_id=user.id;
''')
print(3, cr3.fetchone())

>py -3 sample.py
1 Row(id=1, address_id=1, name='bob', id_1=1, name_1='there', id_2=1, user_id=1, name_2='odif')
2 Row(address_id=1, address_name='there', user_id=1, user_address_id=1, user_name='bob', pet_id=1, pet_user_id=1, pet_name='odif')
3 Row(user_id=1, user_address_id=1, user_name='bob', address_id=1, address_name='there', pet_id=1, pet_user_id=1, pet_name='odif')

Note:  
db.execute => db.cursor().execute
The Row object is just built by an exehook and a rowhook getting the column names from the cursor.description and mangling them somewhat so that the Row has distinct names in its list of column names so that the column name can be accessed as an attribute of the Row ...
If you want to see how it all works get http://www.dessus.com/files/<name> where <name> is one of apsw-monolith27-gcc32.zip, apsw-monolith36-gcc64.zip, apsw-monolith37-gcc64.zip.  The python code is the same in all of them, just the compiled apsw pyd is different.  import newapsw to activate all the stuff, then import apsw will have it all in the normal apsw library (the newapsw module is substituted for the apsw module name in sys.modules).

--
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.



_______________________________________________
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: Request to get alias of table

Keith Medcalf
In reply to this post by J Decker

The query does not work because it is defective.  The column name pragma's only affect the column names that your program sees, not the names used internally in the SQL statement.  It does not affect the identifier use internally.  If your internal identifier use is defective, then it is defective and only fixing the defect will fix it.  In this particular case it can ONLY be fixed by specifying appropriate column names using AS.
 
In other words DO NOT use * unless you are absolutely certain that the column names are unique.  This is not a "personal peeve", this is how SQL works.  Do not use *.  Period.  End of story.  This is almost always one of the very first things one learns when using SQL.  This is because if something gets changed then the meaning of * gets changed.  If the query optimizer decides to do things differently (in a different order), the results will be different.

Using * means that you are relying on happenstance rather than actually writing what you mean.

select * from ( select count(*)count,* from addresses join users
on users.address_id=addresses.id ) address join pets pet on pet.user_id=
address.users.id;

The subquery "select count(*) count,* from addresses join users on users.address_id=addresses.id" has duplicated column names, and there is nothing that you can do about this except use AS to give specific distinct names to the columns.  You then alias this as a tableform entity called address.  There are two id columns in address, and there is no way that you can specify in the outer query which one you want other than by giving them unique names in the inner query by listing explicit columns and giving them unique aliases using AS.

Like I said, if you care about column names then give the columns names, and if you need to distinguish between two columns with the same name, give them different aliases.

--
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.


>-----Original Message-----
>From: sqlite-users [mailto:sqlite-users-
>[hidden email]] On Behalf Of J Decker
>Sent: Thursday, 15 August, 2019 14:37
>To: SQLite mailing list
>Subject: Re: [sqlite] Request to get alias of table
>
>>
>>
>>
>> sqlite> select * from ( select count(*)count,* from addresses join
>users
>> on users.address_id=addresses.id ) address join pets pet on
>pet.user_id=
>> address.users.id;
>> Error: no such column: address.users.id
>>
>>
>>> console.log( db.do( "select count(*)count,* from ( select
>count(*)count,*
>from addresses join users on users.address_id=addresses.id ) address
>join
>pets pet on pet.user_id='address.user.id';" ) );
>
>
>13:34:27.325|212800001160~sql_module.cc(571):Do
>Command[04F1ECF4:test.db]:
>select count(*)count,* from ( select count(*)count,* from addresses
>join
>users on users.address_id=addres
>ses.id ) address join pets pet on pet.user_id='address.user.id';
>13:34:27.326|212800001160~sack.cc(97503):Sqlite3 Err: (284) automatic
>index
>on users(address_id)
>13:34:27.326|212800001160~sack.cc(104891):0
>13:34:27.326|212800001160~sack.cc(101458):Column 0 :
>table_name:(null)
>origin_name:(null)   table_alias:(null)
>13:34:27.327|212800001160~sack.cc(101458):Column 1 :
>table_name:(null)
>origin_name:(null)   table_alias:(null)
>13:34:27.327|212800001160~sack.cc(101458):Column 2 :
>table_name:addresses
>origin_name:id   table_alias:addresses
>13:34:27.327|212800001160~sack.cc(101458):Column 3 :
>table_name:addresses
>origin_name:name   table_alias:addresses
>13:34:27.327|212800001160~sack.cc(101458):Column 4 : table_name:users
>origin_name:id   table_alias:users
>13:34:27.327|212800001160~sack.cc(101458):Column 5 : table_name:users
>origin_name:address_id   table_alias:users
>13:34:27.328|212800001160~sack.cc(101458):Column 6 : table_name:users
>origin_name:name   table_alias:users
>13:34:27.328|212800001160~sack.cc(101458):Column 7 : table_name:pets
>origin_name:id   table_alias:pet
>13:34:27.328|212800001160~sack.cc(101458):Column 8 : table_name:pets
>origin_name:user_id   table_alias:pet
>13:34:27.328|212800001160~sack.cc(101458):Column 9 : table_name:pets
>origin_name:name   table_alias:pet
>13:34:27.328|212800001160~sack.cc(104418):no data
>[
>  {
>    count: 0,
>    'address.count': null,
>    'address.addresses.id': null,
>    'address.addresses.name': null,
>    'address.users.id': null,
>    'address.users.address_id': null,
>    'address.users.name': null,
>    'pet.id': null,
>    'pet.user_id': null,
>    'pet.name': null
>  }
>]
>
>Hmm... I don't get any values that way, I wonder what happens?
>
>I suppose parsing that might be an option; not sure I can guarantee
>databases will always pragma longname....
>_______________________________________________
>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: Request to get alias of table

J Decker
On Thu, Aug 15, 2019 at 4:52 PM Keith Medcalf <[hidden email]> wrote:

>
> The query does not work because it is defective.  The column name pragma's
> only affect the column names that your program sees, not the names used
> internally in the SQL statement.  It does not affect the identifier use
> internally.  If your internal identifier use is defective, then it is
> defective and only fixing the defect will fix it.  In this particular case
> it can ONLY be fixed by specifying appropriate column names using AS.
>
> pragma full_column_names=1;
pragma short_column_names=0;
does change how SQL deals with it.

Without this query works
select count(*)count,* from ( select count(*)count,* from addresses join
users on users.address_id=addresses.id ) address join pets pet on
pet.user_id=address.'id:1';

and with, it doesn't seem to be able to be referenced... you're right, I
did end up making it a string isntead of a colum name, quoting with ``
didn't work either.



> In other words DO NOT use * unless you are absolutely certain that the
> column names are unique.  This is not a "personal peeve", this is how SQL
> works.  Do not use *.  Period.  End of story.  This is almost always one of
> the very first things one learns when using SQL.  This is because if
> something gets changed then the meaning of * gets changed.  If the query
> optimizer decides to do things differently (in a different order), the
> results will be different.
>
> Using * means that you are relying on happenstance rather than actually
> writing what you mean.
>
> select * from ( select count(*)count,* from addresses join users
> on users.address_id=addresses.id ) address join pets pet on pet.user_id=
> address.users.id;
>
> The subquery "select count(*) count,* from addresses join users on
> users.address_id=addresses.id" has duplicated column names, and there is
> nothing that you can do about this except use AS to give specific distinct
> names to the columns.  You then alias this as a tableform entity called
> address.  There are two id columns in address, and there is no way that you
> can specify in the outer query which one you want other than by giving them
> unique names in the inner query by listing explicit columns and giving them
> unique aliases using AS.
>
> Like I said, if you care about column names then give the columns names,
> and if you need to distinguish between two columns with the same name, give
> them different aliases.
>

they already have different aliases, based on where they come from.  For
instance the long name option knows that count is not duplicated.
The query has lots of structure that keeps duplicated values separate...

  {
    count: 1,
    'address.count': 1,
    'address.addresses.id': 1,
    'address.addresses.name': 'there',
    'address.users.id': 1,
    'address.users.address_id': 1,
    'address.users.name': 'bob'
  }



>
> --
> The fact that there's a Highway to Hell but only a Stairway to Heaven says
> a lot about anticipated traffic volume.
>
>
> >-----Original Message-----
> >From: sqlite-users [mailto:sqlite-users-
> >[hidden email]] On Behalf Of J Decker
> >Sent: Thursday, 15 August, 2019 14:37
> >To: SQLite mailing list
> >Subject: Re: [sqlite] Request to get alias of table
> >
> >>
> >>
> >>
> >> sqlite> select * from ( select count(*)count,* from addresses join
> >users
> >> on users.address_id=addresses.id ) address join pets pet on
> >pet.user_id=
> >> address.users.id;
> >> Error: no such column: address.users.id
> >>
> >>
> >>> console.log( db.do( "select count(*)count,* from ( select
> >count(*)count,*
> >from addresses join users on users.address_id=addresses.id ) address
> >join
> >pets pet on pet.user_id='address.user.id';" ) );
> >
> >
> >13:34:27.325|212800001160~sql_module.cc(571):Do
> >Command[04F1ECF4:test.db]:
> >select count(*)count,* from ( select count(*)count,* from addresses
> >join
> >users on users.address_id=addres
> >ses.id ) address join pets pet on pet.user_id='address.user.id';
> >13:34:27.326|212800001160~sack.cc(97503):Sqlite3 Err: (284) automatic
> >index
> >on users(address_id)
> >13:34:27.326|212800001160~sack.cc(104891):0
> >13:34:27.326|212800001160~sack.cc(101458):Column 0 :
> >table_name:(null)
> >origin_name:(null)   table_alias:(null)
> >13:34:27.327|212800001160~sack.cc(101458):Column 1 :
> >table_name:(null)
> >origin_name:(null)   table_alias:(null)
> >13:34:27.327|212800001160~sack.cc(101458):Column 2 :
> >table_name:addresses
> >origin_name:id   table_alias:addresses
> >13:34:27.327|212800001160~sack.cc(101458):Column 3 :
> >table_name:addresses
> >origin_name:name   table_alias:addresses
> >13:34:27.327|212800001160~sack.cc(101458):Column 4 : table_name:users
> >origin_name:id   table_alias:users
> >13:34:27.327|212800001160~sack.cc(101458):Column 5 : table_name:users
> >origin_name:address_id   table_alias:users
> >13:34:27.328|212800001160~sack.cc(101458):Column 6 : table_name:users
> >origin_name:name   table_alias:users
> >13:34:27.328|212800001160~sack.cc(101458):Column 7 : table_name:pets
> >origin_name:id   table_alias:pet
> >13:34:27.328|212800001160~sack.cc(101458):Column 8 : table_name:pets
> >origin_name:user_id   table_alias:pet
> >13:34:27.328|212800001160~sack.cc(101458):Column 9 : table_name:pets
> >origin_name:name   table_alias:pet
> >13:34:27.328|212800001160~sack.cc(104418):no data
> >[
> >  {
> >    count: 0,
> >    'address.count': null,
> >    'address.addresses.id': null,
> >    'address.addresses.name': null,
> >    'address.users.id': null,
> >    'address.users.address_id': null,
> >    'address.users.name': null,
> >    'pet.id': null,
> >    'pet.user_id': null,
> >    'pet.name': null
> >  }
> >]
> >
> >Hmm... I don't get any values that way, I wonder what happens?
> >
> >I suppose parsing that might be an option; not sure I can guarantee
> >databases will always pragma longname....
> >_______________________________________________
> >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: Request to get alias of table

Keith Medcalf

With long names turned on the columns are named differently when returned though the aliased subquery.  Since a dot (.) is the separator between the schema.table or table.column and the column name contains an embedded dot, you have to quote the name ... This is probably why long_column_names was deprecated.

select count(*)count,* from ( select count(*)count,* from addresses join
users on users.address_id=addresses.id ) address join pets pet on
pet.user_id=address.'users.id';

Row(count=1, address_count=1, address_addresses_id=1, address_addresses_name='there', address_users_id=1, address_users_address_id=1, address_users_name='bob', pet_id=1, pet_user_id=1, pet_name='odif')

select count(*)count,* from ( select count(*)count,* from addresses join
users on users.address_id=addresses.id ) address join pets pet on
pet.user_id=address.[users.id];

Row(count=1, address_count=1, address_addresses_id=1, address_addresses_name='there', address_users_id=1, address_users_address_id=1, address_users_name='bob', pet_id=1, pet_user_id=1, pet_name='odif')

select count(*)count,* from ( select count(*)count,* from addresses join
users on users.address_id=addresses.id ) address join pets pet on
pet.user_id=address."users.id";

Row(count=1, address_count=1, address_addresses_id=1, address_addresses_name='there', address_users_id=1, address_users_address_id=1, address_users_name='bob', pet_id=1, pet_user_id=1, pet_name='odif')

--
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.


>-----Original Message-----
>From: sqlite-users [mailto:sqlite-users-
>[hidden email]] On Behalf Of J Decker
>Sent: Thursday, 15 August, 2019 19:19
>To: SQLite mailing list
>Subject: Re: [sqlite] Request to get alias of table
>
>On Thu, Aug 15, 2019 at 4:52 PM Keith Medcalf <[hidden email]>
>wrote:
>
>>
>> The query does not work because it is defective.  The column name
>pragma's
>> only affect the column names that your program sees, not the names
>used
>> internally in the SQL statement.  It does not affect the identifier
>use
>> internally.  If your internal identifier use is defective, then it
>is
>> defective and only fixing the defect will fix it.  In this
>particular case
>> it can ONLY be fixed by specifying appropriate column names using
>AS.
>>
>> pragma full_column_names=1;
>pragma short_column_names=0;
>does change how SQL deals with it.
>
>Without this query works
>select count(*)count,* from ( select count(*)count,* from addresses
>join
>users on users.address_id=addresses.id ) address join pets pet on
>pet.user_id=address.'id:1';
>
>and with, it doesn't seem to be able to be referenced... you're
>right, I
>did end up making it a string isntead of a colum name, quoting with
>``
>didn't work either.
>
>
>
>> In other words DO NOT use * unless you are absolutely certain that
>the
>> column names are unique.  This is not a "personal peeve", this is
>how SQL
>> works.  Do not use *.  Period.  End of story.  This is almost
>always one of
>> the very first things one learns when using SQL.  This is because
>if
>> something gets changed then the meaning of * gets changed.  If the
>query
>> optimizer decides to do things differently (in a different order),
>the
>> results will be different.
>>
>> Using * means that you are relying on happenstance rather than
>actually
>> writing what you mean.
>>
>> select * from ( select count(*)count,* from addresses join users
>> on users.address_id=addresses.id ) address join pets pet on
>pet.user_id=
>> address.users.id;
>>
>> The subquery "select count(*) count,* from addresses join users on
>> users.address_id=addresses.id" has duplicated column names, and
>there is
>> nothing that you can do about this except use AS to give specific
>distinct
>> names to the columns.  You then alias this as a tableform entity
>called
>> address.  There are two id columns in address, and there is no way
>that you
>> can specify in the outer query which one you want other than by
>giving them
>> unique names in the inner query by listing explicit columns and
>giving them
>> unique aliases using AS.
>>
>> Like I said, if you care about column names then give the columns
>names,
>> and if you need to distinguish between two columns with the same
>name, give
>> them different aliases.
>>
>
>they already have different aliases, based on where they come from.
>For
>instance the long name option knows that count is not duplicated.
>The query has lots of structure that keeps duplicated values
>separate...
>
>  {
>    count: 1,
>    'address.count': 1,
>    'address.addresses.id': 1,
>    'address.addresses.name': 'there',
>    'address.users.id': 1,
>    'address.users.address_id': 1,
>    'address.users.name': 'bob'
>  }
>
>
>
>>
>> --
>> The fact that there's a Highway to Hell but only a Stairway to
>Heaven says
>> a lot about anticipated traffic volume.
>>
>>
>> >-----Original Message-----
>> >From: sqlite-users [mailto:sqlite-users-
>> >[hidden email]] On Behalf Of J Decker
>> >Sent: Thursday, 15 August, 2019 14:37
>> >To: SQLite mailing list
>> >Subject: Re: [sqlite] Request to get alias of table
>> >
>> >>
>> >>
>> >>
>> >> sqlite> select * from ( select count(*)count,* from addresses
>join
>> >users
>> >> on users.address_id=addresses.id ) address join pets pet on
>> >pet.user_id=
>> >> address.users.id;
>> >> Error: no such column: address.users.id
>> >>
>> >>
>> >>> console.log( db.do( "select count(*)count,* from ( select
>> >count(*)count,*
>> >from addresses join users on users.address_id=addresses.id )
>address
>> >join
>> >pets pet on pet.user_id='address.user.id';" ) );
>> >
>> >
>> >13:34:27.325|212800001160~sql_module.cc(571):Do
>> >Command[04F1ECF4:test.db]:
>> >select count(*)count,* from ( select count(*)count,* from
>addresses
>> >join
>> >users on users.address_id=addres
>> >ses.id ) address join pets pet on pet.user_id='address.user.id';
>> >13:34:27.326|212800001160~sack.cc(97503):Sqlite3 Err: (284)
>automatic
>> >index
>> >on users(address_id)
>> >13:34:27.326|212800001160~sack.cc(104891):0
>> >13:34:27.326|212800001160~sack.cc(101458):Column 0 :
>> >table_name:(null)
>> >origin_name:(null)   table_alias:(null)
>> >13:34:27.327|212800001160~sack.cc(101458):Column 1 :
>> >table_name:(null)
>> >origin_name:(null)   table_alias:(null)
>> >13:34:27.327|212800001160~sack.cc(101458):Column 2 :
>> >table_name:addresses
>> >origin_name:id   table_alias:addresses
>> >13:34:27.327|212800001160~sack.cc(101458):Column 3 :
>> >table_name:addresses
>> >origin_name:name   table_alias:addresses
>> >13:34:27.327|212800001160~sack.cc(101458):Column 4 :
>table_name:users
>> >origin_name:id   table_alias:users
>> >13:34:27.327|212800001160~sack.cc(101458):Column 5 :
>table_name:users
>> >origin_name:address_id   table_alias:users
>> >13:34:27.328|212800001160~sack.cc(101458):Column 6 :
>table_name:users
>> >origin_name:name   table_alias:users
>> >13:34:27.328|212800001160~sack.cc(101458):Column 7 :
>table_name:pets
>> >origin_name:id   table_alias:pet
>> >13:34:27.328|212800001160~sack.cc(101458):Column 8 :
>table_name:pets
>> >origin_name:user_id   table_alias:pet
>> >13:34:27.328|212800001160~sack.cc(101458):Column 9 :
>table_name:pets
>> >origin_name:name   table_alias:pet
>> >13:34:27.328|212800001160~sack.cc(104418):no data
>> >[
>> >  {
>> >    count: 0,
>> >    'address.count': null,
>> >    'address.addresses.id': null,
>> >    'address.addresses.name': null,
>> >    'address.users.id': null,
>> >    'address.users.address_id': null,
>> >    'address.users.name': null,
>> >    'pet.id': null,
>> >    'pet.user_id': null,
>> >    'pet.name': null
>> >  }
>> >]
>> >
>> >Hmm... I don't get any values that way, I wonder what happens?
>> >
>> >I suppose parsing that might be an option; not sure I can
>guarantee
>> >databases will always pragma longname....
>> >_______________________________________________
>> >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
Reply | Threaded
Open this post in threaded view
|

Re: Request to get alias of table

J Decker
On Thu, Aug 15, 2019 at 7:13 PM Keith Medcalf <[hidden email]> wrote:

>
> With long names turned on the columns are named differently when returned
> though the aliased subquery.  Since a dot (.) is the separator between the
> schema.table or table.column and the column name contains an embedded dot,
> you have to quote the name ... This is probably why long_column_names was
> deprecated.
>
> select count(*)count,* from ( select count(*)count,* from addresses join
> users on users.address_id=addresses.id ) address join pets pet on
> pet.user_id=address.'users.id';
>
> Row(count=1, address_count=1, address_addresses_id=1,
> address_addresses_name='there', address_users_id=1,
> address_users_address_id=1, address_users_name='bob', pet_id=1,
> pet_user_id=1, pet_name='odif')
>
> select count(*)count,* from ( select count(*)count,* from addresses join
> users on users.address_id=addresses.id ) address join pets pet on
> pet.user_id=address.[users.id];
>
> Row(count=1, address_count=1, address_addresses_id=1,
> address_addresses_name='there', address_users_id=1,
> address_users_address_id=1, address_users_name='bob', pet_id=1,
> pet_user_id=1, pet_name='odif')
>
> select count(*)count,* from ( select count(*)count,* from addresses join
> users on users.address_id=addresses.id ) address join pets pet on
> pet.user_id=address."users.id";
>
> Row(count=1, address_count=1, address_addresses_id=1,
> address_addresses_name='there', address_users_id=1,
> address_users_address_id=1, address_users_name='bob', pet_id=1,
> pet_user_id=1, pet_name='odif')
>
>
None of this removes the desire to get the alias of tables specified in
queries.
Even though queries eventually end up flat and column-aliased in the end,
during development, it's still nice to get structured records from the
database; and pragma long_column_names(whatever) changes the query
behavior, so that's not an option to use without breaking old stuff.


> --
> The fact that there's a Highway to Hell but only a Stairway to Heaven says
> a lot about anticipated traffic volume.
>
>
> >-----Original Message-----
> >From: sqlite-users [mailto:sqlite-users-
> >[hidden email]] On Behalf Of J Decker
> >Sent: Thursday, 15 August, 2019 19:19
> >To: SQLite mailing list
> >Subject: Re: [sqlite] Request to get alias of table
> >
> >On Thu, Aug 15, 2019 at 4:52 PM Keith Medcalf <[hidden email]>
> >wrote:
> >
> >>
> >> The query does not work because it is defective.  The column name
> >pragma's
> >> only affect the column names that your program sees, not the names
> >used
> >> internally in the SQL statement.  It does not affect the identifier
> >use
> >> internally.  If your internal identifier use is defective, then it
> >is
> >> defective and only fixing the defect will fix it.  In this
> >particular case
> >> it can ONLY be fixed by specifying appropriate column names using
> >AS.
> >>
> >> pragma full_column_names=1;
> >pragma short_column_names=0;
> >does change how SQL deals with it.
> >
> >Without this query works
> >select count(*)count,* from ( select count(*)count,* from addresses
> >join
> >users on users.address_id=addresses.id ) address join pets pet on
> >pet.user_id=address.'id:1';
> >
> >and with, it doesn't seem to be able to be referenced... you're
> >right, I
> >did end up making it a string isntead of a colum name, quoting with
> >``
> >didn't work either.
> >
> >
> >
> >> In other words DO NOT use * unless you are absolutely certain that
> >the
> >> column names are unique.  This is not a "personal peeve", this is
> >how SQL
> >> works.  Do not use *.  Period.  End of story.  This is almost
> >always one of
> >> the very first things one learns when using SQL.  This is because
> >if
> >> something gets changed then the meaning of * gets changed.  If the
> >query
> >> optimizer decides to do things differently (in a different order),
> >the
> >> results will be different.
> >>
> >> Using * means that you are relying on happenstance rather than
> >actually
> >> writing what you mean.
> >>
> >> select * from ( select count(*)count,* from addresses join users
> >> on users.address_id=addresses.id ) address join pets pet on
> >pet.user_id=
> >> address.users.id;
> >>
> >> The subquery "select count(*) count,* from addresses join users on
> >> users.address_id=addresses.id" has duplicated column names, and
> >there is
> >> nothing that you can do about this except use AS to give specific
> >distinct
> >> names to the columns.  You then alias this as a tableform entity
> >called
> >> address.  There are two id columns in address, and there is no way
> >that you
> >> can specify in the outer query which one you want other than by
> >giving them
> >> unique names in the inner query by listing explicit columns and
> >giving them
> >> unique aliases using AS.
> >>
> >> Like I said, if you care about column names then give the columns
> >names,
> >> and if you need to distinguish between two columns with the same
> >name, give
> >> them different aliases.
> >>
> >
> >they already have different aliases, based on where they come from.
> >For
> >instance the long name option knows that count is not duplicated.
> >The query has lots of structure that keeps duplicated values
> >separate...
> >
> >  {
> >    count: 1,
> >    'address.count': 1,
> >    'address.addresses.id': 1,
> >    'address.addresses.name': 'there',
> >    'address.users.id': 1,
> >    'address.users.address_id': 1,
> >    'address.users.name': 'bob'
> >  }
> >
> >
> >
> >>
> >> --
> >> The fact that there's a Highway to Hell but only a Stairway to
> >Heaven says
> >> a lot about anticipated traffic volume.
> >>
> >>
> >> >-----Original Message-----
> >> >From: sqlite-users [mailto:sqlite-users-
> >> >[hidden email]] On Behalf Of J Decker
> >> >Sent: Thursday, 15 August, 2019 14:37
> >> >To: SQLite mailing list
> >> >Subject: Re: [sqlite] Request to get alias of table
> >> >
> >> >>
> >> >>
> >> >>
> >> >> sqlite> select * from ( select count(*)count,* from addresses
> >join
> >> >users
> >> >> on users.address_id=addresses.id ) address join pets pet on
> >> >pet.user_id=
> >> >> address.users.id;
> >> >> Error: no such column: address.users.id
> >> >>
> >> >>
> >> >>> console.log( db.do( "select count(*)count,* from ( select
> >> >count(*)count,*
> >> >from addresses join users on users.address_id=addresses.id )
> >address
> >> >join
> >> >pets pet on pet.user_id='address.user.id';" ) );
> >> >
> >> >
> >> >13:34:27.325|212800001160~sql_module.cc(571):Do
> >> >Command[04F1ECF4:test.db]:
> >> >select count(*)count,* from ( select count(*)count,* from
> >addresses
> >> >join
> >> >users on users.address_id=addres
> >> >ses.id ) address join pets pet on pet.user_id='address.user.id';
> >> >13:34:27.326|212800001160~sack.cc(97503):Sqlite3 Err: (284)
> >automatic
> >> >index
> >> >on users(address_id)
> >> >13:34:27.326|212800001160~sack.cc(104891):0
> >> >13:34:27.326|212800001160~sack.cc(101458):Column 0 :
> >> >table_name:(null)
> >> >origin_name:(null)   table_alias:(null)
> >> >13:34:27.327|212800001160~sack.cc(101458):Column 1 :
> >> >table_name:(null)
> >> >origin_name:(null)   table_alias:(null)
> >> >13:34:27.327|212800001160~sack.cc(101458):Column 2 :
> >> >table_name:addresses
> >> >origin_name:id   table_alias:addresses
> >> >13:34:27.327|212800001160~sack.cc(101458):Column 3 :
> >> >table_name:addresses
> >> >origin_name:name   table_alias:addresses
> >> >13:34:27.327|212800001160~sack.cc(101458):Column 4 :
> >table_name:users
> >> >origin_name:id   table_alias:users
> >> >13:34:27.327|212800001160~sack.cc(101458):Column 5 :
> >table_name:users
> >> >origin_name:address_id   table_alias:users
> >> >13:34:27.328|212800001160~sack.cc(101458):Column 6 :
> >table_name:users
> >> >origin_name:name   table_alias:users
> >> >13:34:27.328|212800001160~sack.cc(101458):Column 7 :
> >table_name:pets
> >> >origin_name:id   table_alias:pet
> >> >13:34:27.328|212800001160~sack.cc(101458):Column 8 :
> >table_name:pets
> >> >origin_name:user_id   table_alias:pet
> >> >13:34:27.328|212800001160~sack.cc(101458):Column 9 :
> >table_name:pets
> >> >origin_name:name   table_alias:pet
> >> >13:34:27.328|212800001160~sack.cc(104418):no data
> >> >[
> >> >  {
> >> >    count: 0,
> >> >    'address.count': null,
> >> >    'address.addresses.id': null,
> >> >    'address.addresses.name': null,
> >> >    'address.users.id': null,
> >> >    'address.users.address_id': null,
> >> >    'address.users.name': null,
> >> >    'pet.id': null,
> >> >    'pet.user_id': null,
> >> >    'pet.name': null
> >> >  }
> >> >]
> >> >
> >> >Hmm... I don't get any values that way, I wonder what happens?
> >> >
> >> >I suppose parsing that might be an option; not sure I can
> >guarantee
> >> >databases will always pragma longname....
> >> >_______________________________________________
> >> >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
>
_______________________________________________
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: Request to get alias of table

J Decker
Okay let's start with, I originally had an API compatible with ODBC, which
the third information parameter is 'The name of the table, view, alias, or
synonym.'

https://www.ibm.com/support/knowledgecenter/en/SSEPEK_11.0.0/odbc/src/tpc/db2z_fncolumns.html



Column 1 TABLE_CAT (VARCHAR(128))The name of the catalog. The value is NULL
if this table does not have catalogs.Column 2 TABLE_SCHEM (VARCHAR(128))The
name of the schema containing TABLE_NAME.Column 3 TABLE_NAME (VARCHAR(128)
not NULL)The name of the table, view, alias, or synonym.Column 4
COLUMN_NAME (VARCHAR(128) not NULL)The column identifier. The name of the
column of the specified table, view, alias, or synonym.Column 5 DATA_TYPE
(SMALLINT not NULL)The SQL data type of the column that is identified by
COLUMN_NAME. The DATA_TYPE is one of the values in the Symbolic SQL Data
Type column in the table of symbolic and default data types for CLI.Column
6 TYPE_NAME (VARCHAR(128) not NULL)A character string that represents the
name of the data type that corresponds to DATA_TYPE.Column 7 COLUMN_SIZE
(INTEGER)
_______________________________________________
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: Request to get alias of table

J Decker
And sorry for the Spam.  But, having had cake, I really do like cake, and i
do beleive I have deliberatly forced a query to result with an object of
group:{} and users: {} information in the group.

But, having worked motstly with MySQL/MSSQL through ODBC, that's why I
would expect

select count(*) count from table alias

to have a origin column name of 'count(*)' (not null), an alias of 'count'
, a table name of 'table' and a table alias of 'alias'.

Although the ODBC stuff doesn't actually give you the choice to get the
origin names of things.




On Sun, Aug 18, 2019 at 5:01 PM J Decker <[hidden email]> wrote:

> Okay let's start with, I originally had an API compatible with ODBC, which
> the third information parameter is 'The name of the table, view, alias,
> or synonym.'
>
>
> https://www.ibm.com/support/knowledgecenter/en/SSEPEK_11.0.0/odbc/src/tpc/db2z_fncolumns.html
>
>
>
> Column 1 TABLE_CAT (VARCHAR(128))The name of the catalog. The value is
> NULL if this table does not have catalogs.Column 2 TABLE_SCHEM
> (VARCHAR(128))The name of the schema containing TABLE_NAME.Column 3
> TABLE_NAME (VARCHAR(128) not NULL)The name of the table, view, alias, or
> synonym.Column 4 COLUMN_NAME (VARCHAR(128) not NULL)The column
> identifier. The name of the column of the specified table, view, alias, or
> synonym.Column 5 DATA_TYPE (SMALLINT not NULL)The SQL data type of the
> column that is identified by COLUMN_NAME. The DATA_TYPE is one of the
> values in the Symbolic SQL Data Type column in the table of symbolic and
> default data types for CLI.Column 6 TYPE_NAME (VARCHAR(128) not NULL)A
> character string that represents the name of the data type that corresponds
> to DATA_TYPE.Column 7 COLUMN_SIZE (INTEGER)
>
>
_______________________________________________
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: Request to get alias of table

Keith Medcalf

And what would you expect the column, alias, and table alias, and origin table to be for the following query:

select * from (select * from ( select * from (select 1, 2, 3) ta) as tb) tc;

--
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.

>-----Original Message-----
>From: sqlite-users [mailto:sqlite-users-
>[hidden email]] On Behalf Of J Decker
>Sent: Sunday, 18 August, 2019 18:14
>To: SQLite mailing list
>Subject: Re: [sqlite] Request to get alias of table
>
>And sorry for the Spam.  But, having had cake, I really do like cake,
>and i
>do beleive I have deliberatly forced a query to result with an object
>of
>group:{} and users: {} information in the group.
>
>But, having worked motstly with MySQL/MSSQL through ODBC, that's why
>I
>would expect
>
>select count(*) count from table alias
>
>to have a origin column name of 'count(*)' (not null), an alias of
>'count'
>, a table name of 'table' and a table alias of 'alias'.
>
>Although the ODBC stuff doesn't actually give you the choice to get
>the
>origin names of things.
>
>
>
>
>On Sun, Aug 18, 2019 at 5:01 PM J Decker <[hidden email]> wrote:
>
>> Okay let's start with, I originally had an API compatible with
>ODBC, which
>> the third information parameter is 'The name of the table, view,
>alias,
>> or synonym.'
>>
>>
>>
>https://www.ibm.com/support/knowledgecenter/en/SSEPEK_11.0.0/odbc/src
>/tpc/db2z_fncolumns.html
>>
>>
>>
>> Column 1 TABLE_CAT (VARCHAR(128))The name of the catalog. The value
>is
>> NULL if this table does not have catalogs.Column 2 TABLE_SCHEM
>> (VARCHAR(128))The name of the schema containing TABLE_NAME.Column 3
>> TABLE_NAME (VARCHAR(128) not NULL)The name of the table, view,
>alias, or
>> synonym.Column 4 COLUMN_NAME (VARCHAR(128) not NULL)The column
>> identifier. The name of the column of the specified table, view,
>alias, or
>> synonym.Column 5 DATA_TYPE (SMALLINT not NULL)The SQL data type of
>the
>> column that is identified by COLUMN_NAME. The DATA_TYPE is one of
>the
>> values in the Symbolic SQL Data Type column in the table of
>symbolic and
>> default data types for CLI.Column 6 TYPE_NAME (VARCHAR(128) not
>NULL)A
>> character string that represents the name of the data type that
>corresponds
>> to DATA_TYPE.Column 7 COLUMN_SIZE (INTEGER)
>>
>>
>_______________________________________________
>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: Request to get alias of table

J Decker
On Sun, Aug 18, 2019 at 6:51 PM Keith Medcalf <[hidden email]> wrote:

>
> And what would you expect the column, alias, and table alias, and origin
> table to be for the following query:
>
> select * from (select * from ( select * from (select 1, 2, 3) ta) as tb)
> tc;
>
>
Wrong function... it's SQLColAttribute...
https://docs.microsoft.com/en-us/sql/odbc/reference/syntax/sqlcolattribute-function?view=sql-server-2017

'1' '2' '3' no column alias
'ta' is where the values came from.... but probably  'tc'

amusing though.

-------
Added some logging to break it out, because there isn't any reason to have
any depth to that....

SQL_DESC_TABLE_NAME    (TN)
SQL_DESC_BASE_TABLE_NAME   (BN)
SQL_DESC_NAME (CN)

select 1,2,3   =  CN=['1','2','3'] TN=blank BN=blank
select * from( select 1,2,3 ) ta  TN='ta'  BN=blank
select * from (select * from( select 1,2,3 ) ta )tb   TN='tb'  BN='tb';
... etc for tc, td, ...

For MySQL ODBC.

but most importantly;  'select * from someTable ta';   table_name returns
'ta' not 'someTable'   ... base name does return 'someTable'.

in current Sqlite API there is NO way to get 'ta' from the last query.






> --
> The fact that there's a Highway to Hell but only a Stairway to Heaven says
> a lot about anticipated traffic volume.
>
> >-----Original Message-----
> >From: sqlite-users [mailto:sqlite-users-
> >[hidden email]] On Behalf Of J Decker
> >Sent: Sunday, 18 August, 2019 18:14
> >To: SQLite mailing list
> >Subject: Re: [sqlite] Request to get alias of table
> >
> >And sorry for the Spam.  But, having had cake, I really do like cake,
> >and i
> >do beleive I have deliberatly forced a query to result with an object
> >of
> >group:{} and users: {} information in the group.
> >
> >But, having worked motstly with MySQL/MSSQL through ODBC, that's why
> >I
> >would expect
> >
> >select count(*) count from table alias
> >
> >to have a origin column name of 'count(*)' (not null), an alias of
> >'count'
> >, a table name of 'table' and a table alias of 'alias'.
> >
> >Although the ODBC stuff doesn't actually give you the choice to get
> >the
> >origin names of things.
> >
> >
> >
> >
> >On Sun, Aug 18, 2019 at 5:01 PM J Decker <[hidden email]> wrote:
> >
> >> Okay let's start with, I originally had an API compatible with
> >ODBC, which
> >> the third information parameter is 'The name of the table, view,
> >alias,
> >> or synonym.'
> >>
> >>
> >>
> >https://www.ibm.com/support/knowledgecenter/en/SSEPEK_11.0.0/odbc/src
> >/tpc/db2z_fncolumns.html
> >>
> >>
> >>
> >> Column 1 TABLE_CAT (VARCHAR(128))The name of the catalog. The value
> >is
> >> NULL if this table does not have catalogs.Column 2 TABLE_SCHEM
> >> (VARCHAR(128))The name of the schema containing TABLE_NAME.Column 3
> >> TABLE_NAME (VARCHAR(128) not NULL)The name of the table, view,
> >alias, or
> >> synonym.Column 4 COLUMN_NAME (VARCHAR(128) not NULL)The column
> >> identifier. The name of the column of the specified table, view,
> >alias, or
> >> synonym.Column 5 DATA_TYPE (SMALLINT not NULL)The SQL data type of
> >the
> >> column that is identified by COLUMN_NAME. The DATA_TYPE is one of
> >the
> >> values in the Symbolic SQL Data Type column in the table of
> >symbolic and
> >> default data types for CLI.Column 6 TYPE_NAME (VARCHAR(128) not
> >NULL)A
> >> character string that represents the name of the data type that
> >corresponds
> >> to DATA_TYPE.Column 7 COLUMN_SIZE (INTEGER)
> >>
> >>
> >_______________________________________________
> >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