bug: no messages returned from store node when multiple content topics and start/end time are used

[richard-ramos]

If I do the following query (using "/dns4/store-02.gc-us-central1-a.shards.test.statusim.net/tcp/30303/p2p/16Uiu2HAmJnVR7ZzFaYvciPVafUXuYGLHPzSUigqAmeNw9nJUVGeM" because i'm sure that storenode has the messages i need)

{
"request_id":"0102030405060708",
"query":{
  "pubsub_topic":"/waku/2/rs/16/32",
  "content_filters":[
    {"content_topic":"/waku/1/0xfc8069af/rfc26"},
    {"content_topic":"/waku/1/0xbb66b9bc/rfc26"},
    {"content_topic":"/waku/1/0x73fef558/rfc26"},
    {"content_topic":"/waku/1/0x409371d4/rfc26"},
    {"content_topic":"/waku/1/0xf0fbb162/rfc26"}
  ],
  "paging_info":{"page_size":100},
  "start_time":1702486518462700562,
  "end_time":1702493718462700650
  }
}

I end up with an empty response:

{"request_id":"0102030405060708","response":{}}

However, if I change the query to only include a single content topic, like this:

{
"request_id":"0102030405060708",
"query":{
  "pubsub_topic":"/waku/2/rs/16/32",
  "content_filters":[
    {"content_topic":"/waku/1/0xbb66b9bc/rfc26"},
  ],
  "paging_info":{"page_size":100},
  "start_time":1702486518462700562,
  "end_time":1702493718462700650
  }
}

I get 3 messages!

[richard-ramos]

Running nwaku with: ./build/wakunode2 --rest --cluster-id=16 --pubsub-topic=/waku/2/rs/16/32

With a single content topic and no startTime/endTime I do indeed get 3 messages as expected:
http://127.0.0.1:8645/store/v1/messages?peerAddr=%2Fdns4%2Fstore-02.gc-us-central1-a.shards.test.statusim.net%2Ftcp%2F30303%2Fp2p%2F16Uiu2HAmJnVR7ZzFaYvciPVafUXuYGLHPzSUigqAmeNw9nJUVGeM&pubsubTopic=%2Fwaku%2F2%2Frs%2F16%2F32&contentTopics=%2Fwaku%2F1%2F0xbb66b9bc%2Frfc26

With a single content topic and start/endTime, i also get 3 messages
http://127.0.0.1:8645/store/v1/messages?peerAddr=%2Fdns4%2Fstore-02.gc-us-central1-a.shards.test.statusim.net%2Ftcp%2F30303%2Fp2p%2F16Uiu2HAmJnVR7ZzFaYvciPVafUXuYGLHPzSUigqAmeNw9nJUVGeM&pubsubTopic=%2Fwaku%2F2%2Frs%2F16%2F32&contentTopics=%2Fwaku%2F1%2F0xbb66b9bc%2Frfc26&startTime=1702486518462700562&endTime=1702493718462700650

With two content topics and no start/endTime, i get 3 messages two, which is okay
http://127.0.0.1:8645/store/v1/messages?peerAddr=%2Fdns4%2Fstore-02.gc-us-central1-a.shards.test.statusim.net%2Ftcp%2F30303%2Fp2p%2F16Uiu2HAmJnVR7ZzFaYvciPVafUXuYGLHPzSUigqAmeNw9nJUVGeM&pubsubTopic=%2Fwaku%2F2%2Frs%2F16%2F32&contentTopics=%2Fwaku%2F1%2F0xbb66b9bc%2Frfc26%2C%2Fwaku%2F1%2F0x74fef558%2Frfc26

But interestingly enough, with two content topics, and start and end time, i get no messages:
http://127.0.0.1:8645/store/v1/messages?peerAddr=%2Fdns4%2Fstore-02.gc-us-central1-a.shards.test.statusim.net%2Ftcp%2F30303%2Fp2p%2F16Uiu2HAmJnVR7ZzFaYvciPVafUXuYGLHPzSUigqAmeNw9nJUVGeM&pubsubTopic=%2Fwaku%2F2%2Frs%2F16%2F32&contentTopics=%2Fwaku%2F1%2F0xbb66b9bc%2Frfc26%2C%2Fwaku%2F1%2F0x74fef558%2Frfc26&startTime=1702486518462700562&endTime=1702493718462700650

So, looks like the problem is the combination of multiple content topics and start and end time?

[richard-ramos]

I'm thinking the problem might be related to how the content topic sequence is joined in here: waku/waku_archive/driver/postgres_driver/postgres_driver.nim

  if contentTopicSeq.len > 0 and
    pubsubTopic.isSome() and
    startTime.isSome() and
    endTime.isSome():

    ## Considered the most common query. Therefore, we use prepared statements to optimize it.
    return await s.getMessagesPreparedStmt(contentTopicSeq.join(","),
                                           PubsubTopic(pubsubTopic.get()),
                                           cursor,
                                           startTime.get(),
                                           endTime.get(),
                                           maxPageSize,
                                           ascendingOrder)

Because the prepared statement says:

WHERE contentTopic IN ($1)

and if we have multiple content topics like: "a", "b", and "c",
the join(",") will end up creating a single string like this:

"a,b,c"

so maybe the prepared statement is in the end doing something like

WHERE contentTopic IN ("a,b,c")

instead of

WHERE contentTopic IN ("a","b","c")

for what is worth, something I have seen done in status-go when we create this kind of IN, we first calculate the number of elements inside the query, i.e. if we have 3 content topics, we'd do something like this:

    myQuery := "SELECT * FROM TABLE WHERE COLUMN IN ($VALUES)"
    values := []string{"a", "b", "c"}
    s := make([]string, len(values))
    for i := range s {
        s[i] = "?"
    }

    myQuery = strings.Replace(myQuery, "$VALUES", strings.Join(s, ","), -1)

    fmt.Println(myQuery) // SELECT * FROM TABLE WHERE COLUMN IN (?,?,?)

perhaps something similar must be done?

[vpavlin]

I made a quick attempt on fixing this here, but no luck:) #2291

The simplest way to fix this seems to be just drop the predefined query, as the getMessagesArbitraryQuery seems to construct the query just fine (doing something similar to the golang reference).

[Ivansete-status]

Weekly Update

• achieved: bug fix store service in nim-waku when the query used more than one content topic