[SQL] Intentional, or bug?
Hi! I probably found an inconsistence of the working of the = operator. Please read ahead, if you're interested. I have two tables, let't call them a and b. They are: a) x | y - 1 | 100 2 | NULL b) y | z - 1 | 'hallelujah' NULL | 'hmm...' Now let's issue this query: select b.z from a,b where a.y=b.y; The answer is: 'hallelujah', but it omits 'hmm...'. Now let's issue the following: select NULL = NULL; The answer is: true!!! My question is why does it omit the rows with NULL at the first case? However, I have a real life problem, when not all data is given in a column, so I need a query to include the NULL=NULL rows. I can workaround it as defining a new operator, ~=, with the meaning "both null, or =". I did it, it works, but very slow. So, is it an intentinal way of functioning, or it is bug somewhere? Thanks, Baldvin ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Correction for the previous letter:
Sorry, I made a mistake in my previous letter, the table b looks like this: b) y | z 100 (not 1) | 'Hallelujah' NULL| 'Hmm...' Bye, Baldvin ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Intentional, or bug?
On Sun, 16 Sep 2001, Kovacs Baldvin wrote: > select NULL = NULL; > > The answer is: true!!! This has got to be a bug. NULL is not equal to anything (nor is it unequal to anything). SQL uses tri-valued logic, where answers are either true, false, or unknown. When selecting where a = b, if either is null the row will not be selected. Likewise for a <> b. This is standard correct behaviour for SQL. One rationale is, if null represents an unknown value then how can you know if it's equal or not? It might in fact be equal but we don't know. > However, I have a real life problem, when not all data is given in > a column, so I need a query to include the NULL=NULL rows. I can > workaround it as defining a new operator, ~=, with the > meaning "both null, or =". I did it, it works, but very slow. If you want the rows where a = b and where a and b are both NULL, you must specify it precisely: where a = b or (a is null and b is null) > So, is it an intentinal way of functioning, or it is bug somewhere? This is how it is supposed to work. -- Tod McQuillin ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] SQL request change when upgrade from 7.0.2 to 7.1.3
Richard NAGY <[EMAIL PROTECTED]> writes: > SELECT aes.ent_id, e.type, e.nom, aes.sect_id as voulu, > cvd_new(current_date, e.date_entree, 'new') FROM ass_entrep_sectact aes, > entreprise e WHERE e.id = aes.ent_id and aes.sect_id <> 3 and > aes.sect_id <> 9 > and aes.sect_id <> 1 and aes.sect_id <> 13 and aes.sect_id = 56 > UNION > SELECT distinct on (aes.ent_id) aes.ent_id, e.type, e.nom, aes.sect_id, > cvd_new(current_date, e.date_entree, 'new') FROM ass_entrep_sectact aes, > entreprise e WHERE e.id = aes.ent_id and aes.sect_id <> 3 and > aes.sect_id <> 9 > and aes.sect_id <> 1 and aes.sect_id <> 13 and aes.sect_id <> 56 and > aes.ent_id > not in (SELECT ent_id FROM ass_entrep_sectact WHERE sect_id = 56 and > sect_id <> 3 > and sect_id <> 9 and sect_id <> 1 and sect_id <> 13) ORDER BY e.type, > e.nom ORDER BY applied to the result of a UNION has to be on the output columns of the UNION. "e.type" etc are names of input-table columns. Try just "type" and "nom", which will refer to the second and third output columns of the UNION. Pre-7.1 got this wrong (and would sometimes produce wrong output ordering or even a backend crash, if the arms of the UNION didn't all yield the same datatype). regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Intentional, or bug?
Tod McQuillin <[EMAIL PROTECTED]> writes: > On Sun, 16 Sep 2001, Kovacs Baldvin wrote: >> select NULL = NULL; >> >> The answer is: true!!! > This has got to be a bug. NULL is not equal to anything (nor is it > unequal to anything). Indeed, this *should* yield NULL. But right now, postgres' parser rewrites "= NULL" as "IS NULL" for compatibility with certain brain-dead Microsoft products. There have been several discussions on pghackers about undoing this bit of nonstandardness, but so far nothing's been changed. See the list archives for more detail than you really wanted. regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Intentional, or bug?
On Mon, 17 Sep 2001, Tod McQuillin wrote: > On Sun, 16 Sep 2001, Kovacs Baldvin wrote: > > > select NULL = NULL; > > > > The answer is: true!!! > > This has got to be a bug. NULL is not equal to anything (nor is it > unequal to anything). This is because of a hack due to a broken MS client that provided =NULL for IS NULL in some (possibly small) case. There's a grammar hack which makes the token sequence = NULL into IS NULL. There's been talk about making this optional or ripping it out entirely (yay), see past discussions on this topic for more detail. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] optimizing queries and indexes...
Tom, > [ Sorry for slow response, I've been out of town ] Taking a much-deserved vacation, hey? Any new job plans? > Postgres absolutely does not care: the optimizer will always consider > both A-join-B and B-join-A orders for every join it has to do. As > Stephan and Josh noted, you can constrain the join pairs the > optimizer > will consider if you use explicit-JOIN syntax --- but each pair will > be > considered in both directions. Fantastic! You may want to point out to unbelievers that MS SQL Server does not do this; if you fail to put your joins/where clauses in the *exact* order of the indecies in SQL Server, it ignores them and does a table scan. This is especially deadly because table scans are about 1/2 as fast in SQL Server as they are in Postgres. -Josh __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 565-7293 for law firms, small businessesfax 621-2533 and non-profit organizations. San Francisco ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Part 2 of "Intentional, or a bug"....
Hi again. First of all, tank you for the immediate and precise answers. My solution to the problem was the following. However, I found that to be quite slow. I would like to ask if one can suggest me how to optimize this, because I need this regularly. CREATE FUNCTION gyegyenlo(text, text) RETURNS boolean AS ' SELECT CASE WHEN $1 is null and $2 is null THEN true ELSE ($1) = ($2) END; ' LANGUAGE 'sql'; CREATE OPERATOR ~= ( leftarg = text, rightarg = text, procedure = gyegyenlo, commutator = ~= ); After these I use ~= if I need lines with NULLs in both to be selected. The only problem is that it makes the query very slow. Does anyone knows how to optimize this? Thanks, Baldvin ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Part 2 of "Intentional, or a bug"....
Kovacs Baldvin <[EMAIL PROTECTED]> writes: > After these I use ~= if I need lines with NULLs in both to be selected. Perhaps you should reconsider your data model. You seem to be treating NULL as if it were a real value, which is something that SQL discourages rather strongly... regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
