"Unable to resolve column" in DataGrip wrt MySql statement for "Select fields into variable from table where conditions"

Version for DataGrip is:

DataGrip 2021.1.3
Build #DB-211.7442.53, built on June 7, 2021
Licensed to Bill Linder
You have a perpetual fallback license for this version.
Subscription is active until June 8, 2022.
Runtime version: 11.0.11+9-b1341.57 amd64
VM: OpenJDK 64-Bit Server VM by JetBrains s.r.o.
Windows 10 10.0
GC: G1 Young Generation, G1 Old Generation
Memory: 2048M
Cores: 8

Bill Linder,

Could you provide full DDL to reproduce the issue?

Thank you for helping me with this.

The DDL for the containers table from DataGrip, minus comments due to company rules:

create table containers
(
idx bigint unsigned auto_increment
primary key,
containerID varchar(26) null,
type varchar(1) null,
workorder varchar(18) null,
number int unsigned default 1 not null,
split int unsigned default 0 not null,
status varchar(4) null,
current_op varchar(5) null,
current_loc varchar(24) null,
part varchar(18) null,
quantity int null,
sched_start_time datetime null,
sched_finish_time datetime null,
last_update timestamp default CURRENT_TIMESTAMP not null on update CURRENT_TIMESTAMP,
department varchar(8) null,
parent varchar(26) null,
source varchar(26) null,
last_operation datetime null,
verified_on datetime null,
verified_by varchar(8) null,
detail_check_on datetime null,
detail_check_by varchar(8) null,
flagNetChildren tinyint default 0 null,
unitType varchar(2) null,
workorderID varchar(8) null,
create_time time(6) null,
create_date date null,
create_user varchar(8) null,
expire_time time(6) null,
expire_date date null,
expire_user varchar(8) null,
woid varchar(8) null,
containerType varchar(8) null,
lot_ser varchar(18) null,
free_move tinyint null,
traveller_image varchar(300) null,
inspected tinyint null,
inspected_by varchar(8) null,
inspected_on date null,
inspected_at time null,
inspection_passed tinyint null,
qty_complete int null,
qty_scrapped int null,
top varchar(26) null,
serial varchar(12) null,
deleted_qty int null,
deleted_date datetime null,
deleted_by varchar(8) null,
deleted_area varchar(8) null,
deleted_dept varchar(8) null,
deleted_wkctr varchar(8) null,
deleted_location varchar(24) null,
deleted_op varchar(5) null,
deleted_status varchar(4) null,
deleted_reason varchar(80) null,
erp_status varchar(2) null,
erp_status_update tinyint default 1 null,
erp_status_last_update date null,
scrap_qty int null,
scrap_date datetime null,
scrap_by varchar(8) null,
scrap_area varchar(8) null,
scrap_dept varchar(8) null,
scrap_wkctr varchar(8) null,
scrap_location varchar(24) null,
scrap_op varchar(5) null,
scrap_status varchar(4) null,
scrap_reason varchar(80) null,
constraint container
unique (containerID, workorder, part),
constraint containerID_UNIQUE
unique (containerID)
);

create index WOID
on containers (woid);

create index containerID
on containers (containerID);

create index locations
on containers (current_loc);

create index lot
on containers (workorder);

create index part
on containers (part);

create index serial
on containers (serial);

create index status
on containers (status);

create index workorder
on containers (workorder);

Here is the scrubbed up (had to remove certain comments) version of the stored procedure in question as well:

CREATE PROCEDURE `cart_remove_2`(

thisSessionID BIGINT,
userid VARCHAR(8),
cartid VARCHAR(24),
reason VARCHAR(120),
updateThis INT
)
BEGIN
DECLARE authorized BOOL;
DECLARE woCount INT;
DECLARE wo BOOL;
DECLARE containers INT;
DECLARE locations INT;
DECLARE routings INT;
DECLARE thisLot VARCHAR(18);
DECLARE thisSessionID BIGINT;
DECLARE thisAction VARCHAR(8);
DECLARE thisQuantity INT; -- Container qty only.
DECLARE thisLocation VARCHAR(24);
DECLARE thisOp VARCHAR(5);
DECLARE thisStatus VARCHAR(4);
DECLARE constDeleted VARCHAR(4) DEFAULT "DLTD";
DECLARE thisTimeStamp DATETIME;
DECLARE thisUser VARCHAR(8);

-- This routine zeros out all cart entries in the system

DECLARE program VARCHAR(40) DEFAULT 'cart_remove_2';

SET thisTimeStamp =now();

SET thisSessionID = session_start('','');
select thisSessionID;

CALL log_entry_add(thisSessionID,0,userID,now(),program,'DEBUG',concat('START:',program));
CALL log_entry_add(thisSessionID,0,userID,now(),program,'DEBUG',concat('userid:',userid));
CALL log_entry_add(thisSessionID,0,userID,now(),program,'DEBUG',concat('cartid:',cartid));
CALL log_entry_add(thisSessionID,0,userID,now(),program,'DEBUG',concat('reason:',reason));
CALL log_entry_add(thisSessionID,0,userID,now(),program,'DEBUG',concat('updateThis:',updateThis));

SET thisUser = userid;

CASE updateThis
WHEN 0 THEN SET thisAction = "INSPECT";
WHEN 1 THEN SET thisAction = "DELETE";
WHEN 2 THEN SET thisAction = "RESET";
ELSE SET thisAction = "NOACTION";
END CASE;

select distinct(wr_num) into thisLot FROM wr_route where wr_cart_num = cartid or wr_num = cartid;

