AR bug

[eluser]BizComputing[/eluser]
Don't know if this is a bug, but here goes:

Have an active record select with a where clause built using where function, passing array of key/value pairs. Keys are either just field name or field name comparison. Anyhow, one of the values passed in is NULL. Where clause is being build as WHERE field_1 = 'value_1' AND field_2 <> AND field_3...

As you can see, field_2, the one with a NULL value is not represented in the WHERE. Since NULL is a legit value, I would assume that the active record would in the process of escaping and delimiting would see the NULL value and convert it to the NULL literal.

[eluser]Derek Allard[/eluser]
I've split this off. Could you provide some code please, and what the SQL you want to see outputted might look like?

[eluser]BizComputing[/eluser]
Highly sanitized code:

$this->db->select('field_3');
$this->db->where( array(
'field_1' => $my_obj->get_field('field_1'),
'field_2 <>' => $my_obj->get_field('field_2'),
'field_3' =>'value_3'
));
$result = $this->db->get('my_table');

$my_obj is a custom class with data, in this example, $my_obj->get_field('field_2') returns NULL which is a legit value.

here's the sanitized generated query:
SELECT `field_2` FROM (`my_table`) WHERE `field_1` = 'value_1' AND field_2 <> AND `field_3` = 'value_3'

As you can see, field_2 is generated without any value and therefore the query fails syntactically. I would have expected:

SELECT `field_2` FROM (`my_table`) WHERE `field_1` = 'value_1' AND field_2 <> NULL AND `field_3` = 'value_3'

As you can see by the generated query, I am using 1.6 refreshed from SVN yesterday afternoon. I am also noticing now that I am looking closer at the generated SQL that where keys that have both a field name and a condition are not getting backticked.

I am very happy with CI and extremely happy to see the current focus on a next version, keep up the great work!!!

[eluser]Derek Allard[/eluser]
Man AR get's complex. Why do you have to run such a hard query

OK, I need to look at this, could start a bug tracker entry for me?

[eluser]BizComputing[/eluser]
Sorry, have 24 years of experience coding, yet, I still get hit by the stupid stick.

If I were to get the syntax that I wanted, the query would run, but, I would not get the results I expect. mySql in the case of field_2 <> NULL in the where results in no data returned rather than returning all the rows where field_2 IS NOT NULL, so my bad. Don't know if there is a legit use of a NULL value being translated into the NULL literal. I will have to rethink my code.

Sorry for the false bug report!!!

Although, there is still the case where a key in a where function containing both a field name and a comparison results in the field name missing the backticks.

[eluser]BizComputing[/eluser]
Our posts crossed. After reading my oops, let me know if you still want a ticket on this, I suspect not.

Also, would you want a ticket on the backtick issue?

[eluser]Derek Allard[/eluser]
No, don't worry about it. Thanks for the update. I'll look into the backtick in my copious free time

[eluser]BizComputing[/eluser]
WOW, I actually found someone else with copious free time.