1.6.2 DB bug - empty strings not recognized in AR

[eluser]stanleyxu[/eluser]
BTW: I suggest Dev team to create some unit tests to verify the basic functionalities. It is too risky to release a buggy version

[eluser]Derek Allard[/eluser]
Nobody has convinced me this is a bug.

I'm not saying this is working exactly as it should, but what I want is for someone to make an argument for a particular behaviour. I've stated my case several times now. You are feeding it nothing, so it compares against nothing. I'm not saying this is exactly as it should be, but it is "right" from the sense of AR not making undue assumptions for you.

StanlyXu, I would greatly appreciate your help in writing up these unit tests to verify basic functionalities. Is this something you'd be willing to help with?

[eluser]mglinski[/eluser]
[quote author="Derek Allard" date="1210922829"]Nobody has convinced me this is a bug.[/quote]
Umm, its throwing SQL errors that can't be fixed(unless im missing something).

[quote author="Derek Allard" date="1210922829"]You are feeding it nothing, so it compares against nothing.[/quote]
Wrong. MySQL does not accpet whitespace as "nothing". Then there would be mass havoc with people writing very poor queries. MySQL sees "nothing" as '' or NULL

---------------------------------------------------------

Take the following:

Code:

$this->db->where('id','');

I would think that line of code would translate to:

Code:

WHERE id = ''

but it translates to

Code:

WHERE id =

That in itself is a violation of SQL syntax. Meaning its a bug.
There needs to be something for mysql to compare against. When you submit blank space it goes "wtf?". just because you can use a php variable like $php; dosent mean it works the same way in MySQL. For this useage there MUST be either a NULL/NOT NULL or a '' to make it valid syntax.

Ill give you another example.
Add an order_by('id', 'asc') to it

Code:

WHERE id = ORDER BY `id` ASC

Now can you see why its a bug?

If you want i can help with the unit tests.
-Matt

[eluser]Derek Allard[/eluser]
Ok, let's not get hung up on the word "bug". I agree this is not desirable behaviour. I want to work with you guys to come up with the best possible solution. Again though, I don't want to blindly change it to something so that a database stops throwing errors, if that solution isn't the best possible solution.

So far, the 2 candidates are drop in an empty string ("") or go to IS NULL. I won't have any more feedback on this one until I've had a chance to think it through more carefully, or until someone comes up with a rationale to help sway us one way or the next, or propose an alternative.

[eluser]mglinski[/eluser]
Here Derek, ill give you a php equivalent you will understand very quickly.

Code:

if($id == ){echo 'my bad';}

Try and run that.

Ill look in the MySQL documentation for ya and find out what they say.
-Matt

[eluser]mglinski[/eluser]
From the horse's mouth:

The WHERE clause, if given, indicates the condition or conditions that rows must satisfy to be selected. where_condition is an expression that evaluates to true for each row to be selected. The statement selects all rows if there is no WHERE clause.

In the WHERE clause, you can use any of the functions and operators that MySQL supports, except for aggregate (summary) functions. See Chapter 11, Functions and Operators.

http://dev.mysql.com/doc/refman/5.0/en/select.html

There is no "right" way to do it from what i see.
Like we said before, either way is correct, it's more about personal style.
People who use NULL instead of '' usually use logical(AND) operators in their SQL statments, VS their bitwise(&) counterparts.

The way AR is currently setup, using bitwise operators

Code:

WHERE `id` = ''

seems to be the solution which maintains coding style.

NULL != '', that is true.
But people very rarely even know what NULL truely means so they assume it = ''. As such MySQL does the same thing.

Sorry that I personally seem to be really pushing this, but when i updated to 1.6.2 i could no longer find unpaid orders in my website. I fixed it for myself and have posted a fix, like many others, but this is a semi-serious bug that needs to get fixed. It doesn't affect everyone, but 3 separate people filed bug reports on it meaning its getting noticed.
-Matt

[eluser]GDias[/eluser]
Any updates on this?

[eluser]stanleyxu[/eluser]
[quote author="Derek Allard" date="1210923665"]So far, the 2 candidates are drop in an empty string ("") or go to IS NULL. I won't have any more feedback on this one until I've had a chance to think it through more carefully, or until someone comes up with a rationale to help sway us one way or the next, or propose an alternative.[/quote]

Code:

$db->where(array('field'=>'')); // WHERE `field`=''
$db->where(array('field'=>null)); // WHERE `field` IS NULL

Are we clear?

I have attached code to make this happen. Please check bug tracker or somewhere in this post.

[eluser]Newton Wagner[/eluser]
Ok!! I agree with people on saying that is a bug!! But whatever. Going to the next step and trying to discuss what should be better to implement to solve that: '' or IS NULL, why not both? .

It can be done checking the variable type:

if ($var === NULL) { // isnull... } elseif (empty($var)) { //field=''... } else { ... }

[eluser]stanleyxu[/eluser]
[quote author="Newton Wagner" date="1211525701"]Ok!! I agree with people on saying that is a bug!! But whatever. Going to the next step and trying to discuss what should be better to implement to solve that: '' or IS NULL, why not both? .

It can be done checking the variable type:

if ($var === NULL) { // isnull... } elseif (empty($var)) { //field=''... } else { ... }[/quote]
Of course it can. Check the implementation of $this->escape().
That is why I say it can be fixed very quick.