Обсуждение: comparing rows
Can anyone explain the ERROR below? Is this a bug or a feature? This is 7.0.2. test=# select (1,2,3) = (1,2,3);?column? ----------t (1 row) test=# select (1,2,3) = (1,2,9);?column? ----------f (1 row) test=# select (1,2,3) = (1,2,null);?column? ---------- (1 row) test=# select (1,2,null) = (1,2,null); ERROR: Unable to identify an operator '=' for types 'unknown' and 'unknown'You will have to retype this query using an explicitcast -- Tatsuo Ishii
Tatsuo Ishii <t-ishii@sra.co.jp> writes: > test=# select (1,2,null) = (1,2,null); > ERROR: Unable to identify an operator '=' for types 'unknown' and 'unknown' > You will have to retype this query using an explicit cast Well, there's no basis for deciding what the datatype of the third column is. The only reason you don't get the same error from the non-row case regression=# select null = null;?column? ----------t (1 row) is that we have an ugly, horrible kluge in the parser to (mis) interpret "foo = null" as meaning "foo ISNULL", in order to be compatible with broken Microsoft SQL implementations. If you try any other operator you get regression=# select null <> null; ERROR: Unable to identify an operator '<>' for types 'unknown' and 'unknown' You will have to retype this query usingan explicit cast I'd certainly not vote to propagate the "= null" kluge into the row-equality code... regards, tom lane
> Well, there's no basis for deciding what the datatype of the third > column is. > > The only reason you don't get the same error from the non-row case > > regression=# select null = null; > ?column? > ---------- > t > (1 row) > > is that we have an ugly, horrible kluge in the parser to > (mis) interpret > "foo = null" as meaning "foo ISNULL", in order to be compatible with > broken Microsoft SQL implementations. If you try any other operator > you get Not compatible with the current release of MS SQL/MSDE :-) At least not fully. Isn't it just plain *wrong* to state that null = null? After all, NULL is unknown, and is not equal to anything, no? I just checked on MS SQL Server 7.0, and using the query "select CASE WHEN null=null THEN 'Yes' ELSE 'No' END" (just doing null=null produces a syntax error), it returns "No" (that is, null != null). However, if I turn *off* "ANSI nulls, paddings and warnings", it produces "Yes" (null=null). The default can be changed on a per-database basis. By default, any ODBC/OLEDB client turns *on* ANSI Nulls when it connects. Summary of MS: When it runs in ANSI mode, null != null. When it runs in backwards compatible mode , null=null. Perhaps it would be more correct for postgresql to provide an option for "non-ANSI null handling" as well? And leaving ANSI compliant handling as the default? //Magnus
> Not compatible with the current release of MS SQL/MSDE :-) At least not > fully. The original issue was that "foo = NULL" is *not* a legal SQL92 statement. But of course M$ garbage like Access (and presumably other products) generated "foo = NULL" in their automated ODBC queries, despite the fact that "foo IS NULL" is defined in the standard and would have been an obvious choice :( > Isn't it just plain *wrong* to state that null = null? After all, NULL is > unknown, and is not equal to anything, no? Right. But also wrong from an SQL92 compliance standpoint. afaict this is still true for SQL99. btw, it appears that SQL99 (haven't checked SQL92) specifies that test=# select (1,2,3) = (1,2,null);?column? ---------- (1 row) should return FALSE, not NULL. I haven't looked to see if this is consistant with other comparisons involving NULL. > I just checked on MS SQL Server 7.0, and using the query "select CASE WHEN > null=null THEN 'Yes' ELSE 'No' END" (just doing null=null produces a syntax > error), it returns "No" (that is, null != null). > However, if I turn *off* "ANSI nulls, paddings and warnings", it produces > "Yes" (null=null). The default can be changed on a per-database basis. By > default, any ODBC/OLEDB client turns *on* ANSI Nulls when it connects. > Summary of MS: > When it runs in ANSI mode, null != null. *sigh* If it actually *had* an ANSI mode, then "foo = NULL" would be rejected. Period. > When it runs in backwards compatible mode , null=null. > Perhaps it would be more correct for postgresql to provide an option for > "non-ANSI null handling" as well? And leaving ANSI compliant handling as the > default? afaict the option will be "M$" vs "published standards" support, and it seems the wrong way to head. Especially since M$ will try break any compliance we may achieve. Better to ask your friendly M$ supplier to support standards ;) - Thomas
Magnus Hagander <mha@sollentuna.net> writes: > Summary of MS: > When it runs in ANSI mode, null != null. > When it runs in backwards compatible mode , null=null. Oh, that's interesting, they fixed their bug. foo = NULL should produce NULL (*not* true, and *not* false either, though it'd act like false in a CASE test). Here is one vote for ripping out our parser kluge and making null act like it should according to the spec. Comments? regards, tom lane
At 01:44 PM 8/3/00 +0000, Thomas Lockhart wrote: >> Not compatible with the current release of MS SQL/MSDE :-) At least not >> fully. > >The original issue was that "foo = NULL" is *not* a legal SQL92 >statement. Ummm...I think this is wrong, and I have Date on my side (I just checked). foo = NULL is NULL, just like foo + NULL is NULL. If foo = NULL is illegal, doesn't this make it rather difficult to say things like table1.i = table2.i for tables that contain NULLs in column i? Oracle allows it, and returns NULL. Oracle's fairly trustworthy on low-level standards compliance over things like NULL (not entirely, though - it treats '' as NULL in the VALUES list of an INSERT, though not elsewhere, for instance). NULL = NULL is also perfectly legal, returning NULL. Before one gets too excited, NULL <> NULL and NULL < NULL etc all return NULL, too, along with NULL = <anything> so it's not terribly useful, which is why IS NULL exists. - Don Baccus, Portland OR <dhogaza@pacifier.com> Nature photos, on-line guides, Pacific Northwest Rare Bird Alert Serviceand other goodies at http://donb.photo.net.
At 10:15 AM 8/3/00 +0200, Magnus Hagander wrote: >Isn't it just plain *wrong* to state that null = null? After all, NULL is >unknown, and is not equal to anything, no? According to the standard, yes, it is wrong. >Perhaps it would be more correct for postgresql to provide an option for >"non-ANSI null handling" as well? And leaving ANSI compliant handling as the >default? I would think so. - Don Baccus, Portland OR <dhogaza@pacifier.com> Nature photos, on-line guides, Pacific Northwest Rare Bird Alert Serviceand other goodies at http://donb.photo.net.
> Magnus Hagander <mha@sollentuna.net> writes: > > Summary of MS: > > When it runs in ANSI mode, null != null. > > When it runs in backwards compatible mode , null=null. > > Oh, that's interesting, they fixed their bug. > > foo = NULL should produce NULL (*not* true, and *not* false either, > though it'd act like false in a CASE test). Ok. Hmm. I wonder how I check if it produces NULL :-) If I just do: SELECT null=null I get a syntax error. Is there some other way for me to check what it does? It appears that "null=null" isn't a valid expression (neither is "1=1" or something like that). Basically, it does not appear to be possible to SELECT the return value of a comparison at all - and CASE will apparantly not work in this case. I don't think it's capable of handling anything other than TRUE/FALSE as the result of a comparison. //Magnus
Thomas Lockhart <lockhart@alumni.caltech.edu> writes: > btw, it appears that SQL99 (haven't checked SQL92) specifies that > test=# select (1,2,3) = (1,2,null); > ?column? > ---------- > (1 row) > should return FALSE, not NULL. What? If so, they broke it pretty badly. This should be equivalent to 1 = 1 AND 2 = 2 AND 3 = NULL, which should reduce to TRUE AND TRUE AND NULL, which should reduce to NULL. Anything else is not self-consistent. >> Summary of MS: >> When it runs in ANSI mode, null != null. > *sigh* If it actually *had* an ANSI mode, then "foo = NULL" would be > rejected. Period. Well, mumble, that is an overly literal interpretation of the spec if you ask me. It is not unreasonable to allow NULL as a literal constant, especially since it doesn't create any issues that you can't get to with 100%-plain-vanilla-SQL92 constructs likeCASE WHEN TRUE THEN NULL END Where MS blew it was in not following SQL92-compatible semantics of operations on nulls. (We can't throw *too* many stones, since we had a number of problems with logical ops on nulls too, up till 7.0 ...) > afaict the option will be "M$" vs "published standards" support, and it > seems the wrong way to head. I don't want an option either. I want to change our code (back to) SQL compliant semantics of NULL comparisons, ie remove the parser kluge. regards, tom lane
> > Summary of MS: > > When it runs in ANSI mode, null != null. > > When it runs in backwards compatible mode , null=null. > > Oh, that's interesting, they fixed their bug. > > foo = NULL should produce NULL (*not* true, and *not* false either, > though it'd act like false in a CASE test). Hmm. I think I can answer my own question by using the dreaded manual. This is what Books On-Line for MS has to say: " The result of a comparison operator has the Boolean data type, which has three values: TRUE, FALSE, and UNKNOWN. Expressions that return a Boolean data type are known as Boolean expressions. Unlike other SQL Server data types, a Boolean data type cannot be specified as the data type of a table column or variable, and cannot be returned in a result set. When SET ANSI_NULLS is ON, an operator that has one or two NULL expressions returns UNKNOWN. When SET ANSI_NULLS is OFF, the same rules apply, except an equals operator returns TRUE if both expressions are NULL. For example, NULL = NULL returns TRUE if SET ANSI_NULLS is OFF. " So apparantly: a) I can't check it :-) b) It does return UNKNOWN (NULL?) for <anything>=NULL //Magnus
Don Baccus <dhogaza@pacifier.com> writes: > If foo = NULL is illegal, doesn't this make it rather difficult to > say things like table1.i = table2.i for tables that contain NULLs > in column i? Thomas is not saying that the *operation* is illegal. His point is purely a syntactic one: SQL92 allows the keyword "NULL" only in certain specified contexts, and out on its own as a component of an arithmetic expression ain't one of them. You could legally write the same thing in at least two ways, though: foo = (CASE WHEN TRUE THEN NULL END) foo = (CAST NULL AS somedatatype) It may be that the SQL92 authors intended this restriction to avoid having to figure out what datatype an unadorned NULL is. Unfortunately they blew it in the CASE case :-(, so you still have to have a way of deciding that. As far as I can see, allowing NULL as a general-purpose literal is a perfectly reasonable spec extension that *everybody* does, including us. Thomas may be the only person anywhere who is bothered by it ;-) regards, tom lane
> Magnus Hagander <mha@sollentuna.net> writes: > > Summary of MS: > > When it runs in ANSI mode, null != null. > > When it runs in backwards compatible mode , null=null. > > Oh, that's interesting, they fixed their bug. > > foo = NULL should produce NULL (*not* true, and *not* false either, > though it'd act like false in a CASE test). > > Here is one vote for ripping out our parser kluge and making null act > like it should according to the spec. Comments? I thought our only kludge was allowing = NULL because we got tons of MS folks complaining in the past before we did this. Are you thinking that the newer MS versions will not give us a problem. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
> Oracle allows it, and returns NULL. Oracle's fairly trustworthy > on low-level standards compliance over things like NULL (not > entirely, though - it treats '' as NULL in the VALUES list of > an INSERT, though not elsewhere, for instance). So you can't get a zero-length string into a column in Oracle. Interesting. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
Bruce Momjian <pgman@candle.pha.pa.us> writes: > I thought our only kludge was allowing = NULL because we got tons of MS > folks complaining in the past before we did this. Are you thinking that > the newer MS versions will not give us a problem. Well, I'm not sure. Magnus is saying that the newer MS servers default to spec-compliant semantics --- ie, foo = NULL will yield NULL. But IIRC the original complaints were because MS tools like Access would *generate* this expression and expect it to behave like foo IS NULL. Can MS have fixed all their apps already? Seems unlikely. Maybe we have to leave the kluge in there awhile longer. regards, tom lane
At 10:07 AM 8/3/00 -0400, Tom Lane wrote: >Thomas Lockhart <lockhart@alumni.caltech.edu> writes: >> btw, it appears that SQL99 (haven't checked SQL92) specifies that > >> test=# select (1,2,3) = (1,2,null); >> ?column? >> ---------- > >> (1 row) > >> should return FALSE, not NULL. > >What? If so, they broke it pretty badly. Date disagrees with Thomas on this one, too. On the other hand, select (2,2,3) = (1,2,null) would be false because 1 <> 2, therefore the result is not unknown. - Don Baccus, Portland OR <dhogaza@pacifier.com> Nature photos, on-line guides, Pacific Northwest Rare Bird Alert Serviceand other goodies at http://donb.photo.net.
At 10:23 AM 8/3/00 -0400, Tom Lane wrote: >Don Baccus <dhogaza@pacifier.com> writes: >> If foo = NULL is illegal, doesn't this make it rather difficult to >> say things like table1.i = table2.i for tables that contain NULLs >> in column i? > >Thomas is not saying that the *operation* is illegal. His point is >purely a syntactic one: SQL92 allows the keyword "NULL" only in certain >specified contexts, and out on its own as a component of an arithmetic >expression ain't one of them. I misunderstood, didn't check for the use of the literal, just the semantics of the operations. >As far as I can see, allowing NULL as a general-purpose literal is a >perfectly reasonable spec extension that *everybody* does, including us. >Thomas may be the only person anywhere who is bothered by it ;-) Thomas, do you have a reference into the standard? - Don Baccus, Portland OR <dhogaza@pacifier.com> Nature photos, on-line guides, Pacific Northwest Rare Bird Alert Serviceand other goodies at http://donb.photo.net.
At 10:22 AM 8/3/00 -0400, Bruce Momjian wrote: >> Oracle allows it, and returns NULL. Oracle's fairly trustworthy >> on low-level standards compliance over things like NULL (not >> entirely, though - it treats '' as NULL in the VALUES list of >> an INSERT, though not elsewhere, for instance). > >So you can't get a zero-length string into a column in Oracle. >Interesting. The killer is it makes porting from Oracle to Postgres or another DB that does it right a pain in the ass. I've got a lot of experience with this particular issue due to the OpenACS web toolkit (arsDigita's stuff ported from Oracle to Postgres). - Don Baccus, Portland OR <dhogaza@pacifier.com> Nature photos, on-line guides, Pacific Northwest Rare Bird Alert Serviceand other goodies at http://donb.photo.net.
On Thu, 3 Aug 2000, Tom Lane wrote: > Bruce Momjian <pgman@candle.pha.pa.us> writes: > > I thought our only kludge was allowing = NULL because we got tons of MS > > folks complaining in the past before we did this. Are you thinking that > > the newer MS versions will not give us a problem. > > Well, I'm not sure. Magnus is saying that the newer MS servers default > to spec-compliant semantics --- ie, foo = NULL will yield NULL. But > IIRC the original complaints were because MS tools like Access would > *generate* this expression and expect it to behave like foo IS NULL. > > Can MS have fixed all their apps already? Seems unlikely. Maybe we > have to leave the kluge in there awhile longer. I kind of agree with your original comment about removing the kludge, since the kludge does go against the spec ... ... a couple of thoughts to that effect: 1. this won't be 'in effect' until v7.1 comes out anyway 2. v7.0.2 will still have the kludge and will be available if ppl *really* need it, no? My vote is to go with Tom on this and remove the kludge ...
The Hermit Hacker wrote: > > On Thu, 3 Aug 2000, Tom Lane wrote: > > > Can MS have fixed all their apps already? Seems unlikely. Maybe we > > have to leave the kluge in there awhile longer. > > I kind of agree with your original comment about removing the kludge, > since the kludge does go against the spec ... > > ... a couple of thoughts to that effect: > > 1. this won't be 'in effect' until v7.1 comes out anyway > 2. v7.0.2 will still have the kludge and will be available if ppl *really* > need it, no? > > My vote is to go with Tom on this and remove the kludge ... Please don't. It seems true that Microsoft has enabled a mode for SQL Server, but the main problem was Access. And Access 95/97 has a huge installed base of users that would not be able to use its automated query tools with PostgreSQL. If the interfaces' list is any indication, most people use a architecture like: PostgreSQL Server <--------- Access Front-End | | V Web Server I hate kludges. But I'd prefer compatibility with Microsoft tools. Just my opinion, Mike Mascari
Mike Mascari <mascarm@mascari.com> writes: > Please don't. It seems true that Microsoft has enabled a mode for > SQL Server, but the main problem was Access. And Access 95/97 has > a huge installed base of users that would not be able to use its > automated query tools with PostgreSQL. That was what I was afraid of :-(. Question though: if MS has changed the default behavior of their server to be (more) SQL-compliant, aren't these folks being forced to update their Access installs anyway? Presumably those old versions do not know how to select the non-default behavior of SQL Server, so they're gonna be incompatible with newer servers despite the nominal presence of a workaround. I've got no objection to leaving in the kluge for another release or two if there's still a big installed base that needs it --- but I don't want to leave it there indefinitely for the benefit of a few tail-end Charlies. Seems like folks running obsolete Access code are unlikely to pick up the latest Postgres either, so will it really matter if we change? regards, tom lane
On Thu, 3 Aug 2000, Tom Lane wrote: > Mike Mascari <mascarm@mascari.com> writes: > > Please don't. It seems true that Microsoft has enabled a mode for > > SQL Server, but the main problem was Access. And Access 95/97 has > > a huge installed base of users that would not be able to use its > > automated query tools with PostgreSQL. > > That was what I was afraid of :-(. Question though: if MS has changed > the default behavior of their server to be (more) SQL-compliant, aren't > these folks being forced to update their Access installs anyway? > Presumably those old versions do not know how to select the non-default > behavior of SQL Server, so they're gonna be incompatible with newer > servers despite the nominal presence of a workaround. > > I've got no objection to leaving in the kluge for another release or two > if there's still a big installed base that needs it --- but I don't want > to leave it there indefinitely for the benefit of a few tail-end > Charlies. Seems like folks running obsolete Access code are unlikely > to pick up the latest Postgres either, so will it really matter if we > change? that was my opinion ... if these ppl are already using it for talking to PostgreSQL., they have to be running a current release of it with the kludge inplace ... we're only preventing PostgreSQL admin from being able to upgrade while their clients are still stuck with the older Access ...
> btw, it appears that SQL99 (haven't checked SQL92) specifies that > > test=# select (1,2,3) = (1,2,null); > ?column? > ---------- > > (1 row) > > should return FALSE, not NULL. I haven't looked to see if this is > consistant with other comparisons involving NULL. This is interesting. If "(1,2,3) = (1,2,null)" is legal from the point of view of the standard, isn't "(1,2,null) = (1,2,null)" legal comparison too? no? BTW, I would like to object the "null = null" support in PostgreSQL. It is against the standard. That's enough reason to reject the syntax. -- Tatsuo Ishii
> > btw, it appears that SQL99 (haven't checked SQL92) specifies that > > test=# select (1,2,3) = (1,2,null); > > ?column? > > ---------- > > > (1 row) > > > should return FALSE, not NULL. > What? If so, they broke it pretty badly. This should be equivalent to > 1 = 1 AND 2 = 2 AND 3 = NULL, which should reduce to TRUE AND TRUE AND NULL, > which should reduce to NULL. Anything else is not self-consistent. Hmm. I could have sworn I looked this up (and was suprised at the result). But I'm not finding the example anywhere, and Section 8.2 General Rule 1 seems to indicate that we do the right thing here already. Also, I *think* we have settled on the following facts: 1) "3 = NULL" is typical of an expression generated by M$ Access. 2) "3 = NULL" is *not* legal SQL9x syntax, which specifies "3 IS NULL" for the comparison "does three have a value of NULL?". 3) New versions of M$ Access continue to generate bogus queries containing these comparisons. 4) Postgres will continue to understand (at least) the special case of "column/value = NULL" to retain compatibility with M$. Oh, and 5) Thomas will continue to complain about M$ for shipping products with gratuitous deviations from published standards. ;) - Thomas
> Mike Mascari <mascarm@mascari.com> writes: > > Please don't. It seems true that Microsoft has enabled a mode for > > SQL Server, but the main problem was Access. And Access 95/97 has > > a huge installed base of users that would not be able to use its > > automated query tools with PostgreSQL. > > That was what I was afraid of :-(. Question though: if MS has changed > the default behavior of their server to be (more) > SQL-compliant, aren't > these folks being forced to update their Access installs anyway? > Presumably those old versions do not know how to select the > non-default > behavior of SQL Server, so they're gonna be incompatible with newer > servers despite the nominal presence of a workaround. Can somebody enlighten me as to *when* Access creates a query that has "=null" in it? I just tried to reproduce it, and I failed. I type Access 97SR2 Access 2000SR1 ------ ------------ -------------- =NULL Is Null =Null IS NULL Is Null Is Null <>NULL Is Not Null Is Not Null IS NOT NULL Is Not Null Is Not Null Seems to me that Access 2000 *allows* you to write =Null, but if you write "Is Null" (correct), it will leave it alone. And <>NULL is always converted to Is Not Null. I don't have any Access 95 around - but it's so old that hardly anybody should be using it anymore, right? :-) Problem is that it's too easy to change your MS SQL Installation to work in "the old way". Simply have the clients click in a box when they create the ODBC datasource. The downside of backwards compatibilty is not forcing people to fix their stuff :-) //Magnus
Magnus Hagander wrote: > > Can somebody enlighten me as to *when* Access creates a query that has > "=null" in it? > > I just tried to reproduce it, and I failed. > The problem appears (just from a quick look at the mail archive) to manifest itself when Access generates queries to handle Forms processing. Below is a sample issue that people had when using Access 97 w/PostgreSQL: ------------------------------------------------------ From: mmw@paradise.net.nz To: pgsql-interfaces@postgresql.org Subject: Access ODBC worries Date: Thu, 12 Aug 1999 16:44:41 +1200 (NZST) Hi All I have been battling with a problem now for days. I have a linked form in Access 97 to linked tables to a PGSQL backend via ODBC. The code works well, but after the form has been on the screen for about 20 minutes idling or doing something (makes no difference) then all hell breaks loose. The computer tries to requery the form data from the backend in a very strange way. The odbc log file output at the bottom of this message explains the problem in greater detail. ... <ODBC log follows> ... ------------------------------------------------------------ hdbc=91652364, stmt=91555964, result=0 manual_result=0, prepare=0, internal=0 bindings=0, bindings_allocated=0 parameters=92605260, parameters_allocated=1 statement_type=0, statement='SELECT "Toplevel time recordal"."oid" FROM "Toplevel time recordal" WHERE ("File link" = ? ) ORDER BY "Startdate" DESC ' stmt_with_params='SELECT "Toplevel time recordal"."oid" FROM "Toplevel time recordal" WHERE ("File link" = NULL ) ORDER BY "Startdate" DESC ' data_at_exec=-1, current_exec_param=-1, put_data=0 currTuple=-1, current_col=-1, lobj_fd=-1 maxRows=0, rowset_size=1, keyset_size=0, cursor_type=0, scroll_concurrency=1 cursor_name='SQL_CUR0575087C' ----------------QResult Info ------------------------------- CONN ERROR: func=SC_execute, desc='', errnum=110, errmsg='ERROR: parser: parse error at or near "null"' ------------------------------------------------------ I'll attempt to provide an easily repeatable operation, but the problem *does* exist in Access 97. Mike Mascari
On Fri, 4 Aug 2000, Thomas Lockhart wrote: > > > btw, it appears that SQL99 (haven't checked SQL92) specifies that > > > test=# select (1,2,3) = (1,2,null); > > > ?column? > > > ---------- > > > > > (1 row) > > > > > should return FALSE, not NULL. > > What? If so, they broke it pretty badly. This should be equivalent to > > 1 = 1 AND 2 = 2 AND 3 = NULL, which should reduce to TRUE AND TRUE AND NULL, > > which should reduce to NULL. Anything else is not self-consistent. > > Hmm. I could have sworn I looked this up (and was suprised at the > result). But I'm not finding the example anywhere, and Section 8.2 > General Rule 1 seems to indicate that we do the right thing here > already. > > Also, I *think* we have settled on the following facts: > > 1) "3 = NULL" is typical of an expression generated by M$ Access. > > 2) "3 = NULL" is *not* legal SQL9x syntax, which specifies "3 IS NULL" > for the comparison "does three have a value of NULL?". > > 3) New versions of M$ Access continue to generate bogus queries > containing these comparisons. > > 4) Postgres will continue to understand (at least) the special case of > "column/value = NULL" to retain compatibility with M$. Stupid question here ... but ... can't this kludge be "faked" in the ODBC driver itself, vs in the server? *raised eyebrow*
> Stupid question here ... but ... can't this kludge be "faked" in the ODBC > driver itself, vs in the server? *raised eyebrow* Right now, the ODBC driver does not do full parsing of the input queries, so imho it would be difficult to reliably identify the correct string substitution. - Thomas
On Fri, 4 Aug 2000, Thomas Lockhart wrote: > > Stupid question here ... but ... can't this kludge be "faked" in the ODBC > > driver itself, vs in the server? *raised eyebrow* > > Right now, the ODBC driver does not do full parsing of the input > queries, so imho it would be difficult to reliably identify the correct > string substitution. Ah, there went that idea ... to bad we couldn't put some sort of check in the server "if through odbc driver, allow this kludge" :) You say 'right now' ... is full parsing something that the ODBC driver should be done?
On Fri, Aug 04, 2000 at 12:41:10PM -0300, The Hermit Hacker wrote: > On Fri, 4 Aug 2000, Thomas Lockhart wrote: > > > > Stupid question here ... but ... can't this kludge be "faked" in the ODBC > > > driver itself, vs in the server? *raised eyebrow* > > > > Right now, the ODBC driver does not do full parsing of the input > > queries, so imho it would be difficult to reliably identify the correct > > string substitution. > > Ah, there went that idea ... to bad we couldn't put some sort of check in > the server "if through odbc driver, allow this kludge" :) Could the ODBC driver have a little checkbox that causes it to magically issue a "SET BROKEN_MICROSOFT_NULL" immediately after it connects? -- Christopher Masto Senior Network Monkey NetMonger Communications chris@netmonger.net info@netmonger.net http://www.netmonger.net Free yourself, free your machine, free the daemon -- http://www.freebsd.org/
Thomas Lockhart <lockhart@alumni.caltech.edu> writes: >> Stupid question here ... but ... can't this kludge be "faked" in the ODBC >> driver itself, vs in the server? *raised eyebrow* > Right now, the ODBC driver does not do full parsing of the input > queries, so imho it would be difficult to reliably identify the correct > string substitution. However, the ODBC driver's parsing is desperately inadequate anyway (it fails to handle nested function calls properly). It might well be that fixing that will entail doing enough work that "= NULL" could be recognized without much more work. On the third hand, pushing the kluge out to the ODBC driver doesn't make it any less a kluge... regards, tom lane