DEV Community: Amit

Learning Dad's Garden: From 4 Minutes of Phone Video to a Printed Watering Sheet, Offline

Amit — Sun, 11 Oct 2026 19:08:54 +0000

This is a submission for the Hacktoberfest Open-Source AI Challenge Week 1: Touch Grass

My dad has looked after our pot plants for years. On Sunday morning he filmed them on his phone and explained each pot as he went.

At the bush with tag 12, he said: "Give regular amount of water, the usual amount."

He knows exactly how much that is. I didn't. So I built a tool that finds every line like that and asks him.

It's called Learning Dad's Garden. It turns his walk-through into a printed checklist I can take outside. Each pot gets a row, with a tick box for each day it needs something. Gemma 4 does the reading, on my laptop. Before anything prints, Dad answers the questions a beginner would ask.

What I Built

I'm taking up Dad's hobby, and he knows what every pot needs. Some of his instructions are exact. Plant with tag 4 gets "200 to 250 ml of water daily". For the pot with tag 9 he said "one cup of water or one and a half cup of water to be accurate, the green cup we have", which is exact, as long as nobody moves the green cup. Others only make sense if you already know the garden: "the usual amount", "water it regularly", "this plantation". To him, those are complete instructions.

Learning Dad's Garden turns a walk-through like that into something I can follow:

Dad films each part of the garden and talks, the way he would explain it to me in person.
"Learning Dad's Garden" turns his speech into text and splits it into instructions. Then it checks that every amount and time came from his words.
A gap check lists everything I couldn't act on. Dad answers on a local web page that shows the video frame and plays his voice from that moment.
The sheet prints. Each row has the tags, what to do, how much and when, a box for each day it's due, and Dad's exact words underneath.
I take the sheet outside, water, and tick. Each evening Dad checks the pots and marks every row: done right, missed or done wrong. My dad is grading my homework again.

The screen time comes first but it stays small. The routine lives on paper. This week I walk from pot to pot with the sheet and a pencil, and the laptop stays shut.

It works for anyone learning a garden from someone who knows it by feel. The command line calls them the owner and the helper. In our house, that's Papa and me.

The top of our sheet. Tag 12's "usual amount" is now half a glass of water, in Dad's typed words. What he said on Sunday sits under the row.

Demo

Code

GitHub URL

The licence is MIT.

It's built on faster-whisper, Ollama, Gemma 4, PyAV, Pillow, Jinja2 and headless Chromium for the PDF.

How I Built It

The model has one narrow job, and plain Python checks its work at every step. Pipeline :

Step	Done by
Speech to text, with the time of every word	faster-whisper `small.en`, on the CPU
Split into instructions	Gemma 4 E4B through Ollama, held to a JSON schema
Grounding check	Python
Days on the sheet	Python rules
Gap check	Gemma's flags, a phrase list, structure rules
Review	A local page: standard-library server, plain JavaScript
Print	Jinja template, then headless Chromium

What Gemma is allowed to do

Gemma gets the transcript of one part of the garden and a short list of rules. The first rule sets the boundary:

You are not a gardening expert. Never add advice, amounts, times or conditions that the speaker did not say.
(Well, neither am I, which is the whole reason this project exists.)

Ollama's structured output holds Gemma to a JSON schema. For each instruction, Gemma returns the exact quote, the tag numbers, the kind (do, don't, do only if, or a note), a short command, the amount, the timing and the condition. It also lists any words a helper couldn't act on. An example:

{
  "quote": "water this daily in the morning or in the evening anytime when you are free give regular amount of water, the usual amount",
  "pots_said": "",
  "pots": [],
  "kind": "do",
  "what": "Water",
  "amount": "regular amount of water, the usual amount",
  "when": "daily in the morning or in the evening anytime when you are free",
  "condition": "",
  "vague": [
    {"kind": "which_pot", "phrase": "this"},
    {"kind": "how_much", "phrase": "regular amount of water, the usual amount"}
  ]
}

Gemma flagged both gaps on its own. It couldn't tell which pot "this" was, or how much "the usual amount" is. Both became questions for Dad.

I kept Gemma's thinking on, which is its default, with temperature 0 and an 8,192-token context. Gemma 4 E4B can take audio as input, but Whisper still does the listening. The tool needs two things Whisper gives for every word. The time lets the review page play the right clip. The confidence lets the sheet mark words Whisper wasn't sure of. I also give Whisper a list of words from Indian gardens, like tulsi, money plant, curry leaves, lota, etc. so it expects them.

