StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POMySQL: combining 2 queries (one empty) using UNION ALL results in error "1048 - Column cannot be NULL"
text
Body
copied!<p>I need to return two different results in a single query. When I run them <em>independently</em>, the first returns no rows (that's fine) and the second returns some rows (also fine). When I UNION ALL them, I get <code>1048 - Column "Date" cannot be null</code>.</p> <p>I need resulting rows of <code>Date, PW, errors</code> which I will feed a graph to show me what's going on in the system at the points in time specified by <code>Date</code>. In both tables, <code>Date</code> is of the format <code>DateTime</code> and must never be NULL.</p> <pre><code>SELECT `Date`, COUNT(`ID`) AS `PW`, 0 AS `errors` FROM `systemlogins` WHERE `Result` = 'PasswordFailure' AND `Date` >= DATE_SUB(NOW(), INTERVAL 1 DAY) UNION ALL SELECT `Date`, 0 AS `PW`, COUNT(`ID`) AS `errors` FROM `systemerrors` WHERE `Date` >= DATE_SUB(NOW(), INTERVAL 1 DAY) GROUP BY ( 4 * HOUR( `Date` ) + FLOOR( MINUTE( `Date` )/15)) --i.e. full 1/4s of hour ORDER BY ( 4 * HOUR( `Date` ) + FLOOR( MINUTE( `Date` )/15)) </code></pre> <p>I have read that MySQL might ignore tables' NOT NULL conditions in UNIONs, causing that error. I have indeed removed the "NOT NULL" restriction on the tables and, tada, it works. Now, those restrictions have been put there for a reason and I would like to keep them while running the aforementioned query - is there any way?</p> <p><strong>Edit</strong>: <code>Order</code> is the villain - removing it returns a correct result, albeit with one empty row where <code>Date</code> is <code>NULL</code>. For my purposes, I need to order the results by <code>Date</code> somehow.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload