Prasad, This is a bug. It doesn't let you use fields of sub-select results in WHERE conditions. I filed a JIRA ticket for it: IGNITE-9156 <https://issues.apache.org/jira/browse/IGNITE-9156> Thank you for the report!
You can embed the equality condition into the sub-select to avoid this problem. Denis ср, 1 авг. 2018 г. в 12:50, Prasad Bhalerao <[email protected]>: > I am getting Assertion error while executing following SQL. Please check > this highlighted part which causing this error. > > Can someone please advise? Is this a bug or am I doing something wrong? > > > SELECT tmp.assetGroupId, > tmp.name, > tmp.businessImpact, > tmp.userId , > tmp.modifiedBy, > tmp.createdDate, > tmp.updatedDate, > (SELECT ipv4.ipStart, > ipv4.ipEnd > FROM IPV4_ASSET_GROUP_DETAIL_CACHE.IpV4AssetGroupData ipv4 > WHERE ipv4.assetGroupId = assetGroupId LIMIT 1 > ) ip, > (SELECT dmn.id, > dmn.value > FROM ASSET_GROUP_DOMAIN_CACHE.AssetGroupDomainData agdmn > JOIN DOMAIN_CACHE.DomainData dmn > ON ( dmn.id = agdmn.domainId) > WHERE agdmn.assetGroupId = assetGroupId LIMIT 1 > ) dmn, > (SELECT uac.firstName, > uac.lastName > FROM USER_ACCOUNT_CACHE.UserAccountData uac > WHERE uac.userId = tmp.userId > ) owner, > (SELECT uac.firstName, > uac.lastName > FROM USER_ACCOUNT_CACHE.UserAccountData uac > WHERE uac.userId = tmp.modifiedBy > ) modified > FROM > (SELECT ag.assetGroupId, > ag.name, > ag.businessImpact, > ag.userId, > ag.modifiedBy, > ag.createdDate, > ag.updatedDate > FROM AssetGroupData ag > JOIN UNIT_ASSET_GROUP_CACHE.UnitAssetGroupData uag > ON (uag.assetGroupId = ag.assetGroupId) > WHERE ag.subscriptionId = ? > AND uag.unitId = ? > AND name LIKE ? > AND updatedDate >= ? > UNION ALL > SELECT ag.assetGroupId, > ag.name, > ag.businessImpact, > ag.userId, > ag.modifiedBy, > ag.createdDate, > ag.updatedDate > FROM AssetGroupData ag > JOIN USER_ACCOUNT_CACHE.UserAccountData uad > ON (uad.userId = ag.userId) > WHERE ag.subscriptionId = ? > AND uad.unitId = ? > AND name LIKE ? > AND updatedDate >= ? > ) tmp > WHERE tmp.subscriptionId = ? > AND tmp.userId = ? > ORDER BY assetGroupId LIMIT 20 OFFSET 0 > > > Exception: > > > java.lang.AssertionError > at > org.apache.ignite.internal.processors.query.h2.sql.GridSqlQuerySplitter.extractPartitionFromEquality(GridSqlQuerySplitter.java:2334) > at > org.apache.ignite.internal.processors.query.h2.sql.GridSqlQuerySplitter.extractPartition(GridSqlQuerySplitter.java:2268) > at > org.apache.ignite.internal.processors.query.h2.sql.GridSqlQuerySplitter.extractPartition(GridSqlQuerySplitter.java:2279) > at > org.apache.ignite.internal.processors.query.h2.sql.GridSqlQuerySplitter.derivePartitionsFromQuery(GridSqlQuerySplitter.java:2250) > at > org.apache.ignite.internal.processors.query.h2.sql.GridSqlQuerySplitter.splitSelect(GridSqlQuerySplitter.java:1539) > at > org.apache.ignite.internal.processors.query.h2.sql.GridSqlQuerySplitter.splitQueryModel(GridSqlQuerySplitter.java:1227) > at > org.apache.ignite.internal.processors.query.h2.sql.GridSqlQuerySplitter.splitQuery(GridSqlQuerySplitter.java:306) > at > org.apache.ignite.internal.processors.query.h2.sql.GridSqlQuerySplitter.split(GridSqlQuerySplitter.java:224) > at > org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.split(IgniteH2Indexing.java:1938) > at > org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.parseAndSplit(IgniteH2Indexing.java:1900) > at > org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.querySqlFields(IgniteH2Indexing.java:1652) > at > org.apache.ignite.internal.processors.query.GridQueryProcessor$4.applyx(GridQueryProcessor.java:2035) > at > org.apache.ignite.internal.processors.query.GridQueryProcessor$4.applyx(GridQueryProcessor.java:2030) > at > org.apache.ignite.internal.util.lang.IgniteOutClosureX.apply(IgniteOutClosureX.java:36) > at > org.apache.ignite.internal.processors.query.GridQueryProcessor.executeQuery(GridQueryProcessor.java:2578) > at > org.apache.ignite.internal.processors.query.GridQueryProcessor.querySqlFields(GridQueryProcessor.java:2044) > at > org.apache.ignite.internal.processors.cache.IgniteCacheProxyImpl.query(IgniteCacheProxyImpl.java:664) > at > org.apache.ignite.internal.processors.cache.IgniteCacheProxyImpl.query(IgniteCacheProxyImpl.java:615) > at > org.apache.ignite.internal.processors.cache.GatewayProtectedCacheProxy.query(GatewayProtectedCacheProxy.java:356) > at > com.qualys.agms.grid.dao.AbstractDataGridDAO.getFieldResultsByCriteria(AbstractDataGridDAO.java:84) >
