SQLite Forum

Timeline
Login

50 most recent forum posts by user luuk

2021-04-25
10:52 Reply: bug report : adding constant to GROUP BY leads to different output (artifact: 95fa85ba43 user: luuk)

I wonder whether it's a bug..... 😉

09:57 Edit reply: bug report : adding constant to GROUP BY leads to different output (artifact: 29e9a2ddd6 user: luuk)

But when we add a constant to the GROUP BY clause, just like:

SELECT * FROM t0 GROUP BY c0, NULL;

NULL is not a constant, because this does not return TRUE (or 1):

SELECT null=null

But this (adding a constant):

SELECT * FROM t0 GROUP BY c0, '42';

gives the correct output.

09:54 Reply: bug report : adding constant to GROUP BY leads to different output (artifact: a4f0abaf53 user: luuk)

But when we add a constant to the GROUP BY clause, just like:

SELECT * FROM t0 GROUP BY c0, NULL;

NULL is not a constant, because this does not return TRUE (or 1):

SELECT null=null

But this:

SELECT * FROM t0 GROUP BY c0, '42';

gives the correct output.

2021-04-20
16:59 Reply: Administrator SQLite (artifact: 0d3185ad32 user: luuk)

If you create a batchfile (i.e. sqlite.bat) like this:

@echo off

set t=""
whoami /groups | findstr "S-1-16-12288"
if errorlevel 1 goto :next
if errorlevel 0 set t=".system title SQLite"
:next
sqlite3 -cmd %t%

Then running as a 'normal' user will not change,

But as an elevated user, you will see the change in title.

The whoami... line was found here: check-for-elevation-at-command-prompt

2021-03-02
18:14 Reply: Deferred index update? (artifact: 5aba344644 user: luuk)

why are you not doing ON CONFLIT DO IGNORE ?

2020-12-27
08:11 Reply: Creating an emty database (artifact: ea70d6bb74 user: luuk)

.tables will create a zero-length file:

D:\TEMP>sqlite3 foo.sqlite
-- Loading resources from C:\Users\Luuk/.sqliterc
SQLite version 3.34.0 2020-12-01 16:14:00
Enter ".help" for usage hints.
sqlite> .tables
sqlite> .quit

D:\TEMP>dir foo.sqlite
 Volume in drive D is HDD
 Volume Serial Number is D46B-804B

 Directory of D:\TEMP

27-12-2020  09:10                 0 foo.sqlite
               1 File(s)              0 bytes
               0 Dir(s)  672.552.960.000 bytes free

D:\TEMP>
2020-12-04
07:28 Reply: generate_series (artifact: 2a4f2d12ee user: luuk)

OK,

I should have explained, in more detail, that I was not concerned about the order by.

It was more a suggestion if START is higher than END, and STEP is NEGATIVE, i would like it when results are returned.

Thanks for the 'fix', and I hope to see them implemented 😉

2020-12-03
07:10 Edit: generate_series (artifact: c0c08232bf user: luuk)

It does not seems to work with negative step:

sqlite> select * from generate_series(1,3,1);
value
-----
1
2
3
sqlite> select * from generate_series(3,1,-1);
sqlite>

OK, i know i can do it with a recursive with:

sqlite> with recursive s(v) as (select 3 union all select v-1 from s where v-1>=1) select * from s;
v
-
3
2
1
sqlite>

It would be nice if a negative step is supported too, or if the docs state that only positive step values should be used.

07:09 Post: generate_series (artifact: 2c57af7a96 user: luuk)

It does not seems to work with negative step:

sqlite> select * from generate_series(1,3,1);
value
-----
1
2
3
sqlite> select * from generate_series(3,1,-1);
sqlite>

OK, i know i can do it with a recursive with:

sqlite> with recursive s(v) as (select 3 union all select v-1 from s where v-1>=1) select * from s;
v
-
3
2
1
sqlite>

It would be nice if a negative step is supported too, or if the docs state that only positive step values should be used.

2020-11-18
08:03 Reply: Serious Sqlite command line Problems after Windows 10 update (artifact: 5891a7a87a user: luuk)

When you do a rightclick on sqlite3.exe, on the tab 'Compatibility' the checkbox 'Run this program in compatibility mode for' should NOT be checked.

If this is check, there is a possibility a new window is opened when running sqlite3

2020-11-17
19:40 Reply: The 'anonymous' user (artifact: 9f91d7b086 user: luuk)

Ok, sorry, i forgot (and did not read) the discussion half a year ago.

I am nog against people posting anonymously, i myself post just using my firstname, but sometimes it's hard to follow when anonymous does post a reaction to him/herself (also anonymous).

