orderBy not working

[Blake McBride]

Greetings,

I am using an orderBy on a joined table without an explicit index.  It is not sorting correctly.  It seems to have groups of records that are sorted, but the records are randomly interspersed with goups and random records unsorted.

Here is part of my schema definition:

....createTable('client_item_inventory')  // inv
    .addColumn('client_item_inventory_id', lf.Type.STRING)  // uuid
    .addColumn('quantity_on_hand', lf.Type.INTEGER)
    .addColumn('usable_quantity_on_hand', lf.Type.INTEGER)
    .addColumn('location', lf.Type.STRING)
    .addColumn('project_id', lf.Type.STRING)
    .addColumn('item_category', lf.Type.STRING)
    .addColumn('description', lf.Type.STRING)
    .addColumn('model', lf.Type.STRING)
    .addColumn('vendor', lf.Type.STRING)
    .addColumn('vendor_item_code', lf.Type.STRING)
    .addColumn('client_item_code', lf.Type.STRING)
    .addColumn('unit_of_measure', lf.Type.STRING)
    .addColumn('record_status', lf.Type.STRING)  // (I)mported, (M)odified, (D)eleted, (N)ew
    .addNullable(['vendor', 'model', 'vendor_item_code', 'client_item_code', 'location'])
    .addPrimaryKey(['client_item_inventory_id'])
    .addIndex('client_item_inventory_project_idx', ['project_id'], false, lf.Order.ASC)
    .addForeignKey('client_item_inventory_project_fk', {
        local: 'project_id',
        ref: 'project.project_id'
    });

....createTable('project_po_detail')  // podet
    .addColumn('project_po_detail_id', lf.Type.STRING)  // uuid
    .addColumn('project_po_id', lf.Type.STRING)
    .addColumn('po_line_number', lf.Type.INTEGER)
    .addColumn('client_item_inventory_id', lf.Type.STRING)
    .addColumn('quantity_ordered', lf.Type.INTEGER)
    .addColumn('price_each', lf.Type.NUMBER)
    .addColumn('when_due_date', lf.Type.INTEGER)
    .addColumn('when_due_time', lf.Type.INTEGER)
    .addColumn('record_status', lf.Type.STRING)  // (I)mported, (M)odified, (D)eleted, (N)ew
    .addNullable(['when_due_date', 'when_due_time'])
    .addPrimaryKey(['project_po_detail_id'])
    .addIndex('project_po_detail_po_idx', ['project_po_id'], false, lf.Order.ASC)
    .addIndex('project_po_detail_inventory_idx', ['client_item_inventory_id'], false, lf.Order.ASC)
    .addForeignKey('project_po_detail_po_fkey', {
        local: 'project_po_id',
        ref: 'project_purchase_order.project_po_id'
    })
    .addForeignKey('project_po_detail_inventory_fk', {
        local: 'client_item_inventory_id',
        ref: 'client_item_inventory.client_item_inventory_id'
    });

And here is the query:

let recs = await db.select().from(podet)
    .innerJoin(inv, podet.client_item_inventory_id.eq(inv.client_item_inventory_id))
    .orderBy(inv.client_item_code, lf.Order.ASC)
    .exec();

Like I say, it returns fine with all the right data, it's just not sorted completely correctly.

I assumed that if I didn't have an index it would just sort.  Would adding an index correct the problem?  Is an index required?

Thanks!

Blake McBride

[Blake McBride]

I added indexes and it didn't help.  I also verified that there are no random characters in the fields I am attempting to sort by.

[Blake McBride]

Found the problem.  Typo on my part.  Sorry.

[dpa...@chromium.org]

No problem. My guess was going to be that you were passing strings where numbers were expeced (or vice versa). Glad it was not a Lovefield issue.