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 for Me (Login required).

Search for additional results

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