Uploaded image for project: 'Teiid'
  1. Teiid
  2. TEIID-3772

HBase - Phoenix returns more than one result for subquery which should return only one row

    XMLWordPrintable

Details

    • Bug
    • Resolution: Won't Do
    • Minor
    • None
    • 8.7.1.6_2
    • Misc. Connectors
    • None
    • Hide

      Query:
      SELECT intkey FROM BQT1.SmallA AS A WHERE stringkey = (SELECT stringkey FROM BQT1.SmallA AS B WHERE b.intnum = a.intnum) ORDER BY intkey

      Exception

      Unable to find source-code formatter for language: text. Available languages are: actionscript, ada, applescript, bash, c, c#, c++, cpp, css, erlang, go, groovy, haskell, html, java, javascript, js, json, lua, none, nyan, objc, perl, php, python, r, rainbow, ruby, scala, sh, sql, swift, visualbasic, xml, yaml
      11:05:39,105 WARN  [org.teiid.PROCESSOR] (Worker32_QueryProcessorQueue160) TEIID30020 Processing exception for request qvJ3png5LESA.54 'TEIID30504 Source: 22015 Unexpected exception while translating results: ERROR 215 (22015): Single-row sub-query returns more than one row. ERROR 215 (22015): Single-row sub-query returns more than one row. ERROR 215 (22015): ERROR 215 (22015): Single-row sub-query returns more than one row. ERROR 215 (22015): Single-row sub-query returns more than one row.'. Originally TeiidProcessingException SQLExceptionCode.java:388.: org.teiid.core.TeiidProcessingException: TEIID30504 Source: 22015 Unexpected exception while translating results: ERROR 215 (22015): Single-row sub-query returns more than one row. ERROR 215 (22015): Single-row sub-query returns more than one row. ERROR 215 (22015): ERROR 215 (22015): Single-row sub-query returns more than one row. ERROR 215 (22015): Single-row sub-query returns more than one row.
	at org.teiid.dqp.internal.process.DataTierTupleSource.exceptionOccurred(DataTierTupleSource.java:381) [teiid-engine-8.7.1.6_2-redhat-6.jar:8.7.1.6_2-redhat-6]
	at org.teiid.dqp.internal.process.DataTierTupleSource.nextTuple(DataTierTupleSource.java:154) [teiid-engine-8.7.1.6_2-redhat-6.jar:8.7.1.6_2-redhat-6]
	at org.teiid.query.processor.relational.AccessNode.nextBatchDirect(AccessNode.java:369) [teiid-engine-8.7.1.6_2-redhat-6.jar:8.7.1.6_2-redhat-6]
	at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:278) [teiid-engine-8.7.1.6_2-redhat-6.jar:8.7.1.6_2-redhat-6]
	at org.teiid.query.processor.relational.RelationalPlan.nextBatch(RelationalPlan.java:136) [teiid-engine-8.7.1.6_2-redhat-6.jar:8.7.1.6_2-redhat-6]
	at org.teiid.query.processor.QueryProcessor.nextBatchDirect(QueryProcessor.java:151) [teiid-engine-8.7.1.6_2-redhat-6.jar:8.7.1.6_2-redhat-6]
	at org.teiid.query.processor.QueryProcessor.nextBatch(QueryProcessor.java:114) [teiid-engine-8.7.1.6_2-redhat-6.jar:8.7.1.6_2-redhat-6]
	at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:159) [teiid-engine-8.7.1.6_2-redhat-6.jar:8.7.1.6_2-redhat-6]
	at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:141) [teiid-engine-8.7.1.6_2-redhat-6.jar:8.7.1.6_2-redhat-6]
	at org.teiid.dqp.internal.process.RequestWorkItem.processMore(RequestWorkItem.java:444) [teiid-engine-8.7.1.6_2-redhat-6.jar:8.7.1.6_2-redhat-6]
	at org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:326) [teiid-engine-8.7.1.6_2-redhat-6.jar:8.7.1.6_2-redhat-6]
	at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:51) [teiid-engine-8.7.1.6_2-redhat-6.jar:8.7.1.6_2-redhat-6]
	at org.teiid.dqp.internal.process.RequestWorkItem.run(RequestWorkItem.java:254) [teiid-engine-8.7.1.6_2-redhat-6.jar:8.7.1.6_2-redhat-6]
	at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:274) [teiid-engine-8.7.1.6_2-redhat-6.jar:8.7.1.6_2-redhat-6]
	at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119) [teiid-engine-8.7.1.6_2-redhat-6.jar:8.7.1.6_2-redhat-6]
	at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:210) [teiid-engine-8.7.1.6_2-redhat-6.jar:8.7.1.6_2-redhat-6]
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) [rt.jar:1.8.0_40]
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) [rt.jar:1.8.0_40]
	at java.lang.Thread.run(Thread.java:745) [rt.jar:1.8.0_40]
