Error at inner join; order of tables makes a difference

[Former Member]

When using inner joins, the order in which joined tables are introduced should be irrelevant. However, I'm faced with a query which will be executed correctly, but when I change the table order, the query would yield totally wrong records.

Illustration:

I have a table user2files which assigns certain files to certain users. There is NO record in the table that meets the following criteria

user_id = 410784 and file_id = 349658

Thus,

select * from user2files

where user2files.file_id = 349658 and user2files.user_id = 410784

yields an empty result set. So far, so good - correct!

BUT ... when I just add another table to be joined, suddenly I get result records:

// WRONG!! returns one record!

select * from files, user2files

where files.file_id = user2files.file_id

and user2files.file_id = 349658 and user2files.user_id = 410784

Strange - the additional table is linked via inner join, so if the table user2files does not return any records, the additional join to the table files should not change anything - there should be not a single record returned!

However, what it does return is "all rows of files matching the file_id 349658 and all columns of user2files, but empty, just as if I would have notated an outer join".

I tried other combinations. Some work, some are broken - apparently the order of mentioning the tables makes a difference (though it shouldn't). Here are all combinations I tried, the one mentioned above being top of the list:

// WRONG!! returns one record!

select * from files, user2files

where files.file_id = user2files.file_id

and user2files.file_id = 349658 and user2files.user_id = 410784

// works:

select * from user2files, files

where files.file_id = user2files.file_id

and user2files.file_id = 349658 and user2files.user_id = 410784

// works:

select * from user2files join files on user2files.file_id = files.file_id

where user2files.file_id = 349658 and user2files.user_id = 410784

// WRONG!! returns one record!

select * from files join user2files on user2files.file_id = files.file_id

where user2files.file_id = 349658 and user2files.user_id = 410784

// works:

select * from user2files join files on files.file_id = user2files.file_id

where user2files.file_id = 349658 and user2files.user_id = 410784

// WRONG!! returns one record!

select * from files join user2files on files.file_id = user2files.file_id

where user2files.file_id = 349658 and user2files.user_id = 410784

I suppose there has to be a problem with the query optimizer. Is this a known issue?

Version info:

MaxDB, Version 7.6.03.07, running on Windows Server 2003 family (WIN32)

Thank you,

Alex

[lbreddemann]

Hi Alexander,

please make sure to use the most current available patch.

For NON-SAP-customers this is currently MaxDB 7.6.03 Build 15.

After using the current patch, please provide the execution plans both for the statements where the correct data is returned as well as for the failing ones.

Also it would be a whole lot easier to reproduce the issue if we would know:

- the DDL for the tables/indexes

- the parametersetup of your DB

- some testdata that allow to reproduce the issue.

There are some known bugs related to Index-Only strategies and local predicates - we have to see what the DB is doing in your case to know which problem we have here.

KR Lars

[Former Member]

Ok, I'll try to make our admins install the latest patch and will then be happy to provide you with further information! (will take a few days, I'm afraid) ...

[Former Member]

For the initial posting, I abstracted the names of the tables with easy-to-understand table names, so you get the point what the semantics behind the statement would be. To provide you with all relevant information, I am now posting the original table names.

ABSTRACTED_TABLE_NAME -> REAL_TABLE_NAME

-

---

user2files -> anwaelte2akten

files -> v_aktenzeichen (which is a view, DLL s. below)

======================================

statements and execution plans

======================================

// this proves that there is no record in the table anwaelte2akten satifsfying the criteria:

select *

from anwaelte2akten

where anwaelte2akten.akten_id = 349658

and anwaelte2akten.personen_id = 410784

// this executes correctly, it returns no row:

select *

from anwaelte2akten, v_aktenzeichen

where anwaelte2akten.akten_id = 349658

and anwaelte2akten.personen_id = 410784

// the execution plan for the above (correct) statement:

-

---

BEGIN EXECUTION PLAN (correct) -

---

AKZ TABLE SCAN 1

B_ANM JOIN VIA RANGE OF MULTIPLE KEY COLUMNS 1

TABLE HASHED

AKTEN_ID (USED COLUMN)

AKTENZEICHENART (USED COLUMN)

