Fairly normal looking query, two basically identical selects, but in the second one the RVC table is omitted, and NULLS are used. Query works fine in the console, and column headers come from the first select in the join. Fully specifying the JDBC column names for the RV.* and RVC.* did not help, nor did putting single or double quotes around aliases in the second select i.e. Null as 'RVC.GUID' for instance.
The method
public int findColumn(String columnName, boolean useColumnNamesInFindColumn, int indexBase) {
is being invoked and what is coming in is not RV.ID, but null.ID for the column name.
@Override
public List<ReferenceVariety> getAll() {
List<ReferenceVariety> chemicals;
MapSqlParameterSource namedParameters = new MapSqlParameterSource()
.addValue("maxCropId", maxCropId);
chemicals = namedParameterJdbcTemplate.query(
REFERENCE_VARIETIES_ALL_SQL,
namedParameters,
referenceVarietyRowMapper);
return chemicals;
}
public static final String REFERENCE_VARIETIES_ALL_SQL = "( SELECT RV.*, RVC.*, "
+ "PM.ID as PM_ID, PM.LAST_UPDATE_TIMESTAMP as PM_LAST_UPDATE_TIMESTAMP, "
+ "PM.GUID as PM_GUID, PM.ORGANIZATION_ID as PM_ORGANIZATION_ID, "
+ "PM.NAME as PM_NAME, PM.PRODUCT_SOURCE_TYPE, PM.PRODUCT_SOURCE_ID "
+ "FROM REFERENCE_VARIETY RV "
+ "LEFT JOIN PRODUCT_MANUFACTURER PM ON RV.MANUFACTURER_GUID = PM.GUID "
+ "LEFT JOIN REFERENCE_VARIETY_COUNTRY RVC ON RV.GUID = RVC.REFERENCE_VARIETY_GUID "
+ "WHERE SOURCE_SYSTEM IN (2, 3) "
+ "AND CROP_TYPE <= :maxCropId "
+ ") UNION ALL ("
+ "SELECT RV.*, "
+ "Null as GUID, "
+ "Null as SOURCE_SYSTEM_PRODUCT_ID, "
+ "Null as RETIRED_TIMESTAMP, "
+ "Null as REFERENCE_VARIETY_GUID, "
+ "Null as COUNTRY_CODE, "
+ "Null as CREATION_TIMESTAMP, "
+ "Null as LAST_UPDATE_TIMESTAMP, "
+ "Null as ID, "
+ " PM.ID as PM_ID, PM.LAST_UPDATE_TIMESTAMP as PM_LAST_UPDATE_TIMESTAMP, "
+ " PM.GUID as PM_GUID, PM.ORGANIZATION_ID as PM_ORGANIZATION_ID, "
+ " PM.NAME as PM_NAME, PM.PRODUCT_SOURCE_TYPE, PM.PRODUCT_SOURCE_ID "
+ "FROM REFERENCE_VARIETY RV "
+ "LEFT JOIN PRODUCT_MANUFACTURER PM ON RV.MANUFACTURER_GUID = PM.GUID "
+ "WHERE SOURCE_SYSTEM IN (2, 3) "
+ "AND CROP_TYPE <= :maxCropId )";
Root cause:
Caused by: java.sql.SQLException: Column 'RV.ID' not found.
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129)
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:89)
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:63)
at com.mysql.cj.jdbc.result.ResultSetImpl.findColumn(ResultSetImpl.java:584)
at com.mysql.cj.jdbc.result.ResultSetImpl.getLong(ResultSetImpl.java:862)
at com.deere.isg.products.common.repositories.referencevarieties.ReferenceVarietyRowMapper.mapRow(ReferenceVarietyRowMapper.java:23)
at com.deere.isg.products.common.repositories.referencevarieties.ReferenceVarietyRowMapper.mapRow(ReferenceVarietyRowMapper.java:18)
at org.springframework.jdbc.core.RowMapperResultSetExtractor.extractData(RowMapperResultSetExtractor.java:94)
at org.springframework.jdbc.core.RowMapperResultSetExtractor.extractData(RowMapperResultSetExtractor.java:61)
at org.springframework.jdbc.core.JdbcTemplate$1.doInPreparedStatement(JdbcTemplate.java:723)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:651)
... 78 more
Do unions not work here? Am I doing something wrong? Either half of the union select seems to work fine by itself. For now I am issuing two queries and combining the result sets by appending the objects the row mapper generates in a collection, but I would prefer one select statement.
