Inexplicable QSqlQueryModel handling of NULL value

[JonB]

Qt 5.7. I am using QSqlQueryModel against a MySQL table. A column allows NULL, and has value NULL in it in the rows. However, when the model is populated I am finding that the row-column value is not NULL. Read on....

MySQL table looks like:

CREATE TABLE `landlords` (
    ...
    `SMTPAccountId` INT NULL
)

This means the column does allow NULL (and its default value is NULL). The table contains 20 rows. In all rows that column's value is NULL. All this verified in MySQL Workbench.

QSqlQueryModel's query looks like:

SELECT ..., SMTPAccountId
FROM `landlords`
WHERE SMTPAccountId IS NULL

so we are sure all rows returned do indeed have NULL in that column.

When I view in a QTableView, the rows' column value shows 0, not the "blank" I would expect.

When I use debugger to inspect self.modelLandlords.record(0).field("SMTPAccountId").value() it shows as {int} 0, so we know this is a model not a view issue. self.modelLandlords.record(0).field("SMTPAccountId").isNull() returns false.

At this point, I try changing the query to:

SELECT ..., NULL AS SMTPAccountId
FROM `landlords`
WHERE SMTPAccountId IS NULL

Now it does show as blank. However, isNull() still returns false, the difference is value() now returns {str} ''. So it's still wrong.

Delving down to the QSqlField level, requiredStatus() returns 0/false, so that implies that Qt does know that the column allows NULL. For defaultValue() it returns {NoneType} None, so it's not like that is causing the value, somehow, For type() it returns 2 in the first, INT case, and 10 in the second, string case. I use Python and don't have the C++ Qt source code, so I don't actually know what these type values are from their value.

So, please help!! All in all, why am I not getting NULL through ever??

[JonB]

@VRonin , and others,

Well, Hallelujah! After my doing much experimenting, and deep discussions with the author of PyQt, we seem to have identified that this is indeed a PyQt issue after all, and so (presumably) not a problem from C++.

My override now reads:

def data(self, index: QtCore.QModelIndex, role=QtCore.Qt.DisplayRole) -> typing.Any:

    import sip
    was_enabled = sip.enableautoconversion(QtCore.QVariant, False)
    value = super().data(index, role)
    sip.enableautoconversion(QtCore.QVariant, was_enabled)

    return value

This is all due to deep stuff from http://pyqt.sourceforge.net/Docs/PyQt5/pyqt_qvariant.html

v2 (the default for Python v3) does not expose the QVariant class to Python and automatically converts a QVariant to the actual value. While this is usually the best thing to do, it does raise problems of its own:

• There is no obvious way to represent a null QVariant as a standard Python object. [PyQt4 introduced the QPyNullVariant class to address this problem. Removed from PyQt5.]

The gist here, if I understand right, is that the base QSqlQueryModel.data() returns some kind of "null QVariant" when the value is a database's NULL. And then there is a different QVariant which is an "invalid" one, not the same as the "null" one.

But PyQt does not leave QVariants as-is like C++ does, it tries to be clever and convert them into the native Python type of whatever their value actually is. But in doing so it loses the distinction between the "null" QVariant and the "invalid" QVariant.

Somewhere/how in the process of executing that overridden function, this conversion is being performed. I think the base class's returned "null" QVariant gets turned into an "invalid" one, and then an invalid QVariant gets treated as 0 for an int or '' for a string, losing the original NULL completely, and leading to the behaviour I saw.

By wrapping the call to the base class method in a sip.enableautoconversion(QtCore.QVariant, False), I am telling PyQt not to do any conversion, and hence I restore the desired behaviour.

The PyQt author tells me this is "a very unusual case" and he thinks it "may be the only one" which exists where this is a problem. I worry about others, but have to take his word for now.

