SQL Join - MySQL/PostgreSQL difference?

Brice Ruth wrote:

Greetings.

I'm working with a product provided by a third part that interfaces to
data housed in a database of your choice. Previously, my choice was
MySQL - apparently it handled certain queries too slowly, so I'm giving
PostgreSQL a shot. Here's the query:

SELECT
a.Number,
a.Code,
a.Text
FROM
b,
a
WHERE
(b.Id = a.Id) AND
(VersionId = 'key1') AND
(Category = 'key2') AND
(b.d_Id = 'key3')
ORDER BY
a.Number;

(my apologies: I had to 'mangle' the table/column names because of NDA)

So my question is this: would this query operate differently in MySQL
than in PostgreSQL? The reason I ask is that this query in MySQL
returns results, yet in PostgreSQL it does not. I read a post about
PostgreSQL not supporting outer joins, but I don't have enough
experience with SQL to determine if this is such a query or not. Please

advise.

Any help will be (as always) sincerely appreciated.

--
Brice Ruth
WebProjkt, Inc.
VP, Director of Internet Technology
http://www.webprojkt.com/

It should work the same in both. The only thing I notice is that not all
the field names are qualified with table names or aliases. That can lead
to ambiguity, but the query would blow up on both databases if that were a
problem.

Ian

Ian,

Thanx - I figured the same about the ambiguity. I'll keep tryin' to
debug this with the vendor, then.

-Brice

Ian Harding wrote:

Brice Ruth wrote:

Greetings.

I'm working with a product provided by a third part that interfaces to
data housed in a database of your choice. Previously, my choice was
MySQL - apparently it handled certain queries too slowly, so I'm giving
PostgreSQL a shot. Here's the query:

SELECT
a.Number,
a.Code,
a.Text
FROM
b,
a
WHERE
(b.Id = a.Id) AND
(VersionId = 'key1') AND
(Category = 'key2') AND
(b.d_Id = 'key3')
ORDER BY
a.Number;

(my apologies: I had to 'mangle' the table/column names because of NDA)

So my question is this: would this query operate differently in MySQL
than in PostgreSQL? The reason I ask is that this query in MySQL
returns results, yet in PostgreSQL it does not. I read a post about
PostgreSQL not supporting outer joins, but I don't have enough
experience with SQL to determine if this is such a query or not. Please

advise.

Any help will be (as always) sincerely appreciated.

--
Brice Ruth
WebProjkt, Inc.
VP, Director of Internet Technology
http://www.webprojkt.com/

It should work the same in both. The only thing I notice is that not all
the field names are qualified with table names or aliases. That can lead
to ambiguity, but the query would blow up on both databases if that were a
problem.

Ian

--
Brice Ruth
WebProjkt, Inc.
VP, Director of Internet Technology
http://www.webprojkt.com/

FYI: Here are the table definitions:

CREATE TABLE TBLPEMDRUGLINK
(
DRUGID VARCHAR(10) NOT NULL,
MONOGRAPHID VARCHAR(10) NOT NULL,
CONSTRAINT PK_TBLPEMDRUGLINK PRIMARY KEY (DRUGID, MONOGRAPHID)
);

CREATE TABLE TBLFDBMONO
(
VERSIONID VARCHAR(10) NOT NULL,
CATEGORY VARCHAR(10) NOT NULL,
MONOGRAPHID VARCHAR(10) NOT NULL,
SEQUENCENUMBER SMALLINT NOT NULL,
SECTIONCODE VARCHAR(1),
LINETEXT VARCHAR(255),
CONSTRAINT PK_TBLFDBMONO PRIMARY KEY (VERSIONID, CATEGORY,
MONOGRAPHID, SEQUENCENUMBER)
);

Running the following query:

Query1: SELECT sequencenumber,sectioncode,linetext
Query1: FROM tblfdbmono fdb, tblpemdruglink pem WHERE
Query1: fdb.monographid=pem.monographid AND
Query1: fdb.versionid='FDB-PE' AND
Query1: fdb.category='PEM' AND
Query1: pem.drugid='DG-5039';

