You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@drill.apache.org by luoc <lu...@apache.org> on 2022/02/01 03:39:15 UTC
Re: 1.20.0-SNAPSHOT: UserRemoteException: SYSTEM ERROR: UnsupportedOperationException
Hi Daniel,
I should guess the reason for this case, but I need to verify it first. Basically, under the full text, the ObjectId exists as a key-value pair (Map structure), not as a character or number value.
> On Jan 31, 2022, at 23:45, Daniel Clark <cl...@gmail.com> wrote:
>
>
> Here it is. Please see the attached file.
>
>> On Mon, Jan 31, 2022 at 4:22 AM James Turton <dz...@apache.org> 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 <cl...@gmail.com> 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>
>>
>
> <profile.json>
Re: 1.20.0-SNAPSHOT: UserRemoteException: SYSTEM ERROR: UnsupportedOperationException
Posted by Charles Givre <cg...@gmail.com>.
I wonder if some of the other pushdowns are negatively affecting the speed. That's really the only difference here.
> On Feb 1, 2022, at 10:36 AM, Daniel Clark <cl...@gmail.com> wrote:
>
> Hi James,
>
> The amended query runs, however, over 2x slower than Drill 1.19. Thanks.
>
>
> On Tue, Feb 1, 2022 at 4:24 AM James Turton <dz...@apache.org> wrote:
>
>> If you feel like playing around you can try out this variation.
>>
>> 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 convert_to(`Elements`.`_id`,
>> 'JSON') = convert_to(`Elements_Efforts`.`_id`, 'JSON')
>> WHERE (`Elements`.`PlanID` = '1623263140')
>> GROUP BY `Elements_Efforts`.`EffortTypeName`,
>> `Elements`.`ElementSubTypeName`,
>> `Elements`.`ElementTypeName`,
>> `Elements`.`PlanID`
>>
>>
>> On 2022/02/01 11:08, James Turton wrote:
>>
>> I think luoc is exactly right in his diagnosis. So Daniel, does this
>> query run fine on Drill 1.19 with store.mongo.all_text_mode = true?
>>
>> Thanks
>> James
>>
>> On 2022/02/01 05:39, luoc wrote:
>>
>> Hi Daniel,
>> I should guess the reason for this case, but I need to verify it first.
>> Basically, under the full text, the ObjectId exists as a key-value pair
>> (Map structure), not as a character or number value.
>>
>> On Jan 31, 2022, at 23:45, Daniel Clark<cl...@gmail.com>
>> <cl...@gmail.com> wrote:
>>
>>
>> Here it is. Please see the attached file.
>>
>> On Mon, Jan 31, 2022 at 4:22 AM James Turton<dz...@apache.org>
>> <dz...@apache.org> wrote:
>> Please also attach the query profile if you can.
>>
>> Thanks
>> James
>>
>>
>>
>>
Re: 1.20.0-SNAPSHOT: UserRemoteException: SYSTEM ERROR: UnsupportedOperationException
Posted by Daniel Clark <cl...@gmail.com>.
Hi James,
The amended query runs, however, over 2x slower than Drill 1.19. Thanks.
On Tue, Feb 1, 2022 at 4:24 AM James Turton <dz...@apache.org> wrote:
> If you feel like playing around you can try out this variation.
>
> 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 convert_to(`Elements`.`_id`,
> 'JSON') = convert_to(`Elements_Efforts`.`_id`, 'JSON')
> WHERE (`Elements`.`PlanID` = '1623263140')
> GROUP BY `Elements_Efforts`.`EffortTypeName`,
> `Elements`.`ElementSubTypeName`,
> `Elements`.`ElementTypeName`,
> `Elements`.`PlanID`
>
>
> On 2022/02/01 11:08, James Turton wrote:
>
> I think luoc is exactly right in his diagnosis. So Daniel, does this
> query run fine on Drill 1.19 with store.mongo.all_text_mode = true?
>
> Thanks
> James
>
> On 2022/02/01 05:39, luoc wrote:
>
> Hi Daniel,
> I should guess the reason for this case, but I need to verify it first.
> Basically, under the full text, the ObjectId exists as a key-value pair
> (Map structure), not as a character or number value.
>
> On Jan 31, 2022, at 23:45, Daniel Clark<cl...@gmail.com>
> <cl...@gmail.com> wrote:
>
>
> Here it is. Please see the attached file.
>
> On Mon, Jan 31, 2022 at 4:22 AM James Turton<dz...@apache.org>
> <dz...@apache.org> wrote:
> Please also attach the query profile if you can.
>
> Thanks
> James
>
>
>
>
Re: 1.20.0-SNAPSHOT: UserRemoteException: SYSTEM ERROR: UnsupportedOperationException
Posted by James Turton <dz...@apache.org>.
If you feel like playing around you can try out this variation.
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 convert_to(`Elements`.`_id`, 'JSON') =
convert_to(`Elements_Efforts`.`_id`, 'JSON')
WHERE (`Elements`.`PlanID` = '1623263140')
GROUP BY `Elements_Efforts`.`EffortTypeName`,
`Elements`.`ElementSubTypeName`,
`Elements`.`ElementTypeName`,
`Elements`.`PlanID`
On 2022/02/01 11:08, James Turton wrote:
> I think luoc is exactly right in his diagnosis. So Daniel, does this
> query run fine on Drill 1.19 with store.mongo.all_text_mode = true?
>
> Thanks
> James
>
> On 2022/02/01 05:39, luoc wrote:
>> Hi Daniel,
>> I should guess the reason for this case, but I need to verify it
>> first. Basically, under the full text, the ObjectId exists as a
>> key-value pair (Map structure), not as a character or number value.
>>
>>> On Jan 31, 2022, at 23:45, Daniel Clark<cl...@gmail.com> wrote:
>>>
>>>
>>> Here it is. Please see the attached file.
>>>
>>>> On Mon, Jan 31, 2022 at 4:22 AM James Turton<dz...@apache.org> wrote:
>>>> Please also attach the query profile if you can.
>>>>
>>>> Thanks
>>>> James
>
Re: 1.20.0-SNAPSHOT: UserRemoteException: SYSTEM ERROR: UnsupportedOperationException
Posted by James Turton <dz...@apache.org>.
I think luoc is exactly right in his diagnosis. So Daniel, does this
query run fine on Drill 1.19 with store.mongo.all_text_mode = true?
Thanks
James
On 2022/02/01 05:39, luoc wrote:
> Hi Daniel,
> I should guess the reason for this case, but I need to verify it first. Basically, under the full text, the ObjectId exists as a key-value pair (Map structure), not as a character or number value.
>
>> On Jan 31, 2022, at 23:45, Daniel Clark<cl...@gmail.com> wrote:
>>
>>
>> Here it is. Please see the attached file.
>>
>>> On Mon, Jan 31, 2022 at 4:22 AM James Turton<dz...@apache.org> wrote:
>>> Please also attach the query profile if you can.
>>>
>>> Thanks
>>> James