AssertionError: Field ordinal X is invalid for type Y

Muffex · February 7, 2022, 10:38am

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)

balaji.ramaswamy · February 8, 2022, 5:49am

@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

Muffex · February 8, 2022, 9:26am

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

balaji.ramaswamy · February 11, 2022, 6:29am

@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?

Muffex · February 11, 2022, 10:12am

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

Muffex · February 17, 2022, 9:03am

Hey @balaji.ramaswamy,

Are the DDLs I sent the ones you meant?

regards
Muffex

Kartik_Nadar · February 21, 2022, 9:16am

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

Muffex · March 2, 2022, 1:22pm

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

balaji.ramaswamy · March 8, 2022, 10:07pm

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

Muffex · August 25, 2022, 1:16pm

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

Muffex · August 26, 2022, 1:15pm

The issue persists with v22.1.1.

Muffex · December 6, 2022, 1:44pm

The issue persists with v23.1.

At first I tried deleting and re-creating the tables from the query on my initial dremio instance (v4.9.1) Then I set up a completely new dremio docker instance (v23.1), added the psql-source and created a space with the needed tables.

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

The queries are still sent to the same PSQL-DB.

Any updates regarding this?

Muffex · December 7, 2022, 5:57pm

I have by now made three additional attempts.

I tried to set up the Dremio-Container on two other VM on other physical server. → same issue
I tried to use another PSQL-DB (Alfresco) running on other server. → same issue

What am I doing wrong?

Maybe my query causes the issue?

SELECT wikiNode.uuid, wikiNodePropName.string_value AS title
                FROM (SELECT *
                      FROM alfresco_alfNode
                      WHERE audit_creator <> 'System' AND store_id = 6) AS n
                  JOIN alfresco_alfQname AS nodeType ON (nodeType.id = n.type_qname_id AND nodeType.local_name = 'site')
                  JOIN alfresco_alfChildAssoc AS aca ON (aca.parent_node_id = n.id)
                  JOIN alfresco_alfNode AS wikiNode ON (aca.child_node_id = wikiNode.id AND wikiNode.type_qname_id IN (SELECT id
                                                                                                                       FROM alfresco_alfQname
                                                                                                                       WHERE local_name = 'folder'))
                  JOIN alfresco_alfNodeProperties AS wikiNodePropName ON (wikiNodePropName.node_id = wikiNode.id)
                WHERE wikiNodePropName.qname_id IN (SELECT id
                                                    FROM alfresco_alfQname
                                                    WHERE local_name = 'name')
                  AND wikiNodePropName.string_value = 'wiki'

balaji.ramaswamy · December 9, 2022, 4:48am

@Muffex I do not think you are doing anything wrong. Does this query work when directly run via psql?

Muffex · December 14, 2022, 1:23pm

@balaji.ramaswamy,
when running the query directly on the psql-db (via DBeaver) the query works fine. The issue occurs when using a space and views. There is one view for each psql table.

Muffex · December 14, 2022, 2:28pm

@balaji.ramaswamy,
I have now tried sending the query directly via Dremio to the psql-db (source) and get the same error. So this issue seems not to be related to the spaces/views but rather the connection between dremio and psql. For this I also set up a new VM running ubuntu and docker (instead of Redhat and Docker).

balaji.ramaswamy · December 22, 2022, 8:37am

@Muffex Are you able to send the profile?

Muffex · January 2, 2023, 2:07pm

@balaji.ramaswamy,

sure. Attached you can find the profile.

regards
6531dc9c-227d-4575-ba1c-fe4081424161.zip (13.7 KB)

balaji.ramaswamy · January 5, 2023, 5:20am

@Muffex Is column id have different datatypes across datasets?

Muffex · January 5, 2023, 12:31pm

@balaji.ramaswamy,

thanks for your answer. The datatype of all id columns is int8 in psql. Dremio recognises those as correctly as numeric data types.

regards

Topic		Replies	Views
AssertionError: Cannot add expression of different type to set	1	626	September 23, 2023
Error executing query counting the number of null values in a column	0	148	April 21, 2024
Query fails depending on variable order	4	1542	January 25, 2019
Failed to create Incremental Reflection on ElasticSearch	10	2240	July 13, 2018
Cannot find a way to use this dataset: Mixed types double , decimal(30,0) for field <field_name> are not supported	12	1869	February 2, 2021

AssertionError: Field ordinal X is invalid for type Y

Related topics