returns 0 rows.

However, the following two queries produce results:

Query2: SELECT * FROM tblpemdruglink WHERE drugid='DG-5039';

Query3: SELECT * FROM tblfdbmono WHERE
Query3: monographid='2008' AND
Query3: versionid='FDB-PE' AND
Query3: category='PEM';

To my knowledge, Query1 is the join that should produce the same results
as the manual join represented by queries 2 & 3.

What's going on?

-Brice

Ian Harding wrote:

Brice Ruth wrote:

Greetings.

I'm working with a product provided by a third part that interfaces to
data housed in a database of your choice. Previously, my choice was
MySQL - apparently it handled certain queries too slowly, so I'm giving
PostgreSQL a shot. Here's the query:

SELECT
a.Number,
a.Code,
a.Text
FROM
b,
a
WHERE
(b.Id = a.Id) AND
(VersionId = 'key1') AND
(Category = 'key2') AND
(b.d_Id = 'key3')
ORDER BY
a.Number;

(my apologies: I had to 'mangle' the table/column names because of NDA)

So my question is this: would this query operate differently in MySQL
than in PostgreSQL? The reason I ask is that this query in MySQL
returns results, yet in PostgreSQL it does not. I read a post about
PostgreSQL not supporting outer joins, but I don't have enough
experience with SQL to determine if this is such a query or not. Please

advise.

Any help will be (as always) sincerely appreciated.

--
Brice Ruth
WebProjkt, Inc.
VP, Director of Internet Technology
http://www.webprojkt.com/

It should work the same in both. The only thing I notice is that not all
the field names are qualified with table names or aliases. That can lead
to ambiguity, but the query would blow up on both databases if that were a
problem.

Ian

--
Brice Ruth
WebProjkt, Inc.
VP, Director of Internet Technology
http://www.webprojkt.com/

Here's something to chew on ...

The following snippet of SQL produces a result:

--
CREATE TABLE TBLPEMDRUGLINK
(
DRUGID VARCHAR(10) NOT NULL,
MONOGRAPHID VARCHAR(10) NOT NULL,
CONSTRAINT PK_TBLPEMDRUGLINK PRIMARY KEY (DRUGID, MONOGRAPHID)
);
CREATE TABLE TBLFDBMONO
(
VERSIONID VARCHAR(10) NOT NULL,
CATEGORY VARCHAR(10) NOT NULL,
MONOGRAPHID VARCHAR(10) NOT NULL,
SEQUENCENUMBER INT2 NOT NULL,
SECTIONCODE VARCHAR(1),
LINETEXT VARCHAR(255),
CONSTRAINT PK_TBLFDBMONO PRIMARY KEY (VERSIONID, CATEGORY,
MONOGRAPHID, SEQUENCENUMBER)
);
insert into tblpemdruglink values ('DG-5039','2008');
insert into tblfdbmono values ('FDB-PE','PEM','2008',8,'A','some info to display');
SELECT sequencenumber,sectioncode,linetext
FROM tblfdbmono fdb, tblpemdruglink pem WHERE
fdb.monographid=pem.monographid AND
fdb.versionid='FDB-PE' AND
fdb.category='PEM' AND
pem.drugid='DG-5039';
--

This puts some 'fake' data into the tables, but the fake data resembles
the true data. Now, when I load the tables with the true data using:

COPY tblpemdruglink FROM '/tmp/Data/db_files/tblPEMDrugLink.txt' USING
DELIMITERS '|';
COPY tblfdbmono FROM '/tmp/Data/db_files/tblFDBMono.txt' USING
DELIMITERS '|';

and then run the same SELECT as before:

SELECT sequencenumber,sectioncode,linetext
FROM tblfdbmono fdb, tblpemdruglink pem WHERE
fdb.monographid=pem.monographid AND
fdb.versionid='FDB-PE' AND
fdb.category='PEM' AND
pem.drugid='DG-5039';

