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
