[SOLVED] postgresql: negative substring length not allowed but no obvious violation in data

postgresql: negative substring length not allowed but no obvious violation in data

I receive the error message ERROR: negative substring length not allowed when I run the following query in postgresql:

select count(*) as name_matches
from table1 x
inner join
(select * from table2 where trim(last_name) <> 'unknown') y
using (id1)
where substring( trim(x.full_name), 1, (length( trim(x.full_name) ) - 2) ) = trim(y.last_name||' '||y.first_name)

However, this query returns 0 results:

select count(*)
from table1 x 
inner join
(select * from table2 where trim(last_name) <> 'unknown') y
using (id1)
where length( trim(x.full_name) ) < 3

Note that all fields are non-null fields. Any suggestions for what may have been over-looked? Thanks!

Solution

x.full_name and x.last_name in the final WHERE clause are executed on the entire table. The query optimizer may choose to execute that before or after the join. It would appear that, in this case, it's getting executed before the join happens, so you get an error because your 'UNKNOWN' items which would cause a negative substring length aren't filtered out, yet.