I get 0 results. So the logical conclusion: the dataset doesn't contain
a record that satisfies the query. However, the following queries
represent the 'manual join' that the join above automates:

SELECT * FROM tblpemdruglink WHERE drugid='DG-5039';

drugid | monographid
---------+-------------
DG-5039 | 2008
(1 row)

SELECT sequencenumber, sectioncode, linetext FROM tblfdbmono fdb WHERE
fdb.monographid='2008' AND
fdb.versionid='FDB-PE' AND
fdb.category='PEM';

sequencenumber | sectioncode | linetext
----------------+-------------+---------------
{************** results omitted *************)
(64 rows)

So if the 'manual join' produces data yet the actual join does not, then
the logical conclusion above doesn't hold and something is wrong
(besides the data set). That's where I need help. I can stare at the
SQL all day & all night long and not figure anything out ... I'm not an
expert at SQL and this is the first time I'm using PostgreSQL. One
thing I can say, however, is that mirroring this in MySQL (and allowing
for case-sensitivity) produces results (it takes a few seconds because
the tblFDBMono table is actually quite large). What's going on?? Any
help at all is most sincerely appreciated. Hell, I wouldn't mind
coughin' up the $$ for a support incident if I thought that would help
... but I'm not entirely sure where the problem is to warrant a support incident.

Please respond to this newsgroup, the mailing list (which should be
mirrored on the newsgroup), and/or to me personally. I'm twiddlin' my
thumbs until I can figure this one out.

Regards,
Brice Ruth

Brice -
What's the result of

select monographid,length(monographid) from tblpemdruglink where
monographid ~ '^2008';

It occurs to me that your delimited text file may have padded values,
and "=" insists on exact matches for VARCHAR.

update tblpemdruglink set monographid=btrim(monographid);

might help, if the first query returns anything but 4.

Ross

On Tue, Feb 06, 2001 at 05:22:59PM -0600, Brice Ruth wrote:

Here's something to chew on ...

The following snippet of SQL produces a result:

<SNIP>

Ross,

Thanx for the heads up on this. The select did indeed return something
other than four: 5. I updated as you suggested, but that alone didn't
fix the problem. I'm updating tblFDBMono now with the same type of
'fix' to see if this is the root of the problem. Is '=' handled
differently between PostgreSQL and MySQL in this case?

-Brice

"Ross J. Reedstrom" wrote:

Unfortunately ... that didn't seem to help :( I used btrim on all the
fields that were part of an equals (=) statement and reran the select
and got the same result (0 rows). After I was in the process of
updating the tables, I thought that this may fail ... since again, the
'manual join' of these two tables returns the correct results ... and it
uses the same equality tests as the SQL join.

I'm more than happy to keep tryin' different things, though ... anything
to get to the bottom of this.

Regards,
Brice Ruth

"Ross J. Reedstrom" wrote:

Run the following query:

SELECT fdb.versionid, fdb.category, pem.drugid FROM tblfdbmono fdb,
tblpemdruglink pem WHERE fdb.monographid = pem.monographid ORDER BY 1, 2,
3;