Every value has to be in Dad's words

A model that restructures text will sometimes try to help. So nothing Gemma returns goes straight onto the sheet. First, the code finds each quote in the transcript, word for word or close to it, with a similarity of at least 0.8. Then it checks every amount, time and condition. A value must be copied from the instruction's own words, or from elsewhere in the same video. Every number in it must appear there too. Anything else is deleted.

def ground_field(value: str, quote: str, transcript: str) -> str | None:
    """Where a field value came from.

    "said": copied from the instruction's own words.
    "inferred": copied from elsewhere in the same note (for example "do the
    same for 26"); the owner is asked to confirm it.
    None: not found; the value must be dropped.
    """
    if not value or not value.strip():
        return None
    if is_extractive(value, quote) and numbers_ok(value, quote):
        return "said"
    if is_extractive(value, transcript) and numbers_ok(value, transcript):
        return "inferred"
    return None

"Inferred" covers lines like "do the same for 26". The value is real but comes from another sentence, so the tool asks Dad to confirm it.

Days come from rules

The model never decides days. Plain rules turn the timing words into days on the sheet. "Daily" means every day. "Every alternate day", "one day gap" and "every 2 days" all mean days 1, 3, 5 and 7 on a 7-day sheet. That makes 11 rows on odd days and 5 on even days. A striped box means nothing to do that day.

The questions

The gap check rebuilds its questions from the instructions whenever anything changes, and it never stores a question as the truth. An answer changes the instruction. "It's fine as is" dismisses that one question. The questions come from five places:

Gemma's own flags, like the two above.
A phrase list of things people say instead of numbers. Three of its lines:

  ("how_much", r"\b(?:as usual|the usual(?: amount| way| quantity)?|like (?:usual|always|i do|before)|the same amount)\b"),
  ("how_often", r"\b(?:regularly|sometimes|occasionally|now and then|from time to time|often|frequently"
                r"|once in a while|on and off)\b"),
  ("when_to_act", r"\bdepend(?:s|ing)?(?: on| upon)?(?: how| what| whether| if| the)(?: \w+){0,4}"),

Structure rules: a watering row with no amount, two amounts joined by "or" (tag 9's "100 to 200 ml or 250 ml"), a command that is only a verb and an amount (tag 11's "Keep around 200 ml"), and a Do NOT line that also has a timing.
Coverage: tags that no instruction names.
Whisper: a word it heard with less than 5% confidence gets a question if it lands in a field on the sheet. In Dad's quotes it gets a "(?)", as in "amount brown (?) should be not more than 200 ml".

Every question carries its moment in the video. The page shows the frame and plays Dad's voice from the first word of the instruction to the last. He doesn't have to remember what "this" meant, because he sees what the camera saw. For pot with tag 12 he typed "This is tag number 12", then "that is usually 1/2 glass of water".

The tag 6 question, on a copy of the project without our videos, so the frame and audio player are missing. The green line repeats the previous answer back.

