Incomplete nested O2M results since 9.6.0

[flayks]

Preflight Checklist

• I have completed all Troubleshooting Steps.
• I'm on the latest version of Directus.
• There's no other issue that already describes my problem.

Describe the Bug

Did anything breaking/major changed with 9.6.0?
I suddenly cannot read all the items of a nested O2M when it was all fine with 9.5.x.

This query sometimes returns null on the image field, but not always (all locations have images items):

query {
  location(filter: { status: { _eq: "published" } }) {
    id
    name
    photos(sort: "-date_created", limit: 2) {
      image { id, title }
    }
  }
}

{
  "data": {
    "location": [
      {
        "id": "8",
        "name": "Bordeaux",
        "photos": [
          {
            "image": {
              "id": "451c1f12-c58a-4405-9fe0-928e2953c90e",
              "title": "Rue Paul Painlevé 7"
            }
          },
          {
            "image": null
          }
        ]
      },
      {
        "id": "7",
        "name": "Toulouse",
        "photos": [
          {
            "image": null
          },
...

@rijkvanzanten When you wrote on the Discord "[…] and an upgrade nested o2m reading engine that's less heavy on the database CPU", is there anything I missed to upgrade or something?

Seems a bit odd as I downgraded to 9.5.2 and everything works as expected.

To Reproduce

Nothing specific, just make a query of a nested O2M relation field (here, a photos O2M field on a location collection)

Errors Shown

No response

What version of Directus are you using?

9.6.0

What version of Node.js are you using?

17.6.0

What database are you using?

MySQL

What browser are you using?

Brave

What operating system are you using?

macOS Monterey (12)

How are you deploying Directus?

locally

[azrikahar]

May be related to #12011 based on the description here.

[rijkvanzanten]

Did anything breaking/major changed with 9.6.0?

Not on purpose at least

[arladmin]

Did anything breaking/major changed with 9.6.0?

Not on purpose at least

How to resolve this issue?

Really unable to move ahead with a project because there's no working ability to export all data, as required. Neither in v9.5.2 nor in v9.6.0

[rijkvanzanten]

How to resolve this issue?

Someone will have to dive into the code, check if #12011 fixes it, otherwise extract where the nesting goes wrong, and fix it.

Really unable to move ahead with a project because there's no working ability to export all data, as required

What do you mean? (Also that sounds like it could be a different issue / discussion, but i might be mistaken)

[arladmin]

@rijkvanzanten

What do you mean?

So, i need to export all data (export=json, limit=-1), but with the nested fields' data as well (fields=*.*).

But i'm unable to do this, for not only limit=-1 but also limit=10000.

Most of the records in the resultant data contain nested_field : null and not even nested_field : <id> or nested_field : <data> .

[arladmin]

Is there any workaround, until a patch is released to fix the now broken API?

[derkoenigeu]

@rijkvanzanten

What do you mean?

So, i need to export all data (export=json, limit=-1), but with the nested fields' data as well (fields=*.*).

But i'm unable to do this, because of the above mentioned issue, for not only limit=-1 but also limit=10000. The resultant data contains nested_field : null and not even nested_field : <id> or nested_field : <data> , for most of the records.

Related to this my colleagues an I dug a little bit deeper.
We have set LOG_LEVEL=trace to get the queries which are passed on to the MySQL Database and found out that the limit is set hard to 100.
This results in the error that only 100 items are getting returned.

[arladmin]

Any idea how to bypass this so that normal queries as well as queries for nested data work properly for even limit~50k ?

[derkoenigeu]

@arladmin At our current state we only could loop through the items and do seperate rqeuests.
We just went back to v9.5.2 for now and see how this issue evolves since it worked well in 9.5.2

[derkoenigeu]

My co-workers and I might have found the underlying problem.
There was a change in the way how SQL-statements were build up to 9.5.2 and now in 9.6.0.

In 9.5.2 statements were built like:

select * from (select `zbr_keywords`.`id`, `zbr_keywords`.`status`, `zbr_keywords`.`user_created`, `zbr_keywords`.`date_created`, `zbr_keywords`.`user_updated`, `zbr_keywords`.`date_updated`, `zbr_keywords`.`zbr_keyword_hash`, `zbr_keywords`.`zbr_keyword`, `zbr_keywords`.`zbr_query_param`, `zbr_keywords`.`zbr_export_add_phrase_modifier`, `zbr_keywords`.`zbr_export_add_exact_modifier`, `zbr_keywords`.`id_zbr_keyword_themes`, `zbr_keywords`.`zbr_default_audience_present`, `zbr_keywords`.`zbr_competitor_keyword` from `zbr_keywords` where `zbr_keywords`.`id` = ? limit ?) as `foo` union all select * from (select `zbr_keywords`.`id`, `zbr_keywords`.`status`, `zbr_keywords`.`user_created`, `zbr_keywords`.`date_created`, `zbr_keywords`.`user_updated`, `zbr_keywords`.`date_updated`, `zbr_keywords`.`zbr_keyword_hash`, `zbr_keywords`.`zbr_keyword`, `zbr_keywords`.`zbr_query_param`, `zbr_keywords`.`zbr_export_add_phrase_modifier`, `zbr_keywords`.`zbr_export_add_exact_modifier`, `zbr_keywords`.`id_zbr_keyword_themes`, `zbr_keywords`.`zbr_default_audience_present`, `zbr_keywords`.`zbr_competitor_keyword` from `zbr_keywords` where `zbr_keywords`.`id` = ? limit ?) as `foo` union all select * from (select `zbr_keywords`.`id`, `zbr_keywords`.`status`, `zbr_keywords`.`user_created`, `zbr_keywords`.`date_created`, `zbr_keywords`.`user_updated`, `zbr_keywords`.`date_updated`, `zbr_keywords`.`zbr_keyword_hash`, `zbr_keywords`.`zbr_keyword`, `zbr_keywords`.`zbr_query_param`, `zbr_keywords`.`zbr_export_add_phrase_modifier`, `zbr_keywords`.`zbr_export_add_exact_modifier`, `zbr_keywords`.`id_zbr_keyword_themes`, `zbr_keywords`.`zbr_default_audience_present`, `zbr_keywords`.`zbr_competitor_keyword` from `zbr_keywords` where `zbr_keywords`.`id` = ? limit ?) as `foo` union all select * from (select `zbr_keywords`.`id`,......
.......
union all select * from (select `zbr_keywords`.`id`, `zbr_keywords`.`status`, `zbr_keywords`.`user_created`, `zbr_keywords`.`date_created`, `zbr_keywords`.`user_updated`, `zbr_keywords`.`date_updated`, `zbr_keywords`.`zbr_keyword_hash`, `zbr_keywords`.`zbr_keyword`, `zbr_keywords`.`zbr_query_param`, `zbr_keywords`.`zbr_export_add_phrase_modifier`, `zbr_keywords`.`zbr_export_add_exact_modifier`, `zbr_keywords`.`id_zbr_keyword_themes`, `zbr_keywords`.`zbr_default_audience_present`, `zbr_keywords`.`zbr_competitor_keyword` from `zbr_keywords` where `zbr_keywords`.`id` = ? limit ?) as `foo` [67, 100, 70, 100, 8, 100, 21, 100, 3, 100, 66, 100, 64, 100, 4, 100, 229, 100, 113, 100, 217, 100, 114, 100, 232, 100, 234, 100, 230, 100, 233, 100, 72, 100, 309, 100, 311, 100, 312, 100, 313, 100, 336, 100, 314, 100, 339, 100, 310, 100, 337, 100, 574, 100, 235, 100, 395, 100, 576, 100, 240, 100, 32, 100, 73, 100, 577, 100, 276, 100, 239, 100, 241, 100, 287, 100, 575, 100, 283, 100, 286, 100, 306, 100, 409, 100, 585, 100, 333, 100, 514, 100, 584, 100, 89, 100, 68, 100, 88, 100, 91, 100, 90, 100, 92, 100, 211, 100, 212, 100, 213, 100, 100, 100, 214, 100, 216, 100, 325, 100, 210, 100, 219, 100, 220, 100, 260, 100, 258, 100, 262, 100, 215, 100, 261, 100, 289, 100, 263, 100, 322, 100, 323, 100, 382, 100, 328, 100, 326, 100, 259, 100, 330, 100, 329, 100, 331, 100, 383, 100, 324, 100, 346, 100, 394, 100, 386, 100, 345, 100, 387, 100, 388, 100, 349, 100, 390, 100, 350, 100, 389, 100, 347, 100, 348, 100, 354, 100, 356, 100, 355, 100, 357, 100, 370, 100, 363, 100, 351, 100, 391, 100, 362, 100, 371, 100, 392, 100, 372, 100, 373, 100, 376, 100, 374, 100, 375, 100, 525, 100, 378, 100, 380, 100, 379, 100, 381, 100, 451, 100, 397, 100, 377, 100, 400, 100, 407, 100, 399, 100, 396, 100, 408, 100, 452, 100, 453, 100, 454, 100, 522, 100, 528, 100, 523, 100, 527, 100, 532, 100, 531, 100, 642, 100, 529, 100, 533, 100, 526, 100, 534, 100, 645, 100, 643, 100, 9, 100, 10, 100, 524, 100, 158, 100, 33, 100, 44, 100, 43, 100, 644, 100, 319, 100, 116, 100, 316, 100, 317, 100, 343, 100, 320, 100, 65, 100, 321, 100, 367, 100, 315, 100, 344, 100, 318, 100, 352, 100, 181, 100, 182, 100, 359, 100, 365, 100, 360, 100, 332, 100, 257, 100, 353, 100, 361, 100, 218, 100, 364, 100, 368, 100, 384, 100, 419, 100, 385, 100, 369, 100, 231, 100, 410, 100, 402, 100, 366, 100, 403, 100, 404, 100, 405, 100, 412, 100, 393, 100, 417, 100, 411, 100, 423, 100, 421, 100, 406, 100, 426, 100, 420, 100, 466, 100, 464, 100, 422, 100, 460, 100, 462, 100, 425, 100, 428, 100, 463, 100, 431, 100, 455, 100, 434, 100, 456, 100, 457, 100, 427, 100, 459, 100, 461, 100, 465, 100, 467, 100, 482, 100, 469, 100, 458, 100, 478, 100, 486, 100, 480, 100, 481, 100, 483, 100, 484, 100, 485, 100, 479, 100, 471, 100, 477, 100, 472, 100, 474, 100, 470, 100, 468, 100, 476, 100, 530, 100, 639, 100, 535, 100, 473, 100, 604, 100, 487, 100, 605, 100, 475, 100, 606, 100, 608, 100, 488, 100, 609, 100, 603, 100, 613, 100, 611, 100, 612, 100, 607, 100, 614, 100, 616, 100, 617, 100, 490, 100, 610, 100, 618, 100, 619, 100, 489, 100, 620, 100, 621, 100, 615, 100, 622, 100, 517, 100, 623, 100, 624, 100, 627, 100, 626, 100, 491, 100, 629, 100, 630, 100, 632, 100, 633, 100, 625, 100, 636, 100, 635, 100, 628, 100, 638, 100, 631, 100, 640, 100, 647, 100, 641, 100, 634, 100, 648, 100, 637, 100, 649, 100, 650, 100, 646, 100, 508, 100, 506, 100, 507, 100, 505, 100, 504, 100, 296, 100, 536, 100, 2, 100, 1, 100, 18, 100, 6, 100, 11, 100, 13, 100, 14, 100, 15, 100, 38, 100, 19, 100, 20, 100, 28, 100, 12, 100, 29, 100, 31, 100, 16, 100, 30, 100, 17, 100, 39, 100, 40, 100, 42, 100, 34, 100, 48, 100, 45, 100, 50, 100, 35, 100, 47, 100, 61, 100, 82, 100, 49, 100, 120, 100, 46, 100, 86, 100, 87, 100, 84, 100, 56, 100, 83, 100, 62, 100, 51, 100, 52, 100, 103, 100, 81, 100, 105, 100, 115, 100, 117, 100, 118, 100, 119, 100, 130, 100, 102, 100, 121, 100, 104, 100, 124, 100, 125, 100, 126, 100, 128, 100, 127, 100, 131, 100, 132, 100, 122, 100, 133, 100, 123, 100, 134, 100, 150, 100, 135, 100, 152, 100, 151, 100, 129, 100, 154, 100, 153, 100, 157, 100, 160, 100, 164, 100, 162, 100, 169, 100, 155, 100, 168, 100, 156, 100, 174, 100, 170, 100, 159, 100, 171, 100, 172, 100, 163, 100, 173, 100, 175, 100, 167, 100, 161, 100, 180, 100, 179, 100, 190, 100, 184, 100, 186, 100, 187, 100, 188, 100, 192, 100, 194, 100, 197, 100, 183, 100, 193, 100, 195, 100, 196, 100, 198, 100, 191, 100, 176, 100, 199, 100, 224, 100, 225, 100, 204, 100, 200, 100, 205, 100, 223, 100, 189, 100, 236, 100, 243, 100, 242, 100, 237, 100, 245, 100, 238, 100, 244, 100, 247, 100, 248, 100, 226, 100, 246, 100, 250, 100, 252, 100, 251, 100, 256, 100, 253, 100, 277, 100, 280, 100, 227, 100, 249, 100, 285, 100, 281, 100, 284, 100, 342, 100, 282, 100, 334, 100, 335, 100, 279, 100, 444, 100, 341, 100, 398, 100, 413, 100, 414, 100, 415, 100, 442, 100, 340, 100, 445, 100, 446, 100, 448, 100, 447, 100, 499, 100, 254, 100, 500, 100, 501, 100, 443, 100, 278, 100, 509, 100, 255, 100, 503, 100, 515, 100, 513, 100, 512, 100, 516, 100, 502, 100, 537, 100, 553, 100, 544, 100, 545, 100, 542, 100, 546, 100, 547, 100, 543, 100, 549, 100, 539, 100, 540, 100, 538, 100, 541, 100, 551, 100, 569, 100, 552, 100, 554, 100, 555, 100, 556, 100, 557, 100, 558, 100, 548, 100, 570, 100, 559, 100, 571, 100, 572, 100, 561, 100, 560, 100, 573, 100, 582, 100, 563, 100, 562, 100, 579, 100, 580, 100, 581, 100, 583, 100, 586, 100, 578, 100, 600, 100, 588, 100, 590, 100, 589, 100, 591, 100, 592, 100, 593, 100, 594, 100, 595, 100, 587, 100, 597, 100, 599, 100, 598, 100, 567, 100, 568, 100, 566, 100, 596, 100, 651, 100, 652, 100, 653, 100, 36, 100, 185, 100, 401, 100, 85, 100, 654, 100, 655, 100, 656, 100, 657, 100, 22, 100, 23, 100, 24, 100, 25, 100, 26, 100, 27, 100, 37, 100, 41, 100, 58, 100, 107, 100, 108, 100, 109, 100, 110, 100, 111, 100, 112, 100, 136, 100, 137, 100, 138, 100, 139, 100, 140, 100, 143, 100, 144, 100, 145, 100, 146, 100, 147, 100, 148, 100, 149, 100, 165, 100, 166, 100, 177, 100, 178, 100, 203, 100, 206, 100, 207, 100, 208, 100, 209, 100, 291, 100, 292, 100, 294, 100, 295, 100, 297, 100, 299, 100, 300, 100, 302, 100, 303, 100, 304, 100, 305, 100, 307, 100, 308, 100, 338, 100, 358, 100, 418, 100, 429, 100, 430, 100, 432, 100, 433, 100, 435, 100, 436, 100, 437, 100, 494, 100, 495, 100, 496, 100, 497, 100, 498, 100, 518, 100, 519, 100, 520, 100, 521, 100, 658, 100, 659, 100, 69, 100, 424, 100, 450, 100, 288, 100, 327, 100, 438, 100, 439, 100, 565, 100, 440, 100, 441, 100, 5, 100, 7, 100, 54, 100, 55, 100, 57, 100, 416, 100, 59, 100, 60, 100, 63, 100, 71, 100, 510, 100, 74, 100, 75, 100, 76, 100, 77, 100, 78, 100, 449, 100, 79, 100, 80, 100, 99, 100, 101, 100, 106, 100, 511, 100, 141, 100, 550, 100, 142, 100, 564, 100, 221, 100, 222, 100, 228, 100, 290, 100, 293, 100, 601, 100, 298, 100, 301, 100, 602, 100, 53, 100, 201, 100, 202, 100, 93, 100, 94, 100, 95, 100, 96, 100, 97, 100, 98, 100, 264, 100, 265, 100, 266, 100, 267, 100, 268, 100, 269, 100, 270, 100, 271, 100, 272, 100, 273, 100, 274, 100, 275, 100, 660, 100, 661, 100]

in the new version 9.6.0 it is like:

select
`zbr_keywords`.`date_created`,
`zbr_keywords`.`date_updated`,
`zbr_keywords`.`id`,
`zbr_keywords`.`id_zbr_keyword_themes`,
`zbr_keywords`.`status`,
`zbr_keywords`.`user_created`,
`zbr_keywords`.`user_updated`,
`zbr_keywords`.`zbr_competitor_keyword`,
`zbr_keywords`.`zbr_default_audience_present`,
`zbr_keywords`.`zbr_export_add_exact_modifier`,
`zbr_keywords`.`zbr_export_add_phrase_modifier`,
`zbr_keywords`.`zbr_keyword`,
`zbr_keywords`.`zbr_keyword_hash`,
`zbr_keywords`.`zbr_query_param`
from `zbr_keywords`
where `zbr_keywords`.`id` in (67, 70, 8, 21, 3, 66, 64, 4, 229, 113, 217, 114, 232, 234, 230, 233, 72, 309, 311, 312, 313, 336, 314, 339, 310, 337, 574, 235, 395, 576, 240, 32, 73, 577, 276, 239, 241, 287, 575, 283, 286, 306, 409, 585, 333, 514, 584, 89, 68, 88, 91, 90, 92, 211, 212, 213, 100, 214, 216, 325, 210, 219, 220, 260, 258, 262, 215, 261, 289, 263, 322, 323, 382, 328, 326, 259, 330, 329, 331, 383, 324, 346, 394, 386, 345, 387, 388, 349, 390, 350, 389, 347, 348, 354, 356, 355, 357, 370, 363, 351, 391, 362, 371, 392, 372, 373, 376, 374, 375, 525, 378, 380, 379, 381, 451, 397, 377, 400, 407, 399, 396, 408, 452, 453, 454, 522, 528, 523, 527, 532, 531, 642, 529, 533, 526, 534, 645, 643, 9, 10, 524, 158, 33, 44, 43, 644, 319, 116, 316, 317, 343, 320, 65, 321, 367, 315, 344, 318, 352, 181, 182, 359, 365, 360, 332, 257, 353, 361, 218, 364, 368, 384, 419, 385, 369, 231, 410, 402, 366, 403, 404, 405, 412, 393, 417, 411, 423, 421, 406, 426, 420, 466, 464, 422, 460, 462, 425, 428, 463, 431, 455, 434, 456, 457, 427, 459, 461, 465, 467, 482, 469, 458, 478, 486, 480, 481, 483, 484, 485, 479, 471, 477, 472, 474, 470, 468, 476, 530, 639, 535, 473, 604, 487, 605, 475, 606, 608, 488, 609, 603, 613, 611, 612, 607, 614, 616, 617, 490, 610, 618, 619, 489, 620, 621, 615, 622, 517, 623, 624, 627, 626, 491, 629, 630, 632, 633, 625, 636, 635, 628, 638, 631, 640, 647, 641, 634, 648, 637, 649, 650, 646, 508, 506, 507, 505, 504, 296, 536, 2, 1, 18, 6, 11, 13, 14, 15, 38, 19, 20, 28, 12, 29, 31, 16, 30, 17, 39, 40, 42, 34, 48, 45, 50, 35, 47, 61, 82, 49, 120, 46, 86, 87, 84, 56, 83, 62, 51, 52, 103, 81, 105, 115, 117, 118, 119, 130, 102, 121, 104, 124, 125, 126, 128, 127, 131, 132, 122, 133, 123, 134, 150, 135, 152, 151, 129, 154, 153, 157, 160, 164, 162, 169, 155, 168, 156, 174, 170, 159, 171, 172, 163, 173, 175, 167, 161, 180, 179, 190, 184, 186, 187, 188, 192, 194, 197, 183, 193, 195, 196, 198, 191, 176, 199, 224, 225, 204, 200, 205, 223, 189, 236, 243, 242, 237, 245, 238, 244, 247, 248, 226, 246, 250, 252, 251, 256, 253, 277, 280, 227, 249, 285, 281, 284, 342, 282, 334, 335, 279, 444, 341, 398, 413, 414, 415, 442, 340, 445, 446, 448, 447, 499, 254, 500, 501, 443, 278, 509, 255, 503, 515, 513, 512, 516, 502, 537, 553, 544, 545, 542, 546, 547, 543, 549, 539, 540, 538, 541, 551, 569, 552, 554, 555, 556, 557, 558, 548, 570, 559, 571, 572, 561, 560, 573, 582, 563, 562, 579, 580, 581, 583, 586, 578, 600, 588, 590, 589, 591, 592, 593, 594, 595, 587, 597, 599, 598, 567, 568, 566, 596, 651, 652, 653, 36, 185, 401, 85, 654, 655, 656, 657, 22, 23, 24, 25, 26, 27, 37, 41, 58, 107, 108, 109, 110, 111, 112, 136, 137, 138, 139, 140, 143, 144, 145, 146, 147, 148, 149, 165, 166, 177, 178, 203, 206, 207, 208, 209, 291, 292, 294, 295, 297, 299, 300, 302, 303, 304, 305, 307, 308, 338, 358, 418, 429, 430, 432, 433, 435, 436, 437, 494, 495, 496, 497, 498, 518, 519, 520, 521, 658, 659, 69, 424, 450, 288, 327, 438, 439, 565, 440, 441, 5, 7, 54, 55, 57, 416, 59, 60, 63, 71, 510, 74, 75, 76, 77, 78, 449, 79, 80, 99, 101, 106, 511, 141, 550, 142, 564, 221, 222, 228, 290, 293, 601, 298, 301, 602, 53, 201, 202, 93, 94, 95, 96, 97, 98, 264, 265, 266, 267, 268, 269, 270, 271, 272, 273, 274, 275, 660, 661)
limit 100

As you can see in 9.5.2 there was already a limit for each id set to 100 and in the 9.6.0 statement the limit 100 is set for all. due to the change in how the statement is built.

Following that the Bug is a bit older already but did not had any influence by now.

Thanks for doing such a great work with directus :)

[rijkvanzanten]

Thanks for confirming that! I redid the relational fetching (like you mentioned) to make sure we didn't generate massive single union query commands, as that was blowing up some lower-powered database engines when fetching larger sets, but a bug must've snuck in. The The query shown is correct, except for that limit. It should be a higher batch table scan limit which is paginated, so i'll have to investigate that some more!

[aiac]

Same problem here:

  const query = gql`
    query GetDomainData($request_domain: String!, $menu_location: String!) {
      ${domain_fragment}
      guides(filter: { status: { _eq: "published" } }, limit: 30, offset: 10) {
        slug
        }
        photos(limit: 1) {
          directus_files_id {
            id
          }
        }
      }
    }
  `;

These query will return null photos object after spitting 25 items. Anyone working on this maybe?