[jifty-devel] Jifty::DBI bug (probably sqlite3 specific)

bart bunting bart at bunting.net.au
Thu Mar 23 19:11:07 EST 2006


Another bug :)

When using limit on collections it seems that the sql that Jifty::DBI
is generating (for sqlite anyway) is not correct in some cases.

Two limit clauses like the following:

  $products->limit(
		   column => 'price',
		   operator => '>=',
		   value => $PriceMin,
		   entry_aggregator => 'and',
		  );

  $products->limit(
		   column => 'price',
		   operator => '<=',
		   value => $PriceMax,
		   entry_aggregator => 'and',
		  );

generate the following sql:

SELECT main.* FROM products main WHERE ((lower(main.price) >= '76')and(lower(main.price) <= '100')) ORDER BY main.id ASC;

This does not work.

Removing the lower() functions seems to fix it. 

SELECT main.* FROM products main WHERE ((main.price >= '76')and(main.price <= '100')) ORDER BY main.id ASC;

It can be further narrowed to say that the statement:

SELECT main.* FROM products main WHERE ((lower(main.price) < '100')) ;

doesn't work but:
SELECT main.* FROM products main WHERE ((lower(main.price) > '10'));

does.


Hope this helps.

Bart


More information about the jifty-devel mailing list