Bug: SQLite stack overflows when adding members to group that already has members

Describe the bug

During benchmarking I tried adding 5 members to a group that already had 100 members, but this fails when we try to load association state for members that already exist. Here are some logs that show the issue:

Benchmarking add_to_100_member_group_by_address/5: Warming up for 3.0000 sHere
Loading members is problematic
Failing SQL SELECT `association_state`.`inbox_id`, `association_state`.`sequence_id`, `association_state`.`state` FROM `association_state` WHERE ((`association_state`.`inbox_id` = ?) AND (`association_state`.`sequence_id` = ?)) -- binds: ["91cc6e354c52a9ae0bb90ea1a4e9cc67287cfdd281e3c3d3b27aebe393083846", 0]
here 2
Most likely here 3
Adding 100 Members by inbox id
Adding 5 to group
Here
Loading members is problematic
Failing SQL SELECT `association_state`.`inbox_id`, `association_state`.`sequence_id`, `association_state`.`state` FROM `association_state` WHERE ((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((`association_state`.`inbox_id` = ?) AND (`association_state`.`sequence_id` = ?)) OR ((`association_state`.`inbox_id` = ?) AND (`association_state`.`sequence_id` = ?))) OR ((`association_state`.`inbox_id` = ?) AND (`association_state`.`sequence_id` = ?))) OR ((`association_state`.`inbox_id` = ?) AND (`association_state`.`sequence_id` = ?))) OR ((`association_state`.`inbox_id` = ?) AND (`association_state`.`sequence_id` = ?))) OR ((`association_state`.`inbox_id` = ?) AND (`association_state`.`sequence_id` = ?))) OR ((`association_state`.`inbox_id` = ?) AND (`association_state`.`sequence_id` = ?))) OR ((`association_state`.`inbox_id` = ?) AND (`association_state`.`sequence_id` = ?))) OR ((`association_state`.`inbox_id` = ?) AND (`association_state`.`sequence_id` = ?))) OR ((`association_state`.`inbox_id` = ?) AND (`association_state`.`sequence_id` = ?))) OR ((`association_state`.`inbox_id` = ?) AND (`association_state`.`sequence_id` = ?))) OR ((`association_state`.`inbox_id` = ?) AND (`association_state`.`sequence_id` = ?))) OR ((`association_state`.`inbox_id` = ?) AND (`association_state`.`sequence_id` = ?))) OR ((`association_state`.`inbox_id` = ?) AND (`association_state`.`sequence_id` = ?))) OR ((`association_state`.`inbox_id` = ?) AND (`association_state`.`sequence_id` = ?))) OR ((`association_state`.`inbox_id` = ?) AND (`association_state`.`sequence_id` = ?))) OR ((`association_state`.`inbox_id` = ?) AND (`association_state`.`sequence_id` = ?))) OR ((`association_state`.`inbox_id` = ?) AND (`association_state`.`sequence_id` = ?))) OR ((`association_state`.`inbox_id` = ?) AND (`association_state`.`sequence_id` = ?))) OR ((`association_state`.`inbox_id` = ?) AND (`association_state`.`sequence_id` = ?))) OR ((`association_state`.`inbox_id` = ?) AND (`association_state`.`sequence_id` = ?))) OR ((`association_state`.`inbox_id` = ?) AND (`association_state`.`sequence_id` = ?))) OR ((`association_state`.`inbox_id` = ?) AND (`association_state`.`sequence_id` = ?))) OR ((`association_state`.`inbox_id` =?) AND (`association_state`.`sequence_id` = ?))) OR ((`association_state`.`inbox_id` = ?) AND (`association_state`.`sequence_id` = ?))) OR ((`association_state`.`inbox_id` = ?) AND (`association_state`.`sequence_id` = ?))) OR ((`association_state`.`inbox_id` = ?) AND (`association_state`.`sequence_id` = ?))) OR ((`association_state`.`inbox_id` = ?) AND (`association_state`.`sequence_id` = ?))) OR ((`association_state`.`inbox_id` = ?) AND (`association_state`.`sequence_id` = ?))) OR ((`association_state`.`inbox_id` = ?) AND (`association_state`.`sequence_id` = ?))) OR ((`association_state`.`inbox_id` = ?) AND (`association_state`.`sequence_id` = ?))) OR ((`association_state`.`inbox_id` = ?) AND (`association_state`.`sequence_id` = ?))) OR ((`association_state`.`inbox_id` = ?) AND (`association_state`.`sequence_id` = ?))) OR ((`association_state`.`inbox_id` = ?) AND (`association_state`.`sequence_id` = ?))) OR ((`association_state`.`inbox_id` = ?) AND (`association_state`.`sequence_id` = ?))) OR ((`association_state`.`inbox_id` = ?) AND (`association_state`.`sequence_id` = ?))) OR ((`association_state`.`inbox_id` = ?) AND (`association_state`.`sequence_id` = ?))) OR ((`association_state`.`inbox_id` = ?) AND (`association_state`.`sequence_id` = ?))) OR ((`association_state`.`inbox_id` = ?) AND (`association_state`.`sequence_id` = ?))) OR ((`association_state`.`inbox_id` = ?) AND (`association_state`.`sequence_id` = ?))) OR ((`association_state`.`inbox_id` = ?) AND (`association_state`.`sequence_id` = ?))) OR ((`association_state`.`inbox_id` = ?) AND (`association_state`.`sequence_id` = ?))) OR ((`association_state`.`inbox_id` = ?) AND (`association_state`.`sequence_id` = ?))) OR ((`association_state`.`inbox_id` = ?) AND (`association_state`.`sequence_id` = ?))) OR ((`association_state`.`inbox_id` = ?) AND (`association_state`.`sequence_id` = ?))) OR ((`association_state`.`inbox_id` = ?) AND (`association_state`.`sequence_id` = ?))) OR ((`association_state`.`inbox_id` = ?) AND (`association_state`.`sequence_id` = ?))) OR ((`association_state`.`inbox_id` = ?) AND (`association_state`.`sequence_id` = ?))) OR ((`association_state`.`inbox_id` = ?) AND (`association_state`.`sequence_id` = ?))) OR ((`association_state`.`inbox_id` = ?) AND (`association_state`.`sequence_id` = ?))) OR ((`association_state`.`inbox_id` = ?) AND (`association_state`.`sequence_id` = ?))) OR ((`association_state`.`inbox_id` = ?) AND (`association_state`.`sequence_id` = ?))) OR ((`association_state`.`inbox_id` = ?) AND (`association_state`.`sequence_id` = ?))) OR ((`association_state`.`inbox_id` = ?) AND (`association_state`.`sequence_id` = ?))) OR ((`association_state`.`inbox_id` = ?) AND (`association_state`.`sequence_id` = ?))) OR ((`association_state`.`inbox_id` = ?) AND (`association_state`.`sequence_id` = ?))) OR ((`association_state`.`inbox_id` = ?) AND (`association_state`.`sequence_id` = ?))) OR ((`association_state`.`inbox_id` = ?) AND (`association_state`.`sequence_id` = ?))) OR ((`association_state`.`inbox_id` = ?) AND (`association_state`.`sequence_id` = ?))) OR ((`association_state`.`inbox_id` = ?) AND (`association_state`.`sequence_id` = ?))) OR ((`association_state`.`inbox_id` = ?) AND (`association_state`.`sequence_id` = ?))) OR ((`association_state`.`inbox_id` = ?) AND (`association_state`.`sequence_id` = ?))) OR ((`association_state`.`inbox_id` = ?) AND (`association_state`.`sequence_id` = ?))) OR ((`association_state`.`inbox_id` = ?) AND (`association_state`.`sequence_id` = ?))) OR ((`association_state`.`inbox_id` = ?) AND (`association_state`.`sequence_id` = ?))) OR ((`association_state`.`inbox_id` = ?) AND (`association_state`.`sequence_id` = ?))) OR ((`association_state`.`inbox_id` = ?) AND (`association_state`.`sequence_id` = ?))) OR ((`association_state`.`inbox_id` = ?) AND (`association_state`.`sequence_id` = ?))) OR ((`association_state`.`inbox_id` = ?) AND (`association_state`.`sequence_id` = ?))) OR ((`association_state`.`inbox_id` = ?) AND (`association_state`.`sequence_id` = ?))) OR ((`association_state`.`inbox_id` = ?) AND (`association_state`.`sequence_id` = ?))) OR ((`association_state`.`inbox_id` = ?) AND (`association_state`.`sequence_id` = ?))) OR ((`association_state`.`inbox_id` = ?) AND (`association_state`.`sequence_id` = ?))) OR ((`association_state`.`inbox_id` = ?) AND (`association_state`.`sequence_id` = ?))) OR ((`association_state`.`inbox_id` = ?) AND (`association_state`.`sequence_id` = ?))) OR ((`association_state`.`inbox_id` = ?) AND (`association_state`.`sequence_id` = ?))) OR ((`association_state`.`inbox_id` = ?) AND (`association_state`.`sequence_id` = ?))) OR ((`association_state`.`inbox_id` = ?) AND (`association_state`.`sequence_id` = ?))) OR ((`association_state`.`inbox_id` = ?) AND (`association_state`.`sequence_id` = ?))) OR ((`association_state`.`inbox_id` = ?) AND (`association_state`.`sequence_id` = ?))) OR ((`association_state`.`inbox_id` = ?) AND (`association_state`.`sequence_id` = ?))) OR ((`association_state`.`inbox_id` = ?) AND (`association_state`.`sequence_id` = ?))) OR ((`association_state`.`inbox_id` = ?) AND (`association_state`.`sequence_id` = ?))) OR ((`association_state`.`inbox_id` = ?) AND (`association_state`.`sequence_id` = ?))) OR ((`association_state`.`inbox_id` = ?) AND (`association_state`.`sequence_id` = ?))) OR ((`association_state`.`inbox_id` = ?) AND (`association_state`.`sequence_id` = ?))) OR ((`association_state`.`inbox_id` = ?) AND (`association_state`.`sequence_id` = ?))) OR ((`association_state`.`inbox_id` = ?) AND (`association_state`.`sequence_id` = ?))) OR ((`association_state`.`inbox_id` = ?) AND (`association_state`.`sequence_id` = ?))) OR ((`association_state`.`inbox_id` = ?) AND (`association_state`.`sequence_id` = ?))) OR ((`association_state`.`inbox_id` = ?) AND (`association_state`.`sequence_id` = ?))) OR ((`association_state`.`inbox_id` = ?) AND (`association_state`.`sequence_id` = ?))) OR ((`association_state`.`inbox_id` = ?) AND (`association_state`.`sequence_id` = ?))) OR ((`association_state`.`inbox_id` = ?) AND (`association_state`.`sequence_id` = ?))) OR ((`association_state`.`inbox_id` = ?) AND (`association_state`.`sequence_id` = ?))) OR ((`association_state`.`inbox_id` = ?) AND (`association_state`.`sequence_id` = ?))) OR ((`association_state`.`inbox_id` = ?) AND (`association_state`.`sequence_id` = ?))) OR ((`association_state`.`inbox_id` = ?) AND (`association_state`.`sequence_id` = ?))) OR ((`association_state`.`inbox_id` = ?) AND (`association_state`.`sequence_id` = ?))) OR ((`association_state`.`inbox_id` = ?) AND (`association_state`.`sequence_id` = ?))) OR ((`association_state`.`inbox_id` = ?) AND (`association_state`.`sequence_id` = ?))) -- binds: ["77f9bc80b18a8949026bc154739a844e6f7e906c4600dcd705ad4bbab1d341fd", 4990, "899826cc5090cfbef46525a85dd3af8c2fa19e91ba430c95e67afffafb51e7e8", 4962, "91cc6e354c52a9ae0bb90ea1a4e9cc67287cfdd281e3c3d3b27aebe393083846", 5078, "2b39f6a3bbd088bad234f3edeb9eb0914538490b0024325f3d48c6b786a540cc", 5001, "175d6d43fb7bd354e3c6fd1da0f163d02323cecc7d2f91e5e0c5c088c60aa462", 4917, "b71c4a469e7c60f7a530e63c0fb4ba1c840e004e4f0e45b136f7f205dc6bcbe0", 4965, "b1006b2445c961377733901af2789904555c15ea0ef6956013b9bcb6c92480fb", 4976, "d13a17d0d29f5df5e598639f9a16e903aa66e42287b99574a14ab705ebc041a3", 4913, "dce409f1b76aaba26f6a6915210bf000e76a6775cb546e834bdb4ab67d4329b4", 4954, "caf8641095abc419de1658ffa2782d28131df11698315601affb604634062609", 4947, "40cbdc3959618ba5b14232b4e4a82a1c95baff0f3e246044285ce619b10826f0", 4932, "b77f50264df534787a5079e5f1578086d33a9b8a1d84f139b9fc66dcc2352d61", 4983, "18db5cfe96ca9f64a5bc90386fcf5a56328cdfb7bbc5f66f94bd22821cd72e5f", 4960, "be9a93b8fb0539f206e86780325161ffecf57291fbfe554b265f23bbaf052943", 4969, "2fdb1b3ed2304aa629727efc41fd8124870b0d81ce27aa0a25eb5ebea882bff1", 4970, "56e24dffdfec389b24dc145bf0b2b4246255acb1b4dd636c67c81a05b1872ad9", 4980, "a5e5e5635f260210c431890a4849ddf9226622539216ad97f70b372a47fdbf6c", 4937, "1f18adb2ca35b414c78b7c7e0729fb8b6968355d1fbbb9eb98cc0f7bc3237f6f", 4950, "53bb254270b732e56e688a32f1c54ec5fde2324d7f332f19d6c94cd4fd991b3b", 4992, "d54d901455f88ff42b785ac04c1ba262294d974402233a13236f9cf286a49688", 4997, "78cc6a7575454ce25c2aa72bb4379fd7489df4e847876f9f3d348768adc7d3cd", 4953, "f08920ffaf45707527b03732f07ef434d9498631cebe47747fc0a67641916f3a", 4987, "befd730252cf3c56a1b261f16404dfc9e3c86a9664ab18e175013080867c4f55", 4935, "21cdc78ce5f0674a686bd295e293f15168f123512dbc98150b7921e78f98044b", 4930, "b3951818f8cf6706ade9677bc2f30df4c62841bcb7e75f6918134e48e5bc0f25", 4915, "81f30062d879774d3b7cab0f5626e8db3d4839811542c21c37169b23548f5f06", 4905, "0a4819822c5c3cd3f68f8a20d023210d3cda60d17c4182f21d5bcb394ca6e8b7", 4907, "ec191029d20d58f1586faf3487a5dbe951f410a6968383046a20a08e221ef8da", 4961, "18b5a438519404e50ab7e204506adb6388b7471cf7c2be8870dc41a964d2b15b", 4955, "e6d7ea5ec224e0fd45c1f504ee00e8ecc40a57194673553b46658bbd8224ee65", 4952, "4c6ef21c999c48a22a9121cb152231be2056ae758b0b4c3dd710e6935ae46f48", 4994, "7ddc23b67cc3c7397aefe7b536d76507bc580d710fbcd26d994c6c0aefabbb2c", 4971, "48250dde7567ade46398204449dcb6430393a7be2628869e0fa29625ebba13d0", 4989, "17e7988f3cbc71b651c21fc63579dd5721370d4163943ee84b7aa804d3a89a22", 4979, "41e6c1d5fb72bec6b16543a86dfd43659b57455a7faa820641297942b3543942", 4890, "e96533ba7799bdcc3ea2b0c01b25366c639da4c2fcd1cfed2d465b4bfac4aa9c", 4999, "2834e575190032500c8dc239c8c4285a1baef3cf4609cb0d9da825318a45a37c", 4931, "44e7e75ee35dca33b4c284518979b12775e2b54844348f4b033613da44ce9df8", 4981, "02d8fb55146fa5e87bdc38546545a7d756e515f590a14c6f0a878183534ab6c6", 4998, "c707ef38cba1ddf157c409ad1b14f93905732fbcfbb8a52fd0b3fd3ce832e255", 4919, "09c045d2fa08f87e7bce491baa10a4d7446f237decf9ff4c356ea8ca6237633d", 4948, "d07b6d46ae717ae673bc03cc19267267e88813008192af9830f50e44b403d3d9", 4972, "4df8e58af75936d71f0ea87f2e980048752d64e48513836eee0e7a31c0cb464d", 4929, "69f0c7e83258185fa507472c56a227e787848156d5ee019335a5d6e70343573b", 4973, "19de14de33eef620dce89144cbe3477d1330f7b8536fe54a17183aed05f87796", 4978, "de55597e0e3c06fcaa806d6e0e8e12934e8f9a22d150798d70744bb774981c03", 4988, "f92ea7ab6015dbc9a504c72a2d76cb1d90182c31b76e512f555a8b6507d2dbf0", 4922, "ddc4a0fb511fb78eacbfa117dc35f0c5b18f66cdda65cecbfc1852187701763f", 4909, "5ab29a03aa3dbe9270b674f82be676644ed91e93c7c55811aa5e3ebf575fdae0", 4996, "af58ab11c1643d2bdd3426a866c8477d14f6449faee6c90fd1d6ed6940686d9e", 4982, "3c543b3fa6e7aba3ef6af046740491fcb61661529dce667621edc80c13af3b3b", 4908, "0c05e80c3e1ac8e7ab7a25e3aaad873e7a76951babbf5e51f5ede84c7c95dcca", 4880, "8de6b6dd24898fafa419d4b2b47a665c2fb90efebd642b32a1edd8484e6c20fb", 4886, "d4120e6a5ba1c6e7b20bef28d27511e0c54df35b1fe8d550baef49eea3a4f710", 4933, "703e296429d5fa64e0b4e374fe6ab170045cba7c9a2e150021f02e5e6c15339e", 4899, "f22538fc062cf53f08dafa7292b407f6b40ad5121efcb9aff1e1a09d558119ef", 4967, "de88e771922fac5a734eeb151d0a2117a520f85951cb35d879ac205279a1b021", 4945, "32d8c4d11fc7e51ef0975582d847f8ec133c5dfaaab36f6a69e2b034b273474c", 4968, "85e211882d8d12b18d2647fef304f65b811647096da61be50969682306656bf0", 4892, "c887dcfe1a215be00e20bb433e11b30f075af7d5124b52c64d9cb7f8c1b0ef07", 4949, "002edc426de0c90172a24d6b9e2c3ed3f46f79712b40f1de3f59592f1ea296e3", 4977, "7bbe143c28a5b1772e37897fde51d48b268f13473d0819d453809420154a7092", 4974, "e1e79e41ab17da3aaa7f9fbff10eaa50c68148339c18092dc053fc9a1413172f", 4957, "dc1f68bdb480c5b21471113101017a4881f1481be5be94f1a293c77cb6559391", 4956, "64042a3446c9e5cf1654f6b9834f8f951efd9fc8dc3e34a3d40e288039e0462b", 4898, "394669418251e58fda057a5bb7a75b4b718680a373216894990e5e026fc722f6", 4900, "3b0e939ca09922d72616ffd5078c0d8f6b0ca88b979b3e6dc86fdd31230f3f12", 4940, "d85cea3dc8a269492991f8ea89d5672ab10c6a5e62b878e98de9b954955592f1", 4928, "b2594cba59413fb8d768102f3bc665e5b22b2479e6c204cd55ca8124934e2b48", 4995, "f9c2e4d25d172df02bad537800cbee77cb3cb364a0d5fba9d802bc2de913e13f", 4975, "96e3b26d71187d81b25f2f1f6d556bd37f510fd72f15fcdddc53d416aab0aa48", 4904, "3bd53e298d9c32eec3d75a20d16cf81f95b1dacc3c144450a4d89ffa0ad69d1f", 4912, "7a4694a46a316214ecf9b7064ce9620ba2c21ea867a38d56d81e27418b11fcdd", 4918, "fd7118c530c24deb60155fe7fea9e98581cf80f8acf4070038317973a0255361", 4958, "01bb583af52d2859b571b03c2c4c7cb1b5ff482731041a3e63e00bb8338985a7", 4951, "1666e9ad28b12e2ab5dd802fc50e6130213450c36875761f5f076ccd8a3028c6", 4941, "ef1ec84eeae3b83a6ffc14c02914016e3d427a895261471553fb216eb45b5951", 4944, "711a4d82617db3628f323d55b5341adb8ce4e667af19c48cf43a4ccf9b63b710", 4891, "db14ba2b063e0d44ade96dcfba098a8eb941edab39bca3798f88686bc23601da", 4897, "31cba4826043b41c81895f6122a73ad46719fb2f0e4f2fc14115a615d5844a31", 5000, "49639d7fbaca0b348b6fa2a6cac500f562769df4e4ee114d6f8c94f3696027d2", 4985, "347211d15d39b115ddc6c7c48c266409770e10b2599a774a050c250f1be4163a", 4921, "3ecf18baac836b14c7dd13220bfe5aff2d317cf7c7446d501959c9173898edfa", 4993, "172581508e3ced83a558b9e3b3304d05cff9426f752b0b43046a9ee8912ca8e1", 4963, "ea5d9009e8f89e4663ae85fd6c25c074294018d6fe4a510bb018a43383123a8e", 4946, "038dff1cb7b5063cb05dec34a75bb8cc1f825a71d54c7368148e1fed14b46980", 4943, "86631487dd7f9d1fd000b11c4a29cf3c2f166c281cfaf76c601b63b7bc4979b6", 4885, "d7f874c626000a4258d862120253f4b89fa057b2212f26c2147c747ac420b3c7", 4986, "db4958fdf1c5e925ac6a27a5ab61dafe44af266364de7a8a0a5610b4bc146917", 4966, "3fd1a31e7839ab3041d3caf3eceb4fc9e8f6da0847a2912630f89c3439f78ef2", 4894, "b50958ec25a1f9a08c6d9df4828dcefcac38cbfbeaffa616a93576c71d52e8b6", 4936, "d4c760c52d71ccbce24566a5a7a6ea34bb3e7f9bef8e3a7330ddecbb2aab8c4b", 4991, "9fac219e504225526046dfc6f12cdd84068e5fccb335fe7a52c0c0e1622d67ac", 4964, "518c71d24be99c306c2e34f16da6652f3da59d3451e55ca76040f3e1c16863ac", 4934, "1ec98ecd9cd46970a3a9780f356ff4111998a3ac918995ea38339a18c2062801", 4916, "d7d7486d437d8fd418efe75e1a09639deaa5467b3f3b946f27cdd1158d351c19", 4911, "b4013dd12aed5a8e673b6fb1f7ceb81c959c80d2a45ffb077f24811c2dd3c646", 4939, "f0e5176653eca15604267734753368d1bcfdc53011fb42d5dcd67517106907e1", 4984, "459dfdf4209139347c757dd8f82ee638515d9076f6b3c123f92c95157150ccca", 4914, "f14756fd7b3bbc15127386afc8dbdef16e10fec38ceb41941302a9a1cc5fd7da", 4959, "af7502496584dbae840ff2d84814c6a3f6b190b1a1ab1e3c8a8fe70fc3a0d1ae", 4942]
thread 'main' panicked at xmtp_mls/benches/group_limit.rs:215:67:
called `Result::unwrap()` on an `Err` value: Storage(DieselResult(DatabaseError(Unknown, "parser stack overflow")))
note: run with `RUST_BACKTRACE=1` environment variable to display a backtrace

