SQLite Forum

Timeline
Login

40 forum posts by user jdennis

2021-11-27
07:51 Reply: Существует ли справочник синтетических ошибок?Где искать исправления error near? (artifact: 0fe77ab5a5 user: jdennis)

The google "detect language" translation suggests "Is there a manual for synthetic errors? Where to look for fixes". Convert synthetic to syntax and it makes sense. But seeing the query which was being executed would be useful.

2021-11-05
06:58 Reply: Question about memory management (artifact: 31315b1882 user: jdennis)

The code itself is simple. I read in a csv record, assign the fields to the fields in the SQLite database and then add them to the database. Loop until done.

"add them ... loop until done" suggests a series of inserts, rather than an import. Are these being done in a single transaction? Or each being done in its own transaction?

2021-09-21
09:36 Reply: get only first record found (artifact: e6e0c8f47d user: jdennis)
In a simple test I loaded a million rows into a table into an unindexed column:

create table one_million (counter text);
insert into one_million
WITH RECURSIVE counter(value) AS (
   SELECT 1
     UNION ALL
   SELECT value + 1 FROM counter LIMIT 1000000
) select * from counter;

Then with .timer on I executed these selects:

select * from one_million where counter=1 limit 1;
Run Time: real 0.000 user 0.000000 sys 0.000000

select * from one_million where counter=999999 limit 1;
Run Time: real 0.108 user 0.109375 sys 0.000000

This suggests that the select is indeed stopping after finding the first matching row. Zero time to select the first row, 0.1 secs to get to the last row
2021-09-09
09:01 Reply: How to insert duplicate rows? (artifact: 713ecc29bd user: jdennis)
Thanks. This line is missing the backslash at the end

"ref_cur REAL, "

Whatever affect that might have. Obviously the table gets created, so this is not the problem with the inability to insert duplicate rows.
06:35 Reply: How to insert duplicate rows? (artifact: ae054ac0a4 user: jdennis)
Why all the double quotes? They seem to be in very strange places. 

Duplicate rows certainly work:

create table dups (a text,b int);
insert into dups values ('aaa',1);
insert into dups values ('aaa',1);
insert into dups values ('aaa',1);
select * from dups;
aaa|1
aaa|1
aaa|1
2021-08-23
02:03 Edit reply: How to select columns that have name beginning with same prefix? (artifact: ba745286c9 user: jdennis)
You can do this in the CLI using .once and .read

.once /tmp/aaa.sql
select 'select ' || group_concat(name) || ' from mytable' from pragma_table_info('mytable') where substr(name,1,7)='PREFIX_';
.read /tmp/aaa.sql
02:03 Reply: How to select columns that have name beginning with same prefix? (artifact: 2b4378a6bf user: jdennis)

You can do this in the CLI using .once and .read

.once /tmp/aaa.sql select 'select ' || group_concat(name) || ' from mytable' from pragma_table_info('mytable') where substr(name,1,7)='PREFIX_'; .read /tmp/aaa.sql

2021-08-16
04:28 Reply: Numeric vs integer (artifact: 0a37de2b6a user: jdennis)

I find however that I frequently seem to need to cast these columns as integer in order for maths to work correctly.

There is no difference between a NUMERIC value which is an integer and an INTEGER value which is an INTEGER -- they are both INTEGER and behave exactly the same.

Checking through my SQL scripts I find virtually all of the CAST functions are for a single column, which for historical reasons contains an integer value or an empty string. This database comes from a Dataflex application of the 1980s, which because I don't want to rewrite in something more modern is still in use today. The SQLite database is used for our web site, and ad-hoc reporting, and is refreshed after the Dataflex DB is updated.

The issue is with comparisons like this:

select ... where field>=50

where the condition evaluates to true when the field is empty. It should, of course, be null but I am not certain what affect that change might have in other places.

Yes, I am a dinosaur. Both a Dataflex and Ingres RDBMS user.

2021-08-15
06:32 Reply: Numeric vs integer (artifact: 9c087c216a user: jdennis)

