[mythtv-users] UK DVB-T Duplicates usually on Dave, Sky3 etc... how to remove?

[Mike Holden]

Ian Clark wrote:
> 2008/6/6 Mike Holden <mythtv at mikeholden.org>:
>
>> Nick Morrott wrote:
>> <snip>
>>
>
>
>> ... In Oracle, an empty string is equivalent to
>> null.
>>
>
> Well, that's good to know!! (So, that's why people swear about Oracle).
>
>>
>> I don't know if mysql works this way as well, but it would explain the
>> issue we are seeing if the code is testing for equality between 2 empty
>> string values if it does work the same way as Oracle.
>
>
> It would appear this isn't true in MySQL.
>
> mysql> select count(*) FROM users WHERE ''='';
> +----------+
> | count(*) |
> +----------+
> |       17 |
> +----------+
> 1 row in set (0.00 sec)
>
> mysql> select count(*) FROM users WHERE NULL=NULL;
> +----------+
> | count(*) |
> +----------+
> |        0 |
> +----------+
> 1 row in set (0.00 sec)
>
> mysql> select count(*) FROM users WHERE NULL IS NULL;
> +----------+
> | count(*) |
> +----------+
> |       17 |
> +----------+
> 1 row in set (0.00 sec)
>
> mysql>
>
> Infact... that's really f'd up! It's almost as annoying as Sybase treating
> '
> ' and '' as the same thing. (although that's a setting which can be
> changed
> in later versions.)

That 2nd query shows a similar issue, where NULL != NULL !!!

If the string is stored as NULL rather than '', then this could be part of
the issue.

Not at a machine to check it out at the moment.

I over-simplified a bit about Oracle and strings, as there are actually
several different char types that handle nulls and trailing spaces
differently. VARCHAR2(n) is the most common, and this silently trims
trailing spaces and treats an empty string as NULL. There is also the
CHAR(n) type which stores trailing spaces rather than dropping them, and I
can't remember how it deals with empty strings offhand. Hardly anyone uses
CHAR type though!
-- 
Mike Holden

http://www.by-ang.com - the place to shop for all manner of hand crafted
items, including Jewellery, Greetings Cards and Gifts