coalesce | Communication framework for distributed JavaScript

You could pivot your data so that "timepoint" defines the columns, with all your tests on the rows. In order to perform this pivot without creating list-cols, we'll have to group by "timepoint" and create an index for each row within the group:

Your subquery e is ordered by start_date, which is non-deterministic as there can be several lines with the same value.

With MySQL 5.7:

1. The first row of this subquery is the fake one you created via your union. Its from_date is 2022-02-28 10:00:00 and its end_date is 2022-02-28 10:00:00, which is going to be the Available_from of the next row because of your query usage of @lasttime_to.
2. Next row is the one related to appointment 18382 which becomes "from 2022-02-28 10:00:00 to 2022-02-28 10:00:00", with an end_date set to 2022-02-28 10:30:00, which is going to be the Available_from of the next row.
3. Next row is the one related to appointment 18517 which becomes "from 2022-02-28 10:30:00 to 2022-02-28 10:30:00"

So here, none of these 3 lines matches the criteria Available_to > DATE_ADD(Available_from, INTERVAL 14 MINUTE);

With MySQL 8:

1. The first row of this subquery is the one related to the appointment 18382 instead of the fake one. Its from_date is 2022-02-28 10:00:00 and its end_date is 2022-02-28 10:30:00, which is going to be the Available_from of the next row because of your query usage of @lasttime_to.
2. Next row is the fake one you created via your union. It becomes "2022-02-28 10:30:00 2022-02-28 10:00:00" (!), and its end_date is 2022-02-28 10:00:00, which is going to be the Available_from of the next row.
3. Next row is the one related to appointment 18517, which becomes "2022-02-28 10:00:00 to 2022-02-28 10:30:00".

Here, this third row matches the criteria Available_to > DATE_ADD(Available_from, INTERVAL 14 MINUTE);, which is why you get an additional row.

TL;DR: The Available_from values of your rows rely on a non-deterministic order obtained by the subquery e. Your results are then not consistent from an engine to another.

You should have those two indexes to speed up your query :

This is possible by renaming the single parquet file in the dataset so that it always has the same file name, that way the export task will overwrite the previous file in the external system.

This can be done using raw file system access. The write_single_named_parquet_file function below validates its inputs, creates a file with a given name in the output dataset, then copies the file in the input dataset to it. The result is a schemaless output dataset that contains a single named parquet file.

Notes

• The build will fail if the input contains more than one parquet file, as pointed out in the question, calling .coalesce(1) (or .repartition(1)) is necessary in the upstream transform
• If you require transaction history in your external store, or your dataset is much larger than 512 MB this method is not appropriate, as only the latest version is kept, and you likely want multiple parquet files for use in your downstream system. The createTransactionFolders (put each new export in a different folder) and flagFile (create a flag file once all files have been written) options can be useful in this case.
• The transform does not require any spark executors, so it is possible to use @configure() to give it a driver only profile. Giving the driver additional memory should fix out of memory errors when working with larger datasets.
• shutil.copyfileobj is used because the 'files' that are opened are actually just file objects.

Full code snippet

example_transform.py

Finally found a way to make it work using preload instead of includes. We wanted to avoid having seperate queries to load posts and categories but since performance is not affected by it, we don't mind it.

Here is how it look like:

jq has a counterpart to "coalesce" in the form of //. For example, null // 0 evaluates to 0, and chances are that it will suffice in your case, perhaps:

Before the left_join, the column should be assigned (:=) instead of ==

You could use transmute, e.g.

You should cross join the table to the distinct dates also: