-
Using the partition type as a parameter
Hi all,
I have a date-partitioned table called Orders, and am looking to get the number of rows for a certain date, within a lambda.
Would anyone be able to shed some light on the below, the type error and the result = 0 cases? It looks like using date as a parameter is causing an issue when we’ve loaded a date-partitioned database, but would be good to understand the first two results.
q)// Using 4.0 2020.06.18
q)
q){[t;date] ?[t;enlist(=;
date;date);0b;(enlistcnt)!enlist(count;i)]}[Orders;2024.07.15]'type
[1] {[t;date] ?[t;enlist(=;
date;date);0b;(enlistcnt)!enlist(count;i)]}</p><p> ^</p><p>q))\</p><p>q){[t;date] ?[t;enlist(=;date;enlist date);0b;(enlistcnt)!enlist(count;i)]}[Orders;2024.07.15]</p><p>cnt</p><p>---</p><p>0</p><p>q){[t;dt] ?[t;enlist(=;date;dt);0b;(enlistcnt)!enlist(count;i)]}[`Orders;2024.07.15]cnt
--------
86618327
-
7 Replies
-
Hi @eohara_kdb
Apologies, due to formatting issues with our editor, can I ask that you send forward a screenshot of your code instead? 🙂
We are currently working to get this issue resolved for future questions.
Many thanks,
Megan
-
Hi eohara_kdb, have you tried the same with a table in a q process and does this error only occur on a HDB?
-
Hi @unterrainer_ale , it seems that the error only occurs when I’m querying a HDB table and when the parameter used in the where clause to filter on my partition column has the same name as the virtual column. Some different variations of the query in attached screenshot
-
I just replicated the error myself, so it looks like indeed, when you use the same name for the parameter of your functional select as the virtual column (date in this case) it doesn’t work. What you should keep in mind is that when you are on a hdb, the virtual column, date is actually loaded into memory, meaning, the variable date exists. If you just run date on your HDB it will output all available dates. This also impacts your second question, where you asked about count 0. You have an enlist in your where clause (enlist date), and on the HDB I tested, it actually returned 1 single record. If I run the same with enlist dt, it throws a length error
-
-
-
It is advised to avoid using name of column or globals as function param names as much as possible. Here you are seeing issues as a result of this.
In a HDB the issues caused by this scoping confusion can differ from in memory tables.
This is due to map-reduce in the HDB, which means the query execution path is more complicated.
1. type error:
In the query path date would be expected to be a list but if the function local date atom is used a type error is being raised
q){[date] select cnt:count i from Orders where date=2024.07.15}[.z.d] //Errorq){[] select cnt:count i from Orders where date=2024.07.15}[.z.d] //Okay2. count = 0:
You enlist the data param in one of the uses which means = returns a list and does not get the type error of query 1. However this list value does not make sense so an unexpected value is returned.
Returns the count of the table in the first partition of the HDB
q){[date] select cnt:count i from Orders where date=enlist 2024.07.15}[2024.07.15]Same result
q){[date] select cnt:count i from Orders where date=2024.07.15}[enlist 2024.07.15]Returns the counts of the first 2 partitions
q){[date] select cnt:{enlist count x}i from Orders where date=.z.d}[2#.z.d]You don’t even need to put in dates as the date param is being compared to itself you can put anything in:
q){[date] select cnt:{enlist count x}i from Orders where date=1b}[11b]3. Works: Not using date as a param name – no issues
-
The main issue here is changing the .Q.pf (date) value while running the function in scope. Resulting in the error.
Each select query runs, .Q.ps, and while evaluating the where clause, the partition field which is expected to be a list, becomes an atom and (&) fails with type error
When you enlist it, it will work because “where eval c” will always return 1b, which eventually translates into the first partition.
d@:&-6!*c
You can override it to work, if you still want to use it in the function
f:{[date;tab]
c:enlist (=;
date;date);date:.Q.pv;
?[tab;c;date
sym!datesym;(enlistcnt)!enlist (count;`i)]}
Log in to reply.