#1  
Old 2nd December 2006, 01:25 AM
David Charron
 
Posts: n/a
Default LEFT JOIN wont work when tables has its blob fields filled

Hi,

Running the speficied query when the Employer.Picture field has data will
process data forever (no timout event if specified) and no result will ever
be displayed.

When Employer.Picture field is set to null the query runs fine.

This has been tested with 2.05 and 2.05.99 rel3

To NexusDB staff : Please contact me by e-mail (davcha_at_ppdgroup,com) and
I'll send you test tables.

Execute the following query and watch the server workload and memory go way
up without any result beeing returned.

SELECT LABELPPD.AUTONUM, LABELPPD.PIECE, LABELPPD.QTY, LABELPPD."DATETIME"
PRINTDT, LABELPPD.SHIFT, LABELPPD.DTSHIFT,
LABELPPD.EMPLOYEE, EMPLOYER.PRENOM AS FIRSTNAME, EMPLOYER.NOM AS LASTNAME,
MACHINELIST.NAME AS MACHINENAME, RECEPTIONMAGICALOT.LOTSUPPLIER AS
LOT,MANUFACTURER.NAME AS MANUFACTURER,PIECEPPD.UL AS ULCODE,
PIECEPPD.MANUFACTURERPARTNAME,
RECEPTIONMAGICA.RECEPTIONCODE, RECEPTIONMAGICA.AUTONUM AS RECEPTIONINDEX
FROM LABELPPD
LEFT JOIN EMPLOYER ON LABELPPD.EMPLOYEE = EMPLOYER.CODE
LEFT JOIN MACHINELIST ON LABELPPD.MACHINE = MACHINELIST.AUTONUM
LEFT JOIN RECEPTIONMAGICALOT ON LABELPPD.RECEPTIONMAGICALOT =
RECEPTIONMAGICALOT.AUTONUM
LEFT JOIN MANUFACTURER ON LABELPPD.MANUFACTURER = MANUFACTURER.AUTONUM
LEFT JOIN PIECEPPD ON RECEPTIONMAGICALOT.PIECE = PIECEPPD.NOPIECE
LEFT JOIN RECEPTIONMAGICA ON RECEPTIONMAGICALOT.RECEPTIONCODE =
RECEPTIONMAGICA.RECEPTIONCODE
WHERE
LABELPPD.AUTONUM IN (5)

If you set Employer.picture to null the query execute fines.

UPDATE EMPLOYER
SET PICTURE = NULL;

SELECT LABELPPD.AUTONUM, LABELPPD.PIECE, LABELPPD.QTY, LABELPPD."DATETIME"
PRINTDT, LABELPPD.SHIFT, LABELPPD.DTSHIFT,
LABELPPD.EMPLOYEE, EMPLOYER.PRENOM AS FIRSTNAME, EMPLOYER.NOM AS LASTNAME,
MACHINELIST.NAME AS MACHINENAME, RECEPTIONMAGICALOT.LOTSUPPLIER AS
LOT,MANUFACTURER.NAME AS MANUFACTURER,PIECEPPD.UL AS ULCODE,
PIECEPPD.MANUFACTURERPARTNAME,
RECEPTIONMAGICA.RECEPTIONCODE, RECEPTIONMAGICA.AUTONUM AS RECEPTIONINDEX
FROM LABELPPD
LEFT JOIN EMPLOYER ON LABELPPD.EMPLOYEE = EMPLOYER.CODE
LEFT JOIN MACHINELIST ON LABELPPD.MACHINE = MACHINELIST.AUTONUM
LEFT JOIN RECEPTIONMAGICALOT ON LABELPPD.RECEPTIONMAGICALOT =
RECEPTIONMAGICALOT.AUTONUM
LEFT JOIN MANUFACTURER ON LABELPPD.MANUFACTURER = MANUFACTURER.AUTONUM
LEFT JOIN PIECEPPD ON RECEPTIONMAGICALOT.PIECE = PIECEPPD.NOPIECE
LEFT JOIN RECEPTIONMAGICA ON RECEPTIONMAGICALOT.RECEPTIONCODE =
RECEPTIONMAGICA.RECEPTIONCODE
WHERE
LABELPPD.AUTONUM IN (5)

--
Regards from Quebec.

David Charron


  #2  
Old 2nd December 2006, 12:01 PM
Eivind Bakkestuen [NDD]
 
Posts: n/a
Default Re: LEFT JOIN wont work when tables has its blob fields filled

> To NexusDB staff : Please contact me by e-mail
> (davcha_at_ppdgroup,com) and I'll send you test tables.


Please email tables etc to support (at) nexusdb (dot) com, thanks for
reporting.

--

Eivind Bakkestuen
Nexus Database Systems Pty Ltd


  #3  
Old 5th December 2006, 12:03 AM
David Charron
 
Posts: n/a
Default Re: LEFT JOIN wont work when tables has its blob fields filled

E-mail sent.
Please tell me if you dont receive it. I'll put it on a ftp.

David.

"Eivind Bakkestuen [NDD]" <Ieivind.bakkestuenHATE@nexusdb.comSPAM> wrote in
message news:4570dfff@wic040d....
> Please email tables etc to support (at) nexusdb (dot) com, thanks for
> reporting.
>
> --
>
> Eivind Bakkestuen
> Nexus Database Systems Pty Ltd
>



  #4  
Old 5th December 2006, 08:49 AM
Eivind Bakkestuen [NDD]
 
Posts: n/a
Default Re: LEFT JOIN wont work when tables has its blob fields filled

> Please tell me if you dont receive it. I'll put it on a ftp.

Received and logged, thx.

--

Eivind Bakkestuen
Nexus Database Systems Pty Ltd




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
ADO Provider and Blob fields Dave Sellers nexusdb.public.support.adoprovider 9 21st January 2008 08:10 AM
BLOB fields Michael Duncan nexusdb.public.support.adoprovider 3 20th July 2004 05:00 AM
Left join Paul Evers nexusdb.public.support.sql 3 18th November 2003 11:18 PM
copy fields between 2 tables Mickael WONG nexusdb.public.support.sql 4 14th November 2003 11:19 PM
SUM() does not work with Word32-Fields ?! Pascal nexusdb.public.support.sql 1 5th November 2003 08:47 PM


All times are GMT +11. The time now is 02:52 AM.


Powered by vBulletin® Version 3.6.8
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.