EXPLAIN with uuid_to_bin is not working in case primary key in IntelliJ IDEA

[nkonev]

version 0.75.9.
The issue is very similar to #5287

create table transaction_meta(
    id binary(16) DEFAULT (UUID_TO_BIN(uuid())) primary key,
    transaction_state varchar(32) NOT NULL,
    entity_ids JSON NOT NULL
);

insert into transaction_meta values
    (uuid_to_bin('d077821f-f54e-4ce9-978f-8478c1120f8b'), 'SUCCESSFUL', '["478f79d3-4a90-4efc-9d9b-ea1baa15dc1a", "36c7c702-185f-4f4b-a700-12c7f1f943b0"]')
;

select * from transaction_meta;

explain select * from transaction_meta where id = uuid_to_bin('d077821f-f54e-4ce9-978f-8478c1120f8b');

log

2023-03-30T19:58:54Z WARN [conn 1] error running query {connectTime=2023-03-30T19:57:44Z, connectionDb=companies, error=failed to encode `utf8mb4`, query=/* ApplicationName=IntelliJ IDEA 2022.3.1 */ explain select * from transaction_meta where id = uuid_to_bin('d077821f-f54e-4ce9-978f-8478c1120f8b')}

screen

just checked, it works in MySQL

[max-hoffman]

One difference is that this query probably has an IndexedTableAccess, so we are printing more than just Literal.String(). If this is a general problem, it might be useful refactoring more generally be tolerant of binary->hex printing.

[max-hoffman]

In this case it will be probably be this block in IndexedTableAccess.String(), but we might do this in other places

	if ft, ok := i.Table.(sql.FilteredTable); ok {
		var filters []string
		for _, f := range ft.Filters() {
			filters = append(filters, f.String())
		}
		if len(filters) > 0 {
			pr.WriteChildren(fmt.Sprintf("filters: %v", filters))
		}
	}

[nkonev]

@max-hoffman

In case foreign key the error also presents

create table transaction_meta(
    id binary(16) DEFAULT (UUID_TO_BIN(uuid())) primary key,
    transaction_state varchar(32) NOT NULL,
    entity_ids JSON NOT NULL
);

insert into transaction_meta values
    (uuid_to_bin('d077821f-f54e-4ce9-978f-8478c1120f8b'), 'SUCCESSFUL', '["478f79d3-4a90-4efc-9d9b-ea1baa15dc1a", "36c7c702-185f-4f4b-a700-12c7f1f943b0"]')
;

create table transaction_detail(
    id binary(16) DEFAULT (UUID_TO_BIN(uuid())) primary key,
    detail varchar(255),
    txm_id binary(16) not null,
    foreign key (txm_id) references transaction_meta(id)
);

insert into transaction_detail (detail, txm_id) values
('test string', uuid_to_bin('d077821f-f54e-4ce9-978f-8478c1120f8b'));

explain select * from transaction_detail where txm_id = uuid_to_bin('d077821f-f54e-4ce9-978f-8478c1120f8b');

[max-hoffman]

Yeah this is going to be equivalent. We generate an index for txm_id because of the foreign key, so this is an index lookup also. The fix for this should more generally try to catch this error in all of our String() types.

Project
     ├─ columns: [transaction_detail.id:0!null, transaction_detail.detail:1, transaction_detail.txm_id:2!null]
     └─ Filter
         ├─ Eq
         │   ├─ transaction_detail.txm_id:2!null
         │   └─ �w���NL闏�x���� (varbinary(16))
         └─ IndexedTableAccess(transaction_detail)
             ├─ index: [transaction_detail.txm_id]
             ├─ static: [{[�w���NL闏�x����, �w���NL闏�x����]}]
             └─ columns: [id detail txm_id]

[max-hoffman]

These two scripts appear to execute correctly in version 1.35.9 as far as i can tell. Closing.