search

trinodb / trino

Official repository of Trino, the distributed SQL query engine for big data, formerly known as PrestoSQL (https://trino.io)
https://trino.io
Apache License 2.0
10.36k stars 2.98k forks source link

mongodb select * on view returns error CommandNotSupportedOnView #7491

Closed Ognian closed 3 years ago

Ognian commented 3 years ago

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 commented 3 years ago

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

Ognian commented 3 years ago

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 commented 3 years ago

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 commented 3 years ago

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

Ognian commented 3 years ago

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

    image

  3. 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'

    image

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

image

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

image

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

image

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 commented 3 years ago

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 commented 3 years ago

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 commented 3 years ago

And the corresponding Line in the connector is https://github.com/trinodb/trino/blob/0c58b3d5613099143d7ebb1a473e9b5227b423b4/plugin/trino-mongodb/src/main/java/io/trino/plugin/mongodb/MongoSession.java#L266 so maybe a try catch for this case could be enough...

ebyhr commented 3 years ago

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 commented 3 years ago

@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 commented 3 years ago

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 commented 3 years ago

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

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