v0.8.29

Built against Mojo 1.0.0 (ed45d567).

A patch release. SQL reads GROUPING SETS, CUBE, ROLLUP and GROUPING(), and a MAP literal parses. A string column can now be held as codes into its distinct values, and take, filter, a comparison, is_in and a group by read it without decoding it, while the scan decodes one morsel at a time for everything else. Two ClickBench shapes got faster through the planner: a group by on several keys with text among them keeps a map across chunks, which took q18 from 80 to 48 ms and q39 from 112 to 67 ms at 1M, and a sum of a column and a constant is answered from one sum and one count, which took q29 from 4.7 times the hand written port to faster than it. On the pandas side, a power across two labelled operands and the whole frame sum and prod answer what pandas answers where there is a gap or a NaN, and a frame reduction takes skipna=False and min_count.

Changed: take, filter, compare, is_in and group by read an encoded string column without decoding it

take_any, gather_any and filter_any move the codes of a dictionary encoded string column and hand back a column that is still encoded, so moving its rows costs four bytes a row instead of sixteen and the string bytes are never touched. A comparison with a constant and is_in work out the answer once per distinct value and spread it over the rows by code, factorize_any groups on the codes, and DataFrame.group_by does both and decodes only the keys it returns, one row a group. On six million rows over seven distinct values on a six core Linux machine the column is 24 MB against 96 MB flat, a filter took 1.0 to 1.3 ms against 12, is_in 7 ms against 25 and a group by with a sum 24 ms against 65, while an equality with a constant was within noise of flat at 7 ms. This is the third box of #979.

Changed: a query decodes an encoded column one morsel at a time

The pipeline's scan now decodes a column held in any encoding but flat as it hands each morsel out, instead of every operator refusing it. The flat copy only ever exists a morsel at a time, so a query over a frame of dictionary encoded strings pays for a few megabytes of views per morsel and not for the whole column. An operator taught to read the codes will skip this, one operator at a time (#979).

Added

• A MAP literal such as MAP {'a': 1} now reads and prints back instead of being refused while the query is read. Its keys and values are expressions like any other. The plan still refuses it by name, because no firepanda column holds a map yet.

• GROUPING SETS, CUBE and ROLLUP in SQL, alone or beside plain keys, and the GROUPING() function (also spelled grouping_id) that says which keys a row left out. Each grouping set is its own aggregate over a copy of the input, the keys a set leaves out come back as null, and the sets are stacked with a union that keeps every row, so a set written twice gives its rows twice the way DuckDB does. GROUPING(a, b) is a whole number per set with the first argument as the highest bit, and on a plain GROUP BY it is 0. A GROUPING with no groups, or over a column that is not a key, fails with DuckDB's binder message.

Added: a string column can be held as codes into its distinct values

AnyArray.dictionary_encoded(codes, categories) builds a string column whose rows are int32 codes into a list of distinct strings, with Encoding.DICTIONARY on it and dtype still string. That last part is what separates it from the category type: nothing the user can see changes. decoded() turns it back into a flat column by copying one sixteen byte view per row and sharing the categories' payload, so no string bytes are copied. slice and window keep it encoded and nbytes counts the codes plus the categories. Ten million rows over seventeen strings of about thirty bytes weigh 41 MB this way against 485 MB flat, and decoding them took 41 ms on a six core Linux machine. Nothing builds one yet outside the tests; the Arrow and Parquet readers are the next step (#979).

Added: every read of a column's values asks whether the column is flat first

AnyArray.require_flat() raises unless the column is held flat, and AnyArray.decoded() returns a flat column whatever it is held as. check_dtype, strings and into_strings call the first, so as_typed, as_typed_view and into_typed do too, and so does dispatch, whose kernels read through unsafe_ptr and check nothing themselves. The two Arrow exporters read buffers straight out of the column and ask the same question before they do. This is the second box of #979. When a dictionary encoded string column lands, a kernel nobody taught about it raises and names decoded() instead of reading codes as though they were values, and it does not have to remember to ask.

Every column is flat today, so none of these can raise yet, and decoded() on a flat column shares its buffers rather than copying them. The check is one compare a column per kernel call, never one a row.

Changed

• A whole input sum of a signed integer column and a constant, SUM(x + c), SUM(x - c), SUM(c - x) or SUM(x * c), is answered from the sum and the count of x, which are read once a chunk however many sums ask, rather than by building a column for every sum. A count under any of those is the count of x. The operation runs in its own type and can wrap there, so in a type narrower than 64 bits the chunk's smallest and largest values are checked against that type first and the column is built as before when they do not fit, which keeps the answer the same to the bit. ClickBench q29, ninety sums of ResolutionWidth plus ninety constants, now runs faster through the planner than the hand written port that does the ninety additions, where it was 4.7 times slower.
• A streaming group by on two keys or more, when one of them is text, keeps its groups in a map that lasts across chunks rather than stacking the running table with every chunk's table and grouping the two again. Each row's tuple is written out as bytes, a presence byte per key and a length in front of text, on the cores, and looked up in the text map the single key route already used, so a null in a key no longer matters to the route either. ClickBench q18 at 1M went from 80.2 to 48.2 ms and q39 from 112.2 to 67.4 ms through the SQL planner, and the planner's total over the 43 queries against the hand written port went from 1.42 to 1.28, with all 43 still agreeing. A table that arrives in one chunk and a tuple of integers alone stay on the old route, because the map inserts its groups one at a time and loses to one parallel pass there. A float key does too.

Fixed

• A power between two labelled operands answers what pandas answers in a row only one side has, which is 1 where numpy's 1 ** nan or nan ** 0 is 1 and NaN everywhere else. The kernel answers a gap with a null before it looks at either value, so the pandas facing layer lines the two sides up by label with NaN in each gap and takes the power again. That covers a series with a series and a frame with a frame, through **, its reflection and pow and rpow without a fill_value. A side with a repeated label cannot be lined up that way and keeps the NaN it had. (Issue #8)

• sum and prod over a whole frame keep a NaN one column's own arithmetic made, such as inf plus -inf, where they skipped it and answered the total of the other columns. The whole frame form is the per column pass run again over its answers, and the second pass read that NaN as a missing value. A missing cell is already skipped by the first pass, so for a total or a product the second pass no longer skips anything. min and max still skip there, because their NaN is a column with nothing in it. (Issue #8)

• A frame reduction takes skipna=False and min_count, which it refused by name. Each is a rule about the answer, the same as on a series, so the kernel reduces every column as before and a column the rule catches is answered NaN after: a column with a missing value under skipna=False, or one with fewer values than min_count. The answer widens to float64 only when a column is caught, which is what pandas does, and with axis=None the floor counts every cell in the frame rather than each column. A caught column whose answer is not a number is refused, because pandas answers NaT or an object NaN there. (Issue #8)

Note added after the release: the q29 change (#1041) is taken out again by #1044, because the planner's own rule is that q29 must fuse the expression into the reduction rather than answer it with algebra. It will be gone in the next release.