statementBuilder with distinct().columns(...) return empty data ?

[hadi]

any idea about that ?

correct me if i'm wrong, but what i expect from table data and a call
like this :

========================================================
aColumn anotherColumn
========================================================
1 anyData
1 aData
2 anotherData


PreparedStmt <SimpleData> preparedStmt =
statementBuilder.columns("aColumn").distinct().prepareStatement();
dao.query(preparedStmt);

it will return 2 SimpleData object with all data set or at least it
will have SimpleData with prop "aColumn" is set.
But what i got are 2 SimpleData with empty data.

Source code :
what i did was changing example's code from HelloAndroid example
project. (Only HelloAndroid file change)

-------------------------------------------------
HelloAndroid-------------------------------------------

public class HelloAndroid extends OrmLiteBaseActivity<DatabaseHelper>
{

private final String LOG_TAG = getClass().getSimpleName();

static {
OpenHelperManager.setOpenHelperFactory(new SqliteOpenHelperFactory()
{
public OrmLiteSqliteOpenHelper getHelper(Context context) {
return new DatabaseHelper(context);
}
});
}

/**
* Called when the activity is first created.
*/
@Override
public void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
Log.i(LOG_TAG, "creating " + getClass());
TextView tv = new TextView(this);
doSampleDatabaseStuff("onCreate", tv);
setContentView(tv);

}

/**
* Do our sample database stuff.
*/
private void doSampleDatabaseStuff(String action, TextView tv) {
try {
// get our dao
Dao<SimpleData, Object> simpleDao = getHelper().getSimpleDataDao();
Log.i(HelloAndroid.class.getName(), "before query");

//
=================================changes===============================
// query for all of the data objects in the database
PreparedStmt<SimpleData> preparedStmt =
simpleDao.statementBuilder().columns("string").distinct().prepareStatement();
List<SimpleData> list = simpleDao.query(preparedStmt);
Log.i("ForTest", "Size Result : " + list.size());
for (SimpleData sd : list) {
Log.i("ForTest", "string is null " + (sd.string == null));
}
//=================================end of
changes===============================
tv.setText(list.size());
} catch (SQLException e) {
Log.e(LOG_TAG, "Database exception", e);
return;
}
}

-------------------------------------------------
SimpleData-------------------------------------------
public class SimpleData {

@DatabaseField(generatedId = true)
int id;
@DatabaseField
String string;
@DatabaseField
long millis;
@DatabaseField
Date date;
@DatabaseField
boolean even;

SimpleData() {
// needed by ormlite
}

public SimpleData(long millis) {
this.date = new Date(millis);
this.string = "millis = " + millis;
this.millis = millis;
this.even = ((millis % 2) == 0);
}

@Override
public String toString() {
StringBuilder sb = new StringBuilder();
sb.append("id=").append(id).append(", ");
sb.append("string=").append(string).append(", ");
sb.append("millis=").append(millis).append(", ");
sb.append("date=").append(date).append(", ");
sb.append("even=").append(even).append(", ");
return sb.toString();
}
}



-------------------------------------------------
DatabaseHelper-------------------------------------------
public class DatabaseHelper extends OrmLiteSqliteOpenHelper {

private static final String DATABASE_NAME = "test.db";
private static final int DATABASE_VERSION = 2;

private DatabaseType databaseType = new SqliteAndroidDatabaseType();
private Dao<SimpleData, Object> simpleDao = null;

public DatabaseHelper(Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);
}

@Override
public void onCreate(SQLiteDatabase db, ConnectionSource
connectionSource) {
try {
Log.i(DatabaseHelper.class.getName(), "onCreate");
TableUtils.createTable(databaseType, connectionSource,
SimpleData.class);
} catch (SQLException e) {
Log.e(DatabaseHelper.class.getName(), "Can't create database", e);
throw new RuntimeException(e);
}
}

@Override
public void onUpgrade(SQLiteDatabase db, ConnectionSource
connectionSource, int oldVersion, int newVersion) {
try {
Log.i(DatabaseHelper.class.getName(), "onUpgrade");
TableUtils.dropTable(databaseType, connectionSource,
SimpleData.class, true);
// after we drop the old databases, we create the new ones
onCreate(db);
} catch (SQLException e) {
Log.e(DatabaseHelper.class.getName(), "Can't drop databases", e);
throw new RuntimeException(e);
}
}

public Dao<SimpleData, Object> getSimpleDataDao() throws SQLException
{
if (simpleDao == null) {
simpleDao = BaseDaoImpl.createDao(databaseType,
getConnectionSource(), SimpleData.class);
}
return simpleDao;
}
}

[Gray]

On Sep 23, 5:02 am, hadi <idahirt...@gmail.com> wrote:
>
> correct me if i'm wrong, but what i expect from table data and a call like this :
>
> ========================================================
> aColumn anotherColumn
> ========================================================
> 1 anyData
> 1 aData
> 2 anotherData
>
> PreparedStmt <SimpleData> preparedStmt = statementBuilder.columns("aColumn").distinct().prepareStatement();
> dao.query(preparedStmt);

What you should be getting from this query is:

1 null
2 null

You should get distinct results and since you only requested aColumn,
that's the only one you should get -- anotherColum should be all
nulls.

In running your tests and some of my own, I found a fixed a bug with
how ORMLite works with Android's cursor. Turns out that when we do a
'select * from table' then the field-name is stored in the Android
SQLiteCursor as "aColumn". However, when we request a particular
field ("select `aColumn` from table") then the field is stored as
"`aColumn`" -- with the escape characters. This is a bug with their
cursor code IMO.

This has also exposed that my test coverage under Android is not
good. I've expanded it significantly so hopefully we will be better
about seeing these issues in the future.

This will be fixed in 3.4 which I'll try to get out in the next day or
so. Sorry for the problems.
gray

[Gray]

Here's the bug report: https://sourceforge.net/tracker/?func=detail&aid=3074249&group_id=297653&atid=1255989

And here's the http://ormlite.svn.sourceforge.net/viewvc/ormlite?view=revision&revision=900

In case anyone is playing along at home.
gray

[Gray Watson]

Interestingly enough it is the specific combination of DISTINCT and escaped fields. Here's the bug I submitted to Google:

http://code.google.com/p/android/issues/detail?id=11435

gray

[Kevin Galligan]

Weird.

[hadi]

sure.
in the mean time, i'm using raw sql select and ResultRaw.
Fortunately you provide that. :)

[Gray Watson]

On Sep 24, 2010, at 7:52 AM, hadi wrote:

>>>
> in the mean time, i'm using raw sql select and ResultRaw.
> Fortunately you provide that. :)

3.4 has been released. Let me know if that fixes your issue. You can also do just remove the columns() call. It's a bizarre combination of the columns() AND distinct(). You win the obscure bug award for sure!

gray

[hadi]

> 3.4 has been released.  Let me know if that fixes your issue.  

sorry for late response. yes, the updates fixed my case.
anyway, wow...
already version 4.0 ? :)
so many changes, deprecated fuctions.

[Gray Watson]

Yeah. Sorry for that. Much of the interface has stabilized but as I check off the last big TODO items, I still once and a while feel the need to reorg a bit.

Moving the DatabaseType to be internal was a big change. It should be better in the long-run although the port may be a bit painful in the short-term.

gray