We have a query running in v9.5 returning this error:
SQL0443N Routine "DB2EXT.TEXTSEARCH_8K64" (specific name
"CTE21") has
returned an error SQLSTATE with diagnostic text "CTE0157 Syntax
error
near "" ,
"KOLD"". ". SQLSTATE=38757
It does not cause error in v8.2.2.
This is the query:
WITH Q0 AS ( SELECT MPST_SEARCH_DATA.MPS_RECORD_ID FROM
MPST_SEARCH_DATA INNER JOIN MPST_rs_contents ON
MPST_SEARCH_DATA.
MPS_RECORD_ID = MPST_rs_contents.MPS_RECORD_ID WHERE
SET_ID=47952 ) ,
Q1 AS ( SELECT MPST_SEARCH_DATA.MPS_RECORD_ID FROM
MPST_SEARCH_DATA
WHERE CONTAINS ( MPST_SEARCH_DATA.SEARCH_TEXT_S, 'SECTION
("video_source") ("KDBC" , "KDEB" , "KDLH" , "KDRV" , "KENW" ,
"KEPR" ,
"KERO" , "KESQ" , "KET" , "KETV" , "KEVN" , "KEYT" , "KEZI" ,
"KFAA" ,
"KFBB" , "KFBT" , "KFDA" , "KFDX" , "KFMB" , "KFOX" , "KFSN" ,
"KFTA" ,
"KFVS" , "KFXO" , "KGAN" , "KGBT" , "KGMB" , "KGNS" , "KGO" ,
"KGTV" ,
"KGUN" , "KGWN" , "KHBS" , "KHGI" , "KHNL" , "KHON" , "KHQ" ,
"KHQA" ,
"KHSL" , "KICU" , "KIDK" , "KIEM" , "KIFI" , "KIII" , "KIMA" ,
"KIMT" ,
"KINC" , "KINT" , "KION" , "KIRO" , "KITV" , "KIVI" , "KJCT" ,
"KJRH" ,
"KJTL" , "KJTV" , "KKCO" , "KKTU" , "KKTV" , "KLAS" , "KLBK" ,
"KLDO" ,
"KLEW" , "KLFY" , "KLJB" , "KLSR" , "KLST" , "KLTV" , "KLUZ" ,
"KMAX" ,
"KMBC" , "KMEG" , "KMGH" , "KMID" , "KMIR" , "KMIZ" , "KMOL" ,
"KMPH" ,
"KMTV" , "KMTX" , "KMTZ" , "KMVT" , "KNOE" , "KNTV" , "KNVN" ,
"KNVO" ,
"KNVV" , "KNWA" , "KNXV" , "KOAT" , "KOBI" , "KOCO" , "KODE" ,
"KOFY" ,
"KOHD" , "KOKH" , "KOKI" , "KOLD" , "KOLO" , "KOLR" , "KOMO" ,
"KOMU" ,
"KORO" , "KOSA" , "KOTV" , "KOVR" , "KPAX" , "KPHO" , "KPLC" ,
"KPOM" ,
"KPRC" , "KPSP" , "KPTM" , "KPTV" , "KPVI" , "KPVM" , "KQCA" ,
"KQTV" ,
"KRBC" , "KRCR" , "KRDO" , "KRGV" , "KRNV" , "KRON" , "KRQE" ,
"KRTV" ,
"KSAN" , "KSAT" , "KSBI" , "KSBW" , "KSCI" , "KSEE" , "KSFX" ,
"KSHB" ,
"KSL" , "KSLA" , "KSMO" , "KSMS" , "KSNF" , "KSNT" , "KSNW" ,
"KSPR" ,
"KSWO" , "KSWT" , "KTAB" , "KTAL" , "KTEN" , "KTKA" , "KTIV" ,
"KTNV" ,
"KTRE" , "KTRK" , "KTSF" , "KTTC" , "KTUL" , "KTUZ" , "KTVE" ,
"LTVG" ,
"KTVH" , "KTVL" , "KTVN" , "KTVO" , "KTVQ" , "KTVU" , "KTVZ" ,
"KTWO" ,
"KTXA" , "KTXS" , "KULR" , "KUPT" , "KUSI" , "KUTV" , "KUWB" ,
"KVAL" ,
"KVBC" , "KVER" , "KVEW" , "KVIA" , "KVLY" , "KVOA" , "KVRR" ,
"KVVU" ,
"KVWB" , "KWAB" , "KWBA" , "KWBQ" , "KWCH" , "KWES" , "KWKB" ,
"KWQC" ,
"KWSD" , "KWTV" , "KWWL" , "KXAN" , "KXII" , "KXJB" , "KXLF" ,
"KXLY" ,
"KXMB" , "KXRM" , "KXTQ" , "KXXV" , "KYMA" , "KYTX" , "KZSW" ,
"KZTV" ,
"WAAY")') = 1), Q2 AS ( SELECT Q1.MPS_RECORD_ID FROM Q1 INNER
JOIN Q0
ON Q1.MPS_RECORD_ID=Q0.MPS_RECORD_ID) , QDISTINCT AS ( SELECT
DISTINCT
MPS_RECORD_ID FROM Q2 ), QFINAL AS ( SELECT MPST_SEARCH_DATA.
MPS_RECORD_ID FROM MPST_SEARCH_DATA , QDISTINCT WHERE
MPST_SEARCH_DATA.
MPS_RECORD_ID = QDISTINCT.MPS_RECORD_ID AND MPST_SEARCH_DATA.
COLLECTION_ID IN (1)) SELECT COUNT(*) FROM QFINAL
If we reduce the the number of values in the CONTAINS clause the
query
will run. |
****************************************************************
* USERS AFFECTED: *
****************************************************************
* PROBLEM DESCRIPTION: *
* Search queries with too many search words do not work and *
* throw CTE0157 SYNTAX ERROR *
* Example query is given below : *
* *
* WITH Q0 AS ( SELECT MPST_SEARCH_DATA.MPS_RECORD_ID FROM *
* *
* MPST_SEARCH_DATA INNER JOIN MPST_rs_contents ON *
* MPST_SEARCH_DATA. *
* MPS_RECORD_ID = MPST_rs_contents.MPS_RECORD_ID WHERE *
* SET_ID=47952 ) , *
* Q1 AS ( SELECT MPST_SEARCH_DATA.MPS_RECORD_ID FROM *
* MPST_SEARCH_DATA *
* WHERE CONTAINS ( MPST_SEARCH_DATA.SEARCH_TEXT_S, 'SECTION *
* *
* ("video_source") ("KDBC" , "KDEB" , "KDLH" , "KDRV" , "KENW" *
* , "KEPR" , *
* "KERO" , "KESQ" , "KET" , "KETV" , "KEVN" , "KEYT" , "KEZI" *
* , "KFAA" , *
* "KFBB" , "KFBT" , "KFDA" , "KFDX" , "KFMB" , "KFOX" , "KFSN" *
* , "KFTA" , *
* "KFVS" , "KFXO" , "KGAN" , "KGBT" , "KGMB" , "KGNS" , "KGO" *
* , "KGTV" , *
* "KGUN" , "KGWN" , "KHBS" , "KHGI" , "KHNL" , "KHON" , "KHQ" *
* , "KHQA" , *
* "KHSL" , "KICU" , "KIDK" , "KIEM" , "KIFI" , "KIII" , "KIMA" *
* , "KIMT" , *
* "KINC" , "KINT" , "KION" , "KIRO" , "KITV" , "KIVI" , "KJCT" *
* , "KJRH" , *
* "KJTL" , "KJTV" , "KKCO" , "KKTU" , "KKTV" , "KLAS" , "KLBK" *
* , "KLDO" , *
* "KLEW" , "KLFY" , "KLJB" , "KLSR" , "KLST" , "KLTV" , "KLUZ" *
* , "KMAX" , *
* "KMBC" , "KMEG" , "KMGH" , "KMID" , "KMIR" , "KMIZ" , "KMOL" *
* , "KMPH" , *
* "KMTV" , "KMTX" , "KMTZ" , "KMVT" , "KNOE" , "KNTV" , "KNVN" *
* , "KNVO" , *
* "KNVV" , "KNWA" , "KNXV" , "KOAT" , "KOBI" , "KOCO" , "KODE" *
* , "KOFY" , *
* "KOHD" , "KOKH" , "KOKI" , "KOLD" , "KOLO" , "KOLR" , "KOMO" *
* , "KOMU" , *
* "KORO" , "KOSA" , "KOTV" , "KOVR" , "KPAX" , "KPHO" , "KPLC" *
* , "KPOM" , *
* "KPRC" , "KPSP" , "KPTM" , "KPTV" , "KPVI" , "KPVM" , "KQCA" *
* , "KQTV" , *
* "KRBC" , "KRCR" , "KRDO" , "KRGV" , "KRNV" , "KRON" , "KRQE" *
* , "KRTV" , *
* "KSAN" , "KSAT" , "KSBI" , "KSBW" , "KSCI" , "KSEE" , "KSFX" *
* , "KSHB" , *
* "KSL" , "KSLA" , "KSMO" , "KSMS" , "KSNF" , "KSNT" , "KSNW" *
* , "KSPR" , *
* "KSWO" , "KSWT" , "KTAB" , "KTAL" , "KTEN" , "KTKA" , "KTIV" *
* , "KTNV" , *
* "KTRE" , "KTRK" , "KTSF" , "KTTC" , "KTUL" , "KTUZ" , "KTVE" *
* , "LTVG" , *
* "KTVH" , "KTVL" , "KTVN" , "KTVO" , "KTVQ" , "KTVU" , "KTVZ" *
* , "KTWO" , *
* "KTXA" , "KTXS" , "KULR" , "KUPT" , "KUSI" , "KUTV" , "KUWB" *
* , "KVAL" , *
* "KVBC" , "KVER" , "KVEW" , "KVIA" , "KVLY" , "KVOA" , "KVRR" *
* , "KVVU" , *
* "KVWB" , "KWAB" , "KWBA" , "KWBQ" , "KWCH" , "KWES" , "KWKB" *
* , "KWQC" , *
* "KWSD" , "KWTV" , "KWWL" , "KXAN" , "KXII" , "KXJB" , "KXLF" *
* , "KXLY" , *
* "KXMB" , "KXRM" , "KXTQ" , "KXXV" , "KYMA" , "KYTX" , "KZSW" *
* , "KZTV" , *
* "WAAY")') = 1), Q2 AS ( SELECT Q1.MPS_RECORD_ID FROM Q1 *
* INNER JOIN Q0 *
* ON Q1.MPS_RECORD_ID=Q0.MPS_RECORD_ID) , QDISTINCT AS ( *
* SELECT DISTINCT *
* MPS_RECORD_ID FROM Q2 ), QFINAL AS ( SELECT *
* MPST_SEARCH_DATA. *
* MPS_RECORD_ID FROM MPST_SEARCH_DATA , QDISTINCT WHERE *
* MPST_SEARCH_DATA. *
* MPS_RECORD_ID = QDISTINCT.MPS_RECORD_ID AND *
* MPST_SEARCH_DATA. *
* COLLECTION_ID IN (1)) SELECT COUNT(*) FROM QFINAL *
****************************************************************
* RECOMMENDATION: *
* Upgrade to V91fp10 *
**************************************************************** |