For the record, Plant (Tag #12) usual amount is half a glass of water. It took a 19-second video, a local AI model and one question to find that out. I could also have just asked him.

Why Does Open Innovation Matter?

The videos show our front door, our pots and my dad's voice. With Gemma and Whisper running on my laptop, none of it leaves the house, and after the first model download a full run works with the Wi-Fi off. Each run costs nothing, so I could re-run it as often as I liked. Anyone taking over a garden can run it too. It needs Docker and, for speed, an NVIDIA GPU, with Gemma 4 E2B as the smaller fallback.

Prize Categories

Best Use of Gemma. Gemma 4 E4B does all the reading. It splits Dad's speech into instructions, flags the words a beginner can't act on and, as an option, reads tag numbers in video frames. It runs locally through Ollama.

BreakMyQuery: find the row that breaks your SQL

Amit — Sun, 04 Oct 2026 14:55:47 +0000

BreakMyQuery: find the row that breaks your SQL

This is a submission for the Hacktoberfest Weekend Challenge: Build for a Friend.

What I Built

I built BreakMyQuery for a colleague(also a friend🫠) who is a new graduate hire and is struggling with SQL joins or let's say he's scared of SQL joins.

A query can pass the sample data while quietly dropping a customer who has no orders. For someone learning joins, seeing the missing customer gives them a concrete place to start investigating.

BreakMyQuery offers eight SQLite exercises covering joins, NULLs, aggregation, ties, arithmetic, dates, and anti-joins. The learner writes a query, runs it, inspects any counterexample, and tries a repair. Each counterexample shows the breaking dataset, the learner's result, and the expected result. My traps saves their mistakes so they can revisit a query through Retry.

I have demonstrated the app to my friend(or colleague). The goal is to help them connect the query they wrote to the rows it keeps, loses, or duplicates.

Demo

Try BreakMyQuery.

The first exercise asks for every customer's name and their number of paid orders, including customers with zero paid orders.

That one row exposes the problem. The learner can trace why Alice disappears and change their query. The app keeps the reference SQL out of the interface.

The hosted verification record documents the tested counterexample, accepted-query, error, history, and Retry paths.

Code

Source code, exercises, tests, and setup instructions.

The application code uses the MIT license. The model weights have their own licenses.

How I Built It

The stack is Python, Streamlit, SQLite, and Pydantic. The hosted demo uses the open-weight Gemma 4 26B A4B model through Google's Gemini API.

The app first checks the sample. If the results match, it searches for a breaking case:

Propose. The model receives the schema, exercise question, learner query, and reference query. It proposes small datasets intended to make the queries disagree.
Verify. The app validates the proposed data and executes both queries in SQLite. It compares results while preserving duplicate-row counts.
Shrink. It removes rows while preserving the mismatch and database constraints.
Practice. The learner sees the remaining data and differing results, then edits their own query.

Seeded random fuzzing generates additional datasets and provides a fallback when the model returns no usable proposal or the API is unavailable. The hunter does not use the exercise's authored trap fixtures.

What the evaluations showed

I evaluated model proposals and fuzzing separately against 14 deliberately wrong queries and eight equivalent alternatives.

Method	Wrong queries exposed	False positives on alternatives
Gemma 4 via API	14/14	0/8
Using Local Llama 3.2	9/14	0/8
Seeded random fuzzing	14/14	0/8

The Gemma catalog evaluation used live API calls from the local checkout; the deployed app was checked separately.

Gemma's figures combine the primary run with one explicit follow-up after an unusable response. Covering the 22 cases took 23 model attempts. The Gemma report preserves that failed call; the Llama report records the separate local trial.

On this catalog, random testing caught five mistakes Llama missed. Keeping both methods gives the app another chance to find a breaking case.

I also found a teaching problem: Llama could produce a valid counterexample and still explain its cause incorrectly. I disabled generated hints and mistake labels in the demo. Gemma's teaching quality remains unevaluated. The tables and query results give the learner evidence they can inspect.

These measurements cover a small authored catalog. A passing run means no counterexample was found within the search budget. It does not prove the query correct.

Why Does Open Innovation Matter?

Open weights give this project a local deployment option. Once the dependencies, runtime, and model are installed, the Ollama workflow runs inference and SQLite on the learner's computer without a hosted inference API.

The public demo offers an easier starting point: my friend can open a link without installing a model. That convenience has a different data path. Submitted SQL goes to Google, and SQLite verification runs on the app server. The interface discloses this.

I could evaluate local Llama and Gemma through the API using the same SQLite checker. Another developer can inspect that checker, swap the model, and repeat the evaluation. The model's role is explicit: it proposes cases, and SQLite checks whether they demonstrate a mistake.

The counterexample approach was inspired by XData at IIT Bombay. BreakMyQuery applies that idea to a small practice tool for my friend: find a concrete case, inspect the rows, and work out the repair.

Prize Categories

Best Use of Gemma: Gemma 4 proposes the breaking datasets used by the hosted demo. SQLite verifies each accepted counterexample before the learner sees it.

How AI text watermarking works

Amit — Sun, 27 Sep 2026 16:58:01 +0000

An AI model can leave a watermark in the words it chooses. The text looks ordinary, and the mark can survive being copied into Notepad because it is carried by the token sequence. A detector with the matching key can check for that pattern later.

Anthropic explained its approach in August 2026 in "How Claude's text watermark works". It uses a version of SynthID-Text, which Google DeepMind published in Nature in 2024. Anthropic says it is introducing watermarking to meet the EU AI Act's transparency requirements.

I wanted to understand how ordinary word choices could carry a signature, and what a detector finding that signature would actually tell us. I followed the choice of one token through the process, then tested a small implementation with GPT-2 in Google Colab.

Starting with one word

Let's say you upload a photo of a cat to Claude and ask, "What is the cat in this image doing?" Claude starts its answer with "The cat" and now has to pick the next token.

A language model writes one token at a time. A token can be a word, part of a word or punctuation. GPT-2's tokenizer for example, has roughly 50,000 tokens. The model assigns probabilities to possible next tokens. For a simplified example, assume only these three options have non-zero probabilities:

sits: 50%
sat: 30%
lies: 20%

These are invented probabilities and not actual measurements. We will treat each word as one token throughout the example. Without a watermark, sampling from these probabilities would pick "sits" about half of the time. The chosen token joins the sentence, and model calculates probabilities for the next token. The watermark changes how the next token is selected. Model's weights remain unchanged. For the same input, it still produces same initial probabilities. Watermarking just influences the selection.

Without the watermark, a model might write "The cat sits on the mat." With the watermark, it might write "The cat sat on the rug" instead. These are just illustrative outcomes. The choices happen as the text is generated.

A secret key, combined with the preceding tokens, determines the watermark scores for each possible next token. Changing the preceding tokens can change the scores. With same key, settings and token context, calculation gives the same scores every time. That repeatability makes detection possible later.

The tournament

SynthID-Text uses a method called tournament sampling to choose the next token. We can follow it through an example with four candidates and two rounds.
Suppose a model has written "The cat" and assigns these probabilities to the next token:

Token	Probability
sits	50%
sat	30%
lies	20%

First, we draw four candidates using those probabilities. Each draw can pick any of three tokens, including one already picked. One possible result is:
sits, sat, sits, lies

"sits" appears twice in this draw. It's 50% probability makes it more likely to appear, but that does not guarantee two places.
Next, watermarking algorithm uses key and preceding tokens to calculate a score of 0 or 1 for each candidate. Each round has its own scoring function. Within a round, repeated copies of the same token receive the same score. Suppose scores produce these matches:

Round	Match	Winner
1	sits (0) versus sat (1)	sat
1	sits (0) versus lies (1)	lies
2	sat (1) versus lies (0)	sat

The higher score wins each match. Ties are broken randomly. A token's score can differ between rounds, which is why "lies" scores 1 in the first round and 0 in the second. The winning token is "sat", so text now reads "The cat sat". Generation continues from there.

Although "sits" had the highest original probability, both of its copies lost in this tournament. Another draw could produce a different winner. The model's probabilities determine how likely candidates are to enter, and watermark scores determine which candidates advance. This is method described in SynthID-Text paper.

Picking "sat" once provides little evidence of a watermark. An unwatermarked model could pick it too. Across many tokens watermarked text tends to have higher watermark scores than expected by chance. The detector measures that tendency. The bracket helps explain the method. The implementation discussed later calculates the equivalent winning probabilities directly.

Does watermarking affect writing quality??? 🤔

Drawing candidates from the model's probabilities keeps likely continuations well represented. That alone does not guarantee good writing. SynthID-Text has a quality-preserving configuration and another that produces a stronger watermark at a cost to quality. Google tested the quality-preserving configuration on nearly 20 million Gemini responses. The paper reports no statistically significant difference in thumbs-up or thumbs-down ratings between watermarked and unwatermarked responses. When the model has very little choice about the next token, there is less room to leave a watermark. This makes detection harder in passages where accurate wording leaves few alternatives.

How a detector checks without the prompt

The detector uses text, secret key and same watermark settings used during generation. It also needs the matching tokenizer to split the text into tokens. For each token, it takes the preceding tokens and calculates that token's watermark scores again. The calculation is repeatable: the same inputs produce the same scores.

This works without the prompt because the preceding tokens are already in passage. With a four-token context, detector skips scoring the first four tokens. From the fifth token onward, it has the context it needs. A simple detector averages recovered scores across the eligible tokens. It skips repeated contexts so that a repeated phrase does not count as fresh evidence. SynthID-Text also supports more sophisticated detectors.

For text written without this watermark, average is expected to be around 0.5 because the scoring functions assign roughly equal numbers of zeros and ones. Watermarked text tends to score higher because tournament favours tokens with higher scores. An average above 0.5 is not enough on its own to establish a watermark. Short passages can score high by chance. Longer passages usually provide more evidence, provided enough of the watermark survives.

Detectors use thresholds calibrated against unwatermarked text to control false positives. For a simple average-based approach, the threshold also needs to account for text length. Editing can weaken the signal because it changes the inputs to the scoring functions. With a five-token window, replacing one token affects the score inputs for that position and the next four. Replacing a whole word may affect more tokens, depending on how it is tokenized. Small edits can leave much of the original signal intact. Extensive rewriting can make the watermark undetectable.

How the code implements the tournament

A tournament with 30 rounds would require 2³⁰ candidates for each token, which is over a billion. The implementation calculates the equivalent winning probabilities directly.

for i in range(depth):
    g_values_at_depth = g_values[:, :, i]
    g_mass_at_depth = (g_values_at_depth * probs).sum(axis=1, keepdims=True)
    probs = probs * (1 + g_values_at_depth - g_mass_at_depth)

g_values contains the watermark scores, and depth is the number of rounds. Each round adjusts the probabilities to favour tokens that scored 1. The adjustment depends on how much probability those tokens already hold. Using our cat example, the probabilities change as follows. In round 1, "sat" and "lies" score 1. In round 2, only "sat" scores 1; "sits" and "lies" both score 0.

Token	Original probability	After round 1	After round 2
sits	50%	25%	13.75%
sat	30%	45%	69.75%
lies	20%	30%	16.5%

The 69.75% comes from the chance that at least one finalist is "sat". After round 1, each finalist has a 45% chance of being "sat", so each has a 55% chance of being something else. The chance that neither is "sat" is 0.55 × 0.55 = 0.3025, or 30.25%. Since "sat" beats the other tokens in round 2, its chance of winning is 100% - 30.25% = 69.75%.

The implementation calculates these winning probabilities and makes one random choice from them. This gives each token the same chance of selection as running the tournament. "sat" is the most likely choice, but the other tokens still have a combined 30.25% chance.

Testing with GPT-2

I used GPT-2 in Google Colab to generate text with and without a watermark. The setup used 30 key values for 30 watermarking rounds, a five-token window, top_k=40 and temperature=0.7. Each response could contain up to 300 new tokens.

For each of five prompts, I generated one watermarked response and one plain response. I then took the first 20 words of the watermarked response. Separately, I made an edited version of the full watermarked response by replacing one word in four, starting with the first, with a random choice from "the", "a", "very" and "also". These replacements test the effect of disruptive edits rather than natural paraphrasing. I also scored an F1 text sample.

The detector averaged the watermark scores after excluding the opening tokens, repeated contexts and end-of-text padding. It produced a raw average score, not a probability that the text was AI-generated.

The first cell loads GPT-2 and sets the watermark configuration:

import torch, random
from transformers import (AutoTokenizer, AutoModelForCausalLM, SynthIDTextWatermarkingConfig, SynthIDTextWatermarkLogitsProcessor)

DEVICE = "cuda" if torch.cuda.is_available() else "cpu"
tok = AutoTokenizer.from_pretrained("gpt2")
model = AutoModelForCausalLM.from_pretrained("gpt2").to(DEVICE)

CFG = dict(ngram_len=5, keys=[654,400,836,123,340,443,597,160,57,29,590,639,13,715,468, 990,966,226,324,585,118,504,421,521,129,669,732,225,90,960],sampling_table_size=2**16, sampling_table_seed=0,context_history_size=1024)
wm_config = SynthIDTextWatermarkingConfig(**CFG)
lp = SynthIDTextWatermarkLogitsProcessor(**CFG, device=DEVICE)

The second generates a response with or without the watermark:

def gen(prompt, watermark, n=300):
    inp = tok(prompt, return_tensors="pt").to(DEVICE)
    out = model.generate(**inp, do_sample=True, top_k=40, temperature=0.7,max_new_tokens=n, pad_token_id=tok.eos_token_id, watermarking_config=wm_config if watermark else None)
    return tok.decode(out[0, inp.input_ids.shape[1]:], skip_special_tokens=True)

The third calculates the average watermark score:

def score(text):
    ids = tok(text, return_tensors="pt").input_ids.to(DEVICE)
    g = lp.compute_g_values(input_ids=ids).float()  
    eos = lp.compute_eos_token_mask(input_ids=ids, eos_token_id=tok.eos_token_id)[:, CFG["ngram_len"]-1:]
    rep = lp.compute_context_repetition_mask(input_ids=ids)
    m = (eos * rep).float().unsqueeze(-1)
    return ((g * m).sum() / (m.sum() * g.shape[-1])).item()

The fourth runs the comparisons:

prompts = ["Write about how coffee is grown and exported.", "Explain how trains work.",  "Descrieb a day at the beach.", "Tell the history of the bicycle.", "Explain how rain forms."]
human = """George Russell won the Azerbaijan Grand Prix in Baku on Saturday, 26 September, converting pole position into his third victory of the season. It was not a quiet afternoon. The race had two safety car periods, six retirements, and a finish so close that Russell crossed the line only a tenth of a second ahead of Max Verstappen. Isack Hadjar completed the podium in third place.

The first safety car came out after Alex Albon made an error and binned his Williams. The second followed by Franco Colapinto torpedo'ing himself in turn 1, which also ended the races of his teammate Pierre Gasly and Lando Norris. Both Aston Martins retired with mechanical problems. Oscar Piastri led for much of the race, but a lock-up cost him badly and he finished fourteenth. Charles Leclerc took fourth and Lewis Hamilton sixth. Kimi Antonelli had one of the best drives of the day, climbing from sixteenth to fifth.

The result extends Russell's championship lead and denies Verstappen his first win of the season."""

def edit(t, every=4):
    w = t.split()
    return " ".join(random.choice(["the","a","very","also"]) if i % every == 0 else x
                    for i, x in enumerate(w))

rows = {"WM long": [], "Plain long": [], "WM short (20 words)": [], "WM 25% edited": []}
for p in prompts:
    wm, plain = gen(p, True), gen(p, False)
    rows["WM long"].append(score(wm))
    rows["Plain long"].append(score(plain))
    rows["WM short (20 words)"].append(score(" ".join(wm.split()[:20])))
    rows["WM 25% edited"].append(score(edit(wm)))

for k, v in rows.items():
    print(f"{k:22} mean={sum(v)/len(v):.3f}  runs={[round(x,3) for x in v]}")
print(f"{'Human text (F1)':22} {score(human):.3f}")

Results

These scores are from one run. Generation and word replacement are random, so reruns can produce different values.

Text	Average score	Range across five prompts
Watermarked responses	0.564	0.548 to 0.586
Plain GPT-2 responses	0.497	0.489 to 0.509
First 20 words of watermarked responses	0.573	0.545 to 0.607
Watermarked responses with every fourth word edited	0.500	0.495 to 0.504
F1 text sample	0.498	One sample

The watermarked and plain responses had no overlapping scores in these five runs. The F1 sample scored close to the plain responses. This shows a clear difference in this experiment, though more samples would be needed to establish a reliable detection threshold and measure false positives.

The short excerpts also scored high, but I did not test short unwatermarked excerpts for comparison. These results cannot establish reliable detection on short passages.

Replacing every fourth word brought the average down to 0.500, close to the unwatermarked baseline. This editing pattern weakened the signal measured by this detector. It does not establish that changing any 25% of a passage would have the same effect.

This experiment used GPT-2 and the configuration shown above. It does not measure the performance of Claude's watermark. A detected watermark provides evidence that the matching watermarking system was involved in producing the text. Failing to detect one does not establish that a person wrote it.

Sources

Our SQL Server loop kept writing the previous row's value

Amit — Sun, 28 Jun 2026 16:08:34 +0000

A loop that looks correct, passes review, and still writes the wrong value, because of two documented T-SQL behaviors.

Here's a loop that resolves a value for each item: read it from a primary table, fall back to a secondary one when the primary has nothing, write the result. It looks correct. On the wrong data it writes the wrong value, and the wrong value isn't random: each bad row gets the value belonging to the row processed just before it.

No error, no exception. The loop reads correctly top to bottom. The cause is two T-SQL behaviors that Microsoft documents and that are easy to misread:

A variable declared inside a loop is set to NULL once for the whole batch, not once per iteration.
SELECT @var = column leaves the variable alone when the query returns no rows.

Either one is survivable alone. Together they make a missing row reuse the last good answer.

What the code looks like

WHILE (@cnt > 0)
BEGIN
    DECLARE @Value VARCHAR(100)
    SELECT @Item_Code = ITEM FROM @ItemList WHERE RowNo = @cnt

    SELECT @Value = isnull(p.ITEM_VAL, f.ITEM_VAL)
    FROM @Primary p
    LEFT JOIN @Fallback AS f ON p.ITEM_CODE = f.ITEM_CODE
    WHERE p.ITEM_CODE = @Item_Code
      AND p.ITEM_SEQ = (SELECT MAX(pmax.ITEM_SEQ) FROM @Primary pmax WHERE pmax.ITEM_CODE = @Item_Code)

    IF (@Value IS NULL) OR LTRIM(RTRIM(@Value)) = ''
    BEGIN
        SELECT TOP 1 @Value = ITEM_VAL FROM @Fallback WHERE ITEM_CODE = @Item_Code
    END

    UPDATE @Target SET ITEM_VAL = @Value WHERE ITEM_CODE = @Item_Code
    SET @cnt = @cnt - 1
END

Read top to bottom, the intent is plain: resolve from the primary table, fall back to the secondary one, write the result. The DECLARE @Value inside the loop looks like it hands each pass a clean variable. That assumption is the trap.

A DECLARE inside a loop isn't a reset

In C++, Java, or C#, a variable declared inside a loop body is a fresh binding every pass. T-SQL doesn't do that. A local variable's scope is the whole batch, and it's initialized to NULL when it's declared, not at the point in the code where you wrote DECLARE.

So putting DECLARE @Value inside the WHILE is just decoration. There's one @Value for the whole run, set to NULL once, and every iteration after the first inherits whatever the previous one left in it. That's also why SQL Server doesn't complain about a double declaration even though the DECLARE line is reached on every pass; it isn't a statement that re-runs each time.

A quick proof:

DECLARE @i INT = 0
WHILE @i < 3
BEGIN
    DECLARE @x INT
    SET @x = ISNULL(@x, 0) + 1
    PRINT @x
    SET @i = @i + 1
END

It prints 1, 2, 3. If the DECLARE reset @x each pass, you'd get 1, 1, 1.

Docs: DECLARE @local_variable (Transact-SQL).

SELECT-assignment does nothing when nothing matches

The resolve uses SELECT @Value = .... When the item isn't in the primary table, the WHERE returns no rows. You'd assume that sets @Value to NULL. It doesn't. With SELECT-style assignment, a zero-row result leaves the variable exactly where it was.

So on a missing item the assignment runs, changes nothing, and @Value still holds whatever the previous item resolved to.

Microsoft documents this exact case in Example A on that page: a variable set to a default, a SELECT @var = ... WHERE that matches nothing, and the variable keeps the default.

Docs: SELECT @local_variable (Transact-SQL).

How the two combine

Now the failure is just mechanics:

@Value is never cleared between iterations.
For an item missing from the primary table, the resolve matches nothing and doesn't touch @Value.
The guard IF (@Value IS NULL) OR LTRIM(RTRIM(@Value)) = '' sees a leftover non-null value, decides resolution already worked, and skips the fallback.
The item gets written with the previous item's value.

The fallback that should have rescued the missing item never fires, because the stale value makes it look like there was nothing to rescue.

Fixing it

Two changes close the gap.

Clear the variable at the top of every iteration so a missing row can't carry the last value forward:

WHILE (@cnt > 0)
BEGIN
    SET @Value = NULL
    ...
END

And for a single-value lookup like this, prefer SET with a scalar subquery over SELECT-assignment. A scalar subquery that finds no rows gives you NULL, which is what you want here, because then the IS NULL guard does its job:

SET @Value = (
    SELECT isnull(p.ITEM_VAL, f.ITEM_VAL)
    FROM @Primary p
    LEFT JOIN @Fallback f ON p.ITEM_CODE = f.ITEM_CODE
    WHERE p.ITEM_CODE = @Item_Code
      AND p.ITEM_SEQ = (SELECT MAX(pmax.ITEM_SEQ) FROM @Primary pmax WHERE pmax.ITEM_CODE = @Item_Code)
)

It reads fine. Each behavior is harmless on its own, which is why the pair survives review. If a loop variable can outlive its iteration, assume it does.