B_OFF JOIN VIA RANGE OF MULTIPLE KEY COLUMNS 1

TABLE TEMPORARY SORTED

AKTEN_ID (USED COLUMN)

AKTENZEICHENART (USED COLUMN)

B_SRN JOIN VIA RANGE OF MULTIPLE KEY COLUMNS 1

TABLE TEMPORARY SORTED

AKTEN_ID (USED COLUMN)

AKTENZEICHENART (USED COLUMN)

APORTAL ANWAELTE2AKTEN TABLE SCAN 1

INTERNAL TEMPORARY RESULT TABLE SCAN 1

NO TEMPORARY RESULTS CREATED

RESULT IS COPIED , COSTVALUE IS 33

QUERYREWRITE - APPLIED RULES:

DistinctPushDownTo 1

-

---

END EXECUTION PLAN (correct) -

---

// this returns 18 rows (which is wrong!):

select *

from v_aktenzeichen, anwaelte2akten

where anwaelte2akten.akten_id = 349658

and anwaelte2akten.personen_id = 410784

// the execution plan for the above (wrong) statement:

-

---

BEGIN EXECUTION PLAN (wrong) -

---

AKZ TABLE SCAN 1

B_ANM JOIN VIA RANGE OF MULTIPLE KEY COLUMNS 1

TABLE HASHED

AKTEN_ID (USED COLUMN)

AKTENZEICHENART (USED COLUMN)

B_OFF JOIN VIA RANGE OF MULTIPLE KEY COLUMNS 1

TABLE TEMPORARY SORTED

AKTEN_ID (USED COLUMN)

AKTENZEICHENART (USED COLUMN)

B_SRN JOIN VIA RANGE OF MULTIPLE KEY COLUMNS 1

TABLE TEMPORARY SORTED

AKTEN_ID (USED COLUMN)

AKTENZEICHENART (USED COLUMN)

INTERNAL TEMPORARY RESULT TABLE SCAN 1

APORTAL ANWAELTE2AKTEN TABLE SCAN 1

NO TEMPORARY RESULTS CREATED

RESULT IS COPIED , COSTVALUE IS 238609297

QUERYREWRITE - APPLIED RULES:

DistinctPushDownTo 1

-

---

END EXECUTION PLAN (wrong) -

---

======================================

table/view definitions

======================================

CREATE TABLE "ANWAELTE2AKTEN"

(

"ID" Integer NOT NULL,

"AKTEN_ID" Integer NOT NULL,

"FAM_AKTEN_ID" Integer,

"PERSONEN_ID" Integer NOT NULL,

"AKTENTYP" Char (1) ASCII,

"ANWART" Char (5) ASCII,

"ANWAKTENZEICHEN" Varchar (100) ASCII,

"PONUMMER" Varchar (100) ASCII,

PRIMARY KEY ("ID")

)

//

CREATE TABLE "AKTENZEICHEN"

(

"AKZ_ID" Integer NOT NULL,

"INT_IAKZ" Char (16) ASCII,

"PONUMMER" Varchar (40) ASCII,

"TITEL" Varchar (512) UNICODE,

"VEROEFFENTLICHT" Integer NOT NULL DEFAULT 0,

"PATENT_COUNSEL_ID" Integer,

"SCHUTZRECHTSART" Varchar (3) ASCII,

PRIMARY KEY ("AKZ_ID")

)

//

CREATE TABLE "BIBLIOGRAPHIE"

(

"AKTEN_ID" Integer NOT NULL,

"AKTENZEICHEN" Varchar (20) ASCII,

"AKTENZEICHENART" Char (3) ASCII,

"ANMELDEDATUM" Date,

"VERFAHRENSHINWEIS" Char (2) ASCII,

"VERFAHRENSLAND" Char (2) ASCII,

"VERFAHRENSSPRACHE" Char (2) ASCII,

"OFFENLEGUNGSDATUM" Date,

"BEKANNTMACHUNGSDATUM" Date,

"ERTEILUNGSDATUM" Date,

"RECHTSKRAEFTIG_ERTEILT_AM" Date,

"PRUEFUNGSANTRAGSDATUM" Date,

"RECHERCHEANTRAGSDATUM" Date,

"EINGANG_RECHERCHEBERICHT" Date,

"EINGANG_ERTEILUNGSBESCHLUSS" Date,

"EINGESCHRAENKT_ERTEILT_AM" Date,

"ABGEWIESEN_AM" Date,

"ANMELDER" Varchar (180) ASCII,

"ANMELDERTYP" Char (3) ASCII

)

