[sqlite] No such column error

[Aydin Ozgur Yagmur]

I have been developing a c++ program in windows. In this program, in a
loop, I open database, query and update the records then close it.

I am encountering randomly with "No such column error".

Query looks like that (but there are more columns than this STATUS table):
"select a.BIRTH_DATE, a.BIRTH_PLACE, a.BIRTH_XXX, a.BIRTH.YYY from STATUS
a;"

I randomly encounter with this error while executing sqlite3_prepare_v2
method. When encountered this error, I made a second execution of
sqlite3_prepare_v2  with "select * from STATUS a;" statement. Return value
was SQLITE_OK.

Do you have any idea what is going on?

Thank you very much in advance,
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Patch: VTable Column Affinity Question and Change Request

[Keith Medcalf]

Patch to Fix Column Affinity not applied to Virtual Columns.

In expr.c function sqlite3ExprCodeGetColumnOfTable
At or about line 3555 where the OP_Column or OP_VColumn opcode is added to the 
VDBE program, and the default code is generated, make this:

sqlite3VdbeAddOp3(v, op, iTabCur, x, regOut);
sqlite3ColumnDefault(v, pTab, iCol, regOut);

look like this:

sqlite3VdbeAddOp3(v, op, iTabCur, x, regOut);
if ( IsVirtual(pTab) && pTab->aCol[iCol].affinity > SQLITE_AFF_BLOB)
  sqlite3VdbeAddOp4(v, OP_Affinity, regOut, 1, 0, 
&(pTab->aCol[iCol].affinity), 1);
sqlite3ColumnDefault(v, pTab, iCol, regOut);

Of course, it may be that the writer of the VTable should know what they are 
doing and generate a VTable definition that is consistent with how their cursor 
methods return data, however ... this will omit the OP_Affinity if no column 
type was specified when the VTable was defined and most of the VTable 
declarations in the existing code that I looked at do not specify column 
affinities in the declarations.

-- 
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  On
>Behalf Of Keith Medcalf
>Sent: Wednesday, 5 February, 2020 10:58
>To: SQLite Users (sqlite-users@mailinglists.sqlite.org) us...@mailinglists.sqlite.org>
>Subject: [sqlite] VTable Column Affinity Question and Change Request
>
>
>It seems that "column affinities" are not respected in Virtual Table
>implementations -- that is the value that is returned is the datatype
>provided by the the vtab_cursor sqlite3_result_* function and the "column
>affinity" from the vtab declaration is not applied.  In effect the column
>affinity specified in the vtab declaration seems to be ignored (or
>treated as none/blob) no matter what the declaration.
>
>Somehow, I don't think this was always the case but I could be wrong.  In
>any case, what is the point in specifying the column affinity in the vtab
>declaration if it is just going to be ignored?
>
>Example, using the current tip of trunk and the ext\misc\csv.c extension
>with the following input file:
>
>a,b,c,d
>1,2,3,4
>2,3 or 4,4,5
>3,4,5,6
>4,5,6,7
>
>SQLite version 3.32.0 2020-02-05 16:13:24
>Enter ".help" for usage hints.
>Connected to a transient in-memory database.
>Use ".open FILENAME" to reopen on a persistent database.
>sqlite> create virtual table t using csv(filename='t.csv', header=on);
>sqlite> .mode col
>sqlite> .head on
>
>sqlite> select typeof(a), a, typeof(b), b, typeof(c), c, typeof(d), d
>from t;
>
>typeof(a)   a   typeof(b)   b   typeof(c)   c
>typeof(d)   d
>--  --  --  --  --  --  -
>-  --
>text1   text2   text3
>text4
>text2   text3 or 4  text4
>text5
>text3   text4   text5
>text6
>text4   text5   text6
>text7
>
>sqlite> pragma table_xinfo(t);
>
>cid nametypeaff collnotnull
>dflt_value  pk  rowid   autoinc hidden
>--  --  --  --  --  --  -
>-  --  --  --  --
>-1  INTEGER 0
>1   1   0   1
>0   a   TEXTTEXT0
>0   0   0   0
>1   b   TEXTTEXT0
>0   0   0   0
>2   c   TEXTTEXT0
>0   0   0   0
>3   d   TEXTTEXT0
>0   0   0   0
>
>sqlite> drop table t;
>sqlite> create virtual table t using csv(filename='t.csv', header=off,
>schema='create table t(a numeric, b numeric, c numeric, d numeric)');
>
>sqlite> select typeof(a), a, typeof(b), b, typeof(c), c, typeof(d), d
>from t;
>
>typeof(a)   a   typeof(b)   b   typeof(c)   c
>typeof(d)   d
>--  --  --  --  --  --  -
>-  --
>texta   textb   textc
>textd
>text1   text2   text3
>text4
>text2   text3 or 4  te

Re: [sqlite] Is Comment column in 'EXPLAIN' blank?

[x]

Thanks David, getting them now.



From: sqlite-users  on behalf of 
David Raymond 
Sent: Friday, January 31, 2020 1:45:41 PM
To: SQLite mailing list 
Subject: Re: [sqlite] Is Comment column in 'EXPLAIN' blank?

To get the comments the library needs to have been compiled with 
SQLITE_ENABLE_EXPLAIN_COMMENTS flag
https://www.sqlite.org/compile.html#_options_to_enable_features_normally_turned_off

That setting is off by default, but for the pre-compiled CLI they do include it.

I'm not seeing it in the list when I do a "pragma compile_options;" though. Is 
that setting one that's not reported by that pragma?


-Original Message-
From: sqlite-users  On Behalf Of x
Sent: Friday, January 31, 2020 6:22 AM
To: sqlite-users@mailinglists.sqlite.org
Subject: [sqlite] Is Comment column in 'EXPLAIN' blank?

I get the comments using the shell but running an explain query in c++ using 
sqlite3.c (from 3.30 amalgamation) column 7 is returning blank in every row. Is 
something up or is it my code?
___
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

Re: [sqlite] Is Comment column in 'EXPLAIN' blank?

[David Raymond]

To get the comments the library needs to have been compiled with 
SQLITE_ENABLE_EXPLAIN_COMMENTS flag
https://www.sqlite.org/compile.html#_options_to_enable_features_normally_turned_off

That setting is off by default, but for the pre-compiled CLI they do include it.

I'm not seeing it in the list when I do a "pragma compile_options;" though. Is 
that setting one that's not reported by that pragma?


-Original Message-
From: sqlite-users  On Behalf Of x
Sent: Friday, January 31, 2020 6:22 AM
To: sqlite-users@mailinglists.sqlite.org
Subject: [sqlite] Is Comment column in 'EXPLAIN' blank?

I get the comments using the shell but running an explain query in c++ using 
sqlite3.c (from 3.30 amalgamation) column 7 is returning blank in every row. Is 
something up or is it my code?
___
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] Is Comment column in 'EXPLAIN' blank?

[x]

I get the comments using the shell but running an explain query in c++ using 
sqlite3.c (from 3.30 amalgamation) column 7 is returning blank in every row. Is 
something up or is it my code?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] No such column

[Aydin Ozgur Yagmur]

In a specific case, we have to use such a scenario.

I'm aware it is contrary to traditional way, but for this specific "mounted
drive" situation, is there a reasonable solution? Any way to avoid this
kind of database file corruption?

Thanks in advance,
Best Regards,



On Tue, Dec 24, 2019 at 12:45 PM Darren Duncan 
wrote:

> On 2019-12-22 10:48 p.m., Keith Medcalf wrote:
> > On Sunday, 22 December, 2019 23:20, Aydin Ozgur Yagmur <
> ayagmu...@gmail.com> wrote:
> >> I have experienced a weird problem. I have been using sqlite database in
> >> linux by mounting.
> >
> > Can you give some clues what "using sqlite database in linux by
> mounting" means?
>
> My first thought on reading that was that "mounting" meant using the
> ATTACH
> command, since in the analogy of the SQL environment as a filesystem,
> using
> ATTACH is like mounting a volume within the filesystem in order to access
> the
> volume's contents, which are tables. -- Darren Duncan
> ___
> 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

Re: [sqlite] No such column

[Darren Duncan]

On 2019-12-22 10:48 p.m., Keith Medcalf wrote:

On Sunday, 22 December, 2019 23:20, Aydin Ozgur Yagmur  
wrote:

I have experienced a weird problem. I have been using sqlite database in
linux by mounting.


Can you give some clues what "using sqlite database in linux by mounting" means?


My first thought on reading that was that "mounting" meant using the ATTACH 
command, since in the analogy of the SQL environment as a filesystem, using 
ATTACH is like mounting a volume within the filesystem in order to access the 
volume's contents, which are tables. -- Darren Duncan

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] No such column

[Aydin Ozgur Yagmur]

Thank you very much Simon, it is quite explicative.

On Mon, Dec 23, 2019 at 11:38 AM Simon Slavin  wrote:

> On 23 Dec 2019, at 6:19am, Aydin Ozgur Yagmur  wrote:
>
> > I have been using sqlite database in linux by mounting.
> > Nearly all times it works well. But when testing with customer, I
> encounter "No such column" error.
>
> SQLite does not support accessing the database drive across a network.  No
> network file systems seem to implement file locking correctly, and if file
> locking doesn't work correctly, SQLite cannot function correctly.  You may
> find errors like the one you reported, where commands fail to execute
> correctly.  You may also find that your database has been corrupted, but
> this happens less often.
>
> It can be very difficult to track down the locking errors which make
> SQLite fail.  Sometimes a setup will work perfectly, but then occasionally
> fail after a reboot.  Then another reboot may appear to fix the problem.
> We have not found /any/ method of mounting a drive over a network, for any
> operating system, which we can recommend.
>
> If the database you are using has important data, I suggest you run
>
> PRAGMA integrity_check
>
> to make sure that the database is not corrupt.  If you don't actually need
> that database, please delete it and start again with a blank database or a
> backup.
> ___
> 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

Re: [sqlite] No such column

[Simon Slavin]

On 23 Dec 2019, at 6:19am, Aydin Ozgur Yagmur  wrote:

> I have been using sqlite database in linux by mounting.
> Nearly all times it works well. But when testing with customer, I encounter 
> "No such column" error.

SQLite does not support accessing the database drive across a network.  No 
network file systems seem to implement file locking correctly, and if file 
locking doesn't work correctly, SQLite cannot function correctly.  You may find 
errors like the one you reported, where commands fail to execute correctly.  
You may also find that your database has been corrupted, but this happens less 
often.

It can be very difficult to track down the locking errors which make SQLite 
fail.  Sometimes a setup will work perfectly, but then occasionally fail after 
a reboot.  Then another reboot may appear to fix the problem.  We have not 
found /any/ method of mounting a drive over a network, for any operating 
system, which we can recommend.

If the database you are using has important data, I suggest you run

PRAGMA integrity_check

to make sure that the database is not corrupt.  If you don't actually need that 
database, please delete it and start again with a blank database or a backup.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] No such column

[Keith Medcalf]

On Sunday, 22 December, 2019 23:20, Aydin Ozgur Yagmur  
wrote:

>I have experienced a weird problem. I have been using sqlite database in
>linux by mounting.
>Nearly all times it works well. But when testing with customer, I
>encounter
>"No such column" error. After restarting system, it works again well.

>I wonder why I get such an error? Could you please give me some clues?

Can you give some clues what "using sqlite database in linux by mounting" means?

-- 
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.




___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] No such column

[Aydin Ozgur Yagmur]

Hello,

I have experienced a weird problem. I have been using sqlite database in
linux by mounting.
Nearly all times it works well. But when testing with customer, I encounter
"No such column" error. After restarting system, it works again well.

I wonder why I get such an error? Could you please give me some clues?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Result set column names

[Tim Streater]

On 10 Dec 2019, at 21:42, Simon Slavin  wrote:

> On 10 Dec 2019, at 9:35pm, Doug  wrote:
>
>> For column "xxx" what will the full column name be? "foo.xxx"?
>
> For column "xxx" in table "foo", yes.
>
> However, that's only if you've used "PRAGMA full_column_names".  And that
> PRAGMA is now deprecated so you shouldn't use it.

Why are these pragmas deprecated? And what does "deprecated" mean, in this 
context, anyway? There's lots of HTML that's "deprecated", but browsers will 
continue to support it until kingdom come.


-- 
Cheers  --  Tim
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Result set column names

[Simon Slavin]

On 10 Dec 2019, at 9:35pm, Doug  wrote:

> For column "xxx" what will the full column name be? "foo.xxx"?

For column "xxx" in table "foo", yes.

However, that's only if you've used "PRAGMA full_column_names".  And that 
PRAGMA is now deprecated so you shouldn't use it.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Result set column names

[Doug]

For column "xxx" what will the full column name be? "foo.xxx"?
Doug

> -Original Message-
> From: sqlite-users 
> On Behalf Of Simon Slavin
> Sent: Tuesday, December 10, 2019 10:37 AM
> To: SQLite mailing list 
> Subject: Re: [sqlite] Result set column names
> 
> On 10 Dec 2019, at 5:13pm, Doug  wrote:
> 
> > Just to be clear: If I define a column in a table named "xxx",
> and I "select xxx from table", the report will show a column named
> "xxx" always. That's what the standard says, right? It's not
> implementation dependent, right?
> 
> Unless you have specified another column "AS xxx", or used "PRAGMA
> full_column_names".
> ___
> 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

Re: [sqlite] Result set column names

[Simon Slavin]

On 10 Dec 2019, at 5:13pm, Doug  wrote:

> Just to be clear: If I define a column in a table named "xxx", and I "select 
> xxx from table", the report will show a column named "xxx" always. That's 
> what the standard says, right? It's not implementation dependent, right?

Unless you have specified another column "AS xxx", or used "PRAGMA 
full_column_names".
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Result set column names

[Doug]

Just to be clear: If I define a column in a table named "xxx", and I "select 
xxx from table", the report will show a column named "xxx" always. That's what 
the standard says, right? It's not implementation dependent, right?

CREATE TABLE foo(xxx)
INSERT INTO foo(xxx) VALUES(25)
SELECT xxx FROM foo
---|-
xxx 25

Doug

