D1 rows_written is counted in select query

Developers D1
1

Hi, I’m having (maybe) wrong rows_written returned by D1 client API.

In the latest implementation of D1 API await stmt.all() returns meta object containes the field “rows_read” and “rows_written”.
In my study, when I issue a select query with ‘in’ or ‘or’ clause, it returns the positive count of “rows_written”.

  const query = (sql) => db.prepare(sql).all();

  await query(`create table foo (id integer primary key)`);
  await query(`insert into foo (id) values (1)`);
  await query(`insert into foo (id) values (2)`);
  await query(`insert into foo (id) values (3)`);

  await query(`select * from foo`);    //"rows_read":3,"rows_written":0
  await query(`select * from foo where id=1`);    //"rows_read":1,"rows_written":0
  await query(`select * from foo where id=1 or id=2`);    //"rows_read":4,"rows_written":2
  await query(`select * from foo where id in (1,2)`);    //"rows_read":4,"rows_written":2

The bottom 2 calls of avobe code returns “rows_written”:2 even though it is a plain select query.

Here is the whole meta object returned by the bottommost query select * from foo where id in (1,2)

{
	"served_by": "v3-prod",
	"duration": 0.16307299956679344,
	"changes": 0,
	"last_row_id": 3,
	"changed_db": false,
	"size_after": 16384,
	"rows_read": 4,
	"rows_written": 2
}

The version of wrangler is 3.10.1.
I think this is a bug of D1. Hopefully this would be considered.
Thanks.

3

What do you see if you add an EXPLAIN QUERY PLAN before your SELECT queries? Is a virtual table being created under the hood as an optimisation here for the IN which explains the writes?

2 Likes
6

Thanks,
Here is explain results for the query with single matcher

explain query plan select * from foo where id=1
[{"id":2,"parent":0,"notused":0,"detail":"SEARCH foo USING INTEGER PRIMARY KEY (rowid=?)"}]

explain select * from foo where id=1
[{"addr":0,"opcode":"Init","p1":0,"p2":7,"p3":0,"p4":null,"p5":0,"comment":null},
{"addr":1,"opcode":"OpenRead","p1":0,"p2":4,"p3":0,"p4":"0","p5":0,"comment":null},
{"addr":2,"opcode":"Integer","p1":1,"p2":1,"p3":0,"p4":null,"p5":0,"comment":null},
{"addr":3,"opcode":"SeekRowid","p1":0,"p2":6,"p3":1,"p4":null,"p5":0,"comment":null},
{"addr":4,"opcode":"Rowid","p1":0,"p2":2,"p3":0,"p4":null,"p5":0,"comment":null},
{"addr":5,"opcode":"ResultRow","p1":2,"p2":1,"p3":0,"p4":null,"p5":0,"comment":null},
{"addr":6,"opcode":"Halt","p1":0,"p2":0,"p3":0,"p4":null,"p5":0,"comment":null},
{"addr":7,"opcode":"Transaction","p1":0,"p2":0,"p3":26,"p4":"0","p5":1,"comment":null},
{"addr":8,"opcode":"Goto","p1":0,"p2":1,"p3":0,"p4":null,"p5":0,"comment":null}]

and this is the results for the query with in clause

explain query plan select * from foo where id in (1,2)
[{"id":2,"parent":0,"notused":0,"detail":"SEARCH foo USING INTEGER PRIMARY KEY (rowid=?)"}]

explain select * from foo where id in (1,2)
[{"addr":0,"opcode":"Init","p1":0,"p2":21,"p3":0,"p4":null,"p5":0,"comment":null},
{"addr":1,"opcode":"OpenRead","p1":0,"p2":4,"p3":0,"p4":"0","p5":0,"comment":null},
{"addr":2,"opcode":"BeginSubrtn","p1":0,"p2":2,"p3":0,"p4":null,"p5":0,"comment":null},
{"addr":3,"opcode":"Once","p1":0,"p2":12,"p3":0,"p4":null,"p5":0,"comment":null},
{"addr":4,"opcode":"OpenEphemeral","p1":1,"p2":1,"p3":0,"p4":"k(1,)","p5":0,"comment":null},
{"addr":5,"opcode":"Integer","p1":1,"p2":3,"p3":0,"p4":null,"p5":0,"comment":null},
{"addr":6,"opcode":"MakeRecord","p1":3,"p2":1,"p3":4,"p4":"D","p5":0,"comment":null},
{"addr":7,"opcode":"IdxInsert","p1":1,"p2":4,"p3":3,"p4":"1","p5":0,"comment":null},
{"addr":8,"opcode":"Integer","p1":2,"p2":3,"p3":0,"p4":null,"p5":0,"comment":null},
{"addr":9,"opcode":"MakeRecord","p1":3,"p2":1,"p3":4,"p4":"D","p5":0,"comment":null},
{"addr":10,"opcode":"IdxInsert","p1":1,"p2":4,"p3":3,"p4":"1","p5":0,"comment":null},
{"addr":11,"opcode":"NullRow","p1":1,"p2":0,"p3":0,"p4":null,"p5":0,"comment":null},
{"addr":12,"opcode":"Return","p1":2,"p2":3,"p3":1,"p4":null,"p5":0,"comment":null},
{"addr":13,"opcode":"Rewind","p1":1,"p2":20,"p3":0,"p4":null,"p5":0,"comment":null},
{"addr":14,"opcode":"Column","p1":1,"p2":0,"p3":1,"p4":null,"p5":0,"comment":null},
{"addr":15,"opcode":"IsNull","p1":1,"p2":19,"p3":0,"p4":null,"p5":0,"comment":null},
{"addr":16,"opcode":"SeekRowid","p1":0,"p2":19,"p3":1,"p4":null,"p5":0,"comment":null},
{"addr":17,"opcode":"Rowid","p1":0,"p2":5,"p3":0,"p4":null,"p5":0,"comment":null},
{"addr":18,"opcode":"ResultRow","p1":5,"p2":1,"p3":0,"p4":null,"p5":0,"comment":null},
{"addr":19,"opcode":"Next","p1":1,"p2":14,"p3":0,"p4":null,"p5":0,"comment":null},
{"addr":20,"opcode":"Halt","p1":0,"p2":0,"p3":0,"p4":null,"p5":0,"comment":null},
{"addr":21,"opcode":"Transaction","p1":0,"p2":0,"p3":26,"p4":"0","p5":1,"comment":null},
{"addr":22,"opcode":"Goto","p1":0,"p2":1,"p3":0,"p4":null,"p5":0,"comment":null}]

I examined explain select ... since there are no difference for explain query plan select ....
but I don’t familiar with the internal of SQLite.

Can it be read whether the virtual table is created by this log?

7

Yeah, there is an insert step on the second query… so it creates a virtual table to filter against.

I’m really not sure if it’s SQLite or D1 doing it… but I bet it’s SQLite.

edit I checked against a local SQLite instance, and it’s SQLite that does it this way.

1 Like
8

Thank you for the confirmation.
So the wrong part is D1’s counting logic isn’t it?

9

@cherryjimbo asked the team, but as far as I can tell, there are no errors anywhere. The writes are there, one wouldn’t expect them, but the virtual table is created.

10

I’m expecting the write occured by SQLite’s internal optimization with virtual table is not counted as rows_written.
Is this correct?
Or is it included into the counts by D1’s pricing specification?
I hope it to be clearified.
Thanks.