//

CREATE VIEW V_AKTENZEICHEN (AKTEN_ID, INT_IAKZ, ANMELDEAKZ, OFFENLEGUNGSNUMMER, ERTEILUNGSNUMMER) AS

SELECT DISTINCT AKZ.AKZ_ID, AKZ.INT_IAKZ, B_ANM.AKTENZEICHEN, B_OFF.AKTENZEICHEN, B_SRN.AKTENZEICHEN

FROM AKTENZEICHEN AKZ

LEFT JOIN BIBLIOGRAPHIE B_ANM ON B_ANM.AKTEN_ID=AKZ.AKZ_ID AND B_ANM.AKTENZEICHENART = 'ANM'

LEFT JOIN BIBLIOGRAPHIE B_OFF ON B_OFF.AKTEN_ID=AKZ.AKZ_ID AND B_OFF.AKTENZEICHENART = 'OFF'

LEFT JOIN BIBLIOGRAPHIE B_SRN ON B_SRN.AKTEN_ID=AKZ.AKZ_ID AND B_SRN.AKTENZEICHENART = 'SRN'

//

There are no indexes on these tables (not yet).

======================================

database parameters

======================================

KERNELVERSION string KERNEL 7.6.03 BUILD 015-123

-173-107

INSTANCE_TYPE string OLTP

MCOD string NO

USE_FIBERS string YES

SERVERDBFOR_SAP string YES

_UNICODE string YES

DEFAULT_CODE string ASCII

DATE_TIME_FORMAT string INTERNAL

CONTROLUSERID string DBM

CONTROLPASSWORD crypt

MAXLOGVOLUMES int 3

MAXDATAVOLUMES int 64

LOG_VOLUME_NAME_? string

LOG_VOLUME_NAME_002 string D:\maxdb\sdb\data\APORTAL\log\DI

SKL002

LOG_VOLUME_NAME_001 string D:\maxdb\sdb\data\APORTAL\log\DI

SKL001

LOG_VOLUME_TYPE_? string

LOG_VOLUME_TYPE_002 string F

LOG_VOLUME_TYPE_001 string F

LOG_VOLUME_SIZE_? int

LOG_VOLUME_SIZE_002 int 3200

LOG_VOLUME_SIZE_001 int 3200

M_LOG_VOLUME_NAME_? string

M_LOG_VOLUME_TYPE_? string

DATA_VOLUME_NAME_? string

DATA_VOLUME_NAME_0002 string D:\maxdb\sdb\data\APORTAL\data\D

ISKD0002

DATA_VOLUME_NAME_0001 string D:\maxdb\sdb\data\APORTAL\data\D

ISKD0001

DATA_VOLUME_TYPE_? string

DATA_VOLUME_TYPE_0002 string F

DATA_VOLUME_TYPE_0001 string F

DATA_VOLUME_SIZE_? int

DATA_VOLUME_SIZE_0002 int 131072

DATA_VOLUME_SIZE_0001 int 131072

DATA_VOLUME_MODE_? string NORMAL

DATA_VOLUME_MODE_0002 string NORMAL

DATA_VOLUME_MODE_0001 string NORMAL

DATA_VOLUME_GROUPS int 1

LOG_BACKUP_TO_PIPE string NO

MAXBACKUPDEVS int 2

LOG_MIRRORED string NO

MAXVOLUMES int 68

LOG_IO_BLOCK_COUNT int 4

DATA_IO_BLOCK_COUNT int 64

BACKUP_BLOCK_CNT int 64

DELAYLOGWRITER int 0

LOG_IO_QUEUE int 50

RESTARTTIME int 600

MAXUKPS int 1024

MAXCPU int 1

MAX_LOG_QUEUE_COUNT int 0

USED_MAX_LOG_QUEUE_COUNT int 1

LOG_QUEUE_COUNT int 1

