Hi James,
The PlanId column is a String data type. I tried removing the quotes from
the PlanId value below, and the query still returned no records.
with element as (
select
`_id`,
ElementTypeName,
ElementSubTypeName,
PlanId
FROM
`mongo.grounds`.`Elements`
where PlanId = 1623263140
), element_effort as (
select
`_id`,
EffortTypeName
FROM
`mongo.grounds`.`Elements_Efforts`
)
select
*
from
element
join
element_effort on element.`_id` = element_effort.`_id`
On Sun, Feb 6, 2022 at 1:24 PM James Turton <[email protected]> wrote:
> What is the data type of PlanId in Mongo? Is it an integer? If so, what
> happens if you remove the quotes from that last query?
>
> On 6 February 2022 03:43:20 GMT+02:00, Daniel Clark <[email protected]>
> wrote:
>>
>> Hi James,
>>
>> Yes, in Drill 1.19 the original query returns 82 records, where the
>> PlanId = '1623263140' .
>>
>> On Sat, Feb 5, 2022 at 2:13 AM James Turton <[email protected]> wrote:
>>
>>> Let's get this subset of the problem figured out first. Is there an
>>> "element" with that PlanId in Mongo?
>>>
>>> P.S. screenshots don't make it to the mailing list.
>>>
>>> On 2022/02/04 17:12, Daniel Clark wrote:
>>> > After running this query
>>> >
>>> > select
>>> > *
>>> > from
>>> > `mongo.grounds`.`Elements` element
>>> > where element.PlanId = '1623263140';
>>> >
>>> > Nothing is returned. Please see the attached screenshot.
>>> >
>>> > Screen Shot 2022-02-04 at 10.08.07 AM.png
>>> >
>>> > On Fri, Feb 4, 2022 at 10:03 AM James Turton <[email protected]
>>> > <mailto:[email protected]>> wrote:
>>> >
>>> > Do you get records from this query?
>>> >
>>> > select
>>> > *
>>> > from
>>> > `mongo.grounds`.`Elements` element
>>> > where element.PlanId = '1623263140';
>>> >
>>> > Incidentally, for the full query including the WHERE clause, the
>>> form
>>> > below is probably going to be a good deal faster than the ones
>>> we've
>>> > written so far. I reintroduced some backticks, just in case that
>>> was
>>> > causing any trouble.
>>> >
>>> > with element as (
>>> > select
>>> > `_id`,
>>> > ElementTypeName,
>>> > ElementSubTypeName,
>>> > PlanId
>>> > FROM
>>> > `mongo.grounds`.`Elements`
>>> > where PlanId = '1623263140'
>>> > ), element_effort as (
>>> > select
>>> > `_id`,
>>> > EffortTypeName
>>> > FROM
>>> > `mongo.grounds`.`Elements_Efforts`
>>> > )
>>> > select
>>> > *
>>> > from
>>> > element
>>> > join
>>> > element_effort on element.`_id` = element_effort.`_id`
>>> >
>>> > On 2022/02/03 17:05, Daniel Clark wrote:
>>> > > 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.
>>> >
>>>
>> --
> Sent from my Android device with K-9 Mail. Please excuse my brevity.
>
