I tried again with this query: with element as ( select _id, ElementTypeName, ElementSubTypeName, PlanId FROM `mongo.grounds`.`Elements` ), element_effort as ( select _id, EffortTypeName FROM `mongo.grounds`.`Elements_Efforts` ) select * from element join element_effort on element._id = element_effort._id where element.PlanId = '1623263140'
The query completed successfully, but it did not return any rows. I've attached the log and the profile. On Thu, Feb 3, 2022 at 9:02 AM James Turton <[email protected]> wrote: > It looks like there is a FLOAT field called MinutesTotal that is only > present in some documents. Can you try writing a query that uses > explicit column specs like this? > > with element as ( > select > _id, > ElementTypeName, > PlanId, > ... > FROM > `mongo.grounds`.`Elements` > ), element_effort as ( > select > _id, > EffortTypeName > FROM > `mongo.grounds`.`Elements_Efforts` > ) > select > * > from > element > join > element_effort on element._id = element_effort._id; > > (Needs some fleshing out). You can also experiment with the UNION type > for this situation but I understand that one should be cautious about > using it in production. > > I still cannot say why 1.19 has no problem here but it could perhaps be > a batch ordering thing. I think that whether or not the _first_ batch > includes a MinutesTotal field can make a difference to subsequent schema > handling (would need to confirm this last bit). > > > On 2022/02/03 15:33, Daniel Clark wrote: > > Hi James, > > > > Please see the attached. > > > > On Wed, Feb 2, 2022 at 2:35 AM Daniel Clark <[email protected] > > <mailto:[email protected]>> wrote: > > > > Hi James, > > > > There initially weren’t any differences between the 1.19 environment > > and the 1.20.0-SNAPSHOT environment. The config options that worked > > in the 1.19 environment were carried over when I installed the > > snapshot build. The recent change made to the snapshot build was > > setting store.mongo.bson.record.reader to true. The original query > > worked in the 1.19 environment, with the parameter set to false. > > > > Yes, I’m running the exact same query against the exact same data > > sources. I’ll attach a copy of the stack trace and profile, later > > this morning. I’ll also see about reducing the dataset. Thanks for > > following up. > > > > Sent from my iPhone > > > > > On Feb 2, 2022, at 2:03 AM, James Turton <[email protected] > > <mailto:[email protected]>> wrote: > > > > > > Okay. It's always a good idea to attach a stack trace and a > > query profile when you have an error to send in, so maybe you can > > add those? > > > > > > Next, we're left with a reproducibility challenge. Are there > > other config option differences between your two Drill environments, > > beyond the one we've uncovered? Are you running exactly the same > > query against exactly the same data source in both environments? > > Can you reduce the collections involved in the query to minimal (and > > obfuscated if need be) datasets that we can use to reproduce the > > problem? > > > > > >> On 2022/02/01 18:15, Daniel Clark wrote: > > >> No, exec.enable_union_type is set tofalse. > > >> On Tue, Feb 1, 2022 at 10:59 AM James Turton <[email protected] > > <mailto:[email protected]> <mailto:[email protected] > > <mailto:[email protected]>>> wrote: > > >> Do you have exec.enable_union_type = true in your 1.19 > > environment? > > >> On 2022/02/01 17:30, Daniel Clark wrote: > > >> > Hi James, > > >> > > > >> > Yes, the store.mongo.bson.record.reader was set to false. > > I set > > >> it to true > > >> > and re-ran the original query. It returned an error: > > >> > UNSUPPORTED_OPERATION ERROR: Schema changes not supported > in > > >> External Sort. > > >> > Please enable Union type. > > >> > > > >> > > > >> > > > >> > On Tue, Feb 1, 2022 at 9:19 AM James Turton > > <[email protected] <mailto:[email protected]> > > >> <mailto:[email protected] <mailto:[email protected]>>> wrote: > > >> > > > >> >> Hi Daniel > > >> >> > > >> >> Please let us know if you have set the config option > > >> store.mongo.bson.record.reader > > >> >> = false and, if so, please set it to true. > > >> >> > > >> >> Thanks > > >> >> James > > >> >> > > >> >> On 2022/01/31 17:45, Daniel Clark wrote: > > >> >> > > >> >> Here it is. Please see the attached file. > > >> >> > > >> >> On Mon, Jan 31, 2022 at 4:22 AM James Turton > > <[email protected] <mailto:[email protected]> > > >> <mailto:[email protected] <mailto:[email protected]>>> wrote: > > >> >> > > >> >>> Please also attach the query profile if you can. > > >> >>> > > >> >>> Thanks > > >> >>> James > > >> >>> > > >> >>> On 2022/01/31 08:09, luoc wrote: > > >> >>>> Hi Daniel, > > >> >>>> What is the data type of the `_id` field? The > default > > >> ObjectId, or > > >> >>> String or key-value pair (Struct)? > > >> >>>> > > >> >>>>> On Jan 31, 2022, at 11:12, Daniel Clark > > <[email protected] <mailto:[email protected]> > > >> <mailto:[email protected] <mailto:[email protected]>>> > wrote: > > >> >>>>> > > >> >>>>> > > >> >>>>> Hello, > > >> >>>>> > > >> >>>>> I'm running this mongo query on the 1.20.0-SNAPSHOT > > build. It > > >> runs > > >> >>> without error on the 1.19 release. > > >> >>>>> > > >> >>>>> SELECT `Elements_Efforts`.`EffortTypeName` AS > > `EffortTypeName`, > > >> >>>>> `Elements`.`ElementSubTypeName` AS > > `ElementSubTypeName`, > > >> >>>>> `Elements`.`ElementTypeName` AS `ElementTypeName`, > > >> >>>>> `Elements`.`PlanID` AS `PlanID` > > >> >>>>> FROM `mongo.grounds`.`Elements` `Elements` > > >> >>>>> INNER JOIN `mongo.grounds`.`Elements_Efforts` > > >> `Elements_Efforts` ON > > >> >>> (`Elements`.`_id` = `Elements_Efforts`.`_id`) > > >> >>>>> WHERE (`Elements`.`PlanID` = '1623263140') > > >> >>>>> GROUP BY `Elements_Efforts`.`EffortTypeName`, > > >> >>>>> `Elements`.`ElementSubTypeName`, > > >> >>>>> `Elements`.`ElementTypeName`, > > >> >>>>> `Elements`.`PlanID` > > >> >>>>> > > >> >>>>> The error message returned is: > > >> >>>>> > > >> >>>>> org.apache.drill.common.exceptions.UserRemoteException: > > >> SYSTEM ERROR: > > >> >>> UnsupportedOperationException: Map, Array, Union or > repeated > > >> scalar type > > >> >>> should not be used in group by, order by or in a > comparison > > >> operator. Drill > > >> >>> does not support compare between MAP:REQUIRED and > > MAP:REQUIRED. > > >> >>>>> > > >> >>>>> Fragment: 0:0 > > >> >>>>> > > >> >>>>> Please, refer to logs for more information. > > >> >>>>> > > >> >>>>> [Error Id: 21b3260d-9ebf-4156-a5fa-4748453b5465 on > > >> localhost:31010] > > >> >>>>> > > >> >>>>> I've tried searching the mailing list archives, as > well as > > >> googling > > >> >>> the error. The stack trace mentions that memory was > > leaked by > > >> the query. > > >> >>> Any ideas? Full stack trace attached. > > >> >>>>> <stacktrace.txt> > > >> >>> > > >> >>> > > >> >> > > >> > > > >
2022-02-03 09:29:00,256 [1e041952-c343-ff7f-0e6d-9ba64ea4a8a5:foreman] INFO
o.a.drill.exec.work.foreman.Foreman - Query text for query with id
1e041952-c343-ff7f-0e6d-9ba64ea4a8a5 issued by clarkddc: with element as (
select
_id,
ElementTypeName,
ElementSubTypeName,
PlanId
FROM
`mongo.grounds`.`Elements`
), element_effort as (
select
_id,
EffortTypeName
FROM
`mongo.grounds`.`Elements_Efforts`
)
select
*
from
element
join
element_effort on element._id = element_effort._id
where element.PlanId = '1623263140'
2022-02-03 09:29:00,271 [1e041952-c343-ff7f-0e6d-9ba64ea4a8a5:foreman] WARN
o.a.d.e.s.m.s.MongoSchemaFactory - Failure while getting collection names from
'admin'. Command failed with error 13 (Unauthorized): 'not authorized on admin
to execute command { listCollections: 1, cursor: {}, nameOnly: true, $db:
"admin", $clusterTime: { clusterTime: Timestamp(1643898532, 1), signature: {
hash: BinData(0, C919EF5659AB7138F48E56CB85ACDA3EEB54826E), keyId:
7034735910600048641 } }, lsid: { id:
UUID("c21322a2-bf76-4ae0-b9ee-90aaf9076a1f") } }' on server localhost:27017.
The full response is {"operationTime": {"$timestamp": {"t": 1643898532, "i":
1}}, "ok": 0.0, "errmsg": "not authorized on admin to execute command {
listCollections: 1, cursor: {}, nameOnly: true, $db: \"admin\", $clusterTime: {
clusterTime: Timestamp(1643898532, 1), signature: { hash: BinData(0,
C919EF5659AB7138F48E56CB85ACDA3EEB54826E), keyId: 7034735910600048641 } },
lsid: { id: UUID(\"c21322a2-bf76-4ae0-b9ee-90aaf9076a1f\") } }", "code": 13,
"codeName": "Unauthorized", "$clusterTime": {"clusterTime": {"$timestamp":
{"t": 1643898532, "i": 1}}, "signature": {"hash": {"$binary": {"base64":
"yRnvVlmrcTj0jlbLhazaPutUgm4=", "subType": "00"}}, "keyId":
7034735910600048641}}}
2022-02-03 09:39:57,793 [1e041952-c343-ff7f-0e6d-9ba64ea4a8a5:frag:0:0] INFO
o.a.d.e.s.m.MongoScanBatchCreator - Number of record readers initialized : 1
2022-02-03 09:39:57,810 [1e041952-c343-ff7f-0e6d-9ba64ea4a8a5:frag:0:0] INFO
o.a.d.e.s.m.MongoScanBatchCreator - Number of record readers initialized : 1
2022-02-03 09:39:57,825 [1e041952-c343-ff7f-0e6d-9ba64ea4a8a5:frag:0:0] INFO
o.a.d.e.w.fragment.FragmentExecutor - 1e041952-c343-ff7f-0e6d-9ba64ea4a8a5:0:0:
State change requested AWAITING_ALLOCATION --> RUNNING
2022-02-03 09:39:57,825 [1e041952-c343-ff7f-0e6d-9ba64ea4a8a5:frag:0:0] INFO
o.a.d.e.w.f.FragmentStatusReporter - 1e041952-c343-ff7f-0e6d-9ba64ea4a8a5:0:0:
State to report: RUNNING
2022-02-03 09:39:58,373 [1e041952-c343-ff7f-0e6d-9ba64ea4a8a5:frag:0:0] WARN
o.a.d.e.e.ExpressionTreeMaterializer - Unable to find value vector of path
`ElementTypeName`, returning null instance.
2022-02-03 09:39:58,373 [1e041952-c343-ff7f-0e6d-9ba64ea4a8a5:frag:0:0] WARN
o.a.d.e.e.ExpressionTreeMaterializer - Unable to find value vector of path
`ElementSubTypeName`, returning null instance.
2022-02-03 09:39:58,373 [1e041952-c343-ff7f-0e6d-9ba64ea4a8a5:frag:0:0] WARN
o.a.d.e.e.ExpressionTreeMaterializer - Unable to find value vector of path
`PlanId`, returning null instance.
2022-02-03 09:40:30,754 [1e041952-c343-ff7f-0e6d-9ba64ea4a8a5:frag:0:0] INFO
o.a.d.e.w.fragment.FragmentExecutor - 1e041952-c343-ff7f-0e6d-9ba64ea4a8a5:0:0:
State change requested RUNNING --> FINISHED
2022-02-03 09:40:30,755 [1e041952-c343-ff7f-0e6d-9ba64ea4a8a5:frag:0:0] INFO
o.a.d.e.w.f.FragmentStatusReporter - 1e041952-c343-ff7f-0e6d-9ba64ea4a8a5:0:0:
State to report: FINISHED
profile_1.json
Description: application/json