MAXUSERTASKS int 20

TRANSRGNS int 8

TABRGNS int 8

OMSREGIONS int 0

OMSRGNS int 7

OMS_HEAP_LIMIT int 0

OMS_HEAP_COUNT int 1

OMS_HEAP_BLOCKSIZE int 10000

OMS_HEAP_THRESHOLD int 100

OMS_VERS_THRESHOLD int 2097152

HEAP_CHECK_LEVEL int 0

ROWRGNS int 8

RESERVEDSERVERTASKS int 69

MINSERVERTASKS int 134

MAXSERVERTASKS int 134

MAXGARBAGECOLL int 1

ESTIMMAXTRANS int 3

_MAXTRANS int 168

LOCKREGIONS int

MAXLOCKS int 2500

LOCKSUPPLY_BLOCK int 100

DEADLOCK_DETECTION int 4

SESSION_TIMEOUT int 900

OMS_STREAM_TIMEOUT int 30

REQUEST_TIMEOUT int 5000

USEASYNC_IO string YES

USE_BACKUP_SECURITY_DESCRIPTOR string YES

IOPROCSPER_DEV int 1

IOPROCSFOR_PRIO int 0

IOPROCSFOR_READER int 0

USEIOPROCS_ONLY string NO

IOPROCSSWITCH int 2

UKPS int 5

USERPER_UKT int

SERVERPER_UKT int

LRU_FOR_SCAN string NO

PAGESIZE int 8192

PACKETSIZE int 131072

MINREPLYSIZE int 4096

MAXMBDATASIZE int

MBLOCKDATA_SIZE int 32768

MBLOCKQUAL_SIZE int 32768

MBLOCKSTACK_SIZE int 32768

MBLOCKSTRAT_SIZE int 16384

WORKSTACKSIZE int 8192

WORKDATASIZE int 8192

MINCATCACHESIZE int

CATCACHE_MINSIZE int 262144

CAT_CACHE_SUPPLY int 1344

INIT_ALLOCATORSIZE int 262144

ALLOW_MULTIPLE_SERVERTASK_UKTS string NO

TASKCLUSTER01 string tw;al;ut;2000sv,100bup;10*ev,1

0*gc;

TASKCLUSTER02 string ti,100dw;30000us;

TASKCLUSTER03 string compress

MPRGN_QUEUE string YES

MPRGN_DIRTY_READ string DEFAULT

MPRGN_BUSY_WAIT string DEFAULT

MPDISP_LOOPS int 1

MPDISP_PRIO string DEFAULT

MP_RGN_LOOP int -1

MPRGN_PRIO string DEFAULT

MAXRGN_REQUEST int -1

PRIOBASE_U2U int 100

PRIOBASE_IOC int 80

PRIOBASE_RAV int 80

PRIOBASE_REX int 40

PRIOBASE_COM int 10

PRIOFACTOR int 80

DELAYCOMMIT string NO

MAXTASKSTACK int 1024

MAX_SERVERTASK_STACK int 500

MAX_SPECIALTASK_STACK int 500

DWIO_AREA_SIZE int 50

DWIO_AREA_FLUSH int 50

FBM_VOLUME_COMPRESSION int 50

FBM_VOLUME_BALANCE int 10

FBMLOW_IO_RATE int 10

CACHE_SIZE int 2500

DWLRU_TAIL_FLUSH int 25

XP_DATA_CACHE_RGNS int 0

DATACACHE_RGNS int 8

XP_CONVERTER_REGIONS int 0

CONVERTER_REGIONS int 8

XP_MAXPAGER int 0

MAXPAGER int 64

SEQUENCE_CACHE int 1

IDXFILELIST_SIZE int 0

VOLUMENO_BIT_COUNT int 8

OPTIM_MAX_MERGE int 500

OPTIM_INV_ONLY string YES

OPTIM_CACHE string NO

OPTIM_JOIN_FETCH int 0

JOIN_SEARCH_LEVEL int 0

JOIN_MAXTAB_LEVEL4 int 16

JOIN_MAXTAB_LEVEL9 int 5

READAHEADBLOBS int 32

CLUSTER_WRITE_THRESHOLD int 80