Caused by: org.teiid.translator.TranslatorException: 22015 Unexpected exception while translating results: ERROR 215 (22015): Single-row sub-query returns more than one row. ERROR 215 (22015): Single-row sub-query returns more than one row. ERROR 215 (22015): ERROR 215 (22015): Single-row sub-query returns more than one row. ERROR 215 (22015): Single-row sub-query returns more than one row.
	at org.teiid.translator.jdbc.JDBCQueryExecution.next(JDBCQueryExecution.java:351)
	at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.handleBatch(ConnectorWorkItem.java:388) [teiid-engine-8.7.1.6_2-redhat-6.jar:8.7.1.6_2-redhat-6]
	at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.more(ConnectorWorkItem.java:206) [teiid-engine-8.7.1.6_2-redhat-6.jar:8.7.1.6_2-redhat-6]
	at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:301) [teiid-engine-8.7.1.6_2-redhat-6.jar:8.7.1.6_2-redhat-6]
	at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:110) [teiid-engine-8.7.1.6_2-redhat-6.jar:8.7.1.6_2-redhat-6]
	at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:107) [teiid-engine-8.7.1.6_2-redhat-6.jar:8.7.1.6_2-redhat-6]
	at java.util.concurrent.FutureTask.run(FutureTask.java:266) [rt.jar:1.8.0_40]
	at org.teiid.dqp.internal.process.FutureWork.run(FutureWork.java:58) [teiid-engine-8.7.1.6_2-redhat-6.jar:8.7.1.6_2-redhat-6]
	... 6 more
Caused by: java.sql.SQLException: ERROR 215 (22015): Single-row sub-query returns more than one row. ERROR 215 (22015): Single-row sub-query returns more than one row. ERROR 215 (22015): ERROR 215 (22015): Single-row sub-query returns more than one row. ERROR 215 (22015): Single-row sub-query returns more than one row.
	at org.apache.phoenix.exception.SQLExceptionCode$Factory$1.newException(SQLExceptionCode.java:388)
	at org.apache.phoenix.exception.SQLExceptionInfo.buildException(SQLExceptionInfo.java:145)
	at org.apache.phoenix.util.ServerUtil.parseRemoteException(ServerUtil.java:131)
	at org.apache.phoenix.util.ServerUtil.parseServerExceptionOrNull(ServerUtil.java:115)
	at org.apache.phoenix.util.ServerUtil.parseServerException(ServerUtil.java:104)
	at org.apache.phoenix.iterate.BaseResultIterators.getIterators(BaseResultIterators.java:553)
	at org.apache.phoenix.iterate.ConcatResultIterator.getIterators(ConcatResultIterator.java:50)
	at org.apache.phoenix.iterate.ConcatResultIterator.currentIterator(ConcatResultIterator.java:97)
	at org.apache.phoenix.iterate.ConcatResultIterator.next(ConcatResultIterator.java:117)
	at org.apache.phoenix.iterate.DelegateResultIterator.next(DelegateResultIterator.java:44)
	at org.apache.phoenix.jdbc.PhoenixResultSet.next(PhoenixResultSet.java:773)
	at org.jboss.jca.adapters.jdbc.WrappedResultSet.next(WrappedResultSet.java:1862)
	at org.teiid.translator.jdbc.JDBCQueryExecution.next(JDBCQueryExecution.java:338)
	... 13 more
      Show
      Query: SELECT intkey FROM BQT1.SmallA AS A WHERE stringkey = (SELECT stringkey FROM BQT1.SmallA AS B WHERE b.intnum = a.intnum) ORDER BY intkey Exception Unable to find source-code formatter for language: text. Available languages are: actionscript, ada, applescript, bash, c, c#, c++, cpp, css, erlang, go, groovy, haskell, html, java, javascript, js, json, lua, none, nyan, objc, perl, php, python, r, rainbow, ruby, scala, sh, sql, swift, visualbasic, xml, yaml 11:05:39,105 WARN [org.teiid.PROCESSOR] (Worker32_QueryProcessorQueue160) TEIID30020 Processing exception for request qvJ3png5LESA.54 'TEIID30504 Source: 22015 Unexpected exception while translating results: ERROR 215 (22015): Single-row sub-query returns more than one row. ERROR 215 (22015): Single-row sub-query returns more than one row. ERROR 215 (22015): ERROR 215 (22015): Single-row sub-query returns more than one row. ERROR 215 (22015): Single-row sub-query returns more than one row.' . Originally TeiidProcessingException SQLExceptionCode.java:388.: org.teiid.core.TeiidProcessingException: TEIID30504 Source: 22015 Unexpected exception while translating results: ERROR 215 (22015): Single-row sub-query returns more than one row. ERROR 215 (22015): Single-row sub-query returns more than one row. ERROR 215 (22015): ERROR 215 (22015): Single-row sub-query returns more than one row. ERROR 215 (22015): Single-row sub-query returns more than one row. at org.teiid.dqp.internal.process.DataTierTupleSource.exceptionOccurred(DataTierTupleSource.java:381) [teiid-engine-8.7.1.6_2-redhat-6.jar:8.7.1.6_2-redhat-6] at org.teiid.dqp.internal.process.DataTierTupleSource.nextTuple(DataTierTupleSource.java:154) [teiid-engine-8.7.1.6_2-redhat-6.jar:8.7.1.6_2-redhat-6] at org.teiid.query.processor.relational.AccessNode.nextBatchDirect(AccessNode.java:369) [teiid-engine-8.7.1.6_2-redhat-6.jar:8.7.1.6_2-redhat-6] at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:278) [teiid-engine-8.7.1.6_2-redhat-6.jar:8.7.1.6_2-redhat-6] at org.teiid.query.processor.relational.RelationalPlan.nextBatch(RelationalPlan.java:136) [teiid-engine-8.7.1.6_2-redhat-6.jar:8.7.1.6_2-redhat-6] at org.teiid.query.processor.QueryProcessor.nextBatchDirect(QueryProcessor.java:151) [teiid-engine-8.7.1.6_2-redhat-6.jar:8.7.1.6_2-redhat-6] at org.teiid.query.processor.QueryProcessor.nextBatch(QueryProcessor.java:114) [teiid-engine-8.7.1.6_2-redhat-6.jar:8.7.1.6_2-redhat-6] at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:159) [teiid-engine-8.7.1.6_2-redhat-6.jar:8.7.1.6_2-redhat-6] at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:141) [teiid-engine-8.7.1.6_2-redhat-6.jar:8.7.1.6_2-redhat-6] at org.teiid.dqp.internal.process.RequestWorkItem.processMore(RequestWorkItem.java:444) [teiid-engine-8.7.1.6_2-redhat-6.jar:8.7.1.6_2-redhat-6] at org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:326) [teiid-engine-8.7.1.6_2-redhat-6.jar:8.7.1.6_2-redhat-6] at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:51) [teiid-engine-8.7.1.6_2-redhat-6.jar:8.7.1.6_2-redhat-6] at org.teiid.dqp.internal.process.RequestWorkItem.run(RequestWorkItem.java:254) [teiid-engine-8.7.1.6_2-redhat-6.jar:8.7.1.6_2-redhat-6] at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:274) [teiid-engine-8.7.1.6_2-redhat-6.jar:8.7.1.6_2-redhat-6] at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119) [teiid-engine-8.7.1.6_2-redhat-6.jar:8.7.1.6_2-redhat-6] at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:210) [teiid-engine-8.7.1.6_2-redhat-6.jar:8.7.1.6_2-redhat-6] at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) [rt.jar:1.8.0_40] at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) [rt.jar:1.8.0_40] at java.lang. Thread .run( Thread .java:745) [rt.jar:1.8.0_40] Caused by: org.teiid.translator.TranslatorException: 22015 Unexpected exception while translating results: ERROR 215 (22015): Single-row sub-query returns more than one row. ERROR 215 (22015): Single-row sub-query returns more than one row. ERROR 215 (22015): ERROR 215 (22015): Single-row sub-query returns more than one row. ERROR 215 (22015): Single-row sub-query returns more than one row. at org.teiid.translator.jdbc.JDBCQueryExecution.next(JDBCQueryExecution.java:351) at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.handleBatch(ConnectorWorkItem.java:388) [teiid-engine-8.7.1.6_2-redhat-6.jar:8.7.1.6_2-redhat-6] at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.more(ConnectorWorkItem.java:206) [teiid-engine-8.7.1.6_2-redhat-6.jar:8.7.1.6_2-redhat-6] at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:301) [teiid-engine-8.7.1.6_2-redhat-6.jar:8.7.1.6_2-redhat-6] at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:110) [teiid-engine-8.7.1.6_2-redhat-6.jar:8.7.1.6_2-redhat-6] at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:107) [teiid-engine-8.7.1.6_2-redhat-6.jar:8.7.1.6_2-redhat-6] at java.util.concurrent.FutureTask.run(FutureTask.java:266) [rt.jar:1.8.0_40] at org.teiid.dqp.internal.process.FutureWork.run(FutureWork.java:58) [teiid-engine-8.7.1.6_2-redhat-6.jar:8.7.1.6_2-redhat-6] ... 6 more Caused by: java.sql.SQLException: ERROR 215 (22015): Single-row sub-query returns more than one row. ERROR 215 (22015): Single-row sub-query returns more than one row. ERROR 215 (22015): ERROR 215 (22015): Single-row sub-query returns more than one row. ERROR 215 (22015): Single-row sub-query returns more than one row. at org.apache.phoenix.exception.SQLExceptionCode$Factory$1.newException(SQLExceptionCode.java:388) at org.apache.phoenix.exception.SQLExceptionInfo.buildException(SQLExceptionInfo.java:145) at org.apache.phoenix.util.ServerUtil.parseRemoteException(ServerUtil.java:131) at org.apache.phoenix.util.ServerUtil.parseServerExceptionOrNull(ServerUtil.java:115) at org.apache.phoenix.util.ServerUtil.parseServerException(ServerUtil.java:104) at org.apache.phoenix.iterate.BaseResultIterators.getIterators(BaseResultIterators.java:553) at org.apache.phoenix.iterate.ConcatResultIterator.getIterators(ConcatResultIterator.java:50) at org.apache.phoenix.iterate.ConcatResultIterator.currentIterator(ConcatResultIterator.java:97) at org.apache.phoenix.iterate.ConcatResultIterator.next(ConcatResultIterator.java:117) at org.apache.phoenix.iterate.DelegateResultIterator.next(DelegateResultIterator.java:44) at org.apache.phoenix.jdbc.PhoenixResultSet.next(PhoenixResultSet.java:773) at org.jboss.jca.adapters.jdbc.WrappedResultSet.next(WrappedResultSet.java:1862) at org.teiid.translator.jdbc.JDBCQueryExecution.next(JDBCQueryExecution.java:338) ... 13 more

    Description

      Query:
      SELECT intkey FROM BQT1.SmallA AS A WHERE stringkey = (SELECT stringkey FROM BQT1.SmallA AS B WHERE b.intnum = a.intnum) ORDER BY intkey
      Source-specific query:
      SELECT g_0.intkey AS c_0 FROM smalla AS g_0 WHERE g_0.stringkey = (SELECT g_1.stringkey FROM smalla AS g_1 WHERE g_1.intnum = g_0.intnum) ORDER BY c_0

      Subquery returns more than one row. Every value in IntNum is unique except 3 NULL values.
      Changing where clause to: WHERE b.intnum is not null and b.intnum = a.intnum returns correct result.

      It seems like Phoenix bug. A note in the documentation could be sufficient.

      Attachments

        Issue Links

          is incorporated by

          Bug - A problem that impairs or prevents the functions of the product. TEIID-3779 HBase query issues

          • Major - To be worked after higher priority work items are addressed. When the severity is high and the effort to change it is low to moderate, this term is chosen. Issues in this category likely have an existing workaround but implementation or execution may be non-trivial.
          • Closed

          Activity

            People

              rhn-engineering-shawkins Steven Hawkins
              jdurani Juraj Duráni (Inactive)
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved: