Strange problem with sql and mattn/go-sqlite3 driver

[EricR]

Hi! It seems that the driver sometimes does not return an error if a column is null and I scan for s string, but instead returns the column name. I have to explicitly use the following function to test for null.

func FieldIsNull(db *sql.DB, table string, item string, field string) bool {
    var result int
    err := db.QueryRow(fmt.Sprintf(`SELECT EXISTS (SELECT 1 FROM "%s" WHERE ? IS NULL and Id=?)`, table), field, item).Scan(&result)
    if err != nil {
        return false
    }
    return result > 0
}

The problem is, however, that I also sometimes have a custom table with no rows in it. Again, instead of returning an error, the driver or the sql package returns the column name instead of an error. The following test does not work:

func HasListFieldValue(db *sql.DB, table string, item string, field string) bool {
    var result int
    err := db.QueryRow(fmt.Sprintf(`SELECT EXISTS (SELECT 1 FROM "%s" WHERE "%s" IS NOT NULL AND Owner=?)`, table, field), item).Scan(&result)
    if err != nil {
        return false
    }
    return result > 0
}

(It's called a "ListField", because I'm abstracting from the database schema and allow "fields" with single values and those with list values, who are stored in a separate table.)

How can I check whether the rows returned are empty? When the table whose name is in tableName exists and has no rows, then the code below does not return an error but instead the string passed in variable "field"! How is this even possible? Am I overlooking something totally obvious?

Does an sql database driver return the column names first, before it returns any rows? That would explain it but would unusual and does not seem to have been  documented.

func getListField(db *sql.DB, table string, item string, field string) (string, error) {
    tableName := listFieldToTableName(table, field)
    if !TableExists(db, tableName) {
        return "", errors.New(fmt.Sprintf("list field %s does not exist in table %s", field, table))
    }
    if !FieldExists(db, tableName, field) {
        return "", errors.New(fmt.Sprintf("internal error, %s does not have a field %s", tableName, field))
    }
    todo not working
    if HasListFieldValue(db, tableName, item, field) {
        return "", errors.New(fmt.Sprintf("NULL list field %s of item %s in table %s", field, item, table))
    }
    rows, err := db.Query(fmt.Sprintf(`SELECT ? FROM "%s" WHERE Owner=?`, tableName), field, item)
    if err != nil {
        return "", errors.New(fmt.Sprintf("list field %s of item %s in table %s not found: %s", field, item, table, err))
    }
    results := make([]string, 0)
    for rows.Next() {
        var datum sql.NullString
        if err := rows.Scan(&datum); err != nil {
            rows.Close()
            return "", errors.New(fmt.Sprintf("cannot retrieve list field %s of item %s in table %s: %s", field, item, table, err))
        }
        if !datum.Valid {
            rows.Close()
            return "", errors.New(fmt.Sprintf("NULL for field %s of item %s in table %s", field, item, table))
        }
        results = append(results, datum.String)
    }
    rows.Close()
    if err := rows.Err(); err != nil {
        return "", errors.New(fmt.Sprintf("cannot retrieve list field %s of item %s in table %s: %s", field, item, table, err))
    }
    return strings.Join(results, "\n"), nil
}

[EricR]

Sorry, the uncommented "todo not working" was left in by me so it wouldn't compile. It won't work when I fix that either, of course.