# Failure while attempting substitution

**URL:** <https://community.dremio.com/t/failure-while-attempting-substitution/5729>\
**Category:** Uncategorized\
**Created:** [June 15, 2020, 8:49pm UTC](https://community.dremio.com/t/failure-while-attempting-substitution/5729 "2020-06-15T20:49:52Z")\
**Posts on this page:** 9\
**Page:** 1

<div class="post-metadata">

**Author:** ![smora](https://avatars.discourse-cdn.com/v4/letter/s/dec6dc/32.png) [@smora](https://community.dremio.com/u/smora)\
**Post date:** [June 15, 2020, 8:49pm UTC](https://community.dremio.com/t/failure-while-attempting-substitution/5729/1 "2020-06-15T20:49:52Z")

</div>

Hi Dremio Team,

There are couple of scenarios we are still getting this error.

1. create a reflection for a virtual dataset
2. use that vd as part of another vd’s definition
3. query on top level vd results in Failure while attempting substitution

query profile: [4d5da33d-3d56-46f5-89ed-bc6d8a8611bd.zip](https://community.dremio.com/uploads/short-url/8s7jqD4rBoQDloTw2Nu3IMkYMlM.zip) (73.9 KB)  
dremio ver: CE 4.1.4

Thanks

---

<div class="post-metadata">

**Author:** ![ben](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/ben/32/1367_2.png) [@ben](https://community.dremio.com/u/ben)\
**Post date:** [June 15, 2020, 8:57pm UTC](https://community.dremio.com/t/failure-while-attempting-substitution/5729/2 "2020-06-15T20:57:45Z")

</div>

To debug this, we would have to have some additional logging. Try adding these loggers to the `logback.xml` and restarting Dremio:

```auto
<logger name="com.dremio.service.reflection" additivity="false">
   <level value="debug" />
   <appender-ref ref="text" />
 </logger>

<logger name="com.dremio.service.reflection" additivity="false">
   <level value="debug" />
   <appender-ref ref="text" />
 </logger>

```

Reproduce the issue, attach the logs and profile, and then remove the extra logging and restart.

---

<div class="post-metadata">

**Author:** ![smora](https://avatars.discourse-cdn.com/v4/letter/s/dec6dc/32.png) [@smora](https://community.dremio.com/u/smora)\
**Post date:** [June 15, 2020, 9:11pm UTC](https://community.dremio.com/t/failure-while-attempting-substitution/5729/3 "2020-06-15T21:11:48Z")

</div>

@ben  
attached requested info  
profile: [23b59cc7-b170-4825-83e0-b7ad5939a4eb.zip](https://community.dremio.com/uploads/short-url/aXIq9zTUsG9ge27ArnDSA2cpbFu.zip) (76.3 KB)  
log: [dremio master.zip](https://community.dremio.com/uploads/short-url/1qatrntodFCBuZo8jYfMXEAi8o6.zip) (11.2 KB)

---

<div class="post-metadata">

**Author:** ![ben](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/ben/32/1367_2.png) [@ben](https://community.dremio.com/u/ben)\
**Post date:** [June 16, 2020, 9:34pm UTC](https://community.dremio.com/t/failure-while-attempting-substitution/5729/4 "2020-06-16T21:34:55Z")

</div>

Hi @smora,

This does not appear to have the debug logging enabled (you have to restart the Dremio coordinator/master after enabling the debug. That being said have you tried rebuilding the reflection but disabling the reflection, saving this change, and then re-enabling?

---

<div class="post-metadata">

**Author:** ![smora](https://avatars.discourse-cdn.com/v4/letter/s/dec6dc/32.png) [@smora](https://community.dremio.com/u/smora)\
**Post date:** [June 16, 2020, 11:55pm UTC](https://community.dremio.com/t/failure-while-attempting-substitution/5729/5 "2020-06-16T23:55:25Z")

</div>

Hi @ben

yes, i did try disable/enable/delete-all/recreate reflection options.  
just accessing vd uses reflections, but if the reflection is part of another vd’s definitions substitution error occurs, I got the logs again from both master&executor

master-log: [dremio-master.zip](https://community.dremio.com/uploads/short-url/qyF12oHZaN3o6MKHyVg55pbQCcb.zip) (11.4 KB)  
executor-log: [dremio-executor.zip](https://community.dremio.com/uploads/short-url/6GCfzUGjbGkcwg5hxtVKEA4RG8n.zip) (9.5 KB)

direct qry on vd (uses reflection): [977d1204-0724-46a7-b9a5-b51704aac33e.zip](https://community.dremio.com/uploads/short-url/6a6AoDIxAKl95YxGiF2OGvmnBpG.zip) (8.4 KB)

vd part of another vd (substitution error): [77c4f257-e9bd-4502-9c58-e3d694b0283f.zip](https://community.dremio.com/uploads/short-url/tEtEiLkSU36rORNz7uWLiKa9Nfg.zip) (51.3 KB)

after disable/enable/drop/recreate reflection (same error) : [52ff83d7-7f13-4cd1-a99e-f080b97bf8f9.zip](https://community.dremio.com/uploads/short-url/zY2N2npEnp1kNV47xi96ZeXt2PE.zip) (51.0 KB)

logback:

 ![dremio-logback](https://us1.discourse-cdn.com/flex020/uploads/dremio/original/2X/e/e4328cc26ed2b10f75df8ca501a2680590c40199.jpeg)

---

<div class="post-metadata">

**Author:** ![ben](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/ben/32/1367_2.png) [@ben](https://community.dremio.com/u/ben)\
**Post date:** [June 17, 2020, 12:07am UTC](https://community.dremio.com/t/failure-while-attempting-substitution/5729/6 "2020-06-17T00:07:22Z")

</div>

The problem with the logging is apparently the appender, as there is no “text” in the k8s deployment. You should be able to see the debug logging with:

```
<logger name="com.dremio.service.reflection" additivity="false">
   <level value="debug" />
   <appender-ref ref="console" />
 </logger>

<logger name="com.dremio.service.reflection" additivity="false">
   <level value="debug" />
   <appender-ref ref="console" />
 </logger>
```

---

<div class="post-metadata">

**Author:** ![smora](https://avatars.discourse-cdn.com/v4/letter/s/dec6dc/32.png) [@smora](https://community.dremio.com/u/smora)\
**Post date:** [June 17, 2020, 12:43am UTC](https://community.dremio.com/t/failure-while-attempting-substitution/5729/7 "2020-06-17T00:43:42Z")

</div>

@ben

thanks for pointing. here is the updated log&profile

master-log: [dremio-master.zip](https://community.dremio.com/uploads/short-url/23KYEfmeLfCxBhLlHendxVQFu3j.zip) (7.6 KB)  
executor-log: [dremio-executor.zip](https://community.dremio.com/uploads/short-url/3NUlYOaQkKCumrgTFWb7xo7VNJw.zip) (6.8 KB)

job-profile: [68e0431f-240d-4572-9324-5145f91189d6.zip](https://community.dremio.com/uploads/short-url/h5pFX3fGd6K30nTlu4xnu0RUV8v.zip) (51.2 KB)

Thanks

---

<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:** [June 22, 2020, 6:36am UTC](https://community.dremio.com/t/failure-while-attempting-substitution/5729/8 "2020-06-22T06:36:48Z")

</div>

@smora

Couple of things

1. The query ran on “2020-06-17 00:37:43” but the logs are only till “2020-06-16 19:40:31,131”

2. The error you are seeing may be a generic error. If you see the reflection is was matched, which means it had all the information it needs to accelerate the query. The reason why it did not choose could be because of 2 reasons

A) Dremio’s planner estimated cost of using reflection to be more expensive than going back to PDS, to confirm this, turn on “planner.verbose\_profile” and rerun the query and send us the profile  
B) There was an exception during substitution, for this we need the server.log right after you run the query

---

<div class="post-metadata">

**Author:** ![smora](https://avatars.discourse-cdn.com/v4/letter/s/dec6dc/32.png) [@smora](https://community.dremio.com/u/smora)\
**Post date:** [June 22, 2020, 11:40pm UTC](https://community.dremio.com/t/failure-while-attempting-substitution/5729/9 "2020-06-22T23:40:15Z")

</div>

@balaji.ramaswamy @ben

Attached logs&profile as per your advice.

profile: [cb6bb5a0-ada1-4de2-9a1a-dd7cc74c4806.zip](https://community.dremio.com/uploads/short-url/2QgYLS5mWYVQFVohyOTegbco1zq.zip) (85.1 KB)  
master-log: [master.zip](https://community.dremio.com/uploads/short-url/A6xTDdo39mXrvm05pLMlDstiDe8.zip) (10.7 KB)  
executor-log: [executor.zip](https://community.dremio.com/uploads/short-url/A0IOus1Wlj636tOnGT1cYjJnsPM.zip) (8.8 KB)

Thanks  
Sudheer