Unable to find field with index ... in row of ... columns

[DavidLokison]

I spent some time trying to find a way to reproduce the issue since it's somewhat obscure... I tried to add some data to a queue (for emailing purposes) whenever a row's status is updated. Outside of the trigger, the queries are working fine, but inside the trigger they break with either a "unable to find field with index" error or a "is not a valid value" error (both probably due to the same issue).

Example 1

First occasion I reproduced the issue with a reasonable amount of SQL code.

CREATE TABLE t_1 (id BINARY(2), data VARCHAR(10));
CREATE TABLE t_2 (id BINARY(2), other BINARY(2), status TINYINT UNSIGNED DEFAULT 0);
CREATE TABLE queue (id BINARY(2), data VARCHAR(10));
CREATE VIEW v AS SELECT t_2.id, t_1.data, status FROM t_2 INNER JOIN t_1 ON other = t_1.id;
INSERT INTO t_1 (id, data) VALUES (0xC336, "Test");
INSERT INTO t_2 (id, other) VALUES ROW(0x3E32, 0xC336), ROW(0xEDC1, 0xC336), ROW(0x9B15, 0xC336);
CREATE TRIGGER t_change_status AFTER UPDATE ON t_2 FOR EACH ROW INSERT INTO queue (id, data) SELECT id, data FROM v WHERE v.id = NEW.id;
UPDATE t_2 SET status = 1 WHERE id = 0x3E32;

Produces "unable to find field with index 9 in row of 5 columns". I originally thought it was due to me JOINing the source table with another table, but that just exxagerates the indices.

Example 2

Then I got rid of the JOIN cause I thought that might be a problem.

CREATE TABLE t_1 (id BINARY(2), data VARCHAR(10));
CREATE TABLE t_2 (id BINARY(2), other BINARY(2), status TINYINT UNSIGNED DEFAULT 0);
CREATE TABLE queue (id BINARY(2), status TINYINT UNSIGNED);
CREATE VIEW v AS SELECT t_2.id, status FROM t_2;
INSERT INTO t_1 (id, data) VALUES (0xC336, "Test");
INSERT INTO t_2 (id, other) VALUES ROW(0x3E32, 0xC336), ROW(0xEDC1, 0xC336), ROW(0x9B15, 0xC336);
CREATE TRIGGER t_change_status AFTER UPDATE ON t_2 FOR EACH ROW INSERT INTO queue (id, status) SELECT id, status FROM v WHERE id = NEW.id;
UPDATE t_2 SET status = 1 WHERE id = 0x3E32;

Produces "[195 54] out of range for tinyint unsigned". (With a larger id size, it would produce a "is not a valid value".) Coming closer to the column, but not quite.

What ended up not producing an error

By switching the status and other columns, I lined up the table t_2 with the view and the selection from the trigger.

CREATE TABLE t_1 (id BINARY(2), data VARCHAR(10));
CREATE TABLE t_2 (id BINARY(2), status TINYINT UNSIGNED DEFAULT 0, other BINARY(2));
CREATE TABLE queue (id BINARY(2), status TINYINT UNSIGNED);
CREATE VIEW v AS SELECT t_2.id, status FROM t_2;
INSERT INTO t_1 (id, data) VALUES (0xC336, "Test");
INSERT INTO t_2 (id, other) VALUES ROW(0x3E32, 0xC336), ROW(0xEDC1, 0xC336), ROW(0x9B15, 0xC336);
CREATE TRIGGER t_change_status AFTER UPDATE ON t_2 FOR EACH ROW INSERT INTO queue (id, status) SELECT id, status FROM v WHERE id = NEW.id;
UPDATE t_2 SET status = 1 WHERE id = 0x3E32;
SELECT * FROM queue;

This "works", but produces a wrong line. What I thought it'l do is inserting status 1 into the queue (cause the trigger is done AFTER UPDATE), but checking the entries of the queue reveals a status of 0 just like the trigger was executed BEFORE UPDATE.

[timsehn]

@angelamayxie or @elianddb will look at this today

[angelamayxie]

seems like the issue might have to do with referencing a view inside a trigger -- will look into it further

[angelamayxie]

@DavidLokison Sorry for the delay on this bug -- there seems to be multiple bugs happening at the same time, and it's taking some time to unwrap, coupled with the fact that I have not been feeling well the past couple of days. Don't worry, definitely still working on this.

[DavidLokison]

Thank you for taking a look at this! I have worked around the issue for now by outsourcing the logic into a procedure, which is handled "fine" minus the non-updated row but that I go around by passing the respective column as a parameter. So currently it's not crucial to my project at least.

Hope you feel better soon, all good blesses to you!

[angelamayxie]

@DavidLokison I started with investigating the 3rd example you provided (the one that didn't produce an error). I filed a separate issue (#10196) for the root of the problem -- it seems like a pretty hard bug to fix and may require some more in-depth discussion with the larger team.

Still need investigate why you're getting errors in your other query sets.

Thank you for filing bugs for improving our correctness and test coverage!

[DavidLokison]

Well, I am only trying to use dolt directly in a production environment which handles sensitive customer data 😅 that's probably why I trip over those obscure problems in the first place.

And since I don't have the funds to actually sustain a subscription, best I can do is try to come up with easily reproducible bug descriptions. Thank you all for maintaining this awesome project!

[angelamayxie]

@DavidLokison The bug in example 2 has been fixed and the fix will be in the next Dolt release. The trigger will still not work as expected though since it's an after update trigger that references the triggered table (via the view), but at least it will get the right columns now.

Looking into the bug in example 1 now.

[angelamayxie]

the queries in example 1 seem to work without using a view (using a join directly in the trigger). so this issue seems specific to views. investigating further

[angelamayxie]

@DavidLokison fixed the indexing issue from Example 1, and the fix will also be in the next Dolt release. Closing this issue since the bug in Example 3 has a separate issue (#10196) filed for it.