SQLite Forum

Timeline
Login

6 forum posts by user coleifer

2021-07-05
18:08 Post: row value misuse (artifact: 57f1d56b95 user: coleifer)

I was hoping someone could help me understand the rules around using row values and the VALUES keyword. I am having a little trouble understanding what is and isn't allowed.

Starting with a simple table containing a key, value:

create table kv (key text, val integer);
insert into kv (key, val) values ('k1', 1), ('k2', 2), ('k3', 3);

The following works with Sqlite:

select * from kv
where (key, val) = ('k1', 1);

The following seems to work in Postgres, but Sqlite is reporting "row value misused":

select * from kv
where (key, val) in (('k1', 1), ('k3', 3));

The following works in both Postgres and Sqlite:

select * from kv
where (key, val) in (values ('k1', 1), ('k3', 3));

Another thing I have seen done is to join on a list of values, which works well in Postgres, but Sqlite reports a syntax error near "(":

select * 
from kv 
inner join (values ('k1', 1), ('k3', 3)) as bar("k", "v")
  on kv.key = bar.k and kv.val = bar.v;

The following, unsurprising, also works in Postgres but not Sqlite:

select *
from kv, (values ('k1', 1), ('k3', 3)) as bar(k, v)
where (kv.key = bar.k AND kv.val = bar.v);

Moving it into a CTE works in both Postgres and Sqlite:

with bar(k, v) as (values ('k1', 1), ('k3', 3))
select * from kv
inner join bar 
  on (kv.key = bar.k and kv.val = bar.v);

My questions are:

  • When should one use plain row-values versus VALUES?
  • Why does (key, val) = ('k1', 1) work but (key, val) IN (('k1', 1)...) not?
  • Are there ways of constructing the above type of query which would be better or more idiomatic with Sqlite?

Thank you in advance for the help.

2021-02-05
00:24 Reply: RETURNING clause require cursor consumption (artifact: 398f520356 user: coleifer)

This change is working well for me. Here's a small test program I used to check the behaviors:

Inserting multiple rows working as expected:

conn = sqlite3.connect(':memory:')
conn.execute('create table reg(id integer primary key, k text)')

cur = conn.execute('insert into reg (k) values (?), (?), (?) returning id',
                   ('k1', 'k2', 'k3'))
print(next(cur))  # First result row.

# Prints (1,)

cur = conn.execute('select * from reg')
print(cur.fetchall())

# All inserts have been processed, output is now:
# Prints: [(1, 'k1'), (2, 'k2'), (3, 'k3')]

Similar workflow with an UPDATE this time also works as expected:

cur = conn.execute('update reg set k=k||? where k in (?, ?, ?) returning id, k',
                   ('x', 'k1', 'k3', 'k2'))
print(next(cur))  # First result row.

# Prints: (1, 'k1x')

cur = conn.execute('select * from reg')
print(cur.fetchall())

# All updates were processed. Output is:
# [(1, 'k1x'), (2, 'k2x'), (3, 'k3x')]

Do an upsert which will be executed the first time, and not the 2nd time. Both operations working as expected:

# Check behavior w/upsert
conn.execute('create table r2(id integer primary key, k text unique, v real)')
conn.execute('insert into r2 (k, v) values (?, ?), (?, ?)',
             ('k1', 1, 'k2', 2))

for _ in range(2):
    curs = conn.execute('insert into r2 (k, v) values (?, ?) '
                        'on conflict(k) do update set v = v + ? '
                        'where v <= ? '
                        'returning id, k, v', ('k1', 3, 0.5, 1))
    print(curs.lastrowid)
    try:
        print(next(curs))
    except StopIteration:
        pass

# Correctly prints:
# 2
# (1, 'k1', 1.5)
# 2
# <no output here, since no rows via RETURNING>
2021-02-04
16:58 Reply: RETURNING clause require cursor consumption (artifact: 4188f7d464 user: coleifer)

I had been looking at the commit log and not the corresponding draft documentation for the release. It seems that the docs clarify that this is an implementation detail and undefined behavior:

https://www.sqlite.org/draft/lang_returning.html

However, applications should not depend on this ordering. Future versions of SQLite might precompute the outputs and return them all before any rows have been modified, or it might save all the outputs into a buffer then play the buffer back after all database modifications have been applied. Or future versions of SQLite might use a mixture of these algorithms.

Seems like it is behaving as intended, although I'm a little concerned about integrating this into libraries that interop with Postgres where the behavior is different. It certainly caught me off-guard.

16:41 Post: RETURNING clause require cursor consumption (artifact: b3942eeaa5 user: coleifer)

I was a bit surprised that using a RETURNING clause requires explicit consumption of the resulting statement object. Example using Python:

conn = sqlite3.connect(':memory:')
conn.execute('create table reg(id integer primary key, k text)')

cur = conn.execute('insert into reg (k) values (?), (?), (?) returning id',
                   ('k1', 'k2', 'k3'))
print(next(cur))  # First result row.

cur = conn.execute('select * from reg')
print(cur.fetchall())

The above code prints out:

(1,)  <-- the first id inserted
[(1, 'k1'), (2, 'k2')]  <-- first and second row

The python driver has called sqlite3_step twice under-the-hood for our INSERT, and as a result only the first 2 rows are inserted.

My expectation, and the way postgres seems to work, is that the INSERT is performed regardless of whether we consume all the returned rows. I want to raise this now, so if this is expected behavior, I can plan how to work around it in my library code.

2020-05-27
20:59 Reply: SQLITE_DETERMINISTIC change in 3.32 (artifact: 3cbcc77145 user: coleifer)

I've found that it makes better sense to

SELECT 1 WHERE deterministic() == deterministic()

In this way the function is only called once.

2020-05-01
02:21 Reply: how to force python to use recent sqlite3 version (artifact: 233dca7652 user: coleifer)