It seems that that way the DB driver creates queries has changed - consider this AR query:

It would happen that the $folds variable would sometimes be an empty string, which 1.6.1 (and pervious versions) would interpret as:

However, in 1.6.2, the query gets interpreted thusly

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”.

stanleyxu already made a bug report for this problem.

Oops. Mods, feel free to delete or close this.

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

To continue this, I concede that the behaviour has changed, let’s reopen the discussion here on how to best handle this.

Derek Allard - 14 May 2008 06:57 AM

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

$this->db->select('name, price');
$this->db->where("id_fold = '$folds'");
$query = $this->db->get('folding_options', 1);

Just a quotation from my bug report:

Actually my code is:

In previous version, fields[1] would be treated as “‘’”.
But in this version the SQL-statement is:

Hi Derek, even it does not look like what you expected, does it?
Your expected result should be:

I proposed a code change:
Open system\database\DB_active_rec.php and change Line 457 to

[EOF]

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:

There should be no escaping here, no need to escape nothing.

-Matt

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.

Derek Allard - 15 May 2008 09:10 PM

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

But the current result is

This is definately not correct, is it?

.

I think it will be much better, when

.

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:

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.

The problem is, it is doing NEITHER at the moment and because of that there are syntax errors.
-Matt

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

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?

Derek Allard - 15 May 2008 09:27 PM

Nobody has convinced me this is a bug.

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

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

Take the following:

I would think that line of code would translate to:

but it translates to

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

Now can you see why its a bug?

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

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.

Here Derek, ill give you a php equivalent you will understand very quickly.

Try and run that.

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

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

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