🚀 Feature Request: D1 max expression tree depth example and discussion

[xdivby0]

Describe the solution

I upgraded my wrangler v2 to v3 and in that process I start getting Expression tree is too large (maximum depth 20) for some queries.

In the Discord, we figured out that wrangler v2 and my prod database are on the alpha backend (which explains why it's been working on prod for the whole time). But the new version of wrangler seems to have created a beta-d1 binding.

I've been asked to submit the query here so we can discuss potential use cases where a higher depth than 20 may be necessary and useful. The query is in drizzle, I have done research but haven't found a good way to extract the generated SQL:

  const teamArticles = await db.query.articles.findMany({
    where: ownerOrInTeam(articles.userId, authUser), // this is one equality check
    with: {
      pineconeReferences: true,
      outlineDrafts: { // if I do outlineDrafts: true, I am not over 20 anymore
        with: { // this seems to be the edge at which it goes over 20 seemingly
          draftPieces: true
        }
      }
    }
  });

While increasing the limit would be nice of course, since I need basic queries like the one above all the time but the two most important things right now for me:

• How can I tell wrangler v3 to use the alpha backend for the local d1 binding (cause my prod db is alpha as well)
• Is the alpha DB outdated? Do I need to worry it's going to get sunset soon?
• Can I not create a new alpha database any more (so I can still use queries like the above)?

[elithrar]

There sounds like two issues at play here:

1. you are testing in local dev vs remote (production)
2. you have an alpha database

What you are likely seeing is differences in local dev as later versions of wrangler (3.4 and later) use the new backend. You can use an older version of wrangler, but we strongly suggest you migrate your data to the new backend, which has been the default for a few months now.

While we will not forcibly stop you from using it, we aren’t supporting creating new databases on the old backend - this is unfortunately the risk of a public alpha.

We can consider increasing the expression tree depth but without knowing your queries depth (via EXPLAIN) we can’t know what will work for you.

[xdivby0]

There sounds like two issues at play here:

1. you are testing in local dev vs remote (production)

2. you have an alpha database

What you are likely seeing is differences in local dev as later versions of wrangler (3.4 and later) use the new backend. You can use an older version of wrangler, but we strongly suggest you migrate your data to the new backend, which has been the default for a few months now.

Yes, I am aware why this happens, like what is causing it. I just don't particularly like it and I can't really do anything against it right now.

While we will not forcibly stop you from using it, we aren’t supporting creating new databases on the old backend - this is unfortunately the risk of a public alpha.

Fair enough, I'll keep using it for as long until the depth tree issue is resolved.

We can consider increasing the expression tree depth but without knowing your queries depth (via EXPLAIN) we can’t know what will work for you.

I am not exactly sure how the SQL query is constructed and couldn't find any way to extract the SQL query. I'll forward this to issue to the drizzle team as well so they may be able to give us raw queries or a way to get raw queries.

[xdivby0]

I've got the query. For reference I'll give you two versions, the original one which is at above 20 depth and one where I leave out the nested with which will reduce to below 20:

(The queries themselves might have lowercase-uppercase mistakes in the field names because I used an online autoformatter. The EXPLAIN QUERY PLAN and EXPLAIN were done with the original everything-in-one-line query.)

Original query:

 SELECT "id",
       "createdat",
       "googlecountry",
       "title",
       "searchquery",
       "snippeturl",
       "snippetcontent",
       "videosranking",
       "relatedsearches",
       "relatedquestions",
       "userid",
       (SELECT COALESCE(Json_group_array(Json_array("id", "articleid",
                                         "pineconeids")),
                       Json_array()) AS "data"
        FROM   "pineconereferences" "articles_pineconeReferences"
        WHERE  "articles_pineconeReferences"."articleid" = "articles"."id") AS
       "pineconeReferences",
       (SELECT COALESCE(Json_group_array(Json_array("id", "createdat",
                                         "articleid",
                                                          (SELECT COALESCE(
                                                          Json_group_array(
                        Json_array
                        ("id", "createdat", "headinglevel",
                        "headingtext"
                        ,
                        "position"
                        ,
                        "selected"
                        , "content", "contentbrief",
                        "outlinedraftid")), Json_array()) AS "data"
                        FROM   "draftpieces"
                        "articles_outlineDrafts_draftPieces"
                        WHERE
                        "articles_outlineDrafts_draftPieces"."outlinedraftid" =
                        "articles_outlineDrafts"."id"))), Json_array()) AS
               "data"
        FROM   "outlinedrafts" "articles_outlineDrafts"
        WHERE  "articles_outlineDrafts"."articleid" = "articles"."id")      AS
       "outlineDrafts"
FROM   "articles"
WHERE  "articles"."userid" = ? -- params: [1]

Working query:

 SELECT "id",
       "createdat",
       "googlecountry",
       "title",
       "searchquery",
       "snippeturl",
       "snippetcontent",
       "videosranking",
       "relatedsearches",
       "relatedquestions",
       "userid",
       (SELECT COALESCE(Json_group_array(Json_array("id", "articleid",
                                         "pineconeids")),
                       Json_array()) AS "data"
        FROM   "pineconereferences" "articles_pineconeReferences"
        WHERE  "articles_pineconeReferences"."articleid" = "articles"."id") AS
       "pineconeReferences",
       (SELECT COALESCE(Json_group_array(Json_array("id", "createdat",
                                         "articleid")),
                       Json_array()) AS "data"
        FROM   "outlinedrafts" "articles_outlineDrafts"
        WHERE  "articles_outlineDrafts"."articleid" = "articles"."id")      AS
       "outlineDrafts"
FROM   "articles"
WHERE  "articles"."userid" = ? -- params: [1]

Original query EXPLAIN QUERY PLAN:

QUERY PLAN
|--SCAN articles
|--CORRELATED SCALAR SUBQUERY 1
|  `--SCAN articles_pineconeReferences
`--CORRELATED SCALAR SUBQUERY 3
   |--SCAN articles_outlineDrafts
   `--CORRELATED SCALAR SUBQUERY 2
      `--SCAN articles_outlineDrafts_draftPieces

Working query EXPLAIN QUERY PLAN:

|--SCAN articles
|--CORRELATED SCALAR SUBQUERY 1
|  `--SCAN articles_pineconeReferences
`--CORRELATED SCALAR SUBQUERY 2
   `--SCAN articles_outlineDrafts

Original query EXPLAIN:

addr  opcode         p1    p2    p3    p4             p5  comment
----  -------------  ----  ----  ----  -------------  --  -------------
0     Init           0     97    0                    0   Start at 97
1     OpenRead       0     5     0     11             0   root=5 iDb=0; articles
2     Rewind         0     96    0                    0
3       Column         0     10    1                    0   r[1]= cursor 0 column 10
4       Ne             2     95    1     BINARY-8       84  if r[1]!=r[2] goto 95
5       Rowid          0     3     0                    0   r[3]=articles.rowid
6       Column         0     1     4                    0   r[4]=articles.createdAt
7       Column         0     2     5                    0   r[5]= cursor 0 column 2
8       Column         0     3     6                    0   r[6]= cursor 0 column 3
9       Column         0     4     7                    0   r[7]= cursor 0 column 4
10      Column         0     5     8                    0   r[8]= cursor 0 column 5
11      Column         0     6     9                    0   r[9]= cursor 0 column 6
12      Column         0     7     10                   0   r[10]= cursor 0 column 7
13      Column         0     8     11                   0   r[11]= cursor 0 column 8
14      Column         0     9     12                   0   r[12]= cursor 0 column 9
15      Column         0     10    13                   0   r[13]= cursor 0 column 10
16      BeginSubrtn    0     16    0                    0   r[16]=NULL
17        Null           0     17    17                   0   r[17..17]=NULL; Init subquery result
18        Integer        1     18    0                    0   r[18]=1; LIMIT counter
19        Null           0     19    22                   0   r[19..22]=NULL
20        OpenRead       1     21    0     3              0   root=21 iDb=0; pineconeReferences
21        Rewind         1     31    0                    0
22          Column         1     1     1                    0   r[1]= cursor 1 column 1
23          Rowid          0     23    0                    0   r[23]=articles.rowid
24          Ne             23    30    1     BINARY-8       83  if r[1]!=r[23] goto 30
25          Rowid          1     24    0                    0   r[24]=pineconeReferences.rowid
26          Column         1     1     25                   0   r[25]= cursor 1 column 1
27          Column         1     2     26                   0   r[26]= cursor 1 column 2
28          Function       0     24    23    json_array(-1)  0   r[23]=func(r[24..26])
29          AggStep        0     23    22    json_group_array(1)  1   accum=r[22] step(r[23])
30        Next           1     22    0                    1
31        AggFinal       22    1     0     json_group_array(1)  0   accum=r[22] N=1
32        SCopy          22    17    0                    0   r[17]=r[22]
33        NotNull        17    37    0                    0   if r[17]!=NULL goto 37
34        Once           0     36    0                    0
35        Function       0     0     27    json_array(-1)  0   r[27]=func()
36        SCopy          27    17    0                    0   r[17]=r[27]
37        DecrJumpZero   18    38    0                    0   if (--r[18])==0 goto 38
38      Return         16    17    1                    0
39      Copy           17    14    0                    0   r[14]=r[17]
40      BeginSubrtn    0     28    0                    0   r[28]=NULL
41        Null           0     29    29                   0   r[29..29]=NULL; Init subquery result
42        Integer        1     30    0                    0   r[30]=1; LIMIT counter
43        Null           0     31    34                   0   r[31..34]=NULL
44        OpenRead       2     7     0     3              0   root=7 iDb=0; outlineDrafts
45        Rewind         2     85    0                    0
46          Column         2     2     35                   0   r[35]= cursor 2 column 2
47          Rowid          0     36    0                    0   r[36]=articles.rowid
48          Ne             36    84    35    BINARY-8       83  if r[35]!=r[36] goto 84
49          Rowid          2     37    0                    0   r[37]=outlineDrafts.rowid
50          Column         2     1     38                   0   r[38]=outlineDrafts.createdAt
51          Column         2     2     39                   0   r[39]= cursor 2 column 2
52          BeginSubrtn    0     41    0                    0   r[41]=NULL
53            Null           0     42    42                   0   r[42..42]=NULL; Init subquery result
54            Integer        1     43    0                    0   r[43]=1; LIMIT counter
55            Null           0     44    53                   0   r[44..53]=NULL
56            OpenRead       3     6     0     9              0   root=6 iDb=0; draftPieces
57            Rewind         3     73    0                    0
58              Column         3     6     35                   0   r[35]= cursor 3 column 6
59              Rowid          2     54    0                    0   r[54]=outlineDrafts.rowid
60              Ne             54    72    35    BINARY-8       83  if r[35]!=r[54] goto 72
61              Rowid          3     55    0                    0   r[55]=draftPieces.rowid
62              Column         3     1     56                   0   r[56]=draftPieces.createdAt
63              Column         3     2     57    2              0   r[57]=draftPieces.headingLevel
64              Column         3     3     58    Heading 2      0   r[58]=draftPieces.headingText
65              Column         3     4     59                   0   r[59]= cursor 3 column 4
66              Column         3     5     60    1              0   r[60]=draftPieces.selected
67              Column         3     7     61                   0   r[61]= cursor 3 column 7
68              Column         3     8     62                   0   r[62]= cursor 3 column 8
69              Column         3     6     63                   0   r[63]= cursor 3 column 6
70              Function       0     55    54    json_array(-1)  0   r[54]=func(r[55..63])
71              AggStep        0     54    53    json_group_array(1)  1   accum=r[53] step(r[54])
72            Next           3     58    0                    1
73            AggFinal       53    1     0     json_group_array(1)  0   accum=r[53] N=1
74            SCopy          53    42    0                    0   r[42]=r[53]
75            NotNull        42    79    0                    0   if r[42]!=NULL goto 79
76            Once           0     78    0                    0
77            Function       0     0     64    json_array(-1)  0   r[64]=func()
78            SCopy          64    42    0                    0   r[42]=r[64]
79            DecrJumpZero   43    80    0                    0   if (--r[43])==0 goto 80
80          Return         41    53    1                    0
81          Copy           42    40    0                    0   r[40]=r[42]
82          Function       0     37    36    json_array(-1)  0   r[36]=func(r[37..40])
83          AggStep        0     36    34    json_group_array(1)  1   accum=r[34] step(r[36])
84        Next           2     46    0                    1
85        AggFinal       34    1     0     json_group_array(1)  0   accum=r[34] N=1
86        Copy           34    29    0                    0   r[29]=r[34]
87        NotNull        29    91    0                    0   if r[29]!=NULL goto 91
88        Once           0     90    0                    0
89        Function       0     0     65    json_array(-1)  0   r[65]=func()
90        SCopy          65    29    0                    0   r[29]=r[65]
91        DecrJumpZero   30    92    0                    0   if (--r[30])==0 goto 92
92      Return         28    41    1                    0
93      Copy           29    15    0                    0   r[15]=r[29]
94      ResultRow      3     13    0                    0   output=r[3..15]
95    Next           0     3     0                    1
96    Halt           0     0     0                    0
97    Transaction    0     0     35    0              1   usesStmtJournal=0
98    Variable       1     2     0                    0   r[2]=parameter(1,)
99    Goto           0     1     0                    0

Working query EXPLAIN:

addr  opcode         p1    p2    p3    p4             p5  comment
----  -------------  ----  ----  ----  -------------  --  -------------
0     Init           0     67    0                    0   Start at 67
1     OpenRead       0     5     0     11             0   root=5 iDb=0; articles
2     Rewind         0     66    0                    0
3       Column         0     10    1                    0   r[1]= cursor 0 column 10
4       Ne             2     65    1     BINARY-8       84  if r[1]!=r[2] goto 65
5       Rowid          0     3     0                    0   r[3]=articles.rowid
6       Column         0     1     4                    0   r[4]=articles.createdAt
7       Column         0     2     5                    0   r[5]= cursor 0 column 2
8       Column         0     3     6                    0   r[6]= cursor 0 column 3
9       Column         0     4     7                    0   r[7]= cursor 0 column 4
10      Column         0     5     8                    0   r[8]= cursor 0 column 5
11      Column         0     6     9                    0   r[9]= cursor 0 column 6
12      Column         0     7     10                   0   r[10]= cursor 0 column 7
13      Column         0     8     11                   0   r[11]= cursor 0 column 8
14      Column         0     9     12                   0   r[12]= cursor 0 column 9
15      Column         0     10    13                   0   r[13]= cursor 0 column 10
16      BeginSubrtn    0     16    0                    0   r[16]=NULL
17        Null           0     17    17                   0   r[17..17]=NULL; Init subquery result
18        Integer        1     18    0                    0   r[18]=1; LIMIT counter
19        Null           0     19    22                   0   r[19..22]=NULL
20        OpenRead       1     21    0     3              0   root=21 iDb=0; pineconeReferences
21        Rewind         1     31    0                    0
22          Column         1     1     1                    0   r[1]= cursor 1 column 1
23          Rowid          0     23    0                    0   r[23]=articles.rowid
24          Ne             23    30    1     BINARY-8       83  if r[1]!=r[23] goto 30
25          Rowid          1     24    0                    0   r[24]=pineconeReferences.rowid
26          Column         1     1     25                   0   r[25]= cursor 1 column 1
27          Column         1     2     26                   0   r[26]= cursor 1 column 2
28          Function       0     24    23    json_array(-1)  0   r[23]=func(r[24..26])
29          AggStep        0     23    22    json_group_array(1)  1   accum=r[22] step(r[23])
30        Next           1     22    0                    1
31        AggFinal       22    1     0     json_group_array(1)  0   accum=r[22] N=1
32        SCopy          22    17    0                    0   r[17]=r[22]
33        NotNull        17    37    0                    0   if r[17]!=NULL goto 37
34        Once           0     36    0                    0
35        Function       0     0     27    json_array(-1)  0   r[27]=func()
36        SCopy          27    17    0                    0   r[17]=r[27]
37        DecrJumpZero   18    38    0                    0   if (--r[18])==0 goto 38
38      Return         16    17    1                    0
39      Copy           17    14    0                    0   r[14]=r[17]
40      BeginSubrtn    0     28    0                    0   r[28]=NULL
41        Null           0     29    29                   0   r[29..29]=NULL; Init subquery result
42        Integer        1     30    0                    0   r[30]=1; LIMIT counter
43        Null           0     31    34                   0   r[31..34]=NULL
44        OpenRead       2     7     0     3              0   root=7 iDb=0; outlineDrafts
45        Rewind         2     55    0                    0
46          Column         2     2     35                   0   r[35]= cursor 2 column 2
47          Rowid          0     36    0                    0   r[36]=articles.rowid
48          Ne             36    54    35    BINARY-8       83  if r[35]!=r[36] goto 54
49          Rowid          2     37    0                    0   r[37]=outlineDrafts.rowid
50          Column         2     1     38                   0   r[38]=outlineDrafts.createdAt
51          Column         2     2     39                   0   r[39]= cursor 2 column 2
52          Function       0     37    36    json_array(-1)  0   r[36]=func(r[37..39])
53          AggStep        0     36    34    json_group_array(1)  1   accum=r[34] step(r[36])
54        Next           2     46    0                    1
55        AggFinal       34    1     0     json_group_array(1)  0   accum=r[34] N=1
56        SCopy          34    29    0                    0   r[29]=r[34]
57        NotNull        29    61    0                    0   if r[29]!=NULL goto 61
58        Once           0     60    0                    0
59        Function       0     0     40    json_array(-1)  0   r[40]=func()
60        SCopy          40    29    0                    0   r[29]=r[40]
61        DecrJumpZero   30    62    0                    0   if (--r[30])==0 goto 62
62      Return         28    41    1                    0
63      Copy           29    15    0                    0   r[15]=r[29]
64      ResultRow      3     13    0                    0   output=r[3..15]
65    Next           0     3     0                    1
66    Halt           0     0     0                    0
67    Transaction    0     0     35    0              1   usesStmtJournal=0
68    Variable       1     2     0                    0   r[2]=parameter(1,)
69    Goto           0     1     0                    0

[subhendupsingh]

Getting the same error for almost all the queries with multiple nested tables using Drizzle ORM. Here is the example of a query that's failing

select
	"id",
	"title",
	"description",
	"slug",
	"sku",
	"stock",
	"is_always_in_stock",
	"is_visible",
	"product_unit_value",
	"product_unit",
	"mrp",
	"sale_price",
	"created_at",
	"category_id",
	"organization_id",
	"tax_id",
	"status",
	"priority",
	"length",
	"width",
	"height",
	"weight",
	(
	select
		json_array("id",
		"name",
		"description",
		"slug",
		"is_visible",
		"image",
		"created_at",
		"organization",
		"priority",
		"parent") as "data"
	from
		(
		select
			*
		from
			"categories" "products_category"
		where
			"products_category"."id" = "products"."category_id"
		limit ?) "products_category") as "category",
	(
	select
		coalesce(json_group_array(json_array("product_id",
		"media_id",
		(
		select
			json_array("id",
			"created_at",
			"url",
			"thumbnail_url",
			"organization_id",
			"media_type") as "data"
		from
			(
			select
				*
			from
				"media" "products_mediaOnProducts_media"
			where
				"products_mediaOnProducts_media"."id" = "products_mediaOnProducts"."media_id"
			limit ?) "products_mediaOnProducts_media"))),
		json_array()) as "data"
	from
		"products_to_media" "products_mediaOnProducts"
	where
		"products_mediaOnProducts"."product_id" = "products"."id") as "mediaOnProducts",
	(
	select
		coalesce(json_group_array(json_array("id",
		"type",
		"name",
		"product_id",
		(
		select
			coalesce(json_group_array(json_array("id",
			"name",
			"value",
			"product_variant_id")),
			json_array()) as "data"
		from
			"product_variant_items" "products_variants_items"
		where
			"products_variants_items"."product_variant_id" = "products_variants"."id"))),
		json_array()) as "data"
	from
		"product_variants" "products_variants"
	where
		"products_variants"."product_id" = "products"."id") as "variants",
	(
	select
		coalesce(json_group_array(json_array("combination_id",
		"price",
		"stock",
		"product_id",
		"media_id",
		"combination_description",
		(
		select
			coalesce(json_group_array(json_array("combination_id",
			"product_variant_item_id")),
			json_array()) as "data"
		from
			"product_variant_combination_items" "products_variantCombinations_items"
		where
			"products_variantCombinations_items"."combination_id" = "products_variantCombinations"."combination_id"),
		(
		select
			json_array("id",
			"created_at",
			"url",
			"thumbnail_url",
			"organization_id",
			"media_type") as "data"
		from
			(
			select
				*
			from
				"media" "products_variantCombinations_media"
			where
				"products_variantCombinations_media"."id" = "products_variantCombinations"."media_id"
			limit ?) "products_variantCombinations_media"))),
		json_array()) as "data"
	from
		"product_variant_combinations" "products_variantCombinations"
	where
		"products_variantCombinations"."product_id" = "products"."id") as "variantCombinations"
from
	"products"
where
	"products"."id" = ?
limit ?

[elithrar]

This is something we're looking into. The EXPLAIN QUERY PLAN output does not, unfortunately, make it clear what expression depth the query actually hit/needs.

[subhendupsingh]

Thanks, unfortunately, I deleted my alpha database today and created a new one and now I am facing this problem almost in my entire app. Is there a workaround? Do multiple joins add to the query depth?

[elithrar]

Thanks, unfortunately, I deleted my alpha database today and created a new one and now I am facing this problem almost in my entire app. Is there a workaround? Do multiple joins add to the query depth?

Yes, the more joins and/or CTEs -> the deeper the query tree.

[subhendupsingh]

Are you planning to increase the depth? If yes, anytime soon?

[xdivby0]

This is something we're looking into. The EXPLAIN QUERY PLAN output does not, unfortunately, make it clear what expression depth the query actually hit/needs.

I provided the actual EXPLAIN without QUERY PLAN as well. Do you need anything else or was that comment not for me (assuming it was because I was the only one giving a EXPLAIN QUERY PLAN)

[xdivby0]

@elithrar Thank you - Quick question though, what exactly would you need to get the exact expression tree depth?

[elithrar]

For those following: we expect this increased limit to be released within the next week.

[subhendupsingh]

When is this getting released?

[elithrar]

@subhendupsingh This is now deployed. We typically like to wait until the release has been stable for a few days before commenting.

[subhendupsingh]

Got it, should I update my wrangler now to get the changes?

[elithrar]

@subhendupsingh - this change is not related to wrangler (which is client-side). Your queries, provided they fall within the new limit, should work.

Again, because SQLite's query plan does not make the expression depth of a query obvious, if your query is still hitting the new limit of 100 then I would suggest working to reduce the complexity of your query.

[subhendupsingh]

I am still running into the error and it explicitly says that the max depth is 20. I haven't updated anything.

[KianNH]

If you're using wrangler dev for local development then you need to update Wrangler so that it's using a version of workerd that has the changes, which would be at least Wrangler 3.12.2.

wrangler dev --remote and deployed Workers don't require a new Wrangler version for changes in the runtime.