nullable string column and sort by this column results in rows with null omitted?!

[aprixon01112017]

It seems to me that a query where it is sorted by a nullable string column the rows that contain null in this column are ommited. Is that correct? Is that intended?

[freshp86]

Could you post a minimal repro? What is your query? What is the schema? This would help investigate further. At first glance, I don't remember if this is expected. Generally I would expect the behavior to match SQL (sqlite) behavior in this case.

[aprixon01112017]

I have an index on that column. that seems to have the effect of removing the rows with null:

`
// Begin schema creation.
var schemaBuilder = Lovefield.schema.create('crdb', 1);

var table = schemaBuilder.createTable('names').
addColumn('id', Lovefield.Type.STRING).
addColumn('name', Lovefield.Type.STRING).
addColumn('age', Lovefield.Type.INTEGER).
addPrimaryKey(['id']).
addNullable(['name', 'age']).
addIndex('idx_name', ['name'], false, Lovefield.Order.DESC);

// Schema is defined, now connect to the database instance.
schemaBuilder.connect().then(
function(db) {
console.debug('XXXXXXXXXXXXXXXXXXX 1');

  var names = db.getSchema().table('names');
  var row1 = names.createRow({
    'id': '1',
    'name': 'peter',
    'age' : 11
  });

  var row2 = names.createRow({
    'id': '2',
    'name': 'paul',
    'age' : -22
  });

  var row3 = names.createRow({
    'id': '3',
    'name': null,
    'age' : null
  });

  db.insertOrReplace().into(names).values([row1, row2, row3]).exec().then(
    function(rows) {
      console.log('XXXXXXXXXXXXXXXXXXX 2 ' + rows[0]['id']); // 'something'
      console.log('XXXXXXXXXXXXXXXXXXX 3 ' + rows[1]['id']); // 'something'
      console.log('XXXXXXXXXXXXXXXXXXX 4 ' + rows[2]['id']); // 'something'


      var q2 = db.select().
          from(names).
          orderBy(names.name, Lovefield.Order.DESC);

      q2.exec().then(function(rows) {
        console.log('XXXXXXXXXXXXXXXXXXX 5 ' + JSON.stringify(rows));
      });

    });
    
});`

[aprixon01112017]

I see:

Only not-null and indexable columns can be indexed. See Columns for details regarding which column data type are indexable.

https://github.com/google/lovefield/blob/master/docs/spec/01_schema.md#133-indices