Note that you may pass different item_id values to the second query, which
would then return 0 rows, but quickly. If you need to reference the same
parameter in more than one location inside the SQL Statement, use explicit
names or numbers
SELECT
*
FROM
item_info
It would be easier to reproduce if you had checked the provided sample code for
errors first...
.) Error: near ")": syntax error
.) Error: cannot join using column item_id - column not present in both tables
Your first query specifies a full table scan over the users table (via the
covering
Gah, sorry. Another typo. I really should be more awake when I post to
this list. The non-simplified code does have the item_id on the subquery
(otherwise it simply wouldn't execute at all of course). So:
SELECT *
FROM item_info
JOIN (
select
count(1) as num,
item_id
When you are executing the query:
SELECT *
FROM item_info
JOIN (select count(1)
from users
group by item_id)
USING (item_id)
where item_id = ?;
You are telling the SQL Database Engine (whatever it may happen to be, in this
case SQLite3) that you want to take the table
Thanks Keith...
I was indeed surprised to get a result at all - I have no idea where the math
functions are coming from - I'm using the standard Ubuntu Linux install...
which it seems may be broken in some way.
The only files I can find on my system relating to these functions are indeed
However, if you want to do that then you want to use a correlated subquery as
it is designed for retrieving correlated data by running a subquery per result
row:
select *,
(select count(*) from users where item_id = item_info.item_id) as count
from item_info
where item_id = ?;
and
Hi Simon,
Thanks for that - I'm always trying to improve my SQL. I think I see
what you mean now.
Assuming my original query:
SELECT
*
FROM
item_info
JOIN (select count(1) from users group by item_id)
USING (item_id)
where item_id = ?;
There are three
On 2 Jan 2019, at 9:50pm, Jonathan Moules wrote:
> Sorry, but which column is ambiguous? The users.item_id is a foreign key to
> the item_info.item_id - that's why it's a "REFERENCES" - why would I want to
> change it to be something else? Isn't the convention for FK's to have the
> same name
Sorry, but which column is ambiguous? The users.item_id is a foreign key
to the item_info.item_id - that's why it's a "REFERENCES" - why would I
want to change it to be something else? Isn't the convention for FK's to
have the same name across tables? That's what "USING" is for right? (or
Of course. In fact that is how it is used in the shell.c code to allow the
interruption of long running queries ... sqlite3_interrupt is attached to the
SIGINT/SIGBREAK signal handler so that a BREAK from the keyboard interrupts the
query execution.
---
The fact that there's a Highway to
SQLite 3.27.0 2018-12-31 21:43:55
b57c545a384ab5d62becf3164945b32b1e108b2fb4c8dbd939a1706c2079alt2
zlib version 1.2.11
gcc-8.1.0
sqlite> select degrees( radians(175) + atan2( sin(radians(90)) *
...> sin(0.2/6378.14)*cos(radians(-42)), cos(0.2/6378.14) -
...>
Yup, there was a typo, but you guys know what he meant. He's saying there WHERE
could be pushed through the join to the subquery.
sqlite> explain query plan select * from item_info join (select item_id,
count(1) from users group by item_id) using (item_id) where item_id = ?;
QUERY PLAN
On 1/2/19, James K. Lowden wrote:
>
> can I call sqlite3_interrupt from a
> signal handler?
>
Yes. It was designed for that very purpose, and more specifically to
catch Ctrl-C in the CLI and stop the running query rather than kill
off the whole process.
--
D. Richard Hipp
d...@sqlite.org
On 1/2/19, Joe Jacobs wrote:
>
> Long story short is that this may be i'm using an old version of sqlite
> lite and the command has changed.
>
Yes. Since 2013-05-20, there have been 73 releases and 10167 distinct
changes. One of those 10167 changes was this:
On 2 Jan 2019, at 4:44pm, Jonathan Moules wrote:
> SELECT
> *
> FROM
> item_info
> JOIN (select count(1) from users group by item_id)
> USING (item_id)
> where item_id = ?;
You have an ambiguous column name, and I don't think SQLite is doing what you
think
> On Jan 2, 2019, at 12:22 PM, James K. Lowden wrote:
>
> If I don't want to use threads, can I call sqlite3_interrupt from a
> signal handler?
It looks like you can, since all the implementation does is set a boolean flag
inside the sqlite3 struct.
However, if SQLITE_ENABLE_API_ARMOR is
UNABLE TO REPRODUCE.
Your CREATE TABLEs do not work (they contain syntax errors).
The query you complain about taking a long time does not and cannot work
because it is attempting to join two tables using a common column name, that
column name NOT being contained in one of the tables
I think this is an error in the command line documentation or room for
minor improvement. I was reading through the docs trying to learn about
the command line tool and ran into a snag, so i thought i'd report it.
https://www.sqlite.org/cli.html
In section 3 where it talks about dot commands
On Mon, 31 Dec 2018 14:25:41 -0700
"Keith Medcalf" wrote:
> def run_query_with_timeout(db, query, timeout, whizround)
> stmt = prepare(db, query)
> create_thread A interrupt_function(db, stmt, timeout,
> whizround) while sqlite3_step(stmt) == SQLITE_ROW
> ... process
> On Dec 31, 2018, at 1:49 PM, Simon Slavin wrote:
>
> If you have a 100 Gig table with no indexes suited to your clauses, creating
> a temporary index might take a minute or two. But you really only have
> yourself to blame. […]
> Since the one possibly-long operation happens at the very
sqlite> select degrees( radians(175) + atan2( sin(radians(90)) *
sin(0.2/6378.14)*cos(radians(-42)), cos(0.2/6378.14) -
sin(radians(-42))*sin(radians(-42;
264.997582396241
postgres=# select degrees( radians(175) + atan2( sin(radians(90)) *
sin(0.2/6378.14)*cos(radians(-42)),
Hi,
I'm trying to create an SQL in Sqlite3 which returns the X &
Y coords for a point, given origin coordinates in degrees, with distance
and bearing to the new location.
My install of Spatialite (Mint Linux) does not support the Project()
function, and it seems easier to write the SQL
Hi List,
The below seems to my very-non-expert mind like there's scope for
query-plan optimisation.
I have two tables (simplified below):
CREATE TABLE users (
item_id TEXT REFERENCES item_info (item_id)
NOT NULL
COLLATE NOCASE,
some_data
Please note that this is in a typedef. The initialization needs to happen when
a structure of this type is declared.
const PragmaName g_mypragma = { "mypragma", ...};
Would allow the compiler to put *both* the string "mypragma" *and* the
structure g_mypragma into (read only) "string space",
On Wed, Jan 2, 2019 at 1:47 PM Richard Damon
wrote:
> On 12/30/18 6:10 PM, Richard Green wrote:
> const char *const zName; // Note extra space
>
> Then that is declaring that zName is an immutable pointer to a immutable
> string/character, which is actually likely true, as the code shouldn't
>
On 12/30/18 6:10 PM, Richard Green wrote:
> I believe the struct PragmaName (in pragma.h) has an extra 'const'
> keyword for zName, in Version 3.26.0 (2018-12-01); probably has no
> effect.
>
> Currently,
>
> /* Definitions of all built-in pragmas */
> typedef struct PragmaName {
> const char
There is a significant difference
A "const char *" is a (mutable) pointer to an immutable char. You can make it
point somewhere else.
A "char * const" is an immutable pointer to a mutable char. You can change the
char it is pointing to.
A "const char * const" is an immutable pointer to an
I believe the struct PragmaName (in pragma.h) has an extra 'const'
keyword for zName, in Version 3.26.0 (2018-12-01); probably has no effect.
Currently,
/* Definitions of all built-in pragmas */
typedef struct PragmaName {
const char *constzName; /* Name of pragma */
u8 ePragTyp;
On Wednesday, 2 January, 2019 03:06, Dominique Devienne
wrote:
>On Mon, Dec 31, 2018 at 10:31 PM Keith Medcalf wrote:
>>> I don't think the interrupt call will actually terminate a step
>>> that is actually being processed, but only mark that no more steps
>>> should happen. In other words,
On Mon, Dec 31, 2018 at 10:31 PM Keith Medcalf wrote:
> >I don't think the interrupt call will actually terminate a step that
> >is actually being processed, but only mark that no more steps should
> >happen. In other words, I don't think SQLite is spending time
> >checking a flag to stop in the
30 matches
Mail list logo