> -Original Message-
> From: sqlite-users 
> On Behalf Of Adrian Ho
> Sent: Tuesday, December 10, 2019 8:04 AM
> To: sqlite-users@mailinglists.sqlite.org
> Subject: Re: [sqlite] Result set column names
> 
> On 9/12/19 3:45 PM, Graham Holden wrote:
> > Monday, December 09, 2019, 7:25:25 AM, Clemens Ladisch
>  wrote:
> >
> >> The SQL-92 standard actually says:
> >>
> >> |Syntax Rules
> >> |
> >> |9) Case:
> >> |
> >> |   b) If the i-th  in the 
> does not
> >> | specify an  and the  of
> that
> >> |  is a single , then
> the
> >> |  of the i-th column of the result is C.
> >> |
> > Presumably the third line of clause (b) of the standard SHOULD
> have
> > read: "is a single  C,", otherwise "is C" has
> > nothing to refer to.
> 
> C is actually defined in a preceding paragraph:
> 
>  5) Let C be some column. Let QS be the  specification>. Let
> DCi, for i ranging from 1 to the number of  column>s
> inclusively, be the i-th  simply
> contained in
> the  of QS. For all i, C is an underlying
> column
> of DCi, and of any  that identifies
> DCi, if
> and only if C is an underlying column of the  expression>
> of DCi, or C is an underlying column of the  expression>
> immediately contained in QS.
> 
> --
> Best Regards,
> Adrian
> 
> ___
> 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

Re: [sqlite] Result set column names

[Adrian Ho]

On 9/12/19 3:45 PM, Graham Holden wrote:
> Monday, December 09, 2019, 7:25:25 AM, Clemens Ladisch  
> wrote:
>
>> The SQL-92 standard actually says:
>>
>> |Syntax Rules
>> |
>> |9) Case:
>> |
>> |   b) If the i-th  in the  does not
>> | specify an  and the  of that
>> |  is a single , then the
>> |  of the i-th column of the result is C.
>> |
> Presumably the third line of clause (b) of the standard SHOULD have
> read: "is a single  C,", otherwise "is C" has
> nothing to refer to.

C is actually defined in a preceding paragraph:

 5) Let C be some column. Let QS be the . Let
    DCi, for i ranging from 1 to the number of s
    inclusively, be the i-th  simply contained in
    the  of QS. For all i, C is an underlying column
    of DCi, and of any  that identifies DCi, if
    and only if C is an underlying column of the 
    of DCi, or C is an underlying column of the 
    immediately contained in QS.

-- 
Best Regards,
Adrian

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Result set column names

[Graham Holden]

Monday, December 09, 2019, 7:25:25 AM, Clemens Ladisch  
wrote:

> The SQL-92 standard actually says:
>
> |Syntax Rules
> |
> |9) Case:
> |
> |   b) If the i-th  in the  does not
> | specify an  and the  of that
> |  is a single , then the
> |  of the i-th column of the result is C.
> |

Presumably the third line of clause (b) of the standard SHOULD have
read: "is a single  C,", otherwise "is C" has
nothing to refer to.

Graham

(And it's not a transcription error, according to:
https://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Result set column names

[Clemens Ladisch]

Keith Medcalf wrote:
> If you do not provide as AS clause to give a result column a name, then
> each implementation is free to provide whatever names it feels like (so
> sayeth the standard).

The SQL-92 standard actually says:
|6.4  
|
| ::= [   ] 
|
|7.9  
|
| ::=
| SELECT [  ]  
|
| ::=
|   
| |  [ {   }... ]
|
| ::=
|   
| |   
|
| ::=  [  ]
|
| ::= [ AS ] 
|
|Syntax Rules
|
|9) Case:
|
|   a) If the i-th  in the  specifies
| an  that contains a  C, then the
|  of the i-th column of the result is C.
|
|   b) If the i-th  in the  does not
| specify an  and the  of that
|  is a single , then the
|  of the i-th column of the result is C.
|
|   c) Otherwise, the  of the i-th column of the  is implementation-dependent and different
| from the  of any column, other than itself, of
| a table referenced by any  contained in the
| SQL-statement.

SQLite's default short_column_names setting implements rule b).


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Result set column names

[Keith Medcalf]

On Saturday, 7 December, 2019 16:05, Tim Streater  wrote:

>At various times in various threads on this list it has been stated that
>the column name in a result set is not guaranteed unless one uses AS.
>IOW, one should say
>
>  select abc as abc from mytable where i=23;
>
>rather than just:
>
>  select abc from mytable where i=23;
>
>I'm trying to find where on the SQLite website that is documented, if it
>is, so I can point someone at it. The Xojo documentation doesn't mwention
>this so either that is a potential problem for all us Xojo users or
>they've worked around it somehow in their SQLite wrapper.

Currently the default is that 

pragma short_column_names=1;
pragma full_column_names=0;

and the rules as described for the pragma full_column_names is in effect.  The 
use of the pragma's is deprecated.

https://sqlite.org/pragma.html#pragma_full_column_names

Anonymous columns are named "columnX" where x is the column number.  For 
example, "values (1,2),(3,4);" generates a two column table with columns named 
column1 and column2 and two rows (1,2) and (3,4).

You may have duplicate column names in a result set:

create table a(id);
create table b(id);
create table c(id);
insert into a values (1);
insert into b values (1);
insert into c values (1);
select * from a, b, c;

you will get a table that is the result of the join and the first column will 
be named "id", the second "id", and the third "id".  Which one of these 
represents which id from which table depends on the nesting order that the 
optimizer selects when the query is prepared.  If you care, then you need to 
explicitly select the columns by name and give them an output column name using 
AS:

select a.id as a_id, b.id as b_id, c.id as c_id from a,b,c;

You may also use the result column metadata function described at
https://sqlite.org/c3ref/column_database_name.html
to get metadata about the underlying database, table, and column provided that 
you compiled the version of the SQLite3 library you are using with the 
appropropriate SQLITE_ENABLE_COLUMN_METADATA C-preprocessor symbol defined.

The naming of columns in the result set is NOT GUARANTEED unless you use the AS 
clause to provide a name.  If you do not provide as AS clause to give a result 
column a name, then each implementation is free to provide whatever names it 
feels like (so sayeth the standard).  In other words, in *ALL* SQL 
implementations the only time you have guaranteed result column names is if you 
use the AS clause to give the result set columns names.  IN ALL OTHER CASES AND 
FOR ALL IMPLEMENTATIONS OF ALL SQL INTERFACES the name given the result set 
column is a non-specified implementation detail.

However, there is a general reliance on the rules as outlined above so they are 
unlikely to change.  It is possible that a "pragma random_result_names" might 
be introduced at some point in the future to assist in finding code that relies 
on "implementation details" much as "pragma reverse_unordered_selects" assists 
in finding where reliance is placed on the ordering of result sets when no 
order by is used.

-- 
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.




___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Result set column names

[Simon Slavin]

On 7 Dec 2019, at 11:09pm, J. King  wrote:

> It's stated here, at least:
> 

Also note

https://sqlite.org/pragma.html#pragma_short_column_names

https://sqlite.org/pragma.html#pragma_full_column_names

even though they are now both deprecated.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Result set column names

[J. King]

On December 7, 2019 6:04:36 p.m. EST, Tim Streater  
wrote:
>At various times in various threads on this list it has been stated
>that the column name in a result set is not guaranteed unless one uses
>AS. IOW, one should say
>
>  select abc as abc from mytable where i=23;
>
>rather than just:
>
>  select abc from mytable where i=23;
>
>I'm trying to find where on the SQLite website that is documented, if
>it is, so I can point someone at it. The Xojo documentation doesn't
>mwention this so either that is a potential problem for all us Xojo
>users or they've worked around it somehow in their SQLite wrapper.
>
>Thanks for a pointer.
>
>
>-- 
>Cheers  --  Tim
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

It's stated here, at least:

-- 
J. King
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Result set column names

[Tim Streater]

At various times in various threads on this list it has been stated that the 
column name in a result set is not guaranteed unless one uses AS. IOW, one 
should say

  select abc as abc from mytable where i=23;

rather than just:

  select abc from mytable where i=23;

I'm trying to find where on the SQLite website that is documented, if it is, so 
I can point someone at it. The Xojo documentation doesn't mwention this so 
either that is a potential problem for all us Xojo users or they've worked 
around it somehow in their SQLite wrapper.

Thanks for a pointer.


-- 
Cheers  --  Tim
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Multiple Unique column missing in System.Data.DataTable.Constraints after Schema Filling

[Simon Slavin]

On 25 Nov 2019, at 11:02am, Jaroslav Homisin  wrote:

> I have expected a list of the two constraints: of Primary key and of Unique. 
> But I can find only Primary Key Constraint.

Where are you getting your list of constraints from ?

SQLite version 3.28.0 2019-04-15 14:49:49
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> CREATE TABLE XRelations (
   ...>   XRID INTEGER PRIMARY KEY AUTOINCREMENT  NOT NULL , 
   ...> XID1 TEXT (100) NOT NULL , 
   ...> XID2 TEXT (100) NOT NULL , 
   ...> RelationType TEXT (100) NULL , 
   ...> CONSTRAINT unq UNIQUE ( XID1, XID2)
   ...> );
sqlite> INSERT INTO XRelations VALUES (1,'a','n','first');
sqlite> INSERT INTO XRelations VALUES (2,'a','n','first');
Error: UNIQUE constraint failed: XRelations.XID1, XRelations.XID2
sqlite> INSERT INTO XRelations VALUES (1,'b','m','first');
Error: UNIQUE constraint failed: XRelations.XRID
sqlite> .mode column
sqlite> PRAGMA index_list('XRelations');
0   sqlite_autoindex_XRelations_1  1   u   0 
sqlite> PRAGMA index_info('sqlite_autoindex_XRelations_1');
0   1   XID1  
1   2   XID2 
sqlite> PRAGMA index_xinfo('sqlite_autoindex_XRelations_1');
0   1   XID10   BINARY  1 
1   2   XID20   BINARY  1 
2   -1  0   BINARY  0 

This shows details of the index automatically made by SQLite so it can quickly 
find violations of CONSTRAINT unq .
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Multiple Unique column missing in System.Data.DataTable.Constraints after Schema Filling

[Jaroslav Homisin]

Dear SQLite support,

Develop informations:
    Windows 7 64bit
 Visual StudioProfessional 2015 (Version 14.0.25431.01 Update 3);  
   .NET Build Platform 4.5.1
 System.Data.SQLite.dll used from Installation  
sqlite-netFx451-setup-bundle-x64-2013-1.0.112.0.exe
 

I am using followed Table:

CREATE TABLE XRelations (  XRID INTEGER PRIMARY KEY AUTOINCREMENT  NOT NULL , 
  XID1 TEXT (100) NOT NULL , 
  XID2 TEXT (100) NOT NULL , 
  RelationType TEXT (100) NULL , 
  CONSTRAINT unq UNIQUE ( XID1, 
XID2)
   )

In C# .NET Source Code after filling of Schema this table, I am missing the 
Unique constraint. I have expected a list of the two constraints: of Primary 
key and of Unique. But I can find only Primary Key Constraint.
Note: the Unique Constraint works on the Database fine, if I try to insert 
redundant row, occurs Exception: UNIQUE constraint failed. 

DataTable pDt = new DataTable(ptTabellenName);
string ltSQL = "select * from " + ptTabellenName + " where 1=0";
System.Data.SQLite.SQLiteDataAdapter lSQLiteDataAdapterSchema = new 
System.Data.SQLite.SQLiteDataAdapter();
lSQLiteDataAdapterSchema.FillSchema(pDt, SchemaType.Mapped);

 

I have tried some other syntax, but withou effect:
CREATE TABLE XRelations (  XRID INTEGER PRIMARY KEY AUTOINCREMENT  NOT NULL , 
  XID1 TEXT (100) NOT NULL , 
  XID2 TEXT (100) NOT NULL , 
  RelationType TEXT (100) NULL , 
  UNIQUE ( XID1, XID2)
   )
Or via two SQL commands:
CREATE TABLE XRelations (  XRID INTEGER PRIMARY KEY AUTOINCREMENT  NOT NULL , 
  XID1 TEXT (100) NOT NULL , 
  XID2 TEXT (100) NOT NULL , 
  RelationType TEXT (100) NULL 
   );
CREATE UNIQUE INDEX unq ON XRelations (XID1, XID2);


/


If think the Problem is only by multiple column for Unique Key. If I reduce the 
Unique constrain for single column, then it works:

CREATE TABLE XRelations (  XRID INTEGER PRIMARY KEY AUTOINCREMENT  NOT NULL , 
  XID1 TEXT (100) NOT NULL , 
  XID2 TEXT (100) NOT NULL , 
  RelationType TEXT (100) NULL , 
  CONSTRAINT unq UNIQUE (XID2)
   )

 


I can find in DataTable.Constrints also Constraint for multiple Primary Key 
column, but never Constraint for multiple Unique column.

  Do you can help me or explain me why it doesn't work?

  Regards,
 Jaroslav Homisin 
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Getting statement column data types on Android

[Bart Smissaert]

Yes, I think the only solution might be to clean up the tables
So if for example there is text in a real column, update it to 0.0 etc.

RBS



On Tue, May 8, 2018 at 5:46 PM, Simon Slavin <slav...@bigfraud.org> wrote:

> On 8 May 2018, at 5:37pm, Bart Smissaert <bart.smissa...@gmail.com> wrote:
>
> >> SQLite does not have column types.  It has column affinities instead.
> >
> > OK, so I would like to see that declared column affinity as that will
> > determine how to process the data.
>
> Ah.  You don't care about the data, you want the column declaration.  Okay.
>
> <https://sqlite.org/pragma.html#pragma_table_info>
>
> Do whatever you would do for a SELECT statement but instead of a SELECT
> statement execute
>
> PRAGMA schema.table_info(table-name)
>
> The problem is that you don't want that, either.  Because you may be doing
> JOINs or calculations.  Unfortunately SQLite does not derive affinities as
> it does a calculation, it just uses whatever values it finds.  Sorry.
>
> Simon.
> ___
> 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

Re: [sqlite] Getting statement column data types on Android

[Simon Slavin]

On 8 May 2018, at 5:37pm, Bart Smissaert <bart.smissa...@gmail.com> wrote:

>> SQLite does not have column types.  It has column affinities instead.
> 
> OK, so I would like to see that declared column affinity as that will
> determine how to process the data.

Ah.  You don't care about the data, you want the column declaration.  Okay.

<https://sqlite.org/pragma.html#pragma_table_info>

Do whatever you would do for a SELECT statement but instead of a SELECT 
statement execute

PRAGMA schema.table_info(table-name)

The problem is that you don't want that, either.  Because you may be doing 
JOINs or calculations.  Unfortunately SQLite does not derive affinities as it 
does a calculation, it just uses whatever values it finds.  Sorry.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Getting statement column data types on Android

