Snowflake SQL error: NULL result in a non-nullable column

Troubleshooting Snowflake SQL Error : NULL result in a non-nullable column

When working with Snowflake, you might encounter the error message:

SQL Error [100072] [22000]: NULL result in a non-nullable column

This error can be misleading at first glance. Let's walk through a common scenario that triggers this error and how to resolve it.

Scenario

Consider the following SQL script:

-- Create the test table
CREATE TABLE set_test (
    id INT NOT NULL,
    is_deleted CHAR(1) NOT NULL,
    foo FLOAT NULL
);

-- Insert a few records
INSERT INTO set_test (id, is_deleted, foo) VALUES
(1, 'F', 10.5),
(2, 'F', 20.0),
(3, 'F', 30.75);

-- Update one record to set is_deleted to 'T' and foo to NULL
UPDATE set_test
SET is_deleted = 'T' and foo = NULL
WHERE id = 2;

When you run the UPDATE statement, Snowflake returns the error message:

SQL Error [100072] [22000]: NULL result in a non-nullable column

Understanding the Error

The error message indicates that there is an attempt to insert a NULL value into a column that does not allow NULL values. However, in this case, the error is not due to the NULL value itself but rather a syntax issue in the UPDATE statement.

Resolving the Error

The issue lies in the UPDATE statement:

UPDATE set_test
SET is_deleted = 'T' and foo = NULL
WHERE id = 2;

In SQL, the SET clause should use commas to separate multiple column assignments, not the AND keyword. The correct UPDATE statement should be:

UPDATE set_test
SET is_deleted = 'T', foo = NULL
WHERE id = 2;

By replacing and with a comma, the UPDATE statement will execute without errors:

UPDATE set_test
SET is_deleted = 'T', foo = NULL
WHERE id = 2;

Conclusion

When you encounter the "NULL result in a non-nullable column" error in Snowflake, double-check the syntax of your UPDATE statement. Ensure that you are using commas to separate column assignments in the SET clause. This simple fix can save you time and prevent confusion when troubleshooting SQL errors.

If this does not help, see also Snowflake KB Inserting or loading data in table fails with error "NULL result in a non-nullable column".

TP-Link Archer AX6000 vs MSI RadiX AXE6600 vs TP Link Deco P9

I had my TP Link Deco P9 (Wi-Fi 5 from 2019) system with three mesh nodes since 2020, but I was not thrilled with its performance. In 2022, I complained to TP Link support, but they did not help, so now I compared the Deco P9 to a MSI RadiX AXE6600 (Wi-Fi 6e from 2023) and TP-Link Archer AX6000 (Wi-Fi 6 from 2018). In 2020, I paid $250 for the Deco. In 2024, I bought the MSI used for $133.89 and the Archer for $63.49. Do not confuse the RadiX AXE6600 (Wi-Fi 6e) with AX6000 (Wi-FI 6): even Google Search mixes them up.

Attribute	MSI RadiX AXE6600	TP-Link AX6000	TP-Link Deco P9
Brand	MSI	TP-Link	TP-Link
Model	RadiX AXE6600	AX6000	Deco P9
Paid Price	$133.89 in 2024, Amazon used	$63.49 in 2024, ebay	$250.00 in 2020, new
Wi-Fi Generation	6E	6	5
Beamforming	Yes	Yes	Yes
Streams	2/2/4	4/4/0	MU-MIMO
Antennas	6	8	2
2.5 Gbps LAN Ports	1	1	0 per unit
1 Gbps LAN Ports	4	8	2 per unit

For preformance testing, the Wi-Fi systems were set to AP mode and hard wired over 1 Gbps Ethernet to a Belkin RT3200 running OpenWRT, functioning as an iperf3 server to test LAN performance not confounded by an ISP. The MSI was set to 80 MHz on the 5 GHz band (the maximum) and 160 MHz on the 6 GHz band. The Archer was set to 80 MHz on the 5 GHz band. The Archer can do 160 MHz, though the setting was hard to find, and in other testing, it did not make a big difference at close range.

