Re: [sqlite] sqlite_column_table_name() and table alias name

Hi J Decker, `alias_name` can only be identifiers without qualifiers I believe. Hence `select 1 1` isn't possible. Changing values to arrays to allow multi values on same column be able to co-exist is a workaround. It works perfect on orphan columns such as `select 1 a, 2 a`. Though it cannot

Re: [sqlite] sqlite_column_table_name() and table alias name

On Mon, Jan 15, 2018 at 5:34 PM, Jake Chen wrote: > The example result is a demonstration of results nested with table alias > right? If that's the case I think the `select 1, 1` part should result in: > > [ { '': { '1': 1 } } ] > > There's no need to return both. If the results should not be nes

Re: [sqlite] sqlite_column_table_name() and table alias name

Hi Clemens, You're right. I didn't look into your SQL carefully enough. As a reference, in MySQL the table of id would be the first table it belongs to. `articles` it is. Jake > On 15 Jan 2018, at 11:03 PM, Clemens Ladisch wrote: > > Jake Chen wrote: >>> On 15 Jan 2018, at 9:14 PM, Clemens

Re: [sqlite] sqlite_column_table_name() and table alias name

The example result is a demonstration of results nested with table alias right? If that's the case I think the `select 1, 1` part should result in: [ { '': { '1': 1 } } ] There's no need to return both. If the results should not be nested, here the result I'd expect: [ { '1': 1 } ] With corr

Re: [sqlite] sqlite_column_table_name() and table alias name

One more example for the road... create table fruit ( fruit_id,name) create table color ( color_id,name) create table fruit_color ( fruit_id,color_id ) insert into fruit (fruit_id,name) values (1,'apple'),(2,'orange'),(3,'b anana')" insert into color (color_id,name) values (1,'red'),(2,'orange'),

Re: [sqlite] sqlite_column_table_name() and table alias name

On Mon, Jan 15, 2018 at 7:05 AM, J Decker wrote: > > > On Mon, Jan 15, 2018 at 5:14 AM, Clemens Ladisch > wrote: > >> J Decker wrote: >> > What is the expected output? >> >> And just out of curiosity: what should the table name be for these >> columns? >> >> SELECT articles.gmt_deleted+tags.ty

Re: [sqlite] sqlite_column_table_name() and table alias name

On Mon, Jan 15, 2018 at 5:14 AM, Clemens Ladisch wrote: > J Decker wrote: > > What is the expected output? > > And just out of curiosity: what should the table name be for these columns? > > SELECT articles.gmt_deleted+tags.type, 42, id FROM articles JOIN tags > USING (id); > > table name would

Re: [sqlite] sqlite_column_table_name() and table alias name

Jake Chen wrote: >> On 15 Jan 2018, at 9:14 PM, Clemens Ladisch wrote: >> SELECT id FROM articles JOIN tags USING (id); > > `id` is ambiguous here. It's not, because of the USING clause. Regards, Clemens ___ sqlite-users mailing list sqlite-users@mai

Re: [sqlite] sqlite_column_table_name() and table alias name

