SAP Knowledge Base Article - Preview

2351056 - Receive wrong results when doing a select into TOP NNN in Parallel Mode - SAP ASE

Symptom

On a simple select into the showplan for the failed result set shows: 
1> select top 1000 acid into #p1d from pt_trmast where tmcode in ('B','S') and acid > 100000
2> go

QUERY PLAN FOR STATEMENT 1 (at line 1).
Optimized using Parallel Mode
Executed in parallel by coordinating process and 8 worker processes.


STEP 1
The type of query is CREATE TABLE.

STEP 2
The type of query is INSERT.

7 operator(s) under root

|ROOT:EMIT Operator (VA = 7)
|
| |TOP Operator (VA = 6)
| | Top Limit: 1000
| |
| | |EXCHANGE Operator (VA = 5) (Merged)
| | |Executed in parallel by 8 Producer and 1 Consumer processes.

| | |
| | | |EXCHANGE:EMIT Operator (VA = 4)
| | | |
| | | | |INSERT Operator (VA = 3)
| | | | | The update mode is direct.
| | | | |
| | | | | |TOP Operator (VA = 2)
| | | | | | Top Limit: 1000
| | | | | |
| | | | | | |RESTRICT Operator (VA = 1)(0)(0)(0)(7)(0)
| | | | | | |
| | | | | | | |SCAN Operator (VA = 0)
| | | | | | | | FROM TABLE
| | | | | | | | pt_trmast
| | | | | | | | Table Scan.
| | | | | | | | Forward Scan.
| | | | | | | | Positioning at start of table.
| | | | | | | | Executed in parallel with a 8-way partition scan.
| | | | | | | | Using I/O Size 16 Kbytes for data pages.
| | | | | | | | With LRU Buffer Replacement Strategy for data pages.
| | | | |
| | | | | TO TABLE
| | | | | #p1d
| | | | | Using I/O Size 16 Kbytes for data pages.


Total estimated I/O cost for statement 1 (at line 1): 2147483647.

(0 rows affected)

In-house Repro:
1> select top 1000 acid into #t from pt_trmast (index 0) where acid > 100000 and acid !=500000
2> go

QUERY PLAN FOR STATEMENT 1 (at line 1).
Optimized using Parallel Mode
Optimized using the forced options (internally generated Abstract Plan).
Executed in parallel by coordinating process and 8 worker processes.


    STEP 1
        The type of query is CREATE TABLE.

    STEP 2
        The type of query is INSERT.

        6 operator(s) under root

       |ROOT:EMIT Operator (VA = 6)
       |
       |   |TOP Operator (VA = 5)
       |   |  Top Limit: 1000
       |   |
       |   |   |EXCHANGE Operator (VA = 4) (Merged)
       |   |   |Executed in parallel by 8 Producer and 1 Consumer processes.

       |   |   |
       |   |   |   |EXCHANGE:EMIT Operator (VA = 3)
       |   |   |   |
       |   |   |   |   |INSERT Operator (VA = 2)
       |   |   |   |   |  The update mode is direct.
       |   |   |   |   |
       |   |   |   |   |   |TOP Operator (VA = 1)
       |   |   |   |   |   |  Top Limit: 1000
       |   |   |   |   |   |
       |   |   |   |   |   |   |SCAN Operator (VA = 0)
       |   |   |   |   |   |   |  FROM TABLE
       |   |   |   |   |   |   |  pt_trmast
       |   |   |   |   |   |   |  Table Scan.
       |   |   |   |   |   |   |  Forward Scan.
       |   |   |   |   |   |   |  Positioning at start of table.
       |   |   |   |   |   |   | Executed in parallel with a 8-way partition
scan.
       |   |   |   |   |   |   |  Using I/O Size 32 Kbytes for data pages.
       |   |   |   |   |   |   |  With LRU Buffer Replacement Strategy for data
pages.
       |   |   |   |   |
       |   |   |   |   |  TO TABLE
       |   |   |   |   |  #t
       |   |   |   |   |  Using I/O Size 32 Kbytes for data pages.


(0 rows affected)   <<< no rows

 

However, the same query WITHOUT the select into:

QUERY PLAN FOR STATEMENT 1 (at line 1).


    STEP 1
        The type of query is SET OPTION ON.

1> select top 1000 acid from pt_trmast (index 0) where acid > 100000 and acid !=500000
2> go

QUERY PLAN FOR STATEMENT 1 (at line 1).


    STEP 1
        The type of query is EXECUTE.
        Executing a newly cached statement (SSQL_ID = 2117487096).


QUERY PLAN FOR STATEMENT 1 (at line 0).


    STEP 1
        The type of query is DECLARE.


QUERY PLAN FOR STATEMENT 2 (at line 1).
Optimized using Parallel Mode
Optimized using the forced options (internally generated Abstract Plan).
Executed in parallel by coordinating process and 8 worker processes.


    STEP 1
        The type of query is SELECT.

        4 operator(s) under root

       |ROOT:EMIT Operator (VA = 4)
       |
       |   |TOP Operator (VA = 3)
       |   |  Top Limit: 1000
       |   |
       |   |   |EXCHANGE Operator (VA = 2) (Merged)
       |   |   |Executed in parallel by 8 Producer and 1 Consumer processes.

       |   |   |
       |   |   |   |EXCHANGE:EMIT Operator (VA = 1)
       |   |   |   |
       |   |   |   |   |SCAN Operator (VA = 0)
       |   |   |   |   |  FROM TABLE
       |   |   |   |   |  pt_trmast
       |   |   |   |   |  Table Scan.
       |   |   |   |   |  Forward Scan.
       |   |   |   |   |  Positioning at start of table.
       |   |   |   |   | Executed in parallel with a 8-way partition scan.
       |   |   |   |   |  Using I/O Size 32 Kbytes for data pages.
       |   |   |   |   |  With LRU Buffer Replacement Strategy for data pages.


acid
-----------

(1000 rows affected)  <<<< All expected rows returned.


Read more...

Environment

  • SAP Adaptive Server Enterprise 15.7
  • SAP Adaptive Server Enterprise 16.0

 

Product

SAP Adaptive Server Enterprise 15.7 ; SAP Adaptive Server Enterprise 16.0

Keywords

KBA , BC-SYB-ASE , Sybase ASE Database Platform (non Business Suite) , Bug Filed

About this page

This is a preview of a SAP Knowledge Base Article. Click more to access the full version on SAP ONE Support launchpad (Login required).

Search for additional results

Visit SAP Support Portal's SAP Notes and KBA Search.