Thank you. I soon as I opened the link I realised I had read the document before, but I think my brain got fixated on the 8-byte integer that is used internally when processing integers.

Q2 was more of a "for interest" question - I have absolutely no concerns about the performance of SQLite.

I shall rebuild my database with all of the i2 columns defined as integer.

05:34 Post: Numeric vs integer (artifact: 07a8e7ef77 user: jdennis)

I imported a database from another RDBMS, which has many columns defined as "i2". SQLite3 doesn't recognise this as "int2" so the column was created with affinity of Numeric and type as NUM. The loaded data is all integers, so a typeof() returns integer.

I find however that I frequently seem to need to cast these columns as integer in order for maths to work correctly.

Question 1: How much space is taken up in the physical database by a NUM/Numeric field which is storing the integer value 3? If converted to Integer it will be 8 bytes - if the current definition is also 8 bytes then the space requirements will not change.

Question 2: Am I correct to assume that a value defined as integer will be more efficient in artihmetic functions (eg, sum(..)) than having the integer value stored in a Numeric field? Although I would expect it to be very marginal...

2021-08-14
12:11 Reply: AVG funcion problem (artifact: 3d0ba36c8d user: jdennis)

Is the AVG(op.quantity) result correct. Your query does a GROUP BY product_id.

I think you need a very similar query with AVG(op.quantity) and a GROUP BY category_id, so that the average will be calcualted by category.

2021-07-30
08:58 Reply: Round function returning -0.0 (artifact: f19825c660 user: jdennis)
.version
SQLite 3.35.4 2021-04-02 15:20:15 5d4c65779dab868b285519b19e4cf9d451d50c6048f06f653aa701ec212df45e
zlib version 1.2.11
gcc-5.2.0
07:07 Reply: Round function returning -0.0 (artifact: fc46f950a0 user: jdennis)
Running SQLite 3.35.4 on Windows 10:

select round(0.0), round(0.0, 1), round(-0.0), round(-0.0, 1);
0.0|0.0|0.0|0.0
2021-07-21
10:15 Reply: SELECT optimization for constant expression (artifact: 73dc36c494 user: jdennis)

Thanks. Showing my background in another database, where LIKE is case sensitive by default. Sorry for the red herring.

08:40 Edit reply: SELECT optimization for constant expression (artifact: d8e3ab4601 user: jdennis)
I have a similar table, named df_players.  Even ignoring all the parameters and the IS NULL, a very simple single column query demonstrates that the LIKE causes the table scan:

explain query plan select * from df_players where surname LIKE 'Dennis';
QUERY PLAN
`--SCAN TABLE df_players

explain query plan select * from df_players where surname = 'Dennis';
QUERY PLAN
`--SEARCH TABLE df_players USING INDEX pix1 (surname=?)
08:39 Reply: SELECT optimization for constant expression (artifact: 8592637c01 user: jdennis)
I have a similar table, named df_players.  Even ignoring all the parameters and the IS NULL, a very simple single column query demonstrates that the LIKE causes the table scan:

explain query plan select * from df_players where surname like 'Dennis';
QUERY PLAN
`--SCAN TABLE df_players

explain query plan select * from df_players where surname = 'Dennis';
QUERY PLAN
`--SEARCH TABLE df_players USING INDEX pix1 (surname=?)
2021-07-11
12:01 Reply: Unstack one column to multiple (artifact: 745f7fe347 user: jdennis)

It would also be useful to know how many records you are needing to process. 100, 1000, 1 million?

2021-04-17
03:09 Reply: SQL query help for between times and grouping times (artifact: da76705fa8 user: jdennis)
>This doesn't work:

But this one does although possibly not in way you would expect. Your time string has a space preceding the 8. Include that space and rows are returned. 

SELECT EventTime,
       Count(EventTime) AS count
FROM   trafficdata
WHERE  EventTime between ' 8:%%:%% PM' and '10:%%:%% PM'
GROUP  BY EventTime;

The preceding comments about using proper date and time formats are a much preferred solution
2021-04-13
12:17 Reply: count strings with same length (artifact: e6e107d431 user: jdennis)
If you only want to pass how many (for example) surnames share the same length, you can do this:
select length(surname),count(surname) from df_players group by 1;

On a small database I look after, I get this:
length(surname)  count(surname)
---------------  --------------
3                52
4                252
5                524
6                660
7                558
8                396
9                211
10               97
11               40
12               11
13               5
14               6
15               2
16               1
17               1
2021-04-05
06:57 Reply: COUNT statement returns inconsistent values when used with UNION ALL (artifact: bfed5dc019 user: jdennis)
So the count(*) is not inconsistent when comparing the two versions, although if a select * returns four rows, one would think a select count(*) should, for consistency, return 4.
06:54 Reply: COUNT statement returns inconsistent values when used with UNION ALL (artifact: da426488b5 user: jdennis)
The final count(*) returns 2 in both 3.33 and 3.35
However SELECT * FROM v2 NATURAL JOIN v2 returns 2 rows of 0 in 3.33 and returns four rows of zero in 3.35 (on Windows 64-bit)

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> CREATE VIEW v0 ( v1 ) AS SELECT CAST ( 0 AS VARCHAR(1) );
sqlite> CREATE VIEW v2 ( v3 ) AS SELECT * FROM v0 UNION ALL SELECT ( v1 IN ( 10) ) from v0;
sqlite> SELECT * FROM v2 NATURAL JOIN v2;
0
0
sqlite> SELECT COUNT(*) FROM v2 NATURAL JOIN v2;
2


SQLite version 3.35.4 2021-04-02 15:20:15
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> CREATE VIEW v0 ( v1 ) AS SELECT CAST ( 0 AS VARCHAR(1) );
sqlite> CREATE VIEW v2 ( v3 ) AS SELECT * FROM v0 UNION ALL SELECT ( v1 IN ( 10) ) from v0;
sqlite> SELECT * FROM v2 NATURAL JOIN v2;
0
0
0
0
sqlite> SELECT COUNT(*) FROM v2 NATURAL JOIN v2;
2
sqlite>
2021-03-23
10:29 Reply: When is SELECT ... FROM with no table-or-subquery valid. (artifact: edb167d7eb user: jdennis)

I sincerely hope NOT. It has been perfectly legal (right up to release 3.24) to do

SELECT 1,datetime(...),trim(...);

There is no FROM there. This thread is about SELECT ... FROM without a table or subquery.

2021-03-14
10:54 Reply: Feature request: pragma for changing the precision / format of CURRENT_TIMESTAMP to YYYY-MM-SSTHH:MM:SS.SSS (artifact: 7de330ef72 user: jdennis)

fair enough...

09:46 Reply: Feature request: pragma for changing the precision / format of CURRENT_TIMESTAMP to YYYY-MM-SSTHH:MM:SS.SSS (artifact: 6be344edc6 user: jdennis)

Even if a database system prints CURRENT_TIMESTAMP as YYYY-MM-DDTHH:MM:SS.SSS, this is not guaranteed to be a timestamp with millisecond accuracy and actually unlikely to be the case.

I understand the inaccuracy, but would the difference between successive CURRENT_TIMESTAMPs be correct, if used to determine the elapsed time to perform a function?

2021-02-10
02:22 Reply: Split an Extract column (artifact: f3889668fe user: jdennis)
One solution:

select replace(replace(phone_number,'(',''),')','') from your_table
2021-02-02
11:36 Reply: SQLite3 Array? (artifact: deff0ca712 user: jdennis)

I see the same error Error: circular reference: paths with 3.33.0 for Windows, using the executables obtained from the SQLite3 website. I haven't yet downloaded 3.34 but will do so tomorrow.

2021-01-27
06:09 Reply: Help with a search command (artifact: 9e370fcc42 user: jdennis)

It could be used, as is, as the first step in a WITH select to restrict the number of rows, which could then subsequently be queried with the original set of OR conditions. A bit more work in the query though...

03:47 Reply: Help with a search command (artifact: fa0dc202f7 user: jdennis)

This solution will also return rows of the form ('it', 'estimates', 'abc')

