Magento 2 dynamic custom column in UI listing, sorting not working

This is because you don't have a column in the database that matches the name of your two added fields. Magento takes the name from the xml file in which you specify the grid view and when you want to sort by this column it performs a database query in which it takes the column name from the xml file and tries to add it to the SQL query.

All you have to do is override method \Magento\Ui\Component\Listing\Columns\Column::applySorting() You can do it simply by modifying your xml like that:

<?xml version="1.0" encoding="UTF-8"?>
<listing xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="urn:magento:module:Magento_Ui:etc/ui_configuration.xsd">
    <columns name="sales_order_columns">
        <column name="your_column" class="Vendor\Module\Ui\Component\YourColumn">
            <settings>
                <sortable>true</sortable>
            </settings>
        </column>
    </columns>
</listing>

And write your own class Vendor\Module\Ui\Component\YourColumn that extends \Magento\Ui\Component\Listing\Columns\Column. I think something like that should work:

<?php

declare(strict_types=1);

namespace Vendor\Module\Ui\Component;

use Magento\Ui\Component\Listing\Columns\Column;

class YourColumn extends Column
{
    /**
     * Apply sorting
     *
     * @return void
     */
    protected function applySorting()
    {
        $sorting = $this->getContext()->getRequestParam('sorting');
        $isSortable = $this->getData('config/sortable');
        if (
            $isSortable !== false
            && !empty($sorting['field'])
            && !empty($sorting['direction'])
            && $sorting['field'] === $this->getName()
        ) {
            $this->getContext()->getDataProvider()->addOrder(
                'enter here your db column name by which you want to sort',
                strtoupper($sorting['direction'])
            );
        }
    }
}