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

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

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);

Re: [sqlite] Can an SQL script be built from within sqlite?

On 15 Jan 2018, at 3:33pm, J Decker wrote: > But; dropbear ssh sometimes works? Some have issues making it work > so a shell is possible; they just don't have a termianl in the store? > Would be a pretty limited userbase You can install a GUI program to telnet/ssh /out/

Re: [sqlite] Can an SQL script be built from within sqlite?

But; dropbear ssh sometimes works? Some have issues making it work so a shell is possible; they just don't have a termianl in the store? Would be a pretty limited userbase On Mon, Jan 15, 2018 at 7:00 AM, Richard Hipp wrote: > On 1/15/18, Shane Dev

Re: [sqlite] Can an SQL script be built from within sqlite?

On 1/15/18, Shane Dev wrote: > > Did the Apple engineers tell you why it is not possible to compile and run > the SQLite shell on iOS? > You cannot get a command-line prompt on iOS, and the SQLite shell requires a command-line prompt (like bash). If i understand correctly,

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? >>

Re: [sqlite] Can an SQL script be built from within sqlite?

I would have thought that the logical platform-independent scripting language to use with sqlite would be tcl. By default tcl comes with sqlite built in, so you don’t even need to compile anything. ___ sqlite-users mailing list

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

Re: [sqlite] Can an SQL script be built from within sqlite?

>Are you the developer / maintainer of the SQLite shell? THE developer is D Richard Hipp. He is the decisionmaker and principal developer of the SQLite releases originated from sqlite.org. For all practical purposes, that is the official release. On the other hand, because of the open license,

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

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

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,

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

[sqlite] Question about using json_remove to remove items from an array

Hi All, I have a question about removing items from a array. I'm using creating json_object queries to push the results directly into protobuf messages. Long story short, I've used a view to workaround the arbitrary order of json_group_array using a some sequence nr to order by (Is this

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

Re: [sqlite] report a bug for fts5

叶落天下秋 wrote: > select * from fts where fts match 'H & M'; > synatx error near & The documentation says: | An FTS5 bareword is a string of one or more consecutive characters that are all either: | | * Non-ASCII range characters (i.e. unicode

Re: [sqlite] Can an SQL script be built from within sqlite?

Interesting. SQLite is written in ANSI C. Objective-C is a strict superset of ANSI C. Objective-C can be used to write software for OS X and iOS. Did the Apple engineers tell you why it is not possible to compile and run the SQLite shell on iOS? On 15 January 2018 at 02:16, Richard Hipp

Re: [sqlite] Can an SQL script be built from within sqlite?

Hi Peter, Ideally, I would like to execute a series of "test cases" from within the SQLite shell without dependency on an external scripting language. These would not normally be executed by the application end user. Initially, I planned to implement this with triggers but I see now that the

Re: [sqlite] Defect: single row table cross join causes infinite loop

That's interesting because a cross join can always be optimized for lazy evaluation as rows are generated. There are no join constraints. As it stands, without lazy cross join optimization, the only general solution here is to 'pick' individual params from using subqueries: WITH params(n) AS (

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

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,

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

[sqlite] report a bug for fts5

Dear developersI am confused when I am using fts5 like this. CreateTable SQL： CREATE VIRTUAL TABLE fts using fts5(key); QuerySQL： select * from fts where fts match 'H & M'; Error occured：“synatx error near &” when I execute the query SQL. I tried in

Re: [sqlite] Can an SQL script be built from within sqlite?

Sorry, false alarm, the text is correct On 15 January 2018 at 13:36, Shane Dev wrote: > Hi Simon, > > .selftest looks interesting > > I think there is a typo in section 13 - > > The .selftest command reads the rows of the selftest table in selftest.tno > order. > > On 15

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

Re: [sqlite] Can an SQL script be built from within sqlite?

Hi Simon, .selftest looks interesting I think there is a typo in section 13 - The .selftest command reads the rows of the selftest table in selftest.tno order. On 15 January 2018 at 12:06, Simon Slavin wrote: > > > On 15 Jan 2018, at 10:08am, Shane Dev

Re: [sqlite] possible bug: separator string and quote mode

p dev wrote: > https://www.sqlite.org/cli.html 5. Changing Output Formats > > All columns are separated from each other by a comma (or whatever alternative > character is selected using ".separator"). This is indeed a bug in the documentation; quote mode ignores the .separator setting. > I

Re: [sqlite] Defect: single row table cross join causes infinite loop

petern wrote: > there is an infinite loop when params table column "n" is used. > WITH params(n) AS ( >VALUES (5) > ), > coinflip(flip,side) AS ( >SELECT 1, random()>0 >UNION ALL >SELECT flip+1, random()>0 FROM coinflip > ) > SELECT flip,side FROM coinflip,params WHERE

Re: [sqlite] Can an SQL script be built from within sqlite?

On 15 Jan 2018, at 10:08am, Shane Dev wrote: > Ideally, I would like to execute a series of "test cases" from within the > SQLite shell without dependency on an external scripting language. These > would not normally be executed by the application end user. Commands

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