On Thu, Aug 15, 2019 at 7:13 PM Keith Medcalf <kmedc...@dessus.com> 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- > >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 > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
