unexpected results for incrementing counter

[jjdeluxe]

I am not sure what's happening. I've simplified the issue with this example.. In this example, I want to index each row. I've started to use csvq for distilling large-ish datasets with good success, but this issue (and I have a few other questions for another discussion) is a bit of a set back. running on a modern mac, macos 12.3.1

Simple query:

$ csvq 'var @i := 0; select *, @i := @i + 1 as i limit 10' < data.csv
+--------+------------+--------+-----+
| METRIC |    UNIT    | FAMILY |  i  |
+--------+------------+--------+-----+
| 28.5   | 220530482  | VANKAB |   1 |
| 0.1    | 220660173  | DCNKA9 |  71 |
| 108    | 220860039  | MANK59 |  76 |
| 39.1   | 220860080  | MANK4V |  78 |
| 50.9   | 220860094  | MANKEP |  79 |
| 0      | 220860107  | MANK59 |  80 |
| 14.1   | 220860182  | 5BNK2Z |  81 |
| 33.1   | 220860201  | LINKC4 |  82 |
| 24.1   | 220860202  | LINKC4 |  92 |
| 5.2    | 220860206  | LINKC4 |  88 |
+--------+------------+--------+-----+

And then run the exact same query on the same data and get a different, yet equally nonsensical (to me) result.

$ csvq 'var @i := 0; select *, @i := @i + 1 as i limit 10' < data.csv
+--------+------------+--------+-----+
| METRIC |    UNIT    | FAMILY |  i  |
+--------+------------+--------+-----+
| 28.5   | 220530482  | VANKAB |   2 |
| 0.1    | 220660173  | DCNKA9 |   5 |
| 108    | 220860039  | MANK59 |   6 |
| 39.1   | 220860080  | MANK4V |   7 |
| 50.9   | 220860094  | MANKEP |   8 |
| 0      | 220860107  | MANK59 |   9 |
| 14.1   | 220860182  | 5BNK2Z |  10 |
| 33.1   | 220860201  | LINKC4 |  11 |
| 24.1   | 220860202  | LINKC4 |  12 |
| 5.2    | 220860206  | LINKC4 |  13 |
+--------+------------+--------+-----+

Every time I get a different result. Not only that, sometimes the counter "i" is repeated more that once! (threading issue?)

see attached sample dataset

data.csv

[mithrandie]

Ah, that may be a threading issue.

If you just want to index each row, the ROW_NUMBER function is the more appropriate method in the current version.

$ csvq 'select *, row_number() over () as i limit 10' < data.csv
+--------+------------+--------+-----+
| METRIC |    UNIT    | FAMILY |  i  |
+--------+------------+--------+-----+
| 28.5   | 220530482  | VANKAB |   1 |
| 0.1    | 220660173  | DCNKA9 |   2 |
| 108    | 220860039  | MANK59 |   3 |
| 39.1   | 220860080  | MANK4V |   4 |
| 50.9   | 220860094  | MANKEP |   5 |
| 0      | 220860107  | MANK59 |   6 |
| 14.1   | 220860182  | 5BNK2Z |   7 |
| 33.1   | 220860201  | LINKC4 |   8 |
| 24.1   | 220860202  | LINKC4 |   9 |
| 5.2    | 220860206  | LINKC4 |  10 |
+--------+------------+--------+-----+

$ csvq 'select *, row_number() over (order by metric asc, unit asc) as i limit 10' < data.csv
+--------+------------+--------+-----+
| METRIC |    UNIT    | FAMILY |  i  |
+--------+------------+--------+-----+
| 0      | 220860107  | MANK59 |   1 |
| 0      | 220860200  | LINKC4 |   2 |
| 0      | 220860424  | NJNKFF |   3 |
| 0      | 220860475  | MDNK11 |   4 |
| 0      | 220860554  | LINKGC |   5 |
| 0      | 220860562  | 5BNK5M |   6 |
| 0      | 220860577  | 5BNK2X |   7 |
| 0      | 220860681  | NJNKDV |   8 |
| 0      | 220860714  | NJNKE3 |   9 |
| 0      | 220860717  | NJNKE3 |  10 |
+--------+------------+--------+-----+

If you want to do more complex processing and need to use variables, you can force single-threaded processing by specifying the command option --cpu with 1 as a workaround.

$ csvq --cpu 1 'var @i := 0; select *, @i := @i + 1 as i limit 10' < data.csv
+--------+------------+--------+-----+
| METRIC |    UNIT    | FAMILY |  i  |
+--------+------------+--------+-----+
| 28.5   | 220530482  | VANKAB |   1 |
| 0.1    | 220660173  | DCNKA9 |   2 |
| 108    | 220860039  | MANK59 |   3 |
| 39.1   | 220860080  | MANK4V |   4 |
| 50.9   | 220860094  | MANKEP |   5 |
| 0      | 220860107  | MANK59 |   6 |
| 14.1   | 220860182  | 5BNK2Z |   7 |
| 33.1   | 220860201  | LINKC4 |   8 |
| 24.1   | 220860202  | LINKC4 |   9 |
| 5.2    | 220860206  | LINKC4 |  10 |
+--------+------------+--------+-----+

[jjdeluxe]

thanks for the speedy feedback.