"herddb.utils.IllegalDataAccessException: table does not define column" on NOT IN statement

[dmercuriali]

Please describe the issue you observed:

HerdDB v0.22.0
NOT IN statements seems to throw an error when the subquery selects a column with the same name of a column from the main query.

• What did you do?
  Executed this query
  SELECT t0.IDSTRING FROM LOCALIZEDSTRING t0 WHERE t0.IDSTRING NOT IN (SELECT t1.IDSTRING FROM STRINGHISTORYITEM t1 WHERE (t1.TAG IN (?)))

from our application and from the herddb cli. Both fails with the exception below.
Changing the NOT IN to NOT EXISTS fixes the error.
Changing the subquery SELECT t1.IDSTRING FROM to something like SELECT t1.ANOTHERCOL FROM fixes the error

javax.servlet.ServletException: javax.persistence.PersistenceException: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.5.2.v20140319-9ad6abd): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: java.sql.SQLException: herddb.model.StatementExecutionException: herddb.utils.IllegalDataAccessException: table herd.localizedstring does not define column idstring0
	at herddb.model.planner.PlannerOp.executeAsync(PlannerOp.java:80)
	at herddb.core.TableSpaceManager.executePlannedOperationStatement(TableSpaceManager.java:1436)
	at herddb.core.TableSpaceManager.executeStatementAsyncInternal(TableSpaceManager.java:1365)
	at herddb.core.TableSpaceManager.executeStatementAsync(TableSpaceManager.java:1332)
	at herddb.core.DBManager.executeStatementAsync(DBManager.java:710)
	at herddb.core.DBManager.executePlanAsync(DBManager.java:767)
	at herddb.core.DBManager.executePlan(DBManager.java:737)
	at herddb.server.ServerSideConnectionPeer.handleOpenScanner(ServerSideConnectionPeer.java:477)
	at herddb.server.ServerSideConnectionPeer.requestReceived(ServerSideConnectionPeer.java:198)
	at herddb.network.netty.AbstractChannel.processRequest(AbstractChannel.java:96)
	at herddb.network.netty.AbstractChannel.lambda$handlePduRequest$0(AbstractChannel.java:105)
	at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:514)
	at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264)
	at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1135)
	at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635)
	at io.netty.util.concurrent.FastThreadLocalRunnable.run(FastThreadLocalRunnable.java:30)
	at java.base/java.lang.Thread.run(Thread.java:844)
Caused by: herddb.utils.IllegalDataAccessException: table herd.localizedstring does not define column idstring0
	at herddb.codec.RecordSerializer.accessRawDataFromValue(RecordSerializer.java:683)
	at herddb.codec.DataAccessorForFullRecord.get(DataAccessorForFullRecord.java:54)
	at herddb.model.planner.ProjectOp$ZeroCopyProjection$RuntimeProjectedDataAccessor.get(ProjectOp.java:307)
	at herddb.model.Tuple.serialize(Tuple.java:167)
	at herddb.file.FileRecordSet$TupleSerializer.write(FileRecordSet.java:79)
	at herddb.file.FileRecordSet$TupleSerializer.write(FileRecordSet.java:59)
	at herddb.utils.DiskArrayList.startWrite(DiskArrayList.java:156)
	at herddb.utils.DiskArrayList.add(DiskArrayList.java:90)
	at herddb.file.FileRecordSet.add(FileRecordSet.java:98)
	at herddb.model.planner.JoinOp.lambda$execute$1(JoinOp.java:130)
	at herddb.model.DataScanner.forEach(DataScanner.java:84)
	at herddb.model.planner.JoinOp.execute(JoinOp.java:129)
	at herddb.model.planner.FilterOp.execute(FilterOp.java:82)
	at herddb.model.planner.ProjectOp.execute(ProjectOp.java:217)
	at herddb.model.planner.PlannerOp.executeAsync(PlannerOp.java:76)
	... 16 more

Error Code: 0
Call: SELECT t0.IDSTRING FROM LOCALIZEDSTRING t0 WHERE t0.IDSTRING NOT IN (SELECT t1.IDSTRING FROM STRINGHISTORYITEM t1 WHERE (t1.TAG IN (?)))
	bind => [1 parameter bound]
Query: ReportQuery(referenceClass=LocalizedString sql="SELECT t0.IDSTRING FROM LOCALIZEDSTRING t0 WHERE t0.IDSTRING NOT IN (SELECT t1.IDSTRING FROM STRINGHISTORYITEM t1 WHERE (t1.TAG IN (?)))")
	org.glassfish.jersey.servlet.WebComponent.serviceImpl(WebComponent.java:432)
	org.glassfish.jersey.servlet.WebComponent.service(WebComponent.java:370)
	org.glassfish.jersey.servlet.ServletContainer.service(ServletContainer.java:389)
	org.glassfish.jersey.servlet.ServletContainer.service(ServletContainer.java:342)
	org.glassfish.jersey.servlet.ServletContainer.service(ServletContainer.java:229)
	org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53)

[nicoloboschi]

I wrote a simple reproducer here master...nicoloboschi:fix-not-in-query

The issue is that JoinOp#execute creates a 'left data scanner' which contains a column name (DataScanner#getFieldNames) that is not a real table column but a temporary column used for projection computation

During the projection the records are added to a temporary disk map (if we are not in 'local' or 'diskless' mode) creating a MaterializedRecordSet (see

herddb/herddb-core/src/main/java/herddb/model/planner/JoinOp.java

Line 126 in e434d7a

MaterializedRecordSet recordSet = tableSpaceManager.getDbmanager().getRecordSetFactory()

)

In order to serialize the record, RecordSerializer#accessRawDataFromValue is called with the temporary field name as property and it throws error as the property is not a real table column
By-passing that check, my test reproducer pass and the scan result is correct

@eolivelli @diegosalvi AFAIK MaterializedRecordSet do not represent records set owned by a table, so I think that check is useless. Any thoughts?

[eolivelli]

DataAccessorForFullRecord is meant to access a "Record" without any memory copy.
If you see a DataAccessorForFullRecord for something that is not a record then we have a worse problem.

but here we have a RuntimeProjectedDataAccessor, that is a projection, that maps a Record to a different schema, it usually selects columns (by index) and changes the order.

it looks to me that we have a problem somewhere because the mapping should be perfect.

you can run your test and enable "INFO" level for the CalcitePlanner and see the actual access plan, you will see the Projection and what's going on