The problem is with a query that is using TextIndex to search for records and an additional condition.
Additional condition comes from an external function, but it can be any other condition.

If there is no additional condition the there is an TextIndex scan.

But if I add an another condition witch is not an TextIndex CONTAINS the result is 0 rows.

I know that external function is working because the next query is returning correct results.

SELECT HeadingID, StatusActive(H.Status, 8) from Headings H
WHERE ((Heading CONTAINS 'zakon*')  OR (Remark CONTAINS 'zakon*')) AND StatusActive(H.Status, 8)
ORDER BY CAST(Heading AS varchar(20)) COLLATE "SRL_CI"

I am sending Execution plans for all three examples.

================================================================================
SQL Query (Executed by ElevateDB 2.04 Build 4)

Note: The SQL  shown here is generated by ElevateDB and may not be exactly the
same as the SQL that was originally entered.  However, none of the differences
alter the execution results in any way.
================================================================================

SELECT ALL
"HeadingID" AS "HeadingID",
StatusActive("H"."Status", 8) AS "Expression"
FROM "Headings" AS "H"
WHERE (("Heading" CONTAINS 'zakon*') OR ("Remark" CONTAINS 'zakon*')) AND
(("Heading" CONTAINS 'rad*') OR ("Remark" CONTAINS 'rad*'))
ORDER BY CAST("Heading", VARCHAR(20)) COLLATE "SRL_CI"

Source Tables
-------------

Headings (H): 61486 rows

Result Set
----------

The result set was insensitive and read-only
The result set consisted of zero or more rows

Filtering
---------

The following filter condition was applied to the Headings (H) table:

(("Heading" CONTAINS 'rad*')

Index scan (Headings.TX_Heading): 620962 keys, 11509760 bytes estimated cost

OR

("Remark" CONTAINS 'rad*')

Index scan (Headings.TX_Remark): 37137 keys, 671744 bytes estimated cost)

AND

(("Heading" CONTAINS 'zakon*')

Index scan (Headings.TX_Heading): 620962 keys, 11509760 bytes estimated cost

OR

("Remark" CONTAINS 'zakon*')

Index scan (Headings.TX_Remark): 37137 keys, 671744 bytes estimated cost)


Result set I/O statistics
-------------------------

Total rows visited: 229

Row buffer manager

Max buffer size: 1048536 Buffer size: 16488

Hits: 229   Misses: 0   Hit ratio: 1

Reads: 0   Bytes read: 0

Writes: 0   Bytes written: 0

Index Page buffer manager

Max buffer size: 2097152 Buffer size: 8192

Hits: 229   Misses: 0   Hit ratio: 1

Reads: 0   Bytes read: 0

Writes: 0   Bytes written: 0

================================================================================
229 row(s) returned in 2.902 secs
================================================================================

================================================================================
SQL Query (Executed by ElevateDB 2.04 Build 4)

Note: The SQL  shown here is generated by ElevateDB and may not be exactly the
same as the SQL that was originally entered.  However, none of the differences
alter the execution results in any way.
================================================================================

SELECT ALL
"HeadingID" AS "HeadingID",
StatusActive("H"."Status", 8) AS "Expression"
FROM "Headings" AS "H"
WHERE (("Heading" CONTAINS 'zakon*') OR ("Remark" CONTAINS 'zakon*')) AND
(("Heading" CONTAINS 'rad*') OR ("Remark" CONTAINS 'rad*')) AND
((StatusActive("H"."Status", 8)) = TRUE)
ORDER BY CAST("Heading", VARCHAR(20)) COLLATE "SRL_CI"

Source Tables
-------------

Headings (H): 61486 rows

Result Set
----------

The result set was insensitive and read-only
The result set consisted of zero or more rows

Filtering
---------

The following filter condition was applied to the Headings (H) table:

((StatusActive("H"."Status", 8)) = TRUE)

Row scan (Headings): 61486 rows, 3443216 bytes estimated cost

AND

(("Heading" CONTAINS 'rad*') OR ("Remark" CONTAINS 'rad*')) AND (("Heading"
CONTAINS 'zakon*') OR ("Remark" CONTAINS 'zakon*'))

Row scan (Headings): 61486 rows, 3443216 bytes estimated cost


Result set I/O statistics
-------------------------

Total rows visited: 0

Row buffer manager

Max buffer size: 1048536 Buffer size: 0

Hits: 0   Misses: 0   Hit ratio: 0

Reads: 0   Bytes read: 0

Writes: 0   Bytes written: 0

Index Page buffer manager

Max buffer size: 2097152 Buffer size: 0

Hits: 0   Misses: 0   Hit ratio: 0

Reads: 0   Bytes read: 0

Writes: 0   Bytes written: 0

================================================================================
0 row(s) returned in 2.948 secs
================================================================================


================================================================================
SQL Query (Executed by ElevateDB 2.04 Build 4)

Note: The SQL  shown here is generated by ElevateDB and may not be exactly the
same as the SQL that was originally entered.  However, none of the differences
alter the execution results in any way.
================================================================================

