mongodb select * on view returns error CommandNotSupportedOnView

[Ognian]

Any Idea why a select * on a mongodb view results in this error:

SQL-Fehler [65536]: Query failed (#20210403_015848_00023_3siug): com.mongodb.MongoCommandException: 
Command failed with error 166 (CommandNotSupportedOnView): 'Namespace enioSites.enrichedSites is a view,
 not a collection' on server host.docker.internal:27117.
 The full response is 

{
  "operationTime": {
    "$timestamp": {
      "t": 1617453002,
      "i": 1
    }
  },
  "ok": 0,
  "errmsg": "Namespace enioSites.enrichedSites is a view, not a collection",
  "code": 166,
  "codeName": "CommandNotSupportedOnView",
  "$clusterTime": {
    "clusterTime": {
      "$timestamp": {
        "t": 1617453002,
        "i": 1
      }
    },
    "signature": {
      "hash": {
        "$binary": "AAAAAAAAAAAAAAAAAAAAAAAAAAA=",
        "$type": "00"
      },
      "keyId": {
        "$numberLong": "0"
      }
    }
  }
}

Thanks
Ognian

[ebyhr]

Could you share the steps to reproduce? Also, you can ask question in the community Slack https://trino.io/slack.html

[Ognian]

there are no specific steps, I tested with both dbeaver and quix; In the catalog the mongodb database and the collection are shown; when doing select * from enioSites.enrichedSites which is the database and the collection name I get the above error; doing this with a collection which is not a view it simply works; I have not provided a _schema collection. I also tried to find via mongodb what kind of query is run from trino against mongodb but found only the correct query for _schema... so the problem seems to be inside the mongo connector.

[Ognian]

Looks like this is the internal query which is failing:

SELECT TABLE_CAT, TABLE_SCHEM, TABLE_NAME, COLUMN_NAME, DATA_TYPE,
  TYPE_NAME, COLUMN_SIZE, BUFFER_LENGTH, DECIMAL_DIGITS, NUM_PREC_RADIX,
  NULLABLE, REMARKS, COLUMN_DEF, SQL_DATA_TYPE, SQL_DATETIME_SUB,
  CHAR_OCTET_LENGTH, ORDINAL_POSITION, IS_NULLABLE,
  SCOPE_CATALOG, SCOPE_SCHEMA, SCOPE_TABLE,
  SOURCE_DATA_TYPE, IS_AUTOINCREMENT, IS_GENERATEDCOLUMN
FROM system.jdbc.columns
WHERE TABLE_CAT = 'mongodb' AND TABLE_SCHEM LIKE 'eniosites' ESCAPE '\' AND TABLE_NAME LIKE 'enrichedsites' ESCAPE '\' AND COLUMN_NAME LIKE '%' ESCAPE '\'
ORDER BY TABLE_CAT, TABLE_SCHEM, TABLE_NAME, ORDINAL_POSITION

A naive SELECT *FROM system.jdbc.columnsleads to the same error as above

[ebyhr]

Please share the full stacktrace from webui, Trino and MongoDB version and commands to create a enrichedSites view.

[Ognian]

OK, since I cannot share my data I used sample data to reproduce the error.

I'm using MongoDB 4.2.7 Community

Go to https://github.com/SouthbankSoftware/dbkoda-data and Download as zip
unzip and go to OrdersExample directory
mongorestore --uri mongodb://localhost:27117/test dump
you have now an OrderExample database

use the latest MongoDB Compass (Version 1.26.1-beta.6 (1.26.1-beta.6)) to add the following mongodb view:

1. go to the orders collection and go to aggregations
2. click on the drop down arrow next to the + and choose new pipeline from text

4. and add

[{$lookup: {
  from: 'customers',
  localField: 'customerId',
  foreignField: '_id',
  as: 'customer'
}}]

3 click on the drop down arrow next to the green save button and choose create view, name it 'lookupCustomer'

Now you have a 'lookupCustomer' View, and it appears in Compass on the left side with the other OrdersExample collections. By clicking on lookupCustomer check that we have indeed the new field customer with one Object

Now go to i.e dbeaver and see the error:

As you can see Im using the newest trino version: 354

query:

SELECT TABLE_CAT, TABLE_SCHEM, TABLE_NAME, COLUMN_NAME, DATA_TYPE,
  TYPE_NAME, COLUMN_SIZE, BUFFER_LENGTH, DECIMAL_DIGITS, NUM_PREC_RADIX,
  NULLABLE, REMARKS, COLUMN_DEF, SQL_DATA_TYPE, SQL_DATETIME_SUB,
  CHAR_OCTET_LENGTH, ORDINAL_POSITION, IS_NULLABLE,
  SCOPE_CATALOG, SCOPE_SCHEMA, SCOPE_TABLE,
  SOURCE_DATA_TYPE, IS_AUTOINCREMENT, IS_GENERATEDCOLUMN
FROM system.jdbc.columns
WHERE TABLE_CAT = 'mongodb' AND TABLE_SCHEM LIKE 'orderexample' ESCAPE '\' AND TABLE_NAME LIKE 'lookupcustomer' ESCAPE '\' AND COLUMN_NAME LIKE '%' ESCAPE '\'
ORDER BY TABLE_CAT, TABLE_SCHEM, TABLE_NAME, ORDINAL_POSITION

stacktrace:

com.google.common.util.concurrent.UncheckedExecutionException: com.mongodb.MongoCommandException: Command failed with error 166 (CommandNotSupportedOnView): 'Namespace OrderExample.lookupCustomer is a view, not a collection' on server host.docker.internal:27117. The full response is { "operationTime" : { "$timestamp" : { "t" : 1617622153, "i" : 1 } }, "ok" : 0.0, "errmsg" : "Namespace OrderExample.lookupCustomer is a view, not a collection", "code" : 166, "codeName" : "CommandNotSupportedOnView", "$clusterTime" : { "clusterTime" : { "$timestamp" : { "t" : 1617622153, "i" : 1 } }, "signature" : { "hash" : { "$binary" : "AAAAAAAAAAAAAAAAAAAAAAAAAAA=", "$type" : "00" }, "keyId" : { "$numberLong" : "0" } } } }
	at com.google.common.cache.LocalCache$Segment.get(LocalCache.java:2051)
	at com.google.common.cache.LocalCache.get(LocalCache.java:3951)
	at com.google.common.cache.LocalCache.getOrLoad(LocalCache.java:3974)
	at com.google.common.cache.LocalCache$LocalLoadingCache.get(LocalCache.java:4935)
	at com.google.common.cache.LocalCache$LocalLoadingCache.getUnchecked(LocalCache.java:4941)
	at io.trino.plugin.mongodb.MongoSession.getTable(MongoSession.java:167)
	at io.trino.plugin.mongodb.MongoMetadata.getTableMetadata(MongoMetadata.java:315)
	at io.trino.plugin.mongodb.MongoMetadata.listTableColumns(MongoMetadata.java:133)
	at io.trino.metadata.MetadataManager.listTableColumns(MetadataManager.java:608)
	at io.trino.metadata.MetadataListing.listTableColumns(MetadataListing.java:135)
	at io.trino.connector.system.jdbc.ColumnJdbcTable.cursor(ColumnJdbcTable.java:257)
	at io.trino.connector.system.SystemPageSourceProvider$1.cursor(SystemPageSourceProvider.java:128)
	at io.trino.split.MappedRecordSet.cursor(MappedRecordSet.java:53)
	at io.trino.spi.connector.RecordPageSource.<init>(RecordPageSource.java:37)
	at io.trino.connector.system.SystemPageSourceProvider.createPageSource(SystemPageSourceProvider.java:107)
	at io.trino.spi.connector.ConnectorPageSourceProvider.createPageSource(ConnectorPageSourceProvider.java:68)
	at io.trino.split.PageSourceManager.createPageSource(PageSourceManager.java:64)
	at io.trino.operator.ScanFilterAndProjectOperator$SplitToPages.process(ScanFilterAndProjectOperator.java:254)
	at io.trino.operator.ScanFilterAndProjectOperator$SplitToPages.process(ScanFilterAndProjectOperator.java:182)
	at io.trino.operator.WorkProcessorUtils$3.process(WorkProcessorUtils.java:319)
	at io.trino.operator.WorkProcessorUtils$ProcessWorkProcessor.process(WorkProcessorUtils.java:372)
	at io.trino.operator.WorkProcessorUtils$3.process(WorkProcessorUtils.java:306)
	at io.trino.operator.WorkProcessorUtils$ProcessWorkProcessor.process(WorkProcessorUtils.java:372)
	at io.trino.operator.WorkProcessorUtils$3.process(WorkProcessorUtils.java:306)
	at io.trino.operator.WorkProcessorUtils$ProcessWorkProcessor.process(WorkProcessorUtils.java:372)
	at io.trino.operator.WorkProcessorUtils.getNextState(WorkProcessorUtils.java:221)
	at io.trino.operator.WorkProcessorUtils.lambda$processStateMonitor$2(WorkProcessorUtils.java:200)
	at io.trino.operator.WorkProcessorUtils$ProcessWorkProcessor.process(WorkProcessorUtils.java:372)
	at io.trino.operator.WorkProcessorUtils.getNextState(WorkProcessorUtils.java:221)
	at io.trino.operator.WorkProcessorUtils.lambda$finishWhen$3(WorkProcessorUtils.java:215)
	at io.trino.operator.WorkProcessorUtils$ProcessWorkProcessor.process(WorkProcessorUtils.java:372)
	at io.trino.operator.WorkProcessorSourceOperatorAdapter.getOutput(WorkProcessorSourceOperatorAdapter.java:149)
	at io.trino.operator.Driver.processInternal(Driver.java:387)
	at io.trino.operator.Driver.lambda$processFor$9(Driver.java:291)
	at io.trino.operator.Driver.tryWithLock(Driver.java:683)
	at io.trino.operator.Driver.processFor(Driver.java:284)
	at io.trino.execution.SqlTaskExecution$DriverSplitRunner.processFor(SqlTaskExecution.java:1075)
	at io.trino.execution.executor.PrioritizedSplitRunner.process(PrioritizedSplitRunner.java:163)
	at io.trino.execution.executor.TaskExecutor$TaskRunner.run(TaskExecutor.java:484)
	at io.trino.$gen.Trino_354____20210404_135930_2.run(Unknown Source)
	at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
	at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
	at java.base/java.lang.Thread.run(Thread.java:834)
Caused by: com.mongodb.MongoCommandException: Command failed with error 166 (CommandNotSupportedOnView): 'Namespace OrderExample.lookupCustomer is a view, not a collection' on server host.docker.internal:27117. The full response is { "operationTime" : { "$timestamp" : { "t" : 1617622153, "i" : 1 } }, "ok" : 0.0, "errmsg" : "Namespace OrderExample.lookupCustomer is a view, not a collection", "code" : 166, "codeName" : "CommandNotSupportedOnView", "$clusterTime" : { "clusterTime" : { "$timestamp" : { "t" : 1617622153, "i" : 1 } }, "signature" : { "hash" : { "$binary" : "AAAAAAAAAAAAAAAAAAAAAAAAAAA=", "$type" : "00" }, "keyId" : { "$numberLong" : "0" } } } }
	at com.mongodb.internal.connection.ProtocolHelper.getCommandFailureException(ProtocolHelper.java:179)
	at com.mongodb.internal.connection.InternalStreamConnection.receiveCommandMessageResponse(InternalStreamConnection.java:293)
	at com.mongodb.internal.connection.InternalStreamConnection.sendAndReceive(InternalStreamConnection.java:255)
	at com.mongodb.internal.connection.UsageTrackingInternalConnection.sendAndReceive(UsageTrackingInternalConnection.java:99)
	at com.mongodb.internal.connection.DefaultConnectionPool$PooledConnection.sendAndReceive(DefaultConnectionPool.java:444)
	at com.mongodb.internal.connection.CommandProtocolImpl.execute(CommandProtocolImpl.java:72)
	at com.mongodb.internal.connection.DefaultServer$DefaultServerProtocolExecutor.execute(DefaultServer.java:200)
	at com.mongodb.internal.connection.DefaultServerConnection.executeProtocol(DefaultServerConnection.java:269)
	at com.mongodb.internal.connection.DefaultServerConnection.command(DefaultServerConnection.java:131)
	at com.mongodb.internal.connection.DefaultServerConnection.command(DefaultServerConnection.java:123)
	at com.mongodb.operation.CommandOperationHelper.executeWrappedCommandProtocol(CommandOperationHelper.java:242)
	at com.mongodb.operation.CommandOperationHelper.executeWrappedCommandProtocol(CommandOperationHelper.java:233)
	at com.mongodb.operation.CommandOperationHelper.executeWrappedCommandProtocol(CommandOperationHelper.java:136)
	at com.mongodb.operation.ListIndexesOperation$1.call(ListIndexesOperation.java:143)
	at com.mongodb.operation.ListIndexesOperation$1.call(ListIndexesOperation.java:138)
	at com.mongodb.operation.OperationHelper.withConnectionSource(OperationHelper.java:457)
	at com.mongodb.operation.OperationHelper.withConnection(OperationHelper.java:401)
	at com.mongodb.operation.ListIndexesOperation.execute(ListIndexesOperation.java:138)
	at com.mongodb.operation.ListIndexesOperation.execute(ListIndexesOperation.java:69)
	at com.mongodb.client.internal.MongoClientDelegate$DelegateOperationExecutor.execute(MongoClientDelegate.java:179)
	at com.mongodb.client.internal.MongoIterableImpl.execute(MongoIterableImpl.java:132)
	at com.mongodb.client.internal.MongoIterableImpl.iterator(MongoIterableImpl.java:86)
	at io.trino.plugin.mongodb.MongoIndex.parse(MongoIndex.java:36)
	at io.trino.plugin.mongodb.MongoSession.getIndexes(MongoSession.java:266)
	at io.trino.plugin.mongodb.MongoSession.loadTableSchema(MongoSession.java:226)
	at com.google.common.cache.CacheLoader$FunctionToCacheLoader.load(CacheLoader.java:165)
	at com.google.common.cache.LocalCache$LoadingValueReference.loadFuture(LocalCache.java:3529)
	at com.google.common.cache.LocalCache$Segment.loadSync(LocalCache.java:2278)
	at com.google.common.cache.LocalCache$Segment.lockedGetOrLoad(LocalCache.java:2155)
	at com.google.common.cache.LocalCache$Segment.get(LocalCache.java:2045)
	... 42 more

[Ognian]

PS my catalog settings are:

echo '
connector.name=mongodb
mongodb.seeds=host.docker.internal:27117
mongodb.required-replica-set=rs0
mongodb.case-insensitive-name-matching=true
' >mongodb.properties

[Ognian]

OK I was able to find out via mongodb logging what is actually causing the error:

2021-04-06T10:16:08.963+0000 I  COMMAND  [conn67] command OrderExample.$cmd command: listCollections { listCollections: 1, cursor: {}, nameOnly: true, $db: "OrderExample", $clusterTime: { clusterTime: Timestamp(1617704162, 1), signature: { hash: BinData(0, 0000000000000000000000000000000000000000), keyId: 0 } }, lsid: { id: UUID("78d292f8-8ca3-42c1-ba05-7192f407f84d") } } numYields:0 reslen:640 locks:{ ParallelBatchWriterMode: { acquireCount: { r: 1 } }, ReplicationStateTransition: { acquireCount: { w: 1 } }, Global: { acquireCount: { r: 1 } }, Database: { acquireCount: { r: 1 } }, Collection: { acquireCount: { r: 8 } }, Mutex: { acquireCount: { r: 1 } } } protocol:op_msg 0ms
2021-04-06T10:16:08.974+0000 D1 COMMAND  [conn67] assertion while executing command 'listIndexes' on database 'OrderExample' with arguments '{ listIndexes: "lookupCustomer", cursor: {}, $db: "OrderExample", $clusterTime: { clusterTime: Timestamp(1617704162, 1), signature: { hash: BinData(0, 0000000000000000000000000000000000000000), keyId: 0 } }, lsid: { id: UUID("78d292f8-8ca3-42c1-ba05-7192f407f84d") } }': CommandNotSupportedOnView: Namespace OrderExample.lookupCustomer is a view, not a collection
2021-04-06T10:16:08.976+0000 I  COMMAND  [conn67] command OrderExample.$cmd command: listIndexes { listIndexes: "lookupCustomer", cursor: {}, $db: "OrderExample", $clusterTime: { clusterTime: Timestamp(1617704162, 1), signature: { hash: BinData(0, 0000000000000000000000000000000000000000), keyId: 0 } }, lsid: { id: UUID("78d292f8-8ca3-42c1-ba05-7192f407f84d") } } numYields:0 ok:0 errMsg:"Namespace OrderExample.lookupCustomer is a view, not a collection" errName:CommandNotSupportedOnView errCode:166 reslen:291 locks:{ ReplicationStateTransition: { acquireCount: { w: 1 } }, Global: { acquireCount: { r: 1 } }, Database: { acquireCount: { r: 1 } }, Collection: { acquireCount: { r: 2 } }, Mutex: { acquireCount: { r: 1 } } } protocol:op_msg 1ms

the mongodb connector is doing a listIndexeson a view (since it even doesn't know that the table is a view) an mongo errors ...

[Ognian]

And the corresponding Line in the connector is

trino/plugin/trino-mongodb/src/main/java/io/trino/plugin/mongodb/MongoSession.java

Line 266 in 0c58b3d

return MongoIndex.parse(getCollection(tableName).listIndexes());

so maybe a try catch for this case could be enough...

[ebyhr]

As far as I checked, the cause is case sensitivity and it should be captured in isView function or the method caller. I'm looking into now.

[Ognian]

@ebyhr just one more hint:
before adding mongodb.case-insensitive-name-matching=trueI was not able to see a lot of my collections, I think that even lower case collections with "column names" with mixed case where a problem...

[ebyhr]

before adding mongodb.case-insensitive-name-matching=trueI was not able to see a lot of my collections

It's expected behavior.

I think that even lower case collections with "column names" with mixed case where a problem...

Do you mean this issue happens with lowercase? If so, can you share the steps to reproduce?

[Ognian]

OK I tested again without setting mongodb.case-insensitive-name-matching (so it is false):

• databases with a mixed case name are displayed in lower case but have neither tables nor views:
  order example is actually OrderExample

• if the database name is lower case and the collection name is mixed case, the collection is displayed but without any properties
  patient is actually Patient

and trying to do a `select * ` ends with `Query failed: SELECT * not allowed from relation that has no columns`

• if database name is all lower case and collection name is all lower case but property name is mixed case, property is displayed as all lower case and queries are possible -contenttype is actually contentType

I think now that this is all expected behaviour and I understand now how it works...