1

I am trying to run a stored procedure by passing the query in it. My query is something like this:

SELECT DISTINCT VALUE f._self FROM collection f JOIN c IN f.data WHERE (c.Name= 'James') AND (c['DOB'] = '2020-02-02 00:00:00')

But after debugging it inside the console of Data explorer, I am getting output like this:

select distinct value f._self from collection f join c in f.data where (c.name= 'james') and (c['dob'] = '2020-02-02 00:00:00')

It makes the query in smaller case with parameters also. So with this I am not able to query the DB. Why is it behaving like this?

Anurag
  • 117
  • 10

1 Answers1

1

I can't reproduce your issue on my side.Per my experience,there is no such feature(changing your parameter behaviour) in cosmos db SP.

I tested with below SP code:

enter image description here

Never get smaller case:

enter image description here

enter image description here

Update Answer:

I tried your latest sql and reproduce your issue on my side.

enter image description here

And i tested the last sql(contains [Age) and still got lowercase.So,i believe i got correct print resulted from browser cache.

However,based on my below test,i would say that the print console log won't change the sql query result.

Sample data:

enter image description here

Result:

enter image description here

It seems that console log print doesn't affect sql query behavior,but it still confused users sometimes.

Jay Gong
  • 23,163
  • 2
  • 27
  • 32
  • Try this: `SELECT DISTINCT VALUE f._self FROM collection f JOIN c IN f.data WHERE (c.Name= 'James') AND (c['DOB'] = '2020-02-02 00:00:00')` – Anurag Mar 03 '20 at 16:11
  • I don't think it is working fine. I am using it for bulk deletion but it is not deleting anything. – Anurag Mar 04 '20 at 06:44
  • @Anurag Have you provide correct partition key? Or you just leave the pk empty. – Jay Gong Mar 04 '20 at 07:06
  • Partition key is empty. – Anurag Mar 04 '20 at 07:09
  • @Anurag Please set partition key when you execute SP, please see some explanations in this case:https://stackoverflow.com/questions/58718052/delete-documents-from-cosmos-using-query-without-partition-key-specification/58722090#58722090 – Jay Gong Mar 04 '20 at 07:13
  • @Anurag So when you leave it empty, SP only scan the `""` partition scope. – Jay Gong Mar 04 '20 at 07:15
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/208958/discussion-between-anurag-and-jay-gong). – Anurag Mar 04 '20 at 07:17
  • @Anurag Hi,if my answer is helpful for you,would you please mark it? Thank you! – Jay Gong Mar 09 '20 at 05:21
  • Done. I've one query which I asked in Chat. Can you please help? – Anurag Mar 11 '20 at 07:30
  • @Anurag Very Sorry for the delay! I can't get some notification from chat room if i leave there. I will post some reply over there. – Jay Gong Mar 12 '20 at 01:13
  • In my case, I am not getting any results from collection.queryDocuments() method. Any idea why is it so? – Anurag Mar 23 '20 at 09:51
  • @Anurag The first thing i can think is you provided wrong partition key if you check the sql is absolutely right and result could be searched normally in the portal UI. – Jay Gong Mar 23 '20 at 09:54
  • I am using portal UI only. In the query explorer it is showing results but when I try to run a storedProcedure for query Documents it is giving empty results. – Anurag Mar 23 '20 at 10:00
  • @Anurag Consider it is related to pk value,please. – Jay Gong Mar 23 '20 at 10:02
  • I am using Partition key this time – Anurag Mar 23 '20 at 10:11
  • I was able to successfully store the data using pk but while deletion it is returning empty results.(no deletion) – Anurag Mar 23 '20 at 10:12
  • My issue is same as this thread https://stackoverflow.com/questions/45003191/documentdb-stored-procedure-querydocument-returns-nothing – Anurag Mar 23 '20 at 10:29
  • Got it. Yes it was because of pk only. I was missing it one place. Thanks :) – Anurag Mar 23 '20 at 12:09
  • 1
    @Anurag Haha, it is totally resloved by yourself, well done! – Jay Gong Mar 23 '20 at 12:17