[Bart Smissaert]

> SQLite does not have column types.  It has column affinities instead.

OK, so I would like to see that declared column affinity as that will
determine how to process the data.
I have no problem doing this on the Windows PC. More difficult though to do
this on Android.

RBS

On Tue, May 8, 2018 at 5:29 PM, Simon Slavin <slav...@bigfraud.org> wrote:

> On 8 May 2018, at 4:19pm, Bart Smissaert <bart.smissa...@gmail.com> wrote:
>
> > Just tested that (TypeOf) on the Android phone and it doesn't do what I
> > wanted.
> > I tested on a column declared Real but with text values in it as well.
> > It will give both real and text and what I wanted was to produce only
> real
> > as that
> > is what the column is declared as.
>
> SQLite does not have column types.  It has column affinities instead.
> When you supply a value for a column SQLite will try to convert it into the
> desired type, but if it cannot do so (for instance, the column is type REAL
> but you supply "hello") then it will store the text instead.
>
> If you don't want to see text in your numeric columns, don't store text in
> your numeric columns.
>
> Simon.
> ___
> 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

Re: [sqlite] Getting statement column data types on Android

[Simon Slavin]

On 8 May 2018, at 4:19pm, Bart Smissaert <bart.smissa...@gmail.com> wrote:

> Just tested that (TypeOf) on the Android phone and it doesn't do what I
> wanted.
> I tested on a column declared Real but with text values in it as well.
> It will give both real and text and what I wanted was to produce only real
> as that
> is what the column is declared as.

SQLite does not have column types.  It has column affinities instead.  When you 
supply a value for a column SQLite will try to convert it into the desired 
type, but if it cannot do so (for instance, the column is type REAL but you 
supply "hello") then it will store the text instead.

If you don't want to see text in your numeric columns, don't store text in your 
numeric columns.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Getting statement column data types on Android

[Bart Smissaert]

> int sqlite3_column_type(sqlite3_stmt*, int iCol);

Android and certainly B4A doesn't have that as far as I can see.

RBS

On Tue, May 8, 2018 at 3:21 AM, J Decker  wrote:

> https://www.sqlite.org/c3ref/column_blob.html
>
> int sqlite3_column_type(sqlite3_stmt*, int iCol);
>
> ?
>
> On Mon, May 7, 2018 at 4:28 PM, Bart Smissaert 
> wrote:
>
> > Yes, thanks, that might be the best way, but it can get a bit complicated
> > with complex SQL.
> >
> > RBS
> >
> >
> >
> > On Tue, May 8, 2018 at 12:05 AM, Simon Slavin 
> > wrote:
> >
> > > On 7 May 2018, at 10:49pm, Bart Smissaert 
> > > wrote:
> > >
> > > > Using B4A for a SQLite database app on an Android phone.
> > > > B4A doesn't have functions like sqlite3_column_decltype and
> > > > sqlite3_column_type
> > > > and this is causing some difficulty getting the column datatypes of a
> > > row producing statement.
> > >
> > > See page 30 of the Basic4android manual, downloadable from
> > >
> > > 
> > >
> > > The general idea is to execute a query like
> > >
> > > SELECT ID, Name, Age, Height, typeof(Name), typeof(Age),
> > > typeof(Height) FROM Table1
> > >
> > > Simon.
> > > ___
> > > 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

Re: [sqlite] Getting statement column data types on Android

[Bart Smissaert]

Just tested that (TypeOf) on the Android phone and it doesn't do what I
wanted.
I tested on a column declared Real but with text values in it as well.
It will give both real and text and what I wanted was to produce only real
as that
is what the column is declared as.

RBS

On Tue, May 8, 2018 at 12:05 AM, Simon Slavin  wrote:

> On 7 May 2018, at 10:49pm, Bart Smissaert 
> wrote:
>
> > Using B4A for a SQLite database app on an Android phone.
> > B4A doesn't have functions like sqlite3_column_decltype and
> > sqlite3_column_type
> > and this is causing some difficulty getting the column datatypes of a
> row producing statement.
>
> See page 30 of the Basic4android manual, downloadable from
>
> 
>
> The general idea is to execute a query like
>
> SELECT ID, Name, Age, Height, typeof(Name), typeof(Age),
> typeof(Height) FROM Table1
>
> Simon.
> ___
> 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

Re: [sqlite] Getting statement column data types on Android

[J Decker]

https://www.sqlite.org/c3ref/column_blob.html

int sqlite3_column_type(sqlite3_stmt*, int iCol);

?

On Mon, May 7, 2018 at 4:28 PM, Bart Smissaert 
wrote:

> Yes, thanks, that might be the best way, but it can get a bit complicated
> with complex SQL.
>
> RBS
>
>
>
> On Tue, May 8, 2018 at 12:05 AM, Simon Slavin 
> wrote:
>
> > On 7 May 2018, at 10:49pm, Bart Smissaert 
> > wrote:
> >
> > > Using B4A for a SQLite database app on an Android phone.
> > > B4A doesn't have functions like sqlite3_column_decltype and
> > > sqlite3_column_type
> > > and this is causing some difficulty getting the column datatypes of a
> > row producing statement.
> >
> > See page 30 of the Basic4android manual, downloadable from
> >
> > 
> >
> > The general idea is to execute a query like
> >
> > SELECT ID, Name, Age, Height, typeof(Name), typeof(Age),
> > typeof(Height) FROM Table1
> >
> > Simon.
> > ___
> > 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

Re: [sqlite] Getting statement column data types on Android

[Bart Smissaert]

Yes, thanks, that might be the best way, but it can get a bit complicated
with complex SQL.

RBS



On Tue, May 8, 2018 at 12:05 AM, Simon Slavin  wrote:

> On 7 May 2018, at 10:49pm, Bart Smissaert 
> wrote:
>
> > Using B4A for a SQLite database app on an Android phone.
> > B4A doesn't have functions like sqlite3_column_decltype and
> > sqlite3_column_type
> > and this is causing some difficulty getting the column datatypes of a
> row producing statement.
>
> See page 30 of the Basic4android manual, downloadable from
>
> 
>
> The general idea is to execute a query like
>
> SELECT ID, Name, Age, Height, typeof(Name), typeof(Age),
> typeof(Height) FROM Table1
>
> Simon.
> ___
> 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

Re: [sqlite] Getting statement column data types on Android

[Simon Slavin]

On 7 May 2018, at 10:49pm, Bart Smissaert  wrote:

> Using B4A for a SQLite database app on an Android phone.
> B4A doesn't have functions like sqlite3_column_decltype and
> sqlite3_column_type
> and this is causing some difficulty getting the column datatypes of a row 
> producing statement.

See page 30 of the Basic4android manual, downloadable from



The general idea is to execute a query like

SELECT ID, Name, Age, Height, typeof(Name), typeof(Age), typeof(Height) 
FROM Table1

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Getting statement column data types on Android

[Bart Smissaert]

Using B4A for a SQLite database app on an Android phone.
B4A doesn't have functions like sqlite3_column_decltype and
sqlite3_column_type
and this is causing some difficulty getting the column datatypes of a row
producing
statement.

If we have for example:

create table Table1(ID Integer, Name Text, Age Integer, Height Real)

select ID, Name, Age, Height from Table1

Then I would like to get the data types: 1, 3, 1, 2

This would be easy in the above case with some parsing etc. but gets very
tricky if there
are joins and sub-selects etc. I am not too worried about data conversions
and UDF's etc.

Would it somehow be possible to get these data types from an explain, so:
explain  select ID, Name, Age, Height from Table1?

RBS
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Preupdate hook column name

[João Ramos]

Hi,

I've successfully added support for the sqlite3_preupdate_hook(), but I
also need to get the column name when calling either
sqlite3_preupdate_old() or sqlite3_preupdate_new().
I know that I can obtain the type of the value of the column at index i by
calling sqlite3_value_type() but I also need the name of the column and I
can't find a way to do this.

Is this even possible?

Thank you,

-- 
*João Ramos*
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] non-returned column aliases for repeating expressions?

[E.Pasma]

24 mrt 2018, Wout Mertens:
...

SELECT "id" AS _1,"json" AS _2 FROM "testing"
WHERE json_extract(json, '$.foo') < 50
ORDER BY json_extract(json, '$.foo') DESC,"id"

...

SELECT _1, _2 FROM (
SELECT "id" AS _1,"json" AS _2, json_extract(json, '$.foo') AS _3 FROM
"testing"
WHERE _3 < 50
ORDER BY _3 DESC,"id")

...

24 mrt 2018, Don V Nielsen
...

WITH data AS (
SELECT
  "id"AS _1,
  "json"  AS _2,
  json_extract(json, '$.foo') AS _3
FROM "testing"
)
SELECT *
FROM data
where _3 < 50
ORDER BY _3 DESC, _1

...
Assuming an expression index on data (json_extract(json, '$.foo')),
above queries have identical execution plans.
Moreover, in each case the result of the expression is obtained  
exclusively form the index. Nowhere it is reevaluated, I confirmed  
that using a test function.
Even when the result of the expression is included in the selection,  
like in the last query, it is obtained from the index as used for the  
WHERE or ORDER BY part. That is actually quite good of the optimizer!

The queries must be equally fast.



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] non-returned column aliases for repeating expressions?

[Don V Nielsen]

I'm not the sharpest tool in the shed, here, but a couple of observations /
ideas. First, why in the ORDER clause use _3 and then "id" instead of _1?
Using one alias and not the other is inconsistent and could be confusing.

Personally, I would have did the following. Acquire the data using the CTE.
The select what is needed and order it from the CTE. Being script
generated, I find it really easy to create the primary select as a template
and insert the select within the CTE.

WITH data AS (
SELECT
  "id"AS _1,
  "json"  AS _2,
  json_extract(json, '$.foo') AS _3
FROM "testing"
)
SELECT *
FROM data
where _3 < 50
ORDER BY _3 DESC, _1


I read a lot about efficiency with sqlite here. I guess I've become
complacent about it. I find sqlite so fast that efficiency is distracting.
Just me.

dvn

On Sat, Mar 24, 2018 at 11:31 AM, Wout Mertens 
wrote:

> Hi list,
>
> I often have (autogenerated) queries like
>
> SELECT "id" AS _1,"json" AS _2 FROM "testing"
> WHERE json_extract(json, '$.foo') < 50
> ORDER BY json_extract(json, '$.foo') DESC,"id"
>
> where the json_extract(json, '$.foo') is indexed
>
> I wonder if it would be more efficient to write instead
>
> SELECT _1, _2 FROM (
> SELECT "id" AS _1,"json" AS _2, json_extract(json, '$.foo') AS _3 FROM
> "testing"
> WHERE _3 < 50
> ORDER BY _3 DESC,"id")
>
> , so aliasing the common expression? Or is that just extra work for SQLite?
>
> If I did this, I would want to do it always, also when I'm using the
> expression only once (so only ordering or only filtering)
>
> Wout.
> ___
> 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] non-returned column aliases for repeating expressions?

[Wout Mertens]

Hi list,

I often have (autogenerated) queries like

SELECT "id" AS _1,"json" AS _2 FROM "testing"
WHERE json_extract(json, '$.foo') < 50
ORDER BY json_extract(json, '$.foo') DESC,"id"

where the json_extract(json, '$.foo') is indexed

I wonder if it would be more efficient to write instead

SELECT _1, _2 FROM (
SELECT "id" AS _1,"json" AS _2, json_extract(json, '$.foo') AS _3 FROM
"testing"
WHERE _3 < 50
ORDER BY _3 DESC,"id")

, so aliasing the common expression? Or is that just extra work for SQLite?

If I did this, I would want to do it always, also when I'm using the
expression only once (so only ordering or only filtering)

Wout.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Using computed column once in SELECT

[Keith Medcalf]

>BTW, is ‘now’ value locked during the query execution to avoid the
>possibility (however small) of two columns ending up with different
>age calculations (e.g., running during date crossover on someone’s
>birthday)?

By default, 'now' is step stable (that is, it will return the same value for 
all usage within the same step of a statement) -- ie, for each row.  

You can make it statement stable with a small patch to sqlite3VdbeExec and 
compiling with -DSQLITE_NOW_STABILITY_STMT.  This will cause the 
p->iCurrentTime to be reset ONLY when step 0 of the VDBE program is executed, 
rather than on each step.

  assert( p->rc==SQLITE_OK || (p->rc&0xff)==SQLITE_BUSY );
  assert( p->bIsReader || p->readOnly!=0 );
#ifdef SQLITE_NOW_STABILITY_STMT/* INSERT */
  if (p->pc == 0)   /* INSERT */
#endif  /* INSERT */
  p->iCurrentTime = 0;

I believe that since the addition of the SLOCHG option the value of 'now' is 
statement stable by default (ie, it is deterministic for the purposes of 
indexes but not for the query planner), however I still have the above 
suspenders in place just to be sure it is statement stable.

Since the time is cached in the VDBE it can only be made statement stable (all 
steps in the same statement), not transaction stable (all statements in the 
same transaction).  You would have to write your own User-Defined-Function that 
attaches to the commit/rollback hooks to have the 'now' be transaction stable 
if that was required.





___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Using computed column once in SELECT

[petern]

CTE's can give you the dose of syntactic sugar you're craving:
[No, you can reference other column definitions within the same SELECT
statement.]

WITH w_age_col AS (SELECT *,CAST((julianday('now')-julianday(dob))/365.25
AS INT)age FROM the_table)
SELECT *, age,87-age life_expectancy FROM w_age_col;




On Thu, Nov 23, 2017 at 2:17 PM, Tony Papadimitriou  wrote:

> I don’t know if this has come up before.
>
> Example:
>
> select cast((julianday('now')-julianday(dob))/365.25 as int) age,
>87-cast((julianday('now')-julianday(dob))/365.25 as int)
> life_expectancy
>
> (... assuming 87 year average life span)
>
> This works, but as you see the age calculation has to be repeated in every
> column that needs it (and there could many more).
>
> Why not be able to this instead?
>
> select cast((julianday('now')-julianday(dob))/365.25 as int) age,
>87-age life_expectancy
>
> Note that age is defined before it is referenced.
>
> Apparently, ‘age’ can be used in a subsequent join but not a subsequent
> column definition.
> Is there a technical limitation for this or simply an unimplemented
> feature?
>
> BTW, is ‘now’ value locked during the query execution to avoid the
> possibility (however small) of two columns ending up with different age
> calculations (e.g., running during date crossover on someone’s birthday)?
>
> Thanks.
> ___
> 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] Using computed column once in SELECT

