Quantcast

Left Outer Join of a Table-valued Function

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
1 message Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Left Outer Join of a Table-valued Function

Josef Kučera
Hello,
I have discovered a somewhat strange behavior. I utilize the table-valued functionality to inject generated data into SQL queries. Imagine a query like this:

SELECT * FROM mData AS D
LEFT OUTER JOIN gLink(mData.Param1) AS L ON L.ID=D.ID

The virual table-valued gLink processes only the input parameter, all the other constraints are returned with argvIndex=0, omit=0 for SQLite to handle. The mData.Param1 seems to behave like an INNER JOIN constraint and limits the query output. Is that correct?

I have discovered a workaround:

SELECT * FROM mData AS D
LEFT OUTER JOIN (SELECT * FROM gLink(mData.Param1) AS dummy) AS L ON L.ID=D.ID

This query returns the expected data.

Best regards
Joe
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Loading...