It would happen that the $folds variable would sometimes be an empty string, which 1.6.1 (and pervious versions) would interpret as:
SELECT `name`, `price` FROM (`folding_options`) WHERE `id_fold` = '' LIMIT 1
However, in 1.6.2, the query gets interpreted thusly
SELECT `name`, `price` FROM (`folding_options`) WHERE `id_fold` = LIMIT 1
Which, of course, throws an error. As I didn’t see this documented in the 1.6.2 Change Log, I’m going to classify this as bug as opposed to an undocumented “feature”.
Not sure this is a bug. AR can’t know that an empty variable means “”, users in the past wanted that to represent “IS NULL”. Instead, I think you’ll need to check $fold first, and assign it a value of “” if appropriate. In the another approach would be
I don’t feel this is a bug. AR can’t know that an empty variable means “”, users in the past wanted that to represent “IS NULL”. Instead, I think you’ll need to check $fold first, and assign it a value of “” if appropriate. In the another approach would be
Aww, I just submitted a bug report on this as well.
An empty WHERE statement should be ‘’, WHERE is considered a string by the Database engine and a value, even if empty, IS needed for valid syntax.
I propose the following code change, added after line 463:
else if($v === '') { $v = " ''"; }
There should be no escaping here, no need to escape nothing.
While I see where you guys are coming from, I’m still not 100% convinced this is not correct behaviour. I’m not absolutist in my position, but if AR is being passed nothing, then why should it not compare against nothing. Nothing is different then a blank string, but not the same as NULL.
I guess I just want to be sure that we’ve all thought this through so that we aren’t changing things again at a later date. I know what the old behaviour was, but that doesn’t necessarily make it the correct behaviour.
While I see where you guys are coming from, I’m still not 100% convinced this is not correct behaviour. I’m not absolutist in my position, but if AR is being passed nothing, then why should it not compare against nothing. Nothing is different then a blank string, but not the same as NULL.
I guess I just want to be sure that we’ve all thought this through so that we aren’t changing things again at a later date. I know what the old behaviour was, but that doesn’t necessarily make it the correct behaviour.
Hi Derek,
Either of the following results is acceptable
WHERE `field1`='' WHERE `field1` IS NULL
But the current result is
WHERE `field1`=
This is definately not correct, is it?
.
I think it will be much better, when
$db->where('field1'=>''); WHERE `field1`=''
$db->where('field1'=>null); WHERE `field1` IS NULL
Either way is fine, NULL or ‘’. Both mean the same thing to the DB.
I would HIGHLY recommend that it be ‘’, because people use the following a lot, myself included:
WHERE id != ''
It’s also just less code to make it ‘’ the way uoi currently have it coded. you dont need an extra 7 lines of code for an if() block to check for a negative or positive operator. The user already has to pass an operator and if one is not passed an = is used by default. Don’t complicate things any more then needed, use the existing code and just tack on an extra else at the end looking for an empty string.
else if($v === '') { $v = " ''"; }
The problem is, it is doing NEITHER at the moment and because of that there are syntax errors.
-Matt
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?
Umm, its throwing SQL errors that can’t be fixed(unless im missing something).
Derek Allard - 15 May 2008 09:27 PM
You are feeding it nothing, so it compares against nothing.
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
I would think that line of code would translate to:
WHERE id = ''
but it translates to
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
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.
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.
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
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