CLUSTERED_LOBS string NO

RUNDIRECTORY string D:\maxdb\sdb\data\wrk\APORTAL

_KERNELDIAGFILE string knldiag

KERNELDIAGSIZE int 800

_EVENTFILE string knldiag.evt

_EVENTSIZE int 0

_MAXEVENTTASKS int 2

_MAXEVENTS int 100

_KERNELTRACEFILE string knltrace

TRACE_PAGES_TI int 2

TRACE_PAGES_GC int 20

TRACE_PAGES_LW int 5

TRACE_PAGES_PG int 3

TRACE_PAGES_US int 10

TRACE_PAGES_UT int 5

TRACE_PAGES_SV int 5

TRACE_PAGES_EV int 2

TRACE_PAGES_BUP int 0

_MAXBACKUPTASKS int

KERNELTRACESIZE int 1099

EXTERNAL_DUMP_REQUEST string NO

AKDUMP_ALLOWED string YES

_KERNELDUMPFILE string knldump

_RTEDUMPFILE string rtedump

UTILITYPROTFILE string dbm.utl

UTILITY_PROTSIZE int 100

BACKUPHISTFILE string dbm.knl

BACKUPMED_DEF string dbm.mdf

MAXMESSAGE_FILES int 0

__PARAM_CHANGED___ int 0

__PARAM_VERIFIED__ string 2008-06-16 19:04:39

DIAG_HISTORY_NUM int 2

DIAG_HISTORY_PATH string D:\maxdb\sdb\data\wrk\APORTAL\DI

AGHISTORY

DIAGSEM int 1

SHOW_MAX_STACK_USE string NO

SHOW_MAX_KB_STACK_USE string NO

LOG_SEGMENT_SIZE int 1066

_COMMENT string

MINI_DUMP string NORMAL

FORMATTING_MODE string PARALLEL

FORMAT_DATAVOLUME string YES

OFFICIAL_NODE string

UKT_CPU_RELATIONSHIP string NONE

HIRES_TIMER_TYPE string CPU

LOAD_BALANCING_CHK int 0

LOAD_BALANCING_DIF int 10

LOAD_BALANCING_EQ int 5

HS_STORAGE_DLL string libhsscopy

HS_NODE_? string

HS_DELAY_TIME_? int

HS_SYNC_INTERVAL int 50

USE_OPEN_DIRECT string NO

USE_OPEN_DIRECT_FOR_BACKUP string NO

SYMBOL_DEMANGLING string NO

EXPAND_COM_TRACE string NO

JOIN_TABLEBUFFER int 128

SET_VOLUME_LOCK string YES

SHAREDSQL string YES

SHAREDSQL_CLEANUPTHRESHOLD int 25

SHAREDSQL_COMMANDCACHESIZE int 262144

MEMORY_ALLOCATION_LIMIT int 0

USE_SYSTEM_PAGE_CACHE string YES

USE_COROUTINES string YES

FORBID_LOAD_BALANCING string NO

MIN_RETENTION_TIME int 60

MAX_RETENTION_TIME int 480

MAX_SINGLE_HASHTABLE_SIZE int 512

MAX_HASHTABLE_MEMORY int 5120

ENABLE_CHECK_INSTANCE string YES

RTE_TEST_REGIONS int 0

HASHED_RESULTSET string YES

HASHED_RESULTSET_CACHESIZE int 262144

CHECK_HASHED_RESULTSET int 0

AUTO_RECREATE_BAD_INDEXES string NO

AUTHENTICATION_ALLOW string

AUTHENTICATION_DENY string

TRACE_AK string NO

TRACE_DEFAULT string NO

TRACE_DELETE string NO

TRACE_INDEX string NO

TRACE_INSERT string NO

TRACE_LOCK string NO

TRACE_LONG string NO

TRACE_OBJECT string NO

TRACE_OBJECT_ADD string NO

TRACE_OBJECT_ALTER string NO

TRACE_OBJECT_FREE string NO

TRACE_OBJECT_GET string NO

TRACE_OPTIMIZE string NO

TRACE_ORDER string NO

TRACE_ORDER_STANDARD string NO

TRACE_PAGES string NO

TRACE_PRIMARY_TREE string NO

