So the QP is attemopting to determine which cost product is less:
Outer loop IN table (2 records, assuming cost 2) * Inner loop VT (2) = 4
Outer loop VT (3) * inner loop IN lookup (1 record, assuming cost 1) = 3
My guess is it will probably choose the undesired, invalid plan. A linear cost
pena
On Wed, Apr 17, 2019 at 11:43:13AM -0600, Keith Medcalf wrote:
>
> Your made up plans are intriguing. The plan you show for the latter query
> omit to join a and b. Are you just making things up?
The query plans were cut and pasted from the terminal. It's easy enough to
deduce where these pla
On 4/17/19, Peter da Silva wrote:
> whether it's a
> bound parameter or a constant in the query string doesn't tell you if it's
> from a trusted source or not.
How do you get an SQL injection vulnerability to call sqlite3_bind()?
Worse case is that the attacker can make use of an existing parame
I don't think I would use this as a security indicator, whether it's a
bound parameter or a constant in the query string doesn't tell you if it's
from a trusted source or not. This is more an indicator that this value is
likely to change in subsequent queries.
__
Besides which, of course, you example is useless. The sqlite3_value_frombind
would be used to find out if the parameter came from a binding or not as in,
within the SomeFunction function, to determine if the parameter were a "bound"
parameter or not. Example:
select SomeFunction(?);
select S
On 17 Apr 2019, at 7:37pm, Keith Medcalf wrote:
> There are fields (columns) in your invoices table named 1.23 and 7524? Why
> did you do this (or did you just use the wrong quotes around text strings?)
I used the wrong quotes. Sorry, I've been doing things in other languages
recently. Keit
Simon,
There are fields (columns) in your invoices table named 1.23 and 7524? Why did
you do this (or did you just use the wrong quotes around text strings?)
---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a
lot about anticipated traffic volume.
>-Origina
So it becomes the responsibility of the application to acknowledge when
binding is allowed or not. Got it. Clear now. Thanks.
I was thinking small in this case. My own app, I know what's being fed, I
already bind most things, but I absolutely can see the use of this. Time
to see if I can add
On 17 Apr 2019, at 6:37pm, Stephen Chrzanowski wrote:
> What measures the trustworthiness? At what point would the running
> application be notified that the statement was bound or injection avenue?
You can include parameters as text in your SQL command:
UPDATE invoices SET toBePaid="1.23"
Your made up plans are intriguing. The plan you show for the latter query omit
to join a and b. Are you just making things up?
sqlite> select a.rowid from a, b where a.ref=7 and a.rowid in (select rowid
from b);
QUERY PLAN
|--SEARCH TABLE a USING COVERING INDEX aa (ref=? AND rowid=?) (~8 rows
What measures the trustworthiness? At what point would the running
application be notified that the statement was bound or injection avenue?
On Wed, Apr 17, 2019 at 12:40 PM Richard Hipp wrote:
> On 4/17/19, Jens Alfke wrote:
> > The new sqlite3_value_frombind() function sounds intriguing — "T
On 4/17/19, Jens Alfke wrote:
> The new sqlite3_value_frombind() function sounds intriguing — "True if value
> originated from a bound parameter
> — but I’m drawing a blank thinking of use cases for it. Optimizations?
> Security? What was the rationale for adding it?
This facilities additional s
The commit that added it (
https://www3.sqlite.org/cgi/src/info/b3f2c3205a28dc21) says that it's used
to improve fts3_tokenizer(), so maybe look at the diffs of that function to
see how it's used there to get an idea for what it's intended for?
On Wed, Apr 17, 2019, 9:22 AM Jens Alfke wrote:
> T
The new sqlite3_value_frombind() function sounds intriguing — "True if value
originated from a bound parameter
— but I’m drawing a blank thinking of use cases for it. Optimizations?
Security? What was the rationale for adding it?
—Jens
___
sqlite-user
On Wed, 17 Apr 2019 at 15:08, Hick Gunter wrote:
>
> SLEECT contents from textfiles where search_path = 'mypath' and
> name_pattern IN ('*.txt','*.csv');
>
>
It's interesting, I implemented the mask and decided to give such a query a
try (having the same cost adjust I explained in the first post)
On Wed, Apr 17, 2019 at 01:24:11PM +, David Raymond wrote:
> Would you post what those explain query plans results are? All the other
> replies not withstanding I'm still curious as to why #2 would be faster
> (assuming "rowid" is indeed the actual rowid anyway)
>
> Also, is that a typo in #
Second select is not correct. You should have "from a" only not "from a,b".
R.Antloga
On 17.04.2019 10:55, Poor Yorick wrote:
I've used the following two test queries in a version of sqlite built against a
recent checkout of trunk, and also another recent version of sqlite. a.ref is
indexed.
Would you post what those explain query plans results are? All the other
replies not withstanding I'm still curious as to why #2 would be faster
(assuming "rowid" is indeed the actual rowid anyway)
Also, is that a typo in #2, if you're not using b, why would you include it in
the from clause? W
On Wed, 17 Apr 2019 at 15:08, Hick Gunter wrote:
> ...
>
> SELECT contents from textfiles( 'mypath', NULL, 0);
> SELECT contents from textfiles where search_path = 'mypath' and
> is_recursive = 1;
> SLEECT contents from textfiles where search_path = 'mypath' and
> name_pattern IN ('*.txt','*.
Also, let me just add (in case it sounded different) - We definitely do
not wish to document the "why an optimization opportunity might not be
feasible" in any way that would discourage anyone from submitting such a
possible optimization opportunity. That would work against the axiomatic
premis
On 2019/04/17 1:23 PM, Poor Yorick wrote:
That's an apt and accessible description of the issue, but at the denotational
level the meanings of the queries are in fact identical under the conditions
you enumerated. Ideally sqlite would notice and adjust its query plan
accordingly.
Ideally yes,
Ok so lets assume your xConnect function declares something similar to
Create table textfiles( result_path text, result_name text, contents blob,
search_path text hidden, name_pattern text hidden, is_recursive hidden);
So that you can
SELECT contents from textfiles( 'mypath', NULL, 0);
SELECT c
On Wed, Apr 17, 2019 at 11:36:18AM +0200, R Smith wrote:
> On 2019/04/17 10:55 AM, Poor Yorick wrote:
[SNIP]
>
> In your above example you really wish to know all the a's which have an
> entry in b. The first query asks to join and list all b's found for every a
> (which works mathematically in th
On Wed, 17 Apr 2019 at 12:52, Hick Gunter wrote:
> Your xBestIndex function should be returning a cost that is proportional
> to the "effort required to fulfill the query". My own VT implementations
> have been returning the total number of records for queries with no
> constraints and assuming a
On 4/16/19, Esenthel wrote:
>
> I never got any reply for this
You have to sign up for the mailing list in order to get email
replies. If you do not sign up, you have to poll for responses on one
of the archives, such as
https://www.mail-archive.com/sqlite-users@mailinglists.sqlite.org/thrd5.htm
On Wed, Apr 17, 2019 at 10:15:31AM +, Hick Gunter wrote:
> Try EXPLAIN QUERY PLAN or even EXPLAIN to see what is going on
> in each case.
I already have, of course. The question is, how much effort would it be to get
sqlite choose the better query plan in the "join" case as well?
--
Po
Hi,
I never got any reply for this
On Thu, 21 Feb 2019 at 10:31, Esenthel wrote:
> I'm trying to use "sqlite3_open_v2" with a custom VFS (file system), and
> pass a pointer "void *user_data" to the sqlite3_open_v2 function, so it
> gets passed down to the "sqlite3_vfs::xOpen"
> However there's
Try EXPLAIN QUERY PLAN or even EXPLAIN to see what is going on in
each case.
-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im
Auftrag von Poor Yorick
Gesendet: Mittwoch, 17. April 2019 10:56
An: sqlite-users@mailinglists.sqlite.org
Be
Your xBestIndex function should be returning a cost that is proportional to the
"effort required to fulfill the query". My own VT implementations have been
returning the total number of records for queries with no constraints and
assuming a constant fan-out factor for each key field provided as
The sqlite3_db_config() function is a variable argument one, taking a
database handle, a config option, and then what the documentation says
is 'Subsequent
arguments vary depending on the configuration verb.'.
Aside from SQLITE_DBCONFIG_MAINDBNAME and SQLITE_DBCONFIG_LOOKASIDE, they
all require an
On 2019/04/17 10:55 AM, Poor Yorick wrote:
I've used the following two test queries in a version of sqlite built against a
recent checkout of trunk, and also another recent version of sqlite. a.ref is
indexed. The subselect query is faster than the join query -- two orders of
magnitude faster o
I've used the following two test queries in a version of sqlite built against a
recent checkout of trunk, and also another recent version of sqlite. a.ref is
indexed. The subselect query is faster than the join query -- two orders of
magnitude faster on a larger dataset. Is sqlite missing some e
Hi,
I'm trying to implement "optional incoming" parameters for my virtual
tables. It's when there are columns required, but also there are some
fine-tuning columns that should be noticed when provided in the query and
assumed some default if not. The system that I tried seems to work, but I
would
33 matches
Mail list logo