[YCQL] system_schema.functions undefined column 'body'

[ybnelson]

Jira Link: DB-4672
For migrating Cortex, https://github.com/yugabyte/cortex, a project which uses CQL and Cassandra 3.11, it expects the following layout of system_schema.functions:

cqlsh> SELECT * FROM system_schema.functions;

 keyspace_name | function_name | argument_types | argument_names | body | called_on_null_input | language | return_type
---------------+---------------+----------------+----------------+------+----------------------+----------+-------------
cqlsh> DESCRIBE system_schema.functions;
CREATE TABLE system_schema.functions (
    keyspace_name text,
    function_name text,
    argument_types frozen<list<text>>,
    argument_names frozen<list<text>>,
    body text,
    called_on_null_input boolean,
    language text,
    return_type text,
    PRIMARY KEY (keyspace_name, function_name, argument_types)
) WITH CLUSTERING ORDER BY (function_name ASC, argument_types ASC)
    AND bloom_filter_fp_chance = 0.01
    AND caching = {'keys': 'ALL', 'rows_per_partition': 'NONE'}
    AND comment = 'user defined function definitions'
    AND compaction = {'class': 'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy', 'max_threshold': '32', 'min_threshold': '4'}
    AND compression = {'chunk_length_in_kb': '64', 'class': 'org.apache.cassandra.io.compress.LZ4Compressor'}
    AND crc_check_chance = 1.0
    AND dclocal_read_repair_chance = 0.0
    AND default_time_to_live = 0
    AND gc_grace_seconds = 604800
    AND max_index_interval = 2048
    AND memtable_flush_period_in_ms = 3600000
    AND min_index_interval = 128
    AND read_repair_chance = 0.0
    AND speculative_retry = '99PERCENTILE';

From YBDB 2.1.1 instance:

cqlsh> SELECT * FROM system_schema.functions;

 keyspace_name | function_name | argument_types | argument_names | called_on_null_input | language | return_type
---------------+---------------+----------------+----------------+----------------------+----------+-------------

Error generated:
level=error ts=2020-03-09T20:12:31.75034073Z caller=table_manager.go:188 msg="error syncing tables" err="Undefined Column. Column doesn't exist\n\n\t\tSELECT\n\t\t\tfunction_name,\n\t\t\targument_types,\n\t\t\targument_names,\n               ^^^\n\t\t\tbody,\n^^^^\n\t\t\tcalled_on_null_input,\n\t\t\tlanguage,\n\t\t\treturn_type\n\t\tFROM system_schema.functions\n\t\tWHERE keyspace_name = ?\n (ql error -203)"

[kmuthukk]

@yb-andrew

This worked fine for me in 2.0.10.

cqlsh> SELECT * FROM system_schema.functions;

 keyspace_name | function_name | argument_types | argument_names | called_on_null_input | language | return_type
---------------+---------------+----------------+----------------+----------------------+----------+-------------

(0 rows)
cqlsh> exit

I don't see the error you are seeing. Can you double check if it is specific to 2.1.1 and not 2.0.10 (that I tried)?

Also, the PRIMARY KEY is different in YugabyteDB. It has two columns (as shown below), whereas in the output you showed (is that from Apache Cassandra?) -- the PRIMARY KEY has three columns.

CREATE TABLE system_schema.functions (
    keyspace_name text,
    function_name text,
    argument_types list<text>,
    argument_names list<text>,
    called_on_null_input boolean,
    language text,
    return_type text,
    PRIMARY KEY (keyspace_name, function_name)
) WITH CLUSTERING ORDER BY (function_name ASC)

[ybnelson]

The given 'select...' command completes successfully, but the cortexproject (table_manager.go) is expecting a 'body' column which is not present in the Yugabyte 2.0.10 or 2.1.1 when it tries to query its cassandra data source.
I added the table spec from a Cassandra 3.11 instance for comparison. Only the body column is missing from the Yugabyte implementation. If you have docker installed, you can verify with:
docker run -d --name cassandra --rm -p 9042:9042 cassandra:3.11
Then run cqlsh against that local instance on 127.0.0.1
cqlsh> select * from system_schema.functions;

[marcusbooyah]

I'm working on getting Cortex to work on Yugabyte right now and ran into the same issue, as well as another discrepancy. The other is that system_schema.aggregates is missing the return_type column of type TEXT.

Is the plan here to add these columns? I'd happily make the changes and submit a PR.

[bmatican]

Good note @marcusbooyah ! This sounds like a relatively simple change to do, we'd be happy to take in a PR for it!

Looking back, we did setup an upgrade path, specifically to cover for such changes in the future, in case the system table situation evolved. Here's an example commit doing just that, albeit a long time back: 2a4fb09

cc @m-iancu