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- >boun...@mailinglists.sqlite.org] 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 <kmedc...@dessus.com> >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- >> >boun...@mailinglists.sqlite.org] 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 >> >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 _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