is anything returned? If not, that is your problem (no rows exists with
matching monographid's). If information is returned, however, pick an
arbitrary row, and plug the data into the following query (you will have
a valid where clause then):

SELECT fdb.sequencenumber, fdb.sectioncode, fdb.linetext FROM tblfdbmono
fdb, tblpemdruglink pem WHERE fdb.monographid = pem.monographid AND
fdb.versionid = '<<VERSION ID>>' AND fdb.category = '<<CATEGORY>>' AND
pem.drugid = '<<DRUG ID>>'

Also, you may want to try qualifying your table names, i.e.:

SELECT fdb.sequencenumber, fdb.sectioncode, fdb.linetext FROM tblfdbmono
fdb, tblpemdruglink pem WHERE fdb.monographid=pem.monographid AND
fdb.versionid='FDB-PE' AND fdb.category='PEM' AND pem.drugid='DG-5039';

Michael Fork - CCNA - MCP - A+
Network Support - Toledo Internet Access - Toledo Ohio

On Tue, 6 Feb 2001, Brice Ruth wrote:

I believe we're getting closer to the problem here. When I run the
first query below, I get 0 results, which should be wrong. When I run
the query:

SELECT length(monographid) FROM tblpemdruglink WHERE drugid='DG-5039';

I get 5 returned to me, even though the string is only 4 ('2008').
However, after doing:

UPDATE tblpemdruglink SET monographid=trim(monographid);

and rerunning the query above, I still get 5, not 4. Is something being
imported incorrectly by the COPY ... FROM? Any way for me to tell if
there are other non-printing characters in there?

Thank you all for the continued support on this.

Regards,
Brice Ruth

Michael Fork wrote:

Run the following query:

SELECT fdb.versionid, fdb.category, pem.drugid FROM tblfdbmono fdb,
tblpemdruglink pem WHERE fdb.monographid = pem.monographid ORDER BY 1, 2,
3;

is anything returned? If not, that is your problem (no rows exists with
matching monographid's). If information is returned, however, pick an
arbitrary row, and plug the data into the following query (you will have
a valid where clause then):

SELECT fdb.sequencenumber, fdb.sectioncode, fdb.linetext FROM tblfdbmono
fdb, tblpemdruglink pem WHERE fdb.monographid = pem.monographid AND
fdb.versionid = '<<VERSION ID>>' AND fdb.category = '<<CATEGORY>>' AND
pem.drugid = '<<DRUG ID>>'

Also, you may want to try qualifying your table names, i.e.:

SELECT fdb.sequencenumber, fdb.sectioncode, fdb.linetext FROM tblfdbmono
fdb, tblpemdruglink pem WHERE fdb.monographid=pem.monographid AND
fdb.versionid='FDB-PE' AND fdb.category='PEM' AND pem.drugid='DG-5039';

Michael Fork - CCNA - MCP - A+
Network Support - Toledo Internet Access - Toledo Ohio

On Tue, 6 Feb 2001, Brice Ruth wrote:

FYI: Here are the table definitions:

CREATE TABLE TBLPEMDRUGLINK
(
DRUGID VARCHAR(10) NOT NULL,
MONOGRAPHID VARCHAR(10) NOT NULL,
CONSTRAINT PK_TBLPEMDRUGLINK PRIMARY KEY (DRUGID, MONOGRAPHID)
);

CREATE TABLE TBLFDBMONO
(
VERSIONID VARCHAR(10) NOT NULL,
CATEGORY VARCHAR(10) NOT NULL,
MONOGRAPHID VARCHAR(10) NOT NULL,
SEQUENCENUMBER SMALLINT NOT NULL,
SECTIONCODE VARCHAR(1),
LINETEXT VARCHAR(255),
CONSTRAINT PK_TBLFDBMONO PRIMARY KEY (VERSIONID, CATEGORY,
MONOGRAPHID, SEQUENCENUMBER)
);

Running the following query:

Query1: SELECT sequencenumber,sectioncode,linetext
Query1: FROM tblfdbmono fdb, tblpemdruglink pem WHERE
Query1: fdb.monographid=pem.monographid AND
Query1: fdb.versionid='FDB-PE' AND
Query1: fdb.category='PEM' AND
Query1: pem.drugid='DG-5039';

returns 0 rows.

However, the following two queries produce results:

Query2: SELECT * FROM tblpemdruglink WHERE drugid='DG-5039';

Query3: SELECT * FROM tblfdbmono WHERE
Query3: monographid='2008' AND
Query3: versionid='FDB-PE' AND
Query3: category='PEM';

To my knowledge, Query1 is the join that should produce the same results
as the manual join represented by queries 2 & 3.

What's going on?

-Brice

Ian Harding wrote:

Brice Ruth wrote:

Greetings.

I'm working with a product provided by a third part that interfaces to
data housed in a database of your choice. Previously, my choice was
MySQL - apparently it handled certain queries too slowly, so I'm giving
PostgreSQL a shot. Here's the query:

SELECT
a.Number,
a.Code,
a.Text
FROM
b,
a
WHERE
(b.Id = a.Id) AND
(VersionId = 'key1') AND
(Category = 'key2') AND
(b.d_Id = 'key3')
ORDER BY
a.Number;

(my apologies: I had to 'mangle' the table/column names because of NDA)

So my question is this: would this query operate differently in MySQL
than in PostgreSQL? The reason I ask is that this query in MySQL
returns results, yet in PostgreSQL it does not. I read a post about
PostgreSQL not supporting outer joins, but I don't have enough
experience with SQL to determine if this is such a query or not. Please

advise.

Any help will be (as always) sincerely appreciated.

--
Brice Ruth
WebProjkt, Inc.
VP, Director of Internet Technology
http://www.webprojkt.com/

It should work the same in both. The only thing I notice is that not all
the field names are qualified with table names or aliases. That can lead
to ambiguity, but the query would blow up on both databases if that were a
problem.

Ian

--
Brice Ruth
WebProjkt, Inc.
VP, Director of Internet Technology
http://www.webprojkt.com/

--
Brice Ruth
WebProjkt, Inc.
VP, Director of Internet Technology
http://www.webprojkt.com/

Brice Ruth <brice@webprojkt.com> writes:

SELECT length(monographid) FROM tblpemdruglink WHERE drugid='DG-5039';
I get 5 returned to me, even though the string is only 4 ('2008').

Okay, so you've got some invisible character in there, but not a space
(else the trim() woulda got rid of it). Tab maybe? Might want to go
back and examine your original data file more carefully, with an editor
that will show you control characters and such.

regards, tom lane

You could try this to see if it makes a difference

UPDATE tblpemdruglink SET monographid = substr(monographid, 1,
length(monographid) - 1)

Michael Fork - CCNA - MCP - A+
Network Support - Toledo Internet Access - Toledo Ohio

On Wed, 7 Feb 2001, Brice Ruth wrote:

Here's my latest tactic: I'm guessing that the data 'corruption' has
something to do with the way the data was exported from the original
database by the third party ... probably something with the cr/lf
linebreaks or something to that effect (the data field in question
happens to be the last field in a line in the data file). So, I loaded
up all the files in my text editor (BBedit) and saved 'em back out w/
UNIX linebreaks, then transferred the files as a bzipped tar over to the
server. I deleted all the content from the db and am reimporting via
'COPY ... FROM' as I write this. Its about 100MB of data so it'll take
a few minutes to import.

I'll report back any successes ... if things fail again, I'll follow all
the advice I've gotten to this point then report back with any
discoveries. Thank you SO much for all the help with this. I really
appreciate it tremendously.

-Brice

Tom Lane wrote:

Brice Ruth <brice@webprojkt.com> writes:

SELECT length(monographid) FROM tblpemdruglink WHERE drugid='DG-5039';
I get 5 returned to me, even though the string is only 4 ('2008').

Okay, so you've got some invisible character in there, but not a space
(else the trim() woulda got rid of it). Tab maybe? Might want to go
back and examine your original data file more carefully, with an editor
that will show you control characters and such.

regards, tom lane

--
Brice Ruth
WebProjkt, Inc.
VP, Director of Internet Technology
http://www.webprojkt.com/

Brice Ruth <brice@webprojkt.com> writes:

Here's my latest tactic: I'm guessing that the data 'corruption' has
something to do with the way the data was exported from the original
database by the third party ... probably something with the cr/lf
linebreaks or something to that effect (the data field in question
happens to be the last field in a line in the data file).

Ooooh ... the queries you were showing us made it look like the column
was not the last one, so I hadn't thought of that. Almost certainly,
your extra character is a CR. Postgres expects plain LF as newline in
COPY data files; if the newlines are actually CR/LF then the CRs will
be taken as part of the last data field.

regards, tom lane

Is there a simple (unix) command I can run on text files to convert
cr/lf to lf? The way I did it seemed pretty ass-backward to me (not to
mention time consuming).

-Brice

Tom Lane wrote:

Brice Ruth <brice@webprojkt.com> writes:

Here's my latest tactic: I'm guessing that the data 'corruption' has
something to do with the way the data was exported from the original
database by the third party ... probably something with the cr/lf
linebreaks or something to that effect (the data field in question
happens to be the last field in a line in the data file).

Ooooh ... the queries you were showing us made it look like the column
was not the last one, so I hadn't thought of that. Almost certainly,
your extra character is a CR. Postgres expects plain LF as newline in
COPY data files; if the newlines are actually CR/LF then the CRs will
be taken as part of the last data field.

regards, tom lane

--
Brice Ruth
WebProjkt, Inc.
VP, Director of Internet Technology
http://www.webprojkt.com/

Brice Ruth <brice@webprojkt.com> writes:

Is there a simple (unix) command I can run on text files to convert
cr/lf to lf?

You could strip out CRs entirely with

tr -d '\015'

regards, tom lane

On Wed, 7 Feb 2001, Brice Ruth wrote:

Is there a simple (unix) command I can run on text files to convert
cr/lf to lf? The way I did it seemed pretty ass-backward to me (not to
mention time consuming).

perl -pi~ -e 's/\r//g' file1 file2 ... fileN

--
Dominic J. Eidson
"Baruk Khazad! Khazad ai-menu!" - Gimli
-------------------------------------------------------------------------------
http://www.the-infinite.org/ http://www.the-infinite.org/~dominic/

heya

there are a couple of good example on how to do this in the perl
cookbook

the trim function in chapter one might help

the source code from the book is avail at ora.com

On Wed, 7 Feb 2001, Brice Ruth wrote:

Is there a simple (unix) command I can run on text files to convert
cr/lf to lf? The way I did it seemed pretty ass-backward to me (not to
mention time consuming).

perl -pi -e 's/\cM\\g' <filename>

will do the trick, assuming you have Perl instaleld on your system.

-- Brett
http://www.chapelperilous.net/~bmccoy/
---------------------------------------------------------------------------
Money will say more in one moment than the most eloquent lover can in years.

I believe the cr/lf was the problem. I reran the original query:

SELECT
tblFDBMono.SequenceNumber,
tblFDBMono.SectionCode,
tblFDBMono.LineText
FROM
tblPEMDrugLink,
tblFDBMono
WHERE
(tblPEMDrugLink.MonographId = tblFDBMono.MonographId) AND
(VersionId = 'FDB-PE') AND
(Category = 'PEM') AND
(tblPEMDrugLink.DrugId = 'DG-5039')
ORDER BY
tblFDBMono.SequenceNumber

And was returned the results I expected. Sweetness. Damned stupid that
it took me this long to figure this out ... I have the PostgreSQL book,
but I must have missed the section where it talked about COPY ... FROM
only taking the LF and leaving the CR. I knew the files were exported
in 'DOS' format (according to vi) ... and in the back of my mind I
thought that could screw things up. Hrmpf. At least I got it figured out.

Thanx a ton, guys.

"Brett W. McCoy" wrote:

On Wed, 7 Feb 2001, Brice Ruth wrote:

Is there a simple (unix) command I can run on text files to convert
cr/lf to lf? The way I did it seemed pretty ass-backward to me (not to
mention time consuming).

perl -pi -e 's/\cM\\g' <filename>

will do the trick, assuming you have Perl instaleld on your system.

-- Brett
http://www.chapelperilous.net/~bmccoy/
---------------------------------------------------------------------------
Money will say more in one moment than the most eloquent lover can in years.

--
Brice Ruth
WebProjkt, Inc.
VP, Director of Internet Technology
http://www.webprojkt.com/