Request a Demo
Home   >   mailing lists

Re: Problems with question marks in operators (JDBC, ECPG, ...) - Mailing list pgsql-hackers

From Bruno Harbulot
Subject Re: Problems with question marks in operators (JDBC, ECPG, ...)
Date
Msg-id CANPVNBYbdLMKaQjAQLBzVLTX_or2Dre1arz_ZMHFA2DtzH-X7w@mail.gmail.com
Whole thread Raw
In response to Re: Problems with question marks in operators (JDBC, ECPG, ...)  (Tom Lane <tgl@sss.pgh.pa.us>)
Responses Re: Problems with question marks in operators (JDBC, ECPG, ...)
List pgsql-hackers
Tree view


On Tue, May 19, 2015 at 7:22 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Mike Blackwell <mike.blackwell@rrd.com> writes:
> See for example
> http://docs.oracle.com/cd/B19306_01/text.102/b14218/cqoper.htm#i997330,
> Table 3-1, third row, showing the precedence of '?'.  Further down the
> page, under "Fuzzy" see "Backward Compatibility Syntax".

If I'm reading that right, that isn't a SQL-level operator but an operator
in their text search query language, which would only appear in SQL
queries within string literals (compare tsquery's query operators in PG).
So it wouldn't be a hazard for ?-substitution, as long as the substituter
was bright enough to not change string literals.

                        regards, tom lane

That's how I read it too. I've tried this little test: http://sqlfiddle.com/#!4/7436b/4/0

CREATE TABLE test_table (
  id INTEGER PRIMARY KEY,
  name VARCHAR(100)
);

INSERT INTO test_table (id, name) VALUES (1, 'Nicole');
INSERT INTO test_table (id, name) VALUES (2, 'Nicholas');
INSERT INTO test_table (id, name) VALUES (3, 'Robert');
INSERT INTO test_table (id, name) VALUES (4, 'Michael');
INSERT INTO test_table (id, name) VALUES (5, 'Nicola');

CREATE INDEX idx_test_table_name ON test_table(name) INDEXTYPE IS CTXSYS.CONTEXT;

SELECT * FROM test_table WHERE CONTAINS(name, '?Nicolas', 1) > 0;


Fuzzy matching works indeed, but the question mark is part of the literal (similarly to % when using LIKE).

Best wishes,

Bruno.

pgsql-hackers by date:

Previous
From: Robert Haas
Date:
Subject: Re: RFC: Non-user-resettable SET SESSION AUTHORISATION
Next
From: Andres Freund
Date:
Subject: Re: RFC: Non-user-resettable SET SESSION AUTHORISATION