Issue with manager.search(null, { limit: [5,10] });

[theRemix]

this is not an issue with postgrehx, i just don't know the process of updating haxe3's RecordMacros.hx (if this is even a proper fix) and if you run into this issue, maybe this will help you.

manager.search(null, { limit: [5,10] }); will create "SELECT * FROM table WHERE 1 LIMIT 5, 10"

where postgresql will complain ERROR: LIMIT #,# syntax is not supported HINT: Use separate LIMIT and OFFSET clauses.

and ERROR: argument of WHERE must be type boolean, not type integer

so i patched my std/sys/db/RecordMacros.hx

from line 1089:

            if( opt.limit != null ) {
                r.sql = inst.sqlAddString(r.sql, " LIMIT ");
                r.sql = inst.sqlAdd(r.sql, opt.limit.pos, pos);
                if( opt.limit.len != null ) {
                    r.sql = inst.sqlAddString(r.sql, " OFFSET ");
                    r.sql = inst.sqlAdd(r.sql, opt.limit.len, pos);
                }
            }

to:

            if( opt.limit != null ) {
                if( opt.limit.len != null ) {
                    r.sql = inst.sqlAddString(r.sql, " LIMIT ");
                    r.sql = inst.sqlAdd(r.sql, opt.limit.len, pos);
                    r.sql = inst.sqlAddString(r.sql, " OFFSET ");
                    r.sql = inst.sqlAdd(r.sql, opt.limit.pos, pos);
                }else{
                    r.sql = inst.sqlAddString(r.sql, " LIMIT ");
                    r.sql = inst.sqlAdd(r.sql, opt.limit.pos, pos);
                }
            }

and from line 755:

                switch( n ) {
                case "null":
                    return { sql : makeString("NULL", p), t : DNull, n : true };
                case "true":
                    return { sql : makeString("1", p), t : DBool, n : false };
                case "false":
                    return { sql : makeString("0", p), t : DBool, n : false };
                }

to:

                switch( n ) {
                case "null":
                    return { sql : makeString("NULL", p), t : DNull, n : true };
                case "true":
                    return { sql : makeString("TRUE", p), t : DBool, n : false };
                case "false":
                    return { sql : makeString("FALSE", p), t : DBool, n : false };
                }

which turns the sql to: SELECT * FROM table WHERE TRUE LIMIT 10 OFFSET 5; which works in postgresql and still works in mysql

[jdonaldson]

This is strange, because I would think 1/0 would work. http://www.postgresql.org/docs/8.1/static/datatype-boolean.html

[jdonaldson]

Did this ever get updated? I think I need an upstream fix from the compiler here, no?

[theRemix]

i just checked again to make sure this is still an issue. the only problem is the inconsistency between mysql and postgrehx.

posts = Post.manager.search( true, { limit: [5,10] } );
trace( posts.map(function(a){
  return a.title;
}) );

result

{Post #11, Post #12, Post #13, Post #14, Post #15}

where the api says

limit : specify which result range you want to obtain. You can use Haxe variables and expressions in limit values, for instance : { limit : [pos,length] }

you can leave this issue open until it's fixed on the RecordMacro.hx side

[jdonaldson]

Thanks, opened a bug on the compiler side.