[Tony Papadimitriou]

I don’t know if this has come up before.

Example:

select cast((julianday('now')-julianday(dob))/365.25 as int) age,
   87-cast((julianday('now')-julianday(dob))/365.25 as int) life_expectancy

(... assuming 87 year average life span)

This works, but as you see the age calculation has to be repeated in every 
column that needs it (and there could many more).

Why not be able to this instead?

select cast((julianday('now')-julianday(dob))/365.25 as int) age,
   87-age life_expectancy

Note that age is defined before it is referenced.

Apparently, ‘age’ can be used in a subsequent join but not a subsequent column 
definition.
Is there a technical limitation for this or simply an unimplemented feature?

BTW, is ‘now’ value locked during the query execution to avoid the possibility 
(however small) of two columns ending up with different age calculations (e.g., 
running during date crossover on someone’s birthday)?

Thanks.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Error: ambiguous column name

[Rob Golsteijn]

Hi List,



Given a table created as:   create table aaa(a);

Sqlite reports an error "ambiguous column name: main.aaa.a" for the following 
query.


select * from aaa, aaa;
Error: ambiguous column name: main.aaa.a



And also for similar queries



select * from aaa INNER JOIN aaa;
select * from aaa CROSS JOIN aaa;

select * from aaa JOIN aaa;



Tested with sqlite version 3.21.0 and an old version 3.8.4.3.



I think the query is valid and should not result in an error. Typically Sqlite 
would name the resulting columns "a" and "a:1" in this case.

Workaround: add an alias for one of the tables in the join (both columns will 
be called "a").



Met Vriendelijke Groet, Kind Regards, 谨致问候,

Rob Golsteijn
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Providing incrementing column to query

[Jean-Luc Hainaut]

Some suggestions, the validity of which depend on the context and 
whether some ordering must be preserved.
Let T(C1 primary key,other_data) be the table with which we want to 
associate a sequence number (as pseudo column Seq).


Technique 1: Computing Seq through a recursive CTE.

create table T0 as
with SEQUENCE(Seq,C1,other_data) as
 (select 1,C1,other_data
  from (select C1,other_data
from   T order by C1 limit 1)
   union
select S.Seq+1,T.C1,T.other_data
from   T, SEQUENCES
where  T.C1 = (select min(C1)
   from   T
   where  C1 > S.C1)
  )
select * from SEQUENCE;

Technique 2: Extracting rowid from source table (unordered)

create temp table T1(Seq integer,C1,other_data);
insert into T1
   select rowid as Seq,* from T order by C1;

Technique 3: Extracting rowid from a temp table (ordered)

create temp table T2as
   select 0 as Seq,* from T order by C1;
update T2 set Seq = rowid;

Technique 4: Adding auto incremented column in a temp table

create temp table T3(Seq integer primary key autoincrement,C1,other_data)
insert into T3(C1,other_data) select * from Torder by C1;

Technique 5: From declarative definition - suggestion of (C. Ladish)

create temp table T4as
select (select count(*)
from   TT2
where  T2.C1 <= T1.C1) as Seq,
C1,
other_data
from T T1 order by C1;

For small tables (10-100), the execution times are similar (around 1 
ms.), but for medium-size to large tables, some techniques are better.

For a source table T of 32,000 rows of 30 bytes, there are clear winners:
  - technique 1: 185 ms.
- technique 2: 24 ms.
- technique 3: 58 ms.
- technique 4: 17 ms.
- technique 5: 42,000 ms.

All the scores are linear wrt table size but the declarative one, which 
is quadratic.


Regards

Jean-Luc Hainaut





___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Providing incrementing column to query

[Paul Sanderson]

Actually I just need a unique number - but sorted in code now.

Thank You

Paul
www.sandersonforensics.com
skype: r3scue193
twitter: @sandersonforens
Tel +44 (0)1326 572786
http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit
-Forensic Toolkit for SQLite
email from a work address for a fully functional demo licence

On 24 June 2017 at 15:57, Keith Medcalf <kmedc...@dessus.com> wrote:

>
> Of course, if the traversal order is different than the row return order
> then you will not get ascending logical row numbers unless you do something
> like:
>
> select logicalrow() as SequenceNumber,
>t.*
>   from (...query ...) as t;
>
> If you need logical row numbers it is much better (and far more efficient)
> to assign them in your program as the results are returned.
>
> Out of curiosity, why do you need logical result row numbers since they do
> not correlate with anything meaningful?
>
> --
> ˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı
>
> > -Original Message-
> > From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> > On Behalf Of Paul Sanderson
> > Sent: Saturday, 24 June, 2017 06:18
> > To: SQLite mailing list
> > Subject: Re: [sqlite] Providing incrementing column to query
> >
> > Hmm thanks Clemens
> >
> > Have written an extension to do it - some of my tables are very big and
> > feel that the extension might be a better route.
> >
> > Paul
> > www.sandersonforensics.com
> > skype: r3scue193
> > twitter: @sandersonforens
> > Tel +44 (0)1326 572786
> > http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-
> > Toolkit
> > -Forensic Toolkit for SQLite
> > email from a work address for a fully functional demo licence
> >
> > On 24 June 2017 at 13:10, Clemens Ladisch <clem...@ladisch.de> wrote:
> >
> > > Paul Sanderson wrote:
> > > > I Have a number of queries to which I want to supply an incrementing
> > > column,
> > > > some of these queries involve without rowid tables.
> > > >
> > > > Is there a way of doing this with a SQL query?
> > >
> > > First, define a sort order, and ensure that it does not have
> duplicates.
> > > Then use a correlated subquery to count how many rows would come before
> > > the current one in that order:
> > >
> > >   SELECT (SELECT count(*)
> > >   FROM MyTable AS T2
> > >   WHERE T2.name <= MyTable.Name
> > >  ) AS row_number,
> > >  name,
> > >  age
> > >   FROM MyTable
> > >   ORDER BY name;
> > >
> > > It would be a better idea to count returned rows in your program.
> > >
> > >
> > > Regards,
> > > Clemens
> > > ___
> > > 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

Re: [sqlite] Providing incrementing column to query

[Keith Medcalf]

Of course, if the traversal order is different than the row return order then 
you will not get ascending logical row numbers unless you do something like:

select logicalrow() as SequenceNumber, 
   t.*
  from (...query ...) as t;

If you need logical row numbers it is much better (and far more efficient) to 
assign them in your program as the results are returned.

Out of curiosity, why do you need logical result row numbers since they do not 
correlate with anything meaningful?

-- 
˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı

> -Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> On Behalf Of Paul Sanderson
> Sent: Saturday, 24 June, 2017 06:18
> To: SQLite mailing list
> Subject: Re: [sqlite] Providing incrementing column to query
> 
> Hmm thanks Clemens
> 
> Have written an extension to do it - some of my tables are very big and
> feel that the extension might be a better route.
> 
> Paul
> www.sandersonforensics.com
> skype: r3scue193
> twitter: @sandersonforens
> Tel +44 (0)1326 572786
> http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-
> Toolkit
> -Forensic Toolkit for SQLite
> email from a work address for a fully functional demo licence
> 
> On 24 June 2017 at 13:10, Clemens Ladisch <clem...@ladisch.de> wrote:
> 
> > Paul Sanderson wrote:
> > > I Have a number of queries to which I want to supply an incrementing
> > column,
> > > some of these queries involve without rowid tables.
> > >
> > > Is there a way of doing this with a SQL query?
> >
> > First, define a sort order, and ensure that it does not have duplicates.
> > Then use a correlated subquery to count how many rows would come before
> > the current one in that order:
> >
> >   SELECT (SELECT count(*)
> >   FROM MyTable AS T2
> >   WHERE T2.name <= MyTable.Name
> >  ) AS row_number,
> >  name,
> >  age
> >   FROM MyTable
> >   ORDER BY name;
> >
> > It would be a better idea to count returned rows in your program.
> >
> >
> > Regards,
> > Clemens
> > ___
> > 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

Re: [sqlite] Providing incrementing column to query

[Paul Sanderson]

Hmm thanks Clemens

Have written an extension to do it - some of my tables are very big and
feel that the extension might be a better route.

Paul
www.sandersonforensics.com
skype: r3scue193
twitter: @sandersonforens
Tel +44 (0)1326 572786
http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit
-Forensic Toolkit for SQLite
email from a work address for a fully functional demo licence

On 24 June 2017 at 13:10, Clemens Ladisch  wrote:

> Paul Sanderson wrote:
> > I Have a number of queries to which I want to supply an incrementing
> column,
> > some of these queries involve without rowid tables.
> >
> > Is there a way of doing this with a SQL query?
>
> First, define a sort order, and ensure that it does not have duplicates.
> Then use a correlated subquery to count how many rows would come before
> the current one in that order:
>
>   SELECT (SELECT count(*)
>   FROM MyTable AS T2
>   WHERE T2.name <= MyTable.Name
>  ) AS row_number,
>  name,
>  age
>   FROM MyTable
>   ORDER BY name;
>
> It would be a better idea to count returned rows in your program.
>
>
> Regards,
> Clemens
> ___
> 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

Re: [sqlite] Providing incrementing column to query

[Clemens Ladisch]

Paul Sanderson wrote:
> I Have a number of queries to which I want to supply an incrementing column,
> some of these queries involve without rowid tables.
>
> Is there a way of doing this with a SQL query?

First, define a sort order, and ensure that it does not have duplicates.
Then use a correlated subquery to count how many rows would come before
the current one in that order:

  SELECT (SELECT count(*)
  FROM MyTable AS T2
  WHERE T2.name <= MyTable.Name
 ) AS row_number,
 name,
 age
  FROM MyTable
  ORDER BY name;

It would be a better idea to count returned rows in your program.


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Providing incrementing column to query

[Paul Sanderson]

I Have a number of queries to which I want to supply an incrementing column,
some of these queries involve without rowid tables.

 

I have no control over the design of the tables.

 

So for a table defined as: 

 

CREATE TABLE (name text, age integer) with values

 

Steve, 34

Eric, 27

Denis, 41

 

I want to return a result set

 

1, Steve, 34

2, Eric, 27

3, Denis, 41

 

 

Is there a way of doing this with a SQL query?

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] no such column error

[Bart Smissaert]

Hi Rob,

I can see your point, but couldn't tell you if this should be considered a
bug or not.
Probably only DRH can tell you that.

RBS

On Thu, Oct 27, 2016 at 8:21 AM, Rob Golsteijn <rob.golste...@mapscape.eu>
wrote:

> Hi RBS,
>
> Re-introducing mytable in the sub-select is a workaround (and to get the
> same semantics in the general case I have to use the same row from the
> inner mytable and outer mytable). As indicated in my original message I
> already have a workaround for the issue.
>
> The intention of my post was to report that sqlite incorrectly reports
> that mytable does not exists. Table mytable exists --- it is the target
> table of the UPDATE statement and still in scope. I see no reason why it is
> not allowed to use it in that EXISTS expression.
>
> So my question: is there a valid reason why sqlite does not know
> mytable.myfield2 in my simplified query? Or is this a bug?
>
>
>
> Regards,
>
> Rob Golsteijn
>
>
>
> UPDATE mytable
> SET myfield1 = (SELECT 1 from mytable
>   ORDER BY EXISTS (SELECT 1
> WHERE mytable.myfield2 = 1
>   )
>)
>
>
> RBS
>
> On Tue, Oct 25, 2016 at 4:40 PM, Rob Golsteijn <rob.golste...@mapscape.eu>
> wrote:
>
> > Hi List,
> >
> > I encountered a situation in which sqlite does not understand to which
> > field I try to refer. I simplified the original query to show the
> problem.
> > The simplified query itself is now completely meaningless (and for my
> > specific situation I could rewrite the query to work around the problem).
> >
> > In the example below I expected that mytable.myfield2 in the EXISTS
> > expression would refer to myfield2 of table mytable from the top level
> > UPDATE statement. It looks like the combination of an ORDER BY and an
> > EXISTS that refers to the table of an UPDATE statement causes sqlite to
> > report that myfield2 is unknown. Sqlite does not complain when I use
> > mytable.myfield2 in other places in the query.
> >
> > CREATE TABLE mytable
> > (
> >myfield1 INTEGER,
> >myfield2 INTEGER
> > );
> >
> > UPDATE mytable
> >SET myfield1 = (SELECT 1
> >  ORDER BY EXISTS (SELECT 1
> >WHERE mytable.myfield2 =1
> >  )
> >   );
> >
> > Error: no such column: mytable.myfield2
> >
> > Tested with sqlite versions 3.8.4.3,  3.8.8.2,   3.11.1 and 3.15.0
> > (latest).
> >
> > Met Vriendelijke Groet, Kind Regards,
> >
> > Rob Golsteijn
>
>
> ___
> 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

Re: [sqlite] no such column error

[Rob Golsteijn]

Hi RBS,

Re-introducing mytable in the sub-select is a workaround (and to get the same 
semantics in the general case I have to use the same row from the inner mytable 
and outer mytable). As indicated in my original message I already have a 
workaround for the issue.

The intention of my post was to report that sqlite incorrectly reports that 
mytable does not exists. Table mytable exists --- it is the target table of the 
UPDATE statement and still in scope. I see no reason why it is not allowed to 
use it in that EXISTS expression.

So my question: is there a valid reason why sqlite does not know 
mytable.myfield2 in my simplified query? Or is this a bug?



Regards,

Rob Golsteijn



UPDATE mytable
SET myfield1 = (SELECT 1 from mytable
  ORDER BY EXISTS (SELECT 1
WHERE mytable.myfield2 = 1
  )
   )


RBS

On Tue, Oct 25, 2016 at 4:40 PM, Rob Golsteijn <rob.golste...@mapscape.eu>
wrote:

> Hi List,
>
> I encountered a situation in which sqlite does not understand to which
> field I try to refer. I simplified the original query to show the problem.
> The simplified query itself is now completely meaningless (and for my
> specific situation I could rewrite the query to work around the problem).
>
> In the example below I expected that mytable.myfield2 in the EXISTS
> expression would refer to myfield2 of table mytable from the top level
> UPDATE statement. It looks like the combination of an ORDER BY and an
> EXISTS that refers to the table of an UPDATE statement causes sqlite to
> report that myfield2 is unknown. Sqlite does not complain when I use
> mytable.myfield2 in other places in the query.
>
> CREATE TABLE mytable
> (
>myfield1 INTEGER,
>myfield2 INTEGER
> );
>
> UPDATE mytable
>SET myfield1 = (SELECT 1
>  ORDER BY EXISTS (SELECT 1
>WHERE mytable.myfield2 =1
>      )
>   );
>
> Error: no such column: mytable.myfield2
>
> Tested with sqlite versions 3.8.4.3,  3.8.8.2,   3.11.1 and 3.15.0
> (latest).
>
> Met Vriendelijke Groet, Kind Regards,
>
> Rob Golsteijn


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] no such column error