SELECT ALL
"HeadingID" AS "HeadingID",
StatusActive("H"."Status", 8) AS "Expression"
FROM "Headings" AS "H"
WHERE (("Heading" CONTAINS 'zakon*') OR ("Remark" CONTAINS 'zakon*')) AND
StatusActive("H"."Status", 8) = TRUE
ORDER BY CAST("Heading", VARCHAR(20)) COLLATE "SRL_CI"

Source Tables
-------------

Headings (H): 61486 rows

Result Set
----------

The result set was insensitive and read-only
The result set consisted of zero or more rows

Filtering
---------

The following filter condition was applied to the Headings (H) table:

StatusActive("H"."Status", 8) = TRUE

Row scan (Headings): 61486 rows, 3443216 bytes estimated cost

AND

(("Heading" CONTAINS 'zakon*') OR ("Remark" CONTAINS 'zakon*'))

Row scan (Headings): 61486 rows, 3443216 bytes estimated cost


Result set I/O statistics
-------------------------

Total rows visited: 1180

Row buffer manager

Max buffer size: 1048536 Buffer size: 84960

Hits: 1180   Misses: 0   Hit ratio: 1

Reads: 0   Bytes read: 0

Writes: 0   Bytes written: 0

Index Page buffer manager

Max buffer size: 2097152 Buffer size: 57344

Hits: 2066   Misses: 0   Hit ratio: 1

Reads: 0   Bytes read: 0

Writes: 0   Bytes written: 0

================================================================================
1180 row(s) returned in 2.605 secs
================================================================================

Igor


I'm puzzled about it, especially why it moves from an index scan to a row scan. I suggest emailing Tim direct rather than waiting for him to pop up here again.

It might be the query is just baffling Tim's parser / query optimiser. A couple of things to try:

1. put the expression at the start of the WHERE clause
2. wrap the text index conditions in brackets
3. create a temporary table without the external function and then query that table using the expression

Roy Lambert

Igor,

<< The problem is with a query that is using TextIndex to search for records
and an additional condition.  Additional condition comes from an external
function, but it can be any other condition. >>

Could you please send me your database catalog and table files via email ?
I'd like to run the query here and make sure that this is still a problem
with 2.07.

I tried the following query here:

SELECT * FROM events
WHERE (Event_Description CONTAINS 'arena' OR Event_Description CONTAINS
'parking') AND
Event_Photo IS NOT NULL

with a text index on Event_Description, and the query works fine with 2.07.

--
Tim Young
Elevate Software
www.elevatesoft.com

I did some more investigation.  

This query is not working:
1. SELECT HeadingID FROM Headings H WHERE  (Heading CONTAINS 'zakon*') AND (Heading CONTAINS 'rad*') AND (HeadingID > 1000)

But this one is working

2. SELECT HeadingID FROM Headings H WHERE  (Heading CONTAINS 'zakon') AND (Heading CONTAINS 'rad') AND (HeadingID > 1000)

And this one is working

3. SELECT HeadingID FROM Headings H WHERE  (Heading CONTAINS 'zakon*') AND (Heading CONTAINS 'rad*')

I think that the problem is with partial word searching. I am using my own WordGenerator. But I do not thing that WordGenerator is a problem because query 3 is working correctly.
Igor &#268;olovi&#263;
INTERMEX
www.propisi.com

Igor


Can you post a sample database AND your word generator code and any other functions that may be needed to the binaries.

Roy Lambert [Team Elevate]

I have tried with Default word generator. The problem is still there.
The problem is in '*' in CONTAINS expression.

The Query 1. never enters
TEDBExprFilter.Evaluate
...
case TokenType of
...
toCONTAINS,toNOTCONTAINS:
...

But Query 2. do enters this portion of code. I have tryed to change '*' to something else with no success.

Query 1.
SELECT HeadingID FROM Headings WHERE StatusActive(Status, 8) AND (((Heading CONTAINS 'zakon*') AND (Heading CONTAINS 'radu*')) OR ((Remark CONTAINS 'zakon*') AND (Remark CONTAINS 'radu*'))) ORDER BY CAST(Heading AS varchar(20)) COLLATE "SRL_CI"

Query 2.
SELECT HeadingID FROM Headings WHERE StatusActive(Status, 8) AND (((Heading CONTAINS 'zakon') AND (Heading CONTAINS 'radu')) OR ((Remark CONTAINS 'zakon') AND (Remark CONTAINS 'radu'))) ORDER BY CAST(Heading AS varchar(20)) COLLATE "SRL_CI"

-
Igor Colovic
INTERMEX
www.propisi.com

Igor


You're going to have to wait for Tim on this one.

Roy Lambert [Team Elevate]

Hi Igor,

> This query is not working:

I believe I've been able to reproduce something the same results you are
seeing.  I've send a sample database along with a test query and specific
conditions to reproduce it to Tim a little earlier today.  Hopefully he will
be able to reproduce it on his side.

---------------------------------------
Terry Swiers
Millennium Software, Inc.
http://www.1000years.com
---------------------------------------

Terry,

Answered via email.

Thanks, and a fix will be in the upcoming 2.08 release, due out by early
next week.

--
Tim Young
Elevate Software
www.elevatesoft.com