AssertionError: Field ordinal X is invalid for type Y

Hello everyone,

I have been running my queries for several months without issues. However since some days I repeatedly get errors like the following:

AssertionError: Field ordinal 9 is invalid for type 'RecordType(BIGINT id)'

The queries are run on a PSQL-DB (of Alfresco).

Does anyone know how to resolve this?
Today I upgraded to Dremio 20.0 but the error persists.

Updates:

  • Running the same query directly on the PSQL-DB does not lead to any problems.
  • For testing I switched back to 4.9.1-202010230218060541-2e764ed0 → the query works here!

best
Muffex

48e77773-2a93-4538-a3cd-fd2915b423a8.zip (13.6 KB)

@Muffex Can you try running the below 4 commands and then retry query and see if it wporks?

ALTER PDS Alfresco.public.alf_node REFRESH METADATA FORCE UPDATE
ALTER PDS Alfresco.public.alf_qname REFRESH METADATA FORCE UPDATE
ALTER PDS Alfresco.public.alf_child_assoc REFRESH METADATA FORCE UPDATE
ALTER PDS Alfresco.public.alf_node_properties REFRESH METADATA FORCE UPDATE

Hello @balaji.ramaswamy,

thank you very much for your reply! Your four commands were executed successfully.

However, my query fails with the same error.

Updates:

  • I replaced the persistent data of my 20.0 instance with the (working) data of my 4.9.1 instance (and ran: /opt/dremio/bin/dremio-admin upgrade) → same error as before

best
Muffex

@Muffex Is there a PG table used in the query that has a column called ID? would you be able to send the CREATE TABLE DDL for the PG tables involved in this query?

Hello @balaji.ramaswamy,

the tables alf_child_assoc, alf_node and alf_qname contain “id” columns.

DDLs of the four involved tables:

alf_child_assoc

CREATE TABLE public.alf_child_assoc (
	id int8 NOT NULL,
	"version" int8 NOT NULL,
	parent_node_id int8 NOT NULL,
	type_qname_id int8 NOT NULL,
	child_node_name_crc int8 NOT NULL,
	child_node_name varchar(50) NOT NULL,
	child_node_id int8 NOT NULL,
	qname_ns_id int8 NOT NULL,
	qname_localname varchar(255) NOT NULL,
	qname_crc int8 NOT NULL,
	is_primary bool NULL,
	assoc_index int4 NULL,
	CONSTRAINT alf_child_assoc_pkey PRIMARY KEY (id)
);
CREATE INDEX fk_alf_cass_cnode ON public.alf_child_assoc (child_node_id);
CREATE INDEX fk_alf_cass_qnns ON public.alf_child_assoc (qname_ns_id);
CREATE INDEX fk_alf_cass_tqn ON public.alf_child_assoc (type_qname_id);
CREATE INDEX idx_alf_cass_pnode ON public.alf_child_assoc (parent_node_id,assoc_index,id);
CREATE INDEX idx_alf_cass_pri ON public.alf_child_assoc (parent_node_id,is_primary,child_node_id);
CREATE INDEX idx_alf_cass_qncrc ON public.alf_child_assoc (qname_crc,type_qname_id,parent_node_id);
CREATE UNIQUE INDEX parent_node_id ON public.alf_child_assoc (parent_node_id,type_qname_id,child_node_name_crc,child_node_name);


-- public.alf_child_assoc foreign keys

ALTER TABLE public.alf_child_assoc ADD CONSTRAINT fk_alf_cass_cnode FOREIGN KEY (child_node_id) REFERENCES public.alf_node(id);
ALTER TABLE public.alf_child_assoc ADD CONSTRAINT fk_alf_cass_pnode FOREIGN KEY (parent_node_id) REFERENCES public.alf_node(id);
ALTER TABLE public.alf_child_assoc ADD CONSTRAINT fk_alf_cass_qnns FOREIGN KEY (qname_ns_id) REFERENCES public.alf_namespace(id);
ALTER TABLE public.alf_child_assoc ADD CONSTRAINT fk_alf_cass_tqn FOREIGN KEY (type_qname_id) REFERENCES public.alf_qname(id);

alf_node

CREATE TABLE public.alf_node (
	id int8 NOT NULL,
	"version" int8 NOT NULL,
	store_id int8 NOT NULL,
	uuid varchar(36) NOT NULL,
	transaction_id int8 NOT NULL,
	type_qname_id int8 NOT NULL,
	locale_id int8 NOT NULL,
	acl_id int8 NULL,
	audit_creator varchar(255) NULL,
	audit_created varchar(30) NULL,
	audit_modifier varchar(255) NULL,
	audit_modified varchar(30) NULL,
	audit_accessed varchar(30) NULL,
	CONSTRAINT alf_node_pkey PRIMARY KEY (id)
);
CREATE INDEX fk_alf_node_acl ON public.alf_node (acl_id);
CREATE INDEX fk_alf_node_loc ON public.alf_node (locale_id);
CREATE INDEX fk_alf_node_store ON public.alf_node (store_id);
CREATE INDEX idx_alf_node_cor ON public.alf_node (audit_creator,store_id,type_qname_id,id);
CREATE INDEX idx_alf_node_crd ON public.alf_node (audit_created,store_id,type_qname_id,id);
CREATE INDEX idx_alf_node_mdq ON public.alf_node (store_id,type_qname_id,id);
CREATE INDEX idx_alf_node_mod ON public.alf_node (audit_modified,store_id,type_qname_id,id);
CREATE INDEX idx_alf_node_mor ON public.alf_node (audit_modifier,store_id,type_qname_id,id);
CREATE INDEX idx_alf_node_tqn ON public.alf_node (type_qname_id,store_id,id);
CREATE INDEX idx_alf_node_txn ON public.alf_node (transaction_id);
CREATE INDEX idx_alf_node_txn_type ON public.alf_node (transaction_id,type_qname_id);
CREATE INDEX idx_alf_node_ver ON public.alf_node ("version");
CREATE UNIQUE INDEX store_id ON public.alf_node (store_id,uuid);


