# Acceleration is disabled. No substitutions

**URL:** <https://community.dremio.com/t/acceleration-is-disabled-no-substitutions/12396>\
**Category:** Uncategorized\
**Created:** [October 10, 2024, 2:18am UTC](https://community.dremio.com/t/acceleration-is-disabled-no-substitutions/12396 "2024-10-10T02:18:55Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![dacopan](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/dacopan/32/4289_2.png) [@dacopan](https://community.dremio.com/u/dacopan)\
**Post date:** [October 10, 2024, 2:18am UTC](https://community.dremio.com/t/acceleration-is-disabled-no-substitutions/12396/1 "2024-10-10T02:18:55Z")

</div>

Hello friends, please I need your help, we has a strange case that reflection are not used whern run this query:

```sql
SELECT ID_PAGE,
                  MAX(TOTAL_FOLLOWERS) aa
           FROM lake.prod.basics
           WHERE "ID_PAGE" IN
               (SELECT ID_PAGE
                FROM lake.prod.spaces
                WHERE ID_SPACE = '66540a999f94cf328c8bf693' )
                group by ID_PAGE

```

I already check profile, logs, and all I can posibble check but I cannot find the cause.

Reflection definition

 ![imagen](https://us1.discourse-cdn.com/flex020/uploads/dremio/original/2X/d/d7d80c10694b66aa13a989cc9d27b162e46df657.png)

Profile:  
[67524106-a6c2-4e8a-b8da-b2e2376de06a.zip](https://community.dremio.com/uploads/short-url/w9wiaNmDXzSzxk4bB19RmFCERQy.zip) (34,9 KB)

DEBUG log of acceleration (as mentioned here [Dremio 23 not match any reflections with MINIOS3 - #3 by Benny\_Chow](https://community.dremio.com/t/dremio-23-not-match-any-reflections-with-minios3/9982/3))

```bash
2024-10-10 02:13:02,960 [18f8c7d0-a2e2-d4b8-7d28-39c469c2d900/0:foreman-planning] DEBUG c.d.e.p.a.s.DremioSubstitutionProvider - Eliminate extra starflake tables
2024-10-10 02:13:02,960 [18f8c7d0-a2e2-d4b8-7d28-39c469c2d900/0:foreman-planning] DEBUG c.d.e.p.a.n.rules.ConvertToCountOne - Generated newMapping: [-1, 0, -1, -1, 1, -1, -1, 2, -1], reverseMapping: [1, 4, 7] for rel#60704392:LogicalAggregate.NONE.ANY([]).[](input=HepRelVertex#60704391,group={1, 4},max-created_date=MAX($4),count-created_date=COUNT($4),max-total_followers=MAX($7),count-total_followers=COUNT($7),agg-sum0-0=$SUM0($8),agg-count1-0=COUNT($8))
2024-10-10 02:13:02,960 [18f8c7d0-a2e2-d4b8-7d28-39c469c2d900/0:foreman-planning] DEBUG c.d.e.p.a.n.rules.ConvertToCountOne - NewBottomProjectWithLiteralOne: [$1, $4, $7, 1]
2024-10-10 02:13:02,960 [18f8c7d0-a2e2-d4b8-7d28-39c469c2d900/0:foreman-planning] DEBUG c.d.e.p.a.n.rules.ConvertToCountOne - NewAggregate: [$0, $1], [MAX($1), MAX($2), COUNT($2), COUNT($3)]
2024-10-10 02:13:02,960 [18f8c7d0-a2e2-d4b8-7d28-39c469c2d900/0:foreman-planning] DEBUG c.d.e.p.a.n.rules.ConvertToCountOne - NewTopProject: [$0, $1, $2, CASE(IS NULL($1), 0:BIGINT, $5), $3, $4, $5, $5]
2024-10-10 02:13:02,960 [18f8c7d0-a2e2-d4b8-7d28-39c469c2d900/0:foreman-planning] DEBUG c.d.e.p.a.n.rules.ConvertToCountOne - Transforming to LogicalProject#60704396
2024-10-10 02:13:02,960 [18f8c7d0-a2e2-d4b8-7d28-39c469c2d900/0:foreman-planning] DEBUG c.d.e.p.a.n.rules.ConvertToCountOne - Did not find any aggregate calls that are equivalent to count(1)
2024-10-10 02:13:02,960 [18f8c7d0-a2e2-d4b8-7d28-39c469c2d900/0:foreman-planning] DEBUG c.d.e.p.a.n.rules.ConvertToCountOne - Did not find any aggregate calls that are equivalent to count(1)
2024-10-10 02:13:02,960 [18f8c7d0-a2e2-d4b8-7d28-39c469c2d900/0:foreman-planning] DEBUG c.d.e.p.a.n.rules.ConvertToCountOne - Did not find any aggregate calls that are equivalent to count(1)
2024-10-10 02:13:02,961 [18f8c7d0-a2e2-d4b8-7d28-39c469c2d900/0:foreman-planning] DEBUG c.d.e.p.a.s.DremioSubstitutionProvider - Reflection d5a48910-ccaa-42c5-a2e7-045b6e4fbbf1.b026c19e-effb-4a63-8229-34872732211f
Normalized:
LogicalProject(ID_PAGE=[$0], CREATED_DATE=[$1], max-created_date=[$3], max-total_followers=[$4], count-total_followers=[$5], CONVERT_COUNT_STAR=[$6])
  LogicalAggregate(group=[{0, 1, 4}], max-created_date=[MAX($1)], max-total_followers=[MAX($2)], count-total_followers=[COUNT($2)], CONVERT_COUNT_STAR=[COUNT($3)])
    LogicalProject(ID_PAGE=[$1], CREATED_DATE=[$4], TOTAL_FOLLOWERS=[$7], $f3=[1], $_dremio_$_dummy_$=[null:NULL])
      ScanCrel(table=[lake.prod.basics], snapshot=[3553615674448220692], columns=[`ID`, `ID_PAGE`, `RED`, `RED_NAME`, `CREATED_DATE`, `USERNAME`, `NAME`, `TOTAL_FOLLOWERS`], splits=[1])

stripped
2024-10-10 02:13:02,961 [18f8c7d0-a2e2-d4b8-7d28-39c469c2d900/0:foreman-planning] DEBUG c.d.e.p.a.s.DremioSubstitutionProvider - RelNode - appendedTable:
LogicalProject(ID_PAGE=[$0], CREATED_DATE=[$1], max-created_date=[$2], count-created_date=[CASE(IS NULL($1), 0:BIGINT, $5)], max-total_followers=[$3], count-total_followers=[$4], agg-sum0-0=[$5], agg-count1-0=[$5])
  LogicalAggregate(group=[{0, 1}], max-created_date=[MAX($2)], max-total_followers=[MAX($3)], count-total_followers=[$SUM0($4)], CONVERT_COUNT_STAR=[$SUM0($5)])
    LogicalProject(ID_PAGE=[$0], CREATED_DATE=[$1], max-created_date=[$2], max-total_followers=[$3], count-total_followers=[CAST($4):BIGINT NOT NULL], CONVERT_COUNT_STAR=[CAST($5):BIGINT NOT NULL])
      ScanCrel(table=["__accelerator"."d5a48910-ccaa-42c5-a2e7-045b6e4fbbf1"."b026c19e-effb-4a63-8229-34872732211f"], snapshot=[2934187645415682489], columns=[`ID_PAGE`, `CREATED_DATE`, `max-created_date`, `max-total_followers`, `count-total_followers`, `CONVERT_COUNT_STAR`], splits=[1])

2024-10-10 02:13:02,961 [18f8c7d0-a2e2-d4b8-7d28-39c469c2d900/0:foreman-planning] DEBUG c.d.e.p.a.s.DremioSubstitutionProvider - 1 unexpanded materializations
2024-10-10 02:13:02,961 [18f8c7d0-a2e2-d4b8-7d28-39c469c2d900/0:foreman-planning] DEBUG c.d.e.p.a.s.DremioSubstitutionProvider - Finding expansion replacements for 1 materializations
2024-10-10 02:13:02,961 [18f8c7d0-a2e2-d4b8-7d28-39c469c2d900/0:foreman-planning] DEBUG c.d.e.p.a.s.DremioSubstitutionProvider - Found 0 expansion nodes
2024-10-10 02:13:02,961 [18f8c7d0-a2e2-d4b8-7d28-39c469c2d900/0:foreman-planning] DEBUG c.d.e.p.a.s.DremioSubstitutionProvider - Referenced expansions: []
2024-10-10 02:13:02,961 [18f8c7d0-a2e2-d4b8-7d28-39c469c2d900/0:foreman-planning] DEBUG c.d.e.p.a.n.rules.ConvertToCountOne - Did not find any aggregate calls that are equivalent to count(1)
2024-10-10 02:13:02,961 [18f8c7d0-a2e2-d4b8-7d28-39c469c2d900/0:foreman-planning] DEBUG c.d.e.p.a.s.DremioSubstitutionProvider - Normalized target:
LogicalProject(ID_PAGE=[$0], CREATED_DATE=[$1], max-created_date=[$3], max-total_followers=[$4], count-total_followers=[$5], CONVERT_COUNT_STAR=[$6])
  LogicalAggregate(group=[{0, 1, 4}], max-created_date=[MAX($1)], max-total_followers=[MAX($2)], count-total_followers=[COUNT($2)], CONVERT_COUNT_STAR=[COUNT($3)])
    LogicalProject(ID_PAGE=[$1], CREATED_DATE=[$4], TOTAL_FOLLOWERS=[$7], $f3=[1], $_dremio_$_dummy_$=[null:NULL])
      ScanCrel(table=[lake.prod.basics], snapshot=[3553615674448220692], columns=[`ID`, `ID_PAGE`, `RED`, `RED_NAME`, `CREATED_DATE`, `USERNAME`, `NAME`, `TOTAL_FOLLOWERS`], splits=[1])

2024-10-10 02:13:02,962 [18f8c7d0-a2e2-d4b8-7d28-39c469c2d900/0:foreman-planning] DEBUG c.d.e.p.a.n.rules.ConvertToCountOne - Did not find any aggregate calls that are equivalent to count(1)
2024-10-10 02:13:02,962 [18f8c7d0-a2e2-d4b8-7d28-39c469c2d900/0:foreman-planning] DEBUG c.d.e.p.a.n.rules.ConvertToCountOne - Did not find any aggregate calls that are equivalent to count(1)
2024-10-10 02:13:02,962 [18f8c7d0-a2e2-d4b8-7d28-39c469c2d900/0:foreman-planning] DEBUG c.d.e.p.a.n.rules.ConvertToCountOne - Did not find any aggregate calls that are equivalent to count(1)
2024-10-10 02:13:02,962 [18f8c7d0-a2e2-d4b8-7d28-39c469c2d900/0:foreman-planning] DEBUG c.d.e.p.a.n.rules.ConvertToCountOne - Did not find any aggregate calls that are equivalent to count(1)
2024-10-10 02:13:02,962 [18f8c7d0-a2e2-d4b8-7d28-39c469c2d900/0:foreman-planning] DEBUG c.d.e.p.a.n.rules.ConvertToCountOne - Did not find any aggregate calls that are equivalent to count(1)
2024-10-10 02:13:02,962 [18f8c7d0-a2e2-d4b8-7d28-39c469c2d900/0:foreman-planning] DEBUG c.d.e.p.a.n.rules.ConvertToCountOne - Did not find any aggregate calls that are equivalent to count(1)
2024-10-10 02:13:02,962 [18f8c7d0-a2e2-d4b8-7d28-39c469c2d900/0:foreman-planning] DEBUG c.d.e.p.a.s.DremioSubstitutionProvider - Finding bup replacements
2024-10-10 02:13:02,962 [18f8c7d0-a2e2-d4b8-7d28-39c469c2d900/0:foreman-planning] DEBUG c.d.e.p.a.s.DremioSubstitutionProvider - Reporting normalized materialization targets
2024-10-10 02:13:02,962 [18f8c7d0-a2e2-d4b8-7d28-39c469c2d900/0:foreman-planning] DEBUG c.d.e.p.a.s.DremioSubstitutionProvider - Reporting materialization: d5a48910-ccaa-42c5-a2e7-045b6e4fbbf1.b026c19e-effb-4a63-8229-34872732211f
2024-10-10 02:13:02,962 [18f8c7d0-a2e2-d4b8-7d28-39c469c2d900/0:foreman-planning] DEBUG c.d.e.p.a.s.DremioSubstitutionProvider - Prepare stream to generateSubtreeMatches method
2024-10-10 02:13:02,963 [18f8c7d0-a2e2-d4b8-7d28-39c469c2d900/0:foreman-planning] DEBUG c.d.e.p.a.s.DremioSubstitutionProvider - Initial user query variation:
LogicalProject(ID_PAGE=[$0], aa=[$1])
  LogicalAggregate(group=[{0}], aa=[MAX($1)])
    LogicalProject(ID_PAGE=[$1], TOTAL_FOLLOWERS=[$7])
      LogicalJoin(condition=[=($1, $8)], joinType=[inner])
        ScanCrel(table=[lake.prod.basics], snapshot=[3553615674448220692], columns=[`ID`, `ID_PAGE`, `RED`, `RED_NAME`, `CREATED_DATE`, `USERNAME`, `NAME`, `TOTAL_FOLLOWERS`], splits=[1])
        LogicalAggregate(group=[{0}])
          LogicalProject(ID_PAGE=[$1])
            LogicalFilter(condition=[=($0, '66540a999f94cf328c8bf693')])
              ScanCrel(table=[lake.prod.spaces], snapshot=[5027336214320522386], columns=[`ID_SPACE`, `ID_PAGE`, `RED`], splits=[1])

2024-10-10 02:13:02,963 [18f8c7d0-a2e2-d4b8-7d28-39c469c2d900/0:foreman-planning] DEBUG c.d.e.p.a.s.DremioSubstitutionProvider - Processed query variation:
LogicalAggregate(group=[{0}], aa=[MAX($1)])
  LogicalProject(ID_PAGE=[$1], TOTAL_FOLLOWERS=[$7])
    LogicalJoin(condition=[=($1, $8)], joinType=[inner])
      ScanCrel(table=[lake.prod.basics], snapshot=[3553615674448220692], columns=[`ID`, `ID_PAGE`, `RED`, `RED_NAME`, `CREATED_DATE`, `USERNAME`, `NAME`, `TOTAL_FOLLOWERS`], splits=[1])
      LogicalAggregate(group=[{0}])
        LogicalProject(ID_PAGE=[$1])
          LogicalFilter(condition=[=($0, '66540a999f94cf328c8bf693')])
            ScanCrel(table=[lake.prod.spaces], snapshot=[5027336214320522386], columns=[`ID_SPACE`, `ID_PAGE`, `RED`], splits=[1])

2024-10-10 02:13:02,964 [18f8c7d0-a2e2-d4b8-7d28-39c469c2d900/0:foreman-planning] DEBUG c.d.e.p.a.s.DremioSubstitutionProvider - Initial user query variation:
LogicalProject(ID_PAGE=[$0], aa=[$1])
  LogicalAggregate(group=[{0}], aa=[MAX($1)])
    LogicalProject(ID_PAGE=[$0], TOTAL_FOLLOWERS=[$1])
      LogicalJoin(condition=[=($0, $2)], joinType=[inner])
        ScanCrel(table=[lake.prod.basics], snapshot=[3553615674448220692], columns=[`ID_PAGE`, `TOTAL_FOLLOWERS`], splits=[1])
        LogicalAggregate(group=[{0}])
          LogicalProject(ID_PAGE=[$1])
            LogicalFilter(condition=[=($0, '66540a999f94cf328c8bf693')])
              ScanCrel(table=[lake.prod.spaces], snapshot=[5027336214320522386], columns=[`ID_SPACE`, `ID_PAGE`], splits=[1])

2024-10-10 02:13:02,964 [18f8c7d0-a2e2-d4b8-7d28-39c469c2d900/0:foreman-planning] DEBUG c.d.e.p.a.s.DremioSubstitutionProvider - Processed query variation:
LogicalAggregate(group=[{0}], aa=[MAX($1)])
  LogicalProject(ID_PAGE=[$0], TOTAL_FOLLOWERS=[$1])
    LogicalJoin(condition=[=($0, $2)], joinType=[inner])
      ScanCrel(table=[lake.prod.basics], snapshot=[3553615674448220692], columns=[`ID_PAGE`, `TOTAL_FOLLOWERS`], splits=[1])
      LogicalAggregate(group=[{0}])
        LogicalProject(ID_PAGE=[$1])
          LogicalFilter(condition=[=($0, '66540a999f94cf328c8bf693')])
            ScanCrel(table=[lake.prod.spaces], snapshot=[5027336214320522386], columns=[`ID_SPACE`, `ID_PAGE`], splits=[1])

2024-10-10 02:13:02,973 [18f8c7d0-a2e2-d4b8-7d28-39c469c2d900/0:foreman-planning] DEBUG c.d.e.p.a.s.AccelerationAwareSubstitutionProvider - Acceleration is disabled. No substitutions...

```

---

<div class="post-metadata">

**Author:** ![balaji.ramaswamy](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/balaji.ramaswamy/32/843_2.png) [@balaji.ramaswamy](https://community.dremio.com/u/balaji.ramaswamy)\
**Post date:** [October 10, 2024, 4:20am UTC](https://community.dremio.com/t/acceleration-is-disabled-no-substitutions/12396/2 "2024-10-10T04:20:26Z")

</div>

@dacopan The reflection is not matched, I see you have a FILTER on `ID_SPACE` but not selected as a dimension?

---

<div class="post-metadata">

**Author:** ![dacopan](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/dacopan/32/4289_2.png) [@dacopan](https://community.dremio.com/u/dacopan)\
**Post date:** [October 10, 2024, 4:22am UTC](https://community.dremio.com/t/acceleration-is-disabled-no-substitutions/12396/3 "2024-10-10T04:22:45Z")

</div>

Hello thank by your reply, the reflection are in basics table that is filter on id\_page

---

<div class="post-metadata">

**Author:** ![balaji.ramaswamy](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/balaji.ramaswamy/32/843_2.png) [@balaji.ramaswamy](https://community.dremio.com/u/balaji.ramaswamy)\
**Post date:** [October 10, 2024, 4:30am UTC](https://community.dremio.com/t/acceleration-is-disabled-no-substitutions/12396/4 "2024-10-10T04:30:15Z")

</div>

Sorry @dacopan missed that, What happens if you run the below 2 queries?

```auto
SELECT ID_PAGE, MAX(TOTAL_FOLLOWERS) aa
FROM lake.prod.basics b
WHERE "ID_PAGE" = <direct-value>
GROUP BY 2

```

```auto
SELECT b.ID_PAGE, MAX(TOTAL_FOLLOWERS) aa
FROM lake.prod.basics b INNER JOIN lake.prod.spaces s ON b.ID_PAGE = s.ID_PAGE
WHERE ID_SPACE = '66540a999f94cf328c8bf693'
group by b.ID_PAGE

```

---

<div class="post-metadata">

**Author:** ![dacopan](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/dacopan/32/4289_2.png) [@dacopan](https://community.dremio.com/u/dacopan)\
**Post date:** [October 11, 2024, 3:29am UTC](https://community.dremio.com/t/acceleration-is-disabled-no-substitutions/12396/5 "2024-10-11T03:29:47Z")

</div>

hello, in first case reflection was used  
[d78c23b8-44fe-4b52-9e94-91b3903d4777.zip](https://community.dremio.com/uploads/short-url/3sOyYmKsNRgg1GOCPkzJKVzAyEa.zip) (21,6 KB)

In second case, reflection not used

[4385ff10-a8ec-47bd-b250-df281510e7dd.zip](https://community.dremio.com/uploads/short-url/g8Z0Y0cromS1yBjxGPdGreVPhh2.zip) (35,2 KB)

---

<div class="post-metadata">

**Author:** ![balaji.ramaswamy](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/balaji.ramaswamy/32/843_2.png) [@balaji.ramaswamy](https://community.dremio.com/u/balaji.ramaswamy)\
**Post date:** [October 16, 2024, 5:53am UTC](https://community.dremio.com/t/acceleration-is-disabled-no-substitutions/12396/6 "2024-10-16T05:53:46Z")

</div>

@dacopan Query is accelerated on my localrepro, let me see if this is specific to CE edition

 ![Screenshot 2024-10-15 at 10.52.41 PM](https://us1.discourse-cdn.com/flex020/uploads/dremio/original/2X/d/daf2d48ed05c7308904debec0bd23241ea524246.png)

---

<div class="post-metadata">

**Author:** ![dacopan](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/dacopan/32/4289_2.png) [@dacopan](https://community.dremio.com/u/dacopan)\
**Post date:** [October 19, 2024, 11:17am UTC](https://community.dremio.com/t/acceleration-is-disabled-no-substitutions/12396/7 "2024-10-19T11:17:51Z")

</div>

Hello, thank by your help, the error was caused because a colleague was ran this config

```sql
alter system set accelerator.enable_multijoin = FALSE

```

---

<div class="post-metadata">

**Author:** ![balaji.ramaswamy](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/balaji.ramaswamy/32/843_2.png) [@balaji.ramaswamy](https://community.dremio.com/u/balaji.ramaswamy)\
**Post date:** [October 23, 2024, 3:53pm UTC](https://community.dremio.com/t/acceleration-is-disabled-no-substitutions/12396/8 "2024-10-23T15:53:29Z")

</div>

Thanks for the update @dacopan