Hi, I would assume that data in your caches is not collocated (https://apacheignite.readme.io/docs/affinity-collocation). You should make sure that the entries in SubscriptionOrder and in SubscriptionCustomer that have the same RRCUSTOMERNUMBER are stored on the same node.
By default, SQL assumes that the data is not collocated. If it isn’t, SQL may yield incorrect results (because it will not find some combinations). Also, you should rewrite your query to use JOIN instead of WHERE … IN (SELECT …). First of all, it will have better performance. Second, JOINs may be configured to work on non-collocated data: new SqlFieldsQuery(sql).setDistributedJoins(true). To summarize, 1) Rewrite your SQL to use JOIN 2) Then either a. Collocate your data as described at https://apacheignite.readme.io/docs/affinity-collocation (e.g. by using @AffinityKeyMapped). b. OR enable distributed joins (performance will be worse in this case). Thanks, Stan From: hulitao198758 Sent: 20 апреля 2018 г. 9:13 To: [email protected] Subject: Missing shu is a cross-cache associated query. My ignite configuration file looks something like this. <property name="cacheConfiguration"> <list> <bean class="org.apache.ignite.configuration.CacheConfiguration"> <property name='dataRegionName' value='Subscription_Region'/> <property name="name" value="SubscriptionOrderCache"/> <property name="cacheMode" value="PARTITIONED"/> <property name="atomicityMode" value="TRANSACTIONAL"/> <property name="backups" value="0"/> <property name="rebalanceMode" value="SYNC"/> <property name="rebalanceBatchSize" value="#{2 * 1024 * 1024}"/> <property name="rebalanceThrottle" value="100"/> <property name="writeBehindEnabled" value="true"/> <property name="writeBehindFlushThreadCount" value="10"/> <property name="writeBehindFlushFrequency" value="100"/> <property name="writeBehindFlushSize" value="51200"/> <property name="queryEntities"> <list> <bean class="org.apache.ignite.cache.QueryEntity"> <property name="keyType" value="java.lang.Long"/> <property name="valueType" value="subscription.domain.subscription.entity.SubscriptionOrder"/> <property name="keyFieldName" value="uuid"/> <property name="keyFields"> <list> <value>uuid</value> </list> </property> <property name="fields"> <map> <entry key="uuid" value="java.lang.Long"/> <entry key="id" value="java.lang.Long"/> <entry key="orderNumber" value="java.lang.Long"/> <entry key="status" value="java.lang.String"/> <entry key="originalOrderNumber" value="java.lang.String"/> <entry key="poNumber" value="java.lang.String"/> <entry key="salesOrg" value="java.lang.String"/> <entry key="salesOffice" value="java.lang.String"/> <entry key="sourceSystem" value="java.lang.String"/> <entry key="segment" value="java.lang.String"/> <entry key="rrCustomerNumber" value="java.lang.Long"/> <entry key="currency" value="java.lang.String"/> <entry key="paymentMethod" value="java.lang.String"/> <entry key="subscriptionStartDate" value="java.lang.Long"/> <entry key="subscriptionEndDate" value="java.lang.Long"/> <entry key="creditcardTokenNumber" value="java.lang.String"/> <entry key="creditcardType" value="java.lang.String"/> <entry key="creditcardName" value="java.lang.String"/> <entry key="creditcardExpireationDate" value="java.lang.Long"/> <entry key="initialAuthorizedAmt" value="java.math.BigDecimal"/> <entry key="initialAuthorizationNumber" value="java.lang.String"/> <entry key="billingTimeZone" value="java.lang.String"/> <entry key="eccCustomerId" value="java.lang.String"/> <entry key="referenceId" value="java.lang.Long"/> <entry key="orderVersionNumber" value="java.lang.Long"/> <entry key="effectiveFrom" value="java.lang.Long"/> <entry key="effectiveTo" value="java.lang.Long"/> <entry key="transactionId" value="java.lang.String"/> <entry key="phoneAuthorizationStatus" value="java.lang.String"/> <entry key="authorizationResultStatusCode" value="java.lang.String"/> <entry key="requestedAuthorizationAmount" value="java.math.BigDecimal"/> <entry key="addressVerificationServiceResult" value="java.lang.String"/> <entry key="addressVerificationServiceZipCodeResult" value="java.lang.String"/> <entry key="authorizationMessageText" value="java.lang.String"/> <entry key="origOrderUserId" value="java.lang.String"/> <entry key="updatedAt" value="java.lang.Long"/> <entry key="updatedBy" value="java.lang.String"/> </map> </property> </bean> </list> </property> </bean> <bean class="org.apache.ignite.configuration.CacheConfiguration"> <property name='dataRegionName' value='Subscription_Region'/> <property name="name" value="SubscriptionCustomerCache"/> <property name="cacheMode" value="PARTITIONED"/> <property name="atomicityMode" value="TRANSACTIONAL"/> <property name="backups" value="0"/> <property name="rebalanceMode" value="SYNC"/> <property name="rebalanceBatchSize" value="#{2 * 1024 * 1024}"/> <property name="rebalanceThrottle" value="100"/> <property name="writeBehindEnabled" value="true"/> <property name="writeBehindFlushThreadCount" value="10"/> <property name="writeBehindFlushFrequency" value="100"/> <property name="writeBehindFlushSize" value="51200"/> <property name="queryEntities"> <list> <bean class="org.apache.ignite.cache.QueryEntity"> <property name="keyType" value="java.lang.Long"/> <property name="valueType" value="subscription.domain.customer.entity.SubscriptionCustomer"/> <property name="keyFieldName" value="uuid"/> <property name="keyFields"> <list> <value>uuid</value> </list> </property> <property name="fields"> <map> <entry key="uuid" value="java.lang.Long"/> <entry key="id" value="java.lang.Long"/> <entry key="rrCustomerNumber" value="java.lang.Long"/> <entry key="lenovoBpId" value="java.lang.String"/> <entry key="lenovoId" value="java.lang.String"/> <entry key="customerName" value="java.lang.String"/> <entry key="customerType" value="java.lang.String"/> <entry key="status" value="java.lang.String"/> <entry key="domain" value="java.lang.String"/> <entry key="emailAddress" value="java.lang.String"/> <entry key="currency" value="java.lang.String"/> <entry key="language" value="java.lang.String"/> <entry key="country" value="java.lang.String"/> <entry key="city" value="java.lang.String"/> <entry key="state" value="java.lang.String"/> <entry key="address" value="java.lang.String"/> <entry key="address1" value="java.lang.String"/> <entry key="postalCode" value="java.lang.String"/> <entry key="firstName" value="java.lang.String"/> <entry key="lastName" value="java.lang.String"/> <entry key="phoneNumber" value="java.lang.String"/> <entry key="updatedAt" value="java.lang.Long"/> <entry key="updatedBy" value="java.lang.String"/> <entry key="timeZone" value="java.lang.String"/> <entry key="referenceId" value="java.lang.Long"/> </map> </property> </bean> </list> </property> </bean> </list> </property> Then, after launching the ignite project with spring boot, I wrote @repositoryconfig (cacheName = "SubscriptionOrderCache") Public interface SubscriptionOrderRepository extends IgniteRepository < SubscriptionOrder, Long > Save method, respectively in the two Cache to Save a few same data, they all have UUID, is a primary key, and then select * from "SubscriptionOrderCache" SubscriptionOrder shows three data, select * from "SubscriptionCustomerCache" SUBSCRIPTIONCustomer also shows three data, their RRCUSTOMERNUMBER the value of the field is the same, but the execution SELECT * FROM "SubscriptionOrderCache".SUBSCRIPTIONOrder a WHERE r RCUSTOMERNUMBER in( The SELECT RRCUSTOMERNUMBER FROM "SubscriptionCustomerCache". SUBSCRIPTIONCustomer ) the result of this statement is not correct. Where did I configure it? JAVA POJO Like this： IgniteCache<AffinityKey<DomainEntity>, SubscriptionOrder> cache =ignite.cache("SubscriptionOrderCache"); String sql ="SELECT * FROM SUBSCRIPTIONOrder WHERE RRCUSTOMERNUMBER in(" + "SELECT RRCUSTOMERNUMBER FROM \"SubscriptionCustomerCache\".SUBSCRIPTIONCustomer" + ")"; List<List<?>> cursor = cache.query(new SqlFieldsQuery(sql)).getAll(); System.out.println(cursor); -- Sent from: http://apache-ignite-users.70518.x6.nabble.com/