SELECT userAuthorize(userid,program) INTO authorized;

mainBlock:BEGIN

if authorized THEN

IF (updateThis = 1) THEN -- CLEARS ALL ENTRIES FOR THE CART/WORK ORDER
-- wr_route

CALL log_entry_add(thisSessionID,0,userID,now(),program,'DEBUG',concat('ACTION:','DELETING'));


start transaction;
-- update wr_route SET wr_cart_number = NULL where wr_cart_num = cartid or wr_num = cartid;

UPDATE wr_route
SET wr_deleted_qty = thisQuantity,
wr_deleted_datetime = thisTimeStamp,
wr_deleted_by = thisUser
WHERE (wr_cart_num = cartid OR wr_num = cartid);

-- delete FROM wr_route where (wr_cart_num = cartid OR wr_num = cartid);

SET routings = ROW_COUNT();

-- containers
select quantity, current_loc, current_op, `status` into thisQuantity, thisLocation, thisOp, thisStatus
from containers where (containerID = cartid or workorder = cartid);
update containers SET deleted_qty = thisQuantity,
deleted_location = thisLocation,
deleted_op = thisOp,
deleted_status = thisStatus,
deleted_by = thisUser,
deleted_date = thisTimeStamp,
quantity = 0,
-- current_loc = "deleted",
current_op = "",
`status` = constDeleted
where (containerID = cartid or workorder = cartid);
-- delete FROM containers where (containerID = cartid or workorder = cartid);

SET containers = ROW_COUNT();

-- location_detail
delete FROM location_detail where (container_idx = cartid or wo_num = cartid );

SET locations = ROW_COUNT();
commit;

CALL log_transactions('', thisSessionID, program, userid, thisAction, 0, 'SYSTEM', 'All ROUTES, CONTAINERS AND LOCATION DETAILS REMOVED', userid, now(), '', thisLot, 'DELETED');

-- containers

-- location_detail
ELSEIF updateThis=2 THEN -- CLEARS ALL RECORDS EXCEPT MASTER AND SETS PRINT DATE TO 0000-00-00


CALL log_entry_add(thisSessionID,0,userID,now(),program,'DEBUG',concat('ACTION:','RESETTING'));

start transaction;
-- update wr_route SET wr_cart_number = NULL where wr_cart_num = cartid;



UPDATE wr_route
SET wr_deleted_qty = thisQuantity,
wr_deleted_datetime = thisTimeStamp,
wr_deleted_by = thisUser
WHERE (wr_cart_num = cartid OR wr_num = cartid)
AND wr_cart_num <> 'MASTER';

SET routings = ROW_COUNT();
update wr_route set wr_date_printed = '0000-00-00' WHERE (wr_cart_num = cartid OR wr_num = cartid) and wr_cart_num = 'MASTER';

-- containers

select quantity, current_loc, current_op, `status` into thisQuantity, thisLocation, thisOp, thisStatus
from containers where (containerID = cartid or workorder = cartid);
update containers SET deleted_qty = thisQuantity,
deleted_location = thisLocation,
deleted_op = thisOp,
deleted_status = thisStatus,
quantity = 0,
-- current_loc = "deleted",
current_op = "",
`status` = constDeleted
where (containerID = cartid or workorder = cartid);

SET containers = ROW_COUNT();

-- location_detail
delete FROM location_detail where (container_idx = cartid or wo_num = cartid);

SET locations = ROW_COUNT();
COMMIT;
CALL log_transactions('', thisSessionID, program, userid, thisAction, 0, 'SYSTEM', 'All CART ROUTES (NOT MASTER), CONTAINERS AND LOCATION DETAILS REMOVED', userid, now(), '', thisLot, 'CLEARED');

ELSEIF updateThis=0 THEN
-- wr_route
CALL log_entry_add(thisSessionID,0,userID,now(),program,'DEBUG',concat('ACTION:','REVIEWING'));

start transaction;
SELECT 'Work Order Routing',wr_cart_num, wr_num, wr_part, wr_cart_qpc, wr_op, wr_date_start, wr_dept, wr_mch, wr_location, wr_queue_in_date, wr_queue_in_amount, wr_queue_out_date, wr_process_date_start, wr_process_date_finish, wr_process_qty
FROM wr_route
where (wr_cart_num = cartid or wr_num = cartid );

SET routings = ROW_COUNT();
-- containers
SELECT 'Container Master',containerID,workorder,status,current_op,current_loc,part,quantity, source, parent
FROM containers
where (containerID = cartid or workorder = cartid );

SET containers = ROW_COUNT();
-- location_detail
SELECT 'Location Detail', area,dept,wkctr,location, container_idx, pt_num, wo_num, quantity, date_change, employee_idx, status_group, status_idx
FROM location_detail
where (container_idx = cartid or wo_num = cartid );


SET locations = ROW_COUNT();
COMMIT;
CALL log_transactions('', thisSessionID, program, userid, thisAction, 0, 'SYSTEM', 'All ROUTES, CONTAINERS AND LOCATION DETAILS REVIEWED', userid, now(), '', thisLot, 'REVIEWED');

END IF;
END IF;
SELECT "SUCCESS" as STATE, 'SUCCESS', 'Cart Removed' as MESSAGE,routings,containers,locations,thisUser;

END mainBlock;

END

Had an idea:  What if I use a table alias?  And it worked. Now, why?  lol

Also.  Just upgraded DataGrip to the new release.  That did not fix it.

Bill Linder,

For sure, it's an issue. I filed one to up vote and follow.

Thank you Vasily!