DB::Exception: Unknown expression identifier '$1' in scope SELECT $1

[Seredenko-V]

Hello
I try to work with scalar data by analogy with the ClickHouse repository. To do this, I made a patch in which I supplemented the list of package types from the client with the value ClientCodes::Scalar. The number of this value is the same as the ServerCodes::Totals, as it was in the ClickHouse repository.

/// Types of packets received from server
namespace ServerCodes {
    enum {
        Hello                = 0,    /// Name, version, revision.
        Data                 = 1,    /// `Block` of data, may be compressed.
        Exception            = 2,    /// Exception that occurred on server side during query execution.
        Progress             = 3,    /// Query execcution progress: rows and bytes read.
        Pong                 = 4,    /// response to Ping sent by client.
        EndOfStream          = 5,    /// All packets were sent.
        ProfileInfo          = 6,    /// Profiling data
        Totals               = 7,    /// Block of totals, may be compressed.
        Extremes             = 8,    /// Block of mins and maxs, may be compressed.
        TablesStatusResponse = 9,    /// Response to TableStatus.
        Log                  = 10,   /// Query execution log.
        TableColumns         = 11,   /// Columns' description for default values calculation
        PartUUIDs            = 12,   /// List of unique parts ids.
        ReadTaskRequest      = 13,   /// String (UUID) describes a request for which next task is needed
                                     /// This is such an inverted logic, where server sends requests
                                     /// And client returns back response
        ProfileEvents        = 14,   /// Packet with profile events from server.
    };
}

/// Types of packets sent by client.
namespace ClientCodes {
    enum {
        Hello       = 0,    /// Name, version, default database name.
        Query       = 1,    /** Query id, query settings, query processing stage,
                              * compression status, and query text (no INSERT data).
                              */
        Data        = 2,    /// Data `Block` (e.g. INSERT data), may be compressed.
        Cancel      = 3,    /// Cancel query.
        Ping        = 4,    /// Check server connection.
        Scalar      = 7,    /// Work with Block as scalar. NEW VALUE IS SAME ServerCodes::Totals
    };
}

I also added a bool is_scalar parameter to the Client::Impl::SendData to determine the type of record: scalar or data

void Client::Impl::SendData(const Block& block, const std::string& table_name, const bool is_scalar) {
    if (is_scalar) {
        WireFormat::WriteUInt64(*output_, ClientCodes::Scalar);
    } else {
        WireFormat::WriteUInt64(*output_, ClientCodes::Data);
    }

    if (server_info_.revision >= DBMS_MIN_REVISION_WITH_TEMPORARY_TABLES) {
        WireFormat::WriteString(*output_, table_name);
    }

    if (compression_ == CompressionState::Enable) {
        std::unique_ptr<OutputStream> compressed_output = std::make_unique<CompressedOutput>(output_.get(), options_.max_compression_chunk_size, options_.compression_method);
        BufferedOutput buffered(std::move(compressed_output), options_.max_compression_chunk_size);

        WriteBlock(block, buffered);
    } else {
        WriteBlock(block, *output_);
    }

    output_->Flush();
}

and added const std::map<std::string, Block>& blocks to the Client::Execute for working with temp tables and scalars

void Client::Execute(const Query& query, const std::map<std::string, Block>& blocks, const bool is_scalar) {
    impl_->ExecuteQuery(query, blocks, is_scalar);
}

Here is an example of using working with a scalar

int main() {
    Block answer;
    auto handler = [&answer](const Block& block){
        if( block.GetColumnCount() > 0 && block.GetRowCount() > 0 )
            answer = block;
    };

    Query query( "SELECT $1 AS test"s );
    query.OnData( handler );

    auto value = std::make_shared< ColumnFloat64 >();
    value->Append(3.14);
    Block block;
    block.AppendColumn("column"s, value);
    map<string, Block> blocks { {""s, block} };

    Client client( ClientOptions().SetHost( "localhost" ) );
    client.Execute( query, blocks, true );
    cout << answer[0]->AsStrict<ColumnFloat64>()->At(0) << endl;

    return 0;
}

As a result of executing this query, I expect a block with a single Float64 value, but when running this example, an exception is thrown with the text DB::Exception: Unknown expression identifier '$1' in scope SELECT '$1' AS test. Tell me please how to work with scalar?

[Enmk]

Hi @Seredenko-V ! This looks like a half-baked PR, could you please create one, so it would be much easier to reason about without trying to piece everything together manually?

[Seredenko-V]

@Enmk, thank you for answering) The whole solution is there

[Enmk]

Yeah, sorry that link isn't helpful either.... Could you please create a PR into this repository, so it can be executed against CI/CD and it is easier to examine the changes you've introduced?