Under what conditions can orders::date_purchased be NULL?

[lat9]

Looking at the orders-table schema, the date_purchased field is defined as

date_purchased datetime default NULL

Under what circumstances can that field be NULL?

[swguy]

It's set that way because there's no good default value. Look at all the trouble we have had with 0000-00-00 and then 0001-01-01.

[lat9]

That's not my question, my question is "Under what conditions can that field be NULL" (misspelled in the post's initial title).

From inspecting the order-class, that's "always" set to now(), but I believe I've found a site where the date on some orders is NULL.

[DrByte]

Here's the technical answer:

Just like any field's "default value", it will have that "default value" whenever no value is explicitly set.
Therefore it will happen with any insert/update query on that table's records where the field is not part of the query (eg: not mentioned in the field-value pairs the query is inserting/updating).

It also includes records that were present before the field was added to the table.

And of course, if a query explicitly sets it to that value it will have that value.



As for this specific field in this specific table, you're right: it should always be set ... at least for data inserted since the table was created and that code added to the order class.
Is there some other code that creates/injects orders into this database? Updates happening to the data where the code might list the field but pass non-existing data that gets treated (correctly, albeit perhaps unexpectedly) as null?

[lat9]

FWIW, I'm investigating an issue reported on the SNAP Affiliates support-thread.

The SNAP-admin code currently determines the first-order-date for the given store via the following query:

$first_order_date = $db->Execute(
"SELECT date_purchased
FROM " . TABLE_ORDERS . "
ORDER BY date_purchased ASC
LIMIT 1"
);

If one or more of the store's orders has a NULL date_purchased, the query returns no records. If I change the query to:

$first_order_date = $db->Execute(
"SELECT date_purchased
FROM " . TABLE_ORDERS . "
WHERE date_purchased IS NOT NULL
ORDER BY date_purchased ASC
LIMIT 1"
);

... then (so long as the store's recorded at least one order with a non-NULL purchase date) the query returns the expected value.

[DrByte]

Yes, the context is helpful.

One thing to note is that MySQL sorts nulls "first" in search results (lower than the lowest non-null value).
So, yes, excluding null is necessary when sorting for lowest non-null value on a nullable field.

[mc12345678]

FWIW, I'm investigating an issue reported on the SNAP Affiliates support-thread.

The SNAP-admin code currently determines the first-order-date for the given store via the following query:

$first_order_date = $db->Execute(
"SELECT date_purchased
FROM " . TABLE_ORDERS . "
ORDER BY date_purchased ASC
LIMIT 1"
);

If one or more of the store's orders has a NULL date_purchased, the query returns no records. If I change the query to:

$first_order_date = $db->Execute(
"SELECT date_purchased
FROM " . TABLE_ORDERS . "
WHERE date_purchased IS NOT NULL
ORDER BY date_purchased ASC
LIMIT 1"
);

... then (so long as the store's recorded at least one order with a non-NULL purchase date) the query returns the expected value.

Doing so will also "remove" any order that was singly made by a customer and logged as null. I found this on stackoverflow which allows sorting to have the null result(s) returned and to place them at the beginning or end based on the sort order provided: https://stackoverflow.com/questions/...ues-at-the-end

ORDER BY
    date_purchased IS NULL,
    date_purchased

Which would mean the WHERE date_purchased IS NULL portion could be removed.

[lat9]

Yes, the context is helpful.

One thing to note is that MySQL sorts nulls "first" in search results (lower than the lowest non-null value).
So, yes, excluding null is necessary when sorting for lowest non-null value on a nullable field.

Thanks, DrByte, for the confirmation.

Doing so will also "remove" any order that was singly made by a customer and logged as null. I found this on stackoverflow which allows sorting to have the null result(s) returned and to place them at the beginning or end based on the sort order provided: https://stackoverflow.com/questions/...ues-at-the-end

ORDER BY
    date_purchased IS NULL,
    date_purchased

Which would mean the WHERE date_purchased IS NULL portion could be removed.

That's the intent, @mc12345678, no purchase-date, no SNAP affiliate payment. I prefer the use of the WHERE clause as it's (IMO) a bit more readable.

[mc12345678]

In either use, readability is not really the issue. For the desired result (no date, no payment) the where clause is correct as it eliminates all "no date" items. In the situation of ignoring "no date" items unless that is the only item(s), the (yes) somewhat confusing order by method would at least offer the existence of such a record.

Noting also that I hadn't seen the subsequent "reasons" for the question before posting. :)