TRACE_SELECT string NO

TRACE_TIME string NO

TRACE_UPDATE string NO

TRACE_STOP_ERRORCODE int 0

TRACE_ALLOCATOR int 0

TRACE_CATALOG int 0

TRACE_CLIENTKERNELCOM int 0

TRACE_COMMON int 0

TRACE_COMMUNICATION int 0

TRACE_CONVERTER int 0

TRACE_DATACHAIN int 0

TRACE_DATACACHE int 0

TRACE_DATAPAM int 0

TRACE_DATATREE int 0

TRACE_DATAINDEX int 0

TRACE_DBPROC int 0

TRACE_FBM int 0

TRACE_FILEDIR int 0

TRACE_FRAMECTRL int 0

TRACE_IOMAN int 0

TRACE_IPC int 0

TRACE_JOIN int 0

TRACE_KSQL int 0

TRACE_LOGACTION int 0

TRACE_LOGHISTORY int 0

TRACE_LOGPAGE int 0

TRACE_LOGTRANS int 0

TRACE_LOGVOLUME int 0

TRACE_MEMORY int 0

TRACE_MESSAGES int 0

TRACE_OBJECTCONTAINER int 0

TRACE_OMS_CONTAINERDIR int 0

TRACE_OMS_CONTEXT int 0

TRACE_OMS_ERROR int 0

TRACE_OMS_FLUSHCACHE int 0

TRACE_OMS_INTERFACE int 0

TRACE_OMS_KEY int 0

TRACE_OMS_KEYRANGE int 0

TRACE_OMS_LOCK int 0

TRACE_OMS_MEMORY int 0

TRACE_OMS_NEWOBJ int 0

TRACE_OMS_SESSION int 0

TRACE_OMS_STREAM int 0

TRACE_OMS_VAROBJECT int 0

TRACE_OMS_VERSION int 0

TRACE_PAGER int 0

TRACE_RUNTIME int 0

TRACE_SHAREDSQL int 0

TRACE_SQLMANAGER int 0

TRACE_SRVTASKS int 0

TRACE_SYNCHRONISATION int 0

TRACE_SYSVIEW int 0

TRACE_TABLE int 0

TRACE_VOLUME int 0

CHECK_BACKUP string NO

CHECK_DATACACHE string NO

CHECK_KB_REGIONS string NO

CHECK_LOCK string NO

CHECK_LOCK_SUPPLY string NO

CHECK_REGIONS string NO

CHECK_TASK_SPECIFIC_CATALOGCACHE string NO

CHECK_TRANSLIST string NO

CHECK_TREE string NO

CHECK_TREE_LOCKS string NO

CHECK_COMMON int 0

CHECK_CONVERTER int 0

CHECK_DATAPAGELOG int 0

CHECK_DATAINDEX int 0

CHECK_FBM int 0

CHECK_IOMAN int 0

CHECK_LOGHISTORY int 0

CHECK_LOGPAGE int 0

CHECK_LOGTRANS int 0

CHECK_LOGVOLUME int 0

CHECK_SRVTASKS int 0

OPTIMIZE_AGGREGATION string YES

OPTIMIZE_FETCH_REVERSE string YES

OPTIMIZE_STAR_JOIN string YES

OPTIMIZE_JOIN_ONEPHASE string YES

OPTIMIZE_JOIN_OUTER string YES

OPTIMIZE_MIN_MAX string YES

OPTIMIZE_FIRST_ROWS string YES

OPTIMIZE_OPERATOR_JOIN string YES

OPTIMIZE_JOIN_HASHTABLE string YES

OPTIMIZE_JOIN_HASH_MINIMAL_RATIO int 1

OPTIMIZE_OPERATOR_JOIN_COSTFUNC string YES

OPTIMIZE_JOIN_PARALLEL_MINSIZE int 1000000

OPTIMIZE_JOIN_PARALLEL_SERVERS int 0

OPTIMIZE_JOIN_OPERATOR_SORT string YES

OPTIMIZE_QUAL_ON_INDEX string YES

DDLTRIGGER string YES

SUBTREE_LOCKS string NO

MONITOR_READ int 2147483647

MONITOR_TIME int 2147483647

