Thread: BUG #12204: Getting wrong results from full text search

BUG #12204: Getting wrong results from full text search

From
prasanna@semantifi.com
Date:
The following bug has been logged on the website:

Bug reference:      12204
Logged by:          prasanna kumar kuppa
Email address:      prasanna@semantifi.com
PostgreSQL version: 9.1.14
Operating system:   windows
Description:

Hi,

This may table structure:
CREATE TABLE semantified_content_key_word
(
  id bigint NOT NULL,
  semantified_content_id bigint,
  key_word_text text,
  content_date timestamp without time zone NOT NULL,
  context_id bigint NOT NULL,
  CONSTRAINT pk_sckw_id PRIMARY KEY (id )
)
WITH (
  OIDS=FALSE
);
ALTER TABLE semantified_content_key_word
  OWNER TO postgres;

-- Index: idx_sckw_cd

-- DROP INDEX idx_sckw_cd;

CREATE INDEX idx_sckw_cd
  ON semantified_content_key_word
  USING btree
  (content_date );

-- Index: idx_sckw_ctx_id

-- DROP INDEX idx_sckw_ctx_id;

CREATE INDEX idx_sckw_ctx_id
  ON semantified_content_key_word
  USING btree
  (context_id );

-- Index: idx_sckw_kwt

-- DROP INDEX idx_sckw_kwt;

CREATE INDEX idx_sckw_kwt
  ON semantified_content_key_word
  USING gin
  (to_tsvector('english'::regconfig, key_word_text) );

-- Index: idx_sckw_sc_id

-- DROP INDEX idx_sckw_sc_id;

CREATE INDEX idx_sckw_sc_id
  ON semantified_content_key_word
  USING btree
  (semantified_content_id );

Following is the data

