> On Sep 12, 2017, at 12:55 PM, Richard Hipp <d...@sqlite.org> wrote:
> But we deliberately omit common subexpression elimination (CSE).  This
> is because our research shows that out of the millions of queries that
> SQLite compiles every second, only a very tiny fraction would actually
> benefit from CSE

OMG, I can see the BuzzFeed headline: "SQLite database engine is secretly 
sending your queries back to their server for analytics!" ;-)

Seriously, I see your point. But JSON support may have created a new class of 
queries that do benefit, because every reference to a JSON property is through 
a function call. So while a traditional query can use the same column name 
multiple times without incurring overhead, the JSON equivalent repeats a 
json_extract call multiple times, which does have overhead.

        SELECT * FROM stuff WHERE length(name) = 5 OR name = 'five';
        SELECT * FROM stuff WHERE length(json_extract(jsn,'$.name')) = 5 OR 
json_extract(jsn,'$.name') = 'five';

If CSE optimization is too expensive to do by default, maybe it could be 
enabled by a flag when preparing the statement?

Unfortunately using a WITH clause doesn't seem to help; as I suspected, it's 
basically used as a macro that gets expanded. Here's one I tried:
        WITH docs AS (SELECT rowid as id, json_extract(jsn,'$.name') AS name 
FROM stuff) 
        SELECT id FROM docs WHERE length(name) <= 5 OR name = 'five';
The EXPLAIN command shows that this query still makes two identical calls to 
json_extract per row.

sqlite-users mailing list

Reply via email to