MONITOR_SELECTIVITY int 0

MONITOR_ROWNO int 0

CALLSTACKLEVEL int 0

OMS_RUN_IN_UDE_SERVER string NO

OPTIMIZE_QUERYREWRITE string OPERATOR

TRACE_QUERYREWRITE int 0

CHECK_QUERYREWRITE int 0

PROTECT_DATACACHE_MEMORY string NO

LOCAL_REDO_LOG_BUFFER_SIZE int 0

FILEDIR_SPINLOCKPOOL_SIZE int 10

TRANS_HISTORY_SIZE int 0

TRANS_THRESHOLD_VALUE int 60

ENABLE_SYSTEM_TRIGGERS string YES

DBFILLINGABOVELIMIT string 70L80M85M90H95H96H97H98H99H

DBFILLINGBELOWLIMIT string 70L80L85L90L95L

LOGABOVELIMIT string 50L75L90M95M96H97H98H99H

AUTOSAVE int 1

BACKUPRESULT int 1

CHECKDATA int 1

EVENT int 1

ADMIN int 1

ONLINE int 1

UPDSTATWANTED int 1

OUTOFSESSIONS int 3

ERROR int 3

SYSTEMERROR int 3

DATABASEFULL int 1

LOGFULL int 1

LOGSEGMENTFULL int 1

STANDBY int 1

======================================

generate test data

======================================

insert into aktenzeichen(akz_id, int_iakz, ponummer, titel, veroeffentlicht, patent_counsel_id) values (14274, '2000P10003 US01', '?', 'Title 7764', 1, '403891')

//

insert into aktenzeichen(akz_id, int_iakz, ponummer, titel, veroeffentlicht, patent_counsel_id) values (14955, '2003P10002WE', '?', 'Title 8441', 1, '400803')

//

insert into aktenzeichen(akz_id, int_iakz, ponummer, titel, veroeffentlicht, patent_counsel_id) values (34645, '2001P10002 US01', '?', 'Title 8035', 1, '403891')

//

insert into aktenzeichen(akz_id, int_iakz, ponummer, titel, veroeffentlicht, patent_counsel_id) values (40805, '2006P00019 DE', '?', 'Title 40636', 0, '403446')

//

insert into aktenzeichen(akz_id, int_iakz, ponummer, titel, veroeffentlicht, patent_counsel_id) values (40931, '2006P00025 DE', '?', 'Title 40650', 0, '400803')

//

insert into aktenzeichen(akz_id, int_iakz, ponummer, titel, veroeffentlicht, patent_counsel_id) values (174429, '2006P00019 US', '?', 'Title 40636', 0, '403446')

//

insert into aktenzeichen(akz_id, int_iakz, ponummer, titel, veroeffentlicht, patent_counsel_id) values (196498, '2006P00025 CN', '?', 'Title 40650', 0, '400803')

//

insert into aktenzeichen(akz_id, int_iakz, ponummer, titel, veroeffentlicht, patent_counsel_id) values (228770, '2006P00019WO', '?', 'Title 40636', 0, '403446')

//

insert into aktenzeichen(akz_id, int_iakz, ponummer, titel, veroeffentlicht, patent_counsel_id) values (349658, '2006P00025EP', '?', 'Title 40650', 1, '400803')

//

insert into aktenzeichen(akz_id, int_iakz, ponummer, titel, veroeffentlicht, patent_counsel_id) values (349732, '2007P00077 DE', '?', 'Title 349728', 0, '403259')

//

insert into aktenzeichen(akz_id, int_iakz, ponummer, titel, veroeffentlicht, patent_counsel_id) values (349874, '2008P00005 US', '?', 'Title 349873', 0, '405156')

//

insert into aktenzeichen(akz_id, int_iakz, ponummer, titel, veroeffentlicht, patent_counsel_id) values (350247, '200800015', '?', 'Title 349876', 0, '405156')

//

insert into aktenzeichen(akz_id, int_iakz, ponummer, titel, veroeffentlicht, patent_counsel_id) values (350248, '2008P00015 NL', '?', 'Title 349876', 0, '405156')

//

