D1 rows_written is counted in select query

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.

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

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?

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

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

@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.

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.

I found a strange hack for this.

Basically add and 1=1 to first or

SELECT * FROM Customers WHERE
(CustomerId = 1 and 1 = 1) or
CustomerId = 2 or
CustomerId = 3 or
CustomerId = 13

meta shows that this costs 0 writes and 4 reads.

It seems that under the hood it does 4 calls instead of one.

Below is example for 3 ids

EXPLAIN QUERY PLAN SELECT * FROM Customers WHERE (CustomerId = 1 and 1=1) or CustomerId = 2 or CustomerId = 13

id parent notused detail
4 0 0 MULTI-INDEX OR
5 4 0 INDEX 1
9 5 0 SEARCH Customers USING INTEGER PRIMARY KEY (rowid=?)
13 4 0 INDEX 2
16 13 0 SEARCH Customers USING INTEGER PRIMARY KEY (rowid=?)
20 4 0 INDEX 3
23 20 0 SEARCH Customers USING INTEGER PRIMARY KEY (rowid=?)

Speed-wise its 10ms slower than doing just or. But its on a 14 rows table.
I don’t know how this would go on a bigger table like 1M rows.

Hi! PM for D1 here. We’re investigating here.

Besides the WHERE IN () example, are there other examples where folks see rows_written>0 for read-only queries?