Search code examples
sqlpostgresqlcachingignite

Different query results for ignite cache and postgres as a 3rd party database (empty result set for ignite cache queries)

I am currently trying out the ignite database as an in-memory chache on top of a postgres database. The data that sits in the postgres database is basically data that was produced in conformity with the tpc-h schema. After the data was inserted into the postgres I loaded the data into the ignite cache. According to some count(*) queries on the ignite cache and the postgres, every row from the postgres is prensent in the ignite cache. Thats the given situation. Now I would asume a query on the postgres gives the same result as a query on the ignite cache. That' not the case for my queries.

This is the postgres query:

SELECT l_orderkey, SUM(l_extendedprice * ( 1 - l_discount )) AS revenue, o_orderdate, o_shippriority FROM customer, orders, lineitem WHERE c_mktsegment = 'BUILDING' AND c_custkey = o_custkey AND l_orderkey = o_orderkey AND o_orderdate < DATE '1998-06-01' AND l_shipdate > DATE '1998-06-01' GROUP BY l_orderkey, o_orderdate, o_shippriority ORDER BY revenue DESC, o_orderdate LIMIT 10;";

This is the ignite query:

SELECT l_orderkey, SUM(l_extendedprice * ( 1 - l_discount )) AS revenue, o_orderdate, o_shippriority FROM "CustomerCache".customer, "OrdersCache".orders, "LineitemCache".lineitem WHERE c_mktsegment = 'BUILDING' AND c_custkey = o_custkey AND l_orderkey = o_orderkey AND o_orderdate < DATE '1998-06-01' AND l_shipdate > DATE '1998-06-01' GROUP BY l_orderkey, o_orderdate, o_shippriority ORDER BY revenue DESC, o_orderdate LIMIT 10;";

As you can see above, the queries are nearly identical. Only the "FROM" parts are different. This must be like this because the ignite cache needs to be addressed in the following way: "CACHENAME".TABLENAME .

Ignite results:

Ignite results

Postgres results:

Postgres results

The ignite cache doesnt't return any results. The postgres returns the expected results. How is that possible? As a reminder: The complete data was loaded into the ignite cache. When I count the rows in the chached tables, they are as many as in the postgres tables. Question: Why doesn't the ignite resturn the right results for the query above.

The Ignite consists of two nodes deployed in a GKE Cluster. The Ignite config looks like this: NODE-CONFIGURATION.XML

The data was loaded from postgres to cache by deploying an ignite client to the cluster. This client runs the java function IgniteCache.#loadCache() on every cache.

Solution

  • The empty resultset wasn't a problem regarding the cache config. It was a problem regarding the dataset. We got trailing spaces in some fields. Executing a query that compares the field with an = instead of a LIKE only works without trailing spaces ;)