07:30 Post: The 'anonymous' user (artifact: efae508d31 user: luuk)

Is the fact that too much anonymous users post on the forum a problem that is looked at?

Or should we deal with the fact that one anonymous user is replying to another anonymous user? 😥

07:27 Reply: Serious Sqlite command line Problems after Windows 10 update (artifact: 956c065d11 user: luuk)

Can you share a link where rstui.exe can be downloaded for Windows 10?

All google result are from 2013 (or before), from people about missing rstui.exe

2020-11-06
18:19 Reply: no such table: generate_series (artifact: 31553f1fa2 user: luuk)

Thanks, for the info.

I will wait for the update to 3.34, and until then do a recursive with.

and, yes, it was before coffee this morning, so i did have the parameters in the wrong order. 😉

07:25 Post: no such table: generate_series (artifact: b459363efe user: luuk)
D:\TEMP>sqlite3
SQLite version 3.33.0 2020-08-14 13:23:32
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> select * from generate_series(1,1,10);
Error: no such table: generate_series
sqlite>

"https://sqlite.org/series.html": The generate_series(START,END,STEP) table-valued function is a loadable extension included in the SQLite source tree, and compiled into the command-line shell.

I have (when looking at the download page) the current version 3.33

What am i missing?

2020-11-02
17:25 Reply: CLI on Windows: file name encoding problem (artifact: d36c4e68c3 user: luuk)

https://docs.microsoft.com/en-us/windows/win32/fileio/naming-a-file

"Use any character in the current code page for a name, including Unicode characters and characters in the extended character set (128–255)..."

They are making things things hard for themselves at microsoft...

I do think this is more a Windows bug then a bug in SQLITE?

2020-10-10
10:40 Reply: How can I fill a NULL with previous Data ? (artifact: acc31d4752 user: luuk)

But that would, as winter is coming, make it hard (or impossible) to see then difference between 0 degrees and a 'not registered temerature'.

2020-10-08
16:10 Reply: ATTACH DATABASE and DETACH DATABASE (artifact: 36bc4e7c71 user: luuk)

Microsoft SQL also has this, see: https://docs.microsoft.com/en-us/sql/relational-databases/databases/attach-a-database?view=sql-server-ver15

For SQLite see: https://www.sqlite.org/lang_attach.html

2020-09-15
06:20 Reply: select * from t; -- but don't want virtual columns (artifact: 5fe4d578c7 user: luuk)

But "The order of the concatenated elements is arbitrary." in the paragraph under "group_concat(X),group_concat(X,Y)" on https://www.sqlite.org/lang_aggfunc.html

That will hurt harder than the 'problem' which is discussed here.....

2020-08-30
08:00 Reply: HEX - What is the reverse function? (artifact: b0983a135e user: luuk)

https://stackoverflow.com/questions/15366594/convert-hex-to-text-using-sqlite

claims that is can be done using:

select cast(data as varchar) from some_table

but, that does not seem to work.

https://html.developreference.com/article/23338803/Convert+hex+to+text+using+SQLite

shows a piece of SQL code which works:

WITH RECURSIVE test(c,cur) as (
select '','686F77647921'
UNION ALL
select c || char((case substr(cur,1,1) when 'A' then 10 when 'B' then 11 when 'C' then 12 when 'D' then 13 when 'E' then 14 when 'F' then 15 else substr(cur,1,1) end)*16
+ (case substr(cur,2,1) when 'A' then 10 when 'B' then 11 when 'C' then 12 when 'D' then 13 when 'E' then 14 when 'F' then 15 else substr(cur,2,1) end)),
substr(cur,3)
from test where length(cur)>0
)
select * from test
2020-08-09
07:40 Reply: Potential bug? = comparison on columns defined with no type (artifact: e3ed929e7c user: luuk)

The no_column_types also has:

sqlite> insert into no_column_types (id, name, country_id) values (3, 'Richard', '1');
sqlite> select * from no_column_types order by country_id;
id          name        country_id
----------  ----------  ----------
1           Bob         44
2           Karl        44
3           Richard     1

and, of course a simple solution:

sqlite> select * from no_column_types order by 0+country_id;
id          name        country_id
----------  ----------  ----------
3           Richard     1
1           Bob         44
2           Karl        44
2020-07-26
08:16 Reply: Odd, query taking too long (artifact: 962cdae865 user: luuk)

Show all indexes you have, and the EXPLAIN QUERY PLAN.

But it is probably just a missing index on: "name, checkDate" or "checkDate,name" on the table Traffic.