-- public.alf_node foreign keys

ALTER TABLE public.alf_node ADD CONSTRAINT fk_alf_node_acl FOREIGN KEY (acl_id) REFERENCES public.alf_access_control_list(id);
ALTER TABLE public.alf_node ADD CONSTRAINT fk_alf_node_loc FOREIGN KEY (locale_id) REFERENCES public.alf_locale(id);
ALTER TABLE public.alf_node ADD CONSTRAINT fk_alf_node_store FOREIGN KEY (store_id) REFERENCES public.alf_store(id);
ALTER TABLE public.alf_node ADD CONSTRAINT fk_alf_node_tqn FOREIGN KEY (type_qname_id) REFERENCES public.alf_qname(id);
ALTER TABLE public.alf_node ADD CONSTRAINT fk_alf_node_txn FOREIGN KEY (transaction_id) REFERENCES public.alf_transaction(id);

alf_node_properties

CREATE TABLE public.alf_node_properties (
	node_id int8 NOT NULL,
	actual_type_n int4 NOT NULL,
	persisted_type_n int4 NOT NULL,
	boolean_value bool NULL,
	long_value int8 NULL,
	float_value float4 NULL,
	double_value float8 NULL,
	string_value varchar(1024) NULL,
	serializable_value bytea NULL,
	qname_id int8 NOT NULL,
	list_index int4 NOT NULL,
	locale_id int8 NOT NULL,
	CONSTRAINT alf_node_properties_pkey PRIMARY KEY (node_id,qname_id,list_index,locale_id)
);
CREATE INDEX fk_alf_nprop_loc ON public.alf_node_properties (locale_id);
CREATE INDEX fk_alf_nprop_n ON public.alf_node_properties (node_id);
CREATE INDEX fk_alf_nprop_qn ON public.alf_node_properties (qname_id);
CREATE INDEX idx_alf_nprop_b ON public.alf_node_properties (qname_id,boolean_value,node_id);
CREATE INDEX idx_alf_nprop_d ON public.alf_node_properties (qname_id,double_value,node_id);
CREATE INDEX idx_alf_nprop_f ON public.alf_node_properties (qname_id,float_value,node_id);
CREATE INDEX idx_alf_nprop_l ON public.alf_node_properties (qname_id,long_value,node_id);
CREATE INDEX idx_alf_nprop_s ON public.alf_node_properties (qname_id,string_value,node_id);


-- public.alf_node_properties foreign keys

ALTER TABLE public.alf_node_properties ADD CONSTRAINT fk_alf_nprop_loc FOREIGN KEY (locale_id) REFERENCES public.alf_locale(id);
ALTER TABLE public.alf_node_properties ADD CONSTRAINT fk_alf_nprop_n FOREIGN KEY (node_id) REFERENCES public.alf_node(id);
ALTER TABLE public.alf_node_properties ADD CONSTRAINT fk_alf_nprop_qn FOREIGN KEY (qname_id) REFERENCES public.alf_qname(id);

alf_qname

CREATE TABLE public.alf_qname (
	id int8 NOT NULL,
	"version" int8 NOT NULL,
	ns_id int8 NOT NULL,
	local_name varchar(200) NOT NULL,
	CONSTRAINT alf_qname_pkey PRIMARY KEY (id)
);
CREATE UNIQUE INDEX ns_id ON public.alf_qname (ns_id,local_name);


-- public.alf_qname foreign keys

ALTER TABLE public.alf_qname ADD CONSTRAINT fk_alf_qname_ns FOREIGN KEY (ns_id) REFERENCES public.alf_namespace(id);

best
Muffex

Hey @balaji.ramaswamy,

Are the DDLs I sent the ones you meant?

regards
Muffex

Hello Muffex,

I tried to repro the issue but was unable to, seems like fre Table DDL are still missing, specially I am unable to run the statements (Alter statements)

Regards
Kartik Nadar

Hello @Kartik_Nadar,

I am eager to provide the information you need but don’t understand what is missing. I copied all DDLs from the PSQL-Tables (from my PSQL-DB-Tool) and pasted them into my previous post. For me the alter statements did finish successfully (within dremio).

Best Muffex

@Muffex No need for more info, we will look into this

1 Like

Hello @balaji.ramaswamy,

the issue persists with the current docker image (2 months old).

I can circumvent the assertionError (in one of my queries) by ommiting one of my subselects and replacing it with the “hard-coded” value of that subselect. This however, leads to another error. The new error is referring to issues with a longer “CONCAT”.

The same query can be executed in 4.9.1 without issues.

Best
Muffex

The issue persists with v22.1.1.