Phoenix query fails with "Error: ERROR 1001 (42I01): Undefined column family."

[#1]

PROBLEM : Inner query does not recognize alias for the table and consider it as column family. Following exceptions are seen:

0: jdbc:phoenix:localhost:2181> SELECT a.tgb_id FROM "APC:TGB_CURRENT_STATUS" a WHERE a.utc_date_epoch in (SELECT b.utc_date_epoch FROM "APC:TGB_STATUS" b WHERE a.tgb_id = b.tgb_id ORDER BY b.utc_date_epoch DESC limit 2) 
. . . . . . . . . . . . . . . > ; 
Error: ERROR 1001 (42I01): Undefined column family. familyName=A.null (state=42I01,code=1001) 
org.apache.phoenix.schema.ColumnFamilyNotFoundException: ERROR 1001 (42I01): Undefined column family. familyName=A.null 
at org.apache.phoenix.schema.PTableImpl.getColumnFamily(PTableImpl.java:724) 
at org.apache.phoenix.compile.FromCompiler$SingleTableColumnResolver.resolveColumn(FromCompiler.java:365)

STEPS TO REPRODUCE :

1. Create two tables from Phoenix with below schema

CREATE TABLE "${namespace}:TGB_STATUS" ( 
tgb_id integer not null, 
utc_date_epoch integer not null, 
tgb_name varchar(40), 
city_name varchar(25), 
tgb_type_description varchar(64), 
local_date_key integer, 
utc_date_key integer, 
enclosure_type integer, 
door_tamper_flag boolean, 
over_temp_flag boolean, 
under_temp_flag boolean, 
voltage_fault_flag boolean, 
power_fail_flag boolean, 
battery_low_flag boolean, 
major_rectifier_alarm_flag boolean, 
minor_rectifier_alarm_flag boolean, 
env_fault_alarm_flag boolean, 
fusion_proc_load integer, 
fusion_proc_uptime integer, 
pcc_installed_flag boolean, 
vswr_alarm_flag boolean, 
pcc_power_supply_flag boolean, 
forward_power integer, 
reflected_power integer, 
alarm_bits integer, 
alarm_bits_text varchar(8), 

slot0_temperature integer, 
slot0_channel integer, 
slot0_active boolean, 

slot1_temperature integer, 
slot1_channel integer, 
slot1_active boolean, 

slot2_temperature integer, 
slot2_channel integer, 
slot2_active boolean, 

slot3_temperature integer, 
slot3_channel integer, 
slot3_active boolean, 

slot4_temperature integer, 
slot4_channel integer, 
slot4_active boolean, 

slot5_temperature integer, 
slot5_channel integer, 
slot5_active boolean, 

slot6_temperature integer, 
slot6_channel integer, 
slot6_active boolean, 

slot7_temperature integer, 
slot7_channel integer, 
slot7_active boolean, 

slot8_temperature integer, 
slot8_channel integer, 
slot8_active boolean, 

slot9_temperature integer, 
slot9_channel integer, 
slot9_active boolean, 

CONSTRAINT pk_tgb_status PRIMARY KEY (tgb_id ASC, utc_date_epoch DESC) 
)SALT_BUCKETS = ${tgb_status};

And

CREATE TABLE "${namespace}:TGB_CURRENT_STATUS" ( 
tgb_id integer not null, 
utc_date_epoch integer, 
tgb_name varchar(40), 
city_name varchar(25), 
tgb_type_description varchar(64), 
local_date_key integer, 
utc_date_key integer, 
enclosure_type integer, 
door_tamper_flag boolean, 
over_temp_flag boolean, 
under_temp_flag boolean, 
voltage_fault_flag boolean, 
power_fail_flag boolean, 
battery_low_flag boolean, 
major_rectifier_alarm_flag boolean, 
minor_rectifier_alarm_flag boolean, 
env_fault_alarm_flag boolean, 
fusion_proc_load integer, 
fusion_proc_uptime integer, 
pcc_installed_flag boolean, 
vswr_alarm_flag boolean, 
pcc_power_supply_flag boolean, 
forward_power integer, 
reflected_power integer, 
alarm_bits integer, 
alarm_bits_text varchar(8), 

slot0_temperature integer, 
slot0_channel integer, 
slot0_active boolean, 

slot1_temperature integer, 
slot1_channel integer, 
slot1_active boolean, 

slot2_temperature integer, 
slot2_channel integer, 
slot2_active boolean, 

slot3_temperature integer, 
slot3_channel integer, 
slot3_active boolean, 

slot4_temperature integer, 
slot4_channel integer, 
slot4_active boolean, 

slot5_temperature integer, 
slot5_channel integer, 
slot5_active boolean, 

slot6_temperature integer, 
slot6_channel integer, 
slot6_active boolean, 

slot7_temperature integer, 
slot7_channel integer, 
slot7_active boolean, 

slot8_temperature integer, 
slot8_channel integer, 
slot8_active boolean, 

slot9_temperature integer, 
slot9_channel integer, 
slot9_active boolean, 

CONSTRAINT pk_tgb_current_status PRIMARY KEY (tgb_id ASC) 
);

2. Run the following query.

SELECT a.tgb_id FROM "APC:TGB_CURRENT_STATUS" a WHERE a.utc_date_epoch in (SELECT b.utc_date_epoch FROM "APC:TGB_STATUS" b WHERE a.tgb_id = b.tgb_id ORDER BY b.utc_date_epoch DESC limit 2) 

3. Try removing the alias in inner query as well :

SELECT a.tgb_id FROM "APC:TGB_CURRENT_STATUS" a WHERE a.utc_date_epoch in (SELECT b.utc_date_epoch FROM "APC:TGB_STATUS" b WHERE "APC:TGB_CURRENT_STATUS".tgb_id = b.tgb_id ORDER BY b.utc_date_epoch DESC limit 2) 

ROOT CAUSE: This is a known issue reported in internal BUG-78975 with no workaround as of now.