2020-07-25
07:59 Reply: Issue importing csv file to db (artifact: dc5af38258 user: luuk)
nl padron_reducido_ruc.txt  | grep '"' | awk -F'"' 'NF%2!=1{ print $0 }' 

shows indeed some (483) lines which have an odd number of " characters.

I do not think import this file should take into account doing anything special with these (") characters.

I also (that's why i have the nl) do not see anything special about line 13718893

Because the OP (AxioUK) shared a file which he is not actually using, this will be a difficult problem, because we can only 'guess' that he is doing the conversion, from | delimited to , delimited, and from ISO-8859 to UTF-8, the correct way.

2020-07-20
13:36 Reply: Possible bug in .read when a directory is specified? (artifact: a1a20b22e4 user: luuk)

Indeed, and error message seems to be missing on Linux.

2020-07-18
07:52 Reply: Possible bug in .read when a directory is specified? (artifact: 964ec34184 user: luuk)

On both tests there is a directory 'test' and a file 'test.sql'.

Windows:

D:\TEMP>sqlite3 test.db
SQLite version 3.32.3 2020-06-18 14:00:33
Enter ".help" for usage hints.
sqlite> .read test
Error: cannot open "test"
sqlite> .q

Linux (WSL):

/mnt/d/TEMP$ sqlite3 test.db
SQLite version 3.31.1 2020-01-27 19:55:54
Enter ".help" for usage hints.
sqlite> .read test
sqlite> .q

It could be the difference in versions, but it's different.

2020-06-27
15:35 Edit reply: Problem with Sqlite DELETE documentation (artifact: 0630cd5987 user: luuk)

The ORDER BY needs to be given when you want correct records to be deleted.

Example:

CREATE TABLE test(i INTEGER PRIMARY KEY);

INSERT INTO test VALUES (1),(2),(3),(4),(5);

To delete all records:

DELETE FROM test;

But this produces an error (which is OK):

DELETE FROM test ORDER BY i;

But suppose we want to delete two records?

The only way to know WHICH records are deleted is to specify a WHERE clause like:

DELETE FROM TEST WHERE i IN(1,2);

Or, to specify an 'ORDER BY' and a 'LIMIT'

To delete the first two records:

DELETE FROM TEST ORDER BY i ASC LIMIT 2;

To delete the last two records:

DELETE FROM TEST ORDER BY i DESC LIMIT 2;

But these last two lines produce this ERROR: Error: near "ORDER": syntax error (Windows 10, SQLite 3.32.3 )

But the reason for this error is explained in the docs: If SQLite is compiled with the SQLITE_ENABLE_UPDATE_DELETE_LIMIT compile-time option, then the syntax of the DELETE statement is extended by the addition of optional ORDER BY and LIMIT clauses (https://www.sqlite.org/lang_delete.html)

15:34 Reply: Problem with Sqlite DELETE documentation (artifact: d436f8ab6e user: luuk)

The ORDER BY needs to be given when you want correct records to be deleted.

Example: CREATE TABLE test(i INTEGER PRIMARY KEY); INSERT INTO test VALUES (1),(2),(3),(4),(5);

To delete all records: DELETE FROM test;

But this produces an error (which is OK): DELETE FROM test ORDER BY i;

But suppose we want to delete two records?

The only way to know WHICH records are deleted is to specify a WHERE clause like: DELETE FROM TEST WHERE i IN(1,2);

Or, to specify an 'ORDER BY' and a 'LIMIT'

To delete the first two records: DELETE FROM TEST ORDER BY i ASC LIMIT 2;

To delete the last two records: DELETE FROM TEST ORDER BY i DESC LIMIT 2;

But these last two lines produce this ERROR: Error: near "ORDER": syntax error (Windows 10, SQLite 3.32.3 )

But the reason for this error is explained in the docs: If SQLite is compiled with the SQLITE_ENABLE_UPDATE_DELETE_LIMIT compile-time option, then the syntax of the DELETE statement is extended by the addition of optional ORDER BY and LIMIT clauses (https://www.sqlite.org/lang_delete.html)

10:12 Reply: Query planner fails to use obvious index (artifact: 1fe7488810 user: luuk)

explain query plan

select min(id) from test where key_id >= 1

returns "SEARCH TABLE test", which does not use the index (and is very slow in my real world case), while the following "workaround" query

Because 99.9% of the table has to be read, to know the minimal value of id, the choice is made to read the complete table.

Nothing wrong with that.

2020-06-20
07:07 Reply: Decimal128 (artifact: 1e8decae4a user: luuk)

"The third argument is a non-negative integer which is the number of digits of precision after the decimal-point to preserve. If the third argument is omitted, the value used is the larger of the two input numbers."

On decimal-mul() this should be the addition of (number of decimal places of) the two input numbers.

  • 0.3 * 0.66 = something with (1+2=) 3 decimals.
  • 0.99 * 0.99 = something with (2+2=) 4 decimals.
2020-06-14
13:07 Reply: storing number with 2 decimal place (artifact: ff9c96c2c3 user: luuk)

+1 for adding the markup for '<code>' to the available Markup commands.

08:58 Reply: storing number with 2 decimal place (artifact: 0a42ffa693 user: luuk)

What is wrong with storing 0.0 ? One can also get the two decimals when doing the query:

insert into t values(5.0/3);
sqlite> select * from t;
0.00
1.66666666666667
sqlite> select printf('%.2f',s) from t;
0.00
1.67
sqlite>
2020-06-05
12:40 Reply: index question (artifact: ca24b2946a user: luuk)

found the answer:

https://stackoverflow.com/questions/19357490/does-an-unique-constraint-remove-the-need-for-an-explicit-index-in-sqlite

which refers to: https://sqlite.org/lang_createtable.html#uniqueconst

which explains...

12:37 Reply: index question (artifact: 30aa68b339 user: luuk)

"You've already declared name as unique... so there's already an index on it to satisfy the unique constraint"

Is this true? The index is not shown when doing this:

.schema dance

2020-05-30
15:14 Reply: SQLite turns 20 (artifact: 36bca6f1f1 user: luuk)

Can you update the docs with that?

😁

2020-05-29
18:01 Reply: SQLite turns 20 (artifact: 7102d8b7e3 user: luuk)

It's the long productive searches that i care for more....

17:28 Reply: SQLite turns 20 (artifact: 87d58807bb user: luuk)

Could you expand that with some results? Or did you not get any?

;)

2020-05-24
15:43 Edit reply: Bug when converting string to boolean? (artifact: 2d4a3cd308 user: luuk)
But, seriously, ho much work is it to do this:


SQLite version 3.31.1 2020-01-27 19:55:54
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> select 42+'xxxx1';
42
sqlite> select 42+'1enghlissh';
43
sqlite>

and, draw some conclusion from the responses that SQLite gave you?
15:43 Edit reply: Bug when converting string to boolean? (artifact: d676749aac user: luuk)

But, seriously, ho much work is it to do this:

``` SQLite version 3.31.1 2020-01-27 19:55:54 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> select 42+'xxxx1'; 42 sqlite> select 42+'1enghlissh'; 43 sqlite>

and, draw some conclusion from the responses that SQLite gave you?

15:41 Reply: Bug when converting string to boolean? (artifact: 4d764c91b1 user: luuk)

But, seriously, ho much work is it to do this:

SQLite version 3.31.1 2020-01-27 19:55:54 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> select 42+'xxxx1'; 42 sqlite> select 42+'1enghlissh'; 43 sqlite>

and, draw some conclusion from the responses that SQLite gave you?

2020-05-21
10:58 Reply: Querying first of each month (artifact: aa070883e8 user: luuk)

As Stephan said: more info is needed.

Because (technically): SELECT * FROM .... is also a good answer to your question. (It just gives more data than you want)

You did not even give the tablename, we cannot guess...

2020-05-20
18:46 Edit reply: SQLDIFF not showing true differences (artifact: 7e75be7fec user: luuk)

sqlite3 a.db

sqlite>attach 'b.db' as b;

sqlite>select t.s t1, (x.s collate binary) t2 from t inner join b.t x on t.s=x.s where t1<>t2;

t1 t2


sample SAMPLE sqlite>

18:46 Reply: SQLDIFF not showing true differences (artifact: 46ca81f01c user: luuk)

sqlite3 a.db sqlite>attach 'b.db' as b; sqlite>select t.s t1, (x.s collate binary) t2 from t inner join b.t x on t.s=x.s where t1<>t2; t1 t2


sample SAMPLE sqlite>

2020-05-13
06:59 Reply: Forum suggestion (artifact: baae3abe22 user: luuk)

see: https://sqlite.org/forum

2020-05-04
06:19 Reply: Week numbers in queries (artifact: 4fcac408d9 user: luuk)

In the docs it says:

" Universal Coordinated Time (UTC) is used."

(https://www.sqlite.org/lang_datefunc.html )

Implementing '%z' will be useless if only UTC is used.

2020-05-03
08:28 Reply: Week numbers in queries (artifact: 868e3870d9 user: luuk)

Is there a reason why '%V' is not recognized by SQLite ?

%V The ISO 8601 week number (see NOTES) of the current year as a decimal number, range 01 to 53, where week 1 is the first week that has at least 4 days in the new year. See also %U and %W. (Calculated from tm_year, tm_yday, and tm_wday.) (SU)

%w The day of the week as a decimal, range 0 to 6, Sunday being 0. See also %u. (Calculated from tm_wday.)

%W The week number of the current year as a decimal number, range 00 to 53, starting with the first Monday as the first day of week 01. (Calculated from tm_yday and tm_wday.)

2020-04-19
09:11 Reply: Malwarebytes slows SQLite (artifact: a95515f234 user: luuk)

The number of hits, when googling for: "malwarebytes slowing down windows 10" say enough...

some links:

  • https://antivirussupportnumber.org/fix-malwarebytes-slowing-down-windows-10/

  • https://forums.malwarebytes.com/topic/228395-malwarebytes-services-causing-slow-pc/

The best solution, which should only be executed for the first half:

  • remove malwarebytes, reinstall malwarebytes

😁

2020-04-16
06:10 Reply: can not subscribe (artifact: 28abadd7a0 user: luuk)

https://docs.microsoft.com/en-us/microsoft-365/security/office-365-security/use-the-delist-portal-to-remove-yourself-from-the-office-365-blocked-senders-lis?view=o365-worldwide

it has a link to:

https://sender.office.com/ "Office 365 Anti-Spam IP Delist Portal"

It's a 3-step process (send verification / confirm email address / Delist IP )

2020-04-06
19:14 Reply: 3.31.1: docs as PDF? (artifact: 3f55403082 user: luuk)

I will (also) take a look at this.

My own try to produce some PDF has stopped on creating an index (read: working index).

Maybe, when i have some time left coming weekend, but who knows....

Thanks for this script!

Luuk

2020-04-03
16:02 Reply: Opening a database without creating a new one and Error logging (artifact: 8824fc13a6 user: luuk)

https://www.php.net/manual/en/sqlite3.open.php

public SQLite3::open ( string $filename [, int $flags = SQLITE3_OPEN_READWRITE SQLITE3_OPEN_CREATE [, string $encryption_key = "" ]] ) : void

flags Optional flags used to determine how to open the SQLite database. By default, open uses SQLITE3_OPEN_READWRITE | SQLITE3_OPEN_CREATE.

SQLITE3_OPEN_READONLY: Open the database for reading only.

SQLITE3_OPEN_READWRITE: Open the database for reading and writing.

SQLITE3_OPEN_CREATE: Create the database if it does not exist.

But, indeed, this is not a PHP forum.... ;)

2020-03-29
07:40 Reply: Retrieving FIRST occurrence of a string in a column (SQLITE PHP) (artifact: 833117c051 user: luuk)
CREATE TABLE MyTable (progressive_no int, ColumnX int);

INSERT INTO MyTable(progressive_no, ColumnX) VALUES
    (1,1),
    (2,2),
    (3,3),
    (4,4),
    (5,2),
    (6,2),
    (7,5),
    (8,2);

SELECT 
  t1.ColumnX  ,
  t1.progressive_no,
  MAX(t2.progressive_no) as Previous,
  MIN(t3.progressive_no) as Next,
  MIN(t4.progressive_no) as First,
  MAX(t5.progressive_no) as Last
FROM MyTable t1
LEFT JOIN (
	SELECT progressive_no, ColumnX
	FROM MyTable t2
	ORDER BY t2.progressive_no DESC
	
	) t2 ON t2.ColumnX=t1.ColumnX and t2.progressive_no <t1.progressive_no 
LEFT JOIN (
	SELECT progressive_no, ColumnX
	FROM MyTable t3
	ORDER BY t3.progressive_no ASC
	
	) t3 ON t3.ColumnX=t1.ColumnX and t3.progressive_no >t1.progressive_no 
LEFT JOIN (
	SELECT progressive_no, ColumnX
	FROM MyTable t4
	ORDER BY t4.progressive_no ASC
	
	) t4 ON t4.ColumnX=t1.ColumnX 
LEFT JOIN (
	SELECT progressive_no, ColumnX
	FROM MyTable t5
	ORDER BY t5.progressive_no DESC
	
	) t5 ON t5.ColumnX=t1.ColumnX 
WHERE t1.progressive_no=5
GROUP BY t1.progressive_no,t1.Columnx;

output:
ColumnX     progressive_no  Previous    Next        First       Last
----------  --------------  ----------  ----------  ----------  ----------
2           5               2           6           2           8
More ↓