2021-01-10
03:25 Reply: SELECT question (artifact: ec7f5b7fbe user: jdennis)

It looks to me that there's an extraneous right parenthesis on line 4 - I think "docs.source)" should be "docs.source" ??

2020-11-29
02:42 Reply: Proposal: Add the SUBSTRING alias to the SUBSTR function (artifact: 79aa74c0c8 user: jdennis)

I checked it by running the queries. Both the standard SUBSTRING (s FROM start [FOR length]) and the variant SUBSTRING(s,start[,length]) work, and return identical rows. I would call it a documentation bug, or if you like, an undocumented feature :-) This is in 10.2, I don't know what 10S reports as a version string.

2020-11-27
01:33 Reply: How I can get all the information of *msf file? (artifact: 220baf7035 user: jdennis)

I have no experience of RSQLite, but it appears to me that AC2_enriched01.msf is the name of the database. There are 70+ tables in this database. You need to add or retrieve data from a table, not the whole database.

01:28 Reply: Proposal: Add the SUBSTRING alias to the SUBSTR function (artifact: 88b9365eed user: jdennis)

For what it's worth Ingres (now Actian Ingres, I think) follows PostgreSQL and MySQL - both SUBSTR and SUBSTRING are supported. When I worked with and for Ingres we were stuck with LEFT and RIGHT so a substring got a little messy.

2020-11-06
10:16 Reply: no such table: generate_series (artifact: 077ef5df40 user: jdennis)

Note that your function generate_series(1,1,10) will probably not do what you want. The three parameters are START, END, and STEP - so your function call says "start at 1, end at 1". I suspect you mean generate_series(1,10,1)

2020-10-07
10:37 Reply: Performance Issue: How can I increase a performance? (artifact: 56c4ee0905 user: jdennis)

If this still took an awfully long time with an index on three conditions in the query, then that sounds a bit odd. ANALYZE would be interesting? The RDBMS I am most familiar with used a statistical based query optimizer, but there were still odd queries which didn't make sense. What is the data distribution of the three columns in the condition? What happens if you try the index(list_id, modified, item_id) with the columns in different order?

08:36 Reply: Performance Issue: How can I increase a performance? (artifact: 1187c95a6c user: jdennis)

How many of the 33 million rows are returned by the query which took 150.7ms? Have you tried adding item_id to the index, which would save having to go back to the base table to further restrict the results. For example, there might be 10 million rows matching list_id and modified_date_time, but only a tiny percent might also match item_id.

2020-10-05
09:19 Reply: Database integirity check returns Invalid Page number (artifact: 8b5ca35cd5 user: jdennis)

Are you able to run this check against the database you are copying, rather than the copied database?

2020-09-05
07:57 Edit reply: Join Question (artifact: d4094ae0e6 user: jdennis)
select s.main,a.name,s.backup,b.name
from support s, names a, names b
where s.main=a.key
and s.backup=b.key;

Main  Name    Backup  Name
----  ------  ------  -----
10    Bob     72      Sally
106   Amanda  73      Jose

Edit: This is essentially the same solution at the previous post. Apologies.
07:00 Reply: Join Question (artifact: e75ea49649 user: jdennis)
select s.main,a.name,s.backup,b.name
from support s, names a, names b
where s.main=a.key
and s.backup=b.key;

Main  Name    Backup  Name
----  ------  ------  -----
10    Bob     72      Sally
106   Amanda  73      Jose
2020-08-15
09:32 Reply: Time difference between two records (artifact: a6b23151e3 user: jdennis)

This seems to be assuming that the rows are inserted in the start time sequence. True? Do we know that is how the OP's data is stored?
Can this view be written where the sequence of the inserts is not known, so you have a set of values in no specific order and have to rely on an "order by" to list the set in sequence?

2020-08-11
12:07 Reply: Select by column id (artifact: d8a04672a1 user: jdennis)

Not certain whether this answers your question, but it is perfectly valid to do a select from a table where rowid=9999

The difficulty is knowing the rowid, which may change if the table is reloaded.