So there you are. 2 days of me head scratching (as well as @VRonin's time), and it turns out I wasn't going mad and it was a PyQt issue....

SUMMARY

My understanding is:

Using Python/PyQt:

Anywhere that a "null" QVariant has a specific meaning and is being passed as an argument to a virtual function [or returned from one].

you are likely to need to wrap the call to base function inside a sip.enableautoconversion(QtCore.QVariant, False) in order to maintain the distinction between a "null" QVariant (e.g. representing a database NULL) versus an "invalid" QVariant (e.g. representing "no value returned"), which are different objects in C++ but not in what Python usually converts them to. This is especially likely to occur in QtSql database modules, maybe not so much elsewhere.

FINAL EDIT

I have now realized there is a final, huge "gotcha" in this whole override approach:

• In order to make NULL values work, you must write the override to disable the autoconversion of QVariants. There is no choice, because of the fact that the Python override gets called by internal C++ code as a result of the base method being virtual.
• This means that the overridden data() method now must return a QVariant instead of the native Python type which is returned if no override is defined. Again, no choice.
• And that means that anywhere your Python code explicitly calls data() it now gets a QVariant. To restore result type to native to all callers, they must all use data().value() instead of plain data(). Calling code cannot be the same depending on whether an override is or is not defined!

And so finally, faced with having to change hundreds of existing calls in code to reflect this change and be incompatible with if the override were not defined, I have (regretfully) decided to abandon the whole approach of overriding QSqlTableModel.data() and manage without, as the effort/complications are just not worth it.

So beware: overriding a virtual function which returns, say, a QVariant from Python/PyQt is fraught... :(

FINAL, FINAL EDIT (REALLY)

I found that I had to override QSqlTableModel.data() after all, and so revisited this.

I have come up with a formulation which seems to work in all cases that I have tested. It works when the override is called internally by Qt C++ code. It works when the override is called explicitly from Python code, without requiring callers to be altered. The code checks for an "invalid" or "null" QVariant first, returning None in these cases, else it returns the QVariant.value(), the native Python value:

def data(self, index: QtCore.QModelIndex, role=QtCore.Qt.DisplayRole) -> typing.Any:

    import sip
    was_enabled = sip.enableautoconversion(QtCore.QVariant, False)
    val = super().data(index, role)
    sip.enableautoconversion(QtCore.QVariant, was_enabled)

    if not val.isValid():
        return None
    if val.isNull():
        return None

    return val.value

[VRonin]

VERY VERY strange.

can you run just a QSqlQuery manually and iterate through the results and check the type there? just trying to figure out if the problem is with QSqlQueryModel, the db driver or what else

@JonB said in Inexplicable QSqlQueryModel handling of NULL value:

I use Python and don't have the C++ Qt source code, so I don't actually know what these type values are from their value.

http://doc.qt.io/qt-5/qmetatype.html#Type-enum

[JonB]

@VRonin
Thanks for the qmetatype enum values link. So the original was showing that the field type was indeed QMetaType::Int or QMetaType::String appropriately depending on how the query was written. That's fine.

At least you confirm it's not expected behaviour!

So far my playing with a plain QSqlQuery on same query text is showing the column value does indeed have QSqlField::isNull() returning true, which is what it should be. So that implies the query and the driver are good to go.

Since I'm stuck at this point till it works, I'll go re-play with the model code.

But tell me one thing: in QSqlQueryModel case, am I right in assuming that Qt code does some work to set up the column types for the model (unlike a simple query)? Because if so, and if for whatever reason it thinks the column is an int and does not allow NULL, it might be that it will convert a NULL value received to 0 (or to '' in the string case), and that would explain the behaviour I am seeing... ?

EDIT1
Using plain, standalone QSqlQueryModel is getting the isNull() correct like QSqlQuery. However, in real code I use QTableView::setModel(QSqlQueryModel) to cause the model to populate. Is this where some snarling occurs...??

[VRonin]

@JonB said in Inexplicable QSqlQueryModel handling of NULL value:

Is this where some snarling occurs...??

That should not be the case. Do you have a custom delegate?

[JonB]

@VRonin
Well, it is the case!

Here's an outline of the debugging code:

model = QtSql.QSqlQueryModel(self)
model.setQuery("SELECT LandlordNo, SMTPAccountId  ORDER BY LandlordNo ASC")
rowCount = model.rowCount()
if rowCount > 0:
     rec = model.record(0)
     field = rec.field("SMTPAccountId")
     isv = field.isValid()
     isn = field.isNull()
     v = field.value()


self.modelLandlords = DBQueryModel("SELECT LandlordNo, SMTPAccountId ORDER BY LandlordNo ASC", self)
self.tabLandlords.setModel(self.modelLandlords)

model = self.modelLandlords
rowCount = model.rowCount()
if rowCount > 0:
     rec = model.record(0)
     field = rec.field("SMTPAccountId")
     isv = field.isValid()
     isn = field.isNull()
     v = field.value()

From inspection, the column value is NULL in the first, plain QSqlQueryModel case but is not NULL in the second case.

My DBQueryModel is derived from QSqlQueryModel. tabLandlords is derived from a QTableView.

I do notice I have overridden the DBQueryModel's data() method. But seemingly in a harmless fashion. (Also, the above code I showed is all just examining QSqlQueryModel::record(0), I don't know if that's going down to the QSqlQueryModel::data() level? Plus I would not expect the view to have any effect on the content of the model?) Effectively I have:

class DBQueryModel(QtSql.QSqlQueryModel)::
    def data(self, index: QtCore.QModelIndex, role=QtCore.Qt.DisplayRole) -> typing.Any:
        DBModelCommon.dataIndexIsValid(index)
        value = super().data(index, role)
        return DBModelCommon.dataValue(value, role)



class DBModelCommon:
    @staticmethod
    def dataIndexIsValid(index: QtCore.QModelIndex):
        if not index.isValid():
            errfunctions.dbLogger.error("dataValue: Invalid value")
            raise Exception("dataValue: Invalid value")

    @staticmethod
    def dataValue(value: typing.Any, role=QtCore.Qt.DisplayRole) -> typing.Any:
        # Next lines commented out:
        # Although QAbstractTableModel.data() returns None when invalid
        # there are times when this is called (at least by Qt internally) where it is OK to be None
        # if value is None:
        #     errfunctions.dbLogger.error("dataValue: Invalid value")
        #     raise Exception("dataValue: Invalid value")
        if role == QtCore.Qt.DisplayRole:
            if type(value) is float:
                return "£%.2f" % value
        return value

Do you have a custom delegate?

Please remind me where I should look for this?

I realise there is probably some link between behaviour and what I am doing to my model. But TBH I'm getting all very confused here --- I wrote the above code when I started Qt a while back, and remember I'm really a Qt noob :)

Thanks so much for your diagnostic time --- sorry to be a pain, I'm a bit lost as to what I'm doing!

[VRonin]

Ok, now my prime suspect is the model subclass. Just to confirm, can you replace self.tabLandlords.setModel(self.modelLandlords) with self.tabLandlords.setModel(model) and see if the problem goes away?

The delegate is the one that should do what you are wrongly doing in dataValue. Subclass QStyledItemeDelegate, reimplement displayText and just in there check what type is the data and format it in the string you like.
You set the delegate in the view by just calling QTableView::setItemDelegate

[JonB]

@VRonin
Well, yes, I'm guessing that will doubtless solve the issue :) The question is what/why? (I think the only method it has defined/overrides is that single data() one --- I wrote all this code so that all data access to MySQL goes through my own code, where I can check/debug/log.)

I'm going home now! Tomorrow, when I'm fresh, I will obviously try that. And assuming that's the case, I will do my own removing of each bit in the sub-class till I can narrow it down. Then I'll report back.

Thanks a lot --- please be kind enough to have a look at this tomorrow, in case I come with anything/get stuck!

EDIT:
Your:

The delegate is the one that should do what you are wrongly doing in dataValue. Subclass QStyledItemeDelegate, reimplement displayText and just in there check what type is the data and format it in the string you like.
You set the delegate in the view by just calling QTableView::setItemDelegate

I don't get this. First I don't know what I'm doing "wrong" in dataValue? Second, what's all this QStyledItemeDelegate, displayText etc.? That sounds all to do with displaying in a table view. I seem to be showing a bad value stored in the model? Hmm, I guess that's what QtCore.Qt.DisplayRole is all about. The problem here is that I did not start the code from scratch, I inherited 32K lines of code that was "working" from someone departed, so I am constrained to making sure things work as they did before. Some more reading/investigating for me to do....

[VRonin]

@JonB said in Inexplicable QSqlQueryModel handling of NULL value:

First I don't know what I'm doing "wrong" in dataValue?

    if type(value) is float:
                return "£%.2f" % value

You check if a value is a float and convert it to a string so it is formatted correctly when displayed. Conceptually this is wrong. The model should not care about the displaying of the data and even more so it should not change the data type to fit a display. One obvious example of a problem this carries is sorting. Floats will be sorted in alphabetical order as they are strings (so 10.0 will be sorted before 2.0).

If you want to control how the data is displayed in any view then the delegate is the correct place to do it

[JonB]

@VRonin

Right now I am about to start on finding out just what is making my stuff go wrong, thanks to your sub-classing suggestion, and will report back.

Going back to that piece of code in my dataValue override:

• I thought about it last night, and quite agree that type-changing in this function is not right.
• I did this a long time ago. It was inspired by trying to mimic behaviour I am used to in my C#/.NET code for data handling. But of course things are different, and I have made the wrong choice.
• The intention here is: because of MySQL/Python/Qt or whatever not having a "currency/money" type, in this application all floating point columns in this database represent £-money. What I am really trying to do is achieve "the string representation of these includes leading £ symbol". Now, this has nothing to do with it being displayed inside a table-view (styled delegate), it's just as much the case if in a line-edit, label, chart, message, whatever.... So to me this is an attribute of the data in the model, and it is having to code into the views which is "conceptually wrong". But of course I respect what you have indicated and will be changing my approach, whatever that involves.

Having said all of the above, it ought to be irrelevant to my "bad handling of NULL" issue here. You will note that unless a column is a float my dataValue() returns the value passed in quite unchanged. The column in question is not a float, and nor do any of the columns happen to be float in this table. So I am expecting some other issue to be the cause.

I will now do some code altering & debugging to see where I get, and will report back. Thanks again for your help!

[JonB]

@VRonin , or anyone

My dubious definition of dataValue() above is not the issue with the NULL problem. (However, I will be redefining that in due course.)

So far as I can tell, there is something strange going on with overriding QSqlQueryModel::data() method in a derived class.

According to my findings, by paring down my code, if I start with just:

class DBQueryModel(QtSql.QSqlQueryModel):
    def __init__(self, parent=None):
        super().__init__(parent)

all is well --- a NULL value from the database ends up as "blank".

However, as soon as I add just:

    def data(self, index: QtCore.QModelIndex, role=QtCore.Qt.DisplayRole) -> typing.Any:
        return super().data(index, role)

it goes wrong --- a NULL value comes back as 0 if the column is int or as '' if the column is string.

Now, I defined that override based on http://doc.qt.io/qt-5/qsqlquerymodel.html#data:

QVariant QSqlQueryModel::data(const QModelIndex &item, int role = Qt::DisplayRole) const

so I am claiming my override should simply "pass through" to the base definition. Yet it does not seem to be behaving like that.

I suppose it might be possible this is a Python/PyQt issue. I would not have thought so, but maybe. Note that Python does not have anything equivalent to the 2 occurrences of const in the C++ method definition, if that could matter.

Any idea what is wrong? Would someone care to try an equivalent in C++?

[VRonin]

@JonB said in Inexplicable QSqlQueryModel handling of NULL value:

Any idea what is wrong?

I'm 99.99999% sure this is due to typing.Any instead of QVariant. Can you use QVariant in python?

[JonB]

@VRonin
Unfortunately, this falls into your 0.00001% :)

