DB_active_rec.php bug

[eluser]Sam Dark[/eluser]
Mironoff reported this one in russian community forum. Confirmed on 1.6.2.

Code:

$this->db->from('text');
$this->db->join('link2tree','link2tree.link_text = text.id','left');
$this->db->where('link2tree.link_text IS NULL');
$this->db->where('text.trash',0);
$this->db->order_by('sort');
$this->db->get();

Gives us:

Code:

Error Number: 1109

Unknown table '000_link2tree.link_text00_text' in on clause

SELECT * FROM (`000_text`) LEFT JOIN `000_link2tree` ON 000_link2tree.link_text00_text.id WHERE 000_link2tree.link_text IS NULL AND 000_text.trash = 0 ORDER BY `sort`

It's in DB_active_rec.php:

Code:

// If a DB prefix is used we might need to add it to the column names
if ($this->dbprefix)
{
    $this->_track_aliases($table);

    // First we remove any existing prefixes in the condition to avoid duplicates
    $cond = preg_replace('|('.$this->dbprefix.')([\w\.]+)([\W\s]+)|', "$2$3", $cond);

    // Next we add the prefixes to the condition
    $cond = preg_replace('|([\w\.]+)([\W\s]+)(.+)|', $this->dbprefix . "$1$2" . $this->dbprefix . "$3", $cond);
}

And when changing

Code:

// Next we add the prefixes to the condition
$cond = preg_replace('|([\w\.]+)([\W\s]+)(.+)|', $this->dbprefix . "$1$2" . $this->dbprefix . "$3", $cond);

to

Code:

// Next we add the prefixes to the condition
$cond = preg_replace('|([\w\.]+)([\W\s]+)|', $this->dbprefix . "$1$2", $cond);

everything works.

[eluser]Derek Allard[/eluser]
Hey Sam. Do you have '000_' set as your database prefix in config/database.php?

Running your query gives me

Code:

SELECT * FROM (`text`) LEFT JOIN `link2tree` ON link2tree.link_text = text.id WHERE link2tree.link_text IS NULL AND text.trash = 0 ORDER BY `sort`

[eluser]Seppo[/eluser]
Derek, you are right, they must have "000_" as db prefix, but there's still a bug using db prefix on the "on" condition

The expected condition is
000_link2tree.link_text = 000_text.id

and the one generated is
000_link2tree.link_text00_text.id

[eluser]Derek Allard[/eluser]
Well, that is indeed what Sam is reporting, but that isn't what I'm duplicating. Is this what you get if you run his query?

[eluser]Seppo[/eluser]
Exactly. Setting db_prefix as "000_" of course.

[eluser]Derek Allard[/eluser]
Ah, nice catch.

The regex is only looking for "word" characters, and so 000 is failing, but numeric table names are legal, so that's where things are falling down.

The code is on 344 of DB_active_rec.php. Sam, could you file a bug report and link to this thread? I want to think about the best possible way to solve it.

[eluser]Sam Dark[/eluser]
It's already filled: http://codeigniter.com/bug_tracker/bug/4587/

[eluser]Derek Allard[/eluser]
D'uh! Yeah sorry, I did see that. Thanks Sam.