insert into aktenzeichen(akz_id, int_iakz, ponummer, titel, veroeffentlicht, patent_counsel_id) values (350290, '2008P00022 US', '?', 'test', 1, '405994')

//

insert into aktenzeichen(akz_id, int_iakz, ponummer, titel, veroeffentlicht, patent_counsel_id) values (350294, '2008P00022WOUS', '?', 'test 12', 0, '405994')

//

insert into aktenzeichen(akz_id, int_iakz, ponummer, titel, veroeffentlicht, patent_counsel_id) values (350326, '2008P00027 TW', '?', 'Title of ID', 0, '406357')

//

insert into aktenzeichen(akz_id, int_iakz, ponummer, titel, veroeffentlicht, patent_counsel_id) values (350485, '200800037', '?', 'Title 349626', 0, '405156')

//

insert into aktenzeichen(akz_id, int_iakz, ponummer, titel, veroeffentlicht, patent_counsel_id) values (350486, '2008P00037 US', '?', 'Workflow system...', 0, '405156')

//

insert into anwaelte2akten (id, akten_id, fam_akten_id, personen_id, aktentyp, anwart, anwaktenzeichen, ponummer) values (777725, 40931, 40650, 410784, 'A', 'OANW', 'ABC', '?')

//

insert into anwaelte2akten (id, akten_id, fam_akten_id, personen_id, aktentyp, anwart, anwaktenzeichen, ponummer) values (777726, 40931, 40650, 410784, 'A', 'OANW', 'ABC', '?')

//

insert into anwaelte2akten (id, akten_id, fam_akten_id, personen_id, aktentyp, anwart, anwaktenzeichen, ponummer) values (777736, 40931, 40650, 410784, 'A', 'ANW', 'ABC', '?')

//

insert into anwaelte2akten (id, akten_id, fam_akten_id, personen_id, aktentyp, anwart, anwaktenzeichen, ponummer) values (777738, 349658, 40650, 405082, 'A', 'ANW', '?', '?')

//

insert into anwaelte2akten (id, akten_id, fam_akten_id, personen_id, aktentyp, anwart, anwaktenzeichen, ponummer) values (777740, 350486, 350485, 410784, 'A', 'VANW', '?', '?')

//

insert into anwaelte2akten (id, akten_id, fam_akten_id, personen_id, aktentyp, anwart, anwaktenzeichen, ponummer) values (777749, 350485, 350485, 410784, 'A', 'VANW', '?', '?')

//

======================================

further information

======================================

Interesting: if the view V_AKTENZEICHEN is replaced by the underlying table AKTENZEICHEN, then the statement would behave in a correct fashion:

select distinct aktenzeichen.akz_id, aktenzeichen.int_iakz, anwaelte2akten.anwaktenzeichen

from aktenzeichen, anwaelte2akten

where anwaelte2akten.akten_id = 349658

and anwaelte2akten.personen_id = 410784

-> returns an empty set (=correct)

whereas (you remember ...) the following statement would produce results (=wrong):

select distinct v_aktenzeichen.akten_id, v_aktenzeichen.int_iakz, anwaelte2akten.anwaktenzeichen

from v_aktenzeichen, anwaelte2akten

where anwaelte2akten.akten_id = 349658

and anwaelte2akten.personen_id = 410784

So I suppose the LEFT JOINS contained in the view definition play a significant role regarding this problem.

I hope I have included all information you need for helping me ...

TIA

Alex

[lbreddemann]

Hi Alexander,

I was able to reproduce this on 7.6.03 Build 15 but not on 7.6.04 Build 11.

So, I'm pretty sure you're hitting bug

http://maxdb.sap.com/webpts?wptsdetail=yes&ErrorType=0&ErrorID=1152574

This is fixed with 7.6.03 Build 16 ...

There's not much that you can do about this - just rewrite the query to a join instead of using the view.

Best regards,

Lars

[holger_becker]

Hi Alexander,

as a workaround you could try to disable QueryRewrite feature by setting database parameter OPTIMIZE_QUERYREWRITE to NO.

Kind regards

Holger

[lbreddemann]

Hi Holger,

I already tried that (of course). It did not change anything for my testcase. The predicate pushdown was the only applied rule anyhow...

KR Lars