With iperf3 in default mode, the client sends (i.e., uploads): I call this forward mode. In reverse mode, the client receives (i.e., downloads). I used the PingTools Android app for iperf3 and PingMon app for ICMP ping.

The network client was usually a Samsung Galaxy S24 (Wi-Fi 6e) smartphone, but a few tests were done with a Galaxy A15 (Wi-Fi 5), which is a much cheaper phone.

Performance at close range

I began by testing at close range, which represents the ideal scenario for Wi-Fi. In this test, the Deco P9 was a satellite hard-wired to the primary Deco.

Here the MSI RadiX AXE6600 outperformed the others on the 5 GHz band, and as you would expect, the MSI performance on the 6 GHz band was even better than the MSI on 5 GHz.

Strangely, the cheaper Galaxy A15 had much better latency than the Galaxy S24, though the Galaxy S24 performed better than the Galaxy A15 on bandwidth tests, which is expected because the Galaxy S24 supports a newer generation of Wi-Fi.

Access Point	Band	Client	Ping avg ms	iperf forward Mbps<	iperf reverse Mbps
MSI RadiX AXE6600	6 GHz	Galaxy S24	16	777	909
MSI RadiX AXE6600	5 GHz	Galaxy S24	18	578	716
TP-Link Archer AX6000	5 GHz	Galaxy S24	17	617	760
MSI RadiX AXE6600	5 GHz	Galaxy a15	3	314	338
TP-Link Archer AX6000	5 GHz	Galaxy a15	4	334	312
Deco P9	5 GHz	Galaxy a15	4	277	289
Deco P9	5 GHz	Galaxy S24	16	307	472

Indoors, far away

Next, I tested with the phones in a part of the house with poor network coverage. I limited testing to 5 GHz because this location has a desktop computer that uses only 5 GHz.

In a reversal from the close-range testing, the TP-Link Archer outperformed the other access points.

Access Point	Band	Ping Avg MS	Iperf Forward (Mbps)	Iperf Reverse (Mbps)
MSI RadiX AXE6600	6 GHz	27	78	199
MSI RadiX AXE6600	5 GHz	37	30	34
TP-Link Archer AX6000	5 GHz	18	94	318
TP-Link Deco P9	5 GHz	19	41	70

Outdoor

My final test was outdoors where I use a 2.4 GHz camera and 5 GHz phones, so I tested all bands.

I was surprised the 6 GHz worked outside, and it was still fast. The Archer outperformed the other APs on the 2.4 GHz band, and like in the last test, also on the 5 GHz band.

Access Point	Band	Ping Avg MS	iperf Forward (Mbps)	iperf Reverse (Mbps)
MSI RadiX AXE6600	6 GHz		261	558
MSI RadiX AXE6600	5 GHz	67	38	228
TP-Link Archer AX6000	5 GHz	20	303	431
MSI RadiX AXE6600	2.4 GHz	36	6	21
TP-Link Archer AX6000	2.4 GHz	38	28	22
TP-Link Deco P9	2.4 GHz	37	8	17

Other thoughts

The MSI RadiX AXE6600 has a good web interface for viewing system information and changing settings, but the MSI app never logged in for me. When my netmask was 255.0.0.0 the MSI would go into a loop, using all memory, and invoking the OOM killer, so I changed the netmask as a workaround. The MSI made 172800 DNS queries per day, which is excessive.

The Archer's Tether app is spartan with little information and few settings. The Archer has much more info and settings available through its web interface than the Tether app. While the Deco app is the better than Tether, I was annoyed by the slow refresh rates and the lack of a time series display for the network usage.

The MSI 6 GHz band performed well outside and throughout the house, so it makes a credible use case as a high-performance backhaul for Wi-Fi 6e mesh systems.

Achieving gigabit performance on these Wi-Fi systems was rare, so it makes a case against paying for high speed ISPs faster than gigabit. Devices like the Galaxy A13 could not even use half of a gigabit Internet plan.

Conclusion

Despite its age, I decided to keep the TP-Link Archer and sell the Deco P9 and MSI RadiX because the Archer performed best throughout the house and outdoors. It was a surprise that a single device, the Archer, could outperform the three units of the Deco's mesh system, and the Archer was also the oldest, outperforming the newest, which was the MSI. For me, consistent performance across my house and outdoors was more important than fastest speeds at close range. I discounted performance on the 6 GHz band (MSI) because few of my devices have a 6 GHz band: many of my devices are IoT devices that support only 2.4 GHz, while other devices are Wi-Fi 4 and Wi-Fi 5 on 5 GHz. The Archer has more than enough bandwidth to support my DSL ISP.

I bought a dashcam from Temu for $6.31 January 2024, and here is sample footage that includes three scenes: daytime, dusk, and daytime.

Product benefits

• Easy mounting with suction cup
• Easy power with 12V cigarette plug adapter
• Battery lasts a few moments after car turns off
• MicroSD card included
• Cheap price

Problems

• Cheap quality
• Terrible video quality (despite product description)
• Narrow field of view (despite product description)

Notes

• It was a weird choice for it record in .avi instead of .mp4 container.
• The product was discontinued on Temu.

Model (box): Y320
Manufacturer (box): shenzhen Hengxin Weiye Digital Co., LTD
Product title (Temu):
Dash Camera For Cars With 32G Memory Cards Wide Angle Full 1080P Driving Record...

Below is the product menu (PDF on Temu):
Intelligent voice reminder, built-in multinational voice pronunciation, no need to worry about language barriers 1080P highdefinition night vision, even in the weak light environment, can also shoot clearly Loop recording, no missing seconds, segmented storage, automatic monitoring of sto rage space, when the memory is full, automatically delete the earliest recorded video and save the new video Builtin gravity sensor, when a sudden brake or collision is sensed, the current video is instantly locked to prevent overwriting important files during loop recording Supported languages: English, French, German, Russian, Japanese, etc.

Timestamp precision in Snowflake

Timestamps in Snowflake have precisions 0 to 9 with a default of 9, which is a nanosecond, but the Snowflake documentation is not clear on precisions 0 to 8.

Storage difference

I did an empircal test by creating tables. Each table had one million rows and one column with random timestamps. The values have an original precison of one nanosecond, and I used random values because otherwise Snowflake would compress down any number of rows with the same values to a few KB.

create or replace table zzz_timestamp9 as
select dateadd(nanosecond, uniform(1,3e17, random()), current_timestamp())::timestamp(9) as time1
from TABLE(GENERATOR(ROWCOUNT => 1e6))
;
create or replace table zzz_timestamp0 as
select dateadd(nanosecond, uniform(1,3e17, random()), current_timestamp())::timestamp(0) as time1
from TABLE(GENERATOR(ROWCOUNT => 1e6))
;

The storage difference for 1 million rows was 3.5MB vs 7.0MB.

Precision difference

Again, I generated random rows and then copied the value into columns with varied precisions.

select
    dateadd(nanosecond, uniform(1,3e17, random()), current_timestamp())::timestamp(9) as "precision 9",
    "precision 9"::timestamp(3) as "precision 3",
    "precision 9"::timestamp(2) as "precision 2",
    "precision 9"::timestamp(1) as "precision 1",
    "precision 9"::timestamp(0) as "precision 0",
    datediff(ms, "precision 3", "precision 9") as "Precisions 9 vs 3 in milliseconds", /* alwayz zero */
    datediff(second, "precision 0", "precision 9") as "Precisions 9 vs 0 in seconds" /* alwayz zero */
from TABLE(GENERATOR(ROWCOUNT => 10))

Precision 0 is 1 second, precision 1 is 100 ms, precision 2 is 10 ms, precision 3 is 1 ms, etc.

openwrt ssh connection refused

Symptom

Normal connection attempt

$ ssh root@192.168.1.1
Connection to 192.168.1.1 closed.

End of log with verbose ssh

debug1: Authentications that can continue: publickey,password
debug1: Next authentication method: publickey
debug1: Offering public key: /home/z/.ssh/id_ed25519 ED25519 SHA256:XX//XX/XX agent
debug1: Server accepts key: /home/z/.ssh/id_ed25519 ED25519 SHA256:XX//XX/XX agent
Authenticated to 192.168.1.1 ([192.168.1.1]:22) using "publickey".
debug1: channel 0: new [client-session]
debug1: Entering interactive session.
debug1: pledge: filesystem
debug1: Sending environment.
debug1: channel 0: setting env LANG = "en_US.UTF-8"
debug1: client_input_channel_req: channel 0 rtype exit-status reply 0
debug1: channel 0: free: client-session, nchannels 1
Connection to 192.168.1.1 closed.
Transferred: sent 2488, received 1012 bytes, in 0.0 seconds
Bytes per second: sent 346691.3, received 141017.5
debug1: Exit status 1

System log snippet in OpenWRT's luci interface

Sat May 20 23:02:33 2023 authpriv.notice dropbear[7690]: Pubkey auth succeeded for 'root' with ssh-ed25519 key SHA256:XX//XX/XX from 192.168.1.X:X
Sat May 20 23:02:33 2023 authpriv.info dropbear[7691]: Exit (root) from <192.168.1.X:X>: Child failed
Sat May 20 23:02:33 2023 authpriv.info dropbear[7690]: Exit (root) from <192.168.1.X:X>: Disconnect received

Background

A few weeks ago, I installed OpenWRT 22.03.3 on my Belkin RT3200. SSH and everything else was working fine until I updated it to OpenWRT 22.03.5, and then immedietly SSH refused to connect.

Solution

I read a GitHub conversation about a similar problem. In their case, zsh was missing, and I remembered that earlier I installed Bash. I reinstalled it via LuCI, and then SSH worked again.

Enable 5G standalone (NR SA) on Samsung Galaxy A13

I bought two Samsung Galaxy A13 5G phones from Google Fi for $100 each on promo, but by default, they connected in NR NSA (non-standalone) mode, which requires an LTE anchor. Performance was okay in NR NSA except in few geographic areas where LTE bands 2, 4, and 66 had high packet loss of 50-100% because of low signal or interference.

With a little effort, Samsung Galaxy phones can be put into NR SA ("pure 5G") mode. The process is similar to the Galaxy S22 (see guide by peacey8 at end), but there are a few differences. In particular, the A13 uses a Mediatek MT6769V/CU Helio G80 chipset instead of the Qualcomm SM8450 Snapdragon 8 Gen 1 on the Galaxy S22, so the serivce menus differ.

Pros and cons

Pros

• Lower latency and packet loss (sometimes/generally).
• Latest generation of cell network technology.

Cons

