Details
-
Bug
-
Resolution: Done
-
Major
-
8.7
-
None
Description
If I run a sql with composite key in DB2, Teiid translates it to something incorrect DB2 sql.
However, it works for postgres, mysql, and oracle.
Run the following SQL through Teiid against DB2:
select "DB2_SugarCRM_SUGARCRM_SALES_FACT"."AMOUNT" as "DB2_SugarCRM_SUGARCRM_SALES_FACT_AMOUNT",
"BD2_Foodmart_FOODMART_STORE"."COFFEE_BAR" as "BD2_Foodmart_FOODMART_STORE_COFFEE_BAR"
from "BD2_Foodmart_FOODMART"."STORE" "BD2_Foodmart_FOODMART_STORE"
inner join "DB2_SugarCRM_SUGARCRM"."SALES_LOCATION" "DB2_SugarCRM_SUGARCRM_SALES_LOCATION" on ("BD2_Foodmart_FOODMART_STORE"."STORE_STATE" = "DB2_SugarCRM_SUGARCRM_SALES_LOCATION"."STATE" and "DB2_Sug
arCRM_SUGARCRM_SALES_LOCATION"."COUNTRY" = "BD2_Foodmart_FOODMART_STORE"."STORE_COUNTRY" and "DB2_SugarCRM_SUGARCRM_SALES_LOCATION"."CITY" = "BD2_Foodmart_FOODMART_STORE"."STORE_CITY")
inner join "DB2_SugarCRM_SUGARCRM"."SALES_FACT" "DB2_SugarCRM_SUGARCRM_SALES_FACT" on ("DB2_SugarCRM_SUGARCRM_SALES_FACT"."SALES_LOCATION_ID" = "DB2_SugarCRM_SUGARCRM_SALES_LOCATION"."ID")
limit 1000
And Teiid translates it to:
Caused by: org.teiid.jdbc.TeiidSQLException: TEIID30504 666941312: -104 TEIID11008:TEIID11004 Error executing statement(s): [Prepared Values: ['BC', 'Canada', 'Burnaby', 'BC', 'Canada', 'Cliffside', '
BC', 'Canada', 'Haney', 'BC', 'Canada', 'Ladner', 'BC', 'Canada', 'Langford', 'BC', 'Canada', 'Langley', 'BC', 'Canada', 'Metchosin', 'BC', 'Canada', 'N. Vancouver', 'BC', 'Canada', 'Newton', 'BC', 'C
anada', 'Oak Bay', 'BC', 'Canada', 'Port Hammond', 'BC', 'Canada', 'Richmond', 'BC', 'Canada', 'Royal Oak', 'BC', 'Canada', 'Shawnee', 'BC', 'Canada', 'Sooke', 'BC', 'Canada', 'Vancouver', 'BC', 'Cana
da', 'Victoria', 'BC', 'Canada', 'Westminster', 'CA', 'USA', 'Altadena', 'CA', 'USA', 'Arcadia', 'CA', 'USA', 'Bellflower', 'CA', 'USA', 'Berkeley', 'CA', 'USA', 'Beverly Hills', 'CA', 'USA', 'Burbank
', 'CA', 'USA', 'Burlingame', 'CA', 'USA', 'Chula Vista', 'CA', 'USA', 'Colma', 'CA', 'USA', 'Concord', 'CA', 'USA', 'Coronado', 'CA', 'USA', 'Daly City', 'CA', 'USA', 'Downey', 'CA', 'USA', 'El Cajon
', 'CA', 'USA', 'Fremont', 'CA', 'USA', 'Glendale', 'CA', 'USA', 'Grossmont', 'CA', 'USA', 'Imperial Beach', 'CA', 'USA', 'La Jolla', 'CA', 'USA', 'La Mesa', 'CA', 'USA', 'Lakewood', 'CA', 'USA', 'Lem
on Grove', 'CA', 'USA', 'Lincoln Acres', 'CA', 'USA', 'Long Beach', 'CA', 'USA', 'Los Angeles', 'CA', 'USA', 'Mill Valley', 'CA', 'USA', 'National City', 'CA', 'USA', 'Newport Beach', 'CA', 'USA', 'No
vato', 'CA', 'USA', 'Oakland', 'CA', 'USA', 'Palo Alto', 'CA', 'USA', 'Pomona', 'CA', 'USA', 'Redwood City', 'CA', 'USA', 'Richmond', 'CA', 'USA', 'San Carlos', 'CA', 'USA', 'San Diego', 'CA', 'USA',
'San Francisco', 'CA', 'USA', 'San Gabriel', 'CA', 'USA', 'San Jose', 'CA', 'USA', 'Santa Cruz', 'CA', 'USA', 'Santa Monica', 'CA', 'USA', 'Spring Valley', 'CA', 'USA', 'Torrance', 'CA', 'USA', 'West
Covina', 'CA', 'USA', 'Woodland Hills', 'DF', 'Mexico', 'San Andres', 'DF', 'Mexico', 'Santa Anita', 'DF', 'Mexico', 'Santa Fe', 'DF', 'Mexico', 'Tixapan', 'Guerrero', 'Mexico', 'Acapulco', 'Jalisco',
'Mexico', 'Guadalajara', 'Mexico', 'Mexico', 'Mexico City', 'OR', 'USA', 'Albany', 'OR', 'USA', 'Beaverton', 'OR', 'USA', 'Corvallis', 'OR', 'USA', 'Lake Oswego', 'OR', 'USA', 'Lebanon', 'OR', 'USA',
'Milwaukie', 'OR', 'USA', 'Oregon City', 'OR', 'USA', 'Portland', 'OR', 'USA', 'Salem', 'OR', 'USA', 'W. Linn', 'OR', 'USA', 'Woodburn', 'Oaxaca', 'Mexico', 'Tlaxiaco', 'Sinaloa', 'Mexico', 'La Cruz'
, 'Veracruz', 'Mexico', 'Orizaba', 'WA', 'USA', 'Anacortes', 'WA', 'USA', 'Ballard', 'WA', 'USA', 'Bellingham', 'WA', 'USA', 'Bremerton', 'WA', 'USA', 'Burien', 'WA', 'USA', 'Edmonds', 'WA', 'USA', 'E
verett', 'WA', 'USA', 'Issaquah', 'WA', 'USA', 'Kirkland', 'WA', 'USA', 'Lynnwood', 'WA', 'USA', 'Marysville', 'WA', 'USA', 'Olympia', 'WA', 'USA', 'Port Orchard', 'WA', 'USA', 'Puyallup', 'WA', 'USA'
, 'Redmond', 'WA', 'USA', 'Renton', 'WA', 'USA', 'Seattle', 'WA', 'USA', 'Sedro Woolley', 'WA', 'USA', 'Spokane', 'WA', 'USA', 'Tacoma', 'WA', 'USA', 'Walla Walla', 'WA', 'USA', 'Yakima', 'Yucatan', '
Mexico', 'Merida', 'Zacatecas', 'Mexico', 'Camacho', 'Zacatecas', 'Mexico', 'Hidalgo'] SQL: SELECT g_0."STORE_STATE" AS c_0, g_0."STORE_COUNTRY" AS c_1, g_0."STORE_CITY" AS c_2, g_0."COFFEE_BAR" AS c_
3 FROM "FOODMART"."STORE" AS g_0 WHERE (g_0."STORE_STATE", g_0."STORE_COUNTRY", g_0."STORE_CITY") IN ((?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?),
(?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (
?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?,
?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?
, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?,
?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?)
, (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?)) ORDER BY c_0, c_1, c_2]
If I run a sql with composite key in DB2, Teiid translates it to something incorrect DB2 sql.
However, it works for postgres, mysql, and oracle.