The function in question is EncryptedStore::batch_read_from_cache. I think we're reaching the maximum depth for subqueries, so we have to re-write this query to avoid that

Function for convenience:

 pub fn batch_read_from_cache(
        conn: &DbConnection,
        identifiers: &Vec<(InboxId, i64)>,
    ) -> Result<Vec<AssociationState>, StorageError> {
        // If no identifier provided, return empty hash map
        if identifiers.is_empty() {
            return Ok(vec![]);
        }
        let mut query = dsl::association_state.into_boxed();
        for (inbox_id, sequence_id) in identifiers {
            query = query.or_filter(
                dsl::inbox_id
                    .eq(inbox_id)
                    .and(dsl::sequence_id.eq(sequence_id)),
            );
        }
        let sql = diesel::debug_query::<diesel::sqlite::Sqlite, _>(&query);
        println!("Failing SQL {}", sql);
        let association_states =
            conn.raw_query(|query_conn| query.load::<StoredAssociationState>(query_conn))?;

        association_states
            .into_iter()
            .map(|stored_association_state| stored_association_state.try_into())
            .collect::<Result<Vec<AssociationState>, DeserializationError>>()
            .map_err(|err| StorageError::Deserialization(err.to_string()))
    }

Expected behavior

we load association state for members which already exist

Steps to reproduce the bug

create group with 100 members
try to add 5 members to it

xmtp / libxmtp

Bug: SQLite stack overflows when adding members to group that already has members #810

Describe the bug

Expected behavior

Steps to reproduce the bug