INSERT INTO semantified_content_key_word (id, semantified_content_id,
key_word_text, content_date, context_id) VALUES (7347, 7347, ',
agreementnumber customer servicecreditdate the guarantor hereby absolutely
unconditionally irrevocably guarantees its successors assigns a separate
primary obligation due punctual performance observance favor all obligations
expressed be under this service agreement guarantee contained article is
waives any right require that action against taken exhausted prior being
pursuant avoidance doubt shall remain liable case non incomplete',
'2014-11-21 00:00:00', 111);
INSERT INTO semantified_content_key_word (id, semantified_content_id,
key_word_text, content_date, context_id) VALUES (7356, 7356, ', ;
agreementnumber agreementperiod aircraftmodel commencementdate customer
enginemodel enginequantity enginetype foddeductibleamount llpminimumbuild
servicecreditdate steppedpopularrate takeoffderate termdate turnaroundtime
highest engine shop visit 1 fulf!llment cfms sole discretion  subcontract
any designated repair station part all the performance obligation right
benefit service program conformance specification (as long such facility
subcontractor is properly certified rated aaa perform required services
provided that has reasonably review drs quality system audit report
sub-contractors related solely activity relation execution this agreement
customers engines) a case exceptional basis significant issue when consent
not be unreasonably withheld have reasonable investigations subcontractors
facllltles assurance under engines (including parts llps lrus qec) it deems
necessary ensure are being accordance terms reference amm applicable
regulations other laws regulatlons services) if identifies discrepancies
actually provlded shall notify writing within five (5) business days receipt
notice acknowledge propose appropriate corrective action tlmeframe thereof
each satisfaction (acting reasonably) order assist its provide
representative notified lrorn time time) access where an located purpose
conducting observing also make avallable use non-exclusive workspace
sufficiently equipped enable quallty audlt overhaul faclllties set forth
exhibit h maintaining approvals qualifications change orlglnally selected
particular (the "original drs") another list none obligations avoidance
doubt but without limitation transportation costs) will no greater than were
performed original (ii) does valid objection (it agreed parties which would
likely adverse effect tum around constitute objection) rendering behalf
remain legally responsible towards country aircraft registry grant
information concerning compliance easa requirements exercise airworthiness
responsibility data used follows: a)    the maintenance plan approved deviating
significantly (such manual workscope planning gulde associated publlcations
equivalents bulletins published technical am) b)    aaa directives; c)    the then
current 2 procedure visits a)    prior induction ls initiated manager
otherwise) inform whether proposed qualified view lnltlated confirm
satisfies criteria out article instruct programme accordingly determined
meet pursuant paragraph a) treated subject only g) below b)    subject remove
prepare shipment deliver c) wlll initial d)    further subsequent revisions
purchase extent accepts process specified event conditions take precedence
over e) records requested listed c f)    following delivery together documents
described paragraphs o) d) above cause proceed g)    no later fifteen (15)
following satisfying notification effective must (i) determination
supporting evidence available support assertion cannot agree dispute
resolution mechanism i apply h) soon practicable components missing affect
replace items expense supplemental notifies after receiving wishes furnish
items; delivers prior kitting date installation cfm; loans slave unlts/lrus
additional charge purposes testing work misslng considered per provisions
i)    1f determines revised relating needs overall incremental cost estimated
exceed us$50 aggregate promptly detailed express act acceptance hours
further delay impact turn excusable tis )) redllvery redeliver serviceable
k) package shipping stands containers aaas standard commercial practices i)
documentation copies arr finding reports) relevant lessor defined below) m)
retain store scrapped period three (3) months requires disposal sake clarity
end owned seek either destroy return allow facilities duties operator
certificate holder been installed tn operated (each "relevant lessor")
discharging inspection requirement lease addition permit one enter (or
premises) carried during observe 4 workscope
(i) general "workscope") latest guide publications aaa) covered section
describing updated condition mutual intended referred to: a)    ensure made
restrictions deferred next scheduled pertormance restoration (11)
conditions: b)    meet standards include reliability enhancements repairs
incorporate elements term improve operating characteristics cfm-approved
repairs; c)    address pertormed respect visit; restore egt margin achieve
level exhaust gas temperature ("egt") equivalent seventy percent {70%)
production (ll) "performance visit" quallfled which: minimum modules exposed
disassembled piece subsequently refurbished: high-pressure turbine hpt
nozzle rotor shroud) combustor chamber high pressure compressor hpc front
stator rear stator) llmlted can demonstrate tuls recommended engtne slmllar
configuration (thrust llp life remaining margin) since new b) provides build
consistent operation 000 cycles including ("minimum build") do replaced
parts; soft lives hard mutually respected vlstt good faith) acting able
those excess and/or materials (111) miscellaneous acknowledges regarding
non-cfm matertat repatrs their design manufacturing material potential
effects arising oem knowledge expertise developed whole taking into account
environment continued recognizes agrees assessments recommendations nor
maintain reflected present predicated capabllltles already qualifled
truengine maintained status added upon meeting qualification requlrements
result inspections reasons circumstances demonstrated customer) prtor
wrttten (not delayed) replacement same higher thrust least market value
utility removed (lncludlng llps) 5 title risk loss furnished incorporated
deemed sold materfal pass incorporation free encumbrances liens security
interests damage materlal redelivery corresponding repairable parts)
otherwise 6 artlcle basic qec "basic configuration") f ln delivered
equipment redelivered fn dellvered charged directives bulletlns elect
rotable repaired ratable better serialized tracked older withheld) still
rejected was technically acceptable install exchange fee clp price limited
full traceable back birth record accepted before', '2014-11-21 00:00:00',
111);
INSERT INTO semantified_content_key_word (id, semantified_content_id,
key_word_text, content_date, context_id) VALUES (7441, 7441, ',
activationdate agreementnumber enginemodel enginetype llpminimumbuild
servicecreditdate steppedpopularrate turnaroundtime leap-1a as united
continental customer 1/ neutral qec configuration engines shop maintenance:
each engine delivered eacl visit shall come the full list equipment
excluding following items: ata24: idg ata 29: hydraulic pump i pressure
switch case drain 36: ip check valve hp prsov 71: inlet cowl 78: exhaust
nozzle centerbody sake clarity mounts are included this may be modified
later (subject agreement amendment) depending final • 2/ lrus covered
optional flight line lru support services:  73-11-xx fuel filter assembly
sbv / bai servo heater return tank oil cooler (fcoc) 73-21-xx metering unit
splitier control servo-valve activation t12 temperature sensor t25 t3
permanent magnet alternator (rotor+stator) sub system (pss) box rating plug
electronic (eec) flow transmitter wiring harnesses (tbd) 73-34-xx dp
strainer delta p 75-21-xx hptacc 75-22-xx lptacc 75-24-xx blower eec cooling
75-28-xx mtcvalve 75-31-xx vbv actuator 75-32-xx vsv 75-33-xx transient
bleed 79-11-xx 79-21-xx lubrification main fuel- heat exchanger surface air
eductor 79-31-xx quantity 79-32-xx -temperature 79-35-xx 79-36-xx debris
monitoring all other labor charges not specified exhibit us $ 00 per hour
parts material (mfc furnished stated otherwise) type price handling fee new
(non-llp) clp maximum 000 piece part group like nomenclature (llp) n/a zero
fees items maximum$ 500 an exceptional basis mfc acreement used ratable
repair when applicable aggregate capped at$ aooregate serviceable %
subcontracted subcontractor invoice services item component accessories
repair: directory accessory catalog pricing effect time basis: prices states
dollars effective december 7 escalation hourly rates fixed test cell usage
charges: g (escalation)', '2014-11-17 00:00:00', 111);

-------------------------------------------------------------
select sckw.*
FROM semantified_content_key_word sckw
where TO_TSVECTOR(sckw.key_word_text) @@ TO_TSQUERY('exhausted');

This is the query which  i am running.And the keyword "exhausted" is present
only in one of the rows but i am getting all the 3 rows.

How to avoid the rows where the keyword is not present

Thanks
Prasanna

Re: BUG #12204: Getting wrong results from full text search

From
Alvaro Herrera
Date:
prasanna@semantifi.com wrote:

> select sckw.*
> FROM semantified_content_key_word sckw
> where TO_TSVECTOR(sckw.key_word_text) @@ TO_TSQUERY('exhausted');
>
> This is the query which  i am running.And the keyword "exhausted" is present
> only in one of the rows but i am getting all the 3 rows.

My guess, without actual experimentation, is that "exhausted" stems down
to "exhaust", which is present in the three provided rows.

--
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: BUG #12204: Getting wrong results from full text search

From
Tom Lane
Date:
Alvaro Herrera <alvherre@2ndquadrant.com> writes:
> prasanna@semantifi.com wrote:
>> select sckw.*
>> FROM semantified_content_key_word sckw
>> where TO_TSVECTOR(sckw.key_word_text) @@ TO_TSQUERY('exhausted');
>>
>> This is the query which  i am running.And the keyword "exhausted" is present
>> only in one of the rows but i am getting all the 3 rows.

> My guess, without actual experimentation, is that "exhausted" stems down
> to "exhaust", which is present in the three provided rows.

Yeah.  This is a feature, not a bug.  If you don't want stemming to
happen, you should switch to a text search configuration that doesn't
do that.  (I think the built-in "simple" configuration doesn't, but
in any case you could certainly construct one that doesn't.)

            regards, tom lane