[R Smith]

On 2016/10/25 5:44 PM, Bart Smissaert wrote:

Try this:

UPDATE mytable
 SET myfield1 = (SELECT 1 from mytable
   ORDER BY EXISTS (SELECT 1
 WHERE mytable.myfield2 = 1
   )
)


RBS


Thing is, what you propose specifically negates the point the OP is 
trying to make, in that a sub-query of a sub-query can't see the main 
query definitions when specified in an ORDER BY clause.


Your fix puts the definitions within reach, which simply hides the problem.

Myself, I do not actually know whether definitions in a SELECT /can/ in 
fact be seen n-levels of sub-query deep... It doesn't seem to be 
documented in SQLite and I can't find an SQL-Standard reference now that 
specifically demands it, but it seems possible (in SQLite at least) when 
the references happen in the SELECT or WHERE clauses of the 
sub-sub-sub-query. It however doesn't work when referred-to in the ORDER 
BY clause of the same - which is the OP's original point I think.


So to be clear, I do not know which way this should fly, but it does 
seem inconsistent in the current implementation. (Perhaps inconsistent 
is not the right word, it doesn't change from edition to edition, it 
just seem to not act the same way in all clauses, although consistently 
so through the latest editions.)


Cheers,
Ryan


PS to the OP: Kudos for not labeling it a "bug", Rob.

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] no such column error

[Bart Smissaert]

Try this:

UPDATE mytable
SET myfield1 = (SELECT 1 from mytable
  ORDER BY EXISTS (SELECT 1
WHERE mytable.myfield2 = 1
  )
   )


RBS

On Tue, Oct 25, 2016 at 4:40 PM, Rob Golsteijn <rob.golste...@mapscape.eu>
wrote:

> Hi List,
>
> I encountered a situation in which sqlite does not understand to which
> field I try to refer. I simplified the original query to show the problem.
> The simplified query itself is now completely meaningless (and for my
> specific situation I could rewrite the query to work around the problem).
>
> In the example below I expected that mytable.myfield2 in the EXISTS
> expression would refer to myfield2 of table mytable from the top level
> UPDATE statement. It looks like the combination of an ORDER BY and an
> EXISTS that refers to the table of an UPDATE statement causes sqlite to
> report that myfield2 is unknown. Sqlite does not complain when I use
> mytable.myfield2 in other places in the query.
>
> CREATE TABLE mytable
> (
>myfield1 INTEGER,
>myfield2 INTEGER
> );
>
> UPDATE mytable
>SET myfield1 = (SELECT 1
>  ORDER BY EXISTS (SELECT 1
>WHERE mytable.myfield2 =1
>      )
>   );
>
> Error: no such column: mytable.myfield2
>
> Tested with sqlite versions 3.8.4.3,  3.8.8.2,   3.11.1 and 3.15.0
> (latest).
>
> Met Vriendelijke Groet, Kind Regards,
>
> Rob Golsteijn
>
> ___
> 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] no such column error

[Rob Golsteijn]

Hi List,

I encountered a situation in which sqlite does not understand to which field I 
try to refer. I simplified the original query to show the problem. The 
simplified query itself is now completely meaningless (and for my specific 
situation I could rewrite the query to work around the problem).

In the example below I expected that mytable.myfield2 in the EXISTS expression 
would refer to myfield2 of table mytable from the top level UPDATE statement. 
It looks like the combination of an ORDER BY and an EXISTS that refers to the 
table of an UPDATE statement causes sqlite to report that myfield2 is unknown. 
Sqlite does not complain when I use mytable.myfield2 in other places in the 
query.

CREATE TABLE mytable
(
   myfield1 INTEGER,
   myfield2 INTEGER
);

UPDATE mytable
   SET myfield1 = (SELECT 1
 ORDER BY EXISTS (SELECT 1
   WHERE mytable.myfield2 =1
 )
  );

Error: no such column: mytable.myfield2

Tested with sqlite versions 3.8.4.3,  3.8.8.2,   3.11.1 and 3.15.0 (latest).

Met Vriendelijke Groet, Kind Regards,

Rob Golsteijn

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Referencing a column alias (subquery) as function parameter

[R Smith]

Just a fix - the last example had a mistake in and should read:

Option 2 (CTE):

   WITH Q (c1, c2, cd, ac) AS (
   SELECT  c1, c2, CurrentDate, (CASE ActiveCol WHEN 0 THEN c3 ELSE 
c4 END)

 FROM ...
   )
   SELECT c1, c2, cd, ac, MyFn(c1,ac,cd) AS xc1, MyFn(c2,ac,cd) AS xc2
  FROM Q
 WHERE ...




___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Referencing a column alias (subquery) as function parameter

[R Smith]

On 2016/06/20 6:21 PM, Bernard Ertl wrote:

Is it possible to do something like this:

SELECT c1, c2, CurrentDate, (CASE ActiveCol WHEN 0 THEN c3 ELSE c4 END) AS ac,
  MyFn(c1,ac,CurrentDate) AS xc1, MyFn(c2,ac,CurrentDate) AS xc2 FROM ...

where c1, c2, CurrentDate, ActiveCol, c3, c4 are all columns in the table(s) 
referenced in the FROM
clause?

When I try to run the query, I get errors that ac isn't a valid column.  Is
it not possible to reference aliased columns as a parameter to a (custom)
function?  I could just put the CASE statement in the (MyFn) function's
parameter field, but I'm calling the function 4 times in a single SELECT
statement.  Wouldn't that cause SQLite to evaluate the CASE statement each
time?


You cannot dereference an alias in a same-level query clause directly 
since it is hard to predict it's intended value kind (Concats, groups 
concats and custom User functions especially) and there is no telling 
what data type its result may end up as, and other less important 
problems. It's nothing to do with custom functions, you can't even use 
an alias in a simple built-in function, even this is illegal:
SELECT (x+5) AS MyX, (MyX * 2) AS My2X... -- Illegal since MyX is not a 
valid column. You can see why this is a problem if you imagine the 
following:

SELECT (x||'-'||y) AS XY, (XY * 3) AS 3XY, ...
- OR -
SELECT (M + Z) AS MZ, (MZ - M) AS Z, ... - Obviously impossible.


Of course, if you complete the output of the query into a table/set of 
which these things can be known, and then select from that set, 
everything becomes possible again.


Many ways to skin that cat in SQLite, but the two most common is using a 
Query as a table or using a Common Table Expression (CTE). I will try to 
make queries here demonstrating both, but I don't have the sort of data 
or schema you have, so they are untested. Feel free to ask if you don't 
get these to work, we'll try fix it.



Option 1 (Sub-Query):

   SELECT c1, c2, CurrentDate, ac, MyFn(c1,ac,CurrentDate) AS xc1, 
MyFn(c2,ac,CurrentDate) AS xc2
  FROM (
SELECT  c1, c2, CurrentDate, (CASE ActiveCol WHEN 0 THEN c3 ELSE c4 
END) AS ac
  FROM ...
) AS Q
 WHERE ...


Option 2 (CTE):

   WITH Q (c1, c2, cd, ac) AS (
SELECT  c1, c2, CurrentDate, (CASE ActiveCol WHEN 0 THEN c3 ELSE c4 END)
  FROM ...
)
   SELECT c1, c2, cd, ac, MyFn(c1,ac,CurrentDate) AS xc1, 
MyFn(c2,ac,CurrentDate) AS xc2
  FROM Q
 WHERE ...



Good luck!
Ryan

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Referencing a column alias (subquery) as function parameter

[Scott Robison]

On Mon, Jun 20, 2016 at 10:21 AM, Bernard Ertl  wrote:

> Is it possible to do something like this:
>
> SELECT c1, c2, CurrentDate, (CASE ActiveCol WHEN 0 THEN c3 ELSE c4 END) AS
> ac,
>  MyFn(c1,ac,CurrentDate) AS xc1, MyFn(c2,ac,CurrentDate) AS xc2 FROM
> ...
>
> where c1, c2, CurrentDate, ActiveCol, c3, c4 are all columns in the
> table(s) referenced in the FROM
> clause?
>
> When I try to run the query, I get errors that ac isn't a valid column.  Is
> it not possible to reference aliased columns as a parameter to a (custom)
> function?  I could just put the CASE statement in the (MyFn) function's
> parameter field, but I'm calling the function 4 times in a single SELECT
> statement.  Wouldn't that cause SQLite to evaluate the CASE statement each
> time?
>

Try something like this:

select c1, c2, CurrentDate, ac, MyFn(c1,ac,CurrentDate) as xc1,
MyFn(c2,ac,CurrentDate) from (select c1, c2, CurrentDate, (case ActiveCol
when 0 then c3 else c4 end) as ac from test)

Other possibilities exist to avoid restating the case expression mutiple
times, but this should work. Haven't tested it with real data because,
well, I don't have real data with which to test.

-- 
Scott Robison
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Referencing a column alias (subquery) as function parameter

[Bernard Ertl]

Is it possible to do something like this:

SELECT c1, c2, CurrentDate, (CASE ActiveCol WHEN 0 THEN c3 ELSE c4 END) AS ac,
 MyFn(c1,ac,CurrentDate) AS xc1, MyFn(c2,ac,CurrentDate) AS xc2 FROM ...

where c1, c2, CurrentDate, ActiveCol, c3, c4 are all columns in the table(s) 
referenced in the FROM
clause?

When I try to run the query, I get errors that ac isn't a valid column.  Is
it not possible to reference aliased columns as a parameter to a (custom)
function?  I could just put the CASE statement in the (MyFn) function's
parameter field, but I'm calling the function 4 times in a single SELECT
statement.  Wouldn't that cause SQLite to evaluate the CASE statement each
time?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Why sqlite show qualified column names when selecting from views ?

[Domingo Alvarez Duarte]

So in your case (delphi usage) how do you do when using postgresql ?  

Cheers !  
>  Wed Aug 26 2015 4:52:02 pm CEST CEST from "Stephen Chrzanowski"
>  Subject: Re: [sqlite] Why sqlite show qualified 
>column
>names when selecting from views ?
>
>  I'm sure either #1 or #3 happens already. I know I've been nagged about
> ambiguous field names, but I also know that somewhere I've seen _1 or _2 be
> appended to field names (That might be just the SQL IDE I use doing that).
> 
> I can't get on board with #2 because most wrappers only look at the first
> value, or, would overwrite "SalesTotal" with whatever the last column has
> the name SalesTotal. The wrapper I use in Delphi uses both named field
> arrays (IE: tbl.FieldByName['SalesTotal']) as well as field numbers (IE:
> tbl.FieldByID[1]) but I try to use the field names since the order of the
> field names COULD change.
> 
> 
> On Wed, Aug 26, 2015 at 10:12 AM, Domingo Alvarez Duarte <
> sqlite-mail at dev.dadbiz.es> wrote:
> 
>  
>>Hello !
>> 
>> Three possibilities:
>> 
>> 1- Show a qualified name to disambiguate.
>> 
>> 2 - Show duplicated column names.
>> 
>> 3 - Show error message message about ambiguos column name.
>> 
>> 
>> 
>> On the case mentioned apply rule 2, like postgresql do.
>> 
>> Cheers !
>> 
>> 
>>  
>>>Wed Aug 26 2015 2:35:41 pm CEST CEST from "Simon Slavin"
>>>  Subject: Re: [sqlite] Why sqlite show qualified
>>> column names when selecting from views ?
>>> 
>>> On 26 Aug 2015, at 10:17am, Domingo Alvarez Duarte
>>>  wrote:
>>> 
>>> 
>>>  
>>>>But like it's already done on several other cases why not fix this issue
>>>> and
>>>> wrap this fix with "#ifdef SQLITE_CLEAN_COLUMN_NAMES" that's not defined
>>>> by
>>>> default so older code will remain working but new code can enable this

>>>  

>>  and
>>  
>>>  
>>>>use it ?
>>>> 
>>>> 

>>>  With that definition set, suppose you execute the following two

>>  commands:
>> 
>>  
>>>ATTACH DATABASE 'August' AS salesThisMonth;
>>> SELECT clientName, salesThisMonth.salesTotal, salesTotal
>>> FROM sales
>>> JOIN salesThisMonth ON salesThisMonth.clientId = sales.clientId
>>> ORDER BY clientName;
>>> 
>>> What should the name be for the second column ?
>>> 
>>> Also, with
>>> 
>>> SELECT a, b, 100*a/b FROM myTable;
>>> 
>>> What should the name of the third column be ?
>>> 
>>> Simon.
>>> ___
>>> sqlite-users mailing list
>>> sqlite-users at mailinglists.sqlite.org
>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>> 
>>> 
>>> 
>>> 
>>> 
>>> 
>>> 

>>  ___
>> sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>> 

>  ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> 
>
>  



?

[sqlite] Why sqlite show qualified column names when selecting from views ?

[Domingo Alvarez Duarte]

Hello !  

Three possibilities:  

1- Show a qualified name to disambiguate.  

2 - Show duplicated column names.  

3 - Show error message message about ambiguos column name.  

?  

On the case mentioned apply rule 2, like postgresql do.  

Cheers !  

?  
>  Wed Aug 26 2015 2:35:41 pm CEST CEST from "Simon Slavin"
>  Subject: Re: [sqlite] Why sqlite show qualified
>column names when selecting from views ?
>
>  On 26 Aug 2015, at 10:17am, Domingo Alvarez Duarte
> wrote:
> 
>  
>>But like it's already done on several other cases why not fix this issue
>>and
>> wrap this fix with "#ifdef SQLITE_CLEAN_COLUMN_NAMES" that's not defined
>>by
>> default so older code will remain working but new code can enable this and
>> use it ?
>> 

>  With that definition set, suppose you execute the following two commands:
> 
> ATTACH DATABASE 'August' AS salesThisMonth;
> SELECT clientName, salesThisMonth.salesTotal, salesTotal
> FROM sales
> JOIN salesThisMonth ON salesThisMonth.clientId = sales.clientId
> ORDER BY clientName;
> 
> What should the name be for the second column ?
> 
> Also, with
> 
> SELECT a, b, 100*a/b FROM myTable;
> 
> What should the name of the third column be ?
> 
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> 
>
>  