Removing that makes no difference. In Python the -> ... is purely an annotation, which an IDE (like my PyCharm) can use at coding-time to help with code completion etc. It has nothing to do with any runtime at all. Python doesn't have typing like a responsible language.

We are not supposed to try to use QVariant in PyQt (I don't know if we can). Instead these are dealt with by the language/PyQt itself. I will investigate further.

If this turns out to be a PyQt issue only I will raise this with the appropriate authorities. If someone would be kind enough to see whether there is a problem in C++ or not I will know how to proceed.

STOP PRESS
I have looked down in the guts at the PyQt definition of QSqlQueryModel.data() and I see something alarming:

    def data(self, QModelIndex, role=None): # real signature unknown; restored from __doc__
        """ data(self, QModelIndex, role: int = Qt.DisplayRole) -> Any """
        pass

Now, notice that the C++ documentation and the comment in PyQt show role: int = Qt.DisplayRole. But the actual definition shows role=None. I'll bet we have a default-unspecified-role parameter mis-match here.... :(

EDIT:

I have now written to PyQt and received a response from the actual author guy. He states the function definition is not an issue (and I now understand why he is right), and that nothing in PyQt is at fault here.

Which leaves me stuck, completely. I have no idea what is the problem (there should not be a problem at all), nor how to proceed.

How can my one-line override which just calls the base class have this effect? Would anyone be kindly prepared to try it in C++? Any suggestions? Thanks.

[JonB]

@VRonin , and others,

Well, Hallelujah! After my doing much experimenting, and deep discussions with the author of PyQt, we seem to have identified that this is indeed a PyQt issue after all, and so (presumably) not a problem from C++.

My override now reads:

def data(self, index: QtCore.QModelIndex, role=QtCore.Qt.DisplayRole) -> typing.Any:

    import sip
    was_enabled = sip.enableautoconversion(QtCore.QVariant, False)
    value = super().data(index, role)
    sip.enableautoconversion(QtCore.QVariant, was_enabled)

    return value

This is all due to deep stuff from http://pyqt.sourceforge.net/Docs/PyQt5/pyqt_qvariant.html

v2 (the default for Python v3) does not expose the QVariant class to Python and automatically converts a QVariant to the actual value. While this is usually the best thing to do, it does raise problems of its own:

• There is no obvious way to represent a null QVariant as a standard Python object. [PyQt4 introduced the QPyNullVariant class to address this problem. Removed from PyQt5.]

The gist here, if I understand right, is that the base QSqlQueryModel.data() returns some kind of "null QVariant" when the value is a database's NULL. And then there is a different QVariant which is an "invalid" one, not the same as the "null" one.

But PyQt does not leave QVariants as-is like C++ does, it tries to be clever and convert them into the native Python type of whatever their value actually is. But in doing so it loses the distinction between the "null" QVariant and the "invalid" QVariant.

Somewhere/how in the process of executing that overridden function, this conversion is being performed. I think the base class's returned "null" QVariant gets turned into an "invalid" one, and then an invalid QVariant gets treated as 0 for an int or '' for a string, losing the original NULL completely, and leading to the behaviour I saw.

By wrapping the call to the base class method in a sip.enableautoconversion(QtCore.QVariant, False), I am telling PyQt not to do any conversion, and hence I restore the desired behaviour.

The PyQt author tells me this is "a very unusual case" and he thinks it "may be the only one" which exists where this is a problem. I worry about others, but have to take his word for now.

So there you are. 2 days of me head scratching (as well as @VRonin's time), and it turns out I wasn't going mad and it was a PyQt issue....

SUMMARY

My understanding is:

Using Python/PyQt:

Anywhere that a "null" QVariant has a specific meaning and is being passed as an argument to a virtual function [or returned from one].

you are likely to need to wrap the call to base function inside a sip.enableautoconversion(QtCore.QVariant, False) in order to maintain the distinction between a "null" QVariant (e.g. representing a database NULL) versus an "invalid" QVariant (e.g. representing "no value returned"), which are different objects in C++ but not in what Python usually converts them to. This is especially likely to occur in QtSql database modules, maybe not so much elsewhere.

FINAL EDIT

I have now realized there is a final, huge "gotcha" in this whole override approach:

• In order to make NULL values work, you must write the override to disable the autoconversion of QVariants. There is no choice, because of the fact that the Python override gets called by internal C++ code as a result of the base method being virtual.
• This means that the overridden data() method now must return a QVariant instead of the native Python type which is returned if no override is defined. Again, no choice.
• And that means that anywhere your Python code explicitly calls data() it now gets a QVariant. To restore result type to native to all callers, they must all use data().value() instead of plain data(). Calling code cannot be the same depending on whether an override is or is not defined!

And so finally, faced with having to change hundreds of existing calls in code to reflect this change and be incompatible with if the override were not defined, I have (regretfully) decided to abandon the whole approach of overriding QSqlTableModel.data() and manage without, as the effort/complications are just not worth it.

So beware: overriding a virtual function which returns, say, a QVariant from Python/PyQt is fraught... :(

FINAL, FINAL EDIT (REALLY)

I found that I had to override QSqlTableModel.data() after all, and so revisited this.

I have come up with a formulation which seems to work in all cases that I have tested. It works when the override is called internally by Qt C++ code. It works when the override is called explicitly from Python code, without requiring callers to be altered. The code checks for an "invalid" or "null" QVariant first, returning None in these cases, else it returns the QVariant.value(), the native Python value:

def data(self, index: QtCore.QModelIndex, role=QtCore.Qt.DisplayRole) -> typing.Any:

    import sip
    was_enabled = sip.enableautoconversion(QtCore.QVariant, False)
    val = super().data(index, role)
    sip.enableautoconversion(QtCore.QVariant, was_enabled)

    if not val.isValid():
        return None
    if val.isNull():
        return None

    return val.value

[dr_fumanchu]

@JonB said in Inexplicable QSqlQueryModel handling of NULL value:

The PyQt author tells me this is "a very unusual case" and he thinks it "may be the only one" which exists where this is a problem. I worry about others, but have to take his word for now.

I am a little bit astonished, that he said that. There are various situations where you have to distinguish between NULL and a ""/0 value, especially in case of ints.

• you want to generate INSERT statements for an import into another database
• you want to export a CSV file with a correct representation of database rows

I thought it was obvious that NULL and ''/0 are very much different (though Oracle treats empty strings as NULL, but never the other way around) and that a NULL value must never be converted to anything "NOT NULL", if not explicitly requested.

But I found a workaround by using query.record().isNull(i) that works for me:

query = QSqlQuery(self.db)
query.exec('SELECT NULL') # add "FROM DUAL" for databases which need it
    if query.isActive():
        if query.isSelect():
            while query.next():
                # QSqlRecord::isNull(int index) will behave correctly
                print(query.record().isNull(0))
                # QSqlQuery::value(int index) will return the (incorrectly) converted value
                print(query.value(0)) # is converted to int(0) or str('')
                ```