• Requires compatiable carrier. (In the USA, it's only T-Mobile and their MVNOs such as Google Fi and Mint Mobile.)
• Requires somewhat modern phone.
• Requires compatible cell tower. (In my area, 5G coverage is excellent.)
• T-Mobile does not support voice yet on NR SA mode. If band locking is set to NR SA, phone calls will not work. If band locking allows LTE and NR NSA, it will automatically fall back to LTE/NR NSA.
• Allowing NR SA mode requires some initial technical set up.
• Without band locking, the phone may often favor NR NSA over NR SA, even when NR SA has better network performance.
• Band locking settings (optional) gets lost after phone reboot.
• Set up varies by chipset.

Guide

This guide assumes your phone has the XAA (unlocked) CSC profile. If not, see peacey8's guide at the end. If you have TMB profile, then NR SA mode should already work. Once you have XAA, proceed here.

1. On the phone, enable developer mode: go to settings (gear) - About Phone - Software Information - tap seven times on build.
2. In phone settings (Gear) - Developer Options - enable USB debugging.
3. Install Samsung USB driver for Windows. (Sorry, you need a laptop or desktop.)
4. Launch the SamFW FRP tool on Windows.
5. Connect phone by USB cable to Windows machine.
6. In SamFW, enable secret code for Verizon. (You do not need to use Verizon as a carrier.)
7. SamFW should stop at "Waiting for DIAG."
8. In Samsung Phone app (with the blue icon), call *#0808#. (It does not work for me in the Google Phone app with the green icon.) After hitting the last pound (and before hitting the call button), the USB settings menu should appear.
9. If you have two options, then you did not enable USB debugging. Start over.
10. Tap DM+ACM+ADB and tap OK. (The Galaxy S22 has way more USB debugging options, by the way.)
11. SamFW should stop at "Disabling DIAG."
12. Then call *#0808#, and switch back to MTP.
13. Phone can be disconnected from USB, if you wish.
14. In the Samsung phone, app use the dialer code *#27663368378#.
15. UE SETTING AND INFO -SETTING - PROTOCOL- NR -ALLOW LIST- ALLOW LIST OFF
16. Three dots (top right), back
17. NR5G SA / NSA mode control - SA / NSA enable
18. Reboot phone.

Once it is working, you can disable the USB debugging and developer options menu on the phone, and you can uninstall SamFW.

Troubleshooting

Unlike the Galaxy S22, the *#2263# menu will not look any different on the A13. Even when NR SA is active, this service menu looks the same. It doesn't distinguish NR NSA and NR SA, so to confirm you are on SA, some options are:

• Dial *#0011#. Pick relevant SIM. Check fourth line "Serving PLMN." If you see Nr5G, it's SA. If LTE, then it's not.
• Use app such as CellMapper, Network Cell Info Lite, or NetMonster. They will all show the network type on the main page.

If you are still on LTE or NR NSA, some options:

• Check your carrier supports NR SA. Right now, T-Mobile does, while Verizon and AT&T do not.
• Check you have a 5G SIM and a 5G plan.
• Turn off wifi. (It may use LTE only for power savings.)
• Move closer to the tower.
• Move to another tower.
• Reset the connection like this: phone settings (gear) - Connections - Mobile networks - LTE/3G/2G. Wait a moment and set back to 5G/LTE/3G/2G. On our two A13, S22, and S22+ phones, we often need to reset the connection like this, and airplane mode does not seem to help. Otherwise, it may stick to LTE when NR NSA or NR SA are available.
• Disable LTE like this: in the Samsung Phone app (green app icon), call *#2263#. Tap the relevant SIM. Tap CLEAR ALL BANDS. Tap "BLOCK SET BY AP," so the asterisk goes away. Tap NR menu to enter it. Select NR ALL. Go back to main. Apply selection. This may reset after rebooting the phone.
• Alternate way to force NR: in Google Phone app (blue app icon), call *#*#4636#*#*- Phone Information - Set preferred network Type - NR only.

I tested this two Galaxy A13 phones running Android 12 with T-Mobile via Google FI. In case you want to join Google Fi, here's a referral code to get a $20 credit when you join: 2RD2V5.

On the Galaxy S22, voice calls do not work with NR SA: incoming calls go straight to voicemail, and outgoing calls stop a moment after dialing because VoNR apprently is not enabled. It is probably the same on VoNR.

Thank you much to the NR SA for Galaxy S22 guide from peacey8 and molexs's comment about the SCR01 hotspot.

Generate random names and addresses from SAS

For testing data processing systems (e.g., CRM, record linkage), you may need to generate fake people. SAS makes it uniquely easy to generate an unlimited count of fake US residents because it comes with a data set of US zip codes, which include the city and state name.

The system uses four data sets: first names, last names, street names, and US zip codes. Initials are randomly generated from letters. The street addresses probably do not exist in the given zip codes.

You could extend this by:

• Add street directions (i.e., N, S, E, W)
• Add street post type (e.g., Dr., Ct.)
• Add units (e.g., Apt B, Ste 101)
• Add post office boxes and private mail boxes
• Spell out the middle name
• Add name prefix (e.g., Dr., Mr.)
• Add name suffix (e.g., Jr., Sr.)