?

[sqlite] Why sqlite show qualified column names when selecting from views ?

[Simon Slavin]

On 26 Aug 2015, at 10:17am, Domingo Alvarez Duarte  wrote:

> But like it's already done on several other cases why not fix this issue and
> wrap this fix with "#ifdef SQLITE_CLEAN_COLUMN_NAMES" that's not defined by
> default so older code will remain working but new code can enable this and
> use it ?

With that definition set, suppose you execute the following two commands:

ATTACH DATABASE 'August' AS salesThisMonth;
SELECT clientName, salesThisMonth.salesTotal, salesTotal
FROM sales
JOIN salesThisMonth ON salesThisMonth.clientId = sales.clientId
ORDER BY clientName;

What should the name be for the second column ?

Also, with

SELECT a, b, 100*a/b FROM myTable;

What should the name of the third column be ?

Simon.

[sqlite] Why sqlite show qualified column names when selecting from views ?

[Domingo Alvarez Duarte]

I just tested this on postgresql and I always get unqualified column names as
I would expect.  

I remember this was a lot worst in previous sqlite versions and I needed to
play with two compile time/pragma flags and depending on that combination we
got some bizarre results.  

I understand that there is legacy code that should take in consideration when
changing sqlite behavior.  

But like it's already done on several other cases why not fix this issue and
wrap this fix with "#ifdef SQLITE_CLEAN_COLUMN_NAMES" that's not defined by
default so older code will remain working but new code can enable this and
use it ?  

?  

Cheers !  

?
-- next part --
An embedded and charset-unspecified text was scrubbed...
Name: 
URL: 

[sqlite] Why sqlite show qualified column names when selecting from views ?

[Stephen Chrzanowski]

I'm sure either #1 or #3 happens already.  I know I've been nagged about
ambiguous field names, but I also know that somewhere I've seen _1 or _2 be
appended to field names (That might be just the SQL IDE I use doing that).

I can't get on board with #2 because most wrappers only look at the first
value, or, would overwrite "SalesTotal" with whatever the last column has
the name SalesTotal.  The wrapper I use in Delphi uses both named field
arrays (IE: tbl.FieldByName['SalesTotal']) as well as field numbers (IE:
tbl.FieldByID[1]) but I try to use the field names since the order of the
field names COULD change.


On Wed, Aug 26, 2015 at 10:12 AM, Domingo Alvarez Duarte <
sqlite-mail at dev.dadbiz.es> wrote:

> Hello !
>
> Three possibilities:
>
> 1- Show a qualified name to disambiguate.
>
> 2 - Show duplicated column names.
>
> 3 - Show error message message about ambiguos column name.
>
>
>
> On the case mentioned apply rule 2, like postgresql do.
>
> Cheers !
>
>
> >  Wed Aug 26 2015 2:35:41 pm CEST CEST from "Simon Slavin"
> >  Subject: Re: [sqlite] Why sqlite show qualified
> >column names when selecting from views ?
> >
> >  On 26 Aug 2015, at 10:17am, Domingo Alvarez Duarte
> > wrote:
> >
> >
> >>But like it's already done on several other cases why not fix this issue
> >>and
> >> wrap this fix with "#ifdef SQLITE_CLEAN_COLUMN_NAMES" that's not defined
> >>by
> >> default so older code will remain working but new code can enable this
> and
> >> use it ?
> >>
>
> >  With that definition set, suppose you execute the following two
> commands:
> >
> > ATTACH DATABASE 'August' AS salesThisMonth;
> > SELECT clientName, salesThisMonth.salesTotal, salesTotal
> > FROM sales
> > JOIN salesThisMonth ON salesThisMonth.clientId = sales.clientId
> > ORDER BY clientName;
> >
> > What should the name be for the second column ?
> >
> > Also, with
> >
> > SELECT a, b, 100*a/b FROM myTable;
> >
> > What should the name of the third column be ?
> >
> > Simon.
> > ___
> > sqlite-users mailing list
> > sqlite-users at mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> >
> >
>
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] Why sqlite show qualified column names when selecting from views ?

[Simon Slavin]

On 25 Aug 2015, at 10:27pm, Tim Streater  wrote:

> So how does that work with:
> 
>  create table newtable as select * from oldtable;
> 
> Does that mean that, in principle, I could have any random set of column 
> names for my new table?

Future versions of SQLite can do anything.  In real life, for that specific 
command, for column names which are just letters and digits, you'll get what 
you expect.  However ...

If you have column names surrounded by double-quotes or square brackets, do you 
expect them to be part of the column names ?  The SQL specification is a little 
weird on the issue.

Also, suppose you did
create table newtable as select * from myView

and one of the view columns was a calculation.  What do you expect the name of 
the corresponding column to be ?

> Doing this properly is also going to mean that, for safety, I've got 306 
> select statements to fix up in my application. It's gonna be a bit dull if I 
> have one where I select 20 named columns from a table and have to change such 
> as:
> 
>  select absid, firstname, lastname, phone, ...
> 
> to:
> 
>  select absid as absid, firstname as firstname, lastname as lastname, phone 
> as phone, ...

Alternatively you could use the columns which are in specific places.  For 
example, if lastname is the third column of the SELECT command, its values will 
always be the third column of the response.  Not only will this always work but 
it will be faster since it's faster to process the index 3 than to look up a 
string.

Simon.

[sqlite] Why sqlite show qualified column names when selecting from views ?

[Jean-Christophe Deschamps]

At 22:28 25/08/2015, you wrote:

>In the next version of SQLite3 the names of all columns will be 
>'columnname'

"Don't trust me" could be more apropriate. 

[sqlite] Why sqlite show qualified column names when selecting from views ?

[Tim Streater]

On 25 Aug 2015 at 19:37, Richard Hipp  wrote:

> On 25/8/15, Simon Slavin  wrote:
>>
>> "If there is no AS clause then the name of the column is unspecified and may
>> change from one release of SQLite to the next."

> Heed this warning!!!
>
> And yet, there are countless tens of thousands of applications in
> circulation that have ignored this warning and depend on the
> (unsupported) behavior of whatever version of SQLite they were
> originally developed on.  So the reality is that we are extremely
> careful not to change the result column naming algorithms, for fear of
> breaking billions of cellphone apps.

So how does that work with:

  create table newtable as select * from oldtable;

Does that mean that, in principle, I could have any random set of column names 
for my new table?

Doing this properly is also going to mean that, for safety, I've got 306 select 
statements to fix up in my application. It's gonna be a bit dull if I have one 
where I select 20 named columns from a table and have to change such as:

  select absid, firstname, lastname, phone, ...

to:

  select absid as absid, firstname as firstname, lastname as lastname, phone as 
phone, ...

Recipe for typos there, ISTM. Grumble, grumble.

--
Cheers  --  Tim

[sqlite] Why sqlite show qualified column names when selecting from views ?

[Simon Slavin]

On 25 Aug 2015, at 8:52pm, Scott Robison  wrote:

> On Aug 25, 2015 1:02 PM, "Petite Abeille"  wrote:
> 
>> On Aug 25, 2015, at 8:53 PM, R.Smith  wrote:
>> 
>>> I vote to change it every release... Stimulate better habits!
>> 
>> Seconded. Keep them on their toes!
> 
> Or randomly generate names after every prepare! Or just leave them
> anonymous.

In the next version of SQLite3 the names of all columns will be 'columnname'.

Simon.

[sqlite] Why sqlite show qualified column names when selecting from views ?

[Petite Abeille]

> On Aug 25, 2015, at 8:53 PM, R.Smith  wrote:
> 
> I vote to change it every release... Stimulate better habits!

Seconded. Keep them on their toes!

[sqlite] Why sqlite show qualified column names when selecting from views ?

[R.Smith]

On 2015-08-25 08:37 PM, Richard Hipp wrote:
> On 8/25/15, Simon Slavin  wrote:
>> "If there is no AS clause then the name of the column is unspecified and may
>> change from one release of SQLite to the next."
>>
> Heed this warning!!!
>
> And yet, there are countless tens of thousands of applications in
> circulation that have ignored this warning and depend on the
> (unsupported) behavior of whatever version of SQLite they were
> originally developed on.  So the reality is that we are extremely
> careful not to change the result column naming algorithms, for fear of
> breaking billions of cellphone apps.

I vote to change it every release... Stimulate better habits!

[sqlite] Why sqlite show qualified column names when selecting from views ?

[Stephan Beal]

The behaviour you're asking about is explicitly undefined in sqlite. Today
it might work like you are reporting and tomorrow it might do something
different. The ONLY way to guarantee specific column names is to do what
Simon suggested: always use "as". If you don't, the exact results are
undefined.

- stephan
(Sent from a mobile device, possibly from bed. Please excuse brevity,
typos, and top-posting.)
On Aug 25, 2015 19:52, "sqlite-mail"  wrote:

> First of all thanks for reply !
>
> I can understand your point, what confuses me is that when we query a view
> with "a.*" it doesn't qualify the names so I thought that was a mistake
> when
> we use individual names otherwise I would expected qualified names there
> too.
>
>
> Cheers !
> >  Tue Aug 25 2015 19:45:27 CEST from "Simon Slavin"  >
> >Subject: Re: [sqlite] Why sqlite show qualified column names when
> selecting
> >from views ?
> >
> >  On 25 Aug 2015, at 6:13pm, sqlite-mail 
> wrote:
> >
> >
> >>When querying views sqlite shows qualified column names if they are
> >>specified
> >> individually.
> >>
>
> >  Sorry but this has been mentioned a few times here and won't be changed.
> >The SQL standard doesn't mention column names so SQL engines are free to
> do
> >what they want.
> >
> > In SQLite you can depend on column names only if you have specified them
> >using 'AS'. So I would expect, but haven't tested right now ...
> >
> >
> >>SQL: SELECT a.* FROM tbl_view AS a;
> >> Column 0: id
> >> Column 1: name
> >> SQL: SELECT a.id, a.name FROM tbl_view AS a;
> >> Column 0: a.id <<<<<<<<<<<<<<<<<<<<< only with individual fields
> >> Column 1: a.name <<<<<<<<<<<<<<<<<<<
> >>
> >>
>
> >  SQL: SELECT a.id AS id, a.name AS name FROM tbl_view AS a;
> > Column 0: id
> > Column 1: name
> >
> > Simon.
> > ___
> > sqlite-users mailing list
> > sqlite-users at mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> >
> >
>
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] Why sqlite show qualified column names when selecting from views ?

[sqlite-mail]

First of all thanks for reply !  

I can understand your point, what confuses me is that when we query a view
with "a.*" it doesn't qualify the names so I thought that was a mistake when
we use individual names otherwise I would expected qualified names there too.


Cheers !  
>  Tue Aug 25 2015 19:45:27 CEST from "Simon Slavin"  
>Subject: Re: [sqlite] Why sqlite show qualified column names when selecting
>from views ?
>
>  On 25 Aug 2015, at 6:13pm, sqlite-mail  wrote:
> 
>  
>>When querying views sqlite shows qualified column names if they are
>>specified
>> individually. 
>> 

>  Sorry but this has been mentioned a few times here and won't be changed.
>The SQL standard doesn't mention column names so SQL engines are free to do
>what they want.
> 
> In SQLite you can depend on column names only if you have specified them
>using 'AS'. So I would expect, but haven't tested right now ...
> 
>  
>>SQL: SELECT a.* FROM tbl_view AS a;
>> Column 0: id
>> Column 1: name
>> SQL: SELECT a.id, a.name FROM tbl_view AS a;
>> Column 0: a.id <<<<<<<<<<<<<<<<<<<<< only with individual fields 
>> Column 1: a.name <<<<<<<<<<<<<<<<<<<
>> 
>> 

>  SQL: SELECT a.id AS id, a.name AS name FROM tbl_view AS a;
> Column 0: id
> Column 1: name
> 
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> 
>
>  



?

[sqlite] Why sqlite show qualified column names when selecting from views ?

[Richard Hipp]

On 8/25/15, Tim Streater  wrote:
> On 25 Aug 2015 at 19:37, Richard Hipp  wrote:
>
>> On 25/8/15, Simon Slavin  wrote:
>>>
>>> "If there is no AS clause then the name of the column is unspecified and
>>> may
>>> change from one release of SQLite to the next."
>
>> Heed this warning!!!
>>
>> And yet, there are countless tens of thousands of applications in
>> circulation that have ignored this warning and depend on the
>> (unsupported) behavior of whatever version of SQLite they were
>> originally developed on.  So the reality is that we are extremely
>> careful not to change the result column naming algorithms, for fear of
>> breaking billions of cellphone apps.
>
> So how does that work with:
>
>   create table newtable as select * from oldtable;
>
> Does that mean that, in principle, I could have any random set of column
> names for my new table?
>

Yeah.  That's a bummer, isn't it

But in practice, our default column name chooser algorithm does what
you want here, and as I stated before, we are in no hurry to change
it.


-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] Why sqlite show qualified column names when selecting from views ?

[Simon Slavin]

On 25 Aug 2015, at 6:52pm, sqlite-mail  wrote:

> I can understand your point, what confuses me is that when we query a view
> with "a.*" it doesn't qualify the names so I thought that was a mistake when
> we use individual names otherwise I would expected qualified names there too.

I understand that this is not what one would expect.  Worse still, as the 
documentation says ...



"If there is no AS clause then the name of the column is unspecified and may 
change from one release of SQLite to the next."

If you expect your own code to know what columns are called use 'AS' for each 
column.

Simon.

[sqlite] Why sqlite show qualified column names when selecting from views ?

[sqlite-mail]

Hello !  

When querying views sqlite shows qualified column names if they are specified
individually.  

Is this the expected result or a bug ?  

This behavior breaks some of my code that uses column names for other
purposes.  

Cheers !  

output of "test-view-alias"  

SQL: SELECT a.* FROM tbl AS a;
Column 0: id
Column 1: name
SQL: SELECT a.id, a.name FROM tbl AS a;
Column 0: id
Column 1: name
SQL: SELECT a.* FROM tbl_view AS a;
Column 0: id
Column 1: name
SQL: SELECT a.id, a.name FROM tbl_view AS a;
Column 0: a.id?? <<<<<<<<<<<<<<<<<<<<< only with individual fields 
Column 1: a.name <<<<<<<<<<<<<<<<<<<
  

 test-view-alias.c  

#include 
#include "sqlite3.h"

void showStmtColNames(sqlite3 *db, const char *szSQL)
{
??? sqlite3_stmt* stmt;
??? printf("SQL: %s\n", szSQL);
??? int rc = sqlite3_prepare_v2(db, szSQL, -1, , 0);
??? int i, col_count = sqlite3_column_count(stmt);
??? for(i=0; i < col_count; ++i)
??? {
??? ??? printf("Column %d: %s\n", i, sqlite3_column_name(stmt, i));
??? }
??? sqlite3_finalize(stmt);
}

int main(int argc, char *argv[])
{
??? sqlite3 *db;
??? const char dbname_szSQL[] = ":memory:";
??? int rc = sqlite3_open(dbname_szSQL, );
??? if(rc == SQLITE_OK)
??? {
??? ??? char *errmsg;
??? ??? rc = sqlite3_exec(db, "CREATE TABLE tbl(id, name);", NULL,
NULL, );
??? ??? rc = sqlite3_exec(db, "CREATE VIEW tbl_view AS SELECT a.id,
a.name FROM tbl AS a;", NULL, NULL, );
??? ??? rc = sqlite3_exec(db, "INSERT INTO tbl(id, name) VALUES(1,
'dad');", NULL, NULL, );
??? ??? 
??? ??? showStmtColNames(db, "SELECT a.* FROM tbl AS a;");
??? ??? showStmtColNames(db, "SELECT a.id, a.name FROM tbl AS a;");
??? ??? showStmtColNames(db, "SELECT a.* FROM tbl_view AS a;");
??? ??? showStmtColNames(db, "SELECT a.id, a.name FROM tbl_view AS
a;"); //here only we get qualified names
??? ??? 
??? ??? sqlite3_close(db);
??? }
??? return 0;
}  

  

script to compile "test-view-alias.c"  

MYINC=.

gcc -g -O2 \
??? -DSQLITE_DEBUG=1 \
??? -DSQLITE_ENABLE_EXPLAIN_COMMENTS=1 \
??? -DTHREADSAFE=1 \
??? -DSQLITE_DEFAULT_FILE_FORMAT=4 \
??? -DSQLITE_DEFAULT_AUTOVACUUM=1 \
??? -DSQLITE_DEFAULT_FOREIGN_KEYS=1 \
??? -DSQLITE_ENABLE_COLUMN_METADATA=1 \
??? -DSQLITE_ENABLE_FTS4=1 \
??? -DSQLITE_ENABLE_FTS3_PARENTHESIS=1 \
??? -DSQLITE_ENABLE_UNLOCK_NOTIFY=1 \
??? -DSQLITE_ENABLE_RTREE=1 \
??? -DSQLITE_ENABLE_STAT4=1 \
??? -DSQLITE_OMIT_TCL_VARIABLE=1 \
??? -DSQLITE_USE_URI=1 \
??? -DSQLITE_SOUNDEX=1\
??? -o test-view-alias test-view-alias.c -I $MYINC $MYINC/sqlite3.c
-lpthread -lm -ldl  

[sqlite] Why sqlite show qualified column names when selecting from views ?

[Simon Slavin]

On 25 Aug 2015, at 6:13pm, sqlite-mail  wrote:

> When querying views sqlite shows qualified column names if they are specified
> individually. 

Sorry but this has been mentioned a few times here and won't be changed.  The 
SQL standard doesn't mention column names so SQL engines are free to do what 
they want.

In SQLite you can depend on column names only if you have specified them using 
'AS'.  So I would expect, but haven't tested right now ...

> SQL: SELECT a.* FROM tbl_view AS a;
> Column 0: id
> Column 1: name
> SQL: SELECT a.id, a.name FROM tbl_view AS a;
> Column 0: a.id   <<<<<<<<<<<<<<<<<<<<< only with individual fields 
> Column 1: a.name <<<<<<<<<<<<<<<<<<<


SQL: SELECT a.id AS id, a.name AS name FROM tbl_view AS a;
Column 0: id
Column 1: name

Simon.

[sqlite] Why sqlite show qualified column names when selecting from views ?

[Richard Hipp]

On 8/25/15, Simon Slavin  wrote:
>
> "If there is no AS clause then the name of the column is unspecified and may
> change from one release of SQLite to the next."
>

Heed this warning!!!

And yet, there are countless tens of thousands of applications in
circulation that have ignored this warning and depend on the
(unsupported) behavior of whatever version of SQLite they were
originally developed on.  So the reality is that we are extremely
careful not to change the result column naming algorithms, for fear of
breaking billions of cellphone apps.

-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] Why sqlite show qualified column names when selecting from views ?

[Scott Robison]

On Aug 25, 2015 1:02 PM, "Petite Abeille"  wrote:
>
>
> > On Aug 25, 2015, at 8:53 PM, R.Smith  wrote:
> >
> > I vote to change it every release... Stimulate better habits!
>
> Seconded. Keep them on their toes!
>

Or randomly generate names after every prepare! Or just leave them
anonymous. #mostlyjoking

[sqlite] Using SQLite as a column-store database?

[Jean Chevalier]

Would it be realistic to pretend to use SQLite as a column-store database by 
leveraging ALTER TABLE ... ADD COLUMN massively and to expect better 
performance?

Suppose that instead of defining a table like CREATE TABLE (key, c1, c2, ... 
cn) I defined it as CREATE TABLE (key) followed by n-times "ALTER TABLE ... ADD 
COLUMN", for each column.

Presumably when I query the table for a /key,column/ pair, SQLite will only 
need to access the storage area for /key/ and the storage area for /column/, 
and will not need to read the content of all the other columns?

[sqlite] Using SQLite as a column-store database?

[Simon Slavin]

On 29 Jul 2015, at 8:28pm, Jean Chevalier  wrote:

> Would it be realistic to pretend to use SQLite as a column-store database by 
> leveraging ALTER TABLE ... ADD COLUMN massively and to expect better 
> performance?

Sorry.  No.  The data for each row is stored together.

> Suppose that instead of defining a table like CREATE TABLE (key, c1, c2, ... 
> cn) I defined it as CREATE TABLE (key) followed by n-times "ALTER TABLE ... 
> ADD COLUMN", for each column.

There is no difference between the results of these two sets of commands.  The 
only difference in what would be stored in the database file would come if you 
inserted some rows before you added a column.  And the space saved would be one 
octet per NULL value -- in other words, too small to worry about.

> Presumably when I query the table for a /key,column/ pair, SQLite will only 
> need to access the storage area for /key/ and the storage area for /column/, 
> and will not need to read the content of all the other columns?

The data for each row is stored together, in order of column definition.  
Columns not stored are assumed to be NULL, which allows ALTER TABLE ... ADD 
COLUMN to work correctly.  SQLite needs to parse the entire row of data, up to 
and including the last column it needs to retrieve to satisfy the command.  So, 
for example,

SELECT c5 FROM myTable ORDER BY c8

Requires SQLite to parse all of c1,c2,c3,c4,c5,c6,c7,c8 for each row to find 
the two values it actually wants.

For more on the SQLite file format see

<https://www.sqlite.org/fileformat.html>

Simon.

Re: [sqlite] Parentheses in column name

[Stephan Beal]

On Mon, Dec 22, 2014 at 9:19 PM, Federico Jurio <
federicoju...@suremptec.com.ar> wrote:

> E:\Pruebas>ogrinfo -sql "select min("(asd*)") MINASD from Geo1" Geo1.shp
>

You're using double quotes inside double quotes, which cannot parse how you
expect it to. You need to change the outer quotes (around the whole select)
to single quotes, then use double quotes around "(asd*)" (as you've done).
Alternately, i believe '[' and ']' can be used instead of quotes around
table/field names.

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Parentheses in column name

[RSmith]

On 2014/12/22 22:19, Federico Jurio wrote:

Hi guys, i'm trying to make a simple query using sqlite through gdal
library ( http://www.gdal.org/ogr_sql.html )

I have a simple table with two columns: ID and (asd*) (both columns have
integer values)

I want the minimun value of this columns

My first attempt was successful

E:\Pruebas>ogrinfo -sql "select min(id) MINID from Geo1" Geo1.shp
INFO: Open of `Geo1.shp'
   using driver `ESRI Shapefile' successful.

Layer name: Geo1
Geometry: None
Feature Count: 1
Layer SRS WKT:
(unknown)
MINID: Integer (10.0)
OGRFeature(Geo1):0
   MINID (Integer) = 2

My second attempt was unsuccessful

E:\Pruebas>ogrinfo -sql "select min((asd*)) MINASD from Geo1" Geo1.shp
INFO: Open of `Geo1.shp'
   using driver `ESRI Shapefile' successful.
ERROR 1: SQL Expression Parsing Error: syntax error, unexpected ')'.
Occured around :
select min((asd*)) MINASD from Geo1


E:\Pruebas>ogrinfo -sql "select min(""(asd*)"") MINASD from Geo1" Geo1.shp

Should fix it.


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Parentheses in column name

[Federico Jurio]

Hi guys, i'm trying to make a simple query using sqlite through gdal
library ( http://www.gdal.org/ogr_sql.html )

I have a simple table with two columns: ID and (asd*) (both columns have
integer values)

I want the minimun value of this columns

My first attempt was successful

E:\Pruebas>ogrinfo -sql "select min(id) MINID from Geo1" Geo1.shp
INFO: Open of `Geo1.shp'
  using driver `ESRI Shapefile' successful.

Layer name: Geo1
Geometry: None
Feature Count: 1
Layer SRS WKT:
(unknown)
MINID: Integer (10.0)
OGRFeature(Geo1):0
  MINID (Integer) = 2

My second attempt was unsuccessful

E:\Pruebas>ogrinfo -sql "select min((asd*)) MINASD from Geo1" Geo1.shp
INFO: Open of `Geo1.shp'
  using driver `ESRI Shapefile' successful.
ERROR 1: SQL Expression Parsing Error: syntax error, unexpected ')'.
Occured around :
select min((asd*)) MINASD from Geo1

E:\Pruebas>ogrinfo -sql "select min("(asd*)") MINASD from Geo1" Geo1.shp
INFO: Open of `Geo1.shp'
  using driver `ESRI Shapefile' successful.
ERROR 1: SQL Expression Parsing Error: syntax error, unexpected ')'.
Occured around :
select min((asd*)) MINASD from Geo1

Can someone help me with the correct expression?
Thanks in advance!

Regards

-- 
Federico Jurio
SUR Emprendimientos Tecnológicos

Perú 345  Piso 5to Oficina "B" (C1067AAG)
Ciudad de Buenos Aires, Argentina
Tel. +54 (11) 4342-2976/84
federicoju...@suremptec.com.ar
www.suremptec.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite JDBC column count

[Manoj]

I do understand its a bad DB design and i should be actually doing what you
have mentioned. But as mentioned in SQLite site it is possible to increase
column limit during compile time. So i just need to know out of curiosity
how it can be done in SQLite jar (jdbc) 3.7.2?



--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/SQLite-JDBC-column-count-tp76505p76551.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite JDBC column count

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 07/07/14 22:51, Manoj wrote:
> Is there any workaround available for this?

https://sqlite.org/limits.html#max_column

Roger

-BEGIN PGP SIGNATURE-
Version: GnuPG v1

iEYEARECAAYFAlO8B14ACgkQmOOfHg372QRr6QCfZfbcFkz/lowVT8uBFy92FY/7
fEAAn1+GgxCIW81Ml848hgbrB+C6bUhw
=LzUJ
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite JDBC column count

[Simon Slavin]

On 8 Jul 2014, at 6:51am, Manoj <manoj.kuma...@lnttechservices.com> wrote:

> Am using SQLite jdbc 3.7.2 and in a scenario i have a table with 2300 column
> which eventually throwed too many columns exception since the maximum
> allowed column count is 2000.
> 
> Is there any workaround available for this? Maybe a query to increase column
> limit in SQLite jdbc?

I cannot conceive of any way to hold the definitions of 2300 columns in your 
head at one time.  Therefore your database is badly designed.

It might be worth remembering that given the design of SQLite, if you need the 
value of column 1900 of a specific row, SQLite has to read 1900 values in order 
to find that value.

Are these columns numbered ?  If so, you have a relational database and should 
be using a related table.

Do the columns come in sets (e.g. 23 sensors each measuring 100 things) ?  If 
so, you still have a relational database and should be using a table (e.g. of 
sensors) a relation.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLite JDBC column count

[Manoj]

Am using SQLite jdbc 3.7.2 and in a scenario i have a table with 2300 column
which eventually throwed too many columns exception since the maximum
allowed column count is 2000.

Is there any workaround available for this? Maybe a query to increase column
limit in SQLite jdbc?

Thanks in advance! 



--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/SQLite-JDBC-column-count-tp76505.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug report: column name includes table alias when CTE is used

[Kevin Benson]

On Mon, Apr 21, 2014 at 8:29 AM, Richard Hipp <d...@sqlite.org> wrote:

> On Mon, Apr 21, 2014 at 1:16 AM, Andre <andreest...@gmail.com> wrote:
>
> > Hi,
> >
> > Apparently when a CTE is used, the column name includes the table alias.
> > However, when no CTE is used, the alias is not present in the returned
> > column name.
> >
> > SQLite version 3.8.4.3 2014-04-03 16:53:12
> > Enter ".help" for usage hints.
> > Connected to a transient in-memory database.
> > Use ".open FILENAME" to reopen on a persistent database.
> > sqlite> create table X (columnA int);
> > sqlite> insert into X values (1);
> > sqlite> .header on
> > sqlite> select alias.columnA from X alias;
> > *columnA*
> > 1
> > sqlite> with CTE as (select columnA from X) select alias.columnA from CTE
> > alias;
> > *alias.columnA*
> > 1
> > sqlite>
> >
> > I experienced this when rewriting a query to use CTE in an application
> that
> > based some logic on the column name. I'd expect not to see the alias
> either
> > way. Is this a bug or is it expected for CTEs?
> >
>
> See
> http://www.sqlite.org/c3ref/mark/column_name.html?If+there+is+n*fiedfor
> further information.
>
>
Fixed this link for myself and future referrers:

http://www.sqlite.org/c3ref/mark/column_name.html?If+there+is+n*fied

--
   --
  --
 --Ô¿Ô--
K e V i N
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug report: column name includes table alias when CTE is used

[Richard Hipp]

On Mon, Apr 21, 2014 at 1:16 AM, Andre <andreest...@gmail.com> wrote:

> Hi,
>
> Apparently when a CTE is used, the column name includes the table alias.
> However, when no CTE is used, the alias is not present in the returned
> column name.
>
> SQLite version 3.8.4.3 2014-04-03 16:53:12
> Enter ".help" for usage hints.
> Connected to a transient in-memory database.
> Use ".open FILENAME" to reopen on a persistent database.
> sqlite> create table X (columnA int);
> sqlite> insert into X values (1);
> sqlite> .header on
> sqlite> select alias.columnA from X alias;
> *columnA*
> 1
> sqlite> with CTE as (select columnA from X) select alias.columnA from CTE
> alias;
> *alias.columnA*
> 1
> sqlite>
>
> I experienced this when rewriting a query to use CTE in an application that
> based some logic on the column name. I'd expect not to see the alias either
> way. Is this a bug or is it expected for CTEs?
>

See http://www.sqlite.org/c3ref/mark/column_name.html?If+there+is+n*fiedfor
further information.

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug report: column name includes table alias when CTE is used

[Simon Slavin]

On 21 Apr 2014, at 6:16am, Andre <andreest...@gmail.com> wrote:

> Apparently when a CTE is used, the column name includes the table alias.
> However, when no CTE is used, the alias is not present in the returned
> column name.

SQLite has no standards at all about column names unless you specifically set a 
column name using "AS" in your SELECT.  If you don't use "AS" then SQLite can 
return all sorts of weird things as the column names, even without using CTE.

If you are expecting a specific column name, you're welcome to tell us what you 
expect and why, but so far nobody has come up with any good reason for a 
specific form.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Bug report: column name includes table alias when CTE is used

[Andre]

Hi,

Apparently when a CTE is used, the column name includes the table alias.
However, when no CTE is used, the alias is not present in the returned
column name.

SQLite version 3.8.4.3 2014-04-03 16:53:12
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table X (columnA int);
sqlite> insert into X values (1);
sqlite> .header on
sqlite> select alias.columnA from X alias;
*columnA*
1
sqlite> with CTE as (select columnA from X) select alias.columnA from CTE
alias;
*alias.columnA*
1
sqlite>

I experienced this when rewriting a query to use CTE in an application that
based some logic on the column name. I'd expect not to see the alias either
way. Is this a bug or is it expected for CTEs?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] RFE: Rename Column

[Peter Haworth]

>
>
>
> You're both right.  Igor's statement pretty-much /is/ the reason one
> cannot rename a column.  One would need to write a parser and changer for
> SQL statements that could identify and change column names in many
> statements with all sorts of weird possibilities for formatting.
>
> Two alternatives: (a) actually write the parser-and-changer that processes
> SQL commands, or (b) wait until the major file format changes in SQLite4,
> then change the way SQL stores the CREATE commands needed to construct a
> database so it stores a structured version of the commands instead of the
> raw text.
>
> The advantage of either change would be that it allows almost all the
> ALTER TABLE commands SQL users expect, not just changing column names.
>  This simplifies life not just for normal users but also for all the
> writers of SQLite GUI managers out there, who have to write nasty risky
> time-consuming hacks if they want to accomplish those operations.
>
> Simon.
>

SQLiteAdmin is one of those "nasty, risky, time consuming hacks" :-)  It
provides a way to rename a column and deals with changing the name in all
the places it could occur in the database, plus just about any db structure
changes that you'd want to do.  Not as good as a built-in solution
admittedly but it's saved my bacon several times.  If interested, check it
out at www.lcsql.com.  Having written the code, I'd agree with the "nasty"
and "time consuming" adjectives, but not so much the "risky" since all the
changes are done within a transaction and I also offer the option of
backing up the database before making any changes.



Pete
lcSQL Software 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] RFE: Rename Column

[Alek Paunov]

On 06.01.2014 00:58, Simon Slavin wrote:


On 5 Jan 2014, at 6:41pm, Petite Abeille  wrote:

On Jan 5, 2014, at 6:56 PM, Igor Tandetnik  wrote:

On 1/4/2014 7:15 PM, Elrond wrote:

Short: Could you implement alter table rename column?


The problem would be, what to do with all the indexes, triggers, views and 
foreign keys that reference that column?


Sure, but that’s an unrelated set of problems, isn’t it? As it stands, one 
cannot even rename a column.


You're both right.  Igor's statement pretty-much /is/ the reason one cannot 
rename a column.  One would need to write a parser and changer for SQL 
statements that could identify and change column names in many statements with 
all sorts of weird possibilities for formatting.

Two alternatives: (a) actually write the parser-and-changer that processes SQL 
commands, or (b) wait until the major file format changes in SQLite4, then 
change the way SQL stores the CREATE commands needed to construct a database so 
it stores a structured version of the commands instead of the raw text.


If someone have a little time to experiment with the (a) road, it is not 
so complicated as it might look at the first glance: For example sqld3 
[1] is a PEG parser which, as author claims, is derived from the 
SQLite's railroad syntax diagrams, which in order are derived (I 
believe) from the SQLite's sources (i.e. there are chances, that the 
grammar is sound).


