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 ) address join pets pet on pet.user_id=;

The subquery "select count(*) count,* from addresses join users on" 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-
>] 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
>> on ) address join pets pet on
>> Error: no such column:
>>> console.log( "select count(*)count,* from ( select
>from addresses join users on ) address
>pets pet on pet.user_id='';" ) );
>select count(*)count,* from ( select count(*)count,* from addresses
>users on users.address_id=addres
> ) address join pets pet on pet.user_id='';
>13:34:27.326| Err: (284) automatic
>on users(address_id)
>13:34:27.326| 0 :
>origin_name:(null)   table_alias:(null)
>13:34:27.327| 1 :
>origin_name:(null)   table_alias:(null)
>13:34:27.327| 2 :
>origin_name:id   table_alias:addresses
>13:34:27.327| 3 :
>origin_name:name   table_alias:addresses
>13:34:27.327| 4 : table_name:users
>origin_name:id   table_alias:users
>13:34:27.327| 5 : table_name:users
>origin_name:address_id   table_alias:users
>13:34:27.328| 6 : table_name:users
>origin_name:name   table_alias:users
>13:34:27.328| 7 : table_name:pets
>origin_name:id   table_alias:pet
>13:34:27.328| 8 : table_name:pets
>origin_name:user_id   table_alias:pet
>13:34:27.328| 9 : table_name:pets
>origin_name:name   table_alias:pet
>13:34:27.328| data
>  {
>    count: 0,
>    'address.count': null,
>    '': null,
>    '': null,
>    '': null,
>    'address.users.address_id': null,
>    '': null,
>    '': null,
>    'pet.user_id': null,
>    '': 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

sqlite-users mailing list

Reply via email to