`articles.gmt_delete+tags.type` is an select expression, both the original column name and table name should be empty. 42 is a literal, both the original column name and table name should be empty too. `id` is ambiguous here. Proper SQL might require it be prefixed with qualifier (either `articl

Re: [sqlite] sqlite_column_table_name() and table alias name

J Decker wrote: > What is the expected output? And just out of curiosity: what should the table name be for these columns? SELECT articles.gmt_deleted+tags.type, 42, id FROM articles JOIN tags USING (id); Regards, Clemens ___ sqlite-users mailing l

Re: [sqlite] sqlite_column_table_name() and table alias name (J Decker)

Hi J Decker, I've got the amalgamation patch working now. However it seems the alias of subquery isn't processed correctly. The full SQL: SELECT "posts".*, "tagMaps".*, "topics".* FROM (SELECT * FROM "articles" WHERE "gmt_deleted" IS NULL) AS "posts" LEFT JOIN "tag_maps" AS "tagMaps" ON "

Re: [sqlite] sqlite_column_table_name() and table alias name

On 15 Jan 2018, at 12:03pm, Jake Chen wrote: > Sorry for not able to reply to your email directly. I opted to receive > subscriptions in daily digest. It seems there's no way to revert this option. As you’ve worked out, you have to unsubscribe and resubscribe. However, please do not reply di

Re: [sqlite] sqlite_column_table_name() and table alias name

Hi Decker, The last one is the one expected. If queried with `nestTables` positive, the result should be something like: [ { foo: { id: 1, parent_id: 3 }, bar: { id: 3, parent_id: null } }, { foo: { id: 2, parent_id: 4 }, bar: { id: 4, parent_id: null } } ] Regarding your patch, is th

Re: [sqlite] sqlite_column_table_name() and table alias name

@Jake though; What is the expected output? { id: { foo: 1, bar:3 }, parent_id : { foo: 4, bar: 1} } or { id: [1,3], parent_id : [4,1] } or { id: [ 0:1, 1:3, foo: 1, bar:3 ], parent_id : [ 0:4, 1::1, foo: 4, bar: 1] } or { foo : { id : 1, parent_id:3 }, bar : { id: 1, parent_id:3 } } ? On Mo

Re: [sqlite] sqlite_column_table_name() and table alias name

Maybe this patch (the same, but against original source instead of amalgamation) against current fossil head https://drive.google.com/open?id=1c24qvtvS57ASJF5RfZxLJSsgI2FhOVk1 On Sun, Jan 14, 2018 at 11:17 PM, Jake Chen wrote: > Hi Peter, > > Currently the query result returned by node-sqlite3

Re: [sqlite] sqlite_column_table_name() and table alias name

Hi Peter, Currently the query result returned by node-sqlite3 has the rows formatted into objects all ready. Given SQL `SELECT foo.*, bar.* FROM foo JOIN bar`, the columns of foo and bar collide with each other. I've already sent a PR https://github.com/mapbox/node-sqlite3/pull/932

Re: [sqlite] sqlite_column_table_name() and table alias name

Jake. Maybe somebody else can see the patch. There's a login screen at the linked page. Others are using Node.js with the current version of SQLite. Are you sure that patch is needed? If you can briefly explain what's not working I'm sure somebody else will reply. Peter On Sun, Jan 14, 2018 at

Re: [sqlite] sqlite_column_table_name() and table alias name

Hi, My apologies if it's impolite to reply to such an ancient thread. I've encountered the same problem while implementing an object-relational mapping library for Node.js. Is there any chance that OP's patch gets merged into master? If this email weren't able to get related to the original po

Re: [sqlite] sqlite_column_table_name() and table alias name

Hi, any chance that this or a similar sqlite3_column_table_alias_name() were added to SQLite? TIA, Jose F. Gimenez ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite_column_table_name() and table alias name

Simon, BTW, by using "pragma full_column_names=1" and "pragma short_column_names=0", the sentence: SELECT * FROM mytable AS myalias returns the column names as myalias.col1, myalias.col2, etc., which is perfectly correct. But I'm aware that those pragma are deprecated. And in fact, if you u

Re: [sqlite] sqlite_column_table_name() and table alias name

Simon, If you develop bussiness software, where you know exactly the querys because you are writing them, then there is no problem at all. But other kind of software, like a database manager, need all available metadata. And more metadata info is better, for sure. And this is my case now. Yep

Re: [sqlite] sqlite_column_table_name() and table alias name

On 2 Nov 2014, at 6:28pm, Jose F. Gimenez wrote: > If you develop bussiness software, where you know exactly the querys because > you are writing them, then there is no problem at all. But other kind of > software, like a database manager, need all available metadata. And more > metadata info

Re: [sqlite] sqlite_column_table_name() and table alias name

Simon, This is from the documentation of SQLite: "The name of a result column is the value of the "AS" clause for that column, if there is an AS clause. If there is no AS clause then the name of the column is unspecified and may change from one

Re: [sqlite] sqlite_column_table_name() and table alias name

On 1 Nov 2014, at 12:29pm, Jose F. Gimenez wrote: > But if I need the column's fullname, I get: > > messages.subject, people.name, people.name > > And yes, I know that I can specify an alias for those columns which could be > ambiguous. That is "sender.name AS sender_name" and "receipt.name a

Re: [sqlite] sqlite_column_table_name() and table alias name

Simon, thanks for replying. Specify which names you want SQLite to use: SELECT a, b, table2.c AS table2, alias.c AS alias FROM table1 LEFT JOIN table2 ON ... LEFT JOIN table2 AS alias ON ... However, I strongly advise against having one string which is both a table name and a column name ("t

Re: [sqlite] sqlite_column_table_name() and table alias name

> On 31 Oct 2014, at 12:30pm, Jose F. Gimenez wrote: > > SELECT a, b, table2.c, alias.c > FROM table1 > LEFT JOIN table2 ON ... > LEFT JOIN table2 AS alias ON ... > > the API sqlite_column_table_name() applied to both columns 3 and 4 returns > . I know that belongs to in both cases, but is t

[sqlite] sqlite_column_table_name() and table alias name

Hi, in this kind of statements: SELECT a, b, table2.c, alias.c FROM table1 LEFT JOIN table2 ON ... LEFT JOIN table2 AS alias ON ... the API sqlite_column_table_name() applied to both columns 3 and 4 returns . I know that belongs to in both cases, but is there any way to get for column 3 an