PEG [2], is the simplest possible kind of grammar machinery, (probably 
easiest for understanding for non computer language experts) - no 
scanner/parser split, no ambiguity, as people often said - something 
like RegExps on steroids :-).


The above project is in Ruby (is there someone who reads Ruby to give 
some test results?) and it is 3 years old, but this is not so important 
- I think Richard and the team are able to point out even more clever 
path for pure grammar extraction in sync with the latest SQLite sources.


Once a Language grammar is available for given PEG implementation it is 
usually easy to translate it for another - because the PEG rules (for 
the syntax rules :-) ) are basically the same everywhere.


There are hundreds of PEG implementations already - at least several per 
language. My personal favorite is one of the smallest, pure C libs - 
LPeg [3], which just like the SQLite itself compiles the grammar to the 
VM code. LPegLJ [4] port of [3] even does not need a C compiler (the 
source code - it is JIT-ed on demand)


Parsing is the first step. I think, it would be funny if the  dogfooding 
principle for the second - transformation step is tried. i.e. when the 
parse trees of SQlite SQL are stored back in (e.g. in memory) SQlite and 
transformed there ;-).


BTW, transformation relaying on data stores are applied in one of the 
modern products in that field - Rascal [5] (IMP PDB).


Another note: This topic seems somewhat related to the recent 
discussions (e.g. CTEs) about SQLite RFEs which are statically 
implementable (by rewriting, without changes to the SQLite engine)


Kind Regards,
Alek

[1] https://github.com/steveyen/sqld3
[2] http://en.wikipedia.org/wiki/Parsing_expression_grammar
[3] http://www.inf.puc-rio.br/~roberto/lpeg/
[4] https://github.com/sacek/LPegLJ
[5] http://www.rascal-mpl.org/

P.S. @Simon, and others closely following: Please point me to the docs 
for the new style (structured) SQL objects representation in the SQlite4 
- only tables? or scripts too?


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] RFE: Rename Column

[Elrond]

Hi,

On Sun, Jan 05, 2014 at 22:58:31 +, Simon Slavin wrote:
[...]
> You're both right.  Igor's statement pretty-much /is/ the
> reason one cannot rename a column.  One would need to
> write a parser and changer for SQL statements that could
> identify and change column names in many statements with
> all sorts of weird possibilities for formatting.

Isn't most of the parser already existant, because sqlite
must parse the in-db-schema-sql to know the structure of
the database?
And there already must be code to identify column names in
the parsed structure, for exactly the above named reasons
(checking constraints, checking foreign keys)?

Obviously, the parser is not a parser-and-changer. Agreed.

What about this simple solution:

Let "alter table rename column" parse the schema, search
for the column name. If it's in no constraint, etc, then do
the rename in the sqlite_master table.  If the column is
used anywhere, error out (which is not worse than before).
This will at least stop people from creating databases,
that can't be opened after mangling around in the
sqlite_master table.

As originally stated, my aim is not a full blown alter
table implementation right away, but only renaming
(possibly unreferenced) columns.


> Two alternatives: (a) actually write the
> parser-and-changer that processes SQL commands, or

For creating the actual changer, one might use the already
parsed in-memory-structure, change it, and dump it back out
as sql.  That might break formatting for people who love
it, but that's still lots better than doing nothing.  Being
able to dump the internal structures as sql might also be
helpful for debugging and writing test cases.


> (b) wait until the major file format changes in SQLite4,
> then change the way SQL stores the CREATE commands needed
> to construct a database so it stores a structured version
> of the commands instead of the raw text.

The "structured version" could be the dumped-sql from above?

But of course, sqlite4 could use some fancy structured
format for that as well. But why invent a new (binary)
format, when we have a text based format at hand, that can
do everything needed?


> The advantage of either change would be that it allows
> almost all the ALTER TABLE commands SQL users expect, not
> just changing column names.

If that's true, that would be great!


> This simplifies life not just for normal users but also
> for all the writers of SQLite GUI managers out there,

Simplifying the world for everyone would be awesome.

Like the foreignkeys are awesome.  Instead of everybody
having to create (possibly wrong) triggers, those triggers
are now builtin.


> who have to write nasty risky time-consuming hacks if
> they want to accomplish those operations.

I am especially afraid of the risky part. That's not the
reliable attribute sqlite is known for.
That's why my suggestion from above is to start with simple
operations, and if sqlite can't do them reliably (because
it would break something), then error out.
That would even improve the gui managers: They could try
the sqlite internal variant (reliable), then ask the user
"Okay, the reliable method did not work, I have some
fallback methods, which are not as reliable, but used to
work most of the time.  Should I apply those?"


> Simon.

Cheers

  Elrond
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] RFE: Rename Column

[Simon Slavin]

On 5 Jan 2014, at 6:41pm, Petite Abeille  wrote:

> On Jan 5, 2014, at 6:56 PM, Igor Tandetnik  wrote:
> 
>> On 1/4/2014 7:15 PM, Elrond wrote:
>>> Short: Could you implement alter table rename column?
>> 
>> The problem would be, what to do with all the indexes, triggers, views and 
>> foreign keys that reference that column?
> 
> Sure, but that’s an unrelated set of problems, isn’t it? As it stands, one 
> cannot even rename a column.

You're both right.  Igor's statement pretty-much /is/ the reason one cannot 
rename a column.  One would need to write a parser and changer for SQL 
statements that could identify and change column names in many statements with 
all sorts of weird possibilities for formatting.

Two alternatives: (a) actually write the parser-and-changer that processes SQL 
commands, or (b) wait until the major file format changes in SQLite4, then 
change the way SQL stores the CREATE commands needed to construct a database so 
it stores a structured version of the commands instead of the raw text.

The advantage of either change would be that it allows almost all the ALTER 
TABLE commands SQL users expect, not just changing column names.  This 
simplifies life not just for normal users but also for all the writers of 
SQLite GUI managers out there, who have to write nasty risky time-consuming 
hacks if they want to accomplish those operations.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] RFE: Rename Column

[Igor Tandetnik]

On 1/5/2014 1:41 PM, Petite Abeille wrote:

On Jan 5, 2014, at 6:56 PM, Igor Tandetnik <i...@tandetnik.org> wrote:


On 1/4/2014 7:15 PM, Elrond wrote:

Short: Could you implement alter table rename column?


The problem would be, what to do with all the indexes, triggers, views and 
foreign keys that reference that column?


Sure, but that’s an unrelated set of problems, isn’t it? As it stands, one 
cannot even rename a column.


In SQLite, if you update just the CREATE TABLE statement but no other 
parts of the schema, you may easily end up with a database that can't be 
opened at all.



(P.S. Other DBs tend to simply invalidate all the related objects and let the 
user sort it out)


If you are willing to let the user sort it out, then the existing 
mechanism of directly manipulating sqlite_master should suffice.

--
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] RFE: Rename Column

[Petite Abeille]

On Jan 5, 2014, at 6:56 PM, Igor Tandetnik  wrote:

> On 1/4/2014 7:15 PM, Elrond wrote:
>> Short: Could you implement alter table rename column?
> 
> The problem would be, what to do with all the indexes, triggers, views and 
> foreign keys that reference that column?

Sure, but that’s an unrelated set of problems, isn’t it? As it stands, one 
cannot even rename a column.

(P.S. Other DBs tend to simply invalidate all the related objects and let the 
user sort it out)

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] RFE: Rename Column

[Igor Tandetnik]

On 1/4/2014 7:15 PM, Elrond wrote:

Short: Could you implement alter table rename column?


The problem would be, what to do with all the indexes, triggers, views 
and foreign keys that reference that column? SQLite stores database 
schema as text, in the form of CREATE... statements. I suspect it would 
require heroic efforts to go over all those statements, find all 
occurrences of X (where X is the old name of the column), and filter out 
false positives (cases where X names something else, say a column in a 
different table).

--
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] RFE: Rename Column

[Elrond]

Hi everybody,

Short: Could you implement alter table rename column?

I know that sqlite does only support a limited subset of
SQL. Some subsets have only been added lately, like foreign
keys, and can be disabled at compile time to safe space.

Renaming a table is already supported, which is good.

I hope, renaming a column should not be extremely complex.
There are even dangerous recipes out there [1] that
manipulate the internal structure (sqlite_master) using the
public API. So could those recipes be turned into
(hopefully safer) internal code?


Cheers

  Elrond

[1] http://stackoverflow.com/a/6684034/1890086
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite accepts invalid column names

[Richard Hipp]

On Mon, Jun 24, 2013 at 8:32 AM, Rob Golsteijn wrote:

>
> I found the issue in SQLite version 3.7.15. Great if it is solved in the
> latest version.
>

Fixed for 3.7.16 by check-in http://www.sqlite.org/src/info/99127a669c

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite accepts invalid column names

[Rob Golsteijn]

> On Thu, Jun 20, 2013 at 10:26 AM, Rob Golsteijn
> wrote:
> 
> >
> > SELECT a.col2,
> >b.col2,
> >a.b.col2,-- invalid column name
> >b.a.col2,-- invalid column name
> >a.a.col2,-- invalid column name
> >b.b.col2,-- invalid column name
> >anything.a.col2  -- invalid column name
> > FROM aaa a,
> >  bbb b ON a.col1 = b.col1;
> >
> 
> I get an error:  "no such column: a.b.col2".  What version of SQLite did
> you say you were running?
> 
> -- 
> D. Richard Hipp
> d...@sqlite.org

I found the issue in SQLite version 3.7.15. Great if it is solved